SQL优化

序

本文以开发人员的角度介绍SQL优化，旨在帮助开发人员在开发过程中书写出高性能SQL语句，若是专业DBA级别数据库优化请忽略。

1. 系统常见瓶颈

系统常见的问题：性能下降、SQL慢、执行时间长、等待时间长等，通常是由于查询数据过多、关联了太多的表（太多join）、没有利用到索引引起的。

对于以上问题，我们应该分析从业务上能不能拆，条件过滤，尽量不要查询过多的数据。太多join时尽量先过滤（join原理：用A表的每一条数据扫描B表的所有数据，所以尽量先过滤）。索引是针对列建索引，但并不可能每一列都建索引，索引并非越多越好。当数据更新了，索引会进行调整，也会很消耗性能。而且MySQL并不会把所有索引都用上，只会根据其算法挑一个索引用，所以建的准很重要。服务器调优及各个参数设置（缓冲、线程数等）。

数据库常见瓶颈：SQL中对大量数据进行比较、关联、排序、分组。实例内存满足不了缓存数据或排序等需要，导致产生大量物理IO。查询执行效率低，扫描过多数据行。不适宜的锁的设置，导致线程阻塞，性能下降。线程之间交叉调用资源，导致死锁，程序卡住。服务器硬件的性能瓶颈。

2. SQL性能分析

下面将以MySQL为例，详细介绍SQL性能分析。

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，进而分析你的查询语句或是表结构的性能瓶颈。通过执行计划，我们可以知道表的读取顺序、哪些索引可以使用、数据读取操作的操作类型、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询等，使用语法为EXPLAIN+SQL语句。

2.1 id

SQL查询中的序列号，id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

id相同，从上往下顺序执行（t1_count：10，t2_count：20，t3_count：30）：

需要注意的是，表的执行顺序因数量的个数改变而改变，数据量小的表优先查询（t1_count：21，t2_count：20，t3_count：30）：

id值不同，id值越大越优先查询：

id值有相同，又有不同时，id值越大越优先，id值相同，从上往下顺序执行。

2.2 select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION：

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY：

SUBQUERY：子查询中的第一个SELECT：

DERIVED：衍生表，在FROM列表中包含的子查询被标记为DERIVED。情况一，在FROM子查询中只有一张表：

这里我们发现执行计划中的select_type并没有DERIVED，而是SIMPLE，原因是因为我使用的版本为5.7，MySQL的Optimizer（查询优化器）对我们的SQL进行了优化，使用explain extended结合show warnings查看优化器对我们的SQL优化的结果，可以发现优化器将我们的子查询优化成了简单查询：

使用5.5版本后再查看该SQL的执行计划，就可以看到DERIVED了：

DERIVED：情况二，在FROM子查询中，如果有table1 UNION table2，则table1就是DERIVED，table2就是UNION：

DEPENDENT SUBQUERY：子查询中的第一个SELECT，子查询基于外层：

这里我们发现执行计划中的select_type并没有DEPENDENT SUBQUERY，而是SIMPLE，原因和上面DERIVED情况一相同，MySQL5.7的Optimizer（查询优化器）对我们的SQL进行了优化，使用explain extended结合show warnings查看优化器对我们的SQL优化的结果，可以发现优化器将我们的子查询优化成了连接查询：

我们换个方式，使用EXISTS关键字来实现该SQL，发现MySQL5.7就没有对此方式进行优化：

使用5.5版本后再查看该SQL的执行计划，就可以看到DEPENDENT SUBQUERY了：

MATERIALIZED：物化子查询。MySQL5.6版本中加入的一种子查询优化策略，就是把子查询结果物化成临时表，然后代入到外查询中进行查找，来加快查询的执行速度。这样子查询只需要执行一次，而不是像DEPENDENT SUBQUERY那样对于外层查询的每一行都得执行一遍。如下所示，我们使用explain extended结合show warnings查看优化器对我们的SQL优化的结果，可以发现优化器将我们的子查询优化成了连接查询，并且使用了物化子查询（注：MySQL会对我们的SQL进行很多优化，如需了解更多请自行查阅官方文档，这里就不一一列举了）：

UNCACHEABLE SUBQUREY：结果集无法缓存的子查询，必须重新评估外部查询的每一行（@@表示查的环境参数，没办法缓存）：

UNION：UNION中第二个或之后的SELECT语句（若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED）：

UNION RESULT：UNION的结果：

2.3 table

查询的表名。不一定是实际存在的表名，可以为如下的值：

• <unionM,N>：引用id为M和N UNION后的结果。
• ：引用id为N的结果派生出的表，派生表可以是一个结果集，例如派生自FROM中子查询的结果。
• ：引用id为N的子查询结果物化得到的表，即生成一个临时表保存子查询的结果。

2.4 partitions

5.7版本以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况，非分区表该字段为NULL。

2.5 type

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

下面将着重介绍常见type，常见的为：system > const > eq_ref > ref > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。

system：表中只有一行数据或者是空表，这是const类型的一个特例，且只能用于MyISAM和Memory表，如果是InnoDB引擎表，type列在这个情况通常都是ALL或者index。

const：最多只有一行记录匹配，当primary key或unique索引的字段跟常量值比较时，查询类型为const（类型与索引类型有关）。其他数据库也叫做唯一索引扫描：

eq_ref：多表join时，对于来自前面表的每一行，在当前表中只能找到一行（有且只有1个，不能多、不能0），当primary key或unique索引的字段被用作join联接时会使用此类型：

