MySQL四、性能优化

一、优化思路
二、连接-配置优化
三、缓存-架构优化
四、优化器-SQL语句分析与优化
五、存储引擎
六、优化体系

一、优化思路

作为架构师或者开发人员，说到数据库优化，思路应该是什么样的？除了索引、分库分表，还有哪些优化的思路？
我们说到性能调优，其实目的是让我们的查询更快。在第一章分析的时候提到，一个查询的动作是由很多个环节组成的，每个环节都会消耗时间，那么我们就可以从这里面的每个环节着手分析，进行优化。

二、连接-配置优化

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？
有可能服务端连接数不够导致应用程序获取不到连接。比如报了一个MySQL：error 1040: Too many connections的错误。
我们可以从两个方面来解决连接数不够的问题：

服务端
从服务端来说，我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：
a. 修改配置参数增加可用连接数，修改max_connections的大小：

mysql> show variables like 'max_connections';  -- 修改最大连接数，当有多个应用连接的时候
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set

mysql> set global max_connections=888;
Query OK, 0 rows affected

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 888   |
+-----------------+-------+
1 row in set

b. 或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。

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

从客户端
从客户端来说，可以减少从服务端获取的连接数，避免每一次执行SQL都去建立连接，这里就需要引入连接池来实现连接的重用。
我们可以在哪些层面使用连接池？ORM层面(MyBatis自带了一个连接池)；或者使用专业的连接池工具(阿里的Druid、Spring Boot 2.x版本默认的连接池 Hikari、老牌的DBCP和C3P0)。
当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？
大家可能会有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获取到更多的连接数，不需要排队。
实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池，其它客户端排队等待获取连接就可以了。毕竟操作系统底层都是线程来做事情的，每个连接服务端都需要创建一个线程去处理它，连接数越多，服务端创建的线程数就越多，操作系统采用时间片机制，可能就需要频繁的上下文切换，而上下文切换又是需要记录之前线程运行的状态等信息的，反而更浪费性能。
我们使用的连接池，像Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。
具体设置多少合适，在Hikari的github文档中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式：https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing它的建议是CPU核心数*2+1。也就是说，4核的机器，连接池维护在9个就够了。这个公式从一定程度上对于其他数据库也是适用的。

我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括 CPU、内存、磁盘、网络。
在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。
在我们这几天的课程里面也接触了很多的 MySQL 和 InoDB 的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的 buffer_pool_size，默认的页大小，InoDB 并发线程数等。这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的 DBA 完成。
至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这些，更不是我们开发人员关注的重点，就不做过多的介绍了。

如果想要了解一些特定的参数的含义，官网有一份系统的参数列表可以参考：5.1.8 Server System Variables

除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户端跟数据库服务端的连接数的方案呢？
我们可以引入缓存。

三、缓存-架构优化

1、缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。
我们可以用第三方的缓存服务来解决这个问题，例如Redis。

运行独立的缓存服务，属于架构层面的优化。
为了减少单台数据库服务器的读写压力，在架构层面我们还可以做其他哪些优化措施？

2、主从复制

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。
集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？
这个时候我们需要用到复制技术（replication），被复制的节点称为master，复制的节点称为slave。slave 本身也可以作为其他节点的数据来源，这个叫做级联复制。

主从复制是怎么实现的呢？更新语句会记录binlog，它是一种逻辑日志。
有了这个binlog，从服务器会获取主服务器的binlog 文件，然后解析里面的SQL语句，在从服务器上面执行一遍，保持主从的数据一致。
这里面涉及到三个线程，连接到master 获取binlog，并且解析binlog 写入中继日志，这个线程叫做I/O 线程。
Master 节点上有一个log dump 线程，是用来发送binlog 给slave 的。从库的SQL 线程，是用来读取relay log，把数据写入到数据库的。

做了主从复制的方案之后，我们只把数据写入master 节点，而读的请求可以分担到 slave 节点。我们把这种方案叫做读写分离。

读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在master 写入了，马上到slave 查询，而这个时候slave 的数据还没有同步过来，怎么办？
所以，基于主从复制的原理，我们需要弄明白，主从复制到底慢在哪里？

