MySQL 系统库

一. 概念

MySQL含有几个系统库，这几个系统库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息。

⭐️information_schema

保存着MySQL服务器维护的所有其他数据库的信息，比如哪些表，哪些视图，哪些触发器，哪些列，哪些索引。这些是一些描述性信息，称之为元数据。

⭐️mysql

主要存储了MySQL的用户账户和权限信息，还有一些存储过程，事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

⭐️performance_schema

保存MySQL服务器运行过程中的一些状态信息，对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存使用情况等等信息。

⭐️sys

通过视图的形式把information_schema和performance_schema结合起来，更方便让我们了解MySQL服务器的一些性能信息。

二. performance_schema

1. 概念

MySQL的performance_schema是运行在较低级别的用于监控MySQL Server运行过程中资源消耗，资源等待等情况一个功能特性。默认情况下，performance_schema 不在文件系统上创建对应的文件或目录。不过，MySQL 允许通过配置选项来启用或禁用 performance_schema 的一些特性，包括是否将某些性能监控数据记录到文件系统上的文件中。

特点：

1. performance_schema提供了一种在数据库运行时实施检查Server内部执行情况方法。performance_schema数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关数据。
2. performance_schema通过监视Server的事件来实现监视其内部执行情况。事件是在Server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断Server中的相关资源被消耗在哪里。一般来说，事件可以是函数调用，操作系统的等待，SQL语句执行阶段（SQL语句执行过程中的parsing（解析）或sorting（排序）阶段），或者整个SQL语句集合。采集事件可以方便提供Server中的相关存储引擎对磁盘文件，表I/O，表锁等资源等同步调用信息。
3. 当前活跃事件，历史事件和事件摘要相关表中记录的信息，能提供某个事件的执行次数，使用时长，进而可用于分析与某个特定线程，特定对象（mutex或file）相关联的活动。
4. performance_schema存储引擎使用Server源代码中的检测点来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。收集到的事件数据被存储在performance_schema数据库表中。对于这些表可以使用select语句查询，也可使用SQL语句更新performance_schema数据库中的表哦记录（比如动态修改performance_schema的以setup_开头的配置表，配置表更改后会立即生效，这会影响数据收集）。
5. performance_schema的表中数据不会持久化存储磁盘中，而是保存在内存中，一旦服务器重启，这些数据就会丢失（包括配置表在内的整个performance_schema下所有数据）。而是由 MySQL 服务器在运行时动态生成和维护的。这意味着 performance_schema 中的数据确实是在内存中维护的，但并不意味着所有的数据都一直驻留在内存中。

2. 检查当前数据库版本是否支持

performance_schema被视为存储引擎，如果该引擎可用，则应该在information_schema.engines表或show engines语句的输出中可看到它的Support字段为yes。

select * from information_schema.engines;
show engines;

Support为yes时，表示当前数据库版本是支持performance_schema的，虽然数据库实例支持了performance_schema，并不代表着可以使用。performance_schema在MySQL5.6以及之前的版本中默认没有启用，在MySQL 5.7及以后版本是默认启用。

