mysql索引优化实践

心已赠人 2022-12-16 09:16 238阅读 0赞

## 背景 ##

最近在写一些数据统计的面板，里面有sql对表数据的聚合统计，我的主表现在有100来万的数据，其间看了很多资料。记录一下sql索引的优化过程. sql 如下,只有一个连表查询，再加上函数聚合出结果

select count(if(b.severity = 1, true, null)) severityAllNum,
            count(if(b.severity = 2, true, null)) importanceAllNum,
            count(if(b.severity = 1 and timestampdiff(minute, b.time, b.solve_time) <= 60, true, null)) severityDoneNum,
            count(if(b.severity = 2 and timestampdiff(minute, b.time, b.solve_time) <= 240, true, null))
            importanceDoneNum
            from (
            select a.time, a.solve_time, a.severity
            from alarm_log a
            left join alarm_log_app_info alai using(alarm_business_id)
            where
    
                a.channel = 'apppush'
                and a.status in (2, 3, 4)
                and a.time >= '2019-12-30 00:00:00'
                and a.time <= '2020-01-03 23:59:59'
        
            group by alai.alarm_business_id
            order by null
            ) b

我们看一下没有任何索引这个的执行计划，表a的rows函数是100多w，type = all，属于全表扫描了，这样执行sql会非常慢。我们急需索引来筛选不需要扫描的行。  
![在这里插入图片描述][20200108193911876.png]

## 最左前缀匹配原则 ##

最左前缀匹配原则，建立索引我们需要第一个要想到的，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的顺序可以任意调整。 从这里也建议了`最好不要用单列索引`

但是如何建立一个多列索引呢，多个列名怎么做排列？这里引出 `列区分度`,简单的说就是每一列的 所有不重复数据 / 总条数的比例，按照上面的sql，我们用另一条sql计算一下where/select/group by 那些列的区分度

select count(distinct alarm_business_id) / count(*),
           count(distinct time) / count(*),
           count(distinct solve_time) / count(*),
           count(distinct channel) / count(*),
           count(distinct status) / count(*),
           count(distinct severity) / count(*)
    from alarm_log;

![在这里插入图片描述][20200110154753289.png]

我们可以看到 alarm\_business\_id，time 区分度是比较高的。它们可以放在索引的前面，让mysql引擎层筛选出更多的行，但是最左前缀匹配原则会匹配到`范围查询`为止，所以我们更多把 时间字段放在索引最后。下面是我的第一版索引

create index alarm_log_index
        on alarm_log (channel, status, severity, alarm_business_id, time, solve_time);

我们看看索引扫描了多少行,58w多，差不多是总数的一半。说明索引建立的还是有效果的,但是我们的索引优化还没有终结。  
![在这里插入图片描述][20200110163212199.png]

## 覆盖索引 ##

上图中extra列中有一个use index，这是啥意思？这里提到了 `覆盖索引`的概念，我们首先要知道，主键是一个聚簇索引，它保存了这一行所有列的数据，所以当我们根据id 去查找一条数据时，我们取出来的列数据是从索引中直接拉取出来的，无需回表查询，而回表的意思就是拿着主键id重新从数据库查一遍我们需要的列数据，而非主键列的索引 叫非聚簇索引，它只持有主键的id，所以当你用非聚簇索引查询出来的数据 其实经过了两次io – 第一次查询出条件行的id，第二次根据id查询出所需要的列数据(回表)。

那有个疑问了，非聚簇索引就一定只能回表才能查询出所有数据吗？ `覆盖索引` 就解决了我们的难题，我们把 where 和 select的列全部加到索引中，并且执行计划中 extra列 显示use index，就说明覆盖索引开始生效。试想，如果索引的叶子结点已经包含了要查询的数据，我们何必要多做一次回表查询.

## 索引下推 index condition pushdown ##

索引优化没有一个固定的模板，一切都是根据业务实际尝试出来的结果，有些时候你认为mysql应该走这个索引，但是实际上却走的全表扫描，很大原因是因为mysql执行计划认为你的数据量还不够大，走索引还不如全表扫描快，换言之，理论上覆盖索引已经是索引优化的理想态，因为它全部都是走索引取数据嘛，但是实际情况你可以继续减少扫描行来提高查询性能，接下来就介绍索引下推，这应该是mysql 5.6之后出来的特性。

我们来看看 没有索引下推和有索引下推 ，一条sql是怎么筛选数据的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvdmVqajE5OTQ_size_16_color_FFFFFF_t_70]  
`索引下推` 貌似优化了 以前最左前缀匹配的局限，在引擎层可以过滤更多的行，直接的好处就是减少了回表查询和server层额外的where过滤。基于这个特性，我优化了一下我的索引，把区分度最高的time字段放第一个，这样引擎层能一下筛选掉大部分数据，而且有了索引下推，能保证后面字段不会因为范围查询而用不上索引。

create index alarm_log_index
        on alarm_log (time, alarm_business_id, solve_time, metric_name, severity, major_type, channel);

可以看到扫描的行数 下降到15w左右,效果显著！extra列 显示 `use index condition` ,说明索引下推开始生效。  
![在这里插入图片描述][2020011018002217.png]

## 参考：[https://blog.csdn.net/lovejj1994/article/details/103874417][https_blog.csdn.net_lovejj1994_article_details_103874417] ##

[20200108193911876.png]: /images/20221123/da837c97df2f498d8e32f26ac8adefe9.png
[20200110154753289.png]: /images/20221123/a784f449c96d4f74aae1e738ced14389.png
[20200110163212199.png]: /images/20221123/d15f891193ee4ff2b2d140700f604414.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvdmVqajE5OTQ_size_16_color_FFFFFF_t_70]: /images/20221123/381bc4a82d9e429ca96b5d09d2f7ddb3.png
[2020011018002217.png]: /images/20221123/c6da225d6ff94a9989ff269073a74bf6.png
[https_blog.csdn.net_lovejj1994_article_details_103874417]: https://blog.csdn.net/lovejj1994/article/details/103874417