a. 单线程

在早期的MySQL 中，slave 的SQL 线程是单线程。master 可以支持SQL 语句的并行执行，配置了多少的最大连接数就是最多同时多少个SQL 并行执行。
而slave 的SQL 却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯定会出现延迟。

为什么从库上的SQL Thread 不能并行执行呢？举个例子，主库执行了多条SQL 语句，首先用户发表了一条评论，然后修改了内容，最后把这条评论删除了。这三条语句在从库上的执行顺序肯定是不能颠倒的。

insert into user_comments (10000009,'nice');
update user_comments set content ='very good' where id =10000009;
delete from user_comments where id =10000009;

怎么解决这个问题呢？怎么减少主从复制的延迟？

b. 异步与全同步

首先我们需要知道，在主从复制的过程中，MySQL 默认是异步复制的。也就是说，对于主节点来说，写入binlog，事务结束，就返回给客户端了。对于slave 来说，接收到binlog，就完事儿了，master 不关心slave 的数据有没有写入成功。

如果要减少延迟，是不是可以等待全部从库的事务执行完毕，才返回给客户端呢？这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端。
这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大家应该能想到，事务执行的时间会变长，它会导致master 节点性能下降。
有没有更好的办法呢？既减少slave 写入的延迟，又不会明显增加master 返回给客户端的时间？

c. 半同步复制

介于异步复制和全同步复制之间，还有一种半同步复制的方式。半同步复制是什么样的呢？
主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到binlog 并写到relay log 中才返回给客户端。master 不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取relay log，写入从库。

如果我们要在数据库里面用半同步复制，必须安装一个插件，这个是谷歌的一位工程师贡献的。这个插件在mysql 的插件目录下已经有提供：cd /usr/lib64/mysql/plugin/
主库和从库是不同的插件，安装之后需要启用：

-- 主库执行
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
set global rpl_semi_sync_master_enabled=1;
show variables like '%semi_sync%';
-- 从库执行
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
show global variables like '%semi%';

相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个slave 写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。
这个是从主库和从库连接的角度，来保证slave 数据的写入。
另一个思路，如果要减少主从同步的延迟，减少SQL 执行造成的等待的时间，那有没有办法在从库上，让多个SQL 语句可以并行执行，而不是排队执行呢？

d. 多库并行复制

怎么实现并行复制呢？设想一下，如果3 条语句是在三个数据库执行，操作各自的数据库，是不是肯定不会产生并发的问题呢？执行的顺序也没有要求。
当然是，所以如果是操作三个数据库，这三个数据库的从库的SQL 线程可以并发执行。这是MySQL 5.6版本里面支持的多库并行复制。

但是在大部分的情况下，我们都是单库多表的情况，在一个数据库里面怎么实现并行复制呢？或者说，我们知道，数据库本身就是支持多个事务同时操作的；为什么这些事务在主库上面可以并行执行，却不会出现问题呢？
因为他们本身就是互相不干扰的，比如这些事务是操作不同的表，或者操作不同的行，不存在资源的竞争和数据的干扰。那在主库上并行执行的事务，在从库上肯定也是可以并行执行，是不是？
比如在master 上有三个事务同时分别操作三张表，这三个事务是不是在slave 上面也可以并行执行呢？

e. 异步复制之GTID复制

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
所以，我们可以把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，我们把它叫做GTID（GlobalTransaction Identifiers），这种主从复制的方式，我们把它叫做基于GTID 的复制。

如果我们要使用GTID 复制，我们可以通过修改配置参数打开它，默认是关闭的：

mysql> show global variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+

无论是优化master 和slave 的连接方式，还是让从库可以并行执行SQL，都是从数据库的层面去解决主从复制延迟的问题。

除了数据库本身的层面之外，在应用层面，我们也有一些减少主从同步延迟的方法。
我们在做了主从复制之后，如果单个master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表。

3、分库分表

垂直分库，减少并发压力。水平分表，解决存储瓶颈。
垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：

