SQL优化

# MySQL SQL优化

MySQL版本：MySQL5.7

本文主要是对《深入浅出 MySQL：数据库开发、优化与管理维护》的学习记录，同时结合了自身的一些心得。

文章所有涉及的案例表都包括在 MySQL 的案例库 sakila 上，sakila 是一个MySQL官方提供的模拟电影出租厅信息管理系统的数据库，类似 Oracle 提供的 scott 库，sakila 库的下载地址为：http://downloads.mysql.com/docs/sakila-db.zip。压缩包包括 3 个文件：sakila-schema.sql、sakila-data.sql 和 sakila.mwb，分别是 sakila 库的表结构创建、数据灌入、sakila 的 MySQL Workbench 数据模型（可以在 MySQL 工作台打开查看数据库模型）。

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 优化 SQL 的步骤

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

mysql> ? show status
Name: 'SHOW STATUS'
Description:
Syntax:
SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

1
2
3
4
5
6

通过SHOW [GLOBAL | SESSION] STATUS命令可以提供服务器状态信息，也可以在操作系统上使用mysqladmin extended-status命令获得这些消息。SHOW [GLOBAL | SESSION] STATUS可以根据需要加上参数session或者global来显示session级（当前连接会话）的统计结果和global级（自数据库上次启动至今）的统计结果。如果不写，默认使用的参数是session。

下面的命令显示了当前session中所有统计参数的值：

mysql> show status like 'Com_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_assign_to_keycache      | 0     |
...

1
2
3
4
5
6
7

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

Com_select：执行SELECT操作的次数，一次查询累加1
Com_insert：执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次
Com_update：执行UPDATE操作的次数
Com_delete：执行DELETE操作的次数
Com_commit：事务提交的次数
Com_rollback：事务回滚的次数

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

mysql> show status like 'Innodb_%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 230405 16:08:45 |
...

1
2
3
4
5
6
7

Innodb_rows_read：SELECT查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数

通过以上几个参数，可以很容易地判断数据库是写多读少，还是读多写少数据库，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

此外，以下几个参数便于用户了解数据库的基本情况：

mysql> show status like 'Connections%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 10    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'Uptime%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 2845  |
| Uptime_since_flush_status | 2845  |
+---------------------------+-------+
2 rows in set (0.01 sec)

mysql> show status like 'Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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

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

有两种方法可以单位执行效率较低的 SQL 语句：

慢查询日志。

通过慢查询日志定位那些执行效率较低的 SQL 语句。
show processlist命令。

使用show processlist命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

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

通过EXPLAIN或者DESC命令获取 MySQL 如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

例如，分析一个连表统计的 SQL：

mysql> explain select count(*) from actor t1, film_actor t2 where t1.actor_id=t2.actor_id;

参数说明

id：MySQL 会为每个select语句分配⼀个唯⼀的 id 值
select_type：查询的类型，根据关联、union、⼦查询等等分类，常见的查询类型有

SIMPLE、PRIMARY。
table：表示explain的一行正在访问哪个表
partitions：匹配的分区
type：连接类型
possible_keys：可能的索引选择
key：实际选择的索引
key_len：索引的长度
ref：索引的哪一列被引用了
rows：估计要扫描的行数
filtered：表示符合查询条件的数据百分比
Extra：附加信息

# `id`

该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

# `select_type`

查询类型，有如下几种取值：

SIMPLE：简单查询（未使用UNION或子查询）
PRIMARY：最外层的查询
UNION：在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNION：UNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULT：UNION的结果
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的一个SELECT，依赖了外面的查询
DERIVED：依赖表示包含在FROM子句的子查询中的SELECT，MySQL 会递归执行并将结果放到一个临时表中。MySQL 内部将其称为是 Derived table （派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED：派生表，依赖了其他的表
MATERIALIZED：物化子查询
UNCACHEABLE SUBQUERY：子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNION：UNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

# `table`

表示当前这一行正在访问哪张表，如果 SQL 定义了别名，则展示表的别名。

# `partitions`

当前查询匹配记录的分区。对于未分区的表，返回NULL。

# type

连接类型，有如下几种取值，性能从好到坏排序 如下：

NULL：MySQL 不用访问表或者索引，直接就能够得到结果
system：该表只有一行（相当于系统表），system是const类型的特例
const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const查询数据非常快，因为它仅仅读取一次即可
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用primary key或者unique index作为关联条件。性能仅次于system及const
ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的
fulltext：全文索引
ref_or_null：该类型类似于ref，但是 MySQL 会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
```
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
```
1
2
index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引
index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引
range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN等操作符
index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
- 如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
- 按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。
ALL：全表扫描，性能最差

# `possible_keys`

展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

# `key`

