MySQL查询扫描超50万条数据速度极低，添加索引无效果如何优化？

优化方案 #

1. 清理冗余连接、调整连接类型 #

原查询中LEFT JOIN wp_dh_vaps没有在SELECT、WHERE、关联条件中用到任何该表的字段，属于完全冗余的连接，直接删除即可大幅降低运算开销。
同时WHERE条件要求pID必须存在于wp__site_points的筛选结果中，原LEFT OUTER JOIN wp__site_points可以改为INNER JOIN，提前过滤不匹配的记录，缩小后续参与连接的数据集规模。

2. 替换多层嵌套IN子查询为JOIN逻辑 #

MySQL对多层嵌套IN子查询的优化能力较弱，大概率会触发全表扫描，改为从最小的权限过滤表开始关联的JOIN逻辑，执行效率提升非常明显，改写后等价的SQL参考如下：

SELECT 
  u.display_name, 
  sp.point_nam, 
  ps.Report, 
  DATE_FORMAT(ps.Timestamp, '%e %M %Y %H:%i:%s') AS Pretty
FROM wp_dh_site_access sa
INNER JOIN wp_dh_site_vap sv ON sa.site_id = sv.ID
INNER JOIN wp__site_points sp ON sv.site_id = sp.site_fk
INNER JOIN wp__point_scans ps ON sp.pID = ps.Checkpoint
LEFT JOIN wp_users u ON u.ID = ps.Name
WHERE sa.user_id = 38 AND sa.active = 1;

3. 新增联合覆盖索引 #

避免单列索引无法覆盖查询逻辑导致的回表开销，按如下规则创建索引即可让所有过滤、关联、查询操作都直接走索引完成：

• wp_dh_site_access：新增联合索引 idx_user_active_site(user_id, active, site_id)
• wp_dh_site_vap：新增联合索引 idx_id_site(ID, site_id)
• wp__site_points：新增联合索引 idx_site_fk_pid(site_fk, pID, point_nam)
• wp__point_scans：新增联合索引 idx_checkpoint_fields(Checkpoint, Name, Report, Timestamp)

4. 可选扩展优化 #

如果wp__point_scans的数据量还在持续增长，可以考虑按Timestamp字段做时间范围分区，后续如果新增时间维度的过滤条件，性能提升会更显著。

内容的提问来源于stack exchange，提问作者Faisal Iqbal