来自《深入浅出MySQL》15.1 及 15.4章节。

主要讲解最基本的分析方法和常用的SQL语句如何优化。

通用SQL优化办法

主要讲解怎么分析数据库系统的性能问题

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

通过 show [session | global] status 命令可以提供服务器状态信息。

session 表示当前连接的统计结果，默认使用session。
global 表示数据库上次启动至今的统计结果

然后接下来是几个比较关心的统计参数，这四个参数对所有存储引擎的表都会进行累计：

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操作删除的行数

通过以上参数可以了解当前数据库的应用是以插入更新为主，还是以查询操作为主。

对于事务型应用，可以通过Com_commit 和 Com_rollback 可了解事务提交和回滚的情况。

另外还可以了解一下数据库的基本情况：

Connections 试图连接MySQL服务器的次数
Uptime 服务器工作时间
Slow_queries 慢查询次数

定位执行效率较低的SQL语句

通过慢查询日志可以定位执行效率较低的SQL语句。

通过show processlist命令查看当前MySQL在进行的线程状态，是否锁表等。

可以实时查看SQL执行情况，对锁表操作进行优化。

就是我么常说的，一次性对所有需要的表加锁，防止产生冲突等等。

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

这里需要用到Sakila数据库。

安装方法简单讲解如下：

1. 下载Sakila，下载地址：https://dev.mysql.com/doc/index-other.html

2. 选择tar.gz格式下载就好，然后 tar -zxvf … …解压

3. root权限(这里主要需要建表那些权限)，SQL命令导入数据库 mysql -u root -p <sakila-schema.sql

4. 继续用SQL命令导入数据 mysql -u root -p <sakila-data.sql

5. 最后给需要的用户加上访问权限即可：GRANT SELECT, INSERT, UPDATE, DELETE ON sakila.* TO ‘user‘@’%’;

接着我们通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息。

举例：统计某个email为租赁电源拷贝所支付的总金额。

那么我们需要关联客户表customer和付款表payment，对付款金额amount字段做求和操作。

mysql> EXPLAIN SELECT sum(amount) 
    -> FROM customer a, payment b
    -> WHERE 1=1
    -> AND a.customer_id = b.customer_id
    -> AND email='JANE.BENNETT@sakilacustomer.org';
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref                  | rows | Extra       |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY            | NULL               | NULL    | NULL                 |  599 | Using where |
|  1 | SIMPLE      | b     | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.a.customer_id |    1 | NULL        |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+
2 rows in set (0.01 sec)

mysql> EXPLAIN SELECT sum(amount)

-> FROM customer a, payment b

-> WHERE 1=1

-> AND a.customer_id = b.customer_id

-> AND email='JANE.BENNETT@sakilacustomer.org';

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+

| 1 | SIMPLE | b | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.a.customer_id | 1 | NULL |

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------------+

2 rows in set (0.01 sec)

那么我们简单对表格做一下说明：

select_type：表示SELECT的类型
table：输出结果集的表
type：MySQL查找方式，或者说访问类型。
possible_keys：表示查询时可能使用的索引
key：表示实际使用的索引
key_len：使用到索引字段的长度
rows：扫描行的数量
Extra：执行情况的说明和描述

select_type类型常见的有以下几种：

SIMPLE 简单表，不使用表连接或者子查询
PRIMARY 主查询，即外层的查询
UNION 第二个或后面的查询语句
SUBQUERY 子查询中的第一个SELECT语句
DERIVED 驱动表，指的是在From内的查询

常见的查找方式：(左边性能最差，右边性能最好)

ALL

index

range

ref

eq_ref

const,system

NULL

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

mysql> EXPLAIN SELECT * 
    -> FROM film
    -> WHERE 1=1
    -> AND rating > 9;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT *

-> FROM film

-> WHERE 1=1

-> AND rating > 9;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

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

mysql> EXPLAIN SELECT title
    -> FROM film;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | index | NULL          | idx_title | 514     | NULL | 1000 | Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT title

-> FROM film;

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

1 row in set (0.01 sec)

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

mysql> EXPLAIN SELECT *
    -> FROM payment 
    -> WHERE customer_id >=300 
    -> AND customer_id <=350;
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+
| id | select_type | table   | type  | possible_keys      | key                | key_len | ref  | rows | Extra                 |
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | payment | range | idx_fk_customer_id | idx_fk_customer_id | 2       | NULL | 1349 | Using index condition |
+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT *

-> FROM payment

-> WHERE customer_id >=300

-> AND customer_id <=350;

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+

+----+-------------+---------+-------+--------------------+--------------------+---------+------+------+-----------------------+

1 row in set (0.00 sec)

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

mysql> EXPLAIN SELECT *
    -> FROM payment
    -> WHERE customer_id=350;
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
|  1 | SIMPLE      | payment | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   23 | NULL  |
+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT *

-> FROM payment

-> WHERE customer_id=350;

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

+----+-------------+---------+------+--------------------+--------------------+---------+-------+------+-------+

1 row in set (0.01 sec)

这里idx_fk_customer_id是非唯一索引，查询条件为等值查询条件customer_id=35，所以扫描索引的类型为ref。

同时，ref还经常出现在join操作中：

mysql> EXPLAIN SELECT b.*, a.* FROM payment a, customer b WHERE a.customer_id = b.customer_id;
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys      | key                | key_len | ref                  | rows | Extra |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY            | NULL               | NULL    | NULL                 |  599 | NULL  |
|  1 | SIMPLE      | a     | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | sakila.b.customer_id |    1 | NULL  |
+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT b.*, a.* FROM payment a, customer b WHERE a.customer_id = b.customer_id;

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+

| 1 | SIMPLE | a | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.b.customer_id | 1 | NULL |

+----+-------------+-------+------+--------------------+--------------------+---------+----------------------+------+-------+

2 rows in set (0.00 sec)

这里表A是全表扫描，表B是非唯一索引单值查询。

5. type=eq_ref，类似ref，区别在于使用的是唯一索引，对于每个索引键值，表中只有一条记录匹配。

简单来说就是多表连接中，使用primary key 或者 unique index作为关联条件。

mysql> EXPLAIN SELECT * 
    -> FROM film a, film_text b
    -> WHERE a.film_id = b.film_id;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY       | NULL    | NULL    | NULL             | 1000 | NULL        |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.b.film_id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT *

-> FROM film a, film_text b

-> WHERE a.film_id = b.film_id;

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

2 rows in set (0.00 sec)

这里A是全表扫描，B是唯一索引(主键)匹配。

6. type = const/system，单表中最多有一个匹配行，查询起来非常迅速。

这个匹配行中的其他列值可以被优化器在当前查询中当作常量来处理。

例如，主键primary key 或者唯一索引unique index进行查询。

这里涉及到对表结构的修改，给email字段加上一个唯一索引。

mysql> alter table customer add unique index uk_email(email);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table customer add unique index uk_email(email);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

然后执行查询如下：

mysql> EXPLAIN SELECT *
    -> FROM (
    ->     SELECT * 
    ->     FROM customer
    ->     WHERE email='AARON.SELBY@sakilacustomer.org'
    -> ) a;
+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL     | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | customer   | const  | uk_email      | uk_email | 203     | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT *

-> FROM (

-> SELECT *

-> FROM customer

-> WHERE email='AARON.SELBY@sakilacustomer.org'

-> ) a;

+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+

+----+-------------+------------+--------+---------------+----------+---------+-------+------+-------+

2 rows in set (0.00 sec)

这里是将子查询内的数据作为表a继续查询。

7. type = NULL，MySQL不需要访问表和索引，直接能得到结果。

mysql> EXPLAIN SELECT 1 FROM dual WHERE 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT 1 FROM dual WHERE 1;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

这里dual其实就是占位。

在Mysql和SQL Server中可以直接 SELECT 1 ，不需要加 FROM table_name 就可以执行。

而在Oracle中要满足select … from … 的结构，由于SELECT 1不能实现满足上述结构，所以引入dual来满足结构。

所以Oracle这样表示：select 1 from dual，当然在MySQL中这样操作也OK。

type还有其他值，比如

ref_or_null(与ref类似，区别就是包含对NULL的查询)
index_merge(索引合并优化)
unique_subquery(in 的后面是一个查询主键字段的子查询)
index_subquery(in的后面是查询非唯一索引字段的子查询)

通过Show Profile分析SQL

查看MySQL是否支持profile

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

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 |

+-------------+

| 0 |

+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

这样，通过 profile，我们可以更清楚的了解SQL执行的过程。

比如我们在innodb引擎的付款表payment上执行一个count(*)查询：

mysql> SELECT COUNT(*) FROM payment;
+----------+
| COUNT(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM payment;

+----------+

| COUNT(*) |

+----------+

| 16049 |

+----------+

1 row in set (0.01 sec)

接下来我们通过 show profiles语句查看这条SQL的Query ID：

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00222450 | SELECT COUNT(*) FROM payment |
+----------+------------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profiles;

+----------+------------+------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------+

| 1 | 0.00222450 | SELECT COUNT(*) FROM payment |

+----------+------------+------------------------------+

1 row in set, 1 warning (0.00 sec)

因为我们开启profiling之后，只有这一条语句执行，所以ID就是1。

通过show profile for query [id]查看id为1的SQL执行过程中线程的每个状态和消耗时间。

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000082 |
| checking permissions | 0.000020 |
| Opening tables       | 0.000017 |
| init                 | 0.000008 |
| System lock          | 0.000005 |
| optimizing           | 0.000003 |
| statistics           | 0.000008 |
| preparing            | 0.000008 |
| executing            | 0.000001 |
| Sending data         | 0.002033 |
| end                  | 0.000005 |
| query end            | 0.000004 |
| closing tables       | 0.000005 |
| freeing items        | 0.000019 |
| cleaning up          | 0.000009 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 1;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000082 |

| checking permissions | 0.000020 |

| Opening tables | 0.000017 |

| init | 0.000008 |

| System lock | 0.000005 |

| optimizing | 0.000003 |

| statistics | 0.000008 |

| preparing | 0.000008 |

| executing | 0.000001 |

| Sending data | 0.002033 |

| end | 0.000005 |

| query end | 0.000004 |

| closing tables | 0.000005 |

| freeing items | 0.000019 |

| cleaning up | 0.000009 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

可以看到Sending data状态花费了最多的时间。

P.S. Sending data状态表示MySQL线程开始访问数据行，并把结果返回给客户端。

在此状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

或者通过查询 INFORMATION_SCHEMA.PROFILING 表并按照时间做DESC排序：

mysql> SELECT STATE,
    ->        SUM(DURATION) AS Total_R,
    ->        ROUND(
    ->            100 * SUM(DURATION) / 
    ->                (SELECT SUM(DURATION)
    ->                 FROM INFORMATION_SCHEMA.PROFILING
    ->                 WHERE QUERY_ID = @query_id),
    ->            2
    ->        ) AS Pct_R, 
    ->       COUNT(*) AS Calls,
    ->       SUM(DURATION) / COUNT(*) AS "R/Call"
    -> FROM INFORMATION_SCHEMA.PROFILING
    -> WHERE QUERY_ID = @query_id
    -> GROUP BY STATE
    -> ORDER BY Total_R DESC;
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.002033 | 91.29 |     1 | 0.0020330000 |
| starting             | 0.000082 |  3.68 |     1 | 0.0000820000 |
| checking permissions | 0.000020 |  0.90 |     1 | 0.0000200000 |
| freeing items        | 0.000019 |  0.85 |     1 | 0.0000190000 |
| Opening tables       | 0.000017 |  0.76 |     1 | 0.0000170000 |
| cleaning up          | 0.000009 |  0.40 |     1 | 0.0000090000 |
| init                 | 0.000008 |  0.36 |     1 | 0.0000080000 |
| preparing            | 0.000008 |  0.36 |     1 | 0.0000080000 |
| statistics           | 0.000008 |  0.36 |     1 | 0.0000080000 |
| end                  | 0.000005 |  0.22 |     1 | 0.0000050000 |
| System lock          | 0.000005 |  0.22 |     1 | 0.0000050000 |
| closing tables       | 0.000005 |  0.22 |     1 | 0.0000050000 |
| query end            | 0.000004 |  0.18 |     1 | 0.0000040000 |
| optimizing           | 0.000003 |  0.13 |     1 | 0.0000030000 |
| executing            | 0.000001 |  0.04 |     1 | 0.0000010000 |
+----------------------+----------+-------+-------+--------------+
15 rows in set (0.01 sec)

mysql> SELECT STATE,

-> SUM(DURATION) AS Total_R,

-> ROUND(

-> 100 * SUM(DURATION) /

-> (SELECT SUM(DURATION)

-> FROM INFORMATION_SCHEMA.PROFILING

-> WHERE QUERY_ID = @query_id),

-> 2

-> ) AS Pct_R,

-> COUNT(*) AS Calls,

-> SUM(DURATION) / COUNT(*) AS "R/Call"

-> FROM INFORMATION_SCHEMA.PROFILING

-> WHERE QUERY_ID = @query_id

-> GROUP BY STATE

-> ORDER BY Total_R DESC;

+----------------------+----------+-------+-------+--------------+

+----------------------+----------+-------+-------+--------------+

| Sending data | 0.002033 | 91.29 | 1 | 0.0020330000 |

| starting | 0.000082 | 3.68 | 1 | 0.0000820000 |

| checking permissions | 0.000020 | 0.90 | 1 | 0.0000200000 |

| freeing items | 0.000019 | 0.85 | 1 | 0.0000190000 |

| Opening tables | 0.000017 | 0.76 | 1 | 0.0000170000 |

| cleaning up | 0.000009 | 0.40 | 1 | 0.0000090000 |

| init | 0.000008 | 0.36 | 1 | 0.0000080000 |

| preparing | 0.000008 | 0.36 | 1 | 0.0000080000 |

| statistics | 0.000008 | 0.36 | 1 | 0.0000080000 |

| end | 0.000005 | 0.22 | 1 | 0.0000050000 |

| System lock | 0.000005 | 0.22 | 1 | 0.0000050000 |

| closing tables | 0.000005 | 0.22 | 1 | 0.0000050000 |

| query end | 0.000004 | 0.18 | 1 | 0.0000040000 |

| optimizing | 0.000003 | 0.13 | 1 | 0.0000030000 |

| executing | 0.000001 | 0.04 | 1 | 0.0000010000 |

+----------------------+----------+-------+-------+--------------+

15 rows in set (0.01 sec)

那么通过排序，我们就看的更清楚了。

P.S. 在MySQL5.7中，profile已经不建议使用了。使用Performance Schema中一系列的性能视图来替代，详细内容参考《深入浅出MySQL》第二十章。

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

MySQL从5.6版本开始提供了对SQL的跟踪trace。

通过trace文件我们可以进一步了解为什么优化器选择A执行计划，而不是B执行计划。

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

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)

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 1=1
    -> AND 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.03 sec)

mysql> SELECT rental_id

-> FROM rental

-> WHERE 1=1

-> AND 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.03 sec)

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

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE into outfile '/usr/local/mysql/var/trace/SelectFile2020-11-26';
Query OK, 0 rows affected (0.00 sec)

1 2	mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE into outfile '/usr/local/mysql/var/trace/SelectFile2020-11-26'; Query OK, 0 rows affected (0.00 sec)

P.S. 这里我执行的时候报了一系列的错误，解决步骤包括：

给当前操作用户加上全局File权限
在my.conf文件中加上参数：secure_file_priv=”
重启MySQL后，重启打开trace…

最后我们可以打开文件查看内容：

[xiong@AMDServer trace]$ pwd
/usr/local/mysql/var/trace
[xiong@AMDServer trace]$ ll
total 12K
-rw-rw-rw- 1 mysql mysql 9.1K 11月 26 18:38 SelectFile2020-11-26

[xiong@AMDServer trace]$ pwd

/usr/local/mysql/var/trace

[xiong@AMDServer trace]$ ll

total 12K

-rw-rw-rw- 1 mysql mysql 9.1K 11月 26 18:38 SelectFile2020-11-26

写到这里我停住了…两天。

文件里面记录了很多信息，包括访问表的路径、行数、成本等，来帮助读者对执行计划的选择过程进行分析。

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

