SQL优化(一)-优化SQL语句的一般步骤

本示例通过MySQL官方案例库sakila，下载地址https://dev.mysql.com/doc/index-other.html。

压缩包包括三个文件sakila-schema.sql、sakila-data.sql、sakila.mwb，分别是sakila库的结构创建、数据插入、sakila的MySQL Workbench数据模型（可以在MySQL工作台打开查看数据库模型）。

通过show status命令了解各种SQL的执行频率

show status 命令的帮助信息：

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

示例：

mysql> show status like 'Com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
| Com_assign_to_keycache              | 0     |
| Com_alter_db                        | 0     |
| Com_alter_event                     | 0     |
| Com_alter_function                  | 0     |
| Com_alter_instance                  | 0     |
| Com_alter_procedure                 | 0     |
...

Com_xxx 表示每个 xxx 语句的执行次数，我们通常比较关心的试以下几个统计参数。

Com_select: 执行SELECT操作的次数，一次查询只累加1。

Com_insert: 执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次。

Com_update: 执行UPDATE操作的次数。

Com_delete: 执行DELETE操作的次数

上面的这些参数是对于所有存储引擎的的表操作都会进行累加，下面这几个参数只针对InnoDB存储引擎的，累加的算法也有所不同。

Innodb_rows_read: SELECT查询返回的行数。

Innodb_rows_inserted: 执行INSERT操作插入的行数。

Innodb_rows_updated: 执行UPDATE操作更新的行数。

Innodb_rows_deleted: 执行DELETE操作删除的行数。

通过以上几个参数，可以很容易了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数是对执行次数的计数，不论提交还是回滚都会进行累加。

对于事务性的应用，通过Com_commit和Com_rollback可以了解事物提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况。

Connections: 试图连接MySQL数据库的次数。

Uptime: 服务器工作的时间。

Slow_queries: 慢查询的次数

定位执行效率低的SQL语句

通过以下两种方式定位执行效率较低的SQL语句。

通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

慢查询日志查询结束才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。可以使用show processlist命令查看当前MySQL在执行的线程，包括线程的状态，是否锁表等，可以实时的查看SQL的执行情况，同时对一些锁表操作进行优化。

通过EXPLAIN分析低效SQL的执行计划

通过以上步骤查询到执行效率低的的SQL的语句后，可以通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括SELECT语句执行过程中如何连接和连接的顺序。

示例：

mysql> explain select sum(amount) from customer a, payment b where a.customer_id = b.customer_id and email = 'MARY.SMITH@sakilacustomer.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

对每个列做一下简单的说明

select_type: 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接和子查询）、PRIMARY（主查询，及外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中第一个SELECT）等。

table: 输出结果集的表。

type: 表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见类型有ALL、index、range、ref、eq_ref、const/system、NULL，从左到右，性能从最差到最好。

possible_keys: 表示查询时可能使用的索引。

key: 表示实际使用的索引。

key_len: 使用到索引字段的长度。

rows: 扫描行的数量。

Extra: 执行情况的说明和描述，包含不适合在其他列中侠士但是对执行计划非常重要的额外信息。

type的类型：

type=ALL，全表扫描，MySQL遍历全表来找到匹配的行。

type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行。

type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符。

type=ref，使用唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值得记录行。

type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说就是多表连接中使用primary key或者unique index作为关联条件。

type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键primary key或者唯一索引unique index进行的查询。

type=NULL，MySQL不用访问表或者索引，直接就能得到结果。

类型type还有其他值，入ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in 的后面是一个查询主键字段的字段的子查询）、index_subquery（与unique_subquery类似，区别在于 in 的后面是查询非唯一索引字段的子查询）等。

MySQL中explain命令加上show warnings能够看到在SQL真正被执行之前优化器做了哪些SQL改写。

mysql> explain select sum(amount) from customer a, payment b where a.customer_id = b.customer_id and email = 'MARY.SMITH@sakilacustomer.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`a`.`email` = 'MARY.SMITH@sakilacustomer.org') and (`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`))
1 row in set (0.00 sec)

通过show profile分析SQL

MySQL从5.0.37版本开始增加了对show profiles 和show profile语句的支持。通过have profiling参数，能够看到当前MySQL是否支持profile。

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

默认profiling是关闭的，可以通过set语句在Session 级别开启profiling。

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

通过 profile 我们能够更清楚的了解 SQL 执行的过程。例如，我们在一个 InnoDB 引擎的付款表 payment 上，执行一个 COUNT(*) 查询：

mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.29 sec)

然后通过show profiles语句，看到当前 SQL 的 Query_ID 为6：

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00072200 | select  @@profiling          |
|        2 | 0.00039400 | select count(*) from payment |
|        3 | 0.00042725 | SELECT DATABASE()            |
|        4 | 0.00959875 | show databases               |
|        5 | 0.00868875 | show tables                  |
|        6 | 0.28926100 | select count(*) from payment |
+----------+------------+------------------------------+
6 rows in set, 1 warning (0.00 sec)

通过show profile for query语句可以看到执行过程中线程的每个状态和消耗的时间：