mysqld启动后，查看`performance_schema是否启用生效

on：初始化成功并且可以使用

off：启用performance_schema时发生某些错误，可查看错误日志进行排查

如果进行开启或关闭performance_schema，需要使用参数performance_schem=o n\off来设置，并在my.cnf进行配置，该参数为只读参数，需要在实例启动之前设置才生效。

现在，可通过查询information_schema.tables表中与performance_schema存储引擎相关的元数据，或performance_schema库下使用show tables语句了解存在哪些表。

当前版本下，performance_schema一共110个表

3. performance_schema表分类

可按照监视不同维度进行分组，例如：按照不同数据库对象进行分组，按照不同的事件类型进行分组，或按照事件类型分组之后，在进一步按照账号，主机，程序，线程，用户等进行细分。

时间类型分组记录性能事件数据等表：

1. 语句事件记录表：记录语句事件信息的表，包括：events_statements_current(当前语句事件表)，events_statements_history(历史语句事件表)，events_statements_history_long(长语句历史事件表)，以及一些summary表（聚合后的摘要表）。其中summary表还可根据账号（account），主机（host），程序（program），线程（thread），用户（user）和全局（global）进行再细分。

   show tables like 'events_statement%';
   

2. 等待事件记录表：与语句事件记录表类似。

   show tables like 'events_wait%';
   

   

3. 阶段事件记录表：记录语句执行阶段事件的表，与语句事件记录表类似。

   show tables like 'events_stage%';
   

4. 事务事件记录表：记录与事务相关的事件表，与语句事件记录表类似

   show tables like 'events_transaction';
   

   

5. 监视文件系统层调用的表

   show tables like '%file%';
   

   

6. 监视内存使用的表

   show tables like '%memory%';
   

   

7. 动态performance_schema进行配置的配置表

   show tables like '%setup%';
   

   

5. 配置与使用

数据库初始化完成并启动时，并非所有instruments（在采集配置项的配置表中，每一项都有一个开关字段，或为yes，或为no）和consumers（与采集配置项类似，也有一个对应事件类型保存表配置项，yes表示对应的表保存性能数据，no表示对应的表不保存性能数据）都启用，所以默认不会收集所有事件。

打开等待事件采集器配置项开关，等待事件保存表配置项开关。

update setup_instruments set enabled = 'yes', timed = 'yes' where name like 'wait%';

update setup_consumers set enabled = 'yes' where name like 'wait%';

配置好后，可查看Server当前正在做什么，可通过events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）。

_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中就不会在记录该线程的事件信息，history表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息之记录10条，再多会被覆盖掉，*history_long表中记录所有线程事件信息，但总记录数量事1w行，超过会被覆盖掉。summary表提供所有事件的汇总信息，该组中表以不同的方式汇总事件数据（如：按用户，主机，线程等汇总）。

6. 查看执行失败的SQL语句

使用代码对数据库等某些操作（Java的ORM操作）报出语法错误，但代码并没有记录SQL语句的文本功能，在MySQL数据库层能否查看到具体的SQL语句文本，看看是否哪里写错了。这个时候，大多数人去看错误日志，SQL语句的语法错误，错误日志不会记录。

实际上，在performance_schema的语句事件记录表中针对每一条语句的执行状态都记录了较为详细的信息，例如：

1. events_statements表：记录语句所有的执行错误信息
2. events_statements_summary_by_digest表：记录语句在执行过程中发生错误的语句记录统计信息，不记录具体的错误类型，例如：不记录语法错误嘞的信息。

⭐️示范

写一句错误的SQL

select * from a where order by id asc;

查询events_statements_history表中错误号为1064的记录

select * from events_statements_history where mysql_errno=1064\G

如果不知道错误号是多少，可以查询发生错误次数不为0的语句记录，在里边找到SQL_TEXT和MESSAGE_TEXT字段（提示信息为语法错误的就是它）。

select * from events_statements_history where errors > 0\G

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%';

⭐️示例

开启会话，执行事务，插入操作。

通过查询活跃事务，来看正在执行的事务事件

select * from events_transaction_current where state = 
'ACTIVE'\G

回滚事务操作。

查看事务事件当前表events_transaction_current，和事务事件历史记录表events_transactions_history。

可看到两表中都记录了一行事务事件信息，线程ID为93（上图可看到）线程执行了一个事务，事务状态为ROLLED BACK。

select * from events_transactions_current where THREAD_ID = 93\G

select * from events_transactions_history where THREAD_ID = 93 order by TIMER_END DESC \G

当把执行事务的会话关闭后，事务事件当前表events_transaction_current记录会抹除。这时需要查询的话需要去events_transactions_history_long表去查。

8. 小结

performance_schema不止之前所提及的功能，它还能提供查看SQL语句执行阶段和进度信息，MySQL集群下复制功能查看复制报错详情等等。

官网

三. sys系统库

1. 使用须知

sys系统库支持MySQL5.6或更高版本，不支持MySQL5.5.x及一下版本。

sys系统库通常是提供给专业的DBA人员排查一些特定问题使用，其下所涉及的各项查询或多或少会对性能有一定影响。

因为sys系统库提供了一些代替直接访问performance_schema视图，所以必须启用performance_schema（将performance_schema系统参数设置为on）。

同时要完全访问sys系统库，用户必须具有一下数据库的管理员权限。

如果要充分使用sys系统库的功能，则必须启用某些performance_schema的功能。

比如：

1. 启用所有的wait instruments：

   call sys.ps_setup_enable_instrument('wait');
   

   

2. 启用所有事件类型的current表：

   call sys.ps_setup_enable_consumer('current');
   

注意：performance_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用所有需要功能会对性能产生一定影响，因此最好仅启用所需的配置。

2. 使用

如果使用了use语句切换默认数据库，那么就可以直接使用sys系统库下的视图进行查询，就像查询某个库小的表操作一样。

也可使用db_name.view_name，db_name.procedure_name等方式，在不指定默认数据库等情况下访问sys系统库中的对象（这叫做名称限定对象引用）。

sys系统库包含许多视图，它们以各种方式对performance_schema库里的表进行聚合计算展示，这些视图大部分是成对出现的，两个视图名称相同，但有一个视图是带x$前缀。

host_summary_by_file_io
# 和
x$host_summary_by_file_io

代表按照主机进行汇总统计的文件IO性能数据，两个视图访问的数据源是相同的，但在创建视图的语句中，不带x$前缀视图显示的是相关数值经过单位换算后的数据（单位是毫秒，秒，分钟，小时，天等），带x$前缀视图显示的是原始数据（单位是皮秒）。

查询host_summary和x$host_summary

3. 查看慢SQL语句慢在哪

如果我们频繁在慢查询日志发现某个语句执行缓慢，且在表结构，索引结构，统计信息中都无法找出原因时，则可以利用sys系统库中的：sys.session视图结合performance_schema的等待事件来找出问题所在。

session视图的作用：可以查看当前用户会话的进程列信息，看看当前进程到底在干什么，

启用与等待事件相关功能

call sys.setup_enable_instrument('wait');
call sys.ps_setup_consumer('wait');

⭐️

执行sql

select sleep(30);

在另一个session中的sys查询

-- 1.只查询query事件
-- 2.排除当前连接产生的SQL
select * from session where command = 'query' and conn_id != connection_id()\G

表查询的crud数据量和IO耗时统计

select * from schema_table_statistics_with_buffer\G

4. 小结

除此之外，通过sys可查看InnoDB缓冲池中的热点数据，查看是否有事务锁等待，查看未使用的，冗余索引，查看哪些语句使用了全表扫描等。

官网更多使用策略

四. Information_schema

1. 概念

information_schema提供了对数据库元数据，统计信息以及有关MySQL Server信息的访问（例如：数据库或表名，字段的数据类型和访问权限等）。该库中保存的信息可称为MySQL的数据字典或系统目录。

在每个MySQL实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用use语句将默认数据库设置为information_schema，但该库下的所有表只读的，不能执行insert，update，delete等数据变更操作。

针对information_schema下的表查询操作可以替代一些show查询语句（列如：show databases，show tables等）。

MySQL版本的不同，表的个数和存放是不同的。在MySQL5.6版本中共有59个表，在MySQL8.0.27中共有79个。

在MySQL8.0版本中，该schema下的数据字典表（包含部分原Memory存储引擎临时表）迁移到了mysql中的schema下。且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问。

information_schema下的所有表使用的都是Memory和InnoDB存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失。在MySQL的4个系统库中，information_schema也是唯一一个在文件系统上没有对应库的目录和文件的系统库。这是因为 information_schema 中的数据是由 MySQL 服务器动态生成的，并且这些数据是基于服务器当前的状态和配置。默认情况下，performance_schema 也不在文件系统上创建对应的文件或目录。不过，MySQL 允许通过配置选项来启用或禁用 performance_schema 的一些特性，包括是否将某些性能监控数据记录到文件系统上的文件中。

2. 分类

2.1. Server层的统计信息字典表

1. columns表：提供查询表中的列（字段）信息。
2. key_column_usage表：提供查询哪些索引列存在的约束条件。该表中的信息包含主键，唯一索引，外建等约束信息。例如：所在的库表列名，引用的库表列名等。该表中的信息与table_constraints表中记录的信息有些类似，但table_contraints表中没有记录约束引用的库表列信息，而key_column_usage表中却记录了table_constraints表中所没有的约束类型。
3. referential_constraints：提供查询关于外键约束的一些信息。
4. statistics：提供查询关于索引的一些统计信息，一个索引对应一行数据
5. table_constraints：提供查询与表相关的约束信息。
6. files：提供查询与MySQL的数据表空间文件相关信息。
7. engines：提供查询MySQL Server支持的存储引擎相关信息。
8. tablespaces：提供查询活跃表空间信息（主键记录的是NDB存储引擎的表空间信息）；注意：该表不提供有关InnoDB存储引擎的表空间信息。对于InnoDB表空间的元数据信息，请查询innodb_sys_tablespaces和innodb_sys_datafiles表。另外从MySQL5.7.8开始，information_schema.files表也提供查询InnoDB表空间的元数据信息。

2.2. Server层的表级别对象字典表

1. veiws：提供查询数据库中的视图相关信息。查询该表账户需要拥有show view权限。
2. triggers：提供查询关于某个数据库下的触发器相关信息。
3. tables：提供查询与数据库内的表相关基本信息。
4. routines：提供查询关于存储过程和存储函数的信息（不包括用户自定义函数）。该表的信息与mysql.proc中记录的信息相对应（前提是表中有值）。
5. partitions：提供查询关于分区表的信息。
6. events：提供查询与计划任务事件相关信息。
7. parameters：提供有关存储过程和函数参数信息，以及有关存储函数的返回值信息。这些参数信息与mysql.proc表中的param_list列记录的内容类似。

2.3. Server层的混杂信息字典表

1. global_status，global_variables，session_status，session_variables：提供查询全局，会话级别的状态变量与系统变量信息。
2. optimizer_trace：提供优化程序跟踪功能产生的信息。跟踪供默认是关闭的，使用optimizer_trace系统变量启用该功能，则每个会话只能跟踪它自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一条跟踪的SQL语句。
3. plugins：提供查询关于MySQL Server支持哪些插件信息。
4. processlist：提供查询一些关于线程运行过程中的状态信息。
5. profiling：提供查询关于语句性能分析的信息。其记录内容对应于show profiles和show profile语句产生的信息。该表只有在会话边领profiling=1时才会记录语句性能分析信息，否则该表不记录。注意：从MySQL5.7.2开始，此表不在推荐使用，在未来MySQL版本中删除，改用performance schema替代。
6. character_sets：提供查询MySQL Server支持的可用字符集。
7. collations：提供查询MySQL Server支持的可用校对规则。
8. collation_character_set_applicability：提供查询MySQL Server中哪种字符集使用于什么叫对规则。查询结果集相当于从show collation获得的结果集的前2个字段值。目前并没有发现表有太大作用。
9. column_privileges：提供查询关于列（字段）的权限信息，表中的内容来自mysql.column_priv列权限表（需针对一个表的列单独授权之后才会有的内容）。
10. schema_privileges：提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，该表中的信息来自mysql.db表。
11. table_privileges：提供查询关于表级别的权限信息，该表中的内容来自mysql.tables_priv表。
12. user_privileges：提供查询全局权限信息，该表信息来自mysql.user表。

2.4 . InnoDB层的系统字典表

1. innodb_sys_datafiles：提供查询InnoDB所有表空间类型文件的元数据（内部使用的表空间ID和表空间文件的路径信息），包括独立表空间，常规表空间，系统表空间，临时表空间和undo空间（如果开启了独立undo空间的话）。该表中的信息等同于InnoDB数据字典内部sys_datafiles表的信息。
2. innodb_sys_virtual：提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息，等同于InnoDB数据字典内部SYS_VIRTUAL表的信息。该表中展示的行信息是与虚拟生成列相关联列的每个列的信息。
3. innodb_sys_indexes：提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息。
4. innodb_sys_tables：提供查询有关InnoDB表的元数据信息，等同于InnoDB数据字典内部SYS_TABLES表的信息。
5. innodb_sys_fileds：提供查询有关InnoDB索引键列（字段）的元数据信息，等同于InnoDB数据字典内部SYS_FIELDS表的信息。
6. innodb_sys_tablespaces：提供查询有关InnoDB独立表空间和普通表空间的元数据信息（也包含了全文索引表空间），等同于InnoDB数据字典内部SYS_TABLESPACES表的信息。
7. innodb_sys)foreign_cols：提供查询有关InnoDB外键列的状态信息，等同于InnoDB数据字典内部SYS_FOREIGN_COLS表的信息。
8. innodb_sys_columns：提供查询有关InnoDB表列的元数据信息，等同于InnoDB数据字典内部sys_columns表的信息。
9. innodb_sys_foreign：提供查询有关InnoDB外键的元数据信息，等同于InnoDB数据字典内部SYS_FOREIGN表的信息。
10. innodb_sys_tablestats：提供查询有关InnoDB表的较低级别的状态信息视图。 MySQL优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引。这些信息保存在内存中的数据结构中，与存储在磁盘上的数据无对应关系。在InnoDB内部也无对应的系统表。

2.5. InnoDB层的锁，事务，统计信息字典表

1. innodb_locks：提供查询InnoDB引擎中事务正在请求的且同时被其他事务阻塞的锁信息（即没有发生不同事务之间锁等待的锁信息，在这里是查看不到的。例如，当只有一个事务时，无法查看到该事务所加的锁信息）。该表中的内容可用于诊断高并发下的锁争用信息。
2. innodb_trx：提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务什么时间点开始，以及事务正在执行的SQL语句文本信息等（如果有SQL语句的话）。
3. innodb_buffer_page_lru：提供查询缓冲池中的页面信息。与INNODB_BUFFER_PAGE表不同，INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中的页如何进入LRU链表，以及在缓冲池不够用时确定需要从中逐出哪些页的信息。
4. innodb_lock_waits：提供查询InnoDB事务的锁等待信息。如果查询该表为空，则表示无锁等待信息；如果查询该表中有记录，则说明存在锁等待，表中的每一行记录表示一个锁等待关系。在一个锁等待关系中包含：一个等待锁（即，正在请求获得锁）的事务及其正在等待的锁等信息、一个持有锁（这里指的是发生锁等待事务正在请求的锁）的事务及其所持有的锁等信息。
5. innodb_temp_table_info：提供查询有关在InnoDB实例中当前处于活动状态的用户（只对已建立连接的用户有效，断开的用户连接对应的临时表会被自动删除）创建的InnoDB临时表的信息。它不提供查询优化器使用的内部InnoDB临时表的信息。该表在首次查询时创建。
6. innodb_buffer_page：提供查询关于缓冲池中的页相关信息。
7. innodb_metrics：提供查询InnoDB更为详细的性能信息，是对InnoDB的performance_schema的补充。通过对该表的查询，可用于检查InnoDB的整体健康状况，也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。
8. innodb_buffer_pool_stats：提供查询一些InnoDB缓冲池中的状态信息，该表中记录的信息与SHOW ENGINEINNODB STATUS语句输出的缓冲池统计部分信息类似。另外，InnoDB缓冲池的一些状态变量也提供了部分相同的值。

2.6. InnoDB层的全文索引字典表

1. innodb_ft_config
2. innodb_ft_begin_deleted
3. innodb_ft_deleted
4. innodb_ft_default_stopword
5. innodb_ft_index_table

2.7. InnoDB层的压缩相关字典表

1. innodb_cmp和innodb_cmp_reset：这两个表中的数据包含了与压缩的InnoDB表页有关的操作状态信息。表中记录的数据为测量数据库中的InnoDB表压缩的有效性提供参考。
2. innodb_cmp_per_index和innodb_cmp_per_index_reset：这两个表中记录了与InnoDB压缩表数据和索引相关的操作状态信息，对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据。
3. innodb_cmpmem和innodb_cmpmem_reset：这两个表中记录了InnoDB缓冲池中压缩页的状态信息，为测量数据库中InnoDB表压缩的有效性提供参考。

3. 应用

查询索引列信息

innodb_fields表提供查询有关innodb索引列（字段）的元数据信息，等同于InnoDB数据字典中的sys_fields表中的信息。

innodb_indexes表提供查询InnoDB索引元数据信息，等同于InnoDB数据字典内部sys_indexes表中信息。

innodb_tables表提供查询有关InnoDB表的元数据信息，等同于InnoDB数据字典sys_tables表的信息。

假如需要查询test库下的InnoDB表a的索引列名称，组成和索引列顺序等相关信息

SELECT t.NAME     AS d_t_name,
       i.NAME     AS i_name,
       i.type     AS i_type,
       i.N_FIELDS AS i_column_numbers,
       f.NAME     AS i_column_name,
       f.pos      AS i_position
FROM INNODB_TABLES AS t
         JOIN INNODB_INDEXES AS i ON t.TABLE_ID = i.TABLE_ID
         LEFT JOIN INNODB_FIELDS AS f ON i.INDEX_ID = f.INDEX_ID
WHERE t.NAME = 'test/a';

i_type(innodb_indexes.type)，它表示索引类型的数字id：

0：二级索引

1：集群索引

2：唯一索引

3：主键索引

32：全文索引

64：空间索引

128：包含虚拟生成列的二级索引

五. mysql系统库

1. 权限系统表

权限管理时DBA的职责，所以对于这个部分的表，我们大概了解即可。在mysql系统库中，MySQL访问权限系统表，放在mysql库中，主要包含如下几个表

1. user：包含用户账户，全局权限和其他非权限列表（安全配置字段和资源控制字段）。
2. db：数据库级别的权限表，该表中记录的权限信息代表用户是否可以使用这些权限，来访问被授予访问的数据库下的所有对象（表或存储过程）。
3. tables_priv：表级别的权限表。
4. columns_priv：字段级别的权限表。
5. procs_priv：存储过程和函数权限表。
6. proxies_priv：代理用户权限表。

要更改权限表的内容，应该使用账号管理语句，如create user ，grant，revoke等来间接修改（并重新flush privileges），不建议使用DML 语句修改权限表。

grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。DML语句直接修改权限只是修改了表中权限信息，需要执行flush privileges，来更新内存中保存用户权限的相关对象。

2. 统计信息表

持久化统计功能是通过将内存中的统计数据存储到磁盘中，使其在数据库重启时可快速重读入这些统计信息而不用重新执行统计，从而使得查询优化器可以利用这些持久化的统计信息准确地选择执行计划。（如果没有这些持久化的统计信息，那么数据库重启之后内存中的统计信息将会丢失，下一次访问某库某表时，需要重新计算统计信息，并且重新计算可能会因估算值的差异导致查询计划发生变更，从而导致查询性能发生变化）

开启统计信息持久化功能

show variables like 'innodb_stats_persistent';

-- 开启
set @@global.innodb_stats_persistent = 'on';

-- 关闭
set @@global.innodb_stats_persistent = 'off';

2.1. innodb_table_stats

innodb_table_stats表提供查询与表数据相关的统计信息

select * from innodb_table_stats where table_name = 'a'\G

1. database_name：数据库名称。
2. table_name：表名称
3. last_update：InnoDB上次更新时间
4. n_rows：表中估算数据记录行数
5. clustered_index_size：主键索引的大小，以页为单位估算数值
6. sum_of_other_index_sizes：其他（非主键）索引的总大小，以页为单位的估算数值

2.2. innodb_index_stats

innodb_index_stats表提供查询与索引相关统计信息。

select * from innodb_index_stats where table_name = 'a';

1. index_name：索引名称
2. stat_name：统计信息名称，其对应统计信息直接保存在stat_value中。
3. stat_value：保存统计信息名称stat_name字段对应的统计信息
4. sample_size：stat_value字段中提供的统计信息估计值的采样页数。
5. stat_description：统计信息名称stat_name字段中指定统计信息说明

stat_name字段一共有如下几个统计值：

1. size：当stat_name字段为size时，stat_value字段值表示索引中的总页数量。

2. n_leaf_pages：当stat_name字段为n_leaf_pages值时，stat_value字段表示索引叶子页数量

3. n_diff_pfxNN：NN代表数字（例如01，02等）。当stat_name值为n_dff_pfxNN时，stat_value字段值表示索引等first column（即索引等最前索引列，从索引定义顺序的第一个列开始）列的唯一值数量。

   列如，当NN为01时，stat_value字段值表示索引的第一个列的唯一值数量。当NN为02时，stat_value字段值表示索引的第一个和第二个列组合的唯一值数量。此外，当stat_name=n_diff_pxNN情况下，stat_description字段显示一个以逗号分割的计算索引统计信息字段的列表。

index_name字段值情况：

1. PRIMARY：可看到stat_description字段的描述信息为id，主键索引的统计信息只包括主键索引时显示指定的列。
2. idx_age_city：stat_description只有age,city，普通索引的统计信息保罗了显示定义的列和主键列
3. 唯一索引情况只包含创建唯一索引时显式指定的列。

3. 日志记录表

MySQL日志系统包含：

1. 普通查询日志
2. 慢查询日志
3. 错误日志（记录服务器启动时，运行中，停止时的错误信息）
4. 二进制日志（记录服务器运行过程中数据变更的逻辑操作）
5. 中继日志（记录从库I/O线程从主库获取的主库数据变更日志）
6. DDL日志（记录DDL语句执行时元数据变更信息，在MySQL5.7中只支持写入文件中，在MySQL8.0中支持写入innodb_ddl_log表中）

在MySQL5.7中，只有普通查询日志，慢查询日志支持写入表中（也支持写入文件中），可以通过log_output_TABLE设置保存到mysql.general_log表和mysql.slow_log表中，其他日志类型在MySQL5.7中只支持写入文件中。

3.1. general_log

general_log表提供查询普通SQL语句到执行记录信息，用于查看客户端在服务器上所执行的SQL语句。

-- 查看日志记录信息
show variables like 'general_log';
-- 输出类型
-- 'TABLE,FILE'表示同时输出到表和文件
show variables like 'log_output';


-- 开启
set @@global.log_output = 'TABLE';
set @@global.general_log = 'on';

开启后，执行任意一个查询

select * from test.a;

查看general_log信息

select * from mysql.general_log\G

3.2. slow_log

slow_log表提供：

1. 查询执行时间超过long_query_time设置的SQL语句
2. 未使用索引的语句：log_queries_not_using_indexes
3. 管理语句：log_slow_admin_statements。

show variables like 'log_queries_not_usng_indexes';

show variables like 'log_slow_admin_statements';

-- 开启
set @@global.log_queries_not_using_indexes = 'on';
set @@global.log_slow_admin_statements = 'on';

慢查询日志可以帮助定位存在问题的SQL语句，从而进行SQL语句优化

show variables like 'slow_query_log';

-- 开启
set @@global.slow_query_log = 'on';

⭐️使用

-- 设置慢sql时间阈值
-- 或在my.cnf配置 long_query_time=0
-- mysql 8.0不允许动态修改
set @@global.long_query_time = 0;

-- 随便写个sql
select * from test.b;

-- 查询慢sql表
select * from mysql.slow_log\G

4. InnoDB中 统计数据

可通过show table status查看表的统计信息，通过show index可看到关于索引统计数据，统计数据的来源，往后看

4.1. 统计数据存储方式

InnoDB提供了两种存储统计数据方式：

1. 永久性统计数据：存储在磁盘上，服务器重启后，统计信息还在。
2. 非永久性统计数据：存储在内存中，当服务器关闭时，这些统计数据会被清除掉，服务器重启后，在某些适当的场景下才会重新收集这些统计数据。

MySQl提供了系统变量innodb_stats_persistent来控制采用哪种方式去存储统计数据。在MySQL5.5.6之前，innodb_stats_persistent默认是关闭的，可以理解为是存储到内存中，之后的版本中，默认开启，存储磁盘中。

show variables like 'innodb_stats_persistent';

InnoDB默认是以表为单位进行收集和存储统计数据。

可以把某些表统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。通过创建和修改表时指定stats_persistent属性来指明该表的统计数据存储方式：

stat_persistent值：

1. 0，存储到内存中
2. 1，存储到磁盘中

创建时没指定，默认采用系统变量innodb_stats_persistent值作为该属性的值。

create table 表名 (...) engine = innodb, stats_persistent=(1|0);

alter table 表名 engine = innodb, stats_persistent = (1|0);

4.2. 基于磁盘的永久性统计数据

当指定表索引的统计数据存放到磁盘上时，实际上是把这些统计数据簇出到两个表里：

show tables from mysql like 'innodb%';

1. innodb_table_stats：存储了关于表统计数据，每一条记录对应着一个表的统计数据
2. innodb_index_stats：存储了关于索引统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

---

⭐️innodb_index_stats

1. n_rows：表中估算记录数
2. clustered_index_size：表的聚簇索引估算的占用的页面数量
3. sum_of_other_index_sizes：表的其他索引估算的占用页面数量

🌔n_rows统计项的收集

InnoDB统计一个表中有多少行记录是这样的：

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值记录数量乘全部叶子节点的数量。得出n_rows的值。

n_rows值精确在于统计时采样的页面数量，MySQL用名为innodb_stats_persisteng_sample_pages的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量，该值设置的越大，统计出的n_rows值越不精确，但带来的耗时统计特别少，默认是20。

InnoDB默认是以表单位来收集和存储统计数据，我们也可单独设置某个表的采样页面数量，设置方式在创建或修改表时指定stats_sample_pages属性指明表的统计数据存储方式（默认使用系统变量innodb_stats_persistent_sample_pages的值）：

create table 表名 (...) engine = innodb, stats_sample_pages=具体采样页面数量;

alter table 表名 engine = innodb, stats_sample_pages = 具体采样页面数量;

🌔clusted_index_size和sum_of_other_index_sizes

这两个统计项的收集牵涉到具体的InnoDB表空间的知识和存储页面数据的细节。

---

⭐️innodb_index_stats

1. index_name：索引名
2. stat_name：统计项名称
3. stat_value：对应统计项的值
4. sample_size：生成统计数据而采样的页面数量
5. stat_description：对应的统计项描述

desc innodb_index_stats;

innodb_index_stats表的每条记录代表着一个索引的一个统计项，我们看下test.a表的索引统计数据

select * from innodb_index_stats where database_name = 'test' and table_name = 'a';

上面章节已经讲过参数信息，不再赘述。

计算某些列包含多少不重复值时，需要对一些叶子节点进行采样，sample_size代表着采样页面数量。

对于多个列的联合索引来说，

采样的页面数量 = innodb_stats_persistent_sample_pages ✖️ 索引列个数。

当需要采样的页面数量大于该索引的叶子节点数量，就会采用全表扫描来统计索引列的不重复值数量了，所以在查询结果看到不同索引对应size值是不一样的。

🌔定期更新统计数据

随着不断对表进行crud，表中的数据会跟着变化。

innodb_table_stats和innodb_index_stats表里的统计数据也在变化。

MySQL如下两种更新统计数据方式：

1. 开启innodb_stats_auto_recalc
2. 手动调用analyze table语句来更新统计信息
3. 手动更新innodb_table_stats和innodb_index_stats表

⭐️ 1. 开启innodb_stats_auto_recalc

系统变量innodb_stats_auto_recalc决定服务器是否自动重新计算统计数据，它的默认值on。每个表维护了一个变量，该变量记录着对该表进行crud的记录条数，如果发生变动的记录超过表大小的10%，并且自动重新计算统计数据功能打开，那么服务器会重新进行一次统计数据的计算，并更新innodb_table_stats和innodb_index_stats表。

自动更新统计数据是异步，可以理解为自动重新计算的数据可能会延迟几秒才会计算。

InnoDB是以表为单位进行收集和存储统计数据，可单独为某个表设置是否重新计算统计数的属性。设置方式通过创建或修改表的时候通过指定stats_auto_recalc属性来知名该表的统计数据存储方式

0：关闭

1：开启

未设置时，采用系统的默认innodb_stats_auto_recalc属性值。

create table 表名 (...) Engine = InnoDB, STATS_AUTO_RECALC(1|0);

alter table 表名 Engine = InnoDB, STATS_AUTO_RECALC(1|0);

⭐️2. 手动调用analyze table语句更新统计信息

如果innodb_stats_auto_recalc关闭时，也可手动调用analyze table触发自动统计更新。

analyze table 表名;

analyze table语句会立即计算统计数据，为同步操作。在表中索引多或采样页面特别多时，该过程会很慢。

⭐️3. 手动更新innodb_table_stats和innodb_index_stats表

innodb_table_stats和innodb_index_stats表和普通表一样，可做crud。相当于可以手动更新某个表或索引的统计数据。

比如把a表关于行数统计数据更改下：

1. 更新innodb_table_stats表。

   update....
   

2. MySQL查询优化器重新加载更改过的数据。

   flush table a;