第一篇

1. 获取所有表结构(TABLES)

SELECT  *  FROM information_schema.TABLES WHERE  TABLE_SCHEMA='数据库名';  TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。各字段说明如下:

2、详细说明

row_format

若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。

 row_format还有其他一些值：

DEFAULT | FIXED | DYNAMIC | COMPRESSED | REDUNDANT | COMPACT

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：

fixed--->dynamic: 这会导致CHAR变成VARCHAR

dynamic--->fixed: 这会导致VARCHAR变成CHAR

data_free

每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

1.查询数据库空间碎片：

select table_name,data_free,engine from information_schema.tables where table_schema='yourdatabase';

2.对数据表优化：

optimeze table `table_name`;

参考：

http://wenku.baidu.com/link?url=MtPZrab7kbciXsBAjia4w0JUE3aFCtOj9fu_2zXVE5JW6k8UHaFCl6ppGE89HPMUFmLSMTjmp2rqbIMcSXBIJ11LIlxzDYJH1qLHZpNdqYu

http://blog.sina.com.cn/s/blog_70b9a0e90101cmdz.html

http://www.2cto.com/database/201208/144994.html

第二篇

回到顶部

详细说明:

row_format

若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。

所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。

row_format

还有其他一些值：

DEFAULT | FIXED | DYNAMIC | COMPRESSED | REDUNDANT | COMPACT

修改行格式

ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：

fixed--->dynamic: 这会导致CHAR变成VARCHAR

dynamic--->fixed: 这会导致VARCHAR变成CHAR

data_free

每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

1.查询数据库空间碎片：

select table_name,data_free,engine from information_schema.tables where table_schema='yourdatabase';

2.对数据表优化：

optimize table `table_name`;

第三篇

概述

---

 

information_schema.tables信息；

1. use information_schema;
2. show create table tables;

1.desc tables;

 

---

查询所有的数据库信息

1. select distinct TABLE_SCHEMA from tables ;

---

查询数据库和数据表信息

显示mysql数据库下面的所有表信息：（共对比使用）

1. use mysql;
2. show tables;

 

 

通过information_schema.table获取数据库和数据表信息：

1. use information_schema;
2. select TABLE_SCHEMA ,table_name from tables where table_schema like 'mysql';

---

数据表大小以及索引大小

示例1：mysql.time_zone相关表

 

 

 

 

获取time_zone相关表的大小：

1. select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema='mysql' and table_name like 'time_%';

 

示例2： 获取指定数据库的大小；

1. select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema='mysql';

---

判断myisam数据表是否已压缩

1. select distinct row_format,engine from information_schema.tables where engine='myisam';

• Fixed: 表示已压缩；
• Dynamic：表示未压缩；

 

1. select row_format,engine,table_name from information_schema.tables where engine='myisam';

---

通过Linux指令直接获取数据库和数据表信息：

1. mysql -uroot -pxxxx -D information_schema -e "select TABLE_SCHEMA ,table_name from tables where table_schema like 'hsm_syslog_%'"

参数说明：

• 
• -D：表示数据库名称 ；
• -e：表示需要执行的指令：；

mysql使用information_schema.tables统计表的行数，统计结果和count(*)的结果不一样。

select table_name,table_rows from information_schema.tables 
where TABLE_SCHEMA = 'qyqdb' 
order by table_rows desc; 

经查询：information_schema.tables 对于InnoDB表，table_rows行计数仅是大概估计值，不准确。

mysql使用select count(*) from table_name可以查询某个表的总记录数。比较准确！
想快速的知道数据库中所有表的记录数信息怎么办？

另外一种办法还是借助information_schema库的tables表，来拼接出一个条sql语句，例如：

统计qyqdb数据库下所有的表的行数，生产统计语句。
select concat(
    'select "', 
    TABLE_name, 
    '", count(*) from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all'
) from information_schema.tables 
where TABLE_SCHEMA='qyqdb';

把生成的结果手动加工一下。

举例如下：

统计bigData_1数据库下所有表的行数：