在前面的例子：统计某个email为租赁电源拷贝所支付的总金额。

我们可以确认是由于customer表全表扫描导致的效率不理想，那么我们给 customer表的email字段创建索引。

创建索引的语句是： create index idx_email on customer(email);

不过之前我们创建过唯一索引了，就不再重复创建。

然后我们再执行查询：

mysql> EXPLAIN 
    -> SELECT sum(amount)
    -> FROM customer a, payment b
    -> WHERE 1=1
    -> AND a.customer_id = b.customer_id
    -> AND email = 'JANE.BENNETT@sakilacustomer.org';
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY,uk_email   | uk_email           | 203     | const |    1 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   28 | NULL        |
+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+
2 rows in set (0.02 sec)

mysql> EXPLAIN

-> SELECT sum(amount)

-> FROM customer a, payment b

-> WHERE 1=1

-> AND a.customer_id = b.customer_id

-> AND email = 'JANE.BENNETT@sakilacustomer.org';

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

| 1 | SIMPLE | b | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | const | 28 | NULL |

+----+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------------+

2 rows in set (0.02 sec)

可以点击这里跳转到前面进行比较，会发现customer表只扫描了一行，效率大大提升了。

索引可以帮助用户解决大多数的SQL性能问题。

索引的具体用法可以参考这一篇文章：

【MySQL】高性能MySQL —— 索引

常用SQL语句优化

前面讲了通过索引来优化查询，在日常开发中，除了使用查询外，我们还会使用INSERT、GROUP BY等。

接下来我们会对这些SQL语句介绍一些优化办法。

目前在读《深入浅出MySQL》15.4章节，与书上部分地方理解有些不一致，也一一记录。

大批量插入数据

对于 MyISAM表，使用下面的方式可以快速导入大量数据，对于InnoDB引擎，这个方法则不行。

ALTER TABLE table_name DISABLE KEYS;
loading the data...
ALTER TABLE table_name DISABLE KEYS;

ALTER TABLE table_name DISABLE KEYS;

loading the data...

ALTER TABLE table_name DISABLE KEYS;

先关闭MyISAM表非唯一索引的更新，然后导入大量数据到一个非空的MyISAM表，然后再打开非唯一索引更新。

对于InnoDB表：

导入数据应该按照主键顺序排列。
导入数据前可以使用 SET UNIQUE_CHECKS=0 关闭唯一性校验，导入完毕后再设置为1，重新打开。
导入数据前可以关闭自动提交，导入完毕后一次性提交。

优化 INSERT 语句

可以采用以下几种优化方式：

如果同时从同一客户端插入很多行数据，应该选择插入多行值的INSERT语句。这种方式大大减少客户端与数据库之间连接关闭的消耗，效率上能快出许多。
如果从不同客户端插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度。
将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。
对于MyISAM表，可以增加bulk_insert_buffer_size变量值的方法来提高速度。
从一个文本文件装载一个表时，使用 LOAD DATA INFILE，通常比 INSERT 语句快20倍。

优化 ORDER BY 语句

ORDER BY 用于对结果集进行排序，默认按照升序记录进行排序，如果使用DESC则使用降序排序。

在聊优化ORDER BY语句之前，先聊聊MySQL的两种排序方式：

通过有序索引顺序扫描直接返回有序数据
通过对返回数据进行排序，也是通常说的FileSort。

1. 有序索引扫描排序，分析查询显示为Using index

