在 MySQL 数据库中，索引是一种用于提高查询性能的数据结构。正确地使用索引可以大大加快数据的检索速度，而不合理的索引使用则可能导致性能下降。本文将详细介绍 MySQL 索引机制中的匹配规则，帮助读者更好地理解和优化索引的使用。

在正式介绍前，读者可以先执行下面内容，方便大家更好地理解和验证文中的示例（注意：以下示例皆是以MySQL8.0为基础）：

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='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());


DROP PROCEDURE IF EXISTS insert_emp;
delimiter ;;
create procedure insert_emp()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into employees(name, age, position) values(CONCAT('zhuge',i), i, 'dev');
    set i=i+1;
  end while;
end;;
delimiter ;
call insert_emp();

1 匹配规则

1.1 最左匹配原则

最左前缀匹配规则要求查询条件中的列必须与索引中的列从左到右依次匹配。这意味着查询语句中可以包含索引中的部分列，但是这些列必须从左到右依次匹配。

1.1.1 单列索引部分匹配

当使用 like 操作符时，数据库通常无法直接使用索引来加速查询，因为 like 操作符通常需要扫描大量的数据。但是，如果 like 操作符的模式是以固定的前缀开头，那么数据库可以使用索引来快速定位匹配的行。

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

1.1.2 联合索引部分匹配

如果有一个联合索引（col1，col2，col3），并且查询语句中使用了 col1、（col1、col2）或者（col1，col2，col3）进行查询，那么数据库会使用联合索引来加速查询。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

1.1.3 按顺序匹配

然而，如果查询语句中只使用了 （col2，col3）或者col3 进行查询，那么数据库可能不会使用联合索引，因为最左匹配原则没有得到满足。

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

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

1.1.4 范围查询影响索引选择

如果有一个联合索引 (col1, col2, col3)，并且查询语句中使用了col1进行范围查询联，不会走索引

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

如果有一个联合索引 (col1, col2, col3)，并且查询语句中使用了（col1，col2，col3）进行查询，其中 col2 使用了范围条件，那么数据库会使用联合索引中的（col1，col2）来加速查询。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

1.1.5 MySQL8的优化

MySQL 8 会根据查询条件的具体情况进行优化。查询条件中列的顺序与索引定义的顺序不完全一致的情况下，但 MySQL 8 仍然能够识别到这些列可以与联合索引进行匹配。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND position ='manager' AND age = 22 ;

1.2 强制使用索引

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

-- mysql8.0以前需要关闭查询缓存
set global query_cache_size=0;  
set global query_cache_type=0;
-- 执行时间0.092s
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间0.296s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

2 索引失效

2.1 类型转换

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 

EXPLAIN SELECT * FROM employees WHERE name = 1000;

2.2 函数操作

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

给hire_time增加一个普通索引：

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) = '2018-09-30';

还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

2.3 like左模糊匹配

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

问题：解决like'%字符串%'索引不被使用的方法？

a）使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b）如果不能使用覆盖索引则可能需要借助搜索引擎

2.4 ！=、<>、not in ，not exists

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

2.5 is null、is not null

EXPLAIN SELECT * FROM employees WHERE name is null

2.6 or或in根据表大小判断

or或in，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ; -- 给年龄添加单值索引
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

2.7 范围查询根据表大小判断

< 小于、 > 大于、 <=、>= 这些，单次数据量查询过大导致优化器最终选择不走索引

explain select * from employees where age >=1 and age <= 20000;

explain select * from employees where age >=1 and age <= 10000;
explain select * from employees where age >=10001 and age <= 20000;

ALTER TABLE `employees` DROP INDEX `idx_age`; -- 还原最初索引状态

3 结语

通过本文的介绍，我们了解了 MySQL 索引机制中的匹配规则。在实际应用中，我们应该根据具体的业务需求和数据特点，合理地设计和使用索引，以提高查询性能。同时，我们也需要注意避免索引失效的情况，确保索引能够真正发挥作用。希望本文能够对读者在 MySQL 索引的使用和优化方面提供一些帮助。