MySQL查询优化并不仅仅是给字段加索引，它的本质是让查询优化器选择一条更高效的执行路径。一条SQL语句到达MySQL后，会经过解析、预处理、查询优化器生成多个候选执行计划，再根据统计信息和成本模型选择成本最低的一个。理解这个过程，就能明白为什么有时候加了索引没有效果，为什么EXPLAIN中的type不同性能会相差巨大。我们先从执行计划开始建立诊断思路。
在实际优化过程中，数据库管理员和开发者最关注的指标包括扫描行数、是否使用索引、是否产生临时表、是否发生文件排序等。这些信息都可以通过EXPLAIN命令获得。下面从优化器的决策依据、索引设计、执行计划解读和SQL写法优化四个角度深入分析。
一、查询优化器的决策依据：统计信息与成本模型
MySQL优化器依赖表的统计信息来估算每个执行计划的代价。统计信息包括表的行数、索引基数、字段不同值数量等。如果统计信息过时，优化器可能误判，选择全表扫描而不是索引扫描。例如一张表经过大量增删后，实际行数已经变化，但统计信息没有更新，优化器仍然按照旧数据估算扫描成本。此时可以执行ANALYZE TABLE命令重新收集统计信息，让优化器做出更准确的选择。
成本模型会计算IO成本和CPU成本，例如扫描一行数据的成本、通过索引回表读取完整行的成本。如果查询需要回表的行数过多，即使使用了索引，总成本也可能高于直接全表扫描。这就是为什么优化器有时会放弃索引而选择全表扫描，这并不一定是错误。覆盖索引的出现正是为了解决回表问题，它让查询所需的所有列都包含在索引中，从而避免访问数据行。
-- 更新统计信息 ANALYZE TABLE orders; -- 查看索引基数等信息 SHOW INDEX FROM orders;
通过SHOW INDEX可以查看每个索引的Cardinality值，该值越接近表的行数，说明索引区分度越好。如果Cardinality很低，说明该索引列上大量重复值，优化器可能认为使用该索引不划算。
二、索引设计：联合索引与最左前缀原则
索引不是越多越好，每个索引都会占用额外的磁盘空间，并且在写入数据时需要维护。联合索引的顺序非常关键，MySQL使用最左前缀原则，即查询条件必须从联合索引的最左侧列开始匹配，才能利用索引。例如建立一个联合索引idx_orders_user_time，列顺序为(user_id, create_time)，那么单独使用user_id查询可以走索引，单独使用create_time查询则无法使用该索引。
在设计联合索引时，应该把区分度高、经常作为等值条件的列放在前面，把范围查询或排序的列放在后面。如果查询条件是WHERE user_id = 100 AND create_time > '2024-01-01'，那么(user_id, create_time)的索引可以同时用于过滤和排序。但如果把create_time放在前面，范围查询会导致后续列无法使用。因此联合索引的顺序需要结合实际查询场景来确定。
-- 创建联合索引 CREATE INDEX idx_orders_user_time ON orders(user_id, create_time); -- 可以走索引 SELECT * FROM orders WHERE user_id = 100; -- 无法走该索引，因为最左列缺失 SELECT * FROM orders WHERE create_time > '2024-01-01';
覆盖索引是另一个重要设计手段。如果查询只需要user_id和create_time两列，而索引恰好包含这两列，那么查询可以直接从索引中读取数据，不需要回表。这时EXPLAIN的Extra列会显示Using index。例如SELECT user_id, create_time FROM orders WHERE user_id = 100，如果索引是(user_id, create_time)，就实现了覆盖索引，性能会明显优于需要回表的查询。
三、执行计划解读：识别全表扫描、回表与临时表
EXPLAIN输出的关键列包括type、key、rows和Extra。type表示访问类型，从好到差依次为system、const、eq_ref、ref、range、index、ALL。其中ALL表示全表扫描，通常是最需要优化的信号。range表示范围扫描，ref表示使用非唯一索引查找，const表示主键或唯一索引等值匹配。优化目标应尽量让type达到ref或range以上。
Extra列中的Using filesort和Using temporary是重要警告。Using filesort表示MySQL需要对结果进行额外排序，通常是因为ORDER BY的字段没有合理利用索引顺序。Using temporary表示需要创建临时表来保存中间结果，常见于GROUP BY和DISTINCT操作没有走索引的情况。这两种情况都会增加内存或磁盘开销，是查询性能下降的常见原因。
mysql> EXPLAIN SELECT * FROM orders ORDER BY create_time DESC; +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 1000 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+
上面的执行计划显示type为ALL，没有使用任何索引，并且Extra包含Using filesort。针对这个问题，可以尝试在create_time上创建索引，使排序直接利用索引顺序，从而避免文件排序。同时，应该尽量避免SELECT *，因为需要读取所有列，无法利用覆盖索引，还会增加回表次数。
四、SQL写法优化：避免索引失效与减少数据扫描
很多看似合理的SQL写法会导致索引失效。常见的问题包括：对索引列使用函数或表达式、发生隐式类型转换、LIKE以百分号开头、使用OR连接非索引列等。例如WHERE DATE(create_time) = '2024-01-01'会导致create_time上的索引无法使用，因为索引存储的是原始值，经过函数转换后优化器无法匹配索引。应该改写为范围查询WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
隐式类型转换也是一个容易被忽略的问题。如果字段是字符串类型，而查询条件传入了数字，MySQL会将字段转换成数字进行比较，导致索引失效。例如WHERE phone = 13800138000，如果phone是varchar类型，应该写成WHERE phone = '13800138000'。另外，OR条件如果包含非索引列，整个条件可能退化为全表扫描，此时可以改写为UNION ALL来分别走索引。
-- 索引失效：对索引列使用函数 SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01'; -- 改为范围查询，可以使用索引 SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'; -- 使用OR导致索引失效 SELECT * FROM orders WHERE user_id = 100 OR status = 1; -- 改写为UNION ALL SELECT * FROM orders WHERE user_id = 100 UNION ALL SELECT * FROM orders WHERE status = 1;
深分页优化也是实际开发中常见的问题。当OFFSET值很大时，MySQL需要扫描并丢弃大量行，导致查询变慢。例如LIMIT 100000, 20会先扫描前100020行，再返回最后20行。可以使用延迟关联的思路，先在索引上定位主键，再回表取完整数据，或者使用上一页的最大主键作为游标进行查询，避免大偏移量扫描。
MySQL查询优化需要结合执行计划、索引设计和SQL改写综合判断，而不是孤立地套用规则。统计信息的准确性、联合索引的列顺序、覆盖索引的利用以及避免索引失效，都是提升查询性能的关键环节。通过不断练习EXPLAIN分析，开发者能够更准确地定位瓶颈，写出更高效的SQL语句。