表示 MySQL 实际选择的索引。

# `key_len`

索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大 1 字节。

# `ref`

ref列展示的就是与索引列作等值匹配的值，常见的有：const（常量），func，NULL，字段名。

# `rows`

MySQL 估算会扫描的行数，数值越小越好。

# `filtered`

表示符合查询条件的数据百分比，最大 100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是 500。

# `Extra`

展示有关本次查询的附加信息，取值如下：

Distinct：查找distinct值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行
Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问NULL值的时候使用
Impossible HAVING：HAVING子句始终为 false，不会命中任何行
Using filesort：当Query中包含ORDER BY操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。

官方解释：“MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”
Using index：通过二级普通索引查找，实现了覆盖索引，不用进行回表查询
Using index condition：通过二级普通索引查找，在通过索引查到的结果后还有where条件过滤，而且这个过滤筛选是只需要用二级普通索引就可以实现，不用在内存中进行判断筛选，即索引下推。但是需要回表查询需要的字段值。
Using where：不管有没有通过索引查找，只要加载了数据到内存进行where条件筛选，都是
Using index for group-by：数据访问和Using index 一样，所需数据只须要读取索引，当Query中使用GROUP BY或DISTINCT子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by
Using temporary：为了解决该查询，MySQL 需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和ORDER BY子句，通常会发生这种情况

# 分析 SQL 的执行成本

执行成本

IO 成本

MySQL 的 InnoDB 存储引擎会把数据存储到磁盘上，这时候无论怎么优化 SQL，都是需要从磁盘中读取数据到内存，就是 IO 成本，每次读取磁盘，至少读一页，InnoDB 一个页的数据存储大小是 16KB，这个磁盘的 IO 时间成本是 1.0，这里的 1.0 没有单位，就是个比较值。
CPU 成本

从磁盘读到数据后要放到内存中处理数据的过程，这是 CPU 成本。读取后并且检测可能的where条件，这个 CPU 的 IO 时间成本为 0.2，这里的 1.0 和 0.2 被称之为成本常数。

单表查询成本计算步骤

根据搜索条件，找出所有可能使用的索引，也就是EXPLAIN的possible_keys
计算全表扫描的代价
计算使用不同索引执行查询的代价。尤其是可能的索引为多个的时候
对比各种执行方案的代价，找出成本最低的哪一个

查看表状态信息

mysql> show table status like 'payment'\G
*************************** 1. row ***************************
           Name: payment
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 16086
 Avg_row_length: 98
    Data_length: 1589248
Max_data_length: 0
   Index_length: 983040
      Data_free: 4194304
 Auto_increment: 16050
    Create_time: 2023-04-06 09:17:30
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Engine：引擎
Row_format：数据格式
Rows：表数据行数，MyISAM 的这个数据行数统计是准确的，而 InnoDB 是估值是不准确的
Data_length：该表所占用空间的字节数。

SQL 执行成本分析

mysql> explain format=json select * from payment where customer_id = 203 or amount = 3.96\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3314.20"
    },
    "table": {
      "table_name": "payment",
      "access_type": "ALL",
      "possible_keys": [
        "idx_fk_customer_id"
      ],
      "rows_examined_per_scan": 16086,
      "rows_produced_per_join": 1632,
      "filtered": "10.15",
      "cost_info": {
        "read_cost": "2987.65",
        "eval_cost": "326.55",
        "prefix_cost": "3314.20",
        "data_read_per_join": "38K"
      },
      "used_columns": [
        "payment_id",
        "customer_id",
        "staff_id",
        "rental_id",
        "amount",
        "payment_date",
        "last_update"
      ],
      "attached_condition": "((`sakila`.`payment`.`customer_id` = 203) or (`sakila`.`payment`.`amount` = 3.96))"
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

使用show status like 'last_query_cost';，可以查看上一条 SQL 的执行成本：

mysql> select * from payment where customer_id = 203 or amount = 3.96;

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 3314.199000 |
+-----------------+-------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 通过`performance_schema`分析 SQL

略

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

MySQL 5.6 提供了对 SQ L的跟踪 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)

1
2
3
4
5

接下来执行想做trace的 SQL 语句，例如想了解租赁表rental中库存编号inventory_id为 4466 的电影拷贝在出租日期rental_date为 2005-05-25 4:00:00～5:00:00 之间出租的记录：

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.00 sec)

1
2
3
4
5
6
7

最后，检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道 MySQL 是如何执行 SQL 的：
```
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
```
1

# 索引问题

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个”好的“索引性能要好两个数量级。

# 索引的存储类型

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。MySQL5.7 提供了一下 4 种索引：

