这一节内容,整理一些管理 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 |
+--------------+--------+-----------+-------------+--------------+