MySQL SQL语句分析与优化(下)

Alone
307 阅读4分钟

MySQL SQL语句分析与优化(下)

这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

前言:上篇讲解了关于MySQL的explain分析结果的一些关键字,如有未了解的可以去了解MySQL SQL语句分析与优化(下),这篇主要为大家讲解下关于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` ( `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());

上方SQL语句中,我们创建了一张员工表,并且建立了一个联合索引,字段为name,age,position,索引名称为idx_name_age_position

最左前缀原则

当我们查询使用到联合索引的时候,需要遵循最左前缀原则,意思是查询从索引的最左列开始,并且不跳过索引中的列 列子:

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

image.png

大家可以看到,显示结果告诉我们是走了索引的,两个const代表两个常量,正好对应了name与age,那么我们如果跳过name会怎么样

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

image.png 可以看到它是没有走索引的,type是ALL表示全表扫描了,因为我们跳过了name字段,因此无法走索引,那么我们不跳过name,跳过age的话会怎么样呢?

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

image.png

是不是走了索引,但是又没完全走索引?没错!因为我们跳过了age字段,但是没跳过name,因此name是可以走索引的,但是position由于age字段被跳过,因此无法走索引,注意如果age是范围查询,那么也会导致position无法走索引!

全值匹配

全值匹配的前提是得遵守最左前缀原则,其实就是遵循最左前缀原则,指查询的按索引创建的顺序,如下面这些SQL

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 and age = 22 position = manager;

以上的都会走索引,具体结果就不演示了!

尽量使用覆盖索引

尽量使用覆盖索引的意思是,访问索引的查询包含索引列,尽量不用select * 语句

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

尽量不在索引列上做任何操作

计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

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

image.png

少使用不等于

mysql在使用not in ，not exists 的时候大部分无法使用索引会导致全表扫描,

但是在使用！=或者<>,< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,主要跟数据量有关,很有可能只是因为不等于的数据占比很高，走索引不如全表扫描。

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

afde958612ba121a7820c05fa82a7a0.png

fa86be89692c0b8194feb997f797b53.png

同样一条SQL,只是因为数据量不同,导致的情况不同,走索引的时候,表只有几条数据,而没走索引的时候表中的数据虽然只有几十条,但也相对几条来说多了十倍左右,因此有的范围是否走索引跟数据量有关

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

EXPLAIN SELECT * FROM employees WHERE name is null

image.png

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

EXPLAIN SELECT * FROM employees WHERE name like '%LiLei';
EXPLAIN SELECT * FROM employees WHERE name like '%LiLei%';

image.png

上方两个SQL,无论是哪个都无法走索引,但是我们可以用索引覆盖的方法,让它走索引

使用覆盖索引，查询字段必须是建立覆盖索引字段

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

image.png

** 范围查询优化

# 首先添加年龄的单个索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
# 分析语句
explain select * from employees where age >=1 and age <=2000;

image.png

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

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

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

总结

还有很多SQL优化的小结,不一一演示了,再下方给大家总结起来:

  • 字符串不加单引号索引失效:如 name = '1000'; name = 1000; 前者会走索引,后者不会
  • 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,in需要理解小表驱动大表的原则,不懂的可以上网查找

优化总结口诀:

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

avatar
文章
阅读
粉丝