B-Tree 索引：最常见的索引类型，大部分引擎都支持 B 树索引。
HASH 索引：只有 Memory 引擎支持，使用场景简单。
R-Tree 索引（空间索引）：空间索引是 MyISAM 的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text（全文索引）：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从MySQL 5.6 版本开始提供对全文索引的支持。

InnoDB 引擎使用的是 B+ 树，MySQL8.0 开始支持函数索引。

索引	InnoDB	MyISAM	Memory
B-Tree 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-Tree 索引	不支持	支持	不支持
Full-text（全文索引）	支持	支持	不支持

比较常用到的索引就是 B-Tree 索引和 Hash 索引。

# B-Tree 索引

B-Tree 索引适用于全键值、键值范围或前缀查找。其中前缀查找只适用于根据最左前缀的查找。B-Tree 索引对如下类型的查询有效：

全值匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另一列
只访问索引的查询

B-Tree 索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

# Hash 索引

Hash 索引相对简单，只有 Memory/Heap 引擎支持Hash索引。

优点：Hash 索引适用于Key-Value查询，通过 Hash索引要比通过 B-Tree 索引查询更迅速
缺点：Hash 索引不适用范围查询，例如 <、>、<=、>= 这类操作。如果使用Memory/Heap 引擎并且 where 条件中不使用“=”进行索引列，那么不会用到索引。Memory/Heap 引擎只有在“=”的条件下才会使用索引。

# 索引的优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机 I/O 变为顺序 I/O

# MySQL 如何使用索引

为了避免混淆，重命名租赁表rental上的索引rental_date为idx_rental_date:

mysql> alter table rental rename index rental_date to idx_rental_date;

注意：该命令在 MySQL5.7 及以上版本开才是支持。

# MySQL 中能够使用索引的典型场景

匹配全值（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。

例如，租赁表rental中通过指定出租日期rental_date + 库存编号inventory_id + 客户编号 customer_id的组合条件进行查询，从执行计划的 key和extra两字段的值看到优化器选择了复合索引idx_rental_date：

mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: idx_rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14

explain输出结果中字段type的值为const，表示是常量；字段key的值为idx_rental_date，表示优化器选择索引idx_rental_date进行扫描。

匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。

例如，检索租赁表rental中客户编号customer_id在指定范围内的记录：

mysql> explain select * from rental where customer_id >= 373 and customer_id < 400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14

类型type为range说明优化器选择范围查询，索引key为idx_fk_customer_id说明优化器选择索引 idx_fk_customer_id来加速访问，注意到这个例子中Extra列为Using index condition，表示优化器除了利用索引来加速访问之外，还需要根据索引回表查询数据。

匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、（col1 + col2）、（col1 + col2 + col3）的等值查询利用到，可是不能够被 col2、（col2 + col3）的等值查询利用到；

以支付表payment为例，如果查询条件中仅包含索引的第一列支付日期payment_date和索引的第三列更新时间last_update的时候，从执行计划 key和extra看到优化器仍然能够使用复合索引idx_payment_date进行条件过滤：

mysql> alter table payment add index idx_payment_date (payment_date,amount,last_update);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from payment where payment_date = '2006-02-14 15:16:03'and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 10.00
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

但是，如果仅仅选择复合索引idx_payment_date的第二列支付金额amount和第三列更新时间last_update进行查询时，那么执行计划显示并不会利用到索引idx_payment_date：

mysql> explain select * from payment where amount = 3.98 and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高。

对比上一个例子使用Select *，本次选择查询的字段都包含在索引idx_payment_date中时，能够看到查询计划有了一点变动：
```
mysql> explain select last_update from payment where payment_date = '2006-02-14 15:16:03' and amount = 3.98\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 8
     filtered: 100.00
        Extra: Using index
```
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Extra部分变成了Using index，也就意味着，现在直接访问索引就足够获取到所需要的数据，不需要通过索引回表，Using index也就是平常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提升效率。

匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

例如，现在需要查询出标题title是以 AFRICAN 开头的电影信息，从执行计划能够清楚看到，idx_title_desc_part索引被利用上了：

mysql> create index idx_title_desc_part on film_text (title(10), description(20));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 42
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Extra值为Using where表示优化器需要通过索引回表查询数据。

能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）。

例如，需要查询出租日期rental_date为指定日期且客户编号customer_id为指定范围的库存：

mysql> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <= 400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_rental_date,idx_fk_customer_id
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using where; Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14

类型type为ref说明优化器选择非唯一索引匹配，索引key为idx_rental_date说明优化器选择索引idx_rental_date帮助加速查询，同时由于只查询索引字段inventory_id的值，所以在Extra部分能看到Using index，表示查询使用了覆盖索引扫描。

如果列名是索引，那么使用column_name is null就会使用索引。

例如，查询支付表payment的租赁编号rental_id字段为空的记录就用到了索引：

mysql> explain select * from payment where rental_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14

索引下推。MySQL 5.6引入了 Index Condition Pushdown（ICP）的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

mysql> explain select * from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <= 400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: idx_rental_date,idx_fk_customer_id
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14

Using index condition 就表示 MySQL 使用了 ICP 来进一步优化查询，在检索的时候，把条件customer_id的过滤操作下推到存储引擎层来完成，这样能够降低不必要的 IO 访问。

# 查找索引但不能使用索引的典型场景

以%开头的LIKE查询不能够利用 B-Tree 索引，执行计划中key的值为NULL表示没有使用索引：

mysql> explain select * from actor where last_name like '%NI%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

因为 B-Tree 索引的结构，所以以%开头的查询很自然就没法利用索引了，一般都推荐使用**全文索引（Fulltext）**来解决类似的全文检索问题。

也可以考虑利用 InnoDB 的表都是聚簇表的特点，采取一种轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快（某些特殊情况下，索引比表更大），而 InnoDB 表上二级索引idx_last_name实际上存储字段last_name还有主键actor_id，那么理想的访问方式应该是首先扫描二级索引idx_last_name获得满足条件last_name like '%NI%'的主键actor_id列表，之后根据主键回表去检索记录，这样访问避开了全表扫描演员表actor产生的大量 IO 请求。验证一下：

mysql> explain select * from (select actor_id from actor where last_name like '%NI%')a, actor b where a.actor_id = b.actor_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_actor_last_name
      key_len: 182
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

从执行计划中能够看到，内层查询的Using index代表索引覆盖扫描，之后通过主键join操作去演员表actor中获取最终查询结果，理论上是能够比直接全表扫描更快一些。

数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。

例如，演员表actor中的姓氏字段last_name是字符型的，但是 SQL 语句中的条件值 1 是一个数值型值，因此即便存在索引idx_last_name，MySQL 也不能正确地用上索引，而是继续进行全表扫描：

mysql> explain select * from actor where last_name = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

加上引号之后，再次检查执行计划，就发现使用上索引了：

mysql> explain select * from actor where last_name = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14

复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 Leftmost，是不会使用复合索引的：

mysql> explain select * from payment where amount = 3.98 and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 1.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。

例如，查询以“S”开头的标题的电影，需要返回的记录比例较大，MySQL 就预估索引扫描还不如全表扫描更快：

