可能的原因

• MySQL 服务宕机
• MySQL 连接被主动 kill 掉
• MySQL 连接超时
• SQL 超长，超出 max_allowed_packet 限制

具体情况分析和处理

MySQL 服务宕机

可能是异常情况，访问过程中数据库宕机或重启了，期间的数据库访问请求会出现错误。
此种情况可以查看对应时候的 MySQL 相关日志，或者查询 MySQL 运行时间。可通过运行时间和日志，判断该时间是否有服务中断。

mysql> show global status like 'uptime';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Uptime        | 23948658 |
+---------------+----------+

MySQL 连接被主动 kill 掉

部分系统会配置一些连接数过多等情况下，脚本主动 kill 掉相关数据库请求进程，或者可能 DBA 等处理问题时手动 kill 掉，此种情况下也会出现报错。

mysql> show global status like 'com_kill';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 100   |
+---------------+-------+

MySQL 连接超时

MySQL 的连接开启后，很久没有发起新的查询请求，达到了 server 端的超时时间，被 server 端强制关闭连接。此时若该连接再次发起请求时，则会报错 MySQL server has gone away 。此种情况比较常见，一般一个执行时间很长的脚本，开启连接查询部分数据后，进行计算或者请求第三方，在进行数据写入，写入时超时。
可以通过如下命令查看当前 MySQL 的超时时间，

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

可以通过如下命令临时修改超时时间，

mysql> set global wait_timeout = 60 * 60 * 8;

如要长期生效，则需要修改数据库配置文件，并重启 MySQL 服务。

wait_timeout = 28800
interactive_timeout = 28800

SQL 超长，超出 max_allowed_packet 限制

MySQL 会限制 server 段接收的数据包的大小，有时候大的插入和更新发送的数据包大小超过 max_allowed_packet 的限制，服务端也会报错，导致写入或者更新失败。

mysql> show global variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576   |
+--------------------------+------------+

可以通过如下命令临时修改，

mysql> set global max_allowed_packet = 4 * 1024 * 1024;

如要长期生效，则需要修改数据库配置文件，并重启 MySQL 服务。

max_allowed_packet = 4M

前言

今天我们来讲讲如何优化MySQL的性能，主要从索引方面优化。下期文章讲讲MySQL慢查询日志，我们是依据慢查询日志来判断哪条SQL语句有问题，然后在进行优化，敬请期待MySQL慢查询日志篇

建表

