MySQL 性能调优
MySQL 性能调优是一个复杂且多维度的过程,下面从数据库设计、查询优化、配置参数调整、硬件优化几个方面为你介绍相关的调优方法。
本文介绍 MySQL 性能调优中的系统库应用。首先讲解 sys 系统库的使用前提、视图分类及慢 SQL 诊断方法,强调需开启 performance_schema。其次详细阐述 information_schema 的作用、表分类(Server 层与 InnoDB 层)及应用场景,包括索引列查询、锁等待监控等,帮助 DBA 进行数据库元数据管理与性能分析。

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 等参数,以平衡数据安全性和性能。sys 系统库支持 MySQL 5.6 或更高版本,不支持 MySQL 5.5.x 及以下版本。
sys 系统库通常都是提供给专业的 DBA 人员排查一些特定问题使用的,其下所涉及的各项查询或多或少都会对性能有一定的影响。
因为 sys 系统库提供了一些代替直接访问 performance_schema 的视图,所以必须启用 performance_schema(将 performance_schema 系统参数设置为 ON),sys 系统库的大部分功能才能正常使用。
同时要完全访问 sys 系统库,用户必须具有以下数据库的管理员权限。
如果要充分使用 sys 系统库的功能,则必须启用某些 performance_schema 的功能。比如:
启用所有的 wait instruments:
CALL sys.ps_setup_enable_instrument('wait');

启用所有事件类型的 current 表:
CALL sys.ps_setup_enable_consumer('current');

***注意:*performance_schema 的默认配置就可以满足 sys 系统库的大部分数据收集功能。启用所有需要功能会对性能产生一定的影响,因此最好仅启用所需的配置。
如果使用了 USE 语句切换默认数据库,那么就可以直接使用 sys 系统库下的视图进行查询,就像查询某个库下的表一样操作。也可以使用 db_name.view_name、db_name.procedure_name、db_name.func_name 等方式,在不指定默认数据库的情况下访问 sys 系统库中的对象(这叫作名称限定对象引用)。

在 sys 系统库下包含很多视图,它们以各种方式对 performance_schema 表进行聚合计算展示。这些视图大部分是成对出现的,两个视图名称相同,但有一个视图是带 x$前缀的。
host_summary_by_file_io 和 x$host_summary_by_file_io
代表按照主机进行汇总统计的文件 I/O 性能数据,两个视图访问的数据源是相同的,但是在创建视图的语句中,不带 x$前缀的视图显示的是相关数值经过单位换算后的数据(单位是毫秒、秒、分钟、小时、天等),带 x$前缀的视图显示的是原始的数据(单位是皮秒)。


如果我们频繁地在慢查询日志中发现某个语句执行缓慢,且在表结构、索引结构、统计信息中都无法找出原因时,则可以利用 sys 系统库中的工具:sys.session 视图结合 performance_schema 的等待事件来找出症结所在。那么 session 视图有什么用呢?使用它可以查看当前用户会话的进程列表信息,看看当前进程到底在干什么,注意,这个视图在 MySQL 5.7.9 中才出现。

首先需要启用与等待事件相关功能:
call sys.ps_setup_enable_instrument('wait'); call sys.ps_setup_enable_consumer('wait');

然后模拟一下:
一个 session 中执行
select sleep(30);
另外一个 session 中在 sys 库中查询:
select * from session where command='query' and conn_id !=connection_id()\G


查询表的增、删、改、查数据量和 I/O 耗时统计
select * from schema_table_statistics_with_buffer\G

除此之外,通过 sys 还可以查询查看 InnoDB 缓冲池中的热点数据、查看是否有事务锁等待、查看未使用的,冗余索引、查看哪些语句使用了全表扫描等等。
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 版本的不同,表的个数和存放是有所不同的。在 MySQL 5.6 版本中总共有 59 个表,在 MySQL 5.7 版本中,该 schema 下总共有 61 个表,


