摘要

本文深入解析MySQL查询优化的核心方法，包括查询执行过程、索引失效场景分析、EXPLAIN工具详解，以及真实业务场景下的SQL调优案例。适合后端工程师和DBA快速掌握性能优化关键点。

SQL优化并非DBA的专属，作为一名合格的码农特别是想往架构师迈进的“程序猿”，就不仅仅是要会写SQL，更要深知其原理，懂得如何进行SQL优化。因为当在处理千万级数据时，一条看似简单的SELECT语句，一个不走索引的查询能把整个系统拖垮。

一、MySQL查询执行过程：一条SQL经历了什么

理解执行过程，才能知道优化从哪里下手。当客户端发送一条SQL语句到MySQL服务器时，执行流程大致是这样的：

1.1 连接层

客户端发起连接请求，连接器负责身份验证和权限获取。连接建立后，权限信息会缓存到当前连接中，修改权限不会立即生效，需要重新连接。

经常被忽略的点： 长连接占用内存累积问题。MySQL连接复用时，临时内存不会主动释放，大量长连接可能导致OOM。可以通过定期断开重连，或在MySQL5.7+使用mysql_reset_connection来解决。

1.2 查询缓存层（MySQL 8.0已移除）

MySQL 8.0之前有查询缓存，但由于命中率低、锁竞争严重，官方在8.0中移除了这个功能。如果你还在用老版本，建议直接在配置中关闭：query_cache_type = 0。

1.3 分析器层

分析器做两件事：词法分析和语法分析。

词法分析识别SQL中的字符串是什么——"SELECT"是关键字，"users"是表名，"id"是列名；语法分析判断SQL语句是否符合语法规则，不符合直接报错。

这一层不会消耗太多资源，但SQL写得越复杂，解析成本越高。

1.4 优化器层（最关键）

优化器决定怎么执行SQL，这是性能优化的核心战场。优化器的工作包括：

- 选择使用哪个索引（如果有多个索引可选）

- 决定表的连接顺序（多表JOIN时）

- 选择JOIN算法（Nested Loop、Hash Join等

为什么优化器有时会选错索引？

优化器基于统计信息做决策，如果统计信息过时（比如大量数据变更后没更新），或者估算成本出现偏差，就会选错执行计划。这就是为什么有时候FORCEINDEX能救急——虽然不推荐长期依赖。

1.5 执行器层

执行器调用存储引擎接口，实际执行查询。执行时会检查权限（如果有视图或存储过程），然后根据执行计划逐行获取数据。存储引擎层是插件式的，InnoDB、MyISAM、Memory等引擎在这一层工作。实际项目中，多数使用InnoDB（事务、行锁、并发、安全），后面提到的索引特性也主要针对InnoDB。

二、SQL语句核心优化策略

理解SQL执行过程后，可落地以下优化策略，从索引设计、字段规范、语句写法多维度优化SQL性能。

2.1 为 WHERE 及 ORDER BY 涉及的列上建立索引

大表高频查询场景下，优先给 WHERE 过滤字段 + ORDER BY 排序字段建立联合索引；小表、结果集过大、低选择性字段无需刻意建索引，允许全表扫描。

索引数量约束

OLTP业务表索引数量建议 3~5 个，复杂业务不超过 6~7 个。
超高并发写入表，尽量压缩索引数量。

2.2 业务尽量不用 NULL，用默认值（0、-1、空字符串）替代

应尽量避免在 WHERE 子句中对字段进行 NULL 值判断。创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0、-1 作为默认值。

默认值替代NULL的四大原因：

索引不稳定：IS NULL / IS NOT NULL 是否走索引不确定，受 MySQL 版本、数据分布、成本计算影响，不是固定失效。
优化器易放弃索引：<>、IS NULL、IS NOT NULL 通常扫描范围大，优化器判定索引成本高于全表扫描，主动放弃索引。
NULL 会产生隐式转换：NULL 参与运算、判断极易触发隐式转换，直接索引失效。
业务可读性差 + 存储开销大：NULL 无业务含义，且 InnoDB 中 NULL 需要额外存储空间；默认值语义清晰、查询更稳定。