// 建表
CREATE TABLE IF NOT EXISTS staffs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名',
    age INT NOT NULL DEFAULT 0 COMMENT'年龄',
    pos VARCHAR(20) NOT NULL DEFAULT "" COMMENT'职位',
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职事件'
) CHARSET utf8 COMMENT'员工记录表';
// 插入数据
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', now());
INSERT INTO `test`.`staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', now());
// 建立复合索引（即一个索引包含多个字段）
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

优化一：全部用到索引

介绍

建立的复合索引包含了几个字段，查询的时候最好能全部用到，而且严格按照索引顺序，这样查询效率是最高的。（最理想情况，具体情况具体分析）

SQL 案例

优化二：最左前缀法则

介绍

如果建立的是复合索引，索引的顺序要按照建立时的顺序，即从左到右，如：a->b->c（和 B+树的数据结构有关）

无效索引举例

• a->c：a 有效，c 无效
• b->c：b、c 都无效
• c：c 无效

SQL 案例

优化三：不要对索引做以下处理

以下用法会导致索引失效

• 计算，如：+、-、*、/、!=、<>、is null、is not null、or
• 函数，如：sum()、round()等等
• 手动/自动类型转换，如：id = "1"，本来是数字，给写成字符串了

SQL 案例

优化四：索引不要放在范围查询右边

举例

比如复合索引：a->b->c，当 where a="" and b>10 and 3=""，这时候只能用到 a 和 b，c 用不到索引，因为在范围之后索引都失效（和 B+树结构有关）

SQL 案例

优化五：减少 select * 的使用

使用覆盖索引

即：select 查询字段和 where 中使用的索引字段一致。

SQL 案例

优化六：like 模糊搜索

失效情况

• like "%张三%"
• like "%张三"

解决方案

• 使用复合索引，即 like 字段是 select 的查询字段，如：select name from table where name like "%张三%"
• 使用 like "张三%"

SQL 案例

优化七：order by 优化

当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。

filesort 出现的情况举例

• order by 字段不是索引字段
• order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc;
• order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
• order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc;

索引层面解决方法

• 使用主键索引排序
• 按照最左前缀法则，并且使用覆盖索引排序，多个字段排序时，保持排序方向一致
• 在 SQL 语句中强制指定使用某索引，force index(索引名字)
• 不在数据库中排序，在代码层面排序

order by 排序算法

• 双路排序

  Mysql4.1 之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和 ORDER BY 列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在 buffer 进行排序，再从磁盘读取其他字段。

文件的磁盘 IO 非常耗时的，所以在 Mysql4.1 之后，出现了第二种算法，就是单路排序。

• 单路排序

  从磁盘读取查询需要的所有列，按照 orderby 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成顺序 IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

当我们无可避免要使用排序时，索引层面没法在优化的时候又该怎么办呢？尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机 IO 操作,很大幅度地提高排序工作的效率。下面看看单路排序优化需要注意的点

单路排序优化点

• 增大 max_length_for_sort_data

  在 MySQL 中,决定使用"双路排序"算法还是"单路排序"算法是通过参数 max_length_for_ sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择"单路排序"算法,反之,则选择"多路排序"算法。所以,如果有充足的内存让 MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用"单路排序"算法。

• 去掉不必要的返回字段，避免select *

  当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用"单路排序"算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。

• 增大 sort_buffer_size 参数设置

  这个值如果过小的话,再加上你一次返回的条数过多,那么很可能就会分很多次进行排序,然后最后将每次的排序结果再串联起来,这样就会更慢,增大 sort_buffer_size 并不是为了让 MySQL 选择"单路排序"算法,而是为了让 MySQL 尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成 MySQL 不得不使用临时表来进行交换排序。

但是sort_buffer_size 不是越大越好：

• Sort_Buffer_Size 是一个 connection 级参数,在每个 connection 第一次需要使用这个 buffer 的时候,一次性分配设置的内存。
• Sort_Buffer_Size 并不是越大越好,由于是 connection 级的参数,过大的设置和高并发可能会耗尽系统内存资源。
• 据说 Sort_Buffer_Size 超过 2M 的时候,就会使用 mmap() 而不是 malloc() 来进行内存分配,导致效率降低。

优化八：group by

其原理也是先排序后分组，其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。

IT 老哥

一个通过自学，进入大厂做高级Java开发的程序猿，希望能通过我的分享，让你学到知识

一、持久化的作用

1. 什么是持久化

持久化（Persistence），即把数据（如内存中的对象）保存到可永久保存的存储设备中（如磁盘）。
持久化Redis所有数据保持在内存中，对数据的更新将异步地保存到磁盘上。

2. 持久化的实现方式

快照方式持久化

快照方式持久化就是在某时刻把所有数据进行完整备份。

例：Mysql的Dump方式、Redis的RDB方式。

写日志方式持久化

写日志方式持久化就是把用户执行的所有写指令（增删改）备份到文件中，还原数据时只需要把备份的所有指令重新执行一遍即可。

例：Mysql的Binlog、Redis的AOF、Hbase的HLog。

二、RDB

1. 什么是RDB

RDB简介

RDB持久化方式能够在指定的时间间隔能对你的数据进行快照存储。
在默认情况下， Redis 将数据库快照保存在名字为 dump.rdb的二进制文件中。
在 Redis 运行时， RDB 程序将当前内存中的数据库快照保存到磁盘文件中， 在 Redis 重启动时， RDB 程序可以通过载入 RDB 文件来还原数据库的状态。

工作方式

当 Redis 需要保存 dump.rdb 文件时， 服务器执行以下操作:

1. Redis 调用forks。同时拥有父进程和子进程。
2. 子进程将数据集写入到一个临时 RDB 文件中。
3. 当子进程完成对新 RDB 文件的写入时，Redis 用新 RDB 文件替换原来的 RDB 文件，并删除旧的 RDB 文件。

这种工作方式使得 Redis 可以从写时复制（copy-on-write）机制中获益。

2. RDB的三种主要触发机制

save命令（同步数据到磁盘上）

save 命令执行一个同步操作，以RDB文件的方式保存所有数据的快照。

127.0.0.1:6379> save
OK

由于 save 命令是同步命令，会占用Redis的主进程。若Redis数据非常多时，save命令执行速度会非常慢，阻塞所有客户端的请求。
因此很少在生产环境直接使用SAVE 命令，可以使用BGSAVE 命令代替。如果在BGSAVE命令的保存数据的子进程发生错误的时，用 SAVE命令保存最新的数据是最后的手段。

bgsave命令（异步保存数据到磁盘上）

bgsave 命令执行一个异步操作，以RDB文件的方式保存所有数据的快照。

127.0.0.1:6379> bgsave
Background saving started

Redis使用Linux系统的fock()生成一个子进程来将DB数据保存到磁盘，主进程继续提供服务以供客户端调用。
如果操作成功，可以通过客户端命令LASTSAVE来检查操作结果。

save 与 bgsave 对比

自动生成RDB

除了手动执行 save 和 bgsave 命令实现RDB持久化以外，Redis还提供了自动自动生成RDB的方式。

你可以通过配置文件对 Redis 进行设置， 让它在“ N 秒内数据集至少有 M 个改动”这一条件被满足时， 自动进行数据集保存操作。
比如说， 以下设置会让 Redis 在满足“ 60 秒内有至少有 1000 个键被改动”这一条件时， 自动进行数据集保存操作:

save 60 1000

3. RDB相关配置

# RDB自动持久化规则
# 当 900 秒内有至少有 1 个键被改动时，自动进行数据集保存操作
save 900 1
# 当 300 秒内有至少有 10 个键被改动时，自动进行数据集保存操作
save 300 10
# 当 60 秒内有至少有 10000 个键被改动时，自动进行数据集保存操作
save 60 10000

# RDB持久化文件名
dbfilename dump-<port>.rdb

# 数据持久化文件存储目录
dir /var/lib/redis

# bgsave发生错误时是否停止写入，通常为yes
stop-writes-on-bgsave-error yes

# rdb文件是否使用压缩格式
rdbcompression yes

# 是否对rdb文件进行校验和检验，通常为yes
rdbchecksum yes

4. RDB的优点

1. RDB是一个非常紧凑的文件，它保存了某个时间点得数据集，非常适用于数据集的备份，比如你可以在每个小时报保存一下过去24小时内的数据，同时每天保存过去30天的数据，这样即使出了问题你也可以根据需求恢复到不同版本的数据集。
2. RDB是一个紧凑的单一文件，很方便传送到另一个远端数据中心或者亚马逊的S3（可能加密），非常适用于灾难恢复。
3. RDB在保存RDB文件时父进程唯一需要做的就是fork出一个子进程，接下来的工作全部由子进程来做，父进程不需要再做其他IO操作，所以RDB持久化方式可以最大化redis的性能。
4. 与AOF相比，在恢复大的数据集的时候，RDB方式会更快一些。

5. RDB的缺点

1. 耗时、耗性能。RDB 需要经常fork子进程来保存数据集到硬盘上，当数据集比较大的时候，fork的过程是非常耗时的，可能会导致Redis在一些毫秒级内不能响应客户端的请求。如果数据集巨大并且CPU性能不是很好的情况下，这种情况会持续1秒，AOF也需要fork，但是你可以调节重写日志文件的频率来提高数据集的耐久度。
2. 不可控、丢失数据。如果你希望在redis意外停止工作（例如电源中断）的情况下丢失的数据最少的话，那么RDB不适合你。虽然你可以配置不同的save时间点(例如每隔5分钟并且对数据集有100个写的操作)，是Redis要完整的保存整个数据集是一个比较繁重的工作，你通常会每隔5分钟或者更久做一次完整的保存，万一在Redis意外宕机，你可能会丢失几分钟的数据。

三、AOF

1. 什么是AOF

快照功能（RDB）并不是非常耐久（durable）： 如果 Redis 因为某些原因而造成故障停机， 那么服务器将丢失最近写入、且仍未保存到快照中的那些数据。 从 1.1 版本开始， Redis 增加了一种完全耐久的持久化方式： AOF 持久化。
你可以在配置文件中打开AOF方式:

appendonly yes

打开AOF后， 每当 Redis 执行一个改变数据集的命令时（比如 SET）， 这个命令就会被追加到 AOF 文件的末尾。这样的话， 当 Redis 重新启时， 程序就可以通过重新执行 AOF 文件中的命令来达到重建数据集的目的。

AOF运行原理 - 创建

AOF运行原理 - 恢复

2. AOF持久化的三种策略

你可以通过配置文件配置 Redis 多久才将数据 fsync 到磁盘一次。

always

每次有新命令追加到 AOF 文件时就执行一次 fsync ：非常慢，也非常安全。

everysec

每秒 fsync 一次：足够快（和使用 RDB 持久化差不多），并且在故障时只会丢失 1 秒钟的数据。
推荐（并且也是默认）的措施为每秒 fsync 一次， 这种 fsync 策略可以兼顾速度和安全性。

no

从不 fsync ：将数据交给操作系统来处理，由操作系统来决定什么时候同步数据。更快，也更不安全的选择。

always、everysec、no对比

推荐（并且也是默认）的措施为每秒 fsync 一次， 这种 fsync 策略可以兼顾速度和安全性。

3. AOF重写

因为 AOF 的运作方式是不断地将命令追加到文件的末尾， 所以随着写入命令的不断增加， AOF 文件的体积也会变得越来越大。举个例子， 如果你对一个计数器调用了 100 次 INCR ， 那么仅仅是为了保存这个计数器的当前值， AOF 文件就需要使用 100 条记录（entry）。然而在实际上， 只使用一条 SET 命令已经足以保存计数器的当前值了， 其余 99 条记录实际上都是多余的。
为了处理这种情况， Redis 支持一种有趣的特性： 可以在不打断服务客户端的情况下， 对 AOF 文件进行重建（rebuild）。执行 bgrewriteaof 命令， Redis 将生成一个新的 AOF 文件， 这个文件包含重建当前数据集所需的最少命令。
Redis 2.2 需要自己手动执行 bgrewriteaof 命令； Redis 2.4 则可以通过配置自动触发 AOF 重写。

AOF重写的作用

• 减少磁盘占用量
• 加速数据恢复

AOF重写的实现方式

• bgrewriteaof 命令

  Redis bgrewriteaof 命令用于异步执行一个 AOF（AppendOnly File）文件重写操作。重写会创建一个当前AOF文件的体积优化版本。
  即使 bgrewriteaof 执行失败，也不会有任何数据丢失，因为旧的AOF文件在 bgrewriteaof 成功之前不会被修改。
  AOF 重写由 Redis 自行触发，bgrewriteaof 仅仅用于手动触发重写操作。
  具体内容:

  • 如果一个子Redis是通过磁盘快照创建的，AOF重写将会在RDB终止后才开始保存。这种情况下BGREWRITEAOF任然会返回OK状态码。从Redis 2.6起你可以通过INFO命令查看AOF重写执行情况。
  • 如果只在执行的AOF重写返回一个错误，AOF重写将会在稍后一点的时间重新调用。

• AOF重写配置

AOF重写自动触发机制，需要同时满足下面两个条件：

• aof_current_size > auto-aof-rewrite-min-size
• (aof_current_size - aof_base_size) * 100 / aof_base_size > auto-aof-rewrite-percentage

假设 Redis 的配置项为：

auto-aof-rewrite-min-size 64mb
auto-aof-rewrite-percentage 100

当AOF文件的体积大于64Mb，并且AOF文件的体积比上一次重写之久的体积大了至少一倍（100%）时，Redis将执行 bgrewriteaof 命令进行重写。

AOF重写的流程

3. AOF相关配置

# 开启AOF持久化方式
appendonly yes

# AOF持久化文件名
appendfilename appendonly-<port>.aof

# 每秒把缓冲区的数据同步到磁盘
appendfsync everysec

# 数据持久化文件存储目录
dir /var/lib/redis

# 是否在执行重写时不同步数据到AOF文件
# 这里的 yes，就是执行重写时不同步数据到AOF文件
no-appendfsync-on-rewrite yes

# 触发AOF文件执行重写的最小尺寸
auto-aof-rewrite-min-size 64mb

# 触发AOF文件执行重写的增长率
auto-aof-rewrite-percentage 100

4. AOF的优点

1. 使用AOF 会让你的Redis更加耐久: 你可以使用不同的fsync策略：无fsync，每秒fsync，每次写的时候fsync。使用默认的每秒fsync策略，Redis的性能依然很好(fsync是由后台线程进行处理的，主线程会尽力处理客户端请求)，一旦出现故障，你最多丢失1秒的数据。
2. AOF文件是一个只进行追加的日志文件，所以不需要写入seek，即使由于某些原因(磁盘空间已满，写的过程中宕机等等)未执行完整的写入命令，你也也可使用redis-check-aof工具修复这些问题。
3. Redis 可以在 AOF 文件体积变得过大时，自动地在后台对 AOF 进行重写： 重写后的新 AOF 文件包含了恢复当前数据集所需的最小命令集合。 整个重写操作是绝对安全的，因为 Redis 在创建新 AOF 文件的过程中，会继续将命令追加到现有的 AOF 文件里面，即使重写过程中发生停机，现有的 AOF 文件也不会丢失。 而一旦新 AOF 文件创建完毕，Redis 就会从旧 AOF 文件切换到新 AOF 文件，并开始对新 AOF 文件进行追加操作。
4. AOF 文件有序地保存了对数据库执行的所有写入操作， 这些写入操作以 Redis 协议的格式保存， 因此 AOF 文件的内容非常容易被人读懂， 对文件进行分析（parse）也很轻松。 导出（export） AOF 文件也非常简单： 举个例子， 如果你不小心执行了 FLUSHALL 命令， 但只要 AOF 文件未被重写， 那么只要停止服务器， 移除 AOF 文件末尾的 FLUSHALL 命令， 并重启 Redis ， 就可以将数据集恢复到 FLUSHALL 执行之前的状态。

5. AOF的缺点

1. 对于相同的数据集来说，AOF 文件的体积通常要大于 RDB 文件的体积。
2. 根据所使用的 fsync 策略，AOF 的速度可能会慢于 RDB 。 在一般情况下， 每秒 fsync 的性能依然非常高， 而关闭 fsync 可以让 AOF 的速度和 RDB 一样快， 即使在高负荷之下也是如此。 不过在处理巨大的写入载入时，RDB 可以提供更有保证的最大延迟时间（latency）。

四、RDB和AOF的抉择

1. RDB 和 AOF 对比

2. 如何选择使用哪种持久化方式？

一般来说， 如果想达到足以媲美 PostgreSQL 的数据安全性， 你应该同时使用两种持久化功能。

如果你非常关心你的数据， 但仍然可以承受数分钟以内的数据丢失， 那么你可以只使用 RDB 持久化。

有很多用户都只使用 AOF 持久化， 但并不推荐这种方式： 因为定时生成 RDB 快照（snapshot）非常便于进行数据库备份， 并且 RDB 恢复数据集的速度也要比 AOF 恢复的速度要快。

什么是explain

使用优化器可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。

explain能够干什么

• 读取表的顺序
• 哪些索引能够被使用
• 数据读取操作的操作类型
• 哪些索引能够被实际使用
• 表之间的引用
• 每张表有多少行被物理查询

创建一个学习用的数据库

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mydb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`name`) values 
(1,'语文'),(2,'高等数学'),(3,'视听说'),(4,'体育'),(5,'马克思概况'),(6,'民族理论'),(7,'毛中特'),(8,'计算机基础'),(9,'深度学习'),(10,'Java程序设计'),(11,'c语言程序设计'),(12,'操作系统'),(13,'计算机网络'),(14,'计算机组成原理'),(15,'数据结构'),(16,'数据分析'),(17,'大学物理'),(18,'数字逻辑'),(19,'嵌入式开发'),(20,'需求工程');

/*Table structure for table `stu_course` */

DROP TABLE IF EXISTS `stu_course`;

CREATE TABLE `stu_course` (
  `sid` int(10) NOT NULL,
  `cid` int(10) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `stu_course` */

insert  into `stu_course`(`sid`,`cid`) values 
(1,2),(1,4),(1,14),(1,16),(1,19),(2,4),(2,8),(2,9),(2,14),(3,13),(3,14),(3,20),(4,5),(4,8),(4,9),(4,11),(4,16),(5,4),(5,8),(5,9),(5,11),(5,12),(5,16),(6,2),(6,14),(6,17),(7,1),(7,8),(7,15),(8,2),(8,3),(8,7),(8,17),(9,1),(9,7),(9,16),(9,20),(10,4),(10,12),(10,14),(10,20),(11,3),(11,9),(11,16),(12,3),(12,7),(12,9),(12,12),(13,1),(13,5),(13,13),(14,1),(14,3),(14,18),(15,1),
(15,9),(15,15),(16,2),(16,7);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `name_age` (`name`,`age`),
  KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`age`) values 
(25,'乾隆',17),(14,'关羽',43),(13,'刘备',12),(28,'刘永',12),(21,'后裔',12),(30,'吕子乔',28),(18,'嬴政',76),(22,'孙悟空',21),(4,'安其拉',24),(6,'宋江',22),(26,'康熙',51),(29,'张伟',26),(20,'张郃',12),(12,'张飞',32),(27,'朱元璋',19),(11,'李世民',54),(9,'李逵',12),(8,'林冲',43),(5,'橘右京',43),(24,'沙和尚',25),(23,'猪八戒',22),(15,'王与',21),(19,'王建',23),(10,'王莽',43),(16,'秦叔宝',43),(17,'程咬金',65),(3,'荆轲',21),(2,'诸葛亮',71),(7,'钟馗',23),(1,'鲁班',21);

这个数据库实际上的业务是:学生表 - 选课表 - 课程表

如何使用explain

使用而explain很简单就是，在你书写的SQL语句加一个单词 - explain，然后将 explain + SQL执行后会出现一个表，这个表会告诉你MySQL优化器是怎样执行你的SQL的。

就比如执行下面一句语句：

EXPLAIN SELECT * FROM student

MySQL会给你反馈下面一个信息：

    id  select_type  table    partitions  type    possible_keys  key       key_len  ref       rows  filtered  Extra        
------  -----------  -------  ----------  ------  -------------  --------  -------  ------  ------  --------  -------------
     1  SIMPLE       student  (NULL)      index   (NULL)         name_age  68       (NULL)      30    100.00  Using index  

具体这些信息是干什么的，会对你有什么帮助，会在下面告诉你。

explain各个字段代表的意思

• id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• select_type ：查询类型 或者是 其他操作类型
• table ：正在访问哪个表
• partitions ：匹配的分区
• type ：访问的类型
• possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
• key ：实际使用到的索引，如果为NULL，则没有使用索引
• key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
• ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
• rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
• filtered ：查询的表行占表的百分比
• Extra ：包含不适合在其它列中显示但十分重要的额外信息

上面介绍了每个字段的意思，可以大体看一下，下面会逐一介绍每个字段表示的啥？该关注什么？

id与table字段

为什么要将id和table放在一起讲呢？因为通过这两个字段可以完全判断出你的每一条SQL语句的执行顺序和表的查询顺序。

先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那我们就看净胜球，净胜球越多的球队，排在前面。而在explain中你可以把id看作是球队积分，table当作是净胜球。

比如说我们explain一下这一条SQL：

EXPLAIN
SELECT 
    S.id,S.name,S.age,C.id,C.name
FROM course C JOIN stu_course SC ON C.id = SC.cid
JOIN student S ON S.id = SC.sid

结果是这样：

    id  select_type  table   partitions  type    possible_keys        key      key_len  ref      
------  -----------  ------  ----------  ------  -------------------  -------  -------  ----------- 
     1  SIMPLE       SC      (NULL)      index   PRIMARY              PRIMARY  8        (NULL)     
     1  SIMPLE       C       (NULL)      eq_ref  PRIMARY              PRIMARY  4        mydb.SC.cid 
     1  SIMPLE       S       (NULL)      eq_ref  PRIMARY,id_name_age  PRIMARY  4        mydb.SC.sid 

我们看到id全是1，那就说明光看id这个值是看不出来每个表的读取顺序的，那我们就来看table这一行，它的读取顺序是自上向下的，所以，这三个表的读取顺序应当是：SC - C - S。

再来看一条SQL

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` = (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    ) ORDER BY SC.`cid` LIMIT 1
)

这条语句是查询结果是：一个叫安其拉的学生选的课里面，课程id最小的一门课的信息，然后来看一下explain的结果吧！

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref    
------  -----------  ------  ----------  ------  -------------  -------  -------  ------  
     1  PRIMARY      C       (NULL)      const   PRIMARY        PRIMARY  4        const   
     2  SUBQUERY     SC      (NULL)      ref     PRIMARY        PRIMARY  4        const  
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const 

此时我们发现id是不相同的，所以我们很容易就看出表读取的顺序了是吧！C - SC - S

注意！！！！！！你仔细看一下最里面的子查询是查询的哪个表，是S这张表，然后外面一层呢？是SC这张表，最外面这一层呢？是C这张表，所以执行顺序应该是啥呢？是....是.....难道是S - SC - C吗？是id越大的table读取越在前面吗？是的！这就像刚才说的足球联赛积分，分数越高的球队的排序越靠前。

当然还有下面这种情况

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` IN (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    )
)

这个查询是：查询安其拉选课的课程信息

    id  select_type  table   partitions  type    possible_keys  key      key_len  ref           
------  -----------  ------  ----------  ------  -------------  -------  -------  ----------- 
     1  PRIMARY      SC      (NULL)      ref     PRIMARY        PRIMARY  4        const       
     1  PRIMARY      C       (NULL)      eq_ref  PRIMARY        PRIMARY  4        mydb.SC.cid 
     3  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const        

结果很明确：先看id应该是S表最先被读取，SC和C表id相同，然后table中SC更靠上，所以第二张读取的表应当是SC，最后读取C。

select_type字段

• SIMPLE 简单查询，不包括子查询和union查询

  EXPLAIN 
  SELECT * FROM student JOIN stu_course ON student.`id` = sid

      id  select_type  table       partitions  type    possible_keys        key      
  ------  -----------  ----------  ----------  ------  -------------------  --------  
       1  SIMPLE       student     (NULL)      index   PRIMARY,id_name_age  name_age  
       1  SIMPLE       stu_course  (NULL)      ref     PRIMARY              PRIMARY  

• PRIMARY 当存在子查询时，最外面的查询被标记为主查询

• SUBQUERY 子查询

  EXPLAIN
  SELECT SC.`cid` 
  FROM stu_course AS SC 
  WHERE SC.`sid` = 
  (
      SELECT 
          S.`id` 
      FROM student AS S
      WHERE  S.`name` = "安其拉"
  )

      id  select_type  table   partitions  type    possible_keys  key      key_len  ref      
  ------  -----------  ------  ----------  ------  -------------  -------  -------  ------ 
       1  PRIMARY      SC      (NULL)      ref     PRIMARY        PRIMARY  4        const  
       2  SUBQUERY     S       (NULL)      ref     name,name_age  name     63       const   

• UNION 当一个查询在UNION关键字之后就会出现UNION

• UNION RESULT 连接几个表查询后的结果

  EXPLAIN
  SELECT * FROM student WHERE id = 1
  UNION
  SELECT * FROM student WHERE id = 2

      id  select_type   table       partitions  type    possible_keys        key      
  ------  ------------  ----------  ----------  ------  -------------------  ------- 
       1  PRIMARY       student     (NULL)      const   PRIMARY,id_name_age  PRIMARY  
       2  UNION         student     (NULL)      const   PRIMARY,id_name_age  PRIMARY 
  (NULL)  UNION RESULT  <union1,2>  (NULL)      ALL     (NULL)               (NULL)   

  上面可以看到第三行table的值是<union1,2>

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL
  会递归执行这些子查询，把结果放在临时表中
  MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

  如果你想了解更详细的派生合并请点击这里

  当你的MySQL是5.7及以上版本时你要将derived_merge关闭后才能看到DERIVED 状态

  set session optimizer_switch='derived_merge=off';
  set global optimizer_switch='derived_merge=off'; 

  EXPLAIN
  SELECT * FROM 
  (
      SELECT * 
      FROM student AS S JOIN stu_course AS SC 
      ON S.`id` = SC.`sid`
  ) AS SSC

      id  select_type  table       partitions  type    possible_keys        key       
  ------  -----------  ----------  ----------  ------  -------------------  -------- 
       1  PRIMARY      <derived2>  (NULL)      ALL     (NULL)               (NULL)    
       2  DERIVED      S           (NULL)      index   PRIMARY,id_name_age  name_age 
       2  DERIVED      SC          (NULL)      ref     PRIMARY              PRIMARY  

  上面我们观察，最外层的主查询的表是<derived2>,而S和SC表的select_type都是DERIVED,这说明S和SC都被用来做衍生查询，而这两张表查询的结果组成了名为<derived2>的衍生表，而衍生表的命名就是<select_type + id>。

partitions字段

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

注意！！！注意！！！重点来了！

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

• NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

  有没有这样一种疑惑，不查询索引也不查询表那你的数据是从哪里来的啊？谁说SELECT语句必须查询某样东西了？

  EXPLAIN SELECT 5*7

      id  select_type  table   partitions  type    possible_keys  key     
  ------  -----------  ------  ----------  ------  -------------  ------ 
       1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL) 

  我就简简单单算个数不好吗？好啊😊。。。

  但是！！如果只是这样的话我们还explain个毛线啊！我很闲吗？

  存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以你的查询速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那MySQL比你更知道啊！当你要查询最大值或者最小值时，MySQL会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。

  EXPLAIN SELECT MAX(id) FROM student

      id  select_type  table   partitions  type    possible_keys  key    
  ------  -----------  ------  ----------  ------  -------------  ------ 
       1  SIMPLE       (NULL)  (NULL)      (NULL)  (NULL)         (NULL) 

  但是！你要记住，NULL的前提是你已经建立了索引。

• SYSTEM 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

• const 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。

  简单来说，const是直接按主键或唯一键读取。

  EXPLAIN
  SELECT * FROM student AS S WHERE id = 10

      id  select_type  table   partitions  type    possible_keys  key      
  ------  -----------  ------  ----------  ------  -------------  -------  
       1  SIMPLE       S       (NULL)      const   PRIMARY        PRIMARY  

• eq_ref 用于联表查询的情况，按联表的主键或唯一键联合查询。

  多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

  EXPLAIN
  SELECT * FROM student AS S JOIN stu_course AS SC ON  S.`id` = SC.`cid`

      id  select_type  table   partitions  type    possible_keys  key     
  ------  -----------  ------  ----------  ------  -------------  -------  
       1  SIMPLE       SC      (NULL)      index   (NULL)         PRIMARY  
       1  SIMPLE       S       (NULL)      eq_ref  PRIMARY        PRIMARY 

  以上面查询为例，我们观察id和table会知道，先是从SC表中取出一行数据，然后再S表查找匹配的数据，我们观察，SC中取出cid和S表中的id比较，毫无疑问因为id是S表中的主键（不重复），所以只能出现一个id与cid的值相同。所以！满足条件 S 表的 type为eq_ref。

• ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

  EXPLAIN 
  SELECT * FROM student AS S JOIN stu_course AS SC ON S.id = SC.`sid`

  不要在意SQL，以上SQL没有实际查询的意义只是用于表达用例

      id  select_type  table   partitions  type    possible_keys  key      
  ------  -----------  ------  ----------  ------  -------------  -------  
       1  SIMPLE       S       (NULL)      ALL     PRIMARY        (NULL)   
       1  SIMPLE       SC      (NULL)      ref     PRIMARY        PRIMARY  

  SC的主键索引是（cid,sid）所以sid列中肯定是重复的数据，虽然在后面的key中显示使用了主键索引。然后，就很明确了S.id一行能在SC表中通过索引查询到多行数据。

  下面是单表了，写一个例子，但是不细讲了

  EXPLAIN
  SELECT * FROM student AS S WHERE S.`name` = "张飞"

      id  select_type  table   partitions  type    possible_keys  key        
  ------  -----------  ------  ----------  ------  -------------  ---------- 
       1  SIMPLE       S       (NULL)      ref     index_name     index_name

  注意name字段是有索引的哈！！！

• ref_or_null 类似ref，但是可以搜索值为NULL的行

  EXPLAIN
  SELECT * FROM student AS S WHERE S.`name` = "张飞" OR S.`name` IS NULL

      id  select_type  table   partitions  type         possible_keys  key        
  ------  -----------  ------  ----------  -----------  -------------  ----------  
       1  SIMPLE       S       (NULL)      ref_or_null  index_name     index_name  

• index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

  EXPLAIN
  SELECT * FROM student AS S WHERE S.`name` LIKE "张%" OR S.`age` = 30

      id  select_type  table   partitions  type         possible_keys         key                   
  ------  -----------  ------  ----------  -----------  --------------------  -------------------- 
       1  SIMPLE       S       (NULL)      index_merge  index_name,index_age  index_name,index_age 

• range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

  EXPLAIN
  SELECT S.`age` FROM student  AS S WHERE S.`age` > 30

      id  select_type  table   partitions  type    possible_keys         key         
  ------  -----------  ------  ----------  ------  --------------------  ----------  
       1  SIMPLE       S       (NULL)      range   index_name,index_age  index_name  

• indexindex只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

  EXPLAIN
  SELECT S.`name` FROM student AS S 

      id  select_type  table   partitions  type    possible_keys  key         
  ------  -----------  ------  ----------  ------  -------------  ----------  
       1  SIMPLE       S       (NULL)      index   (NULL)         index_name  

• ALL 如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

possible_keys字段

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

key字段

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

ref字段

显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

Extra字段

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

• Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

  EXPLAIN
  SELECT * FROM course AS C ORDER BY C.`name` 

   type    possible_keys  key     key_len  ref       rows  filtered  Extra           
   ------  -------------  ------  -------  ------  ------  --------  ----------------
   ALL     (NULL)         (NULL)  (NULL)   (NULL)      20    100.00  Using filesort  

  没有给C.name建立索引，所以在根据C.name排序的时候，他就使用了外部排序

• Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by。

  EXPLAIN
  SELECT C.`name` FROM course AS C GROUP BY C.`name`

  possible_keys  key     key_len  ref       rows  filtered  Extra                            
  -------------  ------  -------  ------  ------  --------  ---------------------------------
  (NULL)         (NULL)  (NULL)   (NULL)      20    100.00  Using temporary; Using filesort  

  上面这个查询就是同时触发了Using temporary和Using filesort，可谓是雪上加霜。

• Using index 表示使用了索引，很优秀👍。
• Using where 使用了where但是好像没啥用。
• Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
• impossible where 筛选条件没能筛选出任何东西
• distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

InnoDB索引

InnoDB的索引主要分为两大类一个是聚集索引，一个是普通索引。

聚集索引

InnoDB聚集索引的的叶子结点存储行记录，因此，InnoDB必须要有且只有一个聚集索引

• 如果表定义了主键，则主键就是聚集索引
• 如果表没有定义主键，则第一个not null unique列就是聚集索引
• 最后，InnoDB会创建一个隐藏的row_id作为聚集索引

普通索引

InnoDB普通索引的叶子节点存储主键值。通常情况下需要扫码两遍索引树。

为什么不存储指针而是储存主键值

当数据需要更新的时候，二级索引不需要修改，只需要修改聚集索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要聚簇索引，不需要重建二级索引

explain中重要的列

• id 查询中select表的顺序

• type 访问的类型

  • ALL 全表扫描
  • index 索引扫描
  • range 范围扫描
  • ref 非唯一索引扫描
  • eq_ref 唯一索引扫描
  • const 常数
• table 该语句查询的表
• possible_keys 该查询语句，可能走的索引
• key 实际使用的索引
• rows 扫描的行数

• extra

  • using where 使用where筛选
  • using temprorary 使用临时表
  • using filesort 使用文件排序

索引失效

• like查询已 ‘%…’开头，以’xxx%’结尾会继续使用索引

• where语句中使用 <>和 !=。 因为二级索引储存的是主键值，所以失效。对于主键索引还是会使用的

  • or 操作
  • 索引列存在计算或使用函数
  • 联合索引中没有按顺序，或者中间缺失了。
  • 隐式转换

以上情况如果需要查询的列，可以直接索引在索引中得到，那么还是会使用索引的。（覆盖索引）没有使用索引的大部分情况是因为InnoDB普通索引的叶子节点存储主键值，通常情况下需要扫码两遍索引树。