记一次索引优化的案例

场景

不废话直接建表：

create table t_log
(
    id                 bigint auto_increment comment '主键id'
        primary key,
    user_id            bigint                                 null comment '用户ID',
    eqp_number         varchar(255)                           null comment '设备号',
    type               char         default '1'               null comment '日志类型 0离线 1上线',
    title              varchar(255) default ''                null comment '日志标题',
    remote_addr        varchar(255)                           null comment '操作IP地址',
    current_project_id bigint                                 not null comment '当前项目ID',
    root_project_id    bigint                                 not null comment '根项目ID',
    create_time        datetime     default CURRENT_TIMESTAMP null comment '创建时间',
    eqp_name           varchar(50)                            null comment '设备名称',
    eqp_model          varchar(50)                            null comment '设备型号',
    eqp_major_type     varchar(5)                             null comment '设备大类1:微断主机;2';

)
    comment '日志' row_format = DYNAMIC;

现在有一个需求是需要根据项目类型(eqp_major_type) 和当前项目id（current_project_id）去查询且需要按着创建时间排序以10个分页。

OK

SELECT a.current_project_id AS projectId, a.eqp_name, a.eqp_model, a.eqp_number, a.type, a.create_time
FROM t_eqp_log a
WHERE a.current_project_id in (176)
and a.eqp_major_type = 1
order by a.create_time desc
LIMIT 10;

easy啊。

时间久了数据量变大 查询变慢了，leader： 那个谁，你去把这个接口sql优化一下

你看都没看直接加上索引

create index t_eqp_log_create_time_index
    on t_eqp_log (create_time);

create index t_eqp_log_current_project_id_index
    on t_eqp_log (current_project_id);

create index t_eqp_log_eqp_major_type_index
    on t_eqp_log (eqp_major_type);

条件字段都加上不就得了吗

但是

目标索引只有createtime被击中， 其他两个字段都没生效，

不对劲啊 （开始Google GPT

一句话总结就是说mysql优化器认为通过create_time索引效率更高，也就是三个字段都没有索引的情况，create_time字段查询的成本会更高，so嘎 那怎么解决呢。很简单啊 直接联合索引啊

create index t_eqp_log_current_project_id_eqp_major_type_create_time_index
    on t_eqp_log (create_time, current_project_id, eqp_major_type);

再次执行一下 不出意外就要出意外了 果然又没生效

分析分析再分析

怎么这行sql越看越不对劲呢。淦 eqp_major_type是string类型的 也就是 eqp_major_type = 1 是存在一个强转的过程

强转就会导致联合索引失效 服了 继续再来

等会 有点对劲啊 赶紧看看文档

官方文档这大一堆 总结就一句话。index是全索引扫描 效率不如range 且重点：

性能从最好到最差：null > system > const > eq_ref > ref > range > index > all

继续看看extra

总结一下就是说这次执行情况为：index 类型意味着 MySQL 使用了全索引扫描，尽管联合索引被使用，但可能在过滤上不如 range 类型有效。Backward index scan 表示它在倒序扫描整个索引，这种扫描方式效率相对低。

这时候突然有灵感，既然选择了联合查询肯定要考虑最左前导列原则啊，create_time作为第一导列 但是where条件却没有，也就是没法用它很好的利用where条件来过滤大部分数据，自然会选择全索引扫描 赶紧试试验证一下

h h h 果然皇天不负👴的心， 变成range了。依旧刚学到的 range是> index 成了。

让我们总结一下 ：

1. 第一种情况：create_time 作为索引的第一列。
2. 第二种情况：current_project_id 作为索引的第一列。

为什么索引顺序影响查询效率

在 MySQL 中，索引的顺序对于查询的执行计划有重要影响，因为 MySQL 是按索引的最左前导列（即索引的第一个字段）开始扫描的。查询条件中的字段顺序应与索引顺序匹配，以便 MySQL 能够充分利用索引来减少扫描的记录数。

所以如我们当时猜想一样。

create_time作为第一列的时候。where条件并不再索引的前置位置，那么 MySQL 可能只能利用索引的第一部分来进行范围扫描，而不能有效地利用后续条件来进一步减少扫描的范围。也就导致了index 类型的全索引扫描。

而第二种情况 当 current_project_id 是索引的第一列，并且 WHERE 子句中首先使用它来筛选数据时，MySQL 可以更好地利用索引。这通常会导致 range 扫描类型，因为它允许 MySQL 通过范围条件直接跳过不相关的记录。在这种情况下，MySQL 更容易使用索引的其余部分（如 create_time ）来进一步减少扫描范围。

总结与比较

• 第一种情况：index 类型意味着 MySQL 使用了全索引扫描，尽管联合索引被使用，但可能在过滤上不如 range 类型有效。Backward index scan 表示它在倒序扫描整个索引，这种扫描方式效率相对低。
• 第二种情况：range 类型表示 MySQL 进行了范围扫描，这通常更高效，并且 Using index condition 进一步表明 MySQL 在扫描过程中使用了索引来过滤数据。总体来说，这种情况更能够充分利用联合索引，尤其是在处理 WHERE 子句条件时

既然说了最左前导列原则，这里复习一下八股文：

1. 计算 函数导致索引失效 where a+1=3; WHERE LEFT(emp.name,3) = 'abc';--索引失效
2. 模糊查询 SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效
3. 类型转换 select * from emp where type = 1; --type为string类型 且存储数值 索引失效
4. 条件中存在or where a>100 or b<100; --条件查询存在or
5. 不等于(!= 或者<>) SELECT * FROM emp WHERE emp.name <> 'abc' ;--索引失效
6. is not null EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL; EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效
7. 最左匹配原则：

调整一下刚才的索引

create index t_eqp_log_current_project_id_eqp_major_type_user_id_index
    on t_eqp_log (current_project_id, eqp_major_type, user_id);

​ 正常情况下只要存在current_project_id即联合索引生效，但：

1. 索引中范围条件右边的列失效

当 a=30 b>100 c='abc'. 只走a b索引。

注意：当b条件覆盖绝大数数据时也可能不走索引 由优化器成本决定

当a=30 c='abc' b>100 正常走三个索引

2. 存在联合索引和单一索引时 如果联合索引效率高也不走单一索引