2.3 只查需要的列

SELECT * 是性能杀手之一。最好不要使用返回所有：SELECT * FROM t ，用具体的字段列表代替 *，不要返回用不到的任何字段。

SELECT * FROM orders WHERE user_id = 10086;

// 推荐写法
SELECT order_id, amount, create_time
FROM orders
WHERE user_id = 10086;

SELECT * 会带来的问题

增加网络传输开销
增加内存消耗（应用层和数据库层）
可能触发不必要的回表操作（索引覆盖失效）
影响一些数据库的查询优化判断

2.4 慎用子查询，优先 JOIN

MySQL对子查询的优化能力有限，特别是IN子查询。MySQL 8.0对子查询优化升级，部分场景性能接近JOIN，生产环境建议使用EXPLAIN验证执行计划。

// 性能较差
SELECT * FROM orders
WHERE user_id IN (
    SELECT user_id
    FROM users
    WHERE vip_level = 5
);

// 推荐写法
SELECT o.*
FROM orders o
INNER JOIN users u
ON o.user_id = u.user_id
WHERE u.vip_level = 5;

2.5 避免在索引列上做运算

对索引列进行函数运算、数学运算、类型转换，都会导致索引失效，优化器无法解析计算后的值，只能执行全表扫描。

索引失效（列运算）

SELECT * FROM orders WHERE DATE(create_time) = '2024-03-15';

索引有效（范围查询）

SELECT * FROM orders 
WHERE create_time >= '2024-03-15 00:00:00'
  AND create_time <= '2024-03-15 23:59:59';

2.6 LIKE 查询的正确姿势

索引失效，全表扫描

SELECT * FROM users
WHERE name LIKE '%张%';

索引有效，范围扫描

SELECT * FROM users
WHERE name LIKE '张%';

前导通配符 %xxx 会让索引完全失效。如果业务确实需要模糊搜索，考虑：

使用覆盖索引减少回表
引入 Elasticsearch 等搜索引擎
MySQL 5.6+ 的全文索引（对中文支持有限）

2.7 OR条件优化

OR连接不同字段时，MySQL大概率无法有效利用索引，可改写为UNION ALL提升性能。UNION ALL不去重、无排序开销；UNION会自动去重，额外消耗性能。

可能全表扫描

SELECT * FROM orders WHERE user_id = 10086 OR amount > 1000;

优化写法（UNION ALL）

SELECT * FROM orders WHERE user_id = 10086
UNION ALL
SELECT * FROM orders WHERE amount > 1000;

2.8 分页优化

深度分页是常见性能瓶颈，偏移量越大，查询速度越慢，提供延迟关联、游标分页两种优化方案。

深度分页，越往后越慢

SELECT * FROM orders
ORDER BY id
LIMIT 1000000, 20;

优化方案1：延迟关联

SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id
    FROM orders
    ORDER BY id
    LIMIT 1000000, 20
) t
ON o.id = t.id;

优化方案2：游标分页（需要业务配合）

SELECT *
FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 20;

延迟关联的原理是：

先通过子查询（覆盖索引）快速定位 ID，再回表查详细数据。
游标分页性能最好，但需要记住上一次的 ID，不适合随机跳页场景。

三、索引失效的常见场景

索引创建完成不代表一定会生效，以下场景会导致索引失效、触发全表扫描，开发过程需严格规避。

3.1 对索引列使用函数或运算

前面提到过，在索引列进行数学运算、函数调用、类型转换，直接索引失效。需将运算条件转移至查询值侧。

索引失效

WHERE YEAR(create_time) = 2024

WHERE id + 1 = 100

WHERE CAST(user_id AS CHAR) = '10086'

索引有效

WHERE create_time >= '2024-01-01'
  AND create_time < '2025-01-01'

WHERE id = 99

WHERE user_id = 10086

3.2 隐式类型转换

