Mysql底层优化（通讯、缓存池，引擎，分支）

一、MySQL通讯优化

mysql通信协议采用tcp/ip半双工机制的长连接，数据双向传输，但不能同时传输。mysql 的长连接等待超时时间默认为8小时，就是8小时内如果没有做任何sql的操作，MySQL将自动断开该连接，可通过show global variables like 'wait_timeout';命令查看超时时间，28800秒即8小时。

在项目中我们尽量使用数据库连接池内的连接，使得它们不会因为闲置超时而被 MySQL 断开，并且每次使用连接前检查连接是否可用，定期回收空闲的连接。

可通过show global status like 'Thread%';命令查看当前mysql 的状态，其中：

Threads_cached— 服务器端缓存连接；

Threads_connected —当前打开的连接数

Threads_created —创建的线程数

Threads_running—正在运行的线程

通过show PROCESSLIST;命令查询当前mysql服务器接收所有的连接信息，其中：

1. sleep：线程正在等待客户端发送新的请求；

2. query：线程正在执行查询或者正在将结果发送给客户端；

3. locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。

4. analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；

5. copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。

6. sorting Result：线程正在对结果集进行排序。

7. sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

在项目中优可能会遇到MySQL: ERROR 1040: Too many connections”的异常情况，造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。

通过show variables like '%max_connections%';查看当前Mysql允许最大的连接数。默认为100，对于并发量大的应用可能远远不够，可通过set GLOBAL max_connections=800来修改连接数。

Mysql sql语句的接受和结果的返回都是优大小限制的，通过show variables like '%max_allowed_packet%';来查看大小，max_allowed_packet用于设定所接受的包的大小，根据情形不同，其值大小可能是1M或者4M，比如是4M的情况下，这个值的大小即为：4 10241024= 4194304 max_allowed_packet 最大值是1G(1073741824)，如果设置超过1G，查看最终生效结果也只有1G。

二、MySQL缓存池

在看缓存池之前先看下mysql 的执行流程：

1. 先走mysql自带的缓存机制 注意：mysql8开始已经去除缓存机制；

2. 词法和语法解析，先通过词法解析，从左到右将整个sql语句拆分n多个单词，根据规则识别单词，最终分成关键字和非关键字；在做语法解析的时候，判断sql语句是否满足规则，最终会生成一个语法树。

3. 处理器 使用处理器检查表名和列名是否名称正确

4. 执行我们优化器的策略

5. 查询我们对应的执行引擎；数据存放在什么结构：存储引擎

6. 返回数据给客户端；

从上面图中可以看到在优化器执行之后会走到缓存池，在innodb中我们的数据是存放在硬盘中，为了能够减少磁盘io操作，在innodb中引入缓存池技术，会将从磁盘中读取的数据（page页）放入到缓冲池中。如果客户端查询数据（page页） 如果在缓存池的存在的情况下，可以不需要查询磁盘io操作，从而提高效率。可以通过show variables like '%innodb_buffer_pool%'来查看当前缓存池的大小，默认大小是128M，可根据需求适当增大。

缓冲池采用链表结构存放，如果缓存池内存满的情况下采用类似Redis缓存淘汰算法Lru清理最近少使用的page页。mysql 一次读取page页的大小为16k。如果查询的数据在缓存链表中，则mysql将访问的缓存页重新放在链表的头部，如果查询的数据不在缓存中，则会进行磁盘操作找到需要的数据，放在缓存链表的头部，如果空间不够会淘汰掉末尾的缓存页。

当发出修改语句操作的时候，首先修改缓冲池中内容的数据，后台会开启n多个线程，将缓存池中的数据写入到磁盘中。但是缓存池将数据刷新到硬盘中时，正好断电了，可以采用redo log日志下次启动的时候实现数据的恢复。其中redo log日志采用顺序iｏ写入磁盘，效率要比随机ＩＯ高。

三、缓存池的 老生代和新生代 比例优化

在谈老生代和新生代时，先谈两个问题：

1. 预读失效

   缓冲池的预读机制可能会预先加载相邻的数据页。假如查询5，根据预读机制可能也会将6一并取出，如果只有5的缓存页被访问了，而另一个缓存页却没有被访问。此时两个缓存页都放在了在链表的头部，但是为了加载这两个缓存页却淘汰了末尾的缓存页，而被淘汰的缓存页却是经常被访问的。这种情况就是预读失效，被预先加载进缓冲池的页，并没有被访问到，这种情况是不是很不合理。

