该文前置知识：

• SQL优化前置知识之优化前分析
• SQL优化前置知识之索引的正确使用姿势

数据库的性能在系统中非常重要，在前面了解了Redis高可用的背景下也了解到了许多问题都会给数据库施加非常大的压力，一旦数据库崩溃会带来非常严重的后果乃至整个系统崩溃，对数据库性能的优化主要方法是：

1. SQL 语句及索引的优化
2. 数据库表结构的优化
3. 系统配置的优化
4. 硬件的优化

从效果上第一条影响最大，后面越来越小。这篇文章就主要来阐述SQL的优化方法。

注：在本篇文章的编写过程中，我发现mysql5.7版本对某些sql语句已经有了自动优化机制，但还是建议了解sql语句的优化，毕竟版本的事总不如脑子里的东西稳。

优化load data

当使用load data infile命令导入数据的时候，适当的设置可以提高导入的效率。

主键顺序插入

因为聚簇索引的关系，如果主键的值是顺序的，那么 InnoDB 会把每一条记录都存储在上一条记录的后面，填充的是同一个页，减少了IO页的次数。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

关闭唯一性校验

如果可以确定插入的数据满足唯一性约束，可以在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

合并insert语句

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

示例， 原始方式为：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

优化后的方案为 ：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

在事务中进行数据插入

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

数据有序插入

和load data一样的原理

优化join

这部分的内容在数据库理论之查询处理和查询优化谈得比较详细，总的来说是要先选择再连接。

小表驱动大表

如果有两张表连接，类似于二重循环

外层的表应该是小表，内层的应该是大表。

虽然总的遍历次数是一样的，但是频繁切换数据表是影响效率的（IO 次数），应该尽可能减少切换表的次数。

这方面内容可以参考：MySql 小表驱动大表 - 茶饭不撕 - 博客园

优化order by语句

了解：两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

可以在explain中看到sql语句使用的是哪种排序方法。

优化目标：尽量减少额外的排序，通过索引直接返回有序数据。

对于索引(a,b,c)，不能使用索引进行排序的情况：（应避免）
+ ORDER BY a ASC, b DESC, c DESC /排序不一致/
+ WHERE g = const ORDER BY b, c /不满足最左前缀/
+ WHERE a in (...) ORDER BY b, c /对于排序来说，范围in也不能满足最左前缀/

了解：filesort的算法

有的时候并不能避免filesort，该如何优化filesort就得了解它的底层实现。

双路排序：

MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据。

它的工作过程：读取行指针和 orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出

概括： 从磁盘取排序字段,在 buffer进行排序,再从磁盘取其他字段。

然而取一批数据,要对磁盘进行了两次扫描,众所周知,IO是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序：

单路排序的工作过程： 从磁盘读取查询需要的所有列,按照 order by列在 buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

单路排序问题：

在 sort_buffer 中单路排序比双路排序要多占用很多空间，因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排……从而多次 I/O。本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化方式

1) 尽量使用 index 方式排序，遵照索引的最佳左前缀

排序时使用的字段的顺序最好与 index 建立的顺序相同

ORDER BY满足两种情况，会使用Index方法排序：
1. ORDER BY语句使用索引最左前缀列
2. 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前缀列

例：WHERE a = const AND b = const ORDER BY c是允许的

2) select * 是一个大忌

只取出需要的字段， 这点非常重要。在这里的影响是：

1. 尽量使用覆盖索引，可以直接从索引树中读取数据，避免二次查询聚簇索引，进而导致filesort。
2. 当Query的字段大小总和小于 max_length_for_sort_data 而且排序字段不是TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
3. 避免取出太多数据使得占用内存超过 sort_buffer_size 而采用多路排序。

3) 尝试提高 sort_buffer_size 和 max_length_for_sort_data

提高这两个参数，会增加用改进算法的概率。两个参数最好达成一种平衡，否则如果max_length_for_sort_data设的太高，数据总容量超出sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。

优化GROUP BY语句

group by实质是先排序后进行分组,遵照索引的最佳左前缀规则，所以也尽量满足该规则。

当无法使用索引时，GROUP BY 使用两种策略来完成：使用临时表和filesort来做分组。表现为use temporary和use filesort。

当无法使用索引列：
+ 可以优化filesort，即增大sort_buffer_size参数和max_length_for_sort_data参数。
+ 可以只使用临时表，不进行filesort，这样返回的结果是无序的，想要加上ORDER BY null

另，避免使用having，where高于having，能写在where限定的条件就不要去having限定了。

优化嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

事实上，MySQL5.7已经会自动优化一些可以被转成join的sql语句了，临时表开始被避免使用。

示例：

select * from t_user where id in (select user_id from user_role );

优化为：

select * from t_user u , user_role ur where u.id = ur.user_id;

优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。（避免索引失效）

如果无法增加索引，还可以考虑使用union合并有索引的那些查询出来的和没索引查询出来的结果集代替or。（在数据量小的情况下不推荐，用到临时表有的时候效率更低）

优化limit语句

当偏移量非常大的时候，比如 limit 100000,20 这样的查询，这时 MySQL 需要查询 100020 条记录然后只返回最后 20 条，这样的代价非常高。要优化这种查询，要么在页面中限制分页数量，要么优化大偏移的性能。

一个简单的办法是使用覆盖索引（延迟关联）

意思就是先把索引排序，取出100000-20的索引值，然后用这些数据再回表查询对应行的所有列的值。使用子查询的写法就是：

select * from tb_item t, 
(select id from tb_item order by id limit 100000,20) a where t.id=a.id;

另一种方法，如果使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描。假设主键递增，那么当查询limit 100000,20返回的最后一条数据是id= 1000102，那么下一次查询可以使用where id>1000102来限定范围。

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

但是除非你的优化经验很好，否则不建议使用hint强制要求使用某个索引，因为至今MySQL本身对sql的优化程度已经很高了 : )

USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint。

FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint。