慢mysql优化记录

背景

来自于日常工作过程中的慢sql优化有感，记录下一些常见分析流程实践过程。

排序场景优化

一些信息

表名：example_table
存在索引：

  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_batchid_deptno2_status_isdeleted` (`batch_id`,`dept_no2`,`status`,`is_deleted`)

执行的sql

        select *
        from example_table
        where is_deleted = 0
            and promotion_type = 204
            and promotion_no = '41780796'
            and batch_id = 323145
            and status in (6)
            order by id asc
        limit 50,200

复现

直接执行耗时

explain结果

好像300ms多，明显慢sql，看下索引发现走了idx_batchid_deptno2_status_isdeleted索引，通过extra发现会filesort。

Tips
filesort表示在非索引列进行了排序。就是相当于查出来结果集后，会再使用快排排序一次，效率较低。

疑问

索引树默认会带主键id字段，比如索引idx_batchid(batch_id)和索引idx_batchid(batch_id,id)效果相同。查询条件都是等号，正常好的索引是可以直接通过索引树(b+)排好序。
所以解决思路就是怎么可以让查询直接在索引排序。

解决

观察possible_key，很容易注意到两条索引：

  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_batchid_deptno2_status_isdeleted` (`batch_id`,`dept_no2`,`status`,`is_deleted`)

正常情况这两条索引明显冗余了一条，idx_batch_id可以干掉，毕竟查询能走idx_batch_id肯定能走idx_batchid_deptno2_status_isdeleted索引。如果加上排序就有区别，试下使用强制索引走idx_batch_id情况

select *
        from example_table
        force index (idx_batch_id)
        where is_deleted = 0
            and promotion_type = 204
            and promotion_no = '41780796'
            and batch_id = 323145
            and status in (6)
            order by id asc
        limit 50,200

发现耗时减少了10倍，explain可以看到没有filesort了。

原因

以上两条索引默认都带了id，按照最左匹配原则，查询条件没有dept_no2条件，能匹配上idx_batchid_deptno2_status_isdeleted索引，但是从索引树上的结果并不是id排序的结果。所以需要重新排序。
而走idx_batch_id索引，相当于(batch_id，id)索引，按照batch_id过滤后，从索引B+树上取出的结果就是按id排序的。