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

一、MySQL通讯优化

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

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

可通过命令查看当前mysql 的状态，其中：
Threads_cached— 服务器端缓存连接；
Threads_connected —当前打开的连接数
Threads_created —创建的线程数
Threads_running—正在运行的线程

通过命令查询当前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值过小。

通过查看当前Mysql允许最大的连接数。默认为100，对于并发量大的应用可能远远不够，可通过来修改连接数。

Mysql sql语句的接受和结果的返回都是优大小限制的，通过来查看大小，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. 返回数据给客户端；
   

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

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

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

   1. 预读失效
      缓冲池的预读机制可能会预先加载相邻的数据页。假如查询5，根据预读机制可能也会将6一并取出，如果只有5的缓存页被访问了，而另一个缓存页却没有被访问。此时两个缓存页都放在了在链表的头部，但是为了加载这两个缓存页却淘汰了末尾的缓存页，而被淘汰的缓存页却是经常被访问的。这种情况就是预读失效，被预先加载进缓冲池的页，并没有被访问到，这种情况是不是很不合理。
   2. 缓冲池污染
      如果执行了一条 SQL 语句时，扫描出了大量数据或是进行了全表扫描，此时缓冲池中就会加载大量的数据页，从而将缓冲池中已存在的经常访问的热数据替换出去，这种情况同样是不合理的。这就是缓冲池污染，并且还会导致 MySQL 性能急剧下降。

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

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

   
   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服务器本身有的；

   博客书写分享不易，转载请注明版权。

   来源：小毕超

   声明：本站部分文章及图片转载于互联网，内容版权归原作者所有，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！