mysql> update film_text set title = concat('S', title);
Query OK, 1000 rows affected (0.12 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0
mysql> explain select * from film_text where title like 'S%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: ALL
possible_keys: idx_title_desc_part,idx_title_description
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在查询的时候，筛选性越高越容易使用到索引，筛选性越低越不容易使用索引。

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

mysql> explain select * from payment where customer_id = 203 or amount = 3.96\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 10.15
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加 I/O 访问，一次全表扫描过滤条件就足够了。

SQL 中，不等于操作符（<>和!=）会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引。

mysql> explain select * from payment where staff_id != 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: idx_fk_staff_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 49.69
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具体如下。

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 5     |
| Handler_read_key      | 6     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 1120  |
+-----------------------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描。值越低越好。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要 MySQL 扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

# 优化方法

# 定期分析表和检查表

# `ANALYZE TABLE`

语法：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

1
2

用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。

mysql> analyze table payment;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sakila.payment | analyze | status   | OK       |
+----------------+---------+----------+----------+

1
2
3
4
5
6

# `CHECK TABLE`

语法：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

1
2
3
4
5
6
7
8
9
10

检查表的作用是检查一个或多个表是否有错误。CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。

mysql> CHECK TABLE payment;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| sakila.payment | check | status   | OK       |
+----------------+-------+----------+----------+

1
2
3
4
5
6

# 定期优化表

# `OPTIMIZE`优化表

语法：

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

1
2

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。

对于 InnoDB 引擎的表来说，通过设置innodb_file_per_table参数，设置 InnoDB 为独立表空间模式，这样每个数据库的每个表都会生成一个独立的 ibd 文件，用于存储表的数据和索引，这样可以一定程度上减轻 InnoDB 表的空间回收问题。另外，在删除大量数据后，InnoDB 表可以通过alter table但是不修改引擎的方式来回收不用的空间：

mysql> alter table payment engine=innodb;

# 常用 SQL 的优化

# 大批量插入数据

load命令导入数据的语法：

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

有以下几种方式提高 InnoDB 表的导入效率。

已排序主键。因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
关闭唯一性校验。在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
关闭事务自动提交。如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

# 优化`INSERT`语句

如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句快（在大部分情况下，使用多个值表的INSERT语句能比单个INSERT语句快上好几倍）。

insert into test values(1,2),(1,3),(1,4)...

如果从不同客户插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完成后才进行插入。

# 优化`ORDER BY`语句

优化ORDER BY语句之前，首先来了解一下 MySQL 中的排序方式。先看customer表上的索引情况：

# MySQL 中有两种排序方式

第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高，例如：

mysql> explain select customer_id from customer order by store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_store_id
      key_len: 1
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14

第二种是通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小。

例如，按照商店store_id排序返回所有客户记录时，出现了对全表扫描的结果的排序：

mysql> explain select * from customer order by store_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14

又如，只需要获取商店store_id和顾客email信息时，对表customer的扫描就被覆盖索引 idx_storeid_email 扫描替代，此时虽然只访问了索引就足够，但是在索引idx_storeid_email上发生了一次排序操作，所以执行计划中仍然有Using Filesort。

mysql> alter table customer add index idx_storeid_email (store_id,email);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select store_id,email,customer_id from customer order by email\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_storeid_email
      key_len: 204
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using index; Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size 设置的排序区是每个线程独占的，所以同一个时刻，MySQL 中存在多个sort buffer排序区。

了解了 MySQL 排序的方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。

例如，查询商店编号store_id为 1，按照email逆序排序的记录主键customer_id时，优化器使用扫描索引idx_storeid_email直接返回排序完毕的记录：

mysql> explain select store_id, email, customer_id from customer where store_id = 1 order by email desc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_fk_store_id,idx_storeid_email
          key: idx_storeid_email
      key_len: 1
          ref: const
         rows: 326
     filtered: 100.00
        Extra: Using where; Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14

而查询商店编号store_id大于等于 1 小于等于 3，按照email排序的记录主键customer_id的时候，由于在索引idx_storeid_email里，先按照store_id排序，相同的store_id按照email排序，因此以下语句需要额外的排序：

mysql> explain select store_id, email, customer_id from customer where store_id >= 1 and store_id <= 3 order by email desc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index
possible_keys: idx_fk_store_id,idx_storeid_email
          key: idx_storeid_email
      key_len: 204
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using where; Using index; Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14

总结，下列SQL可以使用索引：

SELECT * FROM tabname ORDER BY key_part1,key_part2,... ;
SELECT * FROM tabname WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM tabname ORDER BY key_part1 DESC, key_part2 DESC;

1
2
3

但是在以下几种情况下则不使用索引：

order by的字段混合ASC和DESC

SELECT * FROM tabname ORDER BY key_part1 DESC, key_part2 ASC；

用于查询行的关键字与ORDER BY中所使用的不相同

SELECT * FROM tabname WHERE key2=constant ORDER BY key1

对不同的关键字使用ORDER BY

SELECT * FROM tabname ORDER BY key1, key2；

提示：这里的“不同的关键字”是指它们没有使用组合索引，例如key1和key2没有组合索引，它们分别有自己的索引。

# `Filesort`的优化

对于Filesort，MySQL 有两种排序算法：

两次扫描算法（Two Passes）：首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer中排序。如果排序区sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL 4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机 I/O 操作；优点是排序的时候内存开销较少。
一次扫描算法（Single Pass）：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量max_length_for_sort_data的大小和 Query 语句取出的字段总大小来判断使用哪种排序算法。如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种算法。

优化技巧：

适当加大系统变量max_length_for_sort_data 的值，能够让 MySQL 选择更优化的Filesort排序算法。当然，假如max_length_for_sort_data设置过大，会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。
适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_size参数是每个线程独占的，设置过大，会导致服务器SWAP严重，要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。
尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT *选择所有字段，这样可以减少排序区的使用，提高 SQL 性能。

# 优化`GROUP BY`语句

默认情况下，MySQL 对所有GROUP BY col1,col2,…的字段进行排序。这与在查询中指定ORDER BY col1,col2,…类似。因此，如果显式包括一个包含相同列的ORDER BY子句，则对 MySQL 的实际执行性能没有什么影响。

如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序：

mysql> alter table payment drop index idx_payment_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select payment_date, sum(amount) from payment group by payment_date\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16125
     filtered: 100.00
        Extra: Using temporary; Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

指定ORDER BY NULL禁止排序后的效果：

mysql> explain select payment_date, sum(amount) from payment group by payment_date order by null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16125
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

从上面的例子可以看出，第一个 SQL 查询语句需要进行Filesort，而第二个 SQL 由于ORDER BY NULL不需要进行Filesort，而上文提过Filesort往往非常耗费时间。

# 优化嵌套查询

MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

在下面的例子中，要从客户表customer中找到不在支付表payment中的所有客户信息：

mysql> explain select * from customer where customer_id not in (select customer_id from payment )\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: payment
   partitions: NULL
         type: index_subquery
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: func
         rows: 26
     filtered: 100.00
        Extra: Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

如果使用连接（JOIN）来完成这个查询工作，速度将会快很多。尤其是当payment表中对customer_id建有索引，性能将会更好：

mysql> explain select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: NULL
*************************** 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: Using where; Not exists

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

从执行计划中可以看出查询关联的类型从index_subquery调整为了ref，在MySQL 5.5 以下版本（包括MySQL 5.5），子查询的效率还是不如关联查询（JOIN）。

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

# 优化`OR`条件

对于含有OR的查询子句，如果要利用索引，则**OR之间的每个条件列都必须用到索引**；如果没有索引，则应该考虑增加索引。

查看表customer的索引：

OR之间的条件都有索引：

mysql> explain select * from customer where customer_id=1 or last_name='Zhang'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_last_name
          key: PRIMARY,idx_last_name
      key_len: 2,182
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_last_name); Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

