mysql如何查缺索引_MySQL查缺补漏系列（1）：索引失效问题

MySQL查缺补漏系列(1)：索引失效问题

1、背景知识必备

1.1、key(键)

等于约束字段的行为+索引

primary key(主键)

约束作用：唯一标识数据库表的数据；主键必须唯一则不能为NULL

索引作用：建立了一个主键索引

unique key(唯一约束)

约束作用：唯一标识数据库表中的每条记录

索引作用：建立了一个主键索引

foreign key (外键)

约束作用：引用完整性

索引作用：建立了一个主键索引

1.2、index(索引)

仅是索引，不会约束字段的行为

创建普通索引，create index 索引名 on 数据表(字段)

创建联合索引，create index 索引名 on 数据表(字段1，字段2，。。。)

作用1：联合索引可以减少索引的个数；

每多一个索引，都会增加写操作的开销和磁盘的开销

举例：一个(a,b,c)的组合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引

作用2：索引列越多，通过索引筛选出的数据越少，所以更高效

删除索引，drop index 索引名

1.3、索引最左原则

说明，可以用执行计划explain查看有没有走索引。

索引的底层是一颗B+树，构建一颗B+树只能根据一个值来构建，数据库是通过联合索引最左的字段来构建B+树。

假设建立了一个联合索引(a,b)：

当a值相等时，b值是有顺序的，所以a、b字段是用了索引，

当a值是一个范围时，b是无序的，所以这种情况下a后面的字段都是无法使用索引的

那么，最左原则就是指

以联合索引最左边为起点的任何连续的索引都能匹配上，但是一旦遇到范围查询(>、

特别说明，WHERE子句的搜索条件顺序不会影响查询结果，是因为MySQL的查询优化器会自动化优化了查询顺序。

2、索引正常场景举例

假设联合索引(c1,c2,c3,c4....cN)

2.1、从联合索引的最左边开始连续匹配，结果是走了索引

select * from table_name where c1 = '1' and c2 = '2' and c3 = '3'

2.2、匹配前缀，结果是走了索引，结果是走了索引

select * from table_name where c1 like 'xxx%'

字符串是从第一个字符开始比较

2.3、对最左边的字段进行范围查询，结果是走了索引

explain select * from table_name where c1 > 1 and c1 < 10

数据库是通过联合索引最左的字段即c1来构建B+树，故走了索引

2.4、前面字段连续精确匹配，最后一个字段范围查询，结果是走了索引

select * from table_name where c1 = 1 and c2 > 3;

c1=1时，c2是有序的

2.5、利用索引排序

文件排序是指在内存中或磁盘上进行排序的方式。

把数据加载到内存，然后利用排序算法(快速排序/归并排序等)在内存中对这些记录进行排序

查询结果集太大内存无法进行排序，还可以临时借助磁盘空间，排序操作完成后把中间结果返回给客户端

select * from table_name order by c1,c2,c3,c4....cN limit 10

利用索引排序是原理

B+树本身是安装索引字段排序，可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列

order by 后面字段顺序必须严格按照联合索引字段顺序才有用

3、索引失效场景举例

假设联合索引(c1,c2,c3,c4....cN)

3.1、没有从最左边连续匹配，导致不走联合索引

explain select * from table_name where c1 = '1' and c3 = '3'

只用到了c1列的索引，所以最后没有用到该联合索引，导致全表扫描

3.2、匹配列是中缀和后缀，导致不走联合索引

explain select * from table_name where c1 like 'xxx%' // 前缀走索引

explain select * from table_name where c1 like '%xxx' // 后缀不走索引

explain select * from table_name where c1 like '%xxx%' // 中缀不走索引

字符型是从第一个字符开始比较，故中缀和后缀都是全表扫描

3.3、范围值匹配，导致不走联合索引

explain select * from table_name where c1 > 1 and c2 < 10 // 不走索引

在c1>1时，c2是无序的不能用索引。