mysql性能优化 （四）
有了前面的三篇内容的支持，对查询修改及索引的底层运行原理都有了一定的了解。下面就由前面的知道来总结下对于数据库的优化可以从哪几个维度进行展开。我们在展开优化时，先从下图的SQL执行流程图分析开始。
1 连接
从上图分析可知，优化考虑的第一点应该是客户端到服务端的连接数不够导致应用程序获取不了连接。至于如何解决连接数不够的问题我们可以从以下两个方面解决
1.从服务端来说，我们可以增加服务端的可用连接数。如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：
show variables like 'max_connections';
Druid 的默认最大连接池大小是 8。Hika ri 的默认最大连接池大小是 10。在 Hikari 的github 文挡中，给出了一个 PostgreSQL 数据库建议的设置连接池大小 的公式。https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing，里面说明建议是机器核数乘以2加1。也就是说，4 核的机器 ，连接池维护 9 个连接就 够了。这个公式从一定程度上来说对真他数据库也是适用的。这里面还有一个减少连接 池大小实现提升并发度和吞吐量的案例。
2 架构优化
2.1 缓存
在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。我们可以用第三方的缓存服务来解决这个问题，例如 Redis。
2.2 主从复制
在架构层面的优化除了缓存外我们还可以做集群来进行优化，集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。这个时候我们需要用到复制技术
如上图所示，在主从复制中，更新语句会记录 binlog，前面我们说过它是一种逻辑日志。有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL语句，在从服务器上面执行一遍，保持主从的数据一致。这里面涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写入中继日志，这个线程叫做 I/O 线程；Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的；从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。 如果数据库做了主从复制后，我们只需要把数据写入主节点，而从节点分担读的请求，这种方法我们也可以叫做读写分离。读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。如果我们在 主节点写入了，马上到 从节点 查询，而这个时候 从节点 的数据还没有同步过来，就会导致查询的数据偏差，在解决这个问题前我先来说下MYSQL的发展史。
2.2.1 单线程
在最早的时候MySQL的主服务器可以支持SQL的并发执行，但是从服务器却是单线程的，这就会导致在主服务器高并发时从服务同步数据时会出现延迟。至于为什么从服务器的线程不支持并发执行，那是因为如果主服务器如果对同一个表中的数据同时进行增改删的操作如果从库并发执行的话就可能会导致系统报错，因为对同一张表的增改删操作是有序的。
2.2.2 异步与全同步
在主从复制的过程中，MySQL 默认是异步复制的。对于主节点来说，写入 binlog，事务结束，就返回给客户端了。对于 从节点 来说，接收到 binlog，就完事儿了，主节点不关心 从节点 的数据有没有写入成功。这就是数据延迟的原因，为了减少这种延迟我们可以等待全部从库的事务执行完毕，才返回给客户端，这样的方式叫做全同步复制。从库写完数据，主库才返会给客户端。这种方式虽然可以保证在读之前，数据已经同步成功了，但是带来的副作用大就是事务执行的时间会变长，它会导致主节点性能下降。为了解决性能下降问题就有了下面的半同步。
2.2.3 半同步
介于异步复制和全同步复制之间，这就是半同步复制。半同步复制的过程是主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到 binlog 并写到 relay log 中才返回给客户端。主库不会等待很长的时间，但是返回给客户端的时候，数据就即将写入成功了，因为它只剩最后一步了：就是读取 relay log，写入从库。 相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，它需要等待一个 从库写入中继日志，这里多了一个网络交互的过程，所以，半同步复制最好在低延时的网络中使用。这个是从主库和从库连接的角度，来保证从库 数据的写入。
2.2.4 多库并行复制
大家都知道MySQL复制包括两部分，IO线程 和 SQL线程。 IO线程主要是用于拉取接收Master传递过来的binlog，并将其写入到relay log。 SQL线程主要负责解析relay log，并应用到slave中 其中IO和SQL线程都是单线程的，然后master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。
3 优化器
优化器的作用就是对我们的 SQL 语句进行优化分析，生成执行计划。以前我们做项目时有时会收到DBA的邮箱通知，里面会列出我们项目中耗时比较长的查询语句。
3.1 慢日志
详细了解慢查询日志的可以查看官网https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html，慢日志默认是关闭的，因为开启慢查询日志是有代价的，用下面命令开启慢日志，第一行代表开启，第二行表示慢日志文件位置
在MYSQL中他默认查询时间超过10S就是慢日志,当然这个慢查询时间是可以设置的，如果想设置成2S就用set @@global.long_query_time=2;
我们重新设置完了慢日志时间后我们来创建一个慢日志来看下他内部文件长啥样
从慢日志文件我们可以看到我们执行的时间，获取的行数，还有从多少行数据中进行查询 ，在慢日志文件中都有
但这种修改在重启后会失效，要想重启不失效，我们要修改my.cnf配置文件，以下是配置定义慢查询日志的开关、查询时间用及存放日志位置的配置
slow_query_log=ON long_query_time=2 show_query_log_file=/var/lib/mysql/localhost-slow.log
3.2 mysqldumpslow
前面查询慢日志方法过于费事，在分析慢数据时不方便，为此MySQL提供了分析工具mysqldumpslow官网地址https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html，要使用工具那我们要在MySQL 的 bin 目录下执行
3.3 SHOW PROFILE
官网上还有监测SQL语句执行时CPU、IO的消耗方案，官网https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
在使用前我们先要开启SHOW PROFILE
然后我的执行一个慢查询，执行完成后用show profiles；查看结果
用show profile查看最后一个SQL 的执行详细信息，从下图我们可以分析出是哪一个环节的需要时间最长，从而我们可以进行针对 情的优化
3.2.3 其它系统命令
show processlist 运行线程https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html
我们通过下面命令查询到用户运行的线程，我们可以根据对应的ID干掉对应的线程，例如我们要干掉ID是152的线程那么我们可以运行 kill 152;
浙公网安备 33010602011771号