select concat(
    'select "', 
    TABLE_name, 
    '", count(*) from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all'
) from information_schema.tables 
where TABLE_SCHEMA in ('bigData_1');

结果：
+------------------------------------------------------------------------------------------------------------------------------------+
| concat(
    'select "', 
    TABLE_name, 
    '", count(*) from ', 
    TABLE_SCHEMA, 
    '.',
    TABLE_name,
    ' union all'
) |
+------------------------------------------------------------------------------------------------------------------------------------+
| select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all                                          |
| select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all                                      |
| select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all                                                        |
| select "AA_cert_month", count(*) from bigdata_1.AA_cert_month union all                                                    |
+------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> 

对以上输出结果进行修改，如下：

select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all                                          
select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all
select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all                                                        
select "AA_cert_month", count(*) from bigdata_1.AA_cert_month

输出结果如下：
mysql> select "AA_cert_action_day", count(*) from bigdata_1.AA_cert_action_day union all                                          
    -> select "AA_cert_action_month", count(*) from bigdata_1.AA_cert_action_month union all
    -> select "AA_cert_day", count(*) from bigdata_1.AA_cert_day union all                                                        
    -> select "AA_cert_month", count(*) from bigdata_1.AA_cert_month
    -> ;
+--------------------------+----------+
| report_cert_action_day   | count(*) |
+--------------------------+----------+
| AA_cert_action_day   |      168 |
| AA_cert_action_month |      131 |
| AA_cert_day          |       82 |
| AA_cert_month        |       39 |
+--------------------------+----------+
4 rows in set (0.00 sec)

mysql> 

在MySQL8.0以前，通常会通过infomation_schema的表来获取一些元数据，例如从tables表中获取表的下一个auto_increment值，从indexes表获取索引的相关信息等。

但在MySQL8.0去查询这些信息的时候，出现了不准确的情况。例如auto_increment，

--此时test表的auto_increment是204
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

--从information_schema.tables查出test表的auto_increment是204，这时tables表信息是准确的
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';                                   
+----------------+
| AUTO_INCREMENT |
+----------------+
|            204 |
+----------------+
1 row in set (0.01 sec)
--将test表的auto_increment修改为300
mysql> alter table test auto_increment=300;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

--查询tables表，发现auto_increment仍然是204；在MySQL8.0以前，这时tables表的auto_increment应该是显示最新值300的
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|            204 |
+----------------+
1 row in set (0.00 sec)

--向test表插入数据，应用最新的auto_increment
mysql> insert into test values();
Query OK, 1 row affected (0.02 sec)

--检查test表的最大值，确实是300
mysql> select max(id) from test;
+---------+
| max(id) |
+---------+
|     300 |
+---------+
1 row in set (0.00 sec)

--test表插入操作以后，再次查询tables表，auto_increment值仍然是204
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|            204 |
+----------------+
1 row in set (0.00 sec)

又例如一个表的更新时间

--从tables表看到test表上一次更新时间是2018-11-29 09:12:48
mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test';              
+---------------------+
| UPDATE_TIME         |
+---------------------+
| 2018-11-29 09:12:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2018-11-29 09:21:49 |
+---------------------+
1 row in set (0.00 sec)

--对test表插入数据，这时test表的update_time应该是当前时间
mysql> insert into test values();
Query OK, 1 row affected (0.09 sec)

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2018-11-29 09:22:02 |
+---------------------+
1 row in set (0.00 sec)

--但从tables表查询到update_time仍然没更新
mysql> select update_time from information_schema.tables where table_schema='test' and table_name='test';
+---------------------+
| UPDATE_TIME         |
+---------------------+
| 2018-11-29 09:12:48 |
+---------------------+
1 row in set (0.00 sec)

从以上例子可以看出，MySQL8.0的tables表变得不可靠了。前面文章有说到，MySQL8.0里，tables不再是某个引擎表，而是改造成了视图。再仔细看一下tables视图的定义

