Mysql优化三：索引的使用规则和注意事项

本节来通过几个示例来事件一下Mysql的索引机制，从而更加深入的了解Mysql索引的原理机制。
示例表及数据如下：

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=100007 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO `employees` VALUES ('100004', 'LiLei', '22', 'manager', '2020-01-05 16:10:44');
INSERT INTO `employees` VALUES ('100005', 'HanMeimei', '23', 'dev', '2020-01-05 16:10:44');
INSERT INTO `employees` VALUES ('100006', 'Lucy', '23', 'dev', '2020-01-05 16:10:44');

全值匹配

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';

可以看到随着条件增加，key_len的值逐渐增大，这是因为在第一个查询时，只是使用了联合索引(name,age,position)的第一个字段name，name字段声明为varchar(24)，并且使用的是编码集是UTF-8，使用之前的计算公式3n+2 = 3 * 24 + 2 = 74，后面的两个sql道理相同。

最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

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

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

这里可以看到第一个查询使用了索引，而第二个和第三个均未走索引，这是联合索引的结构所导致的，我们之前了解过联合索引的比较时通过声明的字段逐个判断的，位于后面的字段就不能单单通过索引来进行查找。

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

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

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

可以看到使用LEFT函数后，type列的值为ALL，而且没有使用索引。

存储引擎不能使用索引中范围条件右边的列

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';

但是我这里测试了一下这种情况(mysql版本5.5.33)。

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

这里虽然age是范围值，但是索引使用长度仍然是140，也就是使用了整个索引的长度。这种情况在优化时可以使用。比如查询条件是大于22岁的添加可以写成大于等于23(对于是整数类型字段)。

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

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

查询的字段均在索引中的话：

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

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

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

is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

EXPLAIN SELECT * FROM employees WHERE name is  not null;

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%LiLei';

EXPLAIN SELECT * FROM employees WHERE name like 'Li%';

如果需要解决like ‘%LI%’不走索引可以使用覆盖索引，将*修改为索引中包含的字段：

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

字符串不加单引号索引失效

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

EXPLAIN SELECT * FROM employees WHERE name = 1000;

少用or或in，用它查询时，非主键字段的索引会失效，主键索引有时生效，有时不生效，跟数据量有关，具体还得看mysql的查询优化结果

EXPLAIN SELECT * FROM employees WHERE name = 'Lilei' or name = 'Li';