mysql索引实现

什么是索引失效

在MySQL中，索引失效指的是查询语句无法有效地使用索引，而必须进行全表扫描。索引失效可能会导致查询性能下降，特别是在处理大量数据时。

索引失效的原因

1.索引列进行了运算或函数操作
如果对索引列进行了运算或使用了函数，MySQL无法使用索引，会导致索引失效。例如，对于以下查询语句：

SELECT * FROM table_name WHERE YEAR(date_column) = 2022;

如果date_column是索引列，但由于使用了YEAR函数，索引失效。
使用了不匹配索引的LIKE查询：

2.当使用LIKE进行模糊查询时，如果通配符在开头，MySQL无法使用索引。例如，对于以下查询语句：

SELECT * FROM table_name WHERE column_name LIKE '%value%';

索引失效。
3.类型不匹配
当查询条件的数据类型与索引列的数据类型不匹配时，MySQL无法使用索引。例如，对于以下查询语句：

SELECT * FROM table_name WHERE int_column = 'value';

如果int_column是整数类型的索引列，但查询条件是字符串类型，会导致索引失效。
4.范围查询中的左前缀：

当使用范围查询时，如果索引列只在范围的右边，MySQL无法使用索引。例如，对于以下查询语句：

SELECT * FROM table_name WHERE indexed_column > 10 AND non_indexed_column = 'value';

索引失效。
5.使用OR连接的条件

当查询条件中使用了OR连接多个条件时，如果其中有一个条件无法使用索引，整个查询可能会导致索引失效。例如，对于以下查询语句：

SELECT * FROM table_name WHERE indexed_column = 'value1' OR non_indexed_column = 'value2';

如果non_indexed_column未创建索引，整个查询可能会导致索引失效。

创建测试表和数据
为了演示和测试那种情况下会导致索引失效，我们先创建一个测试表和相应的数据：

– 创建表

drop table if exists student;
create table student(
  id int primary key auto_increment comment '主键',
  sn varchar(32) comment '学号',
  name varchar(250) comment '姓名',
  age int comment '年龄',
  sex bit comment '性别',
  address varchar(250) comment '家庭地址',
  key idx_address (address),
  key idx_sn_name_age (sn,name,age)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加测试数据
insert into student(id,sn,name,age,sex,address) 
  values(1,'cn001','张三',18,1,'高老庄'),
  (2,'cn002','李四',20,0,'花果山'),
  (3,'cn003','王五',50,1,'水帘洞');

当前表中总共有 3 个索引，如下图所示：
在这里插入图片描述

PS：本文以下内容基于 MySQL 5.7 InnoDB 数据引擎下。

索引失效情况1：非最左匹配
最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。
我们本文的联合索引的字段顺序是 sn + name + age，我们假设它们的顺序是 A + B + C，以下联合索引的使用情况如下：
在这里插入图片描述

从上述结果可以看出，如果是以最左边开始匹配的字段都可以使用上联合索引，比如：

A+B+C

A+B

A+C

其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。

而 B+C 却不能使用到联合索引，这就是最左匹配原则。

索引失效情况2：错误模糊查询
模糊查询 like 的常见用法有 3 种：

模糊匹配后面任意字符：like ‘张%’
模糊匹配前面任意字符：like ‘%张’
模糊匹配前后任意字符：like ‘%张%’
而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引，具体执行结果如下：
在这里插入图片描述

索引失效情况3：列运算
如果索引列使用了运算，那么索引也会失效，如下图所示：
在这里插入图片描述

索引失效情况4：使用函数
查询列如果使用任意 MySQL 提供的函数就会导致索引失效，比如以下列使用了 ifnull 函数之后的执行计划如下：
在这里插入图片描述

索引失效情况5：类型转换
如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：
在这里插入图片描述

索引失效情况6：使用 is not null
当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：
在这里插入图片描述

总结
导致 MySQL 索引失效的常见场景有以下 6 种：
联合索引不满足最左匹配原则。
模糊查询最前面的为不确定匹配字符。
索引列参与了运算。
索引列使用了函数。
索引列存在类型转换。
索引列使用 is not null 查询。

怎么排查索引失效

1.执行计划分析：
使用 EXPLAIN 关键字可以查看 MySQL 的查询执行计划。执行 EXPLAIN SELECT … 查询可以得到查询计划的详细信息，包括 MySQL 是否使用了索引，以及索引的选择情况。通过分析执行计划，可以确定是否出现了索引失效的情况。
2.使用索引提示：
如果 MySQL 没有选择正确的索引，可以使用索引提示来强制 MySQL 使用指定的索引。可以在查询语句中使用 USE INDEX 或 FORCE INDEX 关键字来指定要使用的索引，从而排除索引失效的可能性。
3.查看慢查询日志：
如果查询执行时间较长，可能会被记录在 MySQL 的慢查询日志中。可以查看慢查询日志，找出执行时间较长的查询语句，并分析其执行计划和索引使用情况。
4.检查查询条件和索引定义：
检查查询语句中的条件是否能够利用索引。例如，是否存在类型不匹配、使用了函数、使用了 LIKE 模糊查询等情况。同时，检查索引的定义是否覆盖了查询语句中的所有条件。
5.使用性能分析工具：
使用 MySQL 提供的性能分析工具，如 pt-query-digest、mysqldumpslow 等，可以帮助分析查询的执行情况，找出可能导致索引失效的原因。
6.查看数据库配置：
检查 MySQL 数据库的配置文件，查看是否开启了合适的优化选项，如 optimizer_switch 参数中的各种优化器选项，以及 innodb_buffer_pool_size 参数等。正确的配置可以提高索引的利用率，降低索引失效的概率。