2. 缓冲池污染

   如果执行了一条 SQL 语句时，扫描出了大量数据或是进行了全表扫描，此时缓冲池中就会加载大量的数据页，从而将缓冲池中已存在的经常访问的热数据替换出去，这种情况同样是不合理的。这就是缓冲池污染，并且还会导致 MySQL 性能急剧下降。

mysql根据以上问题，将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，只有被访问的页，并且在老生代停留时间超过配置阈值的后，方可进入新生代，以解决批量数据访问，大量热数据淘汰的问题和预读失效的问题。

使用show variables like 'innodb_old_blocks_time'可查看老生代进入新生代阈值，默认时间为1秒钟，也就是当前查询一缓存页数据，会首先在老生代中存放，如果后续再次查询，并且在老生代的时间也超过了1秒的时间，该缓存页则会升级到新生代中存放。这点和jvm的新生代和老年代相反了。

使用show variables like "%innodb_old_blocks_pct%";可以查看老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37，如果项目中查询不同的数据比较频繁可以适当增大老生代的比例，比如修改为50:50 1:1的形式：

set global innodb_old_blocks_pct = 50;

四、缓存池的大小和分区数优化

首先先了解下三个参数

show variables like "innodb_buffer_pool_size";
show variables like "innodb_buffer_pool_chunk_size";
show variables like "innodb_buffer_pool_instances";

其中innodb_buffer_pool_size为缓冲池大小，innodb_buffer_pool_chunk_size定义InnoDB缓冲池大小调整操作的块大小，innodb_buffer_pool_instances为InnoDB 缓冲池划分为的区域数。

官方对上面参数的解释为：

就是innodb_buffer_pool_size必须始终等于innodb_buffer_pool_chunk_size或 innodb_buffer_pool_instances的倍数。如果将缓冲池大小更改为不等于innodb_buffer_pool_chunk_size 或 innodb_buffer_pool_instances的倍数，则缓冲池大小将自动调整为等于innodb_buffer_pool_chunk_size 或 innodb_buffer_pool_instances的倍数。

其中innodb_buffer_pool_instances的主要作用是为了，减少不同线程对缓存页面争用，将缓冲池划分为多个单独的实例从而提高并发性。使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的LRU列表并连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。

在设置innodb_buffer_pool_instances参数的时候，需要innodb_buffer_pool_size大于1G 的时候才会生效，而innodb_buffer_pool_size默认大小为128M，所以innodb_buffer_pool_instances的个数也就默认使用了1个。innodb_buffer_pool_instances最大为64，为了获得最佳效率，指定适当的组合 使得每个缓冲池实例是至少为1GB大小，最好根据实际使用情况变化测试innodb_buffer_pool_instances的个数，选取最佳值。而innodb_buffer_pool_size缓冲池的大小可以设为当前服务器内存的60%-70%大小。

set global innodb_buffer_pool_size=2073741824;
set global innodb_buffer_pool_instances=2;

五、Mysql innodb和myisam引擎选择

1. myisam存储引擎（MySQL8.0已被移除）

Mysql5.5版本之前的默认存储引擎 较多的系统表也还是使用这个存储引擎 系统临时表也会用到Myisam存储引擎，特点：select count(*) from table 无需进行数据的扫描，他有一个专门计算数据的函数，而InnoDB需要一行行的扫描，计算出来。myisam含有表级锁，不支持事物操作。

myisam数据和索引分别存储，不管用哪个存储引擎，都会生成一个.frm文件（表定义文件），数据保存在myd文件，索引保存在myi文件里面。在myisam里面，叶子节点的数据区保存的是.myd的内存地址，在.myi通过索引找到这条数据的内存地址，再通过这个地址去.myd里面找到对应的数据。

2. InnoDB

Mysql5.5及以后版本的默认存储引擎，包含事务ACID、行级锁、聚集索引（主键索引）方式进行数据存储、支持外键关系保证数据完整性（不常用）。

InnoDB只有.ibd和.frm两个文件，他的index和数据放在了一起，在InnoDB中，以主键为索引来组织数据的存储，如果没有明确指定一个主键（ID）索引，他会默认的生成一个隐藏的6byte的Int型的索引来作为他的主键索引，只是这个隐式的索引看不到而已。

注意：MySQL每个版本都是略微差异，比如在MySQL5.7之前都有.frm文件，而在mysql8.0之后就将该文件移除掉了。

目前InnoDB就可以完成99%的场景，并且在mysql8.0已经移除了myisam引擎，所以InnoDB应当是我们项目的不二之选。

六、Mysql 分支选择

相信大多数人使用的应该是官方推出的mysql 分支版本。mysql毕竟是开源项目，肯定是有不错的分支。

