条目
本节来通过几个示例来事件一下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';