如下,当我们查询表碎片时:
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。
通常我们习惯通过以下手段解决此问题:
1、库表拆分,减少单实例打开文件数量
2、调整table_definition_cache和table_open_cache数量
3、添加物理内存
mysql 8.0 问世之后,又提供了一种选择,由于字典表采用innodb引擎,而且字典表可以使用索引。
下面的图解释了MySQL 5.7和8.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 字典表增强的资料请关注其它相关文章!
更多相关Mysql内容来自木庄网络博客
标签:Mysql
相关阅读 >>
php入门教程之使用mysqli操作数据库的方法(连接,查询,事务回滚等)
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。