MySQL索引使用准测

SQL语句中使用索引，是需要遵守一些准则的，否则极有可能导致索引失效，这篇文章主要就是介绍索引使用过程的一些注意事项。

一、数据准备

建立一张员工表：

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (id),
  KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

该表除了主键索引外，还有一个联合索引idx_name_age_position，该联合索引字段的顺序是name,age,position，在联合索引中，字段的顺序是非常重要的，这在《索引基础》这边文章中有做过介绍。

然后为该表添加三条数据：

INSERT INTO employees (id, name, age, position, hire_time) VALUES (1, 'LiLei', 22, 'manager', '2022-03-24 18:26:32');

INSERT INTO employees (id, name, age, position, hire_time) VALUES (2, 'HanMeimei', 23, 'dev', '2022-03-24 18:26:39');

INSERT INTO employees (id, name, age, position, hire_time) VALUES (3, 'Lucy', 23, 'dev', '2022-03-24 18:26:45');

二、索引使用

2.1 最左前缀法则

只有联合索引中部分字段参与查询时，也是可以使用该联合索引的，但使用索引的前提是需要满足最左前缀法则，最左前缀法则指的是查询从索引的最左前列开始并且不跳过索引中的列。简单来说就是这部分字段对应的数据记录，在联合索引的B+树结构上有序的，这样才能索引。

以下面三个SQL语句为例(联合索引idx_name_age_position)：

1 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31; 
2 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev'; 
3 EXPLAIN SELECT * FROM employees WHERE position = 'manager';

上面这三个SQL语句，只有第一个可以使用索引，剩余两个都不能没法使用索引。

分析第二个SQL，联合索引是按照name,age,position的顺序进行排序，在name未知时时，age和position并不一定是有序的，所有如果使用索引就需要遍历所有的记录。

而在第三个SQL中，postion列在age和name不确定的情况下，它也不是有序的，如果要使用索引就需要遍历所有联合索引的子节点。

注：第二和第三个SQL没有使用索引的另外一个原因是查询的结果字段，联合索引并不完全具备，所以还需要回表操作，而回表操作是非常耗时，但如果查找的字段联合索引中有，那么这两个SQL也是可能走索引的。

比如把SQL改成下面这样：

EXPLAIN SELECT name FROM employees WHERE age = 30 AND position = 'dev'; 
EXPLAIN SELECT name FROM employees WHERE position = 'manager';

所以，需要注意的是，能不能走索引并不完全受限于这些准则，而完全是由MySQL内核通过成本分析来决定是否走索引的。

2.2 不在索引列做任何操作

如果对索引列进行计算、函数、(自动or手动)类型转换会导致索引失效而转向全表扫描

索引失效的原因仍然是通过计算，原本有序的索引可能就变得无序了，无序就意味着要扫描所有记录，当然也就不能使用索引了。

比如下面的SQL:

无操作：
函数操作：

对于这个列子，可能会比较好奇，name列原来是有序的，使用left()函数截取后也还是有序的，为什么不能使用索引呢？

因为有的函数操作后还是有序，而有的函数操作后就是无序的，MySQL内部有那么多函数，不可能每个函数还要记录操作后是否有序，所以MySQL就简单粗暴，只要使用了函数操作，就不能使用索引。

2.3 范围条件右边的列不能使用索引

存储引擎不能使用索引中范围条件右边的列，看下面的SQL:

根据执行计划的key_len字段可以计算出，该SQL只使用了name和age这两个列的索引
上面这条SQL，只有前面两个列使用了索引，而position列没有使用索引，还是看B+树的结果，在name列固定时，age > 22的范围中，age依然是有序的，但此时position列并不一定是有序的，自然就不能使用索引。