慢查询优化
建索引的几大原则
-
最左前缀匹配原则。MySQL 从左到右遇到范围查询
(>、<、between、like)后停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4查询语句，如果建立(a, b, c, d)顺序的索引，d字段是用不到索引的；如果建立(a, b, d, c)顺序的索引，则所有字段都可以用到索引。 -
= 和 in 可以乱序。比如
a = 1 and b = 2 and c = 3查询语句，建立(a, b, c)索引可以任意顺序。MySQL 查询优化器可以优化成索引可以识别的形式。 -
选择区分度高的列作为索引。区分度的公式是
count(distinct col)/count(*)，比例越大，说明重复的值越少，扫描的记录数越少，唯一索引的区分度为 1。对于性别、状态等字段在大数据面前区分度就是 0。使用场景不同，这个值也很难确定，一般需要 join 的字段我们 (原作者) 都要求是 0.1 以上，即平均 1 条扫描 10 条记录。 -
尽量扩展索引，而不是新建索引。比如表中有
a字段的索引，新增(a, b)的索引，直接修改原来的索引即可。当前修改索引，只能先删除索引再建立索引？。
慢查询优化基本步骤
- 先运行看看是否真的很慢，注意设置 SQL_NO_CACHE
- where 条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的 where 都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
- explain 查看执行计划，是否与 1 预期一致（从锁定记录较少的表开始查询）
- order by limit 形式的 sql 语句让排序的表优先查
- 解业务方使用场景
- 加索引时参照建索引的几大原则
- 观察结果，不符合预期继续从 0 分析
关联查询优化
- 左连接时，右表/被驱动表要建立索引，因此要根据连接列从右表中获得数据。
- 内连接时，如果有一个表有索引，那么该表被设置为被驱动表；否则，根据表大小等因素确定驱动与被驱动表。
- 最好使用小表驱动大表，即对大表的数据建立索引。
- 大表和小表的定义为，两个表按照各自条件过滤，过滤之后，计算参与 join 连接字段的数据量。数据量小的为小表。
straight_join关键字可以让 MySQL 使用连接顺序进行执行查询。- join 字段，数据类型保持绝对一致，否则不走索引。
- 尽量使用连接查询代替子查询。
子查询优化
子查询效率不高。
- 执行子查询时，MySQL 需要为内层查询语句的查询结果
建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。 - 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都
不会存在索引，所以查询性能会受到一定的影响。 - 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
结论：尽量不要使用
NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
排序优化
MySQL 支持两种排序方式，分别是 Filesort 排序和 index 排序。
index排序，索引可以保证数据的有序性，不需要再排序，效率高。Filesort排序，一般在内存中进行排序，占用 CPU 较多。如果需要排序的结果较多，会产生临时文件 IO 到磁盘中，效率更低。
- 在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用
Filesort排序。 - 去掉
Filesort不一定比没去掉Filesort快，需要根据数据量和 MySQL 实际选择判断。 - 尽量使用
Index完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。 - 无法使用
Index时，需要对Filesort方式进行调优。 - 多个索引同时存在，mysql 自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的。
- 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME);
SELECT * FROM student ORDER BY age,classid; # 需要回表，没有 limit 限制，使用 Filesort
SELECT age,classid,name,id FROM student ORDER BY age,classid; # 使用索引，直接返回
SELECT * FROM student ORDER BY age,classid LIMIT 10; # 使用索引INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则 order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME;
优化思路：
- 建立索引来去掉 Filesort，中等。
CREATE INDEX idx_age_name ON student(age,NAME); - 没去掉 Filesort，尽量让 where 的过滤条件和排序使用上索引，最快。
CREATE INDEX idx_age_stuno_name ON student (age,stuno);
或CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME); - 原因：
排序是在 where 过滤后执行的。如果条件过滤了大部分数据，剩下的数据使用 Filesort 排序并不消耗性能。索引优化了排序，但是提升的性能有限。这里 stuno 字段数据量较多，建立该字段的索引性价比更高。
Filesort 算法
- MySQL4.1 前使用
双路排序。
先将 order by 对应的字段加载到内存中，对字段进行排序；排序后，将需要的其他字段加载到内存中。 - MySQL 当前使用
单路排序。
一次将所有需要的列加载到内存中，按照 order by 列在内存中排序；排序后，直接输出。
避免了二次读取数据，随机 IO 变成顺序 IO，但是会消耗更多的空间。
如果sort_buffer小于一次加载的容量，那么可能会产生临时文件保存到磁盘，变成多次 IO 操作，得不偿失。
Filesort 算法改进
- 尝试提高
sort_buffer_size。
排序时 buffer 的最大容量。 - 尝试提高
max_length_for_sort_data
大于该值，使用双路排序；小于该值，使用单路排序。 - order by 时
select *是一个大忌，最好只查询需要的字段。
GROUP BY 优化
group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。- group by
先排序再分组，遵照索引建的最佳左前缀法则。 - 当无法使用索引列，增大
max_length_for_sort_data和sort_buffer_size参数的设置 where效率高于having，能写在where限定的条件就不要写在having中了- 减少使用
order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。 - 包含了
order by、group by、distinct这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。 - MYSQL 8.0 不再进行 groupby 排序。
优化分页查询
当偏移量很大时，如 limit 100000,10 取第 100001-100010 条记录，mysql 会取出 100010 条记录然后将前 100000 条记录丢弃，这无疑是一种巨大的性能浪费。
SELECT * FROM student LIMIT 2000000, 10;
- 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a WHERE t.id = a.id; - 该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
优先考虑覆盖索引
理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二：非聚簇复合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。
简单说就是，索引列+主键包含 SELECT 到 FROM 之间查询的列。 覆盖索引，直接返回，不需要回表：
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME);
SELECT age,classid,name,id FROM student ORDER BY age,classid;
CREATE INDEX idx_age_name ON student (age,NAME);
EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';- 避免 Innodb 表进行索引的二次查询（回表）
- 可以把随机 IO 变成顺序 IO 加快查询效率
- 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。
覆盖索引的第二个用处，延迟关联：
使用覆盖索引先获得需要查询的主键，之后再在主键索引中查询数据。
索引下推
查询过程
select id from test where k=5;
普通索引查到 k=5 后，继续查找到不满足 k=5 的记录。唯一索引查到 k=5 后，直接停止。但普通索引和唯一性索引的查询，性能差距基本不大。
更新过程
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown） 的过程中，也会执行 merge 操作。
如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
什么条件下可以使用 change buffer 呢？
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违法唯一性约束。比如插入 k=4 的情况，需要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。普通索引可以使用。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小可以通过 innodb_change_buffer_max_size 来动态设置。50 表示 change buffer 的大小最多占用 buffer pool 的 50%。
插入新纪录(4, 400)的话，InnoDB 的处理流程
- 更新的目标页在内存中。
唯一索引，找到 3-5 的位置，判断冲突，插入数据；
普通索引，找到 3-5 的位置，插入数据。 - 更新的目标页不在内存中。
唯一索引，数据页读入内存中，判断冲突与插入数据；
普通索引，将更新记录在change buffer中。
因此，如果有大量数据插入操作时，将唯一索引更改为普通索引效果会好些，降低了数据页读入内存的 IO 操作。
change buffer 的使用场景
- 只用于普通索引中，不适用于唯一索引。
- 对于写多读少情况，
change buffer的效果比较好，一般是账单类、日志类系统。 - 对于读多情况，由于访问数据页，会立即触发 merge 操作。这样随机 IO 次数不会减少，还需要对
change buffer进行维护，因此不适合change buffer，将其关闭。
其他查询优化策略
EXIST 和 IN 的区分
- A 表数据多，使用
in
SELECT * FROM A WHERE cc IN (SELECT cc FROM B) - A 表数据小，使用
exist
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)
**COUNT(*) 与 COUNT(1) **
- 两者时间差不多。
MyISAM引擎，O(1)的复杂度，存在 meta 信息存储了row_count值。InnoDB引擎，O(n)的复杂度。CONUT(具体字段)时，InnoDB引擎最好采用二级索引。因为主键索引是聚集索引，一个数据页中存储的数据较少，需要查看的数据页会较多。COUNT(*)与COUNT(1)，InnoDB引擎会自动采用占用空间key_len更小的二级索引来统计，没有时才会选择主键索引统计。
**不建议使用 SELECT(*) **
- MySQL 解析过程中，会通过查询数据字典将
*转换为所有的列名，耗费资源和时间 - 无法使用覆盖索引
- 后续增加数据字段后，
SELECT(*)的结果会不同。
LIMIT 1 对优化的影响
针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。如果数据表已经对字段建立了唯一索引，就不需要了。
慢查询案例
复杂语句写法
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';- 先运行一下，53 条记录 1.87 秒，又没有用聚合语句，比较慢
53 rows in set (1.87 sec)1.explain
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+简述一下执行计划，首先 mysql 根据 idx_last_upd_date 索引扫描 cm_log 表获得 379 条记录；然后查表扫描了 63727 条记录，分为两部分，derived 表示构造表，也就是不存在的表，可以简单理解成是一个语句形成的结果集，后面的数字表示语句的 ID。derived2 表示的是 ID = 2 的查询构造了虚拟表，并且返回了 63727 条记录。我们再来看看 ID = 2 的语句究竟做了写什么返回了这么大量的数据，首先全表扫描 employee 表 13317 条记录，然后根据索引 emp_certificate_empid 关联 emp_certificate 表，rows = 1 表示，每个关联都只锁定了一条记录，效率比较高。获得后，再和 cm_log 的 379 条记录根据规则关联。从执行过程上可以看出返回了太多的数据，返回的数据绝大部分 cm_log 都用不到，因为 cm_log 只锁定了 379 条记录。
如何优化呢？可以看到我们在运行完后还是要和 cm_log 做 join, 那么我们能不能之前和 cm_log 做 join 呢？仔细分析语句不难发现，其基本思想是如果 cm_log 的 ref_table 是 EmpCertificate 就关联 emp_certificate 表，如果 ref_table 是 Employee 就关联 employee 表，我们完全可以拆成两部分，并用 union 连接起来，注意这里用 union，而不用 union all 是因为原语句有“distinct”来得到唯一的记录，而 union 恰好具备了这种功能。如果原语句中没有 distinct 不需要去重，我们就可以直接使用 union all 了，因为使用 union 需要去重的动作，会影响 SQL 性能。
优化过的语句如下：
select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0-
不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致
-
现有索引可以满足，不需要建索引
-
用改造后的语句实验一下，只需要 10ms 降低了近 200 倍！
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)明确应用场景
举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。
select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);- 先看看运行多长时间, 951 条数据 6.22 秒，真的很慢。
951 rows in set (6.22 sec)- 先 explain，rows 达到了 361 万，type = ALL 表明是全表扫描。
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
-
所有字段都应用查询返回记录数，因为是单表查询 0 已经做过了 951 条。
-
让 explain 的 rows 尽量逼近 951。
看一下 accurate_result = 1 的记录数：
select count(*),accurate_result from stage_poi group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
| 1023 | -1 |
| 2114655 | 0 |
| 972815 | 1 |
+----------+-----------------+我们看到 accurate_result 这个字段的区分度非常低，整个表只有-1, 0, 1 三个值，加上索引也无法锁定特别少量的数据。
再看一下 sync_status 字段的情况：
select count(*),sync_status from stage_poi group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
| 3080 | 0 |
| 3085413 | 3 |
+----------+-------------+同样的区分度也很低，根据理论，也不适合建立索引。
问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当 sync_status 0、3 分布的很平均，那么锁定记录也是百万级别的。
-
找业务方去沟通，看看使用场景。业务方是这么来使用这个 SQL 语句的，每隔五分钟会扫描符合条件的数据，处理完成后把 sync_status 这个字段变成 1, 五分钟符合条件的记录数并不会太多，1000 个左右。了解了业务方的使用场景后，优化这个 SQL 就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。
-
根据建立索引规则，使用如下语句建立索引
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);- 观察预期结果, 发现只需要 200ms，快了 30 多倍。
952 rows in set (0.20 sec)我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把 where 条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第 4 步调差 SQL 的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。
无法优化的语句
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
order by
c.created_time desc limit 0 ,
10;还是几个步骤。
- 先看语句运行多长时间，10 条记录用了 13 秒，已经不可忍受。
10 rows in set (13.06 sec)- explain
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using Filesort |
| 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index |
| 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+从执行计划上看，mysql 先查 org_emp_info 表扫描 8849 记录，再用索引 idx_userid_status 关联 branch_user 表，再用索引 idx_branch_id 关联 contact_branch 表，最后主键关联 contact 表。
rows 返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有 order by + limit 组合，会不会是排序量太大搞的？于是我们简化 SQL，去掉后面的 order by 和 limit，看看到底用了多少记录来排序。
select
count(*)
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
+----------+
| count(*) |
+----------+
| 778878 |
+----------+
1 row in set (5.19 sec)发现排序之前居然锁定了 778878 条记录，如果针对 70 万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据 contact 的 created_time 排序，再来 join 会不会比较快呢？
于是改造成下面的语句，也可以用 straight_join 来优化：
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;验证一下效果预计在 1ms 内，提升了 13000 多倍！
10 rows in set (0.00 sec)本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再 join 和先 join 再排序理论上开销是一样的，为何提升这么多是因为有一个 limit！大致执行过程是：mysql 先按索引排序得到前 10 条记录，然后再去 join 过滤，当发现不够 10 条的时候，再次去 10 条，再次 join，这显然在内层 join 过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql 还傻乎乎的每次取 10 条，几乎遍历了这个数据表！
用不同参数的 SQL 试验下：
select
sql_no_cache c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 2875
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;
Empty set (2 min 18.99 sec)2 min 18.99 sec！比之前的情况还糟糕很多。由于 mysql 的 nested loop 机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。
通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于 SQL 用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过 SQL 优化，第二：不要过于自信，只针对具体 case 来优化，而忽略了更复杂的情况。
慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过 1000 行，涉及到 16 个表 join 的“垃圾 SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过 varchar 等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。