MySQL中數(shù)據(jù)字典是數(shù)據(jù)庫重要的組成部分之一，INFORMATION_SCHEMA首次引入于MySQL 5.0，作為一種從正在運行的MySQL服務器檢索元數(shù)據(jù)的標準兼容方式。用于存儲數(shù)據(jù)元數(shù)據(jù)、統(tǒng)計信息、以及有關MySQL server的訪問信息（例如：數(shù)據(jù)庫名或表名，字段的數(shù)據(jù)類型和訪問權(quán)限等）。

8.0之前：

1、元數(shù)據(jù)來自文件

2、采用MEMORY表引擎

3、frm文件 存放表結(jié)構(gòu)信息

4、opt文件，記錄了每個庫的一些基本信息，包括庫的字符集等信息

5、.TRN，.TRG文件用于存放觸發(fā)器的信息內(nèi)容

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之后：

1、元數(shù)據(jù)存在表中

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實現(xiàn)方式下產(chǎn)生的性能問題背后的關鍵原因是，INFORMATION_SCHEMA表的查詢實現(xiàn)方式是在查詢執(zhí)行期間創(chuàng)建臨時表。

如下，當我們查詢表碎片時：

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表查詢時對文件打開的優(yōu)化；

Skip_open_table：表文件不需要打開。信息已經(jīng)通過掃描數(shù)據(jù)庫目錄在查詢中實現(xiàn)可用。 Open_frm_only：只需要打開表的.frm文件。 Open_full_table：未優(yōu)化的信息查找。必須打開.frm、.MYD和.MYI文件。 Scanned N databases：指在處理information_schema查詢時，有多少目錄需要掃描。

如果一個MySQL實例有上百個庫，每個庫又有上百張表，INFORMATION_SCHEMA查詢最終會從文件系統(tǒng)中讀取每個單獨的frm文件，造成很多I/O讀取。并且最終還會消耗更多的CPU來打開表并準備相關的內(nèi)存數(shù)據(jù)結(jié)構(gòu)。它也確實會嘗試使用MySQL server層的表緩存（系統(tǒng)變量table_definition_cache ），但是在大型實例中，很少有一個足夠大的表緩存來容納所有的表。所以內(nèi)存使用量會急劇上升，甚至出現(xiàn)oom。

通常我們習慣通過以下手段解決此問題：

1、庫表拆分，減少單實例打開文件數(shù)量

2、調(diào)整table_definition_cache和table_open_cache數(shù)量

3、添加物理內(nèi)存

mysql 8.0 問世之后，又提供了一種選擇，由于字典表采用innodb引擎，而且字典表可以使用索引。

下面的圖解釋了MySQL 5.7和8.0設計上的區(qū)別：

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​ 字典表增強的詳細內(nèi)容，更多關于MySQL8.0​ 字典表增強的資料請關注好吧啦網(wǎng)其它相關文章！