MySQL性能优化
优化思路
mysql性能优化,作为架构师或者开发人员,说到数据库性能优化,你的思路是什么样的? 或者具体一点,如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库,你会怎么回答? 我们在第一章开始的时候讲了,这章的目标是为了让大家建立数据库的知识体系,和正确的调优的思路. 我们说到性能调优,大部分时候想要实现的目标是让我们的查询更快.一个查询的动作又是由很多个环节组成的,每个环节都会消耗时间,我们在第一章讲 SQL 语句的执行流程的时候已经分析过了. 我们要减少查询所消耗的时间,就要从每一个环节入手.
在项目里面,方会开启事务,或者配置了事务?无论是在方法上加注解,还是配置切面.
第一个环节是客户端连接到服务端,连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接.比如报了一个 Mysql: error 1040: Too many connections 的错误. 我们可以从两个方面来解决连接数不够的问题： 1、从服务端来说,我们可以增加服务端的可用连接数. 如果有多个应用或者很多请求同时访问数据库,连接数不够的时候,我们可以： 修改配置参数增加可用连接数,修改 max_connections 的大小.
或者,或者及时释放不活动的连接.交互式和非交互式的客户端的默认超时时间都是 28800 秒,8 小时,我们可以把这个值调小.
从客户端来说,可以减少从服务端获取的连接数,如果我们想要不是每一次执行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 除了合理设置服务端的连接数和客户端的连接池大小之外,我们还有哪些减少客户端跟数据库服务端的连接数的方案呢? 我们可以引入缓存.
缓存
在应用系统的并发数非常大的情况下,如果没有缓存,会造成两个问题：一方面是会给数据库带来很大的压力.另一方面,从应用的层面来说,操作数据的速度也会受到影响.
我们可以用第三方的缓存服务来解决这个问题,例如 Redis.
运行独立的缓存服务,属于架构层面的优化. 为了减少单台数据库服务器的读写压力,在架构层面我们还可以做其他哪些优化措施?
主从复制
如果单台数据库服务满足不了访问需求,那我们可以做数据库的集群方案. 集群的话必然会面临一个问题,就是不同的节点之间数据一致性的问题.如果同时读写多台数据库节点,怎么让所有的节点数据保持一致? 这个时候我们需要用到复制技术(replication),被复制的节点称为 master,复制的节点称为 slave.slave 本身也可以作为其他节点的数据来源,这个叫做级联复制.
主从复制是怎么实现的呢?更新语句会记录 binlog,它是一种逻辑日志.
有了这个 binlog,从服务器会获取主服务器的 binlog 文件,然后解析里面的 SQL语句,在从服务器上面执行一遍,保持主从的数据一致.
这里面涉及到三个线程,连接到 master 获取 binlog,并且解析 binlog 写入中继日志,这个线程叫做 I/O 线程.
Master 节点上有一个 log dump 线程,是用来发送 binlog 给 slave 的.
从库的 SQL 线程,是用来读取 relay log,把数据写入到数据库的.
做了主从复制的方案之后,我们只把数据写入 master 节点,而读的请求可以分担到slave 节点.我们把这种方案叫做读写分离.
友情链接 : www.ccwisdom.com
文案参考 : www.mysql.com/cn/