可以发现查询正确地用到了索引，并且从执行计划的描述中，发现 MySQL 在处理含有OR字句的查询时，实际是对OR的各个字段分别查询后的结果进行了UNION操作。

OR之间的其中一个条件没有索引，SQL 就不能用到索引：

mysql> explain select * from customer where customer_id=1 or first_name='Tom'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.15
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是limit 1000,20，此时 MySQL排序出前 1020 条记录后仅仅需要返回第 1001到 1020条记录，前 1000 条记录都会被抛弃，查询和排序的代价非常高。

# 第一种优化思路：延迟关联

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

例如，对电影表film根据标题title排序后取某一页数据，直接查询的时候，能够从explain的输出结果中看到优化器实际上做了全表扫描，处理效率不高：

mysql> explain select film_id, description from film order by title limit 50,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14

而按照索引分页后回表方式改写 SQL 后，从explain的输出结果中已经看不到全表扫描了：

mysql> explain select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: b.film_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 514
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

这种方式让 MySQL 扫描尽可能少的页面来提高分页效率。

# 第二种优化思路：书签方式

把LIMIT查询转换成某个位置的查询。

例如，假设每页 10 条记录，查询支付表payment中按照租赁编号rental_id逆序排序的第 42 页记录，能够看到执行计划走了全表扫描：

mysql> explain select * from payment order by rental_id desc limit 410,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14

翻页的过程中通过增加一个参数last_page_record，用来记录上一页最后一行的租赁编号rental_id，例如第 41 页最后一行的租赁编号rental_id=15640：

mysql> select payment_id, rental_id from payment order by rental_id desc limit 400,10;
+------------+-----------+
| payment_id | rental_id |
+------------+-----------+
|       1669 |     15649 |
|       2193 |     15648 |
|       6785 |     15647 |
|       3088 |     15646 |
|       5831 |     15645 |
|       1201 |     15644 |
|       8105 |     15643 |
|       4369 |     15642 |
|       6499 |     15641 |
|       7095 |     15640 |
+------------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

那么在翻页到第 42 页时，可以根据第 41 页最后一条记录向后追溯，相应的 SQL 可以改写为：

mysql> explain select * from payment where rental_id < 15640 order by rental_id desc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: NULL
         rows: 8043
     filtered: 100.00
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意，这样把LIMIT m,n 转换成LIMIT n的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。

# 使用 SQL 提示

SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

# `USE INDEX`

在查询语句中表名的后面，添加USE INDEX来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

不使用USE INDEX：

mysql>  explain select count(*) from rental\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_staff_id
      key_len: 1
          ref: NULL
         rows: 16008
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用USE INDEX：

mysql> explain select count(*) from rental use index(idx_rental_date)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_rental_date
      key_len: 10
          ref: NULL
         rows: 16008
     filtered: 100.00
        Extra: Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# `IGNORE INDEX`

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX作为HINT。

mysql> explain select count(*) from rental ignore index (idx_rental_date)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_staff_id
      key_len: 1
          ref: NULL
         rows: 16008
     filtered: 100.00
        Extra: Using index

1
2
3
4
5
6
7
8
9
10
11
12
13
14

从执行计划可以看出，系统忽略了指定的索引，使用索引idx_fk_staff_id。

# `FORCE INDEX`

为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX作为HINT。

例如，当不强制使用索引的时候，因为大部分库存inventory_id的值都是大于1的，因此 MySQL 会默认进行全表扫描，而不使用索引，如下所示：

mysql> explain select * from rental where inventory_id > 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ALL
possible_keys: idx_fk_inventory_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16008
     filtered: 50.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用use index的hint：

mysql> explain select * from rental use index (idx_fk_inventory_id) where inventory_id >1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ALL
possible_keys: idx_fk_inventory_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16008
     filtered: 50.00
        Extra: Using where

