SQL语句优化

mysql SQL优化
创建于：2019年09月02日 更新于：2019年09月04日

SQL语句优化基本思路和原则

一般来说，Query语句的优化思路和原则主要体现在以下几个方面：

（1）优化更需要优化的Query；
（2）定位优化对象的性能瓶颈；
（3）明确优化目标；
（4）从Explain入手；
（5）多使用Profile；
（6）永远用小结果集驱动大的结果集；
（7）尽可能在索引中完成排序；
（8）只取自己需要的Columns；
（9）仅仅使用最有效的过滤条件；
（10）尽可能避免复杂的Join和子查询。

上面所列的几点信息，前面5点可以理解为Query优化的一个基本思路，后面部分则是优化的基本原则。

优化更需要优化的Query
一般来说，高并发低消耗（相对）的Query对整个系统的影响远比低并发高消耗的大。下面可以通过以下一个非常简单的案例分析充分说明问题。

假设有一个Query每小时执行10000次，每次需要20个IO，而另外一个Query每小时执行10次，每次需要20000个IO。

首先通过IO消耗来分析。可以看出，两个Query每小时所消耗的IO总数目是一样的，都是200000IO/小时。假设优化第一个Query，从20个IO降低到18个IO，也就是降低了2个IO，则节省了2×10000＝20000（IO/小时）。而如果希望通过优化第二个Query达到相同的效果，必须要让每个Query减少20000／10＝2000IO。可以看出第一个Query节省2个IO即可达到第二个Query节省2000个IO相同的效果。

其次，通过CPU消耗来分析。原理和上面一样，只要让第一个Query节省一小块资源，就可以让整个系统节省出一大块资源，尤其是在排序、分组这些对CPU消耗比较多的操作中更加明显。

定位优化对象的性能瓶颈
在拿到一条须要优化的Query之后，首先要判断出这个Query的瓶颈到底是IO还是CPU，到底是因为在数据访问上消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源。

永远用小结果集驱动大的结果集
不是使用小表驱动大表，因为大表经过Where条件过滤后，返回的结果集不一定比小表大。

尽可能在索引中完成排序
利用索引进行排序操作，主要是利用了索引的有序性。在通过索引进行检索的过程中，就已经得到了有序的数据访问顺序，依次读取结果数据后就不须要进行排序操作，进而避免了此操作，提高了需要有序结果集的Query的性能。

只取出自己需要的字段
对于任何Query，返回的数据都须要通过网络数据包传回给客户端，取出的字段越多，须要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，这都是一个浪费。

仅仅使用最有效的过滤条件
分析Query语句的性能优劣最关键的就是要让它选择一条最佳的数据访问路径，做到通过访问最少的数据量完成自己的任务。

尽可能避免复杂的Join和子查询
Query语句所涉及的表越多，须要锁定的资源就越多。也就是说，越复杂的Join语句，锁定的资源也就越多，所阻塞的其他线程也就越多。相反，如果将比较复杂的Query语句分拆成多个较为简单的Query语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。

充分利用Explain和Profiling

Explain
MySQL Query Optimizer通过执行EXPLAIN命令来告诉我们它将使用一个怎样的执行计划来优化Query。所以，可以说Explain是在优化Query时最直接有效地验证我们想法的工具。
在对某个Query优化过程中，须要不断地使用Explain来验证各种调整是否有效。所有的Query优化都应该充分利用它。

下面看一下在MySQL Explain功能中展示各种信息的解释。

1.ID：MySQL Query Optimizer选定的执行计划中查询的序列号。

2.Select_type：所使用的查询类型，主要有以下这几种查询类型。

  • DEPENDENT SUBQUERY：子查询内层的第一个SELECT，依赖于外部查询的结果集。
  • DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。
  • PRIMARY：子查询中的最外层查询，注意并不是主键查询。
  • SIMPLE：除子查询或UNION之外的其他查询。
  • SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。
  • UNCACHEABLE SUBQUERY：结果集无法缓存的子查询。
  • UNION：UNION语句中第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。
  • UNION RESULT：UNION中的合并结果。

3.Table：显示这一步所访问的数据库中的表的名称。

4.Type：告诉我们对表使用的访问方式，主要包含如下集中类型。

  • all：全表扫描。
  • const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
  • eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
  • fulltext：进行全文索引检索。
  • index：全索引扫描。
  • index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
  • index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
  • rang：索引范围扫描。
  • ref：Join语句中被驱动表索引引用的查询。
  • ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
  • system：系统表，表中只有一行数据；
  • unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。

5.Possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null，这项内容对优化索引时的调整非常重要。

6.Key：MySQL Query Optimizer从possible_keys中所选择使用的索引。

7.Key_len：被选中使用索引的索引键长度。

8.Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的。

9.Rows：MySQL Query Optimizer通过系统收集的统计信息估算出来的结果集记录条数。

10.Extra：查询中每一步实现的额外细节信息，主要会是以下内容。

  • Distinct：查找distinct值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。
  • Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。
  • Impossible WHERE noticed after reading const tables：MySQL Query Optimizer通过收集到的统计信息判断出不可能存在结果。
  • No tables：Query语句中使用FROM DUAL或不包含任何FROM子句。
  • Not exists：在某些左连接中，MySQL Query Optimizer通过改变原有Query的组成而使用的优化方法，可以部分减少数据访问次数。
  • Range checked for each record (index map: N)：通过MySQL官方手册的描述，当MySQL Query Optimizer没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
  • SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在Query中不能有GROUP BY操作。如使用MIN()或MAX()的时候。
  • Using filesort：当Query中包含ORDER BY操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer不得不选择相应的排序算法来实现。
  • Using index：所需数据只需在Index即可全部获得，不须要再到表中取数据。
  • Using index for group-by：数据访问和Using index一样，所需数据只须要读取索引，当Query中使用GROUP BY或DISTINCT子句时，如果分组字段也在索引中，Extra中的信息就会是Using index for group-by。
  • Using temporary：当MySQL在某些操作中必须使用临时表时，在Extra信息中就会出现Using temporary。主要常见于GROUP BY和ORDER BY等操作中。
  • Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现Using where信息。
  • Using where with pushed condition：这是一个仅仅在NDBCluster存储引擎中才会出现的信息，而且还须要通过打开Condition Pushdown优化功能才可能被使用。控制参数为engine_condition_pushdown。
