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 |+--------------+--------+-----------+-------------+--------------+
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

萧木易

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值