MySQL进阶知识（一）--系统库

本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。

本文个人在掘金首发（https://juejin.cn/editor/drafts/6996322071986831368），现在同步到简书社区。

MySQL系统自带的库

当你安装完MySQL（我安装的是8.0.17版本），你会发现里面就会有几个不是我们自己创建的库：

image.png

information_schema、mysql、sys、performance_schema（不同的版本还可能有test，不过没什么实际作用这里不做讨论）。这些库都是MySQL自带的库，我不止一次听别人说不要对这里面的东西进行改动。好奇心驱使我探究一下它们到底有是什么作用。下面我一个一个来介绍它们：

information_schema库

从库的名字来看，这个库应该是保存一些数据库基本信息的库。实际上它并没有保存这些信息，只是对一些信息提供一个访问的入口。你在information_schema库中看到的表都不是真正的表而是视图。你只能select查询这些表的数据，而无法修改或者插入这个库中任意表的数据。这也就是使用Navicat直接打开这些表的时候会报“Field 'Create Table' not found”。

information_schema的表列表.png

被访问的信息主要是MySQL服务器所维护的所有其他数据库元数据。这些元数据有：数据库的名、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等（官方文档）。这里举其中比较重要的表说明一下：

• PROFILING ：该表提供服务器当前正在执行的语句的分析信息，它的内容对应于SHOW PROFILES语句产生的信息；
• ENGINES ：该表提供存储引擎信息 —— 当前版本是否支持这个引擎、引擎描述、是否为默认引擎、是否支持事物、是否能够支持事物的回滚点；
• COLUMNS ：该表提供所有表的所有字段信息 —— 该列在哪个库哪个表中、字段名是什么、数据类型、精度、默认值、索引情况等；
• INNODB_TABLES ：该表提供了InnoDB表的元数据。

使用SELECT ... FROM INFORMATION_SCHEMA语句从information_schema库查询数据库基本信息，也可以通过SHOW语句来查询。前者是后者的一种替代方式，比如：引擎列表查询：SHOW ENGINES和SELECT * FROM INFORMATION_SCHEMA.ENGINES等价; 插件查询：SHOW PLUGINS和SELECT * FROM INFORMATION_SCHEMA.PLUGINS等价;

mysql库

mysql库作为系统的自带库，该库中的表主要存储的是服务器运行时所需要的数据，包括：数据库字典（字符集、表、列、索引等相关信息）、权限设置（用户、表和字段的权限、代理 权限）、日志系统信息、时区等mysql自己需要使用的控制和管理信息（官方文档）。

image.png
这里列举一些关键的表进行说明：

• user：用户帐户、全局权限和其他非权限列 —— 该表决定是否允许用户连接到服务器。如果允许连接，权限字段则为该用户的全局权限。
  [图片上传失败...(image-937211-1631631211494)]
• db: 数据库级权限--用于决定哪些用户可以从哪些主机访问哪些数据库。包含在db表中的权限适用于这个表标识的数据库；

image.png

• tables_priv: 表级权限 —— 用于决定哪些用户对哪些表有什么权限（有：Select、Insert、Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等）；
• columns_priv: 列级权限 —— 用于决定哪些用户对哪些表的指定字段有什么权限（有：Select、Insert、Update 和 References）；
• plugin：在服务器正常启动期间，会加载此表中注册的插件；
• slow_log: 慢查询日志表。
• general_log: 一般查询日志表。
• default_roles：此表列出了在用户连接并验证或执行后要激活的默认角色
• time_zone：时区相关；

performance_schema库：

从库的名字来看，这个库应该是保存一些数据库运行时性能相关的数据。实时上该库也确实用于给用户查询服务器运行期间性能相关的信息，包括：服务器函数调用、等待操作系统、SQL 语句执行阶段（例如解析或排序）、同步调用（例如互斥锁）文件、表 I/O、表锁等信息。performance_schema库中的数据不会持久磁盘到磁盘的，只会在内存表中存在。内容在服务器启动时重新填充，并在服务器关闭时丢弃。其存储引擎使用的是PERFORMANCE_SCHEMA引擎（performance_schema库：官方文档）。

image.png
下面挑几个有代表的表做介绍，从这些表你大概能看出该库大概有什么作用：

• account：该表保存了已连接到服务器不同用户当前持有的连接数和总连接数；
  image.png
• data_locks该表显示了持有和请求的数据锁。有关哪些锁请求被哪些持有锁阻塞的信息；
  image.png
• data_lock_waits:该表保存会话之间的数据锁相关性。包括：会话或事务正在等待哪个锁和当前持有该锁的会话或事务；
  image.png
• events_transactions_current: 保存当前服务每条线程正在进行的事务事件；
• events_transactions_history: 保存服务每条线程最近已结束的事务事件；
• events_waits_current: 保存服务器每个线程的最新的等待事件；
• events_waits_history: 保存服务器每个线程的最近已完成等待事件；

sys库：

从这个库的名字来看像是保存MySQL一些系统配置类信息，实际上它主要是为了方便开发人员或者DBA对performance_schema库收集的服务器性能信息进行查询（将performance_schema里面的信息汇总转化成更直观更容易理解的形式来展示）。这个库里面主要是通过视图的方式来进行数据的查询和更直观的展示（只有sys_config才是真正的会存数据的表），这些视图的源数据表就是来自于performance_schema库。它的功能有：生成性能诊断报告、查询性能架构配置等等（官方文档）。注意只有5.6或以上的MySQL版本才会有该库。如果你不想要也可以把它删掉，是的你删了它也没什么关系。

image.png

这里介绍几个比较常见和重要的视图：

• host_summary：该视图汇总了不同host执行语句的行为，包括文件I/O、连接、总内存分配、当前内存分配等信息。如下图示：
  image.png
• statement_analysis：该视图汇汇总语句的执行信息，其中包括：语句的SQL、目标库、执行次数、错误和告警次数、总耗时、最大耗时等等。如下图所示：
  image.png
• io_global_by_file_by_bytes：该视图展示了文件IO的使用情况，记录了文件的读写情况。通过这些信息你就能知道我们的数据库IO都耗在了什么地方。
  image.png

一般线上MySQL服务器是不会对开发人员开放这几个库的权限。很多配置上的问题（比如权限、连接数、慢查询标准等）运维或者DBA就帮开发人员弄好了。要查一些系统信息也是通过运维和DBA帮助查询。确实开发人员不知道这些表有什么用也不影响写代码，但是了解这几个表能很好帮助我们认识MySQL的一些基本运行规则，比如数据库的权限体系可以怎么划分、MySQL服务器性能怎么样、死锁时要怎么排查、MySQL服务器压测怎么才算过关等等。

总结

深入了解过这些库之后，我发现平时自己忽略的东西其实是如此重要。了解这些库所包含的内容有助于我们了解数据库的监控体系、权限控制等等，并且在出现问题的时候知道怎么通过这些库去定位问题。