字段类型与查询条件类型不匹配，MySQL会自动转换类型，等效于在索引列做运算，造成索引失效，ORM框架中极易出现该问题。

假设 user_id 是 VARCHAR 类型，而查询条件用了数字：

索引失效

SELECT *
FROM users
WHERE user_id = 10086;

索引有效

SELECT *
FROM users
WHERE user_id = '10086';

3.3 组合索引的“最左前缀原则”

组合索引的使用必须遵循最左前缀原则。

假设索引为 (a, b, c)：

索引有效

WHERE a = 1

WHERE a = 1 AND b = 2

WHERE a = 1 AND b = 2 AND c = 3

索引失效（缺少最左列 a）

WHERE b = 2

WHERE c = 3

部分有效（只用到了 a 列）

WHERE a = 1 AND c = 3

更细节的规则

范围查询（>、<、BETWEEN）后的索引列无法使用
但是 IN 查询不受此限制

索引只用到 a 列

WHERE a = 1 AND b > 2 AND c = 3

索引全部有效

WHERE a = 1 AND b IN (2, 3) AND c = 4

3.4 != 或 <> 操作符

大概率全表扫描

WHERE status != 1

WHERE status <> 1

!=会让优化器放弃索引，因为需要扫描的数据量可能超过一定阈值（通常是表的 30%）。如果业务确实需要这种查询，考虑：

改写为 IN 查询
使用覆盖索引
建立反向索引（如果值域较小）

3.5 IS NULL 和 IS NOT NULL

可能让索引失效

WHERE name IS NULL

WHERE name IS NOT NULL

是否能用索引取决于数据分布。如果 NULL 值很少，IS NULL 可能走索引；如果 NULL 值很多，优化器会判断全表扫描更快。

3.6 NOT IN、NOT EXISTS

索引失效风险高

WHERE id NOT IN (1, 2, 3)

WHERE NOT EXISTS (
    SELECT 1
    FROM ...
)

改写为 LEFT JOIN + IS NULL

SELECT o.*
FROM orders o
LEFT JOIN blacklist b
ON o.user_id = b.user_id
WHERE b.user_id IS NULL;

四、EXPLAIN详解：SQL调优的核心工具

explain关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句：explain + SQL语句。表头信息如下：

explain各个字段代表的意思

id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type ：查询类型或者是其他操作类型
table ：正在访问哪个表
partitions ：匹配的分区
type ：访问的类型
possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key ：实际使用到的索引，如果为NULL，则没有使用索引
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered ：查询的表行占表的百分比
Extra ：包含不适合在其它列中显示但十分重要的额外信息

4.1 ID 字段

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。三种情况：

【1】id 相同：执行顺序由上而下；

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';

【2】id 不同： 如果是子查询，id 序号会递增，id 越大优先级越高，越先被执行；

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));

【3】id 相同不同同时存在： id 如果相同，可以认为是一组，由上往下执行；在所有组里 id 越大，优先级越高，越先执行；

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;

4.2 select_type 字段

主要用于区别普通查询、联合查询、子查询等的复杂程度。
【1】SIMPLE： 简单的 select 查询，查询中不包含子查询或者 UNION；
【2】PRIMARY： 查询中若包含任何复杂的自查询，最外层查询为 PRIMARY；

【3】SUBQUERY： 在 SELECT 或 WHERE 中包含子查询；
【4】UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行
【5】DERIVED： 在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放进临时表；

【6】UNION： 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 FROM 子句的子查询，则外层SELECT 将被标记为 DERIVED；
【7】UNION RESULT： 从 UNION表中获取结果的 SELECT；

4.3 table字段

显示这行数据是关于那张表

4.4 type字段

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

【1】NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以你的查询速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那MySQL比你更知道啊！当你要查询最大值或者最小值时，MySQL会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。

NULL的前提是你已经建立了索引。

【2】SYSTEM

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

【3】const

表示通过索引一次就能找到，const用于比较 primary和 unique索引。因为只匹配一行数据，所以很快；

简单来说，const是直接按主键或唯一键读取。

