MySql 索引实践一Mysql 索引实践示例示例表：全值匹配最左前缀法则不在索引列上做任何操作（计算、函数、

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

全值匹配

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

最左前缀法则

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

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

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

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

给hire_time 增加一个普通索引：

ALTER TABLE employees ADD INDEX id_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';

还原最初索引状态

ALTER TABLE employees DROP INDEX id_hire_time;

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

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

尽量使用覆盖索引（只访问索引的查询（也就是整个查询结果只在辅助索引中就可以一次性拿到，不需要通过辅助索引树找到主键，再通过主键索引树里获取其他字段值），减少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 以通配符开头（'%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).如果不能使用覆盖索引则可能需要借助搜索引擎

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

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

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

少用or或者in,用它查询时，mysql5.7 不一定使用索引，mysql5.7内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引，详见范围查询优化 -》（mysql8不管表的大小都会走索引）

EXPLAIN SELECT * FROM employees WHERE `name` = 'LiLei' OR `name` = 'HanMeiMei';

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

范围查询优化

#给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (age) USING BTREE;
EXPLAIN SELECT * FROM employees WHERE age >=1 AND age <=2000;	
	# mysql5.7有时使用索引范围查询不走索引的原因：mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引。（如这个例子在mysql5.7中将不会走索引，可能是由于单次数据量查询过大导致优化器最终选择不走索引
	# 优化方法： 可以将大范围拆分成多个小范围
ALTER TABLE `employees` DROP INDEX `idx_age`;

索引使用总结

假设index(a,b,c)

where 语句	索引是否被使用
where a = 3	Y,使用到a
where a=3 and b=5	Y,使用到a,b
where a=3 and b=5 and c=4	Y,使用到a,b,c
where b=3 或者 where b=3 and c=4 或者 where c=4	N
where a=3 and c=5	Y,使用到a,但是c不可以，b中间断了
where a=3 and b>4 and c=4	Y,使用到a和b,c不能在范围之后，b断了
where a=3 and b like 'kk%' and c=4	Y,使用到a,b,c
where a=3 and b like '%kk%' and c=4	Y, 只用到a
where a=3 and b like 'k%kk%' and c=4	Y, 使用到a,b,c

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

MySql 索引实践 一

Mysql 索引实践示例

示例表：

索引使用总结

MySql 索引实践一