MySQL如何为指定查询设置最优索引，解决带where、大offset查询慢问题

问题SQL #

SELECT
    attachment.attachment_id AS attachmentID,
    attachment.data_item_id AS candidateID,
    attachment.title AS title,
    candidate.first_name AS firstName,
    candidate.last_name AS lastName,
    candidate.city AS city,
    candidate.state AS state
FROM
    attachment
LEFT JOIN candidate
    ON attachment.data_item_id = candidate.candidate_id
WHERE candidate.is_active = 1
ORDER BY
    lastName ASC
LIMIT 92000, 20

注：该查询中LEFT JOIN加了candidate表的非空过滤条件，实际执行时会转为内连接，建议直接写INNER JOIN减少优化器判断开销，语义也更明确。

推荐索引配置 #

• candidate表新增联合覆盖索引：
  CREATE INDEX idx_candidate_active_lastname ON candidate (is_active, last_name, candidate_id, first_name, city, state);
  索引设计逻辑：优先放等值过滤的is_active过滤掉非活跃数据，随后放排序字段last_name直接利用索引有序性避免文件排序，再放关联键candidate_id满足关联需求，最后包含查询需要的所有candidate表字段，实现覆盖索引无需回表。
• attachment表新增联合覆盖索引：
  CREATE INDEX idx_attachment_dataitem ON attachment (data_item_id, attachment_id, title);
  索引设计逻辑：data_item_id是关联键，放在最左匹配关联查询需求，后续包含需要返回的attachment表字段，实现覆盖索引无需回表。

大offset分页优化 #

offset过大查询慢是因为LIMIT 92000,20需要先扫描排序92020条数据再丢弃前92000条，开销极高。建议改为键集分页：
记录上一页最后一条数据的last_name和candidate_id（避免重名导致漏数据），下一页查询时新增条件WHERE ... AND (last_name > 上一页最后一个last_name OR (last_name = 上一页最后一个last_name AND candidate_id > 上一页最后一个candidate_id))，直接用LIMIT 20即可，不需要跳过大量数据。

判断需要加索引的列的核心规则 #

• 优先选择WHERE子句中高频使用的过滤列，等值过滤列优先放在联合索引最左侧，范围过滤列放在等值列之后
• 其次选择JOIN语句的关联列，关联两侧的表都要为关联字段配置索引，避免嵌套循环关联时逐行全表扫描
• 再其次选择ORDER BY、GROUP BY涉及的列，利用索引的有序性避免额外的排序、分组计算开销
• 优先设计联合覆盖索引，将查询需要返回的所有字段都包含在索引中，避免回表查询聚簇索引的额外IO开销
• 避免创建冗余索引，例如已有(a,b,c)的联合索引时，不需要额外创建(a)、(a,b)的索引，除非有单独只用a或a、b过滤的查询场景

内容的提问来源于stack exchange，提问作者Ashish Nishad