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

插入3条数据：

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());

1. 全值匹配

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

 

2. 最左前缀法则

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

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

 

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

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

转化为日期范围查询，有可能会走索引：

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

 

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

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

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

explain select name, age from employees where name='LiLei' and age=20 and position='manager';

explain select * from employees where name='LiLei' and age=23 and position='manager';

6. mysql在使用不等于( !=或者<>），not in， not exists的时候无法使用索引会导致全表扫描

<小于、>大于、<=、>=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体是否使用索引

explain select * from employees where name!='LiLei';

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

explain select * from employees where name is not null;

 

8. like以通配符开头('$abc...') mysql索引失效会变成全表扫描操作

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)  如果不能使用覆盖索引则可能需要借助搜索引擎

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

explain select * from employees where name=1000;

 

10. 少用or或in, 用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

explain select * from employees where name='LiLei' or name='HanMeiMei';

 

11. 范围查询优化

给年龄添加单值索引

alter table `employees` add index `idx_age` (`age`) using btree;
explain select * from employees where age>=1 and age<=2000;

 

 优化方法：将大的范围拆分成多个小范围。

索引使用总结：