概述

本文所指的SQL优化主要是指MySQL优化，当然思想是通用的，其他SQL语言不排除其通用性。谈到SQL优化，最好有一个指导或者规范，照着这个规范去对比，发现可以优化的地方。参考博文MySQL推荐使用规范。

总体思路
优化的总体思路，也可以说是数据库性能优化方法论。遇到性能问题时，要判断是哪一种，然后才能知道需要优化什么，及如何优化。任何计算机应用系统最终性能瓶颈问题可以归结为：

• CPU消耗
• 内存使用
• 对磁盘，网络或其他I/O设备的I/O操作。

导致慢SQL的原因
在遇到慢SQL情况时，不能简单的把原因归结为SQL编写问题，实际上导致慢SQL有很多因素，甚至包括硬件和MySQL本身的bug。出现的概率从大到小排序：

• SQL编写问题
• 锁
• 业务实例相互干绕对 IO/CPU 资源争用
• 服务器硬件
• MYSQL BUG

数据库优化
数据库优化可以从架构优化，硬件优化，DB优化，SQL优化。呈倒三角，此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

架构优化

常见的优化手段有：分布式缓存，读写分离，分库分表。

分布式缓存

引入分布式缓存中间件如Redis，需要考虑缓存一致性问题，即如果DB数据变更后，缓存内的数据是否能及时同步更新；也需要应对极端情况，如：缓存穿透、缓存击穿和缓存雪崩的问题。

读写分离

读写分离，常用于读多写少的应用场景，通过增加数据库服务器节点，形成一主多从的架构，主库负责接受写请求，从库负责接受读（查询）请求。

主从之间，通过binlog同步数据。当准备实施读写分离时，为了保证高可用，需要实现故障的自动转移，主从架构会有潜在主从不一致性问题。

分库分表

水平切分：当应用业务数据量很大，单库容量成为性能瓶颈后，采用水平切分，可以降低数据库单库容量，提升数据库写性能。当准备实施水平切分时，需要结合实际业务选取合理的分片键（sharding-key），有时候为了解决非分片键查询问题还需要将数据写到单独的查询组件，如ElasticSearch。

总结：

1. 读写分离主要是用于解决数据库读性能问题
2. 水平切分主要是用于解决数据库数据量大的问题
3. 分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景

硬件优化

以机械硬盘、普通固态硬盘、PCIE固态硬盘三种不同的硬盘为例，来看他们的评测数据：

吞吐率：单位时间内读写的数据量
机械硬盘：约100MB/s ～ 200MB/s
普通固态硬盘：200MB/s ～ 500MB/s
PCIE固态硬盘：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次数
机械硬盘：100 ～200
普通固态硬盘：30000 ～ 50000
PCIE固态硬盘：数十万

可以明显发现：越昂贵的硬盘，其运行效率越高，对SQL执行的效率提升越明显。

DB优化

数据库实例参数优化遵循三句口诀：日志不能小、缓存足够大、连接要够用。

数据库事务提交后需要将事务对数据页的修改刷（fsync）到磁盘上，才能保证数据的持久性。这个刷盘，是一个随机写，性能较低，如果每次事务提交都要刷盘，会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法：

• 先将事务写到日志文件RedoLog（WAL），将随机写优化成顺序写
• 加一层缓存结构Buffer，将单次写优化成顺序写

所以日志跟缓存对数据库实例尤其重要。而连接如果不够用，数据库会直接抛出异常，系统无法访问。

常见的参数配置：

• innodb_buffer_pool_size：物理内存的50%-80%，越大性能越好
• innodb_log_buffer_size：16-32M，根据运行情况调整
• sync_binlog：可选值，1，100，0，1安全性最好
• max_connections：根据业务调整
• innodb_flush_log_at_trx_commit：2，安全和性能的折中考虑
• wait_timeout，interactive_timeout：28800，避免应用连接定时中断

SQL优化

提到SQL优化，一定要知道执行计划，也要能看懂执行计划。

常见的优化技巧：

1. 合理使用索引
   索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况
2. 使用UNION ALL替代UNION
   UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序
3. 避免select * 写法
   执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。
4. JOIN字段建议建立索引
   一般JOIN字段都提前加上索引
5. 避免复杂SQL语句
   提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
6. 避免where 1=1写法
7. 避免order by rand()类似写法
   RAND()导致数据列被多次扫描

通过show warning语句查看告警信息

分析诊断工具

在日常开发工作中，可以做一些工作达到预防慢SQL问题，比如在上线前预先用诊断工具对SQL进行分析。常用的工具有：

• mysqldumpslow
• mysql profile
• mysql explain

SQL优化过程