1
2
3
4
5
6
7
8
9
10
11
12
13
14

发现仍然不行，MySQL 还是选择走全表扫描。但是，当使用FORCE INDEX进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入FORCE INDEX提示后再次执行上面的 SQL：

mysql> explain select * from rental force index (idx_fk_inventory_id) where inventory_id >1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_inventory_id
          key: idx_fk_inventory_id
      key_len: 3
          ref: NULL
         rows: 8004
     filtered: 100.00
        Extra: Using index condition

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 常用 SQL 技巧

# 正则表达式的使用

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串，通常缩写成REGEX或者REG EXP。

MySQL 利用REGEXP命令提供给用户扩展的正则表达式功能，REGEXP实现的功能类似 UNIX 上GREP和SED的功能，并且REGEXP在进行模式匹配时是区分大小写的。熟悉并掌握REGEXP的功能可以使模式匹配工作事半功倍。

符号	符号说明
^	在字符串的开始出进行匹配
$	在字符串的末尾出进行匹配
.	匹配任意单个字符，包括换行符
[...]	匹配出括号内的任意字符
[^...]	不匹配括号内的任意字符
a*	匹配零个或多个a（包括空串）
a+	匹配 1 个或多个 a（不包括空串）
a?	匹配 1 个或零个 a
a\|b	匹配 a 或 b
a(m)	匹配 m 个 a
a(m,)	至少匹配 m 个 a
a(m, n)	匹配 m 到 n 个 a
a(,n)	匹配 0 到 n 个 a（即至多 n 个 a）
(...)	将模式元素组成单一元素（分组）

^在字符串的开始处进行匹配，返回结果为 1 表示匹配，返回结果为 0 表示不匹配。下例中尝试匹配字符串“abcdefg”是否以字符“a”开始：

mysql> select 'abcdefg' REGEXP '^a';
+-----------------------+
| 'abcdefg' REGEXP '^a' |
+-----------------------+
|                     1 |
+-----------------------+

1
2
3
4
5
6

# 巧用`RAND()`提取随机行

大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。它的原理其实就是ORDER BY RAND()能够把数据随机排序。

对表category随机采样：

mysql> select * from category order by rand();

order by rand() limit n对表category随机采 n 条样：

mysql> select * from category order by rand() limit 5;

# 利用`GROUP BY`的`WITH ROLLUP`子句

在 SQL 语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。

在支付表payment中，按照支付时间payment_date的年月、经手员工编号staff_id列分组对支付金额amount列进行聚合计算如下：

mysql> select date_format(payment_date, '%Y-%m'), staff_id, sum(amount) from payment group by date_format(payment_date, '%Y-%m'), staff_id;
+------------------------------------+----------+-------------+
| date_format(payment_date, '%Y-%m') | staff_id | sum(amount) |
+------------------------------------+----------+-------------+
| 2005-05                            |        1 |     2621.83 |
| 2005-05                            |        2 |     2202.60 |
| 2005-06                            |        1 |     4776.36 |
| 2005-06                            |        2 |     4855.52 |
| 2005-07                            |        1 |    14003.54 |
| 2005-07                            |        2 |    14370.35 |
| 2005-08                            |        1 |    11853.65 |
| 2005-08                            |        2 |    12218.48 |
| 2006-02                            |        1 |      234.09 |
| 2006-02                            |        2 |      280.09 |
+------------------------------------+----------+-------------+
10 rows in set (0.03 sec)

mysql> select date_format(payment_date, '%Y-%m'), staff_id, sum(amount) from payment group by date_format(payment_date, '%Y-%m'), staff_id with rollup;
+------------------------------------+----------+-------------+
| date_format(payment_date, '%Y-%m') | staff_id | sum(amount) |
+------------------------------------+----------+-------------+
| 2005-05                            |        1 |     2621.83 |
| 2005-05                            |        2 |     2202.60 |
| 2005-05                            |     NULL |     4824.43 |
| 2005-06                            |        1 |     4776.36 |
| 2005-06                            |        2 |     4855.52 |
| 2005-06                            |     NULL |     9631.88 |
| 2005-07                            |        1 |    14003.54 |
| 2005-07                            |        2 |    14370.35 |
| 2005-07                            |     NULL |    28373.89 |
| 2005-08                            |        1 |    11853.65 |
| 2005-08                            |        2 |    12218.48 |
| 2005-08                            |     NULL |    24072.13 |
| 2006-02                            |        1 |      234.09 |
| 2006-02                            |        2 |      280.09 |
| 2006-02                            |     NULL |      514.18 |
| NULL                               |     NULL |    67416.51 |
+------------------------------------+----------+-------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

