MySQL查缺补漏系列（1）：索引失效问题

MySQL查缺补漏系列（1）：索引失效问题

1、背景知识必备

1.1、key（键）

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

• primary key（主键）

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

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

• unique key（唯一约束）

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

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

• foreign key （外键）

  1. 约束作用：引用完整性

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

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+树。

此处省略了一个图：多个键值的B+树

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

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

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

那么，最左原则就是指

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

特别说明，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是无序的不能用索引。