1. XtraDB

   XtraDB是一款独立的产品，但它仍被认为是MySQL的一个分支。XtraDB实际上是基于MySQL的数据库的一个存储引擎。XtraDB被认为是已成为MySQL一部分的标准MyISAM和InnoDB的一个额外存储引擎。MySQL 4和5使用默认的MyISAM存储引擎安装每个表。InnoDB也是一个相对较新的存储引擎选择，在建立数据库时，数据库管理员和开发人员可以基于每个表选择存储引擎类型。两个存储引擎的主要区别是：MyISAM没有提供事务支持，而InnoDB提供了事务支持。其他差别是许多细微的性能差别，与MyISAM相比，InnoDB提供了许多细微的性能改进，并且在处理潜在的数据丢失时提供了更高的可靠性和安全性。似乎InnoDB是用于未来改进的更适合的存储引擎，因此从版本5.5开始，MySQL已将默认存储引擎从MyISAM更改为InnoDB。

   基于这些优势，InnoDB存储引擎本身拆分出了一个分支，一个名为XtraDB的更新的存储引擎。这个存储引擎有多新呢？它3年前由Percona首次发布，因此它相对较新。它是专门针对在现代服务器上运行的现代高可用性网站设计的。它被设计为在具有十几个或更多核心和大内存（32GB及更多）的服务器上运行。任何公司都可以从服务器管理公司购买这些类型的服务器，因此应将数据库设计为能够充分利用这些服务器。

   XtraDB分支有另一个目标，即成为InnoDB存储引擎的简单替代，这样用户就可以轻松地切换其存储引擎，无需更改任何现有的应用程序代码。XtraDB必须能够向后兼容InnoDB，以提供它们想要添加的所有新功能和改进。它们实现了此目标。

   XtraDB的速度有多快？我找到的一个性能测试表明：与内置的MySQL 5.1 InnoDB 引擎相比，它每分钟可处理2.7倍的事务。（请参见参考资料）。速度显然是一个不可以忽略的因素，在考虑替代产品时更是如此。

2. Percona

   Percona是一个相对比较成熟的、优秀的MySQL分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方ORACLE MySQL版本基本完全兼容，并且性能大约有20%以上的提升，由领先的MySQL咨询公司Percona发布。Percona Server是一款独立的数据库产品，为用户提供了换出其MySQL安装并换入Percona Server产品的能力。通过这样做，就可以利用XtraDB存储引擎。Percona Server声称可以完全与MySQL兼容，因此从理论上讲，您无需更改软件中的任何代码。这确实是一个很大的优势，适合在您寻找快速性能改进时控制质量。因此，采用Percona Server的一个很好的理由是，利用XtraDB引擎来尽可能地减少代码更改。

   此外，他们是XtraDB存储引擎的原作者。Percona将此代码用作开源代码，因此您可以在其他产品中找到它，但引擎的最初创建者与编写此产品的是同一个人，所以您可以随心所欲地使用此信息。

3. MariaDB

   另一款提供了XtraDB存储引擎的产品是MariaDB产品。它与Percona产品非常类似，但是提供了更多底层代码更改，试图提供比标准MySQL更多的性能改进。MariaDB直接利用来自Percona的XtraDB引擎，由于它们使用的是完全相同的引擎，因此每次使用存储引擎时没有显著的差别。

   此外，MariaDB提供了MySQL提供的标准存储引擎，即MyISAM和InnoDB。因此，实际上，可以将它视为MySQL的扩展集，它不仅提供MySQL提供的所有功能，还提供其他功能。MariaDB还声称自己是MySQL的替代，因此从MySQL切换到MariaDB时，无需更改任何基本代码即可安装它。

   最后可能也是最重要的一点是，MariaDB的主要创建者是Monty Widenius，也是MySQL的初始创建者。Monty成立了一家名为Monty Program的公司来管理MariaDB的开发，这家公司雇佣开发人员来编写和改进MariaDB产品。这既是一件好事，也是一件坏事：有利的一面在于他们是Maria功能和bug修复的佼佼者，但公司不是以赢利为目的，而是由产品驱动的，这可能会带来问题，因为没有赢利的公司不一定能长久维持下去。

补充

上面提到redo log文件是作为数据恢复的，mysql 还有Undolog和binlog文件，他们之间的区别为：

1. Redolog日志：记录我们数据页修改的日志，方便后期mysql崩溃的时候实现数据的恢复。

2. undoLog日志：记录事务回滚操作

3. Binlog日志：mysql服务器端自带的增量同步日志 主从复制 集群 二进制

注意：Redolog/undoLog属于InnoDB自带的、Binlog属于MySQL服务器本身有的；