从上面的例子中可以看到第 2 个 SQL 语句的结果比第一个 SQL 语句的结果多出了很多行，而这些行反映出了更多的信息，例如，第2个 SQL 语句的结果的前 2 行表示 2005-05 月份各个员工（1、2）的经手的支付金额，而第 3 行表示 2005-05 月份总支付金额为 4824.43，这个信息在第一个 SQ L语句中是不能反映出来的，第 16 行表示总支付金额为 67416.51，这个信息在第一个 SQL 语句中是没有的。

其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。

# 数据库名、表名大小写问题

在 MySQL 中，数据库对应操作系统下的数据目录。数据库中的每个表至少对应数据库目录中的一个文件（也可能是多个，这取决于存储引擎）。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。在大多数 UNIX 环境中，由于操作系统对大小写的敏感性导致了数据库名和表名对大小写敏感性，而在 Windows 中，由于操作系统本身对大小写不敏感，因此在 Windows 下的 MySQL 数据库名和表名对大小写也不敏感。

列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在 UNIX 中对大小写敏感，但在 Windows 或 Mac OS X 中对大小写不敏感。

# 小结

SQL 优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问题都是由不合适的 SQL 语句造成。

优化 SQL 的步骤：
1. 使用show status查看各 SQL 的执行频率；
2. 通过慢查询日志和show processlist命令定位执行效率较低的 SQL；
3. 通过EXPLAIN分析 SQL 的执行计划；
4. 判断数据库是不是在刷脏页；
5. 判断单表数据是否过大；
6. 判断系统和网络资源是否遇到了瓶颈；
7. 排查锁问题；
8. 确定原因。可能的原因有：SQL 语句不合理；索引设置不正确；数据库配置等问题。
InnoDB 引擎默认使用 B+ 树作为索引的数据结构。
编写 SQL 时，需要注意 MySQL 索引失效的场景。
慢 SQL 优化思路：
- 避免不需要的列，避免select *，节省资源、减少网络开销，避免回表查询。
- 分页优化
  - 延迟关联
  - 书签方式
- 索引优化
  - 利用覆盖索引
  - 使用OR时注意两侧字段索引问题
  - 避免使用!=、<>、is null和is not null
  - 避免列上函数运算
  - 正确地使用联合索引
- JOIN 优化
  - 优化子查询
  - 小表驱动大表
  - 适当增加冗余字段
  - 避免join太多的表
- 排序优化，利用索引扫描做排序
- union优化，条件下推

# 参考

《深入浅出 MySQL：数据库开发、优化与管理维护》
《高性能 MySQL》

上次更新: 2024/05/29, 14:25:22

← MySQL MVCC MySQL 慢查询日志→

SQL优化

# MySQL SQL优化

# 优化 SQL 的步骤

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

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

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

# id

# select_type

# table

# partitions

# type

# possible_keys

# key

# key_len

# ref

# rows

# filtered

# Extra

# 分析 SQL 的执行成本

# 通过performance_schema分析 SQL

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

# 索引问题

# 索引的存储类型

# B-Tree 索引

# Hash 索引

# 索引的优点

# MySQL 如何使用索引

# MySQL 中能够使用索引的典型场景

# 查找索引但不能使用索引的典型场景

# 查看索引使用情况

# 优化方法

# 定期分析表和检查表

# ANALYZE TABLE

# CHECK TABLE

# 定期优化表

# OPTIMIZE优化表

# 常用 SQL 的优化

# 大批量插入数据

# 优化INSERT语句

# 优化ORDER BY语句

# MySQL 中有两种排序方式

# Filesort的优化

# 优化GROUP BY语句

# 优化嵌套查询

# 优化OR条件

# 优化分页查询

# 第一种优化思路：延迟关联

# 第二种优化思路：书签方式

# 使用 SQL 提示

# USE INDEX

# IGNORE INDEX

# FORCE INDEX

# 常用 SQL 技巧

# 正则表达式的使用

# 巧用RAND()提取随机行

# 利用GROUP BY的WITH ROLLUP子句

# 数据库名、表名大小写问题

# 小结

# 参考

评论

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

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

# `id`

# `select_type`

# `table`

# `partitions`

# `possible_keys`

# `key`

# `key_len`

# `ref`

# `rows`

# `filtered`

# `Extra`

# 通过`performance_schema`分析 SQL

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

# `ANALYZE TABLE`

# `CHECK TABLE`

# `OPTIMIZE`优化表

# 优化`INSERT`语句

# 优化`ORDER BY`语句

# `Filesort`的优化

# 优化`GROUP BY`语句

# 优化`OR`条件

# `USE INDEX`

# `IGNORE INDEX`

# `FORCE INDEX`

# 巧用`RAND()`提取随机行

# 利用`GROUP BY`的`WITH ROLLUP`子句