MySQL中數(shù)據(jù)字典是數(shù)據(jù)庫(kù)重要的組成部分之一，INFORMATION_SCHEMA首次引入于MySQL 5.0，作為一種從正在運(yùn)行的MySQL服務(wù)器檢索元數(shù)據(jù)的標(biāo)準(zhǔn)兼容方式。用于存儲(chǔ)數(shù)據(jù)元數(shù)據(jù)、統(tǒng)計(jì)信息、以及有關(guān)MySQL server的訪問(wèn)信息（例如：數(shù)據(jù)庫(kù)名或表名，字段的數(shù)據(jù)類(lèi)型和訪問(wèn)權(quán)限等）。
8.0之前：
1、元數(shù)據(jù)來(lái)自文件
2、采用MEMORY表引擎
3、frm文件 存放表結(jié)構(gòu)信息
4、opt文件，記錄了每個(gè)庫(kù)的一些基本信息，包括庫(kù)的字符集等信息
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庫(kù)下，改為innodb表引擎，且被隱藏
3、information_schema下只能通過(guò)view查看
4、NULL的全部為view
5、存儲(chǔ)在單獨(dú)的表空間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有了一些改進(jìn)，但I(xiàn)NFORMATION_SCHEMA的性能仍然是我們?cè)S多用戶的主要痛點(diǎn)。在當(dāng)前INFORMATION_SCHEMA實(shí)現(xiàn)方式下產(chǎn)生的性能問(wèn)題背后的關(guān)鍵原因是，INFORMATION_SCHEMA表的查詢實(shí)現(xiàn)方式是在查詢執(zhí)行期間創(chuàng)建臨時(shí)表。
如下，當(dāng)我們查詢表碎片時(shí)：
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信息會(huì)有Open_full_table; Scanned all databases 。
Skip_open_table,Open_frm_only,Open_full_table這些值表示適用于INFORMATION_SCHEMA表查詢時(shí)對(duì)文件打開(kāi)的優(yōu)化；
- Skip_open_table：表文件不需要打開(kāi)。信息已經(jīng)通過(guò)掃描數(shù)據(jù)庫(kù)目錄在查詢中實(shí)現(xiàn)可用。
- Open_frm_only：只需要打開(kāi)表的.frm文件。
- Open_full_table：未優(yōu)化的信息查找。必須打開(kāi).frm、.MYD和.MYI文件。
- Scanned N databases：指在處理information_schema查詢時(shí)，有多少目錄需要掃描。
如果一個(gè)MySQL實(shí)例有上百個(gè)庫(kù)，每個(gè)庫(kù)又有上百?gòu)埍恚琁NFORMATION_SCHEMA查詢最終會(huì)從文件系統(tǒng)中讀取每個(gè)單獨(dú)的frm文件，造成很多I/O讀取。并且最終還會(huì)消耗更多的CPU來(lái)打開(kāi)表并準(zhǔn)備相關(guān)的內(nèi)存數(shù)據(jù)結(jié)構(gòu)。它也確實(shí)會(huì)嘗試使用MySQL server層的表緩存（系統(tǒng)變量table_definition_cache ），但是在大型實(shí)例中，很少有一個(gè)足夠大的表緩存來(lái)容納所有的表。所以內(nèi)存使用量會(huì)急劇上升，甚至出現(xiàn)oom。
通常我們習(xí)慣通過(guò)以下手段解決此問(wèn)題：
1、庫(kù)表拆分，減少單實(shí)例打開(kāi)文件數(shù)量
2、調(diào)整table_definition_cache和table_open_cache數(shù)量
3、添加物理內(nèi)存
mysql 8.0 問(wèn)世之后，又提供了一種選擇，由于字典表采用innodb引擎，而且字典表可以使用索引。
下面的圖解釋了MySQL 5.7和8.0設(shè)計(jì)上的區(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 字典表增強(qiáng)的詳細(xì)內(nèi)容，更多關(guān)于MySQL8.0 字典表增強(qiáng)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- Mysql用戶忘記密碼及密碼過(guò)期問(wèn)題的處理方法
- 詳解MySQL的用戶密碼過(guò)期功能
- mysql密碼過(guò)期導(dǎo)致連接不上mysql
- MySQL8.0中的降序索引
- Docker 部署 Mysql8.0的方法示例
- MySQL8.0中binlog的深入講解
- MySQL8.0 如何快速加列
- mysql8.0.21安裝教程圖文詳解
- Windows系統(tǒng)下MySQL8.0.21安裝教程(圖文詳解)
- MySQL8.0.21.0社區(qū)版安裝教程(圖文詳解)
- 詳解MySQL8.0 密碼過(guò)期策略
