我正在参加「掘金·启航计划」

联合索引

对主键建立的索引叫做聚簇索引, 对普通字段建立的索引叫做二级索引

多个普通字段组合在一起创建的索引叫做联合索引, 也被称之为组合索引

在创建联合索引时, 需要着重注意多个字段的顺序问题, 因为(a,b,c)和(b,a,c)在使用时会有不同

联合索引的使用需要遵循最左前缀匹配原则, 也就是按照最左优先的方式进行索引的匹配

联合索引执行示例

创建一个(a,b,c)的联合索引, 接下来将会举例可能会遇到的所有情况, 并写出是否会执行索引

Where语句	索引是否被使用
where a = 1	Y,使用到a
where a = 1 and b = 2	Y,使用到a，b
where a = 1 and b = 2 and c = 3	Y,使用到a,b,c
where a = 1 and b like 'kk%' and c = 3	Y,使用到a,b,c
where a = 1 and b like '%kk' and c = 3	Y,只用到a
where a = 1 and b like '%kk%' and c = 3	Y,只用到a
where a = 1 and b like 'k%kk%' and c = 3	Y,使用到a,b,c
where a = 1 and c = 3	使用到a， 但是c不可以，b中间断了
where a =13 and b > 2 and c = 3	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不一定能用上索引（8.0）
where b = 2 或者 where b = 3 and c = 4 或者 where c = 4	N
where a <> 1	不能使用索引
where abs(a) =1	不能使用 索引
where b = 2	不能使用 索引
where c = 3	不能使用 索引
where b = 2 and c = 3	不能使用 索引

因为有查询优化器, 所以字段 a在 where子句中的顺序不重要

索引的 order by优化

MySQL中的排序方式

在 MySQL中有两种排序方式:

• Using filesort: 通过表的索引或全表扫描, 读取满足条件的数据行, 然后在排序缓冲区sort buffer中完成排序操作, 所有不是通过索引直接返回排序结果的排序都叫Using filesort
• Using index: 通过有序索引顺序扫描直接返回有序数据, 这种情况下使用的是Using index, 不需要额外的排序, 操作效率高

很明显, Using index 使用到了索引, 肯定是性能高的, 所以我们在实际使用中尽量将 SQL优化到Using index

接下来我们就测试一下 order by的索引使用

数据准备

测试数据嘛, 肯定是越多越好. 这里建议观看我的这篇文章 MySQL批量插入测试数据的几种方式 - 掘金 (juejin.cn)

准备了一张表, 数据量 2w

角色表:

• id: 自增长
• role_name: 随机字符串, 不允许重复
• orders: 1-1000任意数字

无索引

这里我们要使用到explain命令, 也是大家很熟悉的了

explain命令主要用于查看 SQL的执行计划, 该命令可以模拟优化器执行 SQL查询语句

当前我们的role表是没有索引的

接下来我们会执行以下 SQL语句分别查看没有索引和有索引的情况

explain select * from role order by orders

此时可以看到, 因为排序所用到的条件orders没有用到索引, 索引会用到排序缓冲区, 也就是把数据读出来, 然后在排序缓冲区进行排序后展示出来

有索引

这个时候我们给role表新增索引

-- 给tb_user中的age和phone创建索引 
-- CREATE INDEZ 索引名 ON 表名(字段名...);
CREATE INDEX or_role ON role(orders,role_name);

现在我们就创建好需要的索引了, 重新执行一下之前的 SQL语句

explain select * from role order by orders, role_name

这次我们可以看到Extra出现了Using index, 也就代表着我们使用到了索引, 同时需要注意的是, 这次我们使用到了两个排序字段orders和role_name, 也就是我们之前创建的索引, 众所周知, MySQL有自己的执行优化器, where子句索引字段所处的位置无关紧要, 只要使用到了就可以, 那么order by是不是也是这样呢

where子句索引字段顺序不一致

explain select * from role where orders = 500 and role_name like 'a%'

咱就说, 不知道没关系, 有图有真相

order by索引字段顺序不一致

explain select * from role order by role_name,orders

接下来我们看一下 order by子句字段顺序与索引顺序不一致的情况

可以看到, 最后还是出现了Using filesort的情况

索引字段升降序不一致

explain select * from role order by orders asc, role_name desc

我们在使用 order by的时候如果没有指定顺序, 默认都是按照升序排列的, 索引也是这样, 字段默认是升序排列的, 但是当我们查询的时候一个升序, 一个降序, 此时就会出现Using filesort

如果想解决这个问题, 我们可以使用下面的 SQL语句在生成索引的时候指定索引的排列顺序

CREATE INDEX or_role ON role(orders asc,role_name desc);

总结

当我们使用联合索引的时候, 在where子句中要考虑最左前缀索引是否使用到了, 合理的去创建索引, 因为 MySQL有优化器的存在, 所以在where子句中不用考虑字段的顺序问题

但是在order by使用联合索引的时候, 要考虑order by字段和索引顺序是否一致, 排序规则和索引是否一致

本文内容到此结束了 如有收获欢迎点赞👍收藏💖关注✔️，您的鼓励是我最大的动力。 如有错误❌疑问💬欢迎各位大佬指出。 我是 宁轩 , 我们下次再见