# 使用了前导%，导致索引生效
mysql> explain select email from user where username like '%admin%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

# 未使用前导%，索引正常
mysql> explain select email from user where username like 'chenyy%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: username
          key: username
      key_len: 162
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.08 sec)

在看一个稍微复杂一点的query

mysql> explain select email from user where id in (select user_id from user_package where end_time > 1535952906)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: index
possible_keys: PRIMARY
          key: email
      key_len: 242
          ref: NULL
         rows: 3
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_package
         type: ref
possible_keys: user_id,end_time
          key: user_id
      key_len: 4
          ref: test.user.id
         rows: 1
        Extra: Using where; FirstMatch(user)
2 rows in set (0.21 sec)

首先是对user进行全索引扫描，第二步才访问user_package表。对所需数据的访问方式是索引扫描，所以索引扫描的类型为ref，过滤条件是user表的id字段。可以使用的索引有两个，最后选用了user_id。

Profiling的使用

要想优化一条Query，就须要清楚这条Query的性能瓶颈到底在哪里，是消耗的CPU计算太多，还是需要的IO操作太多。

MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具，通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU、IO、IPC、SWAP等，以及发生的PAGE FAULTS、CONTEXT SWITCHE等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。

具体用法：
（1）通过执行“set profiling”命令，可以开启关闭Query Profiler功能。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

（2）在开启Query Profiler功能之后，MySQL就会自动记录所有执行的Query的profile信息。

mysql> select username, end_time from towser_user_package limit 5;
+-------------+------------+
| username    | end_time   |
+-------------+------------+
| test1       | 1575258126 |
| test2       | 1481084075 |
| tset3       | 1481170483 |
| test4       | 1478578556 |
| test5       | 1510719403 |
+-------------+------------+
5 rows in set (0.07 sec)

（3）执行“SHOW PROFILES”命令获取当前系统中保存的多个Query的profile的概要信息

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.01888000 | select * from user where username like '%chenyy%'          |
|        2 | 0.00054600 | select * from user where username like '%chen%'            |
|        3 | 0.00700175 | set profiling=1                                            |
|        4 | 0.05704725 | select * from user where username like '%chen%'            |
|        5 | 0.07627825 | select username, end_time from user_package limit 5        |
|        6 | 0.00029925 | select username from user                                  |
+----------+------------+------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

（4）针对单个Query获取详细的profile信息。

mysql> show profile cpu, block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000538 | 0.000000 |   0.000000 |           56 |             0 |
| checking permissions | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.075484 | 0.000000 |   0.000000 |          160 |             0 |
| end                  | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000020 | 0.001000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000101 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

Status ： sql 语句执行的状态
Duration: sql 执行过程中每一个步骤的耗时
CPU_user: 当前用户占有的cpu
CPU_system: 系统占有的cpu
Block_ops_in : I/O 输入
Block_ops_out : I/O 输出


ps：Sending Data不是网络发送，是从硬盘读取，发送到网络是Writing to net。建议：通过索引或加上LIMIT，减少需要扫描并且发送给客户端的数据量 。

合理利用并设计索引

索引除了可以提高数据的查询速度，还可以降低数据的排序成本。

索引的弊端：索引也会占据物理存储空间，而且可能会比表本身还大。因此在考虑数据库的存储空间时，需要考虑索引要占用的空间。

可以从以下几个方面考虑是否使用索引：

  1. 大多数用于表结合的字段都应该设置索引。
  2. 经常在ORDER BY和GROUP BY里引用的字段也应该考虑设置索引。
  3. 低基数列不应该建立索引，例如‘性别、状态’
  4. 更新非常频繁的字段不适合创建索引
  5. 不应该对包含大量NULL值的字段设置索引。索引对在不同记录中包含不同数据的字段特别有效。字段中过多的NULL值会严重影响索引的运行效率。
  6. 索引不应该用于小规模的表。因为查询索引会增加额外的查询时间。对于小规模的表，让搜索发动机进行全表搜索，往往比先查询索引的速度更快。
  7. 较频繁的作为查询条件的字段应该创建索引

在选择是使用单字段索引还是组合索引时，要考虑在查询的WHERE子句里最经常使用什么字段。如果经常只使用一个字段，单字段索引就是最适合的；如果经常使用两个或多个字段，组合索引就是最好的索引。

其它一些SQL语句需要注意的地方：

不使用%前导的查询，例如like ‘%xxx’
无法使用索引，导致全表扫描

用in来代替or
低效查询
select * from t where id = 10 or id = 20 or id = 30;
高效查询
select * from t where id in (10,20,30);

避免数据类型不一致

select * from t where id = '10';
select * from t where id = 10;

避免使用连表查询

不要在索引列进行数学运算和函数运算
无法使用索引导致全表扫描

不使用反向查询——not like/not in

不使用!= 或者<>

join连表时永远用小结果集驱动大结果集

保证Join语句中被驱动表的Join条件字段已经被索引

版权声明： 本博客所有文章除特别声明外，均采用 CC 4.0 BY-SA 许可协议。转载请注明出处