在 MySQL 8.0 版本中,该 schema 下的数据字典表(包含部分原 Memory 引擎临时表)都迁移到了 mysql schema 下,且在 mysql schema 下这些数据字典表被隐藏,无法直接访问,需要通过 information_schema 下的同名表进行访问。
information_schema 下的所有表使用的都是 Memory 和 InnoDB 存储引擎,且都是临时表,不是持久表,在数据库重启之后这些数据会丢失。在 MySQL 的 4 个系统库中,information_schema 也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。
(1)COLUMNS • 提供查询表中的列(字段)信息。
(2)KEY_COLUMN_USAGE • 提供查询哪些索引列存在约束条件。 • 该表中的信息包含主键、唯一索引、外键等约束信息,例如:所在的库表列名、引用的库表列名等。该表中的信息与 TABLE_CONSTRAINTS 表中记录的信息有些类似,但 TABLE_CONSTRAINTS 表中没有记录约束引用的库表列信息,而 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 表。另外,从 MySQL 5.7.8 开始,INFORMATION_SCHEMA.FILES 表也提供查询 InnoDB 表空间的元数据信息。
(9)SCHEMATA • 提供查询 MySQL Server 中的数据库列表信息,一个 schema 就代表一个数据库。
(1)VIEWS • 提供查询数据库中的视图相关信息。查询该表的账户需要拥有 show view 权限。
(2)TRIGGERS • 提供查询关于某个数据库下的触发器相关信息。
(3)TABLES • 提供查询与数据库内的表相关的基本信息。
(4)ROUTINES • 提供查询关于存储过程和存储函数的信息(不包括用户自定义函数)。该表中的信息与 mysql.proc 中记录的信息相对应(如果该表中有值的话)。
(5)PARTITIONS • 提供查询关于分区表的信息。
(6)EVENTS • 提供查询与计划任务事件相关的信息。
(7)PARAMETERS • 提供有关存储过程和函数的参数信息,以及有关存储函数的返回值信息。这些参数信息与 mysql.proc 表中的 param_list 列记录的内容类似。
(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 时才会记录语句性能分析信息,否则该表不记录。 • 注意:从 MySQL 5.7.2 开始,此表不再推荐使用,在未来的 MySQL 版本中删除,改用 Performance Schema 代替。
(6)CHARACTER_SETS • 提供查询 MySQL Server 支持的可用字符集。
(7)COLLATIONS • 提供查询 MySQL Server 支持的可用校对规则。
(8)COLLATION_CHARACTER_SET_APPLICABILITY • 提供查询 MySQL Server 中哪种字符集适用于什么校对规则。查询结果集相当于从 SHOW COLLATION 获得的结果集的前两个字段值。目前并没有发现该表有太大的作用。
(9)COLUMN_PRIVILEGES • 提供查询关于列(字段)的权限信息,表中的内容来自 mysql.column_priv 列权限表(需要针对一个表的列单独授权之后才会有内容)。
(10)SCHEMA_PRIVILEGES • 提供查询关于库级别的权限信息,每种类型的库级别权限记录一行信息,该表中的信息来自 mysql.db 表。
(11)TABLE_PRIVILEGES • 提供查询关于表级别的权限信息,该表中的内容来自 mysql.tables_priv 表。
(12)USER_PRIVILEGES • 提供查询全局权限的信息,该表中的信息来自 mysql.user 表。
(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_FIELDS • 提供查询有关 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 内部也无对应的系统表。
(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 ENGINE INNODB STATUS 语句输出的缓冲池统计部分信息类似。另外,InnoDB 缓冲池的一些状态变量也提供了部分相同的值。
(1)INNODB_FT_CONFIG (2)INNODB_FT_BEING_DELETED (3)INNODB_FT_DELETED (4)INNODB_FT_DEFAULT_STOPWORD (5)INNODB_FT_INDEX_TABLE
(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 表压缩的有效性提供参考。
查看索引列的信息
INNODB_SYS_FIELDS 表提供查询有关 InnoDB 索引列(字段)的元数据信息,等同于 InnoDB 数据字典中 SYS_FIELDS 表的信息。
INNODB_SYS_INDEXES 表提供查询有关 InnoDB 索引的元数据信息,等同于 InnoDB 数据字典内部 SYS_INDEXES 表中的信息。
INNODB_SYS_TABLES 表提供查询有关 InnoDB 表的元数据信息,等同于 InnoDB 数据字典中 SYS_TABLES 表的信息。
假设需要查询 lijin 库下的 InnoDB 表 order_exp 的索引列名称、组成和索引列顺序等相关信息,


则可以使用如下 SQL 语句进行查询
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_SYS_TABLES AS t JOIN INNODB_SYS_INDEXES AS i ON t.TABLE_ID = i.TABLE_ID LEFT JOIN INNODB_SYS_FIELDS AS f ON i.INDEX_ID = f.INDEX_ID WHERE t.NAME = 'lijin/order_exp';

结果中的列都很好理解,唯一需要额外解释的是 i_type(INNODB_SYS_INDEXES.type),它是表示索引类型的数字 ID:
0 =二级索引 1=集群索引 2 =唯一索引 3 =主键索引 32 =全文索引 64 =空间索引 128 =包含虚拟生成列的二级索引。

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online