管理MySQL常用的统计语句

悦专栏 2022-05-25

205

这一节内容，整理一些管理 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: 16384
MAX_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: NULL
TABLE_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 |
+--------------+--------+-----------+-------------+--------------+