1. 定位有问题的语句
2. 检查执行计划
3. 检查执行计划中优化器的统计信息
4. 分析相关表的记录数、索引情况
5. 改写SQL语句、使用HINT、调整索引、表分析
6. 有些SQL语句不具备优化的可能，需要优化处理方式
7. 达到最佳执行计划

SQL优化方法

1. 优化业务数据
2. 优化数据设计
3. 优化流程设计
4. 优化SQL语句
5. 优化物理结构
6. 优化内存分配
7. 优化I/O
8. 优化内存竞争
9. 优化操作系统

优化目标：

1. SQL语句尽量简单，模块化；
2. 易读，易维护；
3. 节省资源：内存、CPU、扫描的数据块要少、少排序等；
4. 不造成死锁等。

SQL优化层级
应用程序级调优

• sql语句调优
• 管理变化调优

实例级调优

• 内存
• 数据结构
• 实例配置

操作系统交互

• I/O
• swap
• Parameters

关于SQL语句的优化的文章不要太多，本文尝试做一个总结，并试图大致分类一下，当然这个分类可能并不严格：

• 执行计划
• 索引
• SELECT
• IN, OR, UNION
• where
• 临时表

EXPLAIN执行计划

基本用法

desc/explain sql;
extended explain sql;
通过show warnings可以查看实际执行的语句

提高性能的特性

索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index；
ICP特性(Index Condition Pushdown)：本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤。5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL Server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解MySQL的架构图分为Server和存储引擎层；
索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引。

extra字段

• using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”，其实不一定是文件排序，内部使用的是快排；
• using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by；
• using index：表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错；
• impossible where：WHERE子句的值总是false，不能用来获取任何元组；
• select tables optimized away：在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；
• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作。

using filesort、using temporary十分耗费性能，在使用group by时，虽然没有使用order by，如果没有索引，是可能同时出现using filesort，using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

type字段

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现；

• const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量；

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引；

• index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘读取的；

• all：Full Table Scan，遍历全表获得匹配的行。

• EXPLAIN
  拿到慢SQL后，先用explain运行一下，查看SQL执行计划。关于explain的结果的解读，参考博文MySQL Explain详解。重点关注下面5个指标数据

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。

• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。

• key_len列，索引长度。

• rows列，扫描行数。该值是个预估值。

• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

当只需要一条数据或者明确知道只有一条返回结果时，使用limit 1
使EXPLAIN中type列达到const类型；数据库并不知道只有一条数据，加上limit 1让它主动停止游标移动；

索引

• 检查索引：在SQL语句的WHERE和JOIN部分中用到的所有字段上，都应该加上索引。
• 不要在区分度不大的字段建立索引
  在数据区分度不大的字段使用索引，不但不会降低逻辑I/O，相反往往会增加大量逻辑I/O降低性能。如性别列，男和女。经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。
• 避免在索引列上使用IS NULL或者NOT
  避免在索引中使用任何可以为空的列，导致无法使用索引
• 对于联合索引来说，要遵守最左前缀法则
  举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。
• 必要时使用force index来强制查询走某个索引
  有时MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是想要的。此时采用force index来强制优化器使用指定的索引。
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 如果排序字段没有用到索引，就尽量少排序
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
• 尽量使用最左匹配的模糊查询：name like 'ABC%'方式，优于name like '%ABC%'。

SELECT

• SELECT语句务必指明字段名称
  select * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。额外的字段通常会增加返回数据的纹理，从而导致更多的数据被返回到SQL客户端。使用带有报告和分析功能的应用程序时，有时报告性能低是因为报告工具必须对收到的、带有详细形式的数据做聚合操作。当使用一个面向列的DBMS时，只有你选择的列会从磁盘读取。在你的查询中包含的列越少，IO开销就越小。所以要求直接在select后面接上字段名。及时是需要查询所有列时，也不要使用select *，sql解析时，需要把“*”依次转换为所有的列名，这个工作需要查询数据字典完成！

IN, OR, UNION

• SQL语句中IN包含的值不应过多
  MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。
• 如果限制条件中其他字段没有索引，尽量少用or
  or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20，可以这样查询：select id from t where num=10 union all select id from t where num=20
• 尽量用union all代替union
  union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

where

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  例子：select user_id,user_project from user_base where age*2=36;会造成引擎放弃使用索引，建议改成：select user_id,user_project from user_base where age=36/2;
• 避免在where子句中对字段进行null值判断。对于null的判断会导致引擎放弃使用索引而进行全表扫描。如select id from t where num is null，可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
• 避免隐式类型转换
  where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。
• 注意范围查询语句
  对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
• 负向条件查询不能使用索引
  举例select * from order where status!=0 and stauts!=1，not in/not exists都不是好习惯，可以优化为in查询：select * from order where status in(2,3)

临时表

• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。
• 临时表并不是不可使用，适当地使用可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，较好使用导出表。
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
• 如果使用到临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

JOIN

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

• MySQL中没有full join，可以用以下方式来解决：select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
• 尽量使用inner join，避免left join
  参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
• 合理利用索引
  被驱动表的索引字段作为on的限制字段。
• 利用小表去驱动大表
  
  如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。
• 巧用STRAIGHT_JOIN
  inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。这个方式有时能减少3倍的时间。
  

other

其他一些优化技巧：

• 不使用ORDER BY RAND()
  举例来说，select id from dynamic order by rand() limit 1000;这条SQL可以优化为：select id from dynamict1 join (select rand() * (select max(id) fromdynamic) as nid) t2 on t1.id > t2.nidlimit 1000;

• 批量提交SQL
  可以减少网络来回损耗，IO请求等；如执行一个大的DELETE或INSERT语句，因为这两个操作会锁表，表锁住后，别的操作都进不来。

• 强制类型转换会全表扫描：select name from user where phone=13800001234并不能走索引，phone字段应该定义为varchar；

• 限制工作数据集的大小
  检查那些SELECT语句中用到的表，看看你是否可以应用WHERE子句进行过滤。一个典型的例子是，当表中只有几千行记录时，一个查询能够很好地执行。但随着应用程序的成长，查询慢了下来。解决方案或许非常简单，限制查询来查看当前月的数据即可。
  当你的查询语句带有子查询时，注意在子查询的内部语句上使用过滤，而不是在外部语句上。

• 移除不必要的表
  移除不必要的表的原因，和移除查询语句中不需要的字段的原因一致。
  编写SQL语句是一个过程，通常需要大量编写和测试SQL语句的迭代过程。在开发过程中，你可能将表添加到查询中，而这对于SQL代码返回的数据可能不会有任何影响。一旦SQL运行正确，我发现许多人不会回顾他们的脚本，不会删除那些对最终的返回数据没有任何影响和作用的表。通过移除与那些不必要表的JOINS操作，你减少了大量数据库必须执行的流程。有时，就像移除列一样，你会发现你减少的数据又通过数据库返回来了。

• 移除外部连接查询
  它取决于改变表的内容有多大的影响。一个解决办法是通过在两个表的行中放置占位符来删除OUTER JOINS操作。假设你有以下的表，它们通过定义OUTER JOINS来确保返回所有的数据：

解决办法是在customer表的行中增加一个占位符，并更新sales表中的所有NULL值到占位符。

6. 删除JOIN和WHERE子句中的计算字段
   它取决于你更改表模式的权限大小。可以将连接语句中用到的计算字段作为一个新字段在表中创建。给出以下SQL语句：
   FROM sales a JOIN budget b ON ((YEAR(a.sale_date)*100) + MONTH(a.sale_date)) = b.budget_year_month
   在sales表中利用年和月增加一列，可以提高性能。更新后的SQL语句将如下:
   SELECT * FROM PRODUCTS FROM sales a JOIN budget b ON a.sale_year_month = b.budget_year_month

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num

12、不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，就应该考虑改写。使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONEINPROC 消息。
29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
30、尽量避免大事务操作，提高系统并发能力。

总结

• 检查索引
• 在所需要的最小数据集上操作
• 移除不必要的字段和表
• 移除JOIN和WHERE子句中的计算操作

MySQL优化check清单

1. 上面索引失效的各种情况就是优化清单其一；
2. 使用 InnoDB 存储引擎使用 InnoDB 存储引擎
   InnoDB 比 MyISAM 更有优势，它们是如何利用物理内存的：
   MyISAM：仅在内存中保存索引。
   InnoDB：在内存中保存索引和数据。
   表转换存储引擎的命令：ALTER TABLE table_name ENGINE=InnoDB;
3. 任何地方都不要使用select * from t，用具体的字段列表来代替，不要返回用不到的任何字段。哪怕需要查询30个字段也全部写出来，借助于DataGrip工具，Alt + Enter快捷键可以一键生成数据表的全部字段，然后按需删除不需要返回的字段。原因：避免MySQL去分析表，获取表里面的全部字段。
4. InnoDB高内存多任务运行
   对于缓冲池数量的官方建议是：为了实现最佳的效果，要综合考虑 innodb_buffer_pool_instances 和 innodb_buffer_pool_size 的设置，以确保每个实例至少有不小于 1 GB 的缓冲池。innodb_buffer_pool_size 建议设置为服务器物理内存的 80%。innodb_buffer_pool_size 设置为大于1；配置多个缓冲池的好处显而易见：通过启用多缓冲池来最小化多线程资源争用情况。修改my.cnf，重启生效：sudo service mysql restart

参考
高效sql性能优化极简教程
项目中常用的19条MySQL优化