欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

详解MySQL8.0​ 字典表增强

程序员文章站 2022-11-29 15:29:18
mysql中数据字典是数据库重要的组成部分之一,information_schema首次引入于mysql 5.0,作为一种从正在运行的mysql服务器检索元数据的标准兼容方式。用于存储数据元数据、统计...

mysql中数据字典是数据库重要的组成部分之一,information_schema首次引入于mysql 5.0,作为一种从正在运行的mysql服务器检索元数据的标准兼容方式。用于存储数据元数据、统计信息、以及有关mysql server的访问信息(例如:数据库名或表名,字段的数据类型和访问权限等)。

8.0之前:

详解MySQL8.0​ 字典表增强

1、元数据来自文件

2、采用memory表引擎

3、frm文件 存放表结构信息

4、opt文件,记录了每个库的一些基本信息,包括库的字符集等信息

5、.trn,.trg文件用于存放触发器的信息内容

5.6> select table_schema ,engine ,count(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by table_schema ,engine;
+--------------------+--------------------+----------+
| table_schema    | engine       | count(*) |
+--------------------+--------------------+----------+
| information_schema | memory       |    49 |
| information_schema | myisam       |    10 |
| mysql       | csv        |    2 |
| mysql       | innodb       |    6 |
| mysql       | myisam       |    21 |
| performance_schema | performance_schema |    52 |
+--------------------+--------------------+----------+
5.7> select table_schema ,engine ,count(*) from information_schema.tables where table_schema in ('information_schema' ,'mysql','performance_schema', 'sys') group by table_schema ,engine;
+--------------------+--------------------+----------+
| table_schema    | engine       | count(*) |
+--------------------+--------------------+----------+
| information_schema | innodb       |    10 |
| information_schema | memory       |    51 |
| mysql       | csv        |    2 |
| mysql       | innodb       |    19 |
| mysql       | myisam       |    10 |
| performance_schema | performance_schema |    87 |
| sys        | null        |   100 |
| sys        | innodb       |    1 |
+--------------------+--------------------+----------+

8.0之后:

详解MySQL8.0​ 字典表增强

1、元数据存在表中

2、全部迁到mysql库下,改为innodb表引擎,且被隐藏

3、information_schema下只能通过view查看

4、null的全部为view

5、存储在单独的表空间mysql.ibd

8.0> select table_schema,engine,count(*) from tables where table_schema in ('information_schema','mysql','performance_schema','sys') group by table_schema,engine;
+--------------------+--------------------+----------+
| table_schema    | engine       | count(*) |
+--------------------+--------------------+----------+
| information_schema | null        |    65 |
| mysql       | innodb       |    31 |
| mysql       | csv        |    2 |
| performance_schema | performance_schema |   102 |
| sys        | null        |   100 |
| sys        | innodb       |    1 |
+--------------------+--------------------+----------+

尽管5.7有了一些改进,但information_schema的性能仍然是我们许多用户的主要痛点。在当前information_schema实现方式下产生的性能问题背后的关键原因是,information_schema表的查询实现方式是在查询执行期间创建临时表。

如下,当我们查询表碎片时:

5.7> explain select round(data_free/1024/1024) as data_free from information_schema.tables where data_free/1024/1024 > 1024 and table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys');
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra                        |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | simple   | tables | all | null     | null | null  | null | null | using where; open_full_table; scanned all databases |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+

extra信息会有open_full_table; scanned all databases 。
skip_open_table,open_frm_only,open_full_table这些值表示适用于information_schema表查询时对文件打开的优化;

  • skip_open_table:表文件不需要打开。信息已经通过扫描数据库目录在查询中实现可用。
  • open_frm_only:只需要打开表的.frm文件。
  • open_full_table:未优化的信息查找。必须打开.frm、.myd和.myi文件。
  • scanned n databases:指在处理information_schema查询时,有多少目录需要扫描。

如果一个mysql实例有上百个库,每个库又有上百张表,information_schema查询最终会从文件系统中读取每个单独的frm文件,造成很多i/o读取。并且最终还会消耗更多的cpu来打开表并准备相关的内存数据结构。它也确实会尝试使用mysql server层的表缓存(系统变量table_definition_cache ),但是在大型实例中,很少有一个足够大的表缓存来容纳所有的表。所以内存使用量会急剧上升,甚至出现oom。

详解MySQL8.0​ 字典表增强

通常我们习惯通过以下手段解决此问题:

1、库表拆分,减少单实例打开文件数量

2、调整table_definition_cache和table_open_cache数量

3、添加物理内存

mysql 8.0 问世之后,又提供了一种选择,由于字典表采用innodb引擎,而且字典表可以使用索引。

下面的图解释了mysql 5.7和8.0设计上的区别:

详解MySQL8.0​ 字典表增强

8.0> explain select table_name,table_rows,concat(round(data_length/1024/1024, 2), 'mb') as size,concat(round(index_length/1024/1024, 2), 'mb') as index_size,data_free/1024/1024 as data_free_mb from information_schema.tables where table_schema not in ('information_schema','performance_schema','test') order by data_free_mb desc limit 10;
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key    | key_len | ref              | rows | filtered | extra                    |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+
| 1 | simple   | cat  | null    | index | primary      | name    | 194   | null             |  1 |  100.00 | using index; using temporary; using filesort |
| 1 | simple   | sch  | null    | ref  | primary,catalog_id | catalog_id | 8    | mysql.cat.id         |  6 |  50.00 | using where; using index           |
| 1 | simple   | tbl  | null    | ref  | schema_id     | schema_id | 8    | mysql.sch.id         |  52 |  100.00 | using where                 |
| 1 | simple   | ts  | null    | eq_ref | primary      | primary  | 8    | mysql.tbl.tablespace_id    |  1 |  100.00 | null                     |
| 1 | simple   | stat | null    | eq_ref | primary      | primary  | 388   | mysql.sch.name,mysql.tbl.name |  1 |  100.00 | null                     |
| 1 | simple   | col  | null    | eq_ref | primary      | primary  | 8    | mysql.tbl.collation_id    |  1 |  100.00 | using index                 |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------------+------+----------+----------------------------------------------+

以上就是详解mysql8.0​ 字典表增强的详细内容,更多关于mysql8.0​ 字典表增强的资料请关注其它相关文章!