本示例通过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优化的具体措施。