select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,
if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,
internal_table_rows(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `TABLE_ROWS`,
internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `AVG_ROW_LENGTH`,
internal_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_LENGTH`,
internal_max_data_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `MAX_DATA_LENGTH`,
internal_index_length(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `INDEX_LENGTH`,
internal_data_free(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `DATA_FREE`,
internal_auto_increment(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`) AS `AUTO_INCREMENT`,
`tbl`.`created` AS `CREATE_TIME`,
internal_update_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `UPDATE_TIME`,
internal_check_time(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECK_TIME`,
`col`.`name` AS `TABLE_COLLATION`,internal_checksum(`sch`.`name`,`tbl`.`name`,if(isnull(`tbl`.`partition_type`),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0)) AS `CHECKSUM`,
if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1))) AS `CREATE_OPTIONS`,
internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` 
from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) 
join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) 
left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) 
left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) 
left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) 
where (can_access_table(`sch`.`name`,`tbl`.`name`) and is_visible_dd_object(`tbl`.`hidden`))

可以看到，auto_increment和update_time列均引用自mysql.table_stats表。那么tables视图的信息不准确，根本原因就是table_stats表的统计信息并没有实时更新。

解决统计信息过旧的问题，从以往的经验来看，当表数据更新占比达到一定数值，就会触发统计信息收集。所以尝试了不断插入和更新test表，但tables视图的信息仍然是不准确的，也就说明table_stats的统计信息根本没有更新。

终极的手段当然是使用analyze table命令去人为的触发表信息收集，tables视图的信息会更新至当前准确的状态。

但如果总是要analyze table命令去人为更新才能得到真实的数据，那么tables表存在的意义何在？

对此，做一番研究。

原来在MySQL8.0，数据字典方面做了不少的改动。本文就不详细介绍所有的知识点，后续文章中再讲述。针对tables视图等不准确的情况，其实是跟数据字典表和其数据缓存有关系。

数据字典有很多相关的表，但这些表是不可见的。既不能通过select来获取表数据，也不能通过show tables看到它的踪影，同样也不会出现在information_schema.tables的table_name范畴里。例如跟库表有关的数据字典tables（注意，此tables跟information_schema.tables并不是同一个东西），在归属于mysql库下，但就算有火眼金睛也不能让它显形：

mysql> use mysql;
Database changed
mysql> show tables like 'table';
Empty set (0.01 sec)

但是，大部分数据字典表会有相关的视图来获取它的数据，例如tables表相关的视图是information_schema.tables，当然，从information_schema.tables的定义看，也不是一对一的关系，其中还包含其他表的数据。

数据字典表用来做什么呢，还记得.frm，db.opt这些文件吗？在MySQL8.0里，你会发现这些文件都没有了。原本记录在这些文件中的元数据，现在记录就记录在数据字典表里，而数据字典表集中存在一个单独的innodb表空间中，系统文件名为mysql.ibd，也就是说，元数据不再是直接在.frm等文件上读写，而是存在存储引擎上。

为了最小化磁盘IO，MySQL8.0增加了一个字典对象缓存（dictionary object cache）。同时为了提高information_schema的查询效率，statistics和tables字典表的数据缓存在字典对象缓存中，并且有一定的保留时间，如果没超过保留时间，即使是实例重启，缓存中的信息也不会更新，只有超过了保留时间，才会到存储引擎里抓取最新的数据。同时，字典对象缓存采用LRU的方式来管理缓存空间。

那么到这里，information_schema.tables视图不准确的疑问就解开了，原因即是字典对象缓存中统计信息并没有更新，那么怎么解决呢？可以通过设置information_schema_stats_expiry为0来使字典对象缓存实时更新，该参数默认值为86400，即24小时。

问题解决了，那么来捋一捋，都有哪些情况下，字典缓存中索引和表的统计信息不会自动更新呢？
1.缓存中统计信息还没过期；
2.information_schema_stats_expiry没设成0；
3.当实例在read_only相关模式下运行；
4.当查询同时获取performance schema的数据。

针对第一二点，可以通过设置set global information_schema_stats_expiry=0来解决，也可以仅在会话级设置；针对以上问题，除了第三点，都可以通过analyze table来解决。