MySQL 大量异常数据导致慢查询

现象

服务端生产环境报错

org.springframework.dao.RecoverableDataAccessException: ### Error querying database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 420,676 milliseconds ago. The last packet sent successfully to the server was 420,678 milliseconds ago.

定位到具体位置后发现是SQL查询过慢导致

select distinct mccr.xxx_id
        from ap ap
        inner join mccr mccr on ap.xxx_id = mccr.xxx_code
        where ap.status_code = 1 and mccr.status_code = 1

• 其中ap表数据量1.3W mccr表数据链4W+
• 两张数据量不大的表连接查询异常慢
• 关联字段为varchar类型大概是varchar(64)
• status_code字段为逻辑删除字段

思路

• 考虑是否没走索引导致

  • 两个关联字段上都有索引
  • 通过explain看到inner join 自动将数据量少的ap作为驱动表，用到了被驱动表mccr的索引

• status_code 为逻辑删除标识字段，DBA观测到SQL过慢后觉得where条件后该字段无索引导致给ap的status_code加上索引

  • 加上索引后确实变快了

• explain查看 在ap加索引之前ap作为驱动表，ap加逻辑删除的索引后MySQL inner join选择机制变更且Extra列中出现Using index condition产生索引下推，变为mccr作为驱动表 推论考虑到ap加上索引后可以用到索引下推因此ap利用索引效率更高（可能还用到了索引合并 因为ap上 逻辑删除字段status_code和连接字段都是单独索引）

• 现象：全表扫描4w的表比ap表要更快

• 探索：变更 删除索引，强制用right join让mccr强制全表扫描，且去掉where后的条件，发现速度从25s降低到11s 还是很慢

• 考虑：可能varchar索引没有建立前缀索引 导致变慢

  • 查询区分度

  • SELECT
    	count(
    	DISTINCT LEFT ( customer_id, 5 ))/ count(*) 
    FROM
    	ap;
    

  • 发现10个字符左右区分度达到一半然后无法再上升,两表都是类似现象

  • 构建前缀索引

• 具体执行后发现前缀索引基本没什么效果

• 通过SET profiling=1;select;SHOW PROFILE;SET profiling=0; 查看具体执行

• 发现executing列执行时间占了95%+无法更高提升的区分度+两个字段只是not null default ''并不是唯一

• 推论：字段实际数据有问题，导致MySQL执行查询速度慢

• 字段异常统计

  • select customer_id,count(1) as count 
    				from ap
    				where status_code = 1
    				group by customer_id
    				order by count desc
    

  • 结果显示空字符串的数据列高达6000+占用了将近一半的空间

• 推论：大量相同数据导致关联/查找每次都类似全表扫描

解决方案

• 复制一张表 在其他环境做测试，将表内无效数据（空字符串）填充为随机字符串
• 再执行关联查询发现速度变快
• 解决方案：在where后加上 ap.customer_id ！= ‘’条件 过滤无效字段+索引下推极大加快查询效率

小结

• join可以用但是join字段要加索引
• 走索引更优但是索引的效果要足够好
• 一个区分度极低的索引还不如全表扫描
• 非唯一索引注意索引字段的区分度，若区分度不够高则考虑是否存在异常数据