水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。

通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的，但是如果master 节点挂了，怎么办？
所以，高可用（High Available）也是高性能的基础。

4、高可用方案

https://dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html

a. 主从复制

传统的HAProxy + keepalived 的方案，基于主从复制。

b. NDB Cluster

https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-overview.html
基于NDB 集群存储引擎的MySQL Cluster。

c. Galera

https://galeracluster.com/
一种多主同步复制的集群方案。

d. MHA/MMM

https://tech.meituan.com/2017/06/29/database-availability-architecture.html
MMM（Master-Master replication manager for MySQL），一种多主的高可用架构，是一个日本人开发的，像美团这样的公司早期也有大量使用MMM。
MHA（MySQL Master High Available）。
MMM 和MHA 都是对外提供一个虚拟IP，并且监控主节点和从节点，当主节点发生故障的时候，需要把一个从节点提升为主节点，并且把从节点里面比主节点缺少的数据补上，把VIP 指向新的主节点。

e. MGR

https://dev.mysql.com/doc/refman/5.7/en/group-replication.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html
MySQL 5.7.17 版本推出的InnoDB Cluster，也叫MySQL Group Replicatioin（MGR），这个套件里面包括了mysql shell 和mysql-route。

总结一下：
高可用HA 方案需要解决的问题都是当一个master 节点宕机的时候，如何提升一个数据最新的slave 成为master。如果同时运行多个master，又必须要解决master 之间数据复制，以及对于客户端来说连接路由的问题。
不同的方案，实施难度不一样，运维管理的成本也不一样。
以上是架构层面的优化，可以用缓存，主从，分库分表。

第三个环节：
解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由Sever自己处理，跳过。

四、优化器-SQL语句分析与优化

优化器就是对我们的SQL 语句进行分析，生成执行计划。
问题：在我们做项目的时候，有时会收到DBA 的邮件，里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？
我们的服务层每天执行了这么多SQL 语句，它怎么知道哪些SQL 语句比较慢呢？
第一步，我们要把SQL 执行情况记录下来。

1、慢日志查询

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

a. 打开慢日志开关

因为开启慢查询日志是有代价的（跟bin log、optimizer-trace 一样），所以它默认是关闭的：
show variables like '%low_query%';
除了这个开关，还有一个参数，控制执行超过多长时间的SQL 才记录到慢日志，默认是10 秒。
show variables like 'long_query_time';
可以直接动态修改参数（重启后失效）。

set @@global.slow_query_log=1; -- 1 开启，0 关闭，重启后失效
set @@global.long_query_time=3; -- mysql 默认的慢查询时间是10 秒，另开一个窗口后才会查到最新值
show variables like '%long_query%';
show variables like '%slow_query%';

或者修改配置文件my.cnf。
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log

模拟慢查询：

select sleep(10);

或者查询百万级别且用不到索引的表。

b. 慢日志分析

日志内容

show global status like '%slow_queries%';  -- 查看有多少慢查询
show variables like '%slow_query%';  -- 获取慢日志目录

cat /var/lib/mysql/ localhost-slow.log

...
# User@Host: root[**] @ localhost [***.***.***.*]  Id:     9
# Query_time: 10.001408  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use mybatis;
SET timestamp=1600246816;
select sleep(10);

有了慢查询日志，怎么去分析统计呢？比如SQL 语句的出现的慢查询次数最多，平均每次执行了多久？

mysql dump slow

https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html
MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。
mysqldumpslow --help
例如：查询用时最多的 20 条慢 SQL：mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log

Count:代表这个SQL执行了多少次。
Time：执行花费时间，括号内为累计时间。
Lock：表示锁定的时间，括号是累计。
Rows:表示返回的记录数，括号内是累计。
除了慢查询日志之外，还有一个 SHOW PROFILE 工具可以使用。

2、show profile

https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
SHOW PROFILE 是谷歌高级架构师 Jermy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。
在 SQL 中输入 hel profile 可以得到详细的帮助信息。

a. 查看是否开启

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set

mysql> set @@profiling=1;
Query OK, 0 rows affected

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