【4】eq_ref

用于联表查询的情况，按联表的主键或唯一键联合查询。多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。

【5】ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

【6】ref_or_null 类似ref，但是可以搜索值为NULL的行

【7】index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

【8】range

只检索给定范围的行，使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为只需开始索引的某一点，而结束另一点，不用扫描全部索引；

【9】index： Full Index Scan，index与 ALL区别为 index类型只遍历索引树，索引文件通常比数据文件小。index从索引中读取，而All是从硬盘读取；

【10】ALL

从磁盘中读取；如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

4.5 possible_keys字段

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

4.6 key字段

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

4.7 ref字段

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

4.8 rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

4.9 Extra字段

【1】Using index

表示相应的 select操作中使用了覆盖索引（convering index），避免访问了表的数据行，效率不错！

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

【2】Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序 order by和分组查询 group by。

【3】Using fileSort

表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序。

MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上，一般可以通过合适的索引来减少或者避免。（出现表示不好）

上面提到的常见情况，SQL语句通常写成这样select * from a where type = 5 order by id，这类语句一般会产生Using filesort这个选项，即使你在type和id上分别添加了索引。我们想一下它的工作过程，先根据type的索引从所有数据信息中挑选出满足type = 5条件的，然后根据id列的索引信息对挑选的数据进行排序，所以产生了Using filesort选项。可以通过联合索引解决这个问题，即在type, id两列上建立一个联合索引。

【4】Using where：

查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra 中为 Using where。

所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。比如通过：CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。

【5】using where,using index

① 查询的列被索引覆盖，并且 where筛选条件是索引列之一但是不是索引的前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据；

② 查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查询到符合条件的数据

【6】Using index condition

① 查询的列不全在索引中，where条件中是一个前导列的范围

② 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

【7】NULL（既没有Using index，也没有Using where Using index，也没有using where）

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

【8】Using join buffer

使用了连接缓存。

1.1 小表驱动大表
在表连接过程中。一般选择小表作为驱动表，大表作为被驱动表。
驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引，则可以走索引。如果没有建立索引则也需要全表扫描。
1.2 两张表连接的情况
被驱动表的连接字段有索引：主键索引
对于驱动表中的每一条数据，到被驱动表的聚簇索引上寻找其对应的数据。
被驱动表的连接字段有索引：二级索引
对于驱动表上的每一条数据，到被驱动表的二次索引上寻找其对应的数据id，然后再根据数据id到聚簇索引上寻找对应的数据。
被驱动表的连接字段没有索引
对于驱动表上的每一条数据，都要到被驱动表上进行一次全表遍历，找到对应的数据。
1.3 join buffer的作用
就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描，所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。

【9】impossible where

where子句总是false，不能用来获取任何元素。即筛选条件没能筛选出任何数据。

【10】select tables optimized away

在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX操作。

【11】distinct

优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。

五、真实案例：慢SQL优化实战

最近在做的是一个学生体测相关的项目，全国各地，一个学生又涉及多个体测项目，所以，部分表数据就很容易堆积到百万甚至千万的量级，慢查、超时就随之涌现...以下是几个优化的案例。

案例一：find_in_set

业务中有这样一个场景，上级需要看到其所有下级的数据，如省及单位需要能查看其下所有市区县的学校。为了方便，之前将学生所隶属的学校以及其上级省市区单位，记录在一个字段sponsor_ids中，然后通过find_in_set查询，如下：

SELECT grade FROM enroll 
WHERE match_id = 60 AND FIND_IN_SET(36, sponsor_ids) 
GROUP BY grade ORDER BY grade

这样设计，代码写起来很方便，但数据量级大是，查询效率极其低下，因为find_in_set无法使用索引。

优化：事先查询所属下级，然后通过in查询使用索引

SELECT
	s.id
