MySQL性能调优（三）：MySQL中的体系库（简介、performance_schema）
https://i-blog.csdnimg.cn/direct/e0e02699917a4cccb81a2c173d0b7289.png个人主页：道友老李
欢迎参加社区：道友老李的学习社区
MySQL性能调优
MySQL 性能调优是一个复杂且多维度的过程，下面从数据库筹划、查询优化、设置参数调整、硬件优化几个方面为你介绍相关的调优方法。
数据库筹划优化
[*]合理筹划表结构：确保表结构遵循数据库筹划范式，减少数据冗余，同时要根据现实业务需求灵活调整，避免过度范式化导致的查询复杂度过高。
[*]选择合适的数据类型：利用合适的数据类型可以减少存储空间，进步查询性能。例如，对于固定长度的字符串利用CHAR，对于可变长度的字符串利用VARCHAR；对于整数类型，根据取值范围选择合适的类型，如TINYINT、SMALLINT等。
[*]创建适当的索引：索引可以加速数据的查找速度，但过多的索引会增加写操纵的开销，因此需要根据查询需求创建适当的索引。例如，对于经常用于WHERE子句、JOIN条件和ORDER BY子句的列，可以思量创建索引。
查询优化
[*]避免全表扫描：尽量利用索引来避免全表扫描，例如在WHERE子句中利用索引列进行过滤。
[*]优化子查询：子查询可能会导致性能问题，可以思量利用JOIN来替代子查询。
[*]减少不必要的列：在查询时只选择需要的列，避免利用SELECT *。
设置参数调整
[*]调整内存分配：根据服务器的硬件资源和业务需求，调整innodb_buffer_pool_size、key_buffer_size等参数，以进步缓存掷中率。
[*]调整日记参数：根据业务需求调整log_bin、innodb_log_file_size等参数，以均衡数据安全性和性能。
硬件优化
[*]利用高速存储设备：如 SSD 可以显著进步磁盘 I/O 性能。
[*]增加内存：足够的内存可以减少磁盘 I/O，进步查询性能。
1.MySQL中的体系库
1.1.体系库简介
MySQL有几个体系数据库，这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。
https://i-blog.csdnimg.cn/img_convert/b4b3cf4eac677d7f9fbc5834dc780011.png
performance_schema
这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包罗统计最近执行了哪些语句，在执行过程的每个阶段都耗费了多长时间，内存的利用情况等等信息。
information_schema
这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些是一些描述性信息，称之为元数据。
sys
这个数据库通过视图的形式把information_schema和performance_schema联合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。
mysql
主要存储了MySQL的用户账户和权限信息，还有一些存储过程、变乱的界说信息，一些运行过程中产生的日记信息，一些资助信息以及时区信息等。
1.2.performance_schema
1.2.1.什么是performance_schema
MySQL的performance_schema 是运行在较低级别的用于监控MySQL Server运行过程中的资源消耗、资源期待等情况的一个功能特性，它具有以下特点。
**运行在较低级别：**采集的东西相对比较底层，比如磁盘文件、表I/O、表锁等等。
• performance_schema提供了一种在数据库运行时及时检查Server内部执行情况的方法。performance_schema 数据库中的表利用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关数据。
• performance_schema通过监视Server的变乱来实现监视其内部执行情况，“变乱”就是在Server内部运动中所做的任何事变以及对应的时间消耗，利用这些信息来判断Server中的相关资源被消耗在哪里。一样平常来说，变乱可以是函数调用、操纵体系的期待、SQL语句执行的阶段[如SQL语句执行过程中的parsing（解析）或sorting（排序）阶段]大概整个SQL语句的聚集。采集变乱可以方便地提供Server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。
• 当前活跃变乱、汗青变乱和变乱摘要相关表中记录的信息，能提供某个变乱的执行次数、利用时长，进而可用于分析与某个特定线程、特定对象（如mutex或file）相关联的运动。
• performance_schema存储引擎利用Server源代码中的“检测点”来实现变乱数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或变乱筹划程序）不同。
收集到的变乱数据被存储在performance_schema数据库的表中。对于这些表可以利用SELECT语句查询，也可以利用SQL语句更新performance_schema数据库中的表记录（比如动态修改performance_schema的以“setup_”开头的设置表，但要注意，设置表的更改会立即见效，这会影响数据收集）。
• performance_schema的表中数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据就会丢失（包罗设置表在内的整个performance_schema下的所有数据）。
1.2.2.performance_schema利用
通过上面介绍，信赖你对于什么是performance_schema这个问题了解得更清晰了。下面开始介绍performance_schema的利用。
1.2.3.检查当前数据库版本是否支持
performance_schema被视为存储引擎，假如该引擎可用，则应该在
INFORMATION_SCHEMA.ENGINES表或show engines语句的输出中可以看到它的Support字段值为YES，如下所示。
select * from INFORMATION_SCHEMA.ENGINES;
show engines;
https://i-blog.csdnimg.cn/img_convert/6a586ea2b4ff8af260e707e2f2c9c587.png
https://i-blog.csdnimg.cn/img_convert/b164236dbb4438eedc332318ca8ac095.png
当我们看到performance_schema对应的Support字段值为YES时，就表现当前的数据库版本是支持performance_schema的。但确认了数据库实例支持performance_schema存储引擎就可以利用了吗？NO，很遗憾，performance_schema在MySQL 5.6及之前的版本中默认没有启用，在MySQL 5.7及之后的版本中才修改为默认启用。
mysqld启动之后，通过如下语句查看performance_schema启用是否见效（值为ON表现performance_schema已初始化乐成且可以利用了；值为OFF表现在启用performance_schema时发生某些错误，可以查看错误日记进行排查）。
show variables like 'performance_schema';
https://i-blog.csdnimg.cn/img_convert/5cd002d63ca2d048e98f46d1ecbbbc97.png
（假如要显式启用或关闭 performance_schema ，则需要利用参数performance_schema=ON|OFF来设置，并在my.cnf中进行设置。注意 : 该参数为只读参数，需要在实例启动之前设置才见效）
现在，可以通过查询INFORMATION_SCHEMA.TABLES表中与performance_schema存储引擎相关的元数据，大概在performance_schema库下利用show tables语句来了解其存在哪些表。
利用show tables语句来查询有哪些performance_schema引擎表。
现在，我们知道了在当前版本中，performance_schema库下一共有87个表，
https://i-blog.csdnimg.cn/img_convert/bd87b305207f715ed4aeb8c7830dd480.png
https://i-blog.csdnimg.cn/img_convert/ed02b1881e508d00c58bd38ac9e3089e.png
那么这些表都用于存放什么数据呢？我们如何利用它们来查询数据呢？先来看看这些表是如何分类的。
1.2.4.performance_schema表的分类
performance_schema库下的表可以按照监视的不同维度进行分组，例如：按照不同的数据库对象进行分组、按照不同的变乱类型进行分组，大概按照变乱类型分组之后，再进一步按照账号、主机、程序、线程、用户等进行细分。
下面介绍按照变乱类型分组记录性能变乱数据的表。
• 语句变乱记录表：记录语句变乱信息的表，包罗：events_statements_current（当前语句变乱表）、events_statements_history（汗青语句变乱表）、events_statements_history_long（长语句汗青变乱表）以及一些summary表（聚合后的摘要表）。其中，summary表还可以根据账号（account）、主机（host）、程序（program）、线程（thread）、用户（user）和全局（global）再进行细分。
show tables like 'events_statement%';
https://i-blog.csdnimg.cn/img_convert/7a769f4b4a7c905ac19c95977edb4d05.pnghttps://i-blog.csdnimg.cn/img_convert/537750910ec6a8695a761d31c275cb49.png
• 期待变乱记录表：与语句变乱记录表雷同。
show tables like 'events_wait%';
https://i-blog.csdnimg.cn/img_convert/ee93367b2e1213df9fe53b4451a41544.png
• 阶段变乱记录表：记录语句执行阶段变乱的表，与语句变乱记录表雷同。
show tables like 'events_stage%';
https://i-blog.csdnimg.cn/img_convert/e051bdaa4947f6e8403a1764292c86ac.png
• 事务变乱记录表：记录与事务相关的变乱的表，与语句变乱记录表雷同。
show tables like 'events_transaction%';
https://i-blog.csdnimg.cn/img_convert/a2a8314c34450b8058283cc294c28700.png
• 监视文件体系层调用的表：
show tables like '%file%';
https://i-blog.csdnimg.cn/img_convert/f7328ac48c644ad0193970f863c123a2.png
• 监视内存利用的表：
show tables like '%memory%';
https://i-blog.csdnimg.cn/img_convert/58ec3bee703dd6405d3f805106103ab7.png
• 动态对performance_schema进行设置的设置表：
show tables like '%setup%';
https://i-blog.csdnimg.cn/img_convert/bf7f387554b4b24d8fd2e38bcecae98f.png
现在，我们已经大概知道了performance_schema中主要表的分类，但如何利用这些表来提供性能变乱数据呢？
1.2.5.performance_schema简朴设置与利用
当数据库初始化完成并启动时，并非所有的instruments（在采集设置项的设置表中，每一项都有一个开关字段，或为YES，或为NO）和consumers（与采集设置项雷同，也有一个对应的变乱类型保存表设置项，为YES表现对应的表保存性能数据，为NO表现对应的表不保存性能数据）都启用了，所以默认不会收集所有的变乱。
可能你想检测的变乱并没有打开，需要进行设置。可以利用如下两条语句打开对应的instruments和consumers，我们以设置监测期待变乱数据为例进行分析。
打开期待变乱的采集器设置项开关，需要修改setup_instruments 设置表中对应的采集器设置项。
update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';
https://i-blog.csdnimg.cn/img_convert/95c6a0fb35cfa035a8f0115258698b7f.png
打开期待变乱的保存表设置项开关，修改setup_consumers 设置表中对应的设置项。
update setup_consumers set enabled='yes' where name like 'wait%';
https://i-blog.csdnimg.cn/img_convert/6badd396a9d8bc2eb8d8d83847d39f4b.png
设置好之后，我们就可以查看Server当前正在做什么了。可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于表现每个线程的最新监视变乱（正在做的事变）。
_current表中每个线程只保存一条记录，且一旦线程完成工作，该表中就不会再记录该线程的变乱信息了。_history表中记录每个线程已经执行完成的变乱信息，但每个线程的变乱信息只记录10条，再多就会被覆盖掉。*_history_long表中记录所有线程的变乱信息，但总记录数量是10000行，凌驾会被覆盖掉。
summary表提供所有变乱的汇总信息。该组中的表以不同的方式汇总变乱数据（如：按用户、按主机、按线程等汇总）。
1.2.6.查看最近执行失败的SQL语句
利用代码对数据库的某些操纵（比如：利用Java的ORM框架操纵数据库）报出语法错误，但是代码并没有记录SQL语句文本的功能，在MySQL数据库层可否查看到具体的SQL语句文本，看看是否哪里写错了？这个时间，大多数人起首想到的就是去查看错误日记。很遗憾，对于SQL语句的语法错误，错误日记并不会记录。
现实上，在performance_schema的语句变乱记录表中针对每一条语句的执行状态都记录了较为详细的信息，例如：events_statements_表和events_statements_summary_by_digest表（events_statements_表记录了语句所有的执行错误信息，而events_statements_summary_by_digest表只记录了语句在执行过程中发生错误的语句记录统计信息，不记录具体的错误类型，例如：不记录语法错误类的信息）。下面看看如何利用这两个表查询语句发生错误的语句信息。
起首，我们模仿一条语法错误的SQL语句，利用events_statements_history_long表或events_statements_history表查询发生语法错误的SQL语句：
https://i-blog.csdnimg.cn/img_convert/63abc444969aeeaf66cf64d1ba92ce14.png
然后，查询events_statements_history表中错误号为1064的记录
select * from events_statements_history where mysql_errno=1064\G
https://i-blog.csdnimg.cn/img_convert/3ecfd35c06de740630f2786519889aa9.png
假如不知道错误号是多少，可以查询发生错误次数不为0的语句记录，在里边找到SQL_TEXT和MESSAGE_TEXT字段（提示信息为语法错误的就是它）。
1.2.7.查看最近的事务执行信息
我们可以通过慢查询日记查询到一条语句的执行总时长，但是假如数据库中存在着一些大事务在执行过程中回滚了，大概在执行过程中异常停止，这个时间慢查询日记就爱莫能助了，这时我们可以借助performance_schema的events_transactions_*表来查看与事务相关的记录，在这些表中详细记录了是否有事务被回滚、活跃（长时间未提交的事务也属于活跃事务）或已提交等信息。
起首需要进行设置启用，事务变乱默认并未启用
update setup_instruments set enabled='yes',timed='yes' where name like 'transaction%';
update setup_consumers set enabled='yes' where name like '%transaction%';
https://i-blog.csdnimg.cn/img_convert/e3e836ebd36eccfea94f08eb006ca546.png
现在我们开启一个新会话（会话2）用于执行事务，并模仿事务回滚。
https://i-blog.csdnimg.cn/img_convert/65d0eeaae9f6bb3aed6aacba6e9de74f.png
查询活跃事务，活跃事务表现当前正在执行的事务变乱，需要从events_transactions_current表中查询。
下图中可以看到有一条记录，代表当前活跃的事务变乱。
https://i-blog.csdnimg.cn/img_convert/39cc83514e06932068746e52785d3ced.png
会话2中回滚事务：
https://i-blog.csdnimg.cn/img_convert/9f591d4be017f0089d08df0ff1e8c8d8.png
查询事务变乱当前表（events_transactions_current）和事务变乱汗青记录表（events_transactions_history）
可以看到在两表中都记录了一行事务变乱信息，线程ID为30的线程执行了一个事务，事务状态为ROLLED BACK。
https://i-blog.csdnimg.cn/img_convert/552762d4e479911d29ebd6b52c469824.png
https://i-blog.csdnimg.cn/img_convert/9757e53af70857613de0c26592e10821.png
https://i-blog.csdnimg.cn/img_convert/2f9e5c6b93d8a109e12693a9f1598982.png
但是当我们关闭会话2以后，事务变乱当前表中（events_transactions_current）的记录就消失了。
https://i-blog.csdnimg.cn/img_convert/254f54b150b9a376917b95a299ff2a76.png
要查询的话需要去（events_transactions_history_long）表中查
https://i-blog.csdnimg.cn/img_convert/f86581bb7f2904ce78b23ea0780e2bf9.png
https://i-blog.csdnimg.cn/img_convert/3d98677c05b9d079c8d8258d7d411c87.png
1.2.8.小结
当然performance_schema的用途不止我们上面说到过的这些，它还能提供比如查看SQL语句执行阶段和进度信息、MySQL集群下复制功能查看复制报错详情等等。
具体可以参考官网：MySQL :: MySQL 5.7 Reference Manual :: 25 MySQL Performance Schema
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]