示例：

mysql> EXPLAIN SELECT customer_id FROM customer ORDER BY store_id;
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key             | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
|  1 | SIMPLE      | customer | index | NULL          | idx_fk_store_id | 1       | NULL |  599 | Using index |
+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT customer_id FROM customer ORDER BY store_id;

+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

1 row in set (0.00 sec)

这里直接返回了有序数据，不需要排序，操作效率高。

能直接返回有序数据的原因是：对于InnoDB来说主键列customer_id已经包含在二级索引 idx_fk_store_id 中了。

2. 对返回数据排序，分析查询显示为 Using filesort

示例：

mysql> EXPLAIN SELECT * FROM customer ORDER BY store_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  599 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM customer ORDER BY store_id;

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

所有不是通过索引直接返回排序结果的的排序都叫Filesort排序。

示例2：它也无法通过索引直接返回排序结果，所以是 Using filesort

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  599 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id;

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

所以其实这里我们就能明确优化方案：减少额外排序，通过索引返回排序结果。

给出如下建议：

WHERE条件和ORDER BY使用相同的索引
ORDER BY顺序和索引顺序一致
ORDER BY字段都是升序或者都是降序

对于上面的示例2，我们可以加上一个 idx_storeid_email 索引

mysql> ALTER TABLE customer add index idx_storeid_email(store_id, email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id;
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | customer | index | NULL          | idx_storeid_email | 204     | NULL |  599 | Using index |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE customer add index idx_storeid_email(store_id, email);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id;

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

1 row in set (0.01 sec)

这里就完成了对示例2的优化。

示例3：假设这里实际上要按照email进行排序呢？

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY email;
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+
| id | select_type | table    | type  | possible_keys | key               | key_len | ref  | rows | Extra                       |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | customer | index | NULL          | idx_storeid_email | 204     | NULL |  599 | Using index; Using filesort |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY email;

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-----------------------------+

1 row in set (0.00 sec)

这里因为索引idx_storeid_email是先按store_id(第一列)排序，再按email(第二列)排序的，所以不适用了。

优化办法就是：让ORDER BY顺序和索引顺序一致，加上 idx_email_storeid索引。

接下来还有两个例子，

示例4：查询商店编号store_id=1，按照email逆序排序的主键值customer_id。

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer WHERE store_id=1 ORDER BY email DESC;
+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys                     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | customer | ref  | idx_fk_store_id,idx_storeid_email | idx_storeid_email | 1       | const |  325 | Using where; Using index |
+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer WHERE store_id=1 ORDER BY email DESC;

+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+

+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+

+----+-------------+----------+------+-----------------------------------+-------------------+---------+-------+------+--------------------------+

1 row in set (0.00 sec)

这里是能够完美使用上索引的。

示例5：查询商店编号store_id大于等于1，小于等于3，按照email DESC排序的主键值customer_id。

mysql> EXPLAIN SELECT store_id, email, customer_id 
    -> FROM customer
    -> WHERE 1=1
    -> AND store_id >=1 
    -> AND store_id <=3 
    -> ORDER BY email DESC;
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+
| id | select_type | table    | type  | possible_keys                     | key               | key_len | ref  | rows | Extra                                    |
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | customer | index | idx_fk_store_id,idx_storeid_email | idx_storeid_email | 204     | NULL |  599 | Using where; Using index; Using filesort |
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id

-> FROM customer

-> WHERE 1=1

-> AND store_id >=1

-> AND store_id <=3

-> ORDER BY email DESC;

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+------------------------------------------+

1 row in set (0.00 sec)

这里数据值依据索引查询出来之后，还是要额外的按照email进行一次排序。

优化办法：加上idx_email_storeid索引，让ORDER BY顺序和索引顺序一致。

mysql> ALTER TABLE customer add index idx_email_storeid(email, store_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer WHERE 1=1 AND store_id >=1  AND store_id <=3  ORDER BY email DESC;
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys                     | key               | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | customer | index | idx_fk_store_id,idx_storeid_email | idx_email_storeid | 204     | NULL |  599 | Using where; Using index |
+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE customer add index idx_email_storeid(email, store_id);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer WHERE 1=1 AND store_id >=1 AND store_id <=3 ORDER BY email DESC;

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+

+----+-------------+----------+-------+-----------------------------------+-------------------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

回顾示例3：刚刚我们加上了索引后，示例3也使用索引扫描返回有序数据了。

mysql> EXPLAIN  SELECT store_id, email, customer_id FROM customer ORDER BY email;
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | customer | index | NULL          | idx_email_storeid | 204     | NULL |  599 | Using index |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY email;

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

1 row in set (0.00 sec)

在这一小节的最后，记录一下对书上理解不一致的点：

索引排序的本质是：先按照第一列排序，然后第二列，第三列…依次类推。

当索引能够按顺序覆盖ORDER BY的情况下就是完美的。

书上给出这个例子： SELECT * FROM tabname ORDER BY key1, key2; 认为因为对不同关键字使用 ORDER BY导致不使用索引。

我认为是错误的，比如下面的例子符合书上的样例：

mysql> EXPLAIN SELECT * FROM customer ORDER BY store_id, email;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  599 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM customer ORDER BY store_id, email;

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

我最初认为这里不使用索引跟ORDER BY的排序字段是两个没关系，而是因为使用了SELECT *。

这里查询了一下MySQL的文档，参考：

MySQL中文文档，8.2.1.14 优化ORDER BY

MySQL官方文档（英文版），8.2.1.14 ORDER BY Optimization

这里给出的一段话是这样的：

However, the query uses SELECT *, which may select more columns than key_part1 and key_part2.

翻译：但是查询使用SELECT *，它可能选择比key_part1和key_part2更多的列。

In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results.

在这种情况下，扫描整个索引并回表查询以查找不在索引中的列可能比扫描表并对结果进行排序的开销更大。

If so, the optimizer is not likely to use the index.

如果是这样，优化器不太可能使用索引。

If SELECT * selects only the index columns, the index is used and sorting avoided.

如果SELECT *只选择索引列，则使用索引并避免排序。

所以我们看一个不用SELECT *的例子：

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id, email;
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | customer | index | NULL          | idx_storeid_email | 204     | NULL |  599 | Using index |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, email, customer_id FROM customer ORDER BY store_id, email;

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------------+

1 row in set (0.00 sec)

这种情况下idx_storeid_email索引覆盖了我们的查询项，就直接使用索引查询返回了数据。

所以书上的例子问题根本就不是使用了多个列进行ORDER BY，而是因为回表查询的开销让优化器决定了不使用索引。

那我这里如果一定要 SELECT * 怎么办？这里先提出基本规则：一般查询不允许使用SELECT *，这里只是提供索引无法覆盖的情况。

参考链接：【mysq order by 不走索引问题】。

解决方案：强制使用索引

mysql> EXPLAIN SELECT * FROM customer force index(idx_storeid_email) ORDER BY store_id, email;
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key               | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+
|  1 | SIMPLE      | customer | index | NULL          | idx_storeid_email | 204     | NULL |  599 | NULL  |
+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM customer force index(idx_storeid_email) ORDER BY store_id, email;

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+

+----+-------------+----------+-------+---------------+-------------------+---------+------+------+-------+

1 row in set (0.00 sec)

这种方案在上面的参考链接中测试，10W行数据，的确提升了性能，但是场景略微不太一致。

这里建议根据实际情况测试，然后进行抉择。

P.S. 其实如果只是简单的索引无法覆盖，就加一个联合索引就OK了…比如：

mysql> EXPLAIN SELECT store_id, last_name, customer_id FROM customer ORDER BY store_id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  599 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT store_id, last_name, customer_id FROM customer ORDER BY store_id;

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

这里我们其实只需要给store_id, last_name加上联合索引就够了。

ORDER BY子句 – FileSort的优化

【MySQL】通用SQL优化办法 & 常用SQL语句优化

Tagged on: MySQL