MySQL性能调优（三）：MySQL中的体系库（mysql体系库）
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，进步查询性能。
MySQL中的体系库
1.5.Mysql中mysql体系库
1.5.1.权限体系表
因为权限管理是DBA的职责，所以对于这个部门的表，我们大概相识下即可。在mysql体系库中，MySQL访问权限体系表，放在mysql库中，主要包含如下几个表。
https://i-blog.csdnimg.cn/img_convert/48f74371749d1caa1d4d24e5424c83f4.png
• user：包含用户账户、全局权限和其他非权限列表（安全配置字段和资源控制字段）。
• db：数据库级别的权限表。该表中记录的权限信息代表用户是否可以利用这些权限来访问被授予访问的数据库下的所有对象（表或存储程序）。
• tables_priv：表级别的权限表。
• columns_priv：字段级别的权限表。
• procs_priv：存储过程和函数权限表。
• proxies_priv：署理用户权限表。
提示：
要更改权限表的内容，应该利用账号管理语句（如： CREATE USER 、 GRANT 、 REVOKE等）来间接修改，不发起直接利用DML语句修改权限表。
(grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。dml语句直接修改权限表只是修改了表中权限信息，必要执行flush privileges;来更新内存中保存用户权限的相关对象)
1.5.2.统计信息表
长期化统计功能是通过将内存中的统计数据存储到磁盘中，使其在数据库重启时可以快速重新读入这些统计信息而不消重新执行统计，从而使得查询优化器可以利用这些长期化的统计信息正确地选择执行筹划（假如没有这些长期化的统计信息，那么数据库重启之后内存中的统计信息将会丢失，下一次访问到某库某表时，必要重新盘算统计信息，而且重新盘算可能会因为估算值的差异导致查询筹划发生变更，从而导致查询性能发生变化）。
如何启用统计信息的长期化功能呢？当innodb_stats_persistent = ON时全局的开启统计信息的长期化功能，默认是开启的，
show variables like 'innodb_stats_persistent';
假如要单独关闭某个表的长期化统计功能，则可以通过ALTER TABLE tbl_name STATS_PERSISTENT = 0语句来修改。
1.5.2.1.innodb_table_stats
innodb_table_stats表提供查询与表数据相关的统计信息。
select * from innodb_table_stats where table_name = 'order_exp'\G
https://i-blog.csdnimg.cn/img_convert/a6b2e314bc8086f57cd60f29561ff98a.png
database_name：数据库名称。
• table_name：表名、分区名或子分区名。
• last_update：表示InnoDB上次更新统计信息行的时间。
• n_rows：表中的估算数据记录行数。
• clustered_index_size：主键索引的巨细，以页为单位的估算数值。
• sum_of_other_index_sizes：其他（非主键）索引的总巨细，以页为单位的估算数值。
1.5.2.2.innodb_index_stats
innodb_index_stats表提供查询与索引相关的统计信息。
select * from innodb_index_stats where table_name = 'order_exp';
https://i-blog.csdnimg.cn/img_convert/6b82966af0341b632cf43bed89f238e5.png
表字段寄义如下。
• database_name：数据库名称。
• table_name：表名、分区表名、子分区表名。
• index_name：索引名称。
• last_update：表示InnoDB上次更新统计信息行的时间。
• stat_name：统计信息名称，其对应的统计信息值保存在stat_value字段中。
• stat_value：保存统计信息名称stat_name字段对应的统计信息值。
• sample_size：stat_value字段中提供的统计信息估计值的采样页数。
• stat_description：统计信息名称stat_name字段中指定的统计信息的说明。
从表的查询数据中可以看到：
• stat_name字段一共有如下几个统计值。
■ size：当stat_name字段为size值时，stat_value字段值表示索引中的总页数目。
■ n_leaf_pages：当stat_name字段为n_leaf_pages值时，stat_value字段值表示索引叶子页的数目。
■ n_diff_pfxNN：NN代表数字（例如01、02等）。当stat_name字段为n_diff_pfxNN值时，stat_value字段值表示索引的first column（即索引的最前索引列，从索引定义顺序的第一个列开始）列的唯一值数目。例如：当NN为01时，stat_value字段值就表示索引的第一个列的唯一值数目；当NN为02时，stat_value字段值就表示索引的第一个和第二个列组合的唯一值数目，依此类推。此外，在stat_name = n_diff_pfxNN的环境下，stat_description字段显示一个以逗号分隔的盘算索引统计信息字段的列表。
• 从index_name字段值为PRIMARY数据行的stat_description字段的描述信息“id”中可以看出，主键索引的统计信息只包罗创建主键索引时显式指定的列。
• 从index_name字段值为u_idx_day_status数据行的stat_description字段的描述信息“insert_time,order_status,expire_time”中可以看出，唯一索引的统计信息只包罗创建唯一索引时显式指定的列。
• 从index_name字段值为idx_order_no数据行的stat_description字段的描述信息“order_no,id”中可以看出，平凡索引（非唯一的辅助索引）的统计信息包罗了显式定义的列和主键列。
注意，上述的描述中出现的诸如叶子页，索引的最前索引列等等，这些东西在索引章节有解说，这里不再阐述。
1.5.3.日记记录表
MySQL的日记体系包含：平凡查询日记、慢查询日记、错误日记（记录服务器启动时、运行中、停止时的错误信息）、二进制日记（记录服务器运行过程中数据变更的逻辑日记）、中继日记（记录从库I/O线程从主库获取的主库数据变更日记）、DDL日记（记录DDL语句执行时的元数据变更信息。在MySQL 5.7中只支持写入文件中，在MySQL 8.0中支持写入innodb_ddl_log表中。在MySQL5.7中，只有平凡查询日记、慢查询日记支持写入表中（也支持写入文件中）,可以通过log_output=TABLE设置保存到mysql.general_log表和mysql.slow_log表中，其他日记范例在MySQL 5.7中只支持写入文件中。
1.5.3.1. general_log
general_log表提供查询平凡SQL语句的执行记录信息，用于查看客户端到底在服务器上执行了什么SQL语句。
缺省不开启
show variables like 'general_log';
https://i-blog.csdnimg.cn/img_convert/5922927b677e90c55a347eb11cff6e88.png
开启
set global log_output='TABLE'; -- 'TABLE,FILE'表示同时输出到表和文件
set global general_log=on;
show variables like 'general_log';
https://i-blog.csdnimg.cn/img_convert/bb111c7992e24d06df69eb36924023d8.png
恣意执行一个查询后
https://i-blog.csdnimg.cn/img_convert/056c4421442b863c924fa119792d2abc.png
select * from mysql.general_log\G
https://i-blog.csdnimg.cn/img_convert/fa9ee02ba715080faf420e76b49b5d95.png
https://i-blog.csdnimg.cn/img_convert/63f74f3a722f4132bb8968353adcd570.png
1.5.3.2. slow_log
slow_log表提供查询执行时间凌驾long_query_time设置值的SQL语句、未利用索引的语句（必要开启参数log_queries_not_using_indexes=ON）或者管理语句（必要开启参数log_slow_admin_statements=ON）。
show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';
https://i-blog.csdnimg.cn/img_convert/81b4414e0f7003990236644c724ec24f.png
https://i-blog.csdnimg.cn/img_convert/4a8a27059261f6ad9bdd7d479e6ab504.png
开启
set global log_queries_not_using_indexes=on;set global log_slow_admin_statements=on;show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';
https://i-blog.csdnimg.cn/img_convert/ad9981f1df15a1825aa4f6dea5580427.png
我们已经知道慢查询日记可以帮助定位可能存在题目的SQL语句，从而举行SQL语句层面的优化。但是默认值为关闭的，必要我们手动开启。
show VARIABLES like 'slow_query_log';
https://i-blog.csdnimg.cn/img_convert/60593e8358f17ad529a086164aafae8c.png
set GLOBAL slow_query_log=1;
开启1，关闭0
但是多慢算慢？MySQL中可以设定一个阈值，将运行时间凌驾该值的所有SQL语句都记录到慢查询日记中。long_query_time参数就是这个阈值。默认值为10，代表10秒。
show VARIABLES like '%long_query_time%';
当然也可以设置
set global long_query_time=0;
默认10秒，这里为了演示方便设置为0
https://i-blog.csdnimg.cn/img_convert/fb9932447252d0be5c9a80732a617d76.png
然后我们测试一把，恣意写一个SQL
https://i-blog.csdnimg.cn/img_convert/33170994d616ca371236f3d867e98e0e.png
select * from mysql.slow_log\G
https://i-blog.csdnimg.cn/img_convert/c9c04bb458dfdd5a4779c152234c8855.png
1.5.4.InnoDB中的统计数据
我们前边絮聒查询成本的时候常常用到一些统计数据，好比通过SHOW TABLE STATUS可以看到关于表的统计数据，通过SHOW INDEX可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？
1.5.4.1 统计数据存储方式
InnoDB提供了两种存储统计数据的方式：
永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被扫除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。
MySQL给我们提供了体系变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。
SHOW VARIABLES LIKE 'innodb_stats_persistent';
https://i-blog.csdnimg.cn/img_convert/8c8fcd439ebbb2f1a90da39f8c955698.png
不过最近的MySQL版本都基本不消基于内存的非永久性统计数据了，所以我们也就不深入研究。
不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (…)
Engine=InnoDB, STATS_PERSISTENT = (1|0);
ALTER TABLE 表名
Engine=InnoDB, STATS_PERSISTENT = (1|0);
当STATS_PERSISTENT=1时，表明我们想把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明我们想把该表的统计数据临时的存储到内存中。假如我们在创建表时未指定STATS_PERSISTENT属性，那默认采用体系变量innodb_stats_persistent的值作为该属性的值。
1.5.4.2 基于磁盘的永久性统计数据
当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：
SHOW TABLES FROM mysql LIKE 'innodb%';
https://i-blog.csdnimg.cn/img_convert/87a189b6620e550a9296524e10ef7e3c.png
可以看到，这两个表都位于mysql体系数据库下边，此中：
innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。
innodb_table_stats
直接看一下这个innodb_table_stats表中的各个列都是干嘛的：
https://i-blog.csdnimg.cn/img_convert/4caf38f39108557aeca1b091873a90d9.png
database_name 数据库名
table_name 表名
last_update 本条记录末了更新时间
n_rows表中记录的条数
clustered_index_size 表的聚簇索引占用的页面数目
sum_of_other_index_sizes 表的其他索引占用的页面数目
我们直接看一下这个表里的内容：
SELECT * FROM mysql.innodb_table_stats;
https://i-blog.csdnimg.cn/img_convert/d2f653161601aeb7df2c125baf45b713.png
几个紧张统计信息项的值如下：
n_rows的值是10350，表明order_exp表中大约有10350条记录，注意这个数据是估计值。
clustered_index_size的值是97，表明order_exp表的聚簇索引占用97个页面，这个值是也是一个估计值。
sum_of_other_index_sizes的值是81，表明order_exp表的其他索引一共占用81个页面，这个值是也是一个估计值。
n_rows统计项的收集
InnoDB统计一个表中有多少行记录是如许的：
按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，盘算每个页面中主键值记录数目，然后盘算平均一个页面中主键值的记录数目乘以全部叶子节点的数目就算是该表的n_rows值。
可以看出来这个n_rows值精确与否取决于统计时采样的页面数目，MySQL用名为innodb_stats_persistent_sample_pages的体系变量来控制利用永久性的统计数据时，盘算统计数据时采样的页面数目。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。所以在实际利用是必要我们去权衡利弊，该体系变量的默认值是20。
InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数目，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (…)
Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数目;
ALTER TABLE 表名
Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数目;
假如我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES属性的话，将默认利用体系变量innodb_stats_persistent_sample_pages的值作为该属性的值。
clustered_index_size和sum_of_other_index_sizes统计项的收集牵涉到很具体的InnoDB表空间的知识和存储页面数据的细节，我们就不深入解说了。
innodb_index_stats
直接看一下这个innodb_index_stats表中的各个列都是干嘛的：
desc mysql.innodb_index_stats;
字段名描述
database_name 数据库名
table_name 表名
index_name 索引名
last_update 本条记录末了更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值
sample_size 为生成统计数据而采样的页面数目
stat_description 对应的统计项的描述
innodb_index_stats表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于order_exp表的索引统计数据都有些什么：
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'order_exp';
https://i-blog.csdnimg.cn/img_convert/44045122e3bb427be8ab550bb2e3ead7.png
先查看index_name列，这个列说明该记录是哪个索引的统计信息，从效果中我们可以看出来，PRIMARY索引（也就是主键）占了3条记录，idx_expire_time索引占了6条记录。
针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的寄义的。我们来具体看一下一个索引都有哪些统计项：
n_leaf_pages：表示该索引的叶子节点占用多少页面。
size：表示该索引共占用多少页面。
n_diff_pfxNN：表示对应的索引列不重复的值有多少。此中的NN长得有点儿怪呀，啥意思呢？
其实NN可以被更换为01、02、03… 如许的数字。好比对于u_idx_day_status来说：
n_diff_pfx01表示的是统计insert_time这单单一个列不重复的值有多少。
n_diff_pfx02表示的是统计insert_time,order_status这两个列组合起来不重复的值有多少。
n_diff_pfx03表示的是统计insert_time,order_status,expire_time这三个列组合起来不重复的值有多少。
n_diff_pfx04表示的是统计key_pare1、key_pare2、expire_time、id这四个列组合起来不重复的值有多少。
对于平凡的二级索引，并不能包管它的索引列值是唯一的，好比对于idx_order_no来说，key1列就可能有许多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。
对于主键和唯一二级索引则没有这个题目，它们本身就可以包管索引列值的不重复，所以也不必要再统计一遍在索引列后加上主键值的不重复值有多少。好比u_idx_day_statu和idx_order_no。
在盘算某些索引列中包含多少不重复值时，必要对一些叶子节点页面举行采样，sample_size列就表明了采样的页面数目是多少。
对于有多个列的联合索引来说，采样的页面数目是：innodb_stats_persistent_sample_pages × 索引列的个数。
https://i-blog.csdnimg.cn/img_convert/123e0077f1d4c570ebf94989b55ff28e.png
当必要采样的页面数目大于该索引的叶子节点数目的话，就直接采用全表扫描来统计索引列的不重复值数目了。所以大家可以在查询效果中看到不同索引对应的size列的值可能是不同的。
定期更新统计数据
随着我们不断的对表举行增删改利用，表中的数据也一直在变化，innodb_table_stats和innodb_index_stats表里的统计数据也在变化。MySQL提供了如下两种更新统计数据的方式：
开启innodb_stats_auto_recalc。
体系变量innodb_stats_auto_recalc决定着服务器是否自动重新盘算统计数据，它的默认值是ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表举行增删改的记录条数，假如发生变更的记录数目凌驾了表巨细的10%，而且自动重新盘算统计数据的功能是打开的，那么服务器会重新举行一次统计数据的盘算，而且更新innodb_table_stats和innodb_index_stats表。不过自动重新盘算统计数据的过程是异步发生的，也就是纵然表中变更的记录数凌驾了10%，自动重新盘算统计数据也不会立刻发生，可能会耽误几秒才会举行盘算。
再一次夸大，InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独为某个表设置是否自动重新盘算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (…)
Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名
Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
当STATS_AUTO_RECALC=1时，表明我们想让该表自动重新盘算统计数据，当STATS_AUTO_RECALC=0时，表明不想让该表自动重新盘算统计数据。假如我们在创建表时未指定STATS_AUTO_RECALC属性，那默认采用体系变量innodb_stats_auto_recalc的值作为该属性的值。
手动调用ANALYZE TABLE语句来更新统计信息
假如innodb_stats_auto_recalc体系变量的值为OFF的话，我们也可以手动调用ANALYZE
TABLE语句来重新盘算统计数据，好比我们可以如许更新关于order_exp表的统计数据：
ANALYZE TABLE order_exp;
https://i-blog.csdnimg.cn/img_convert/01808e15f40c59f3136bfb52c13710fc.png
ANALYZE TABLE语句会立刻重新盘算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙的时候再运行。
手动更新innodb_table_stats和innodb_index_stats表
其实innodb_table_stats和innodb_index_stats表就相当于一个平凡的表一样，我们能对它们做增删改查利用。这也就意味着我们可以手动更新某个表或者索引的统计数据。好比说我们想把order_exp表关于行数的统计数据更改一下可以这么做：
步骤一：更新innodb_table_stats表。
步骤二：让MySQL查询优化器重新加载我们更改过的数据。
更新完innodb_table_stats只是单纯的修改了一个表的数据，必要让MySQL查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：
FLUSH TABLE order_exp;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]