优化思路
作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，你会怎么回答？我们在第一章开始的时候讲了，这章的目标是为了让大家建立数据库的知识体系，和正确的调优的思路。我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多个环节组成的，每个环节都会消耗时间，我们在第一章讲 SQL 语句的执行流程的时候已经分析过了。我们要减少查询所消耗的时间，就要从每一个环节入手。
sql执行流程.png
在项目里面，方会开启事务，或者配置了事务？无论是在方法上加注解，还是配置切面。
一，连接——配置优化
第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error 1040: Too many connections 的错误。我们可以从两个方面来解决连接数不够的问题：
1，服务端
我们可以增加服务端的可用连接数。如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：
（1）修改配置参数增加可用连接数，修改 max_connections 的大小：
show variables like 'max_connections';
（2）或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是 28800 秒，8 小时，我们可以把这个值调小。
show global variables like 'wait_timeout';
2，客户端
可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL 都创建一个新的连接，应该怎么做？
这个时候我们可以引入连接池，实现连接的重用。
我们可以在哪些层面使用连接池？ORM 层面（MyBatis 自带了一个连接池）；或者使用专用的连接池工具（阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老牌的 DBCP 和 C3P0）。
当客户端改成从连接池获取连接之后，连接池的大小应该怎么设置呢？
大家可能会有一个误解，觉得连接池的最大连接数越大越好，这样在高并发的情况下客户端可以获取的连接数更多，不需要排队。实际情况并不是这样。连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。
Druid 的默认最大连接池大小是 8。Hikari 的默认最大连接池大小是 10。为什么默认值都是这么小呢？
在 Hikari 的 github 文档中，给出了一个 PostgreSQL 数据库建议的设置连接池大小的公式：github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing它的建议是机器核数乘以 2 加 1。也就是说，4 核的机器，连接池维护 9 个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接池大小实现提升并发度和吞吐量的案例。
为什么有的情况下，减少连接数反而会提升吞吐量呢？
为什么建议设置的连接池大小要跟 CPU 的核数相关呢？
每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。
问题：CPU 是怎么同时执行远远超过它的核数大小的任务的？
时间片。上下文切换。而 CPU 的核数是有限的，频繁的上下文切换会造成比较大的性能开销。我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括 CPU、内存、磁盘、网络。在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。在我们前面也接触了很多的 MySQL 和 InnoDB 的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的 buffer_pool_size，默认的页大小，InnoDB 并发线程数等等。这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的 DBA 完成。至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这些，更不是我们开发人员关注的重点，这个我们就不做过多的介绍了。如果想要了解一些特定的参数的含义，官网有一份系统的参数列表可以参考：dev.mysql.com/doc/refman/5.7/en/server-system-variables.html除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户端跟数据库服务端的连接数的方案呢？我们可以引入缓存。
二，缓存——架构优化
1，缓存
在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。
我们可以用第三方的缓存服务来解决这个问题，例如 Redis。
缓存.png
运行独立的缓存服务，属于架构层面的优化。为了减少单台数据库服务器的读写压力，在架构层面我们还可以做其他哪些优化措施？
主从复制
如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？这个时候我们需要用到复制技术（replication），被复制的节点称为 master，复制的节点称为 slave。slave 本身也可以作为其他节点的数据来源，这个叫做级联复制。
主从复制.png
主从复制是怎么实现的呢？
更新语句会记录 binlog，它是一种逻辑日志。有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL语句，在从服务器上面执行一遍，保持主从的数据一致。
涉及到三个线程：
连接到 master 获取 binlog，并且解析 binlog 写入中继日志，这个线程叫做 I/O 线程。
Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的。
从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。
master-slave.png
3，读写分离
做了主从复制的方案之后，我们只把数据写入 master 节点，而读的请求可以分担到slave 节点。我们把这种方案叫做读写分离。
读写分离.png
读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在 master 写入了，马上到 slave 查询，而这个时候 slave 的数据还没有同步过来，怎么办？
所以，基于主从复制的原理，我们需要弄明白，主从复制到底慢在哪里？
1，单线程
在早期的 MySQL 中，slave 的 SQL 线程是单线程。master 可以支持 SQL 语句的并行执行，配置了多少的最大连接数就是最多同时多少个 SQL 并行执行。
而 slave 的 SQL 却只能单线程排队执行，在主库并发量很大的情况下，同步数据肯定会出现延迟。
为什么从库上的 SQL Thread 不能并行执行呢？举个例子，主库执行了多条 SQL 语句，首先用户发表了一条评论，然后修改了内容，最后把这条评论删除了。这三条语句在从库上的执行顺序肯定是不能颠倒的。
insertinto(10000009,'nice');
updateset='very good'where=10000009;
deletefromwhere=10000009;
怎么解决这个问题呢？怎么减少主从复制的延迟？
2，异步与全同步
首先我们需要知道，在主从复制的过程中，MySQL 默认是异步复制的。也就是说，对于主节点来说，写入 binlog，事务结束，就返回给客户端了。对于 slave 来说，接收到 binlog，就完事儿了，master 不关心 slave 的数据有没有写入成功。
全同步复制.png
如果要减少延迟，是不是可以等待全部从库的事务执行完毕，才返回给客户端呢？这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端。
这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大家应该能想到，事务执行的时间会变长，它会导致 master 节点性能下降。
有没有更好的办法呢？既减少 slave 写入的延迟，又不会明显增加 master 返回给客户端的时间？
3，半同步复制
介于异步复制和全同步复制之间，还有一种半同步复制的方式。
半同步复制是什么样的呢？
主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到 binlog 并写到 relay log 中才返回给客户端。master 不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取 relay log，写入从库。
半同步复制.png
如果我们要在数据库里面用半同步复制，必须安装一个插件，这个是谷歌的一位工程师贡献的。这个插件在 mysql 的插件目录下已经有提供：
cd /usr/lib64/mysql/plugin/
主库和从库是不同的插件，安装之后需要启用：
-- 主库执行
INSTALLPLUGINSONAME'semisync_master.so';
setglobal=1;
showvariableslike'%semi_sync%';
-- 从库执行
INSTALLPLUGINSONAME'semisync_slave.so';
setglobal=1;
showglobalvariableslike'%semi%';
相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个 slave 写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。
这个是从主库和从库连接的角度，来保证 slave 数据的写入。
另一个思路，如果要减少主从同步的延迟，减少 SQL 执行造成的等待的时间，那有没有办法在从库上，让多个 SQL 语句可以并行执行，而不是排队执行呢？
多库并行复制
怎么实现并行复制呢？设想一下，如果 3 条语句是在三个数据库执行，操作各自的数据库，是不是肯定不会产生并发的问题呢？执行的顺序也没有要求。当然是，所以如果是操作三个数据库，这三个数据库的从库的 SQL 线程可以并发执行。这是 MySQL 5.6版本里面支持的多库并行复制。
多库并行复制.png
但是在大部分的情况下，我们都是单库多表的情况，在一个数据库里面怎么实现并行复制呢？或者说，我们知道，数据库本身就是支持多个事务同时操作的；为什么这些事务在主库上面可以并行执行，却不会出现问题呢？
因为他们本身就是互相不干扰的，比如这些事务是操作不同的表，或者操作不同的行，不存在资源的竞争和数据的干扰。那在主库上并行执行的事务，在从库上肯定也是可以并行执行，是不是？比如在 master 上有三个事务同时分别操作三张表，这三个事务是不是在 slave 上面也可以并行执行呢？
5，异步复制之 GTID
复制dev.mysql.com/doc/refman/5.7/en/replication-gtids.html所以，我们可以把那些在主库上并行执行的事务，分为一个组，并且给他们编号，这一个组的事务在从库上面也可以并行执行。这个编号，我们把它叫做 GTID（Global Transaction Identifiers），这种主从复制的方式，我们把它叫做基于 GTID 的复制。
GTID复制.png
如果我们要使用 GTID 复制，我们可以通过修改配置参数打开它，默认是关闭的：
show global variables like 'gtid_mode';
无论是优化 master 和 slave 的连接方式，还是让从库可以并行执行 SQL，都是从数据库的层面去解决主从复制延迟的问题。
除了数据库本身的层面之外，在应用层面，我们也有一些减少主从同步延迟的方法。
我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据分型拆分，这个就是分库分表
4，分库分表
垂直分库，减少并发压力。水平分表，解决存储瓶颈。
1，垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：
垂直分库.png
2，水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。
水平分库分表.png
通过主从或者分库分表可以减少单个数据库节点的访问压力和存储压力，达到提升数据库性能的目的，但是如果 master 节点挂了，怎么办？所以，高可用（High Available）也是高性能的基础。
3，高可用方案dev.mysql.com/doc/mysql-ha-scalability/en/ha-overview.html
cluster-components-1.png
主从复制
传统的 HAProxy + keepalived 的方案，基于主从复制。
NDB Cluster
dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-overview.html基于 NDB 集群存储引擎的 MySQL Cluster。
Galera
galeracluster.com/一种多主同步复制的集群方案。
Galera.png
MHA/MMM
tech.meituan.com/2017/06/29/database-availability-architecture.htmlMMM（Master-Master replication manager for MySQL），一种多主的高可用架构，是一个日本人开发的，像美团这样的公司早期也有大量使用 MMM。MHA（MySQL Master High Available）。MMM 和 MHA 都是对外提供一个虚拟 IP，并且监控主节点和从节点，当主节点发生故障的时候，需要把一个从节点提升为主节点，并且把从节点里面比主节点缺少的数据补上，把 VIP 指向新的主节点。
MGR
dev.mysql.com/doc/refman/5.7/en/group-replication.htmldev.mysql.com/doc/refman/5.7/en/mysql-cluster.htmlMySQL 5.7.17 版本推出的 InnoDB Cluster，也叫 MySQL Group Replicatioin（MGR），这个套件里面包括了 mysql shell 和 mysql-route。
MGR.png
总结一下：
高可用 HA 方案需要解决的问题都是当一个 master 节点宕机的时候，如何提升一个数据最新的 slave 成为 master。如果同时运行多个 master，又必须要解决 master 之间数据复制，以及对于客户端来说连接路由的问题。不同的方案，实施难度不一样，运维管理的成本也不一样。以上是架构层面的优化，可以用缓存，主从，分库分表。
三，解析器
词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever自己处理，跳过。
四，优化器——SQL 语句分析与优化
优化器就是对我们的 SQL 语句进行分析，生成执行计划。问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？第一步，我们要把 SQL 执行情况记录下来。
1，慢查询日志 slow query log：dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
（1）打开慢日志开关：因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：
show variables like 'slow_query%;
v1.png
（2）控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。
show variables like '%slow_query%';
可以直接动态修改参数（重启后失效）。
set@@global.slow_query_log=1;-- 1 开启，0 关闭，重启后失效
set@@global.long_query_time=3;-- mysql 默认的慢查询时间是 10 秒，另开一个窗口后才会查到最新值
showvariableslike'%long_query%';
showvariableslike'%slow_query%;
或者修改配置文件 my.cnf。以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
（3）模拟慢查询：
select sleep(10);
查询 user_innodb 表的 500 万数据（检查是不是没有索引）。
SELECT * FROM `user_innodb` where phone = '136';
（4）慢日志分析
日志内容
showglobalstatuslike'slow_queries';-- 查看有多少慢查询
showvariableslike'%slow_query%';-- 获取慢日志目录
cat /var/lib/mysql/ localhost-slow.log
log1.png
有了慢查询日志，怎么去分析统计呢？比如 SQL 语句的出现的慢查询次数最多，平均每次执行了多久？
mysqldumpslowdev.mysql.com/doc/refman/5.7/en/mysqldumpslow.htmlMySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。
mysqldumpslow --help
例如：查询用时最多的 20 条慢 SQL：
mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-
log2.png
Count 代表这个 SQL 执行了多少次；Time 代表执行的时间，括号里面是累计时间；Lock 表示锁定的时间，括号是累计；Rows 表示返回的记录数，括号是累计。
2， SHOW PROFILE 工具可以使用.
SHOW PROFILE
dev.mysql.com/doc/refman/5.7/en/show-profile.htmlSHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。在 SQL 中输入 help profile 可以得到详细的帮助信息.
查看是否开启
select @@profiling;
set@profiling=1;
查看 profile 统计（命令最后带一个 s）
show profiles;
v2.png
查看最后一个 SQL 的执行详细信息，从中找出耗时较多的环节（没有 s）
show profile;
v3.png
6.2E-5，小数点左移 5 位，代表 0.000062 秒。
也可以根据 ID 查看执行详细信息，在后面带上 for query + ID.
show profile for query 1;
3，除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。
（1）show processlist 运行线程
dev.mysql.com/doc/refman/5.7/en/show-processlist.html
show processlist;这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kill 线程。也可以查表，效果一样：
select * from information_schema.processlist;
v4.png
（2）show status 服务器运行状态
dev.mysql.com/doc/refman/5.7/en/show-status.htmlSHOW STATUS 用于查看 MySQL 服务器运行状态（重启后会清空），有 session和 global 两种作用域，格式：参数-值。可以用 like 带通配符过滤。
SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次数
（3）show engine 存储引擎运行信息
dev.mysql.com/doc/refman/5.7/en/show-engine.htmlshow engine 用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件 IO 请求；buffer pool 统计信息。例如：
show engine innodb status;
如果需要将监控信息输出到错误信息 error log 中（15 秒钟一次），可以开启输出。
showvariableslike'innodb_status_output%';-- 开启输出：
SETGLOBAL=ON;
SETGLOBAL=ON;
我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运行信息的命令，如果让你去写一个数据库监控系统，你会怎么做？其实很多开源的慢查询日志监控工具，他们的原理其实也都是读取的系统的变量和状态。
现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪里?
MySQL 提供了一个执行计划的工具（在架构中我们有讲到，优化器最终生成的就是一个执行计划），其他数据库，例如 Oracle 也有类似的功能。通过 EXPLAIN 我们可以模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。explain 可以分析 update、delete、insert 么？MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、insert 了.
4，EXPLAIN 执行计划
官方链接：dev.mysql.com/doc/refman/5.7/en/explain-output.html我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。
TABLEIFEXISTS;
CREATETABLE`course`(
`cid`int(3)DEFAULTNULL,`cname`varchar(20)DEFAULTNULL,`tid`int(3)DEFAULT)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
DROPTABLEIFEXISTS;
CREATETABLE`teacher`(
`tid`int(3)DEFAULTNULL,`tname`varchar(20)DEFAULTNULL,`tcid`int(3)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
DROPTABLEIFEXISTS;
CREATETABLE`teacher_contact`(
`tcid`int(3)DEFAULTNULL,`phone`varchar(200)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
INSERTINTO`course`VALUES('1','mysql',);
INSERTINTO`course`VALUES('2','jvm','1');
INSERTINTO
