MySQL 索引实践 使用的表 最佳实践 小总结 参考文档

发表于

「这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战」

使用的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sql复制代码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 USING BTREE (name, age, position)
) ENGINE = InnoDB AUTO_INCREMENT = 4 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());

最佳实践

  1. 全值匹配
1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

image.png

2.最佳左前缀法则

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

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE position = 'manager';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

image.png

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

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

image.png

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

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

image.png

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

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

image.png

6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

image.png

7.is null,is not null 也无法使用索引

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name is null;

image.png

8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name like '%Lei';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';

image.png
问题：解决like’%字符串%’索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段

1
sql复制代码EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b）当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

9.强制类型转换导致索引失效

强制类型转换导致索引失效, 比如：字符串不加单引号索引失效

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name = '1000';

image.png

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name = 1000;

image.png

10.少用or,用它连接时很多情况下索引会失效

1
sql复制代码EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

image.png

小总结

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

总结 1

总结 2

参考文档

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%