mysql索引最佳使用案例

索引最佳实践

示例表：
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=4 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());

上面已经给 name age position 添加了联合索引 idx_name_age_position

上面是联合索引的b+树结构图,最下面的节点存放的不是数据,
是mysql主键索引的唯一标识,没画出来,在实际下面还有一个数据指向唯一主键索引
走索引的话会根据索引查出主键索引的值,在回表查询到这些行数据
(覆盖索引指的是查询的字段在索引中已经全部获得到了,不需要回表,效率会比较高)

先确认下每个索引的长度上一文章有说
id int类型4字节
name varchar类型 3n+2 74字节
age int类型 4字节
position varchar类型 3n+2 62字节
hire_time timestamp 类型 4字节

全值匹配

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

上图 索引长度74用到了联合索引中的name字段索引

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

上图 依旧是全值匹配 这个时候索引长度78 name+age = 74+4 =78

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

上图 同理 name+age+position = 74+4+62 = 140

最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。这个不是百分百的,要根据mysql底层b+树变种的结构来分析,而且有时候mysql引擎自己会有自己一套成本计算方式,走不走索引它也会自己做判断,虽然可以强制走索引,但是并不推荐

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

除了第一个语句走索引,2和3是不走索引的,试试就知道了

索引列计算,索引失效

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

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

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

函数日期查询,并不走索引;
转化为日期范围查询，有可能会走索引：只是可能有概率不走

EXPLAIN  select * from employees where hire_time >='2018-09-30 00:00:00'  and  hire_time <='2018-09-30 23:59:59';

很简单,想想mysql的b+tree就好了,tree的叶子节点值与值之间用的是双向指针关联
所以大于小于有顺序的是可以走索引的,但是使用函数是无法定位到索引节点的

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

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

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
//这个索引用到了name age  position  key_len = 140

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
//这个索引因为age > 22导致position的结果获取变得无序,无法根据position 的值精准定位到结果,可能继续用索引定位position的成本大于回表成本,导致不走这个索引也是有可能的
所以这里的索引只用到了name age key_len = 78

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

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

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

不等于不走索引

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

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

is null,is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

like以通配符%开头mysql索引失效会变成全表扫描操作

//索引会失效,如果非要用到全模糊查询,最好采取覆盖索引,不回表来优化
实在不行就上搜索引擎
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

前模糊查询会走索引

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

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

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

少用or或in

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，如果非要,最好可以保证or或in在400以内

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

这里,你觉得会走,但是实际上不一定会走
没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

范围查询优化

由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以将大的范围拆分成多个小范围

索引使用总结：

like KK%相当于=常量，%KK和%KK% 相当于范围

– mysql5.7关闭ONLY_FULL_GROUP_BY报错

select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));