DB
MySQL

索引优化一

示例数据

1
2
3
4
5
6
7
8
9
10
11
12
13
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());

全值匹配

通过key_len和ref可以明显的看出生效的索引个数

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

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

1
2
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在执行时会做一些顺序优化，一下三条SQL语句都是使用到了索引，explain出来的结果一样

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

一下两个SQL由于跳过了索引中的name列，导致索引失效

1
2
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

以下情况任然可以用到name列的索引

1
2
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE position ='manager' AND name= 'LiLei';

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

对name列做left计算导致索引失效

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

给hire_time增加一个普通索引，查询时进行日期类型转换，导致索引失效

1
2
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';

转化为日期范围查询，有可能会走索引，具体是否会走索引，MySQL底层会做一些评估

1
2
EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <= '2018‐09‐30 23:59:59';
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

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

以下两种情况一样仅联合索引的name列和age生效了

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

尽量使用覆盖索引

减少select *语句，只访问索引的查询，即索引列包含查询列。下面的例子使用了覆盖索引：

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

下面的情况是未使用覆盖索引的情况，先从联合索引中查出数据的主键索引，然后再回表到主键索引中查询数据

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

使用不等于

在使用不等于!=，<>，not in，not exists时候无法使用索引会导致全表扫描；使用范围查询<、 > 、 <=、>=等，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

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

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

1
EXPLAIN SELECT * FROM employees WHERE name is null

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

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

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

1
2
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%33%';

解决like’%字符串%’索引不被使用的方法，使用覆盖索引，查询字段必须是建立覆盖索引字段，如果不能使用覆盖索引则可能需要借助搜索引擎

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

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

1
EXPLAIN SELECT * FROM employees WHERE name = 1000;

少用or或in

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

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

范围查询优化

给年龄添加单值索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，可能由于单次数据量查询过大导致优化器最终选择不走索引，优化方法可将大的范围拆分成多个小范围

1
2
3
4
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE;
explain select * from employees where age >=1 and age <=2000;
explain select * from employees where age >=1 and age <=10;
ALTER TABLE `employees` DROP INDEX `idx_age`;