MySql基础知识总结（SQL优化篇）

该篇的主要内容是通过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的编写过程