b. 查看profile统计

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 3.7E-5   |
| checking permissions | 4E-6     |
| Opening tables       | 6.2E-5   |
| init                 | 5E-6     |
| System lock          | 6E-6     |
| optimizing           | 1.3E-5   |
| statistics           | 1.1E-5   |
| preparing            | 4E-6     |
| executing            | 0.000197 |
| Sending data         | 9E-6     |
| end                  | 1E-6     |
| query end            | 2E-6     |
| closing tables       | 2E-6     |
| removing tmp table   | 7E-6     |
| closing tables       | 4E-6     |
| freeing items        | 3.3E-5   |
| cleaning up          | 6E-6     |
+----------------------+----------+

3.7E-5，小数点左移 5 位，代表 0.037 秒。
也可以根据 ID 查看执行详细信息，在后面带上 for query + ID。

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 4.2E-5   |
| checking permissions | 1.1E-5   |
| Opening tables       | 1.9E-5   |
| init                 | 1.1E-5   |
| optimizing           | 3E-6     |
| executing            | 5E-6     |
| end                  | 2E-6     |
| query end            | 2E-6     |
| closing tables       | 2E-6     |
| freeing items        | 3.5E-5   |
| cleaning up          | 1E-5     |
+----------------------+----------+

除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。

c. 其它系统命令

show processlist 运行线程

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

mysql> show processlist;
+----+------+-----------------+---------+---------+--------+-------+------------------+
| Id | User | Host            | db      | Command | Time   | State | Info             |
+----+------+-----------------+---------+---------+--------+-------+------------------+
|  3 | root | localhost:2134  | NULL    | Sleep   | 170700 |       | NULL             |
|  4 | root | localhost:2135  | mybatis | Sleep   | 170699 |       | NULL             |
|  7 | root | localhost:11253 | mybatis | Sleep   |  87388 |       | NULL             |
|  9 | root | localhost:8619  | mybatis | Query   |      0 | init  | show processlist |
| 10 | root | localhost:8924  | mybatis | Sleep   |    133 |       | NULL             |
+----+------+-----------------+---------+---------+--------+-------+------------------+
5 rows in set

这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kil 线程。
也可以查表，效果一样：
select * from information_schema.processlist;

列 | 含义

| -
Id | 线程的唯一标志，可以根据它 kil 线程
User | 启动这个线程的用户，普通用户只能看到自己的线程
Host | 哪个 IP 端口发起的连接
db | 操作的数据库
Comand | 线程的命令 https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
Time | 操作持续时间，单位秒
State | 线程状态，比如查询可能有 copying to tmp table，Sorting result，Sendig data https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
Info | SQL 语句的前 10 个字符，如果要查看完整的 SQL 语句，用 SHOW FULL PROCESLIST

show status 服务器运行状态

https://dev.mysql.com/doc/refman/8.0/en/show-status.html
SHOW STAUS 用于查看 MySQL 服务器运行状态（重启后会清空），有 session 和 global 两种作用域，格式：参数-值。
可以用 like 带通配符过滤。

mysql> show global status like '%select%'; -- 查看select次数 Com_select 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_insert_select        | 0     |
| Com_replace_select       | 0     |
| Com_select               | 34    |
| Connection_errors_select | 0     |
| Select_full_join         | 0     |
| Select_full_range_join   | 0     |
| Select_range             | 0     |
| Select_range_check       | 0     |
| Select_scan              | 96    |
+--------------------------+-------+
9 rows in set

show engine 存储引擎运行信息

https://dev.mysql.com/doc/refman/8.0/en/show-engine.html
show engine 用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件 IO 请求；bufer pool 统计信息。

show engine innodb status;
如果需要将监控信息输出到错误信息 error log 中（15 秒钟一次），可以开启输出。

mysql> show variables like 'innodb_status_output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | OFF   |
| innodb_status_output_locks | OFF   |
+----------------------------+-------+
2 rows in set
mysql> set global innodb_status_output=ON;
Query OK, 0 rows affected
mysql> set @@global.innodb_status_output_locks=1;
Query OK, 0 rows affected

