江苏龙网

  1. 主页 > 生活百科 > >

阿里P8架构师谈：MySQL数据库的索引原理、与慢SQL优化的5大原则( 四 )

索引mysql

简述一下执行计划 ， 首先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 empon 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 unionselect emp.id from cm_log cl inner join emp_certificate econ cl.ref_table = 'EmpCertificate'and cl.ref_oid = ec.id inner join employee empon 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 = 04.不需要了解业务场景 ， 只需要改造的语句和改造之前的语句保持结果一致
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=1and ( sp.sync_status=0or sp.sync_status=2or 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


推荐阅读


上一篇：自己一个人怎么漂发 漂流可以一个人自己漂的吗

下一篇：切开的猕猴桃隔夜能吃吗 猕猴桃切开放一夜还能吃吗