大厂实践 - 美团: MySQL索引原理及慢查询优化

大厂实践 - 美团: MySQL索引原理及慢查询优化

背景

MySQL 凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通 MySQL”、“SQL 语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

本人从 2013 年 7 月份起，一直在美团核心业务系统部做慢查询的优化工作，共计十余个系统，累计解决和积累了上百个慢查询案例。随着业务的复杂性提升，遇到的问题千奇百怪，五花八门，匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。

一个慢查询引发的思考

select
   count(*) 
from
   task 
where
   status=2 
   and operator_id=20839 
   and operate_time>1371169729 
   and operate_time<1371174603 
   and type=2;

系统使用者反应有一个功能越来越慢，于是工程师找到了上面的 SQL。并且兴致冲冲的找到了我，“这个 SQL 需要优化，给我把每个字段都加上索引”。我很惊讶，问道：“为什么需要每个字段都加上索引？”“把查询的字段都加上索引会更快”，工程师信心满满。

“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以 operate_time 需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。

多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。

慢查询优化

关于 MySQL 索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：

建索引的几大原则

1. 最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>​、<​、between​、like​)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4​ 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。
2. =和 in 可以乱序，比如 a = 1 and b = 2 and c = 3​ 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。
3. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)​，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。
4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’ ​就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)。
5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

回到开始的慢查询

根据最左匹配原则，最开始的 sql 语句的索引应该是 status、operator_id、type、operate_time 的联合索引；其中 status、operator_id、type 的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：

select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;

那么索引建立成 (status,type,operator_id,operate_time) ​就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则

查询优化神器 - explain 命令

慢查询优化基本步骤

0. 先运行看看是否真的很慢，注意设置 SQL_NO_CACHE

1. where 条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的 where 都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2. explain 查看执行计划，是否与 1 预期一致（从锁定记录较少的表开始查询）

3. order by limit 形式的 sql 语句让排序的表优先查

4. 了解业务方使用场景

5. 加索引时参照建索引的几大原则

6. 观察结果，不符合预期继续从 0 分析

慢查询案例。

复杂语句写法

很多情况下，我们写 SQL 只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对 mysql 的执行计划和索引原则有非常清楚的认识，请看下面的语句：

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';

0. ​先运行一下​，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                    |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+

2. ​简述一下执行计划​，首先 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 条记录。
3. ​如何优化呢​？可以看到我们在运行完后还是要和 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

4. 不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致
5. 现有索引可以满足，不需要建索引
6. 用改造后的语句实验一下，只需要 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
   );

0. ​先看看运行多长时间​,951 条数据 6.22 秒，真的很慢。

951 rows in set (6.22 sec)

1. ​先 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 |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

2. 所有字段都应用查询返回记录数，因为是单表查询 0 已经做过了 951 条。
3. 让 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 分布的很平均，那么锁定记录也是百万级别的。

4. ​找业务方去沟通，看看使用场景​。业务方是这么来使用这个 SQL 语句的，每隔五分钟会扫描符合条件的数据，处理完成后把 sync_status 这个字段变成 1,五分钟符合条件的记录数并不会太多，1000 个左右。了解了业务方的使用场景后，优化这个 SQL 就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。
5. ​根据建立索引规则​，使用如下语句建立索引

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

6. ​观察预期结果​,发现只需要 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,
      1)  
   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;

还是几个步骤。

0. ​先看语句运行多长时间​，10 条记录用了 13 秒，已经不可忍受。

10 rows in set (13.06 sec)

1. 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 等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。

‍

参考链接

转载说明

• 大厂实践 - 美团: MySQL索引原理及慢查询优化
• 《高性能 MySQL》
• 《数据结构与算法分析》

‍