mysql> show profile for query 4;
+----------------------------+----------+
| Status                     | Duration |
+----------------------------+----------+
| starting                   | 0.000448 |
| checking permissions       | 0.000035 |
| Opening tables             | 0.001103 |
| checking permissions       | 0.000029 |
| checking permissions       | 0.000011 |
| checking permissions       | 0.000011 |
| checking permissions       | 0.000080 |
| checking permissions       | 0.002672 |
| init                       | 0.000315 |
| checking permissions       | 0.000030 |
| checking permissions       | 0.000011 |
| checking permissions       | 0.000009 |
| checking permissions       | 0.000265 |
| System lock                | 0.000043 |
| optimizing                 | 0.000116 |
| statistics                 | 0.000386 |
| preparing                  | 0.000407 |
| Creating tmp table         | 0.001238 |
| executing                  | 0.001724 |
| end                        | 0.000032 |
| query end                  | 0.000013 |
| waiting for handler commit | 0.000035 |
| removing tmp table         | 0.000028 |
| waiting for handler commit | 0.000015 |
| closing tables             | 0.000031 |
| freeing items              | 0.000268 |
| cleaning up                | 0.000249 |
+----------------------------+----------+
27 rows in set, 1 warning (0.00 sec)

在获取最消耗时间的线程状态后，MySQL 支持进一步选择all、cpu、block io、context switch、page faults 等明细类型来查看MySQL在使用什么资源上消耗了过高的时间，例如：

mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000140 | 0.000037 | 0.000100   |
| Executing hook on transaction  | 0.000019 | 0.000005 | 0.000013   |
| starting                       | 0.000021 | 0.000006 | 0.000016   |
| checking permissions           | 0.000017 | 0.000004 | 0.000012   |
| Opening tables                 | 0.000401 | 0.000109 | 0.000294   |
| init                           | 0.000026 | 0.000007 | 0.000017   |
| System lock                    | 0.000025 | 0.000006 | 0.000019   |
| optimizing                     | 0.000016 | 0.000005 | 0.000011   |
| statistics                     | 0.000032 | 0.000009 | 0.000024   |
| preparing                      | 0.000037 | 0.000009 | 0.000027   |
| executing                      | 0.002256 | 0.003559 | 0.000000   |
| end                            | 0.000089 | 0.000034 | 0.000000   |
| query end                      | 0.000018 | 0.000016 | 0.000000   |
| waiting for handler commit     | 0.000113 | 0.004119 | 0.000000   |
| closing tables                 | 0.000039 | 0.000035 | 0.000000   |
| freeing items                  | 0.000454 | 0.000458 | 0.000000   |
| cleaning up                    | 0.000081 | 0.000076 | 0.000000   |
+--------------------------------+----------+----------+------------+
17 rows in set (0.01 sec)

如果对 MySQL 源码感兴趣，还可以通过show profile source for query查看 SQL 解析执行过程中每个步骤对应的源码文件、函数名以及具体的源文件行数：

mysql> show profile source for query 6;
+--------------------------------+----------+-------------------------+----------------------+-------------+
| Status                         | Duration | Source_function         | Source_file          | Source_line |
+--------------------------------+----------+-------------------------+----------------------+-------------+
| starting                       | 0.000191 | NULL                    | NULL                 | NULL        |
| Executing hook on transaction  | 0.000019 | launch_hook_trans_begin | rpl_handler.cc       |        1119 |
| starting                       | 0.000020 | launch_hook_trans_begin | rpl_handler.cc       |        1121 |
| checking permissions           | 0.000019 | check_access            | sql_authorization.cc |        2176 |
| Opening tables                 | 0.000065 | open_tables             | sql_base.cc          |        5591 |
| init                           | 0.000020 | execute                 | sql_select.cc        |         677 |
| System lock                    | 0.000025 | mysql_lock_tables       | lock.cc              |         331 |
| optimizing                     | 0.000016 | optimize                | sql_optimizer.cc     |         282 |
| statistics                     | 0.000040 | optimize                | sql_optimizer.cc     |         502 |
| preparing                      | 0.000037 | optimize                | sql_optimizer.cc     |         583 |
| executing                      | 0.003318 | ExecuteIteratorQuery    | sql_union.cc         |        1409 |
| end                            | 0.000036 | execute                 | sql_select.cc        |         730 |
| query end                      | 0.000015 | mysql_execute_command   | sql_parse.cc         |        4606 |
| waiting for handler commit     | 0.000026 | ha_commit_trans         | handler.cc           |        1589 |
| closing tables                 | 0.000023 | mysql_execute_command   | sql_parse.cc         |        4657 |
| freeing items                  | 0.000444 | mysql_parse             | sql_parse.cc         |        5330 |
| cleaning up                    | 0.000058 | dispatch_command        | sql_parse.cc         |        2184 |
+--------------------------------+----------+-------------------------+----------------------+-------------+

show profile能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。

通过trace分析优化器如何选择执行计划

MySQL 提供了对 SQL 的追踪 trace ，通过 trace 文件能够进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划，帮助我们更好理解优化器的行为。

使用方式：首先打开 trace，格式设置为 JSON，设置最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能完整显示。

mysql> set OPTIMIZER_TRACE="enabled=on", END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

接下来执行想 trace 的 SQL 语句：

mysql> select rental_id from rental where rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
|        39 |
+-----------+
1 row in set (0.05 sec)

最后检查 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道 MySQL 是如何执行 SQL 语句的。

mysql> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
                            QUERY: select rental_id from rental where rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `rental`.`rental_id` AS `rental_id` from `rental` where ((`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    ...
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

确定问题并采取相应的优化措施

经过以上步骤，基本可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化提高执行的效率。

后面会介绍SQL优化的具体措施。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 158,425评论 4赞 361
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,058评论 1赞 291
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,186评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,848评论 0赞 204
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,249评论 3赞 286
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,554评论 1赞 216
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,830评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,536评论 0赞 197
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,239评论 1赞 241
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,505评论 2赞 244
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,004评论 1赞 258
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,346评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 32,999评论 3赞 235
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,060评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,821评论 0赞 194
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,574评论 2赞 271
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,480评论 2赞 267