MySQL 5.7中常用的统计数据库、数据表语句（统计数据表行数、占用文件空间大小等信息）

艺帆风顺 发布于 2025-04-03 20 次阅读

1、查询数据库中各个表的总行数

    SELECT    TABLE_NAME,    TABLE_ROWS  FROM    INFORMATION_SCHEMA.TABLES  WHERE    TABLE_SCHEMA= 'vehicle_s2';

    统计总行数，并按照数据量排序：

    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;

    2、查看数据表行数、索引长度及数据长度信息内容

      SELECT table_name AS '表名', table_rows AS '行数', data_length AS '数据长度', index_length AS '索引长度', data_free AS '未使用空间'FROM information_schema.tablesWHERE table_schema = 'vehicle_s2';

      3、查看数据各表的表空间文件大小

        SELECT  table_name AS '表名',  ROUND((data_length + index_length) / 1024 / 1024, 2) AS '表空间大小(MB)'FROM  information_schema.tables WHERE  table_schema = 'vehicle_s2';

        4、查看所有业务库

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

        5、查看所有用户数

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

          +------------------------------+| user |+------------------------------+| 'fim'@'%'; || 'root'@'%'; || 'zabbix'@'%'; || 'zbx_monitor'@'%'; || 'mysql.session'@'localhost'; || 'mysql.sys'@'localhost'; || 'root'@'localhost'; |+------------------------------+7 rows in set (0.02 sec)