关于MySQL的索引之最左前缀优化详解

Elina ·

更新时间:2024-09-20

· 431 次阅读

一、联合索引

联合索引执行示例

二、索引的 order by优化

MySQL中的排序方式

数据准备

无索引

有索引

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

order by索引字段顺序不一致

索引字段升降序不一致

三、总结

一、联合索引

对主键建立的索引叫做聚簇索引, 对普通字段建立的索引叫做二级索引多个普通字段组合在一起创建的索引叫做联合索引, 也被称之为组合索引在创建联合索引时, 需要着重注意多个字段的顺序问题, 因为(a,b,c)和(b,a,c)在使用时会有不同联合索引的使用需要遵循最左前缀匹配原则, 也就是按照最左优先的方式进行索引的匹配

联合索引执行示例

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

Where语句	索引是否被使用
where a = 1Y,	使用到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的索引使用

数据准备

测试数据嘛, 肯定是越多越好.准备了一张表, 数据量 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%'