MySQL常用的统计语句

这一节内容，整理一些管理 MySQL 会经常用到的统计语句，比如表的碎片率、非 InnoDB 的表、所有用户和所有业务库等。

1 查看所有业务库

select schema_name from information_schema.schemata where schema_name not in ('information_schema','sys','mysql','performance_schema');

注意：

information_schema 中的数据默认不是实时的数据，如果需要实时的数据，需要执行下面命令：

SET GLOBAL information_schema_stats_expiry=0;

2 批量操作某个前缀的表

select concat('select count(*) from martin.',table_name,';') from information_schema.tables where table_schema='martin' and table_name like 'a_%';

效果如下：

+-------------------------------------------------------+| concat('select count(*) from martin.',table_name,';') |+-------------------------------------------------------+| select count(*) from martin.a_01;                     || select count(*) from martin.a_02;                     |+-------------------------------------------------------+

3 查找业务库中的非 InnoDB 的表

select table_schema,table_name,engine from information_schema.tables  where table_schema not in('information_schema','sys','mysql','performance_schema') and  engine<>'InnoDB';

4 批量构造修改存储引擎的语句

select distinct concat('alter table ',table_schema,'.',table_name,' engine=innodb',';') from information_schema.tables where (engine <> 'innodb' and table_schema not in ('information_schema','sys','mysql','performance_schema'));

效果如下：

+-------------------------------------------------------------------------+| concat('alter table ',table_schema,'.',table_name,' engine=innodb',';') |+-------------------------------------------------------------------------+| alter table martin.b_myisam engine=innodb;                              |+-------------------------------------------------------------------------+1 row in set (1.53 sec)

5 查看每张表数据量，并按数据量排序

select table_schema,table_name, table_rows from information_schema.tables where table_schema not in ('information_schema','sys','mysql','performance_schema') order by table_rows desc;

效果如下：

+--------------+--------------+------------+| TABLE_SCHEMA | TABLE_NAME   | TABLE_ROWS |+--------------+--------------+------------+| martin       | student_info |          8 || martin       | a_02         |          2 || martin       | a_01         |          0 || martin       | b_myisam     |          0 |+--------------+--------------+------------+4 rows in set (0.50 sec)

注意：该命令统计的数据量为估值。

6 某个库所有表的字段详情

select table_schema,table_name,column_name,column_type,collation_name from information_schema.columns  where table_schema='martin';

效果如下：

+--------------+--------------+--------------+-------------------+--------------------+| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_TYPE | COLLATION_NAME |+--------------+--------------+--------------+-------------------+--------------------+| martin | a_01 | id | int | NULL || martin | a_01 | stu_name | varchar(10) | utf8mb4_0900_ai_ci || martin | a_01 | stu_class | varchar(10) | utf8mb4_0900_ai_ci || martin | a_01 | stu_num | int | NULL |......

7 某个库中所有表详情

select table_schema,table_name,engine,table_collation from information_schema.tables where table_schema='martin';

8 查看某张表的具体信息

select * from information_schema.tables where table_schema='martin' and table_name='student_info'\G

效果如下：

  TABLE_CATALOG: def   TABLE_SCHEMA: martin     TABLE_NAME: student_info     TABLE_TYPE: BASE TABLE         ENGINE: InnoDB        VERSION: 10     ROW_FORMAT: Dynamic     TABLE_ROWS: 8 AVG_ROW_LENGTH: 2048    DATA_LENGTH: 16384MAX_DATA_LENGTH: 0   INDEX_LENGTH: 49152      DATA_FREE: 0 AUTO_INCREMENT: 13    CREATE_TIME: 2022-05-05 20:38:21    UPDATE_TIME: 2022-05-25 01:51:18     CHECK_TIME: NULLTABLE_COLLATION: utf8mb4_0900_ai_ci       CHECKSUM: NULL CREATE_OPTIONS:   TABLE_COMMENT: 学生信息表1 row in set (0.46 sec)

9 查看 MySQL 所有用户

select distinct concat("'",user,'''@''',host,"';") as user from mysql.user;

效果如下：

+---------------------------------+| user                            |+---------------------------------+| 'mysql.infoschema'@'localhost'; || 'mysql.session'@'localhost';    || 'mysql.sys'@'localhost';        || 'root'@'localhost';             |+---------------------------------+4 rows in set (0.03 sec)

这种结果就很方便执行 show grants，比如下面的：

show grants for 'root'@'localhost';

10 查看某个库所有表的碎片率

select table_name,data_free / (data_free + data_length + index_length) as aaa,data_free,data_length,index_length from information_schema.tables where table_schema = 'martin' order by aaa desc;

效果如下：

+--------------+--------+-----------+-------------+--------------+| TABLE_NAME   | aaa    | DATA_FREE | DATA_LENGTH | INDEX_LENGTH |+--------------+--------+-----------+-------------+--------------+| a_01         | 0.0000 |         0 |       16384 |        49152 || a_02         | 0.0000 |         0 |       16384 |        49152 || b_myisam     | 0.0000 |         0 |           0 |         1024 || student_info | 0.0000 |         0 |       16384 |        49152 |+--------------+--------+-----------+-------------+--------------+