information_schema有何用？ mysql8.0 之前的查询方式
会在查询information_schema 某个表时创建临时表
来自文件的元数据，扫描文件系统获取FRM文件的表定义
存储引擎的详细信息，例如动态表统计信息
来自MySQL服务器中全局数据结构的数据
在表的数量很多时，每次查询I_S会从文件系统中读取每个单独的FRM文件，使用更多的CPU周期来打开表并准备相关的内存数据结构
mysql8.0 开始的查询方式引入了基于InnoDB的本地数据字典表
表中不在有FRM表定义文件
所有数据库表的元数据都存储在事务数据字典表中
I_S中表被设计为数据字典表上的VIEW（有些还是临时表0_0）
消除了以下成本
查询INFORMATION_SCHEMA时创建的临时表
扫描文件系统目录以查找FRM文件
改进
利用MySQL优化器的全部功能，使用数据字典表上的索引来更好的查询
mysql5.7中表文件
ll test*
Jul 10 10:52 testse.frm
Jul 10 10:52 testse.ibd
mysql8.0中表文件
ll test*
Jul 10 10:25 testse.ibd
mysql5.7 和mysql8.0 I_S 中tables 表的存在方式
mysql5.7.22 show create table information_schema.tables\G *************************** 1. row *************************** Table: TABLES Create Table: CREATE TEMPORARY TABLE `TABLES` ( `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '', `ENGINE` varchar(64) DEFAULT NULL, mysql8.0.15 show create table information_schema.tables\G *************************** 1. row *************************** View: TABLES Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `mysql5.7 中I_S中tables表是以临时表的形式存在的（查询该表就会创建临时表，创建的临时表过多，可能会导致mysql占用的内存暴涨，出现OOM）
mysql8.0 中I_S中tables表以视图的形式存在（查询该视图，不会创建临时表，会使用到视图中表的索引）
mysql5.7中获取表大小情况
SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'testse'; +------------+-------------+--------------+------------+----------------+------------+---------------------+ | table_name | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | table_rows | update_time | +------------+-------------+--------------+------------+----------------+------------+---------------------+ | testse | 0.02M | 0.02M | 0.00G | 862 | 19 | 2019-07-10 10:52:02 | +------------+-------------+--------------+------------+----------------+------------+---------------------+ 执行计划中出现了where，未用到索引（存储引擎检索数据后，server层进行过滤） desc SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT(data_length + index_length / 1024 / 1024 / 1024, 2 ), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'testse'; +----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+ | 1 | SIMPLE | tables | NULL | ALL | NULL | TABLE_SCHEMA,TABLE_NAME | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned 0 databases | +----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+mysql8.0中获取表大小情况
SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'testse'; +------------+-------------+--------------+------------+----------------+------------+---------------------+ | TABLE_NAME | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | TABLE_ROWS | UPDATE_TIME | +------------+-------------+--------------+------------+----------------+------------+---------------------+ | testse | 0.02M | 0.02M | 0.00G | 862 | 19 | 2019-07-10 10:25:16 | +------------+-------------+--------------+------------+----------------+------------+---------------------+ 能使用到数据字典表的索引 desc SELECT table_name, CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size, CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size, CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH, table_rows, update_time FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'testse'; +----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+ | 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 | | 1 | SIMPLE | sch | NULL | eq_ref | PRIMARY,catalog_id | catalog_id | 202 | mysql.cat.id,const | 1 | 100.00 | Using index | | 1 | SIMPLE | tbl | NULL | eq_ref | schema_id | schema_id | 202 | mysql.sch.id,const | 1 | 100.00 | Using where | | 1 | SIMPLE | stat | NULL | const | PRIMARY | PRIMARY | 388 | const,const | 1 | 100.00 | NULL | | 1 | SIMPLE | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | NULL | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+ 测试5.7和8.0不同版本访问I_S库的性能