eq_ref可用于使用=操作符作比较的索引列，比较的值可以是常量（此时查询类型为const），也可以是使用在此表之前读取的表的列的表达式：

ref：对于来自前面表的每一行，在此表的索引中可以匹配到多行（0行或多行），若连接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）：

ref和eq_ref的区别：eq_ref使用的是唯一索引（主键或唯一索引），而ref使用的是非唯一索引或者普通索引。eq_ref只能找到一行（有且只有1个，不能多、不能0），而ref能找到多行（0行或多行）。

ref可用于使用=或<=>操作符作比较的索引列：

range：索引范围查询，常见于使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN、LIKE等运算符的查询中。

index：索引全表扫描，把索引从头到尾扫一遍。查询使用了覆盖索引，它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。该情况会在Extra中显示Using index，反之，如果在索引上进行全表扫描，则没有Using index的提示：

ALL：全表扫描，性能最差：

fulltext：使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL不管代价，优先选择使用全文索引。

ref_or_null：跟ref类型类似，只是增加了null值的比较，实际用的不多。

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and，or的条件使用了不同的索引，MySQL官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)。

index_subquery：该联接类型类似于unique_subquery，适用于非唯一索引，可以返回重复值。

2.6 possible_keys

查询可能使用到的索引都会在这里列出来。

2.7 key

查询真正使用到的索引。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

2.8 key_len

查询用到的索引长度（字节数）。如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

2.9 ref

如果是使用常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

2.10 rows

rows也是一个重要的字段，这是MySQL估算的需要扫描的行数（不是精确值），这个值非常直观显示SQL的效率好坏，原则上rows越少越好。

2.11 filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数，这个字段不重要。

2.12 Extra

EXPLAIN中的很多额外的信息会在Extra字段显示，常见的有以下几种内容：

Using filesort：当Extra中有Using filesort时，表示MySQL需额外的排序操作，不能通过索引顺序达到排序效果。一般有Using filesort，都建议优化去掉，因为这样的查询CPU资源消耗大。

Using temporary：查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化。

Using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。如果同时出现Using where，表明索引被用来执行索引键值的查找，如果没有同时出现Using where，表明索引只是用来读取数据而非利用索引执行查找。

Using where：表明使用了where过滤。

Using join buffer：使用了连接缓存。

distinct：在select部分使用了distinc关键字。

impossible where：where子句的值总是false，不能用来获取任何数据。

3. SQL优化

1. 最佳左前缀法则：对于复合索引，查询应从索引的最左前列开始并且不跳过索引中的列，否则索引失效。

2. 在索引列上操作（计算、函数、自动或手动类型转换），会导致索引失效。

3. 范围条件若有索引则能使用到索引，对于复合索引，范围条件右边的索引会失效（范围条件右边与范围条件使用的同一个复合索引，右边的才会失效，若是不同索引则不会失效）。

4. 尽量使用覆盖索引（只访问索引的查询，即索引列和查询列一致），避免select *。

5. MySQL在使用不等于（!=或者<>）的时候无法使用索引，is not null也无法使用索引，但是is null是可以使用索引的。

6. like以通配符开头会导致索引失效。

7. 如果列类型是字符串，那一定要在where条件中将数据使用单引号引用起来，否则索引列发生了自动类型转换，导致索引失效。

8. 尽量避免在where子句中使用or来连接条件，否则索引失效，可以使用union来代替。

9. 如果MySQL估计使用全表扫描要比使用索引快，也会不使用索引。

10. 对于单键索引，尽量选择针对当前查询过滤性更好的索引。如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说某个数据列里包含了净是些诸如0/1或Y/N等值，就没有必要为它创建一个索引。

11. 在选择复合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

12. 如果在where子句中使用参数，也会导致全表扫描，因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择到运行时，它必须在编译时进行选择。然而，如果在编译时执行访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

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

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

15. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

16. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

17. 尽量避免大事务操作，提高系统并发能力。

18. 在join操作中，保证被驱动表的join字段已经被索引。left join时，选择小表作为驱动表，大表作为被驱动表。inner join时，MySQL会自己帮你把小结果集的表选为驱动表。子查询尽量不要放在被驱动表，因为我们不可能对子查询产生的衍生表创建索引，所以可将子查询设置为驱动表，在被驱动表的join字段创建索引。

19. 在join操作中，MySQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。

20. 包含exists和in的子查询，如果主查询的数据集大，则使用in效率高，如果子查询的数据集大，则使用exists效率高（最佳方式是使用join代替）。

21. ORDER BY子句，尽量使用Using index方式排序，避免使用Using filesort方式排序。Using filesort有两种算法（根据IO的次数区分）：双路排序、单路排序 。MySQL4.1之前默认使用双路排序，双路表示需要扫描2次磁盘（1：从磁盘读取排序字段和主键，在buffer中对排序字段进行排序。2：根据排好序的主键回表取出其他字段）。MySQL4.1之后默认使用单路排序（只读取一次，读取的是全部字段，在buffer中进行排序）。但种单路排序会有一定的隐患，不一定真的是单路（1次IO，有可能多次IO），因为如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行分片读取、多次读取。可以增大sort_buffer_size（单路排序的内存大小）、max_length_for_sort_data（单次排序请求数据的大小）参数的设置，去掉select后面不需要的字段来优化排序。

22. GROUP BY实质是先排序后进行分组，和ORDER BY一样遵照最佳左前缀法则，where优先于having执行，能写在where限定的条件就不要去having限定了。

23. 尽量不要使用DISTINCT关键字去重，使用GROUP BY优化，因为GROUP BY可以走索引。