MySQL之联合索引最左原则

MySQL可以创建复合索引，一个索引最多可以包含16列。联合索引参与条件规则的字段需要放在前面，即需要满足最左匹配原则。举例来说，如果你有一个三列的索引(col1, col2, col3)，走索引的条件顺序为(col1)，(col1, col2)以及 (col1, col2, col3)。
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，指标TYPE结果值好坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

假设存在测试表，增加联合索引 idx_last_first_name (last_name,first_name)

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL COMMENT '员工号',
  `birth_date` date NOT NULL COMMENT '出生年月',
  `first_name` varchar(14) NOT NULL COMMENT '名',
  `last_name` varchar(16) NOT NULL COMMENT '姓',
  `gender` enum('M','F') NOT NULL COMMENT '性别',
  `hire_date` date NOT NULL COMMENT '雇佣时间',
  `ids` char(18) DEFAULT NULL COMMENT '身份证号',
  PRIMARY KEY (`emp_no`),
  KEY `idx_last_first_name` (`last_name`,`first_name`)
)

下面SQL是遵循了最左匹配原则的语句

1	SELECT * FROM employees WHERE last_name='Jones';

1	SELECT * FROM employees WHERE last_name='Jones' ORDER BY last_name,first_name desc;

1	SELECT * FROM employees WHERE last_name='Jones' AND first_name='John';

1	SELECT * FROM employees WHERE last_name='Jones' AND (first_name='John' OR first_name='Jon');

1	SELECT * FROM employees WHERE last_name='Jones' AND first_name >='M' AND first_name < 'N';

1	SELECT * FROM employees WHERE last_name ='M' ORDER BY first_name,last_name desc;

不走索引的语句类型有

1	SELECT * FROM employees WHERE first_name='John';

1	SELECT * FROM employees WHERE last_name='Jones' OR first_name='John';

1	SELECT * FROM employees WHERE last_name='Jones' ORDER BY first_name,last_name desc;

注意，排序时是否走索引受MySQL版本的影响，在MySQL8.0后，在排序查询中字段顺序不受影响。如图

但是在MySQL5.7及以下版本中，排序查询受字段顺序影响。

参考文档

MySQL联合索引 https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
EXPLAIN输出格式 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
Visual Explain图 https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html
各个引擎支持的索引类型 https://dev.mysql.com/doc/refman/5.7/en/create-index.html