MySQL统计32万行VISITS表慢查询优化、索引调整及删除提速

当前查询虽然在EXPLAIN中显示命中覆盖索引（Extra为USING INDEX），但实际性能差的核心原因有两点：

1. 未加最长统计周期的时间过滤条件，需要扫描目标用户名下全部32万+条索引记录逐行做时间判断，扫描范围过大
2. 索引首列USER_ID定义为char(255)，单条索引仅该列就占用765字节（utf8编码下每字符占3字节），单索引页可存储的条目数极少，扫描时需要加载大量索引页，IO成本极高
   另外EXPLAIN显示的预估扫描行数1是统计信息失真导致的——当前全表仅存在1个USER_ID值，优化器基数计算不准，不代表实际扫描量。

1. count统计查询优化 #

• 给查询加时间下限收窄扫描范围：需要统计的最长时间周期是12个月，直接在WHERE条件中增加12个月的时间过滤，数据库会利用(USER_ID, VISITED_IN)的联合索引，直接定位到12个月前的索引起点开始扫描，跳过所有更早的无效记录，同时12个月维度的统计可以直接用COUNT(*)省去CASE判断，修改后语句如下：

SELECT 
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 60 MINUTE) THEN 1 END) AS LAST_60_MINUTES,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) AS LAST_24_HOURS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) AS LAST_7_DAYS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) AS LAST_30_DAYS,
COUNT(CASE WHEN VISITED_IN >= DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 1 END) AS LAST_6_MONTHS,
COUNT(*) AS LAST_12_MONTHS
FROM VISITS 
WHERE USER_ID = 'C9YAoq'
AND VISITED_IN >= DATE_SUB(NOW(), INTERVAL 12 MONTH);

• 极端场景下用预聚合代替实时扫表：如果单用户12个月内的访问量持续处于几十万级别的异常高位，不要每次请求实时扫原表统计。可以按小时/天粒度做预聚合，新建汇总表存储每个用户每小时/每天的访问量，查询时直接按时间范围汇总计数，耗时可以降到毫秒级。近60分钟这种短周期统计，可以搭配Redis做短周期缓存进一步降低压力。

2. 过期数据删除效率优化 #

• 替换全量删除为分批小粒度删除：不要一次性删除所有过期数据，每次删除限定行数，循环执行直到无符合条件的记录，避免长事务、锁表和大量binlog写入影响线上业务，示例语句如下，每次删完可以休眠200-500毫秒降低IO冲击：

-- 循环执行，直到影响行数为0即删除完成
DELETE FROM VISITS 
WHERE VISITED_IN < DATE_SUB(NOW(), INTERVAL 12 MONTH)
LIMIT 1000;

• 数据量大时用分区表实现秒级过期清理：如果使用MySQL 5.7及以上版本，可以将表改为按VISITED_IN做范围分区，按月份拆分分区，过期数据清理直接执行DROP PARTITION删除对应月份的分区，属于元数据操作，秒级完成，效率远高于逐行删除。注意分区键需要包含在现有联合索引中，不会影响现有查询性能。
• 批量删除时如果没有级联删除用户数据的需求，可以临时关闭外键检查（SET FOREIGN_KEY_CHECKS=0;），删除完成后再打开，减少外键校验的额外开销，操作前务必做好数据备份。

3. 索引配置优化 #

现有索引有明确的优化空间，不需要额外新增索引：

• 缩小USER_ID字段长度：从示例值C9YAoq来看，用户ID是短字符串，完全不需要定义为char(255)，根据实际用户ID的长度调整为定长char(N)或变长varchar(N)即可，比如6位定长ID就改为char(6)，索引单条体积会从765字节降到十几字节，单索引页存储的条目数提升数十倍，扫描速度会有量级提升。
• 现有联合索引(USER_ID, VISITED_IN)的列顺序是正确的，符合先等值匹配USER_ID、再范围匹配VISITED_IN的查询模式，不需要调整列顺序，修改字段类型后索引效率会自然提升。
• 不要在该表上额外建其他索引，会增加写入时的索引维护成本，现有索引已经可以覆盖所有查询场景。

内容的提问来源于stack exchange，提问作者Taha Sami