dsl 索引字段问json 索引 index

背景

最近在写一些数据统计的面板，里面有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会非常慢。我们急需索引来筛选不需要扫描的行。

最左前缀匹配原则

最左前缀匹配原则，建立索引我们需要第一个要想到的，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;

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

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

我们看看索引扫描了多少行,58w多，差不多是总数的一半。说明索引建立的还是有效果的,但是我们的索引优化还没有终结。

覆盖索引

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

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

索引下推 index condition pushdown

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

我们来看看 没有索引下推和有索引下推 ，一条sql是怎么筛选数据的。

索引下推 貌似优化了 以前最左前缀匹配的局限，在引擎层可以过滤更多的行，直接的好处就是减少了回表查询和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 ,说明索引下推开始生效。

鸣谢

这篇文章是我真实的一个sql索引调优过程，但是期间参考了很多资料，也系统学习了下索引调优。要感谢下面的文章

• 一起学习Mysql索引三（ICP,索引条件下推）
• MySQL索引原理及慢查询优化.
• 高性能mysql，必看经典的sql语句优化，第5章可以好好看看。
• MySQL高阶问题：server层和存储引擎层是如何交互的？ 有利于理解上面关于 索引下推 的流程图