FROM
( SELECT * FROM sponsor WHERE `status` = 1 AND  superior_id IS NOT NULL order by level asc ) s,
  ( SELECT @pid := #{sponsorId} ) pd
WHERE
FIND_IN_SET( superior_id, @pid ) != 0
AND @pid := concat( @pid, ',', id )

SELECT grade FROM enroll 
WHERE match_id = 1 AND  sponsor_id in (1) 
GROUP BY grade ORDER BY grade

虽然在查询下级机构时，也使用了find_in_set，但机构表数据量有限，全表扫也无太大压力，而enroll报名表使用in查询后，效率明显提升，由几秒提升到几十毫秒。

案例二：联合索引

还有一些其他情况，比如：

SELECT sponsor_id, count(sponsor_id) AS sponsor_count FROM enroll 
WHERE match_id = 60 
GROUP BY sponsor_id

这句sql，是用于统计某次体测活动中，各个学校的报名人数。设计表时在match_id，sponsor_id上，分别建立的索引，报名人数不多时，效率还可以。但报名人数几十万之后，发现查询效率明显下降，需要几秒钟。explain分析如下：

从上述的分析可以看出，查询使用了索引，但只用到了match_id这一个索引，而extra中显示了using temporary，即使用了中间表进行分组，并未用到索引，数据量达到一定量级后，中间表也会很大，效率自然也就降低了。

为此，针对该查询，建立了match_id和sponsor_id的联合索引，explain发现，不在使用中间表，实际查询效率也明显提升，大概几百毫秒。（使用到覆盖索引，不需要回表查询）

案例三：深分页拖垮数据库

SELECT * FROM enroll_result 
WHERE status = 1
ORDER BY id
LIMIT 100000, 20;

执行分析：

type为index，大量扫描前置偏移量数据，偏移量越大性能越差。

优化方案（延迟关联）：

SELECT o.* FROM enroll_result o 
INNER JOIN ( 
    SELECT id FROM enroll_result
    WHERE status = 1
    ORDER BY id
    LIMIT 100000, 20
) t ON o.id = t.id;

优化效果：

子查询通过覆盖索引快速筛选ID，仅20次回表查询，耗时从30秒降至500ms。

案例四：批量插入与更新（避免循环单条插入）

批量操作，是业务中很常见的，比如批量导入学生，简单粗暴的一种做法就是，for循环，然后在循环中insert，如：

for (int i = 0; i < 50000; i++){
  Student student = new Student("云端行笔" + i,24,"北京市" + i,i + "号");
  studentMapper.insert(student);
}

<insert id="add" parameterType="com.peng.Student">
  INSERT INTO TEST(ID,Student) VALUES(#{id},#{student});
</insert>

当插入百八十条数据时，不会觉得效有多低，但当插入上万条数据时，循环插入可能需要一两分钟甚至更久，这就无法忍受了。正确的打开方式：

<insert id="batchAdd" parameterType="java.util.List">
  INSERT INTO TEST(ID,Student)
VALUES
  <foreach collection="list" item="item" index="index" separator="," >
    (#{item.id},#{item.student})
    </foreach>
</insert>

案例五：无能为力的SQL

业务中有这样一个场景：学生参与体测活动，需要报名，因此，除了student学生表之外，还有一张enroll报名表，用于记录学生在多个体测活动中的报名状态。业务中需要统计所有未报名的学校，思路很简单，就是学生表和报名表取差集，然后统计差集中有哪些学校，SQL如下：

select distinct(s.sponsor_id) from student s
LEFT JOIN enroll e on e.student_id = s.id
where s.status = 1 and e.id is null

问题在于，一个地区会有几百万甚至上千万的学生，如此庞大的两张表取交集，太慢了.，优化好久也没思路...（欢迎评论区指点迷津）。SQL无力了，最终选择，将未报名的学校数据提前统计计算，放在缓存之中，有学生状态或者报名状态发生变更，则去更新。该思路也是类似于数据平台，千万级别的数据，实时统计，肯定会有效率问题，因此，往往会在数据服务中非实时计算。

六、常见问题解答（FAQ）

Q1：如何查看慢查询日志？

MySQL默认关闭慢查询日志，需要手动开启：

 -- 查看配置                                                                   

  SHOW VARIABLES LIKE 'slow_query%';                                            

  SHOW VARIABLES LIKE 'long_query_time';                                                          

  -- 开启慢查询日志                                                             

  SET GLOBAL slow_query_log = 'ON';                                             

  SET GLOBAL long_query_time = 1;  -- 超过1秒记录                               

  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Q2：索引是不是越多越好？

不是。索引的代价：

- 增加存储空间

- 降低INSERT、UPDATE、DELETE性能（需要维护索引）

- 增加优化器的选择成本

经验法则：单表索引数量控制在5个以内，组合索引优先于多个单列索引。

Q3：为什么加了索引还是慢？

检查以下几点：

1. 索引是否真的被使用（用EXPLAIN验证）

2. 是否存在索引失效场景

3. 数据量是否太大，需要分库分表

4. 是否有锁等待（用SHOW PROCESSLIST检查）

5. 硬件是否成为瓶颈

Q4：EXPLAIN的rows准确吗？

rows是优化器的估算值，基于统计信息。如果统计信息过时，估算可能偏差很大。定期执行ANALYZE TABLE更新统计信息，可以提高估算准确度。

Q5：什么时候该用FORCE INDEX？

不推荐常规使用。FORCEINDEX会绑死执行计划，当数据分布变化后可能适得其反。只在以下场景考虑：

- 优化器明确选错索引

- 紧急修复，临时方案

- 统计信息无法及时更新

总结

MySQL优化不是玄学，而是基于执行原理和数据分析的科学。核心思路可以归纳为三点：

理解执行过程：知道SQL每一步在做什么，才能找到瓶颈

用好分析工具：EXPLAIN是SQL调优的透视镜，读懂它的输出

建立索引思维：不是建了索引就能用，理解索引失效场景，设计合理的组合索引

最后，优化没有银弹。每一条"优化规则"都有适用场景，在生产环境中永远用EXPLAIN验证实际效果，而不是盲目套用公式。定期审查慢查询日志，把性能问题消灭在萌芽阶段，比事后救火要轻松得多。

分享

MySQL查询优化完全指南：从执行原理到实战调优技巧

摘要

一、MySQL查询执行过程：一条SQL经历了什么

1.1 连接层

1.2 查询缓存层（MySQL 8.0已移除）

1.3 分析器层

1.4 优化器层（最关键）

1.5 执行器层

二、SQL语句核心优化策略

2.1 为 WHERE 及 ORDER BY 涉及的列上建立索引

2.2 业务尽量不用 NULL，用默认值（0、-1、空字符串）替代

2.3 只查需要的列

2.4 慎用子查询，优先 JOIN

2.5 避免在索引列上做运算

2.6 LIKE 查询的正确姿势

2.7 OR条件优化

2.8 分页优化

三、索引失效的常见场景

3.1 对索引列使用函数或运算

索引失效

索引有效

3.2 隐式类型转换

索引失效

3.3 组合索引的“最左前缀原则”

索引有效

索引失效（缺少最左列 a）

更细节的规则

索引只用到 a 列

索引全部有效

3.4 != 或 <> 操作符

3.5 IS NULL 和 IS NOT NULL

3.6 NOT IN、NOT EXISTS

四、EXPLAIN详解：SQL调优的核心工具

4.1 ID 字段

4.2 select_type 字段

4.3 table字段

4.4 type字段

4.5 possible_keys字段

4.6 key字段

4.7 ref字段

4.8 rows字段和Filter字段

4.9 Extra字段

五、真实案例：慢SQL优化实战

案例一：find_in_set

案例二：联合索引

案例三：深分页拖垮数据库

案例四：批量插入与更新（避免循环单条插入）

案例五：无能为力的SQL

六、常见问题解答（FAQ）

Q1：如何查看慢查询日志？

Q2：索引是不是越多越好？

Q3：为什么加了索引还是慢？

Q4：EXPLAIN的rows准确吗？

Q5：什么时候该用FORCE INDEX？

总结

评论