在开发时，由于初始数据较少，所以更注重功能上的实现。但当应用系统正式上线后，随着生产的数据急剧增加，性能的影响也越来越大，此时有问题的SQL语句就成为了系统性能的瓶颈，因此我们必须要对其进行优化。
下面的案例表使用的是 MySQL 的案例库 sakila，这是一个由 MySQL 官方提供的可以模拟电影出租厅信息管理系统的数据库。
压缩包包含三个文件：
sakila-schema.sql表结构创建sakila-data.sql表数据sakila.mwb数据模型，可以使用 MySQL Workbench 查看
优化步骤
SQL执行频率
可以通过 SHOW [session|global] status 命令查看数据库状态，session 表示当前链接，global 表示自数据库启动以来的统计结果。默认是 session。
下面是显示了当前 session 部分统计参数的值。
mysql> show status like 'com_%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
...
+-----------------------------+-------+
149 rows in set (0.00 sec)Com_xxx 表示每个 xxx 语句执行次数，通常我们比较关注以下几个统计参数：
Com_select执行 SELECT 操作的次数，一次查询只累加 1。Com_insert执行 INSERT 操作的次数Com_updae执行 UPDATE 操作的次数Com_delete执行 DELETE 操作的次数
上面的参数，所有的存储引擎都会累计，下面仅针对 Innodb 引擎，累加算法也不同：
Innodb_rows_readSELECT 查询返回的行数Innodb_rows_inserted执行 INSERT 插入的行数Innodb_rows_uodated执行 UPDATE 更新的行数Innodb_rows_deleted执行 DELETE 删除的行数
通过以上的几个参数，很容易的了解到当前数据库的应用是以写为主，还是读为主，以及各类SQL执行的比例是多少。对于更新操作的计数，是对于执行次数的计数，不论是提交还是回滚都会累加。
事务计数：
Com_commit提交次数Com_rollback回滚次数
对于回滚操作非常频繁的数据库，意味着应用编写存在问题。
其他参数：
Connections试图链接 MySQL 服务器的次数Uptime服务器工作时间Slow_queries慢查询次数
定位效率低的SQL
可以通过以下两种方式查看执行效率较低的 SQL 语句
- 通过慢查询日志定位那些执行效率较低的SQL语句，将 slow_query_log 参数设置为 1 开启慢查询日志，MySQL 会将所有执行时间超过 slow_query_time 参数设置阈值的 SQL，写入 slow_query_file 参数所指定的文件中。
- 慢查询日志只有查询结束后才会记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以事实时的查看 SQL 执行的情况，同时对一些锁表操作进行优化。
EXPLAIN 分析 SQL 执行计划
通过上面步骤查询到效率较低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MYSQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何链接和链接的顺序，比如想统计某个 email 为租赁电影拷贝所支付的总金额，需要关联客户表和付款表，并且对金额字段做和 (sum) 操作，相应 SQL 如下：
mysql> explain SELECT SUM(amount) FROM customer a, payment b where 1 AND a.customer_id = b.customer_id AND email = 'JANE.BENNETT@sakilacustomer.org'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)对每个列简单描述一下：
select_type表示 SELECT 类型- SIMPLE 简单表，即不使用表连接或子查询
- PRIMARY 主查询，即外层的查询
- UNION UNION 中的第二个或后面的查询语句
- SUBQUERY 子查询中的第一个 SELECT 等
table输出结果集的表type表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见类型如下，由上至下性能最差到最好：- ALL 全表扫描，MySQL 需要遍历全表来找到匹配的行
- index， 索引全扫描，MySQL 遍历整个索引来找到匹配的行
- range， 索引范围扫描，常见于 <、<=、>、>= between 等操作符
- ref 使用非唯一索引扫描或唯一索引的前缀扫描，返回某个匹配值的单独记录行。
- eq_ref 类似于 ref，区别就在于使用的索引是唯一索引，对于每个索引键值。
- ref_or_null 类似 ref，区别在于标间中包含对 NULL的查询。
- const/system 单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当常量来处理，例如根据该主键或者唯一索引进行的查询。
- NULL 表示不访问表或索引，就能得到结果。
- index_merge 索引合并优化
- unqiue_subquery in 的后面是一个查询主键字段的子查询
- unqiue_subquery in 的后面是一个查询非唯一索引字段的子查询
possible_keys表示查询时可能使用到的索引key表示查询时实际使用到的索引key_len使用到索引字段的长度rows扫描行的数量filtered表示返回行数占查询时读到行数的百分比，这个值仅在 type 是 index 或 all 的时候才有意义.Extra执行情况的说明和描述，包含不适合在其它列中显示但是对执行计划非常重要的额外信息。
使用 explain partitions 命令可以查看SQL所访问的分区，结果中有一列 partitions 显示分区名字。
通过 show profile 分析 SQL
MySQL 5.0.37 版本增加了对 show profiles 和 show profile 的支持。通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile：
mysql> SELECT @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)默认 profiling 是关闭的，可以通过 set语句在 SESSION 级别中开启 profiling：
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)通过 profile，用户可以更清楚的了解SQL执行的过程。比如，我们知道 MyISAM 表有表元数据缓存（例如行数，count(*)）,当 MyISAM 表获取行数时是不需要消耗太多资源的，二对于 InnoDB 来说，因为没有元数据，所以查询执行起来是比较慢的，下面做个实验验证一下：
首先在 InnoDB 表上执行一个 count(*) 查询：
mysql> SELECT count(*) FROM area;
+----------+
| count(*) |
+----------+
| 46299 |
+----------+
1 row in set (0.02 sec)然后通过 show profiles 语句，看到当前 SQL 的 Query ID 为 1：
mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------+
| 1 | 0.00585800 | SELECT count(*) FROM area |
+----------+------------+---------------------------+
1 row in set (0.01 sec)通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间：
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000062 |
| checking permissions | 0.000004 |
| Opening tables | 0.000539 |
| init | 0.000009 |
| System lock | 0.000004 |
| optimizing | 0.000003 |
| statistics | 0.000008 |
| preparing | 0.000006 |
| executing | 0.000001 |
| Sending data | 0.005842 |
| end | 0.000004 |
| query end | 0.000008 |
| closing tables | 0.000005 |
| freeing items | 0.000046 |
| cleaning up | 0.000007 |
+----------------------+----------+
15 rows in set (0.05 sec)通过查询 show profile for query 的输出，可以发现 COUNT(*) 执行的过程中 时间主要消耗在 Sending data 这个状态上。为了更清晰地看到排序的结果，可以查询 INFORMATION_SCHEMA.PROFILNG 表，并按照这个时间做 DESC 排序。
mysql> SET @query_id = 2;
Query OK, 0 rows affected (0.00 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 Rct_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 | Rct_R | CallS | R/Call |
+----------------------+----------+-------+-------+--------------+
| Sending data | 0.005842 | 89.22 | 1 | 0.0058420000 |
| Opening tables | 0.000539 | 8.23 | 1 | 0.0005390000 |
...
| executing | 0.000001 | 0.02 | 1 | 0.0000010000 |
+----------------------+----------+-------+-------+--------------+
15 rows in set (0.05 sec)在获取到最消耗时间的线程状态之后，MySQL 支持进一步选择 all、cpu、block io、content、switch、page、faults 等明细类型来查看 MySQL 在哪方面资源上耗费了过高的时间，比如选择查看 CPU 的时间：
mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000062 | 0.000000 | 0.000000 |
...
| executing | 0.000001 | 0.000000 | 0.000000 |
| Sending data | 0.005842 | 0.000000 | 0.000000 |
| end | 0.000004 | 0.000000 | 0.000000 |
...
| cleaning up | 0.000007 | 0.000000 | 0.000000 |
+----------------------+----------+----------+------------+
15 rows in set (0.05 sec)能够发现在 Sending data 状态下，时间主要消耗在 CPU 上了。
对比同一个表的 MyISAM 引擎，同样执行 count(*) 操作:
mysql> SELECT count(*) FROM area_myisam;
+----------+
| count(*) |
+----------+
| 46299 |
+----------+
1 row in set (0.06 sec)
mysql> show profiles;
...
mysql> show profile for query 6;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000067 |
| checking permissions | 0.000004 |
| Opening tables | 0.000222 |
| init | 0.000008 |
| System lock | 0.000005 |
| optimizing | 0.000003 |
| executing | 0.000005 |
| end | 0.000001 |
| query end | 0.000002 |
| closing tables | 0.000005 |
| freeing items | 0.000052 |
| cleaning up | 0.000007 |
+----------------------+----------+
12 rows in set (0.06 sec)可以看到 InnoDB 经历了 Sending data 状态，存在访问数据的过程，而 MyISAM 在 executing 之后直接结束了查询，完全不需要访问数据。
show profile 能够在做 SQL 优化时帮助我们了解时间都消耗到了那些地方，而 MySQL 5.6 之后通过 trace 文件进一步向我们展示了优化器是如何选择执行计划的。
performance_schema 中的一系列性能视图代替。
通过 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)比如需要查询租赁表中 4466 电影 在 2005-05-25 04:00:00 ~ 2005-05-25 05:00:00 的出租记录：
mysql> SELECT rental_id FROM rental WHERE rental_date BETWEEN '2005-05-25 04:00:00' AND '2005-05-25 05:00:00' AND inventory_id = 4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.00 sec)然后查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就知道 MySQL 是如何执行 SQL 语句的：
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;
*************************** 1. row ***************************
QUERY: SELECT rental_id FROM rental WHERE rental_date BETWEEN '2005-05-25 04:00:00' AND '2005-05-25 05:00:00' AND inventory_id = 4466
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `rental`.`rental_id` AS `rental_id` from `rental` where ((`rental`.`rental_date` between '2005-05-25 04:00:00' and '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))"
}
] /* steps */
} /* join_preparation */
}
...
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)文件中记录了很多信息，包括访问表的路径、行数、成本等，来帮助读者对执行计划的选择进行分析，后面会有一些使用的例子。
确定问题并采取优化措施
经过上面的步骤，基本就可以确认问题出现的原因了。此时用户可以根据情况采用相应的措施，进行优化提高 SQL 的执行效率。
在上面 “EXPLAIN 分析 SQL 执行计划” 的例子中，已经可以确认是由于对客户表 的全表扫描导致效率不理想，那么我们可以对需要查询的字段添加索引来优化：
mysql> create index idx_email on customer(email);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0创建索引后，我们再看一下语句的执行计划：
mysql> explain SELECT SUM(amount) FROM customer a, payment b where 1 AND a.customer_id = b.customer_id AND email = 'JANE.BENNETT@sakilacustomer.org'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: PRIMARY,idx_email
key: idx_email
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)可以发现，建立索引后对客户表的扫描行数明显减少，可见索引可以大大提高数据库的访问速度，在表数据量很庞大的时候尤其明显。
(第一次走了 All 全表扫描，第二次走了 ref 索引扫描 )
索引问题
索引是数据库优化中最重要的手段之一，通过索引可以解决大多数的SQL性能问题。
索引的存储分类
索引是在存储引擎层实现的，而不是在服务器层实现，所以每一种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。目前 MySQL 提供了下面四种索引。
- B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引
- HASH 索引：只有 Memory/NDB 引擎支持，使用场景简单
- R-Tree 索引（空间索引）： MyISAM 的一个特殊索引类型，主要用于地理空间数据，通常使用较少
- Full-text （全文索引）：也是 MyISAM 的一个特殊索引，主要用于全文索引，InnoDB 从 MySQL 5.6 版本开始支持全文索引。
MySQL 8.0.13 版本开始支持函数索引，在之前的版本中可以使用两种方式实现函数索引的功能。
前缀索引
对某一列的前面某一部分进行索引，如标题的 title 字段，可以只取前10个字符进行索引，这个特性大大缩小了索引文件的大小，但前缀索引也有缺点，在排序和分组时无法使用。在设计表结构的时候也可以对文本列使用该特性进行灵活设置。
创建前缀索引的例子：
mysql> create index idx_title on film(title(10));
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0虚拟列索引
大多数商业数据库中，早已经支持了函数索引，而在 MySQL 5.7 开始，可以使用创建虚拟列的方式来实现函数索引的功能：
在支付表执行如下语句：
mysql> desc SELECT * FROM `payment` WHERE WEEK(payment_date) < 25\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 where
1 row in set, 1 warning (0.00 sec)很明显，因为在查询条件上使用了函数，导致索引失效走了全表扫描。
此时，我们创建一个虚拟列 vm_payment_date:
mysql> ALTER TABLE payment add column vm_payment_date int generated always as (WEEK(payment_date));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0然后在虚拟列上添加索引：
mysql> ALTER TABLE payment add key idx_vm_payment_date(vm_payment_date);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0然后再次查看执行计划：
mysql> desc SELECT * FROM `payment` WHERE WEEK(payment_date) < 25\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: range
possible_keys: idx_vm_payment_date
key: idx_vm_payment_date
key_len: 5
ref: NULL
rows: 2697
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)现在函数已经可以走索引，性能大幅提升。
mysql> ALTER TABLE payment_myisam add key idx_vm_payment_date(vm_payment_date);
1478 - Table storage engine 'MyISAM' does not support the create option 'Index on virtual generated column'
MyISAM、InnoDB、Memory 引擎支持的索引类型比较
| 索引 | MyISAM | InnoDB | Memory |
|---|---|---|---|
| B-Tree | 支持 | 支持 | 支持 |
| HASH | 不支持 | 不支持 | 支持 |
| R-Tree | 支持 | 不支持 | 不支持 |
| Full-text | 支持 | 支持 | 不支持 |
比较常用的是B-Tree 和 Hash 索引，只有Memory/NDB 引擎支持完全的 Hash 索引，InnoDB 存储引擎 在MySQL 5.7 支持了自适应的 Hash 索引。所谓自适应，就是MySQL 根据数据访问频率和模式为某些热点页自动创建 Hash 索引，索引由 buffer pool 中的 B-tree 来自动完成，这个特性由 innodb_adaptive_hash_index 来控制，默认开启。
Hash 索引适用于 key-Value 查询，通过 Hash 索引要比 B-Tree 索引查询更加迅速；Hash 不适用范围查询，如果查询中 Where 条件不使用 "="，则不会用索引。
B-Tree 情况较为复杂，下面详细分析 MySQL 如何利用 B-Tree 索引的。
MySQL 如何使用索引
B-Tree 索引是最常见的索引，结构类似于二叉树，能够根据键值提供一行或一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。
可以利用 B-Tree 索引进行全关键字、关键字范围和关键字前缀查询。、
为了避免混淆，我们将 rental 表中的索引 rental_date 重命名为：idx_rental_date
mysql> ALTER TABLE rental RENAME index rental_date to idx_rental_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0使用索引的典型场景
匹配全值
对索引中所有列都指定具体值，即对索引中所有列都有等值匹配的条件。
例如，在租赁表中通过指定出租日期+库存编号+用户编号的组合条件进行查询：
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 row in set, 1 warning (0.00 sec)在 explain 结果中，type 字段为 const，表示常量，key 字段表示 优化器选择使用 idx_rental_date 索引进行扫描。
匹配值的范围查找
对索引的值进行范围查找。
例如：检索租赁表的客户编号在指定范围内的记录：
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 row in set, 1 warning (0.00 sec)type 为 range 说明优化器选择范围查询，索引 key 为表示使用 idx_fk_customer_id 来加速访问，Extra 值为 Using where 表示优化器除了利用索引提高访问速度外，还需要回表查询数据。
匹配最左原则
在索引中仅使用最左边的列来查找，比如 col1 + col2 + col3 在字段上的联合索引能包含 col1、col1+col2、col1+col2+col3 的等值查询利用到，可是不能够被 col2、col2+col3、col3 的等值查询利用到。
以支付表为例，如果查询条件仅包含 组合索引的第一列或第三列的时候，从执行计划看到，优化器仍然能够使用复合索引 idx_payment_date 进行条件过滤：
mysql> ALTER TABLE payment ADD INDEX idx_payment_date (payment_date,amount,last_update);
Query OK, 0 rows affected (0.11 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 row in set, 1 warning (0.00 sec)但如果仅选择复合索引 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 row in set, 1 warning (0.00 sec)覆盖索引
仅仅对索引进行查询，当查询的列都在索引的字段中时，查询的效率更高。例如，对比上一个例子，我们只查询 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 row in set, 1 warning (0.00 sec)Extra 部分变成了 Using index，这意味着，现在直接访问索引就足够获取所需要的数据，不需要通过索引回表，Using index 也就是平常说的覆盖索引扫描。只访问必须访问的数据，一般情况下，减少不必要的数据访问能够提升效率。
Using index，就说明走了覆盖索引。
匹配列前缀
仅使用索引中的第一列，并只包含第一列索引开头的一部分进行查找。例如，现在需要查询标题以 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.04 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 row in set, 1 warning (0.00 sec)Extra 列的值为 Using where 表示优化器需要通过索引回标查找数据。
匹配部分字段
能够实现索引匹配部分精确而其他部分进行范围匹配。
例如：查询出租日期为指定日期且客户编号为范围的库存：
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.86
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)类型为 ret 说明优化器选择前缀扫描，索引 key 为 idx_rental_date 说明优化器选择索引 idx_rental_date 帮助加速查询，同时因为查询字段在该索引中，所以 Extra 部分能看到 Using index 表示查询使用了覆盖索引扫描。
列名是索引
如果列明是索引，那么 column_name is null 就会使用到索引。
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 row in set, 1 warning (0.01 sec)
--- KEY `fk_payment_rental` (`rental_id`),ICP 特性
MySQL 5.6 引入了 Index Condition Pushdown 特性，进一步优化了查询，Pushdown 表示操作下放，某些情况下的条件过滤操作下放到存储引擎。
这个特性是默认开启的，为了演示，我们先关闭他，模拟 MySQL 5.6 之前的处理方法：
mysql> set @@optimizer_switch = "index_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)
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.86
Extra: Using where
1 row in set, 1 warning (0.00 sec)执行计划显示：
优化器使用了复合索引 idx_rental_date 的首字段 rental_date 过滤出符合条件的记录，然后根据复合索引 idx_rental_date 回表获取记录。最后根据条件 customer_id >= 300 AND customer_id <= 400 来过滤出最后的查询结果（需要访问满足 rental_date 的所有记录）。
然后我们开启 ICP，重新执行上面的查询：
mysql> set @@optimizer_switch = "index_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)
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.86
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)Using index condition 就表示 MySQL 使用 ICP 来优化查询，在检索的时候，把条件 customer_id 的过滤操作下推到存储引擎层完成，这样可以减少不必要的 IO 访问。
索引失效的典型场景
有时候有索引，但不能被优化器选择使用。下面列举几个不能使用索引的常见场景。
LIKE 前缀模糊
以 % 开头的 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 row in set, 1 warning (0.00 sec)因为 B-Tree 索引的结构导致 % 开头的查询无法利用索引，一般推荐使用全文索引 (FULLTEXT) 来解决此类全文索引的问题。或者考虑利用 InnoDB 的表都是聚簇表的特点，使用覆盖索引的方式解决：
一般情况下，索引都会比整张表要小，扫描索引要比扫描整张表更快（某些特殊情况下，索引会比表大，不适用该方法），而 InnoDB 的二级索引 idx_actor_last_name 实际上除了存储字段外还包含主键 actor_id。
那么理想的访问方式应该是 LIKE 模糊查询索引，拿到主键id，然后通过主键id回表检索记录，这样访问避开了全表扫描产生的的大量磁盘 IO，我们验证一下是否可行：
mysql> explain SELECT actor_id FROM actor WHERE last_name LIKE '%NI%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 182
ref: NULL
rows: 200
filtered: 11.11
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)可以通过执行计划看到，内存查询的 Using index 表示索引覆盖扫描，之后我们通过主键 JOIN 操作拿到最终数据，理论上这比直接全表扫描更快一些。
数据类型隐式转换
数据类型出现隐式转换也不会使用索引，特别是当列类型是字符串，那么写 WHERE 条件时把字符常量值用引号引起来，否则即使这个列有索引，MySQL 也不会用到。
例如演员表的演员 last_name 是字符型的，但 SQL 语句中的条件值是整数型值， 因此即使存在索引 idx_actor_last_name 也依然会走全表扫描。
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 row in set, 3 warnings (0.00 sec)加上引号之后，再次检查执行计划，就可以正确的使用索引了：
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 row in set, 1 warning (0.00 sec)复合索引最左原则
当复合索引不满足最左原则的时候，也是不会做索引的。
mysql> explain SELECT * FROM payment WHERE amount = 3.98\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: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)走索引更慢
如果 MySQL 估计使用索引比全表扫描更慢，则不会使用索引。
例如：查询以S 开头的电影标题，需要返回的记录比例较大，MySQL 预估索引扫描还不如全表扫描快：
mysql> UPDATE film_text set title = CONCAT('S',title);
Query OK, 1000 rows affected (0.08 sec)
Rows matched: 1000 Changed: 1000 Warnings: 0
mysql> explain SELECT * FROM payment WHERE amount = 3.98\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: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)我们可以通过 trace 清晰的看到优化器选择的过程，全表扫描 table scan 需要访问的记录为 row 1000，代价 cost 计算为 213.1 :
{
"table_scan": {
"rows": 1000,
"cost": 213.1
}
}而如果使用索引，优化器预估需要返回 1000 条记录，访问代价为 1201 远高于全表扫描的代价，所以优化器更倾向于全表扫描：
{
"index": "idx_title_desc_part",
"ranges": [
"S\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 <= title <= S\uffff\uffff\uffff\uffff\uffff\uffff\uffff\uffff\uffff"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1000,
"cost": 1201,
"chosen": false,
"cause": "cost"
}当我们更换一个选择率更高的值时，发现优化器更倾向于使用索引扫描：
mysql> explain SELECT * FROM film_text WHERE title LIKE 'SW%'\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: 43
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)通过 trace 可以看到 title LIKE 'SW%' 优化器预估需要返回 43条数据， 代价为 52.61，远小于全表扫描的代价，所以优化器更倾向于选择索引扫描：
{
"index": "idx_title_desc_part",
"ranges": [
"SW\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 <= title <= SW"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 43,
"cost": 52.61,
"chosen": true
}总结一下：筛选性越高的值越容易使用索引，筛选行越低的值越不容易使用索引。
OR 索引失效
使用 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 row in set, 1 warning (0.00 sec)因为 OR 后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在查询存在全表扫描的情况下时，就没有必要再检索索引多一次 IO 访问，全表扫描过滤条件就足够了。
查看索引的使用情况
如果索引正在工作，Handler_read_key 的值将会很高，这代表一个行被索引读取的次数，很低的值表明增加索引得到的性能改善不高，因为索引不经常使用。
Handler_read_rnd_next 的值很高意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读取下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值较高，通常说明表索引不正确或写入的查询没有利用到索引。
mysql> show status LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 4 |
| Handler_read_key | 4 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2006 |
+-----------------------+-------+
7 rows in set (0.00 sec)可以看出目前数据库的索引使用情况并不理想。
优化方法
大部分更复杂的优化，更倾向于交给专业的 DBA 来做，下面介绍两个简单适用的优化方法：
分析表
分析表语法如下：
ANALYZE [LOCAL | NO_WRITE_TO_BIGLOG] TABLE tbl_name [, tbl_name] ...分析的结果可以使系统得到准确的统计信息，从而生成正确的执行计划，如果感觉SQL的实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。对于 MyISAM、InnoDB 和 BDB 表有作用，对于 MyISAM 表，执行分析表语句与使用 myisamchk -a 相当，使用方法如下：
mysql> analyze table payment;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| sakila.payment | analyze | status | OK |
+----------------+---------+----------+----------+
1 row in set (0.03 sec)检查表
检查表语法如下：
CHECK TABLE tbl_name [,tbl_name] ... [option] ...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}优化表的作用是检查一个或多个表、视图是否有错误，对于 MyISAM 表，关键字统计数据被更新，例如：
mysql> check table payment_myisam;
+-----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| sakila.payment_myisam | check | status | OK |
+-----------------------+-------+----------+----------+
1 row in set (0.01 sec)也可以用来检查视图是否有误：
-- 创建一个视图
mysql> create view v_payment_myisam as select * FROM payment_myisam;
Query OK, 0 rows affected (0.00 sec)
-- 然后删除视图依赖的表
mysql> drop table payment_myisam;
Query OK, 0 rows affected (0.00 sec)
-- 检查视图
mysql> check table v_payment_myisam\G;
*************************** 1. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: Error
Msg_text: Table 'sakila.payment_myisam' doesn't exist
*************************** 2. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: Error
Msg_text: View 'sakila.v_payment_myisam' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
Table: sakila.v_payment_myisam
Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)使用 CHECK 检查试图时发现报错了，原因是 Table 'sakila.payment_myisam' 不存在。
优化表
优化表语法如下：
OPTIMIZE [LOCAL | NO_WRITE_TO_BIGLOG] TABLE tbl_name [, tbl_name] ...当对表进行了大幅度的修改（删除或修改了含有可变长度行的表，如VARCHAR、BLOB 或者 TEXT 列的表），则应使用 OPTIMIZE TABLE 命令进行优化表。
这个命令可以将表中的空间碎片进行合并，并且可以消除由删除或更新造成的空间浪费，但 OPTIMIZE TABLE 命令仅对 MyISAM 、BDB 和 InnoDB 表起作用。执行期间 MyISAM 会全程锁表，而 InnoDB 表会将优化命令转换为重建表和分析表两个操作，加锁时间也仅仅在整个工作的 prepare 和 commit 期间做短暂的加锁工作，对表的读写几乎没有影响。
下面是优化 payment 和 payment_myisam 表的过程：
mysql> optimize table payment;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| sakila.payment | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| sakila.payment | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.33 sec)
mysql> optimize table payment_myisam;
+-----------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| sakila.payment_myisam | optimize | status | OK |
+-----------------------+----------+----------+----------+
1 row in set (0.01 sec)对于 InnoDB 的表来说，通过设置 innodb_file_per_table 参数，设置 InnoDB 为独立表空间模式，这样每一个数据库的数据表都会生成一个独立的 ibd 文件，用于存储数据和索引，一定程度上减轻 InnoDB 表的空间回收问题。
另外，删除大量后，InnoDB 可以通过设置存储引擎的方式来回收不用的空间：
mysql> alter table payment engine=innodb;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0常用 SQL 优化
在日常的开发工作中，除了使用查询外，我们还会是以哦那个一些其他常用的SQL，比如 INSERT、GROUP BY 等。下面介绍针对这些语句的一些优化方法。
大批量插入
当使用 load 命令导入数据时，适当的设置可以提高导入的速度。
这里使用 film_text 表，填充数据至 102w 条，来测试大量数据导入。
MyISAM 引擎
对于 MyISAM 的表，我们可以使用下面的方式来快速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLe tbl_name ENABLE KEYS;这个设置是用来打开和关闭 MyISAM 表的非唯一索引的更新。
在导入大量数据时，遇到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。
如果是导入一个空的 MyISAM 表，默认就是先导入数据后创建索引，所以不需要进行设置。
下面使用 load 语句导入 102w 数据耗时 133.44s:
mysql> load data infile './film_text2.txt' into table film_text2;
Query OK, 1024015 rows affected (1 min 33.44 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0而使用关闭索引更新的方式导入，总耗时 0.01 + 9.71 + 56.44 = 66.16s 提高了两倍多。
(书中提高了六倍，可能是因为测试的数据量、硬件差异导致)
mysql> ALTER TABLE film_text2 DISABLE KEYS;
Query OK, 0 rows affected (0.01 sec)
mysql> load data infile './film_text2.txt' into table film_text2;
Query OK, 1024015 rows affected (9.71 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0
mysql> ALTER TABLE film_text2 ENABLE KEYS;
Query OK, 0 rows affected (56.44 sec)InnoDB 引擎
对于 InnoDB 引擎的表来说，上面提到的方式并不能提高导入的速度，下面有几种可以提高 InnoDB 表导入的效率。
主键顺序导入
因为 InnoDB 类型的表是按照主键顺序保存的，所以将导入的数据按照主键的排列顺序导入，可以有效的提高导入数据的效率。
下面是按照主键顺序导入数据，耗时 26.38 秒：
mysql> load data infile './film_text2.txt' into table film_text4;
Query OK, 1024015 rows affected (26.38 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0下面是按照主键倒序的数据，耗时 32.32 秒：
mysql> load data infile './film_text3.txt' into table film_text4;
Query OK, 1024015 rows affected (32.32 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0可以看出，当被导入的数据按照主键顺序存储时，导入的效率更高。
关闭唯一性校验
导入数据前执行 SET UNIQUE_CHECKS = 0，关闭唯一性校验；在导入数据后执行 SET UNIQUE_CHECKS = 1，恢复唯一性校验，可以提高导入的效率。
-- 开启唯一性校验(默认)
mysql> SET UNIQUE_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> load data infile './film_text2.txt' into table film_text4;
Query OK, 1024015 rows affected (28.11 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0
-- 关闭唯一性校验
mysql> SET UNIQUE_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> TRUNCATE TABLE film_text4;
Query OK, 0 rows affected (0.77 sec)
mysql> load data infile './film_text2.txt' into table film_text4;
Query OK, 1024015 rows affected (25.70 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0注：实际我测试了很多次几乎没有区别，这里是找出了时间差别最大的两个记录。
关闭自动提交
如果应用使用了自动提交的方式，建议在导入前执行 SET AUTOCOMMIT = 0，来关闭自动提交，导入结束后再打开自动提交，也可以提高导入效率。
开启自动提交时，耗时 25.18s：
mysql> load data infile './film_text2.txt' into table film_text4;
Query OK, 1024015 rows affected (25.18 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0关闭自动提交时，耗时 25.04s：
mysql> load data infile './film_text2.txt' into table film_text4;
Query OK, 1024015 rows affected (8.53 sec)
Records: 1024015 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected (16.51 sec)貌似与书上提到的效果并不一致,与网友讨论后认为问题可能出在CPU频率与作者使用的测试机不一致导致的。
优化 INSERT 语句
通常插入数据的时候，可以考虑采用以下几种优化方案
- 如果是同一个客户插入很多行，应尽量使用批量插入（既多个 VALUES 值），这种方式可以大大缩减客户端与服务器之间的连接、关闭等消耗，使得效率比单条插入语句快，大部分情况下能比单个 insert 语句快好几倍。
- 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
- 如果是批量插入，可以通过增加 bulk_insert_buffer_size 缓存值的方法来提高速度（该配置选用于控制批量插入的缓存大小，值设置越大可批量插入条数越多。），该操作仅对 MyISAM 的表有效。
- 当从一个文本文件中装载表时，使用 LOAD DATA INFILE 比 INSERT 快 20 倍。
- 当列有默认值，且插入的数据与默认值一致时，不要显示插入，可以减少 MySQL 解析，提高插入速度。
优化 ORDER BY
在优化 ORDER BY 之前我们先了解一下 MySQL 的排序方式。先看一下 customer 表上的索引情况：
mysql> SHOW INDEX FROM customer\G
*************************** 1. row ***************************
Table: customer
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: customer_id
Collation: A
Cardinality: 599
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: customer
Non_unique: 1
Key_name: idx_fk_store_id
Seq_in_index: 1
Column_name: store_id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: customer
Non_unique: 1
Key_name: idx_fk_address_id
Seq_in_index: 1
Column_name: address_id
Collation: A
Cardinality: 599
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: customer
Non_unique: 1
Key_name: idx_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 599
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
4 rows in set (0.00 sec)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 row in set, 1 warning (0.00 sec)第二种是直接对返回的数据进行排序，也就是常说的 filesort 排序，所有不是通过索引直接返回排序结果的都叫 Filsort 排序。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 row in set, 1 warning (0.00 sec)或者我们只查询覆盖索引，但因为发生了排序操作，所以执行计划中依然包含 Using Filesort。
mysql> alter table customer add index idx_storeid_email (store_id, email);
Query OK, 0 rows affected (0.04 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 row in set, 1 warning (0.00 sec)Filesoft 是通过相应的排序算法，将取得的数据在 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 row in set, 1 warning (0.01 sec)
而查询商店编号 store_id 在 1 至 3 之间，按照 email 排序的记录主键 customer_id 的时候，由于优化器评估使用 索引 idx_storeid_email 进行范围扫描代价 cost 最低，所以最终是对索引扫描的结果，并且进行了额外的按照 email 的逆序排序操作。
mysql> explain SELECT store_id,email,customer_id FROM customer WHERE store_id BETWEEN 1 AND 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 row in set, 1 warning (0.00 sec)总结
下面的情况可以使用索引：
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;下面几种情况则不能使用索引:
-- ORDER BY 字段混用 DESC 和 ASC
SELECT * FROM tabname ORDER BY key_part1 DESC,key_part2 ASC;
-- 条件字段与排序字段不一致
SELECT * FROM tabname WHERE key2 = constant ORDER BY key1;
-- 对于同关键字使用 ORDER BY
SELECT * FROM tabname ORDER BY key1, key2;Filesort 的优化
通过创建合适的索引能够减少 Filesort 的出现，但是在某些情况下，因为各种条件限制不能让 Filesort 消失，那就需要想办法加速 Filesort 的操作。对于 Filesort MySQL 有两种排序方式。
两次扫描算法（Two Passes）:
首先根据条件去除排序字段和行指针信息，之后在排序区 sort buffer 中排序，如果排序区 sort buffer 不够，则在临时表 Tempoary 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 参数是每个线程独占的，设置过大会导致服务器 SWAP 严重，要根据数据库活动的连接数和服务器内存大小来设置排序区。
尽量把只查询必要的字段，而不是使用 SELECT * 查询所有字段，这样可以减少排序区的使用，提高SQL性能。
优化 GROUP BY
默认情况下，MySQL 对所有 GROUP BY 的字段进行排序。这与在查询中指定 ORDER BY col1,col2 类似。因此 如果显式包含一个相同列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。
如果查询包含 GROUP BY 但想避免排序对性能的消耗，可以指定 ORDER BY NULL 禁止排序，比如下面的例子：
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 row in set, 1 warning (0.00 sec)
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)可以看到，第二次查询不需要进行 Filesort，而上文提到 Filesort 非常消耗时间。
优化 JOIN 操作
MySQL 对多表 JOIN 目前仅支持一种算法—— Nested-Loop Join (NLJ)。NLJ的原理非常简单，就是内外两层循环，对于外循环每条记录，都要在内循环中做一次检索，如下面的伪代码：
for each row in t1 matching range {
for each row in t2 matching reference key {
if row satisfies join conditions, send to client
}
}其中 t1 和 t2 表进行 join, t1 通过范围扫描取每条记录作为外循环，t2 通过关联字段在表中做扫描。满足条件则返回客户端；不断重复这个过程直到外循环结束。外循环的表通常也被称之为驱动表。
通过这个流程我们可以发现，NLJ的性能高低主要取决于两个方面：一是外循环的结果集大小，二是内循环扫描数据的效率。常见的优化方案是在外循环上增加 WHERE 条件并创建合适的索引，是的外循环的结果集更小，读取效率更高；内循环为了提高扫描效率，通常要在关联字段上加索引。
通过上面的优化方案，大多数情况下，NLJ 的性能都是可以满足要求的，尤其是关联字段在内循环是主键或唯一索引时效率尤其高。但有两种情况，NLJ的性能会明显的下降：
- 外循环结果集非常大，导致访问内循环表的 io 次数非常多。
- 内循环的关联字段并不是唯一索引，而是普通索引，如果需要访问的数据列不在索引上，通常需要再次回表，通过普通索引的主键找到聚集索引的实际数据，而回表会致使大量的随机 IO 产生，导致性能明显下降。
为了优化这两个问题，MySQL 先后推出了 NLJ 的变种，BNL (Block Nested-Loop Join) 和 BKA (Batched Key Access)。
BNL
BNL 在 MySQL 较早的版本就引入了，算法原理如下：
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
empty join buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions, send to client
}
}
}通过缓存外层循环读取的行，来降低内层表的读取次数，例如，将 10 行数据读取到缓冲区，然后将缓冲区传递到内层循环，内层表读取出的每一行数据都与缓冲区中的 10 行进行比较，这样就降低了内部表的读取次数。
在 MySQL 5.7 中，BNL 优化器是默认打开的，下面测试一下。用 join 关联 customer 和 payment 表，关联字段上均无索引：
-- 查看 BNL 优化器状态
mysql> SHOW variables LIKE 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT COUNT(*) FROM customer,payment WHERE customer.create_date = payment.payment_date\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: NULL
*************************** 2. 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: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.01 sec)
mysql> SELECT COUNT(*) FROM customer,payment WHERE customer.create_date = payment.payment_date;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.45 sec)根据 Extra 部分的显示，连接使用了 BNL。实际执行时间耗时 0.45 秒。
然后我们关闭 BNL 特性，再测试一下：
mysql> SET optimizer_switch="block_nested_loop=off";
Query OK, 0 rows affected (0.01 sec)
mysql> explain SELECT COUNT(*) FROM customer,payment WHERE customer.create_date = payment.payment_date\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: NULL
*************************** 2. 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: 10.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> SELECT COUNT(*) FROM customer,payment WHERE customer.create_date = payment.payment_date;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (1.85 sec)执行计划中 BNL 部分消失，比使用 BNL 慢了将近四倍多（书中例子为两倍）。
BNL 虽然性能大幅度提升，但使用条件较为苛刻，只有当 JOIN 类型是 all/ind ex/range 时才会使用，也就是内表不使用索引或者索引效率很低时不得已才使用。 buffer 的大小由参数 join_buffer_size 进行设置，buffer 中保存次啊与连接的所有列信息，join 结束后 buffer 释放。对于使用到 BNL 且 性能较差的 SQL, 在 session 级别将 join_buffer_size 临时增大来提高性能。
MRR & BKA
根据上面的描述，BNL 的使用场景较为苛刻，最重要的条件是内表关联字段没有索引或者索引效率很低，此时使用 BNL 可以较明显的降低内表的访问次数，同时降低回表的 IO 次数，以此来达到优化的目的。但在大部分情况下，表 JOIN 操作通常是通过效率较高的索引来做 ref 和 eq_ref 方式进行连接，这种情况下是无法使用 BNL 的。为了优化这种更常见的 JOIN， MySQL 引入了 MRR 和 BKA。
MRR
MRR (Multi Range Read) 是 MySQL 5.6 引入的特性。MRR 优化的目的就是为了减少磁盘的随机访问，InnoDB 因聚簇索引的特性，如果使用辅助索引，并且用到表中非索引列，那么就需要回表读取数据做后续处理，过于随机的回表会产生大量的随机 IO。MRR的优化就是将范围扫描到的数据存入 read_ebd_buffer_size ，然后对其按照 主键排序，最后使用排序好的主键进行回表。这样就将随机 IO 转换为了顺序 IO，对于瓶颈为IO 的 SQL 查询语句带来了极大的性能提升。
MRR 的特性在单表和多表 join 查询时都能使用。其中单表查询是通过范围查询，多表 join 的方式如果是 ref/eq_ref 则先通过 BAK 算法批量提取key 到 join_buffer，然后讲 join_buffer 中的 key 作为参数传入 MRR 的调用接口，MRR 高效读取需要的数据返回。
flowchart TD
A[ref,eq_ref access] --> B[Batched Key <br/> Access]
B --> C[Multi Range Read]
C --> D[Storage Engine]
E[range access] --> CMRR 使用方式
如果要打开 MRR 特性，则需要设置以下两个优化器的参数：
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)mrr 是控制 MRR 特性是否打开，默认为 no；mrr_cost_based 用了控制是否根据优化器的计算成本来决定使用 MRR 特性，默认是 no；如果希望尽可能使用 MRR，可以将此参数设为 off。
要判断是否使用了 MRR 特性，需要观察执行计划的 Extra 部分是否存在 "Using MRR" 字符串，下列 SQL 就使用了 MRR 特性。
mysql> EXPLAIN SELECT * FROM payment WHERE customer_id BETWEEN 1 AND 200 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 5444
filtered: 100.00
Extra: Using index condition; Using MRR
1 row in set, 1 warning (0.01 sec)BKA
BKA (Batched Key Access) 是 MySQL 5.6 引入的新算法，结合 MRR 特性进行高效 JOIN 操作，算法步骤如下：
- 将外循环表中的相关列放入 Join Buffer 中。
- 批量的将 Kay (索引键值) 发送到 MRR 接口。
- MRR 通过收到的 Kay 根据其对应的主键进行排序，然后再根据排序后的 主键id 顺序读取聚集索引，拿到需要的数据。
- 返回结果集个客户端。
MySQL 5.7 之后 BKA 默认是打开的，由优化器中的参数 batched_key_access 来控制。如果需要使用 BKA，则需要先打开 MRR 特性，通常一起设置如下参数：
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)判断是否使用 BKA 算法，需要查看执行计划中 Extra 部分是否含有 "Using join buffer(Batched Key Access)"，字符串:
mysql> EXPLAIN SELECT * FROM film,film_actor WHERE film.film_id = film_actor.film_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 5
filtered: 100.00
Extra: Using join buffer (Batched Key Access)
2 rows in set, 1 warning (0.00 sec)通过 BKA 来优化 Join，很多情况下都可以提高连接的效率，但对 Join 也有一定的条件限制，一个条件是连接的列要求是唯一索引或普通索引，但不能是主键（如果是主键就直接拿数据了，不存在回表）；另一个是要对有非主键列的查询操作，否则优化器可以通过覆盖索引拿到需要的数据，不需要回表。也不会调用 MRR 了。
优化嵌套查询
MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后当作条件用于另一个查询中。使用子查询可以完成逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或表锁死。但在有些情况下，子查询可以被更有效率的 JOIN 代替。
例如：需要在客户表中查找没有支付过的客户信息：
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如果使用 JOIN 来查询，速度会快很多。尤其是当 payment 表中对 customer_id 建有索引，性能会更好。
mysql> EXPLAIN SELECT * FROM customer LEFT JOIN payment ON customer.customer_id = payment.customer_id WHERE payment.customer_id IS NULL \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: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.customer.customer_id
rows: 26
filtered: 100.00
Extra: Using where; Not exists
2 rows in set, 1 warning (0.00 sec)从执行计划可以看出查询关联的类型从 index_subquery 调整为 ref ，在 MySQL 5.5 及其以下的版本中，子查询的效率不如关联查询。
关联查询（JOIN）之所以更快，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
优化 OR 条件
对于含有 OR 条件的查询子句，如果要利用索引，则 OR 之间的每个条件列必须用到索引，如果没有索引，则需要考虑增加索引。
MySQL 在处理含有 OR 的查询时，实际是对 OR 的各个字段分别查询然后进行 UNION 操作。
优化分页查询
分页查询是一个非常常见的查询场景，例如需要查询的场景是 "limit 1000,10"，此时 MySQL 需要排序出去前 1010 条记录，然后仅返回 第 1001 到 1010 记录，前 1000 条的排序都会被丢弃，而查询和排序的内存代价还是磁盘 IO 都非常的高。
覆盖索引优化
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
例如：对 film 表根据标题 title 排序后取某一页数据，直接查询的时候，能够从执行计划看出走了全表扫描，效率不高。
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 row in set, 1 warning (0.01 sec)如果使用根据索引分页后再回表的方式 改写SQL，再次查看执行计划，已经没有全表扫描了。
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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using filesort设置查询起点
把 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: 16125
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)可以由前端同事在查询下一页的时候，带上一个 last_page_reecord，用于记录上一页最后一行的租赁编号 rental_id 然后我们根据 rental_id 只获取后面的 10 条即可。
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 |
+------------+-----------+
10 rows in set (0.01 sec)
mysql> EXPLAIN SELECT payment_id,rental_id 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: 8062
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)使用 SQL 提示
SQL 提示简单来说就是人为的添加关键字，来提示 MySQL 如何查询数据（注意是你提示数据库怎么查询，而不是数据库提示你怎么写SQL）。例如下面的例子：
SELECT SQL_BUFFER_RESULTS * FROM...这个语句将强制 MySQL 生成一个临时结果集。只要临时的结果集生成后，表上的锁定都会被释放，这能在遇到表锁定问题时或要花很长时间将结果传输给客户端时有所帮助，因为可以尽快释放锁资源。
USE INDEX
在查询语句的表明后面添加 USE INDEX 来提供希望 MySQL 使用的索引，就可以让 MySQL 不用参考其他可用索引。
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: 16005
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
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: 16005
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)可以看到第一次查询 MySQL 选择的并不是 idx_rental_date 索引，但可以通过提示，MySQL 走了 idx_rental_date 索引。
IGNORE INDEX
如果想让 MySQL 忽略一个或多个索引则可以使用 IGNORE INDEX 作为 HINT。
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: 16005
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)
mysql> explain select count(*) from rental ignore index(idx_fk_staff_id)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: index
possible_keys: NULL
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 16005
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)FORCE INDEX
为 MySQL 强制指定一个特定的索引，例如：当不强制使用索引的时候，因为大部分库存 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: 16005
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)尝试用 use index 看看：
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: 16005
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)虽然这里提示了使用索引，但 MySQL 发现全表扫描的效率更高，所以不接受你的建议。而 FORCE INDEX 进行提示时，即使使用索引的效率不是最高的，MySQL 依然会选择索引。
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: 8002
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)可以看到 MySQL 使用了 FORCE INDEX 提示的索引。
直方图
直方图是 MySQL 8.0 引入的新功能，利用直方图，可以分析一列做数据分布统计，特别是针对没有索引的字段。这可以帮助优化器找到更优的执行计划。
统计直方图的主要使用场景是用来计算字段选择性，即过滤率。
什么是直方图
在数据库里，查询优化器负责将 SQL 转化为最有效的执行计划。但有时由于一些字段的数据分布不均，导致优化器针对某些值不会选择最优的执行计划，从而使得执行效率降低。为了能做出更准确的选择的，查询优化器需要了解条件列中具体的数据分布情况，而直方图的引入就是为了统计这些信息。
生成直方图
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [,col_name] WITH N BUCKETS;删除直方图
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [,col_name];其中 BUCKETS 表示生成桶的个数，桶用来存放不同值的分布情况，默认值为 100，最大到 1024。
emp1 员工表的表结构及性别字段 gender 数据分布如下：
mysql> DESC emp1;
+--------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+----------------+
| emp_no | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
+--------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT gender,count(*) FROM emp1 GROUP BY gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M | 229376 |
| F | 32768 |
+--------+----------+
2 rows in set (0.09 sec)
在没有直方图的情况下直接查询员工表的性别，执行计划如下：
mysql> EXPLAIN SELECT count(1) FROM emp1 WHERE gender = 'F'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 262208
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)可以看到执行计划中的 filtered 值是 50%，即优化器不知道数据的实际分布情况，只能按照值的个数进行平均分配。如果在 gender 上创建了直方图，则会按照实际数据分布进行过滤。
mysql> ANALYZE table emp1 UPDATE HISTOGRAM ON gender \G;
*************************** 1. row ***************************
Table: test.emp1
Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'gender'.
1 row in set (1.50 sec)
mysql> EXPLAIN SELECT count(1) FROM emp1 WHERE gender = 'F'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 262208
filtered: 12.50
Extra: Using where
1 row in set, 1 warning (0.00 sec)创建直方图之后，执行计划 filtered 的属性值已经从 50% 降低为 12.50%，符合实际分布情况。这些数信息使得优化器对 gender 字段查询的代价计算更为精确，从而在某些 gender 查询相关 SQL 中生成更高效的执行计划。
直方图分类
MySQL 目前支持两种直方图类型：等宽直方图（singleton）和等高直方图（equi-height）。它们的共同点是将数据分到了一系列的 buckets 中；区别在于如果列中值的复杂度小于等于 buckets 数，则为等宽直方图，否则为等高直方图。 MySQL 会自动将数据规划到不同的 bucket 中，也会自动决定创建那种类型的直方图。
直方图的统计信息放在 information_schema 库的 column_statistics 视图中，以 JSON 的格式保存。
mysql> SELECT * FROM information_schema.column_statistics \G;
*************************** 1. row ***************************
SCHEMA_NAME: test
TABLE_NAME: emp1
COLUMN_NAME: gender
HISTOGRAM: {"buckets": [[1, 0.875], [2, 1.0]], "data-type": "enum", "null-values": 0.0, "collation-id": 255, "last-updated": "2022-04-17 10:26:19.434912", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100}
1 row in set (0.01 sec)其中 HISTOGRAM 列包含了 gender 字段的直方图信息，内容包括如下几项：
buckets: [[1, 0.875], [2, 1.0]]表示 gender 上创建了两铬 bucket ,bucket 为 1 的值包含了 87.50 % 的数据，bucket 为 2 的值包含了 1 - 87.50 = 12.50% 的数据，即 2 后面的 1.0 是累积的数据分布。由于 enum 保存的并不是字面值，这里的 1 和 2 是enum 类型的 M 和 F 实际保存的值。date-type： enum 表示 gender 为枚举类型null-values： 0.0 表示字段中没有空值collation-id：表示排序方式，对应information_schema.collations表中的 idlast-updated：表示直方图的最后更新日期，直方图只能手动 update，不会随着数据的更新而更新；当数据发生较大的变化时，要手动重新生成直方图。sampling-rate：1.0 表示数据的采样率。对于数据量巨大的表，MySQL 出于性能考虑，不会全部扫描，只取一部分数据用来生成直方图。采样大小由histogram_generation_max_size进行控制。这个值是控制最大允许多少内存被使用。histogram-type：直方图类型，等宽直方图（singleton）和等高直方图（equi-height）。number-of-buckets-specified：表示指定了多少个 bucket。对于等宽直方图来说，如果创建直方图时指定的 buckets 个数大于列中唯一值的个数，那么只需要创建唯一值的个数的 buckets 就可以了。本例默认为 100 个buckets，但因为选择性只有两个，所以只用了 2 个 bucket。
对于等高直方图，bucket 显示的内容有所不同，由于列中不同值的数量大于 bucket 的数量，因此每个 bucket 上对应的不是一个值，而是一个具有上下限的列值范围。
直方图小结
直方图的引入给SQL优化提出了一个新的思路，但不是所有的大表字段都需要创建直方图。通常在一些唯一值较少、数据分布不均衡、查询较为频繁、且没有创建索引的字段上考虑加直方图。虽然有时候创建的索引也能达到优化效果，但由于这类字段索引使用率低、索引的维护成本高，因此一般不会在这些字段上单独设置索引。而直方图只需要创建一次，对于数据的变更不需要实时进行维护，代价较小，更适合此类条件的查询。
SQL技巧
RAND() 取随机行
有时候我们需要在数据库中随机取出一部分数据，可以使用 rand() 函数。
mysql> SELECT * FROM film_actor order by rand() limit 5;
+----------+---------+---------------------+
| actor_id | film_id | last_update |
+----------+---------+---------------------+
| 96 | 846 | 2006-02-15 05:05:03 |
| 181 | 301 | 2006-02-15 05:05:03 |
| 85 | 216 | 2006-02-15 05:05:03 |
| 47 | 787 | 2006-02-15 05:05:03 |
| 146 | 177 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
5 rows in set (0.00 sec)利用 WITH ROLLUP 汇总
在SQL 语句中，使用 GRUOP BY 的 WITH ROLLUP 子句，可以检索出更多分组聚合下信息。
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.01 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 |
+-----------------------------------+----------+-------------+
16 rows in set (0.03 sec)可以看出第二个查询比第一个查询多出了每个月的汇总和最后一行的汇总。WITH ROLLUP 反应的是一种 OLAP 思想，也就是说执行完这个 GROUP BY 语句后可以满足用户想要得到的任何一个分组以及分组组合的聚合值信息。
用 BIT GROUP FUNCTIONS 做统计
简单来说就是使用 GROUP BY 语句的 BIN_AND()、BIN_OR() 做 bitmap 位运算，来实现节省空间存储状态的效果。
我们假设这么一个场景：一个超市需要记录每个用户每次来超市都买了那些东西。为了简单化我们假设超市只有 3060 3070 3080 和 空气 四种商品。
通常我们需要先建立一个购物表，记录购物的发生时间、顾客信息等；然后再建立一个购物商品明细表，记录用户购买的商品、单价、数量等商品信息。
这样可以详细的记录用户购买信息，但如果我们的需求只是想知道用户了那些商品以及总金额，那么这样的数据库设计显得太复杂了。或者有人会想到使用一个表来完成实现这个功能，将订单的商品明细用JOSN的方式存入一个字段，这也是一种方式。但如果顾客一次购买的商品比较多，则需要很大的存储空间，而且用来做各种统计时也会捉襟见肘。
我们可以通过 bitmap 的方式存储顾客购买的商品信息，使用一个数值型类型的字段，当这个数值转换为二进制时，每一位代表一种商品。比如数值第一位代表 3060（按照规则从右往左计算）、第二位代表 3070、第三位代表 3080、第四位代表空气。例如一个购物记录的商品数值为 5，那么二进制表示为 0101，这样从右到左第一位和第三位是 1，就可以知道用户买了 3060 和 3080，而如果客户有多个订单，我们则可以通过 BIN_OR() 操作快速了解到用户构建买了哪些商品。
我们先初始化一些数据：
mysql> CREATE TABLE order_rab(id int,customer_id int,kind int);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO order_rab VALUES(1,1,5),(2,1,4),(3,2,3),(4,2,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM order_rab;
+------+-------------+------+
| id | customer_id | kind |
+------+-------------+------+
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
+------+-------------+------+
4 rows in set (0.00 sec)初始化了两个顾客的数据，每人购物两次，前者的购买数值是 5 和 4，转换为二进制分别为 0101 和 0100，表示顾客第一次进店购买了 3060 和 3080，第二次购买了 3080；后者的购买数值分别是 3 和 4，转换为二进制为 0011 和 0100，表示顾客第一次进店购买了 3060 和 3070， 第二次购买了 3080。
下面使用 ORDER BY 统计一下这两个顾客在超市购买过什么产品。
+-------------+--------------+-------------------+
| customer_id | BIT_OR(kind) | BIN(BIT_OR(kind)) |
+-------------+--------------+-------------------+
| 1 | 5 | 101 |
| 2 | 7 | 111 |
+-------------+--------------+-------------------+
2 rows in set (0.00 sec)因为我们是以四位来计算所以我们人为给二进制左侧补 0 方便统计。顾客1 的结果是 0101 表示购买过 3060 和 3080，顾客 2 的结果是 0111 表示购买过 3060、3070、3080。
同理我们也可以使用 BIT_AND() 统计顾客每次来都会购买的商品。
mysql> SELECT customer_id,BIT_OR(kind),BIN(BIT_AND(kind)) FROM order_rab GROUP BY customer_id;
+-------------+--------------+--------------------+
| customer_id | BIT_OR(kind) | BIN(BIT_AND(kind)) |
+-------------+--------------+--------------------+
| 1 | 5 | 100 |
| 2 | 7 | 0 |
+-------------+--------------+--------------------+
2 rows in set (0.00 sec)可以看到客户 1 每次来都会购买 3080，而顾客 2 并没有每次都会购买的商品。
从上面的例子可以看出，这种数据库结构设计的优势是能用很简洁的数据表示丰富的信息，这样的方式可以大大的节省存储空间，而且能够提升部分统计计算的速度。不过需要注意的是，这样的方式其实损失了顾客购买商品的详细信息，因此还需要根据实际应用场景有选择的考虑数据库的结构设计。
关于外键
在 MySQL 中只有 InnoDB 引擎支持对外部关键字约束条件的检查。而对于其他存储引擎的表，也可以定义外键，但没有实际效果，只是作为备忘录或者注释来提醒用户目前正在定义的列表指向另一个表中的一个字段。
小结
SQL 优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问题都是由不合适的 SQL 语句造成。本篇笔记描述了 SQL 优化的一般过程，从定位一个有问题的 SQL 到分析产生问题的原因，最后采取什么措施优化SQL语句的性能。
笔记来自：《深入浅出MySQL：数据库开发，优化与管理维护（第三版）》