MySql知识体系总结（SQL优化篇）

作者：微信小助手

发布时间：2021-07-24T07:28:39

本篇是MySQL知识体系总结系列的第二篇，该篇的主要内容是通过explain逐步分析sql，并通过修改sql语句与建立索引的方式对sql语句进行调优，也可以通过查看日志的方式，了解sql的执行情况，还介绍了MySQL数据库的行锁和表锁。


一、explain返回列简介

1、type常用关键字

system > const > eq_ref > ref > range > index > all。

  1. system：表仅有一行，基本用不到；

  2. const：表最多一行数据配合，主键查询时触发较多；

  3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

  4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

  5. range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

  6. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

  7. all：全表扫描；

实际sql优化中，最后达到ref或range级别。

2、Extra常用关键字

Using index：只从索引树中获取信息，而不需要回表查询；


Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。


Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

二、触发索引代码实例

1、建表语句 + 联合索引

CREATE TABLE `student` (  `id` int(10) NOT NULL,  `name` varchar(20) NOT NULL,  `age` int(10) NOT NULL,  `sex` int(11) DEFAULT NULL,  `address` varchar(100) DEFAULT NULL,  `phone` varchar(100) DEFAULT NULL,  `create_time` timestamp NULL DEFAULT NULL,  `update_time` timestamp NULL DEFAULT NULL,  `deleted` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `student_union_index` (`name`,`age`,`sex`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、使用主键查询

3、使用联合索引查询

4、联合索引，但与索引顺序不一致

备注：因为mysql优化器的缘故，与索引顺序不一致，也会触发索引，但实际项目中尽量顺序一致。

5、联合索引，但其中一个条件是 >

6、联合索引，order by

where和order by一起使用时，不要跨索引列使用。


三、单表sql优化

1、删除student表中的联合索引。

2、添加索引

alter table student add index student_union_index(name,age,sex);

优化一点，但效果不是很好，因为type是index类型，extra中依然存在using where。

3、更改索引顺序

因为sql的编写过程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

解析过程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

 因此我怀疑是联合索引建的顺序问题，导致触发索引的效果不好。are you sure？试一下就知道了。

alter table student add index student_union_index2(age,sex,name);

删除旧的不用的索引：

drop index student_union_index on student

索引改名

ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index

更改索引顺序之后，发现type级别发生了变化，由index变为了range。

range：只检索给定范围的行，使用一个索引来选择行。

备注：in会导致索引失效，所以触发using where，进而导致回表查询。

4、去掉in

ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

index 提升为ref了，优化到此结束。

5、小结

  1. 保持索引的定义和使用顺序一致性；

  2. 索引需要逐步优化，不要总想着一口吃成胖子；

  3. 将含in的范围查询，放到where条件的最后，防止索引失效；

四、双表sql优化

1、建表语句

CREATE TABLE `student` (  `id` int(10) NOT NULL,  `name` varchar(20) NOT NULL,  `age` int(10) NOT NULL,  `sex` int(11) DEFAULT NULL,  `address` varchar(100) DEFAULT NULL,  `phone` varchar(100) DEFAULT NULL,  `create_time` timestamp NULL DEFAULT NULL,  `update_time` timestamp NULL DEFAULT NULL,  `deleted` int(11) DEFAULT <