我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运行信息的命令，如果让你去写一个数据库监控系统，你会怎么做？
其实很多开源的慢查询日志监控工具，他们的原理其实也都是读取的系统的变量和状态。

现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪里？
MySQL 提供了一个执行计划的工具（在架构中我们有讲到，优化器最终生成的就是一个执行计划），其他数据库，例如 Oracle 也有类似的功能。
通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

explain 可以分析 update、delte、insert 么？
MySQL 5.63以前只能分析 SELCT; MySQL5.63以后就可以分析update、delte、insert 了。

3、explain 执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

DROP TABLE IF EXISTS course;
CREATE TABLE `course` (
 `cid` int(3) DEFAULT NULL,
 `cname` varchar(20) DEFAULT NULL,
 `tid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher` (
 `tid` int(3) DEFAULT NULL,
 `tname` varchar(20) DEFAULT NULL,
 `tcid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE `teacher_contact` (
 `tcid` int(3) DEFAULT NULL,
 `phone` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `course` VALUES ('1', 'mysql', '1');
INSERT INTO `course` VALUES ('2', 'jvm', '1');
INSERT INTO `course` VALUES ('3', 'juc', '2');
INSERT INTO `course` VALUES ('4', 'spring', '3');

INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
INSERT INTO `teacher` VALUES ('2', 'jack', '2');
INSERT INTO `teacher` VALUES ('3', 'mic', '3');

INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
INSERT INTO `teacher_contact` VALUES ('3', '17722225555');

explain 的结果有很多的字段，我们详细地分析一下。
先确认一下环境：

select version();
show variables like '%engine%';

接下来分析explain内每个自动的含义：

3.1、id

a. id值不同

id值不同的时候，先查询id值大的(先大后小)
使用子查询，根据课程名称查找对应教师的联系方式：

EXPLAIN 
SELECT tc.phone 
FROM teacher_contact tc
WHERE tc.tcid = (
  SELECT t.tcid
  FROM teacher t
  WHERE t.tid = (
    SELECT c.tid
    FROM course c
    where c.cname = 'mysql'
  )
)

查询顺序：先查课程表，再查教师表，最后查教师联系方式表； course c -> teacher t -> teacher_contact tc

因为是子查询，只有拿到内层的结果之后才会进行外层查询。

b. id值相同

同样是根据课程名称查找对应教师的联系方式：

EXPLAIN
SELECT tc.phone 
FROM teacher_contact tc, teacher t, course c
WHERE tc.tcid = t.tcid 
  AND t.tid = c.tid
  AND c.cname = 'mysql';

此时，id值相同，表的查询顺序是从上往下顺序执行。例如这次查询的id都是1，查询的顺序是 teacher_contact(3条) - teacher(3条) - course(4条)。

在做一个测试，往teacher表里插入3条数据后再查看刚才的执行计划

INSERT INTO `teacher` VALUES (4, 'james', 4);
INSERT INTO `teacher` VALUES (5, 'tom', 5);
INSERT INTO `teacher` VALUES (6, 'seven', 6);
COMMIT;

EXPLAIN
SELECT tc.phone 
FROM teacher_contact tc, teacher t, course c
WHERE tc.tcid = t.tcid 
  AND t.tid = c.tid
  AND c.cname = 'mysql';

会发现id也都是1，但是从上往下查询顺序变成了：teacher_contact(3条) -> teacher(t条) -> course(6条)

为什么数据量不同的时候顺序会发生变化呢？这个是由笛卡尔积决定的。
举例：假如有 a、b、c 三张表，分别有 2、3、4 条数据，如果做三张表的联合查询，当查询顺序是 a→b→c 的时候，它的笛卡尔积是：234=64=24。如果查询顺序是 c→b→a，它的笛卡尔积是 432=12=24。
因为 MySQL 要把查询的结果，包括中间结果和最终结果都保存到内存，所以 MySQL会优先选择中间结果数据量比较小的顺序进行查询。所以最终联表查询的顺序是 a→b→c。这个就是为什么 teacher 表插入数据以后查询顺序会发生变化。
这也是小表驱动大表的思想

c. 既有相同也有不同

如果id有相同也有不同，就是ID不同的先大后小，ID相同的从上往下。

3.2、select_type 查询类型

3.2.1 SIMPLE

简单查询，不包含子查询，不包含关联查询 union。
比如刚刚的查询，或者更简单的一条查询语句。

EXPLAIN
SELECT tc.phone 
FROM teacher_contact tc, teacher t, course c
WHERE tc.tcid = t.tcid 
  AND t.tid = c.tid
  AND c.cname = 'mysql';

这里并没有列举全部（其它：DEPNDENT UNION、DEPNDENT SUBQUERY、MATERIALIZED、UNCAHEABLE SUBQUERY、UNCAHEABLE UNION）。

3.2.2 PRIMARY、SUBQUERY

PRIMARY ：子查询 SQL 语句中的主查询，也就是最外面的那层查询。
SUBQUERY ：子查询中所有的内层查询都是 SUBQUERY 类型的

EXPLAIN 
SELECT tc.phone 
FROM teacher_contact tc
where tc.tcid = (
  SELECT t.tcid
  FROM teacher t
  WHERE t.tid = (
    SELECT c.tid
    FROM course c
    where c.cname = 'mysql'
  )
);

3.2.3 DERIVED、UNION、UNION RESULT

DERIVED ：衍生查询，表示在得到最终查询结果之前会用到临时表。
UNION ：用到了 UNION 查询。
UNION RESULT ：主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。

EXPLAIN SELECT cr.cname
FROM (
SELECT * FROM course WHERE tid = 1
UNION
SELECT * FROM course WHERE tid = 2
) cr;

3.3、type连接类型

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
所有的连接类型中，上面的最好，越往下越差。
在常用的链接类型中：sytem > const > eq_ref > ref > range > index > all
这里并没有列举全部（其他： fulltext 、 ref_or_null 、 index_merger 、unique_subquery、index_subquery）。
以上访问类型除了 all，都能用到索引。

3.3.1 const

主键索引或者唯一索引，只能查到一条数据的 SQL。

DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data(
id int(3) PRIMARY KEY, 
content varchar(20)
);
insert into single_data values(1,'a');

EXPLAIN SELECT * FROM single_data a where id = 1;

3.3.2 system

system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。

EXPLAIN SELECT * FROM mysql.proxies_priv;

3.3.3 eq_ref

通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的访问类型。
现在为teacher、teacher_contact 建立索引，且保持两表内连接时数据完全匹配。

DELETE FROM teacher where tid in (4,5,6);
commit;

ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);  -- 建立主键索引
ALTER TABLE teacher ADD INDEX idx_tcid(tcid);  -- 关联字段建立索引

-- 执行查看执行计划
EXPLAIN
SELECT t.tcid 
FROM teacher t, teacher_contact tc 
WHERE t.tcid = tc.tcid;

此时的执行计划（teacher_contact 表是 eq_ref）：

以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。毕竟条件都比较苛刻。

3.3.4 ref

查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
explain SELECT * FROM teacher where tcid = 3; 执行范围查询（字段上有普通索引）：

3.3.5 rang

索引范围扫描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。
explain SELECT * FROM teacher where tcid < 3;

3.3.6 index

Full Index Scan，查询全部索引中的数据，无需回表（比不走索引要快）。
explain SELECT tcid FROM teacher;

3.3.7 all

Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。

3.3.8 NULL

不用访问表或者索引就能得到结果，例如：
explain select 1 from dual;

一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。ALL（全表扫描）和 index（查询全部索引）都是需要优化的。

3.4、possible_key、key

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。
possible_key 可以有一个或者多个，可能用到索引不代表一定用到索引。
反过来，possible_key 为空，key 可能有值吗？

-- 表上创建联合索引：
ALTER TABLE course ADD INDEX comidx_name_tid(cname,tid);

-- 执行计划，只查询索引字段
explain SELECT tid from course;

结论：是有可能的（这里是覆盖索引的情况）。如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。

3.5、ken_len 索引的长度

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。

3.6、rows

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

3.7、filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

3.8、ref

使用哪个列或者常数和索引一起从表中筛选数据。

3.9、Extra

执行计划给出的额外的信息说明。

3.9.1 using index

使用了覆盖索引，不需要回表。

3.9.2 using where

使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。

3.9.3 using index condition (ICP)

索引下推，前一章单独介绍了。
https://www.cnblogs.com/Qkxh320/p/MySQL_02.html#4-%E7%B4%A2%E5%BC%95%E6%9D%A1%E4%BB%B6%E4%B8%8B%E6%8E%A8icp-index-condition-push-down

3.9.4 using filesort

不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。

3.9.5 using temporary

用到了临时表。例如（以下不是全部的情况）：
1、distnct 非索引列
EXPLAIN select DISTINCT(tid) from teacher t;
2、group by 非索引列
EXPLAIN select tname from teacher group by tname;
3、使用 join 的时候，group 任意列
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid
需要优化，例如创建复合索引。
此时可以为where、order by、join的字段建立索引来提升查询性能。

模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对 SQL 语句的具体优化。比如怎么用到索引，怎么减少锁的阻塞等待，之前两章也单独介绍过了。

4、SQL与索引优化

当我们的 SQL 语句比较复杂，有多个关联和子查询的时候，就要分析 SQL 语句有没有改写的方法。
举个简单的例子，一模一样的数据：

-- 大偏移量的 limit
select * from user_inodb limit 900000,10; 
-- 改成先过滤 ID，再 limt
SELECT * FROM user_inodb WHERE id >= 900000 LIMIT 10;

对于具体的 SQL 语句的优化，MySQL 官网也提供了很多建议，这个是我们在分析具体的 SQL 语句的时候需要注意的，也是以后的工作里面要去慢慢地积累的。

https://dev.mysql.com/doc/refman/8.0/en/optimization.html

五、存储引擎

1、存储引擎的选择

为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。临时数据用 Memory。常规的并发大更新多的表用 InnoDB。批量数据移植的表可以先使用MyISAM存储引擎，移植之后使用InnoDB。

2、分区或分表

分区不推荐。
交易历史表：在年底为下一年度建立 12 个分区，每个月一个分区。
渠道交易表：分成当日表；当月表；历史表，历史表再做分区。

3、字段定义

原则：使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型：

3.1、整数类型

INT 有 8 种类型，不同的类型的最大存储范围是不一样的。性别？用 TINYINT，因为 ENUM 也是整型存储。

3.2、字符类型

变长情况下，varchar 更节省空间，但是对于 varchar 字段，需要一个字节来记录长度。
固定长度的用 char，不要用 varchar。

3.3、非空

非空字段尽量定义成 NOT NULL，提供默认值，或者使用特殊值、空串代替 nnul。NULL 类型的存储、优化、使用都会存在问题。

3.4、不要用外键、触发器、视图

降低了可读性；
影响数据库性能，应该把计算的事情交给程序，数据库专心做存储；
数据的完整性应该在程序中检查。

3.5、大文件存储

不要用数据库存储图片（比如 base64 编码）或者大文件；
把文件放在 NAS 上，数据库只需要存储 URI（相对路径），在应用中配置 NAS 服务器地址。

3.6、表拆分

将不常用的字段拆分出去，避免列数过多和数据量过大。
比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用blob 或者 text 存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

六、优化体系

除了对于代码、SQL 语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。举几个例子：
1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动（充 30 送 50）？
因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。
2）在去年的双十一，为什么在凌晨禁止查询今天之外的账单？
这是一种降级措施，用来保证当前最核心的业务。
3）最近几年的双十一，为什么提前一个多星期就已经有双十一当天的价格了？
预售分流。

在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入 MQ 削峰，等等。
为什么同样用 MySQL，有的公司可以扛住百万千万级别的并发，而有的公司几百个并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。
当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。

posted @ 2020-04-19 20:10 BigShen 阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

独学而无友，则孤陋而寡闻