合 MySQL查询前几张大表
👉 本文共约120个字，系统预计阅读时间或需1分钟。
目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | SELECT table_schema AS '数据库', table_name AS '表名', a.TABLE_TYPE, a.ENGINE, a.CREATE_TIME, a.UPDATE_TIME, a.TABLE_COLLATION, table_rows AS '记录数', TRUNCATE(a.DATA_LENGTH / 1024 / 1024, 2 ) AS '数据容量(MB)', TRUNCATE( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)', TRUNCATE( ( data_length + index_length ) / 1024 / 1024, 2 ) AS '总大小(MB)', TRUNCATE( a.DATA_FREE / 1024 / 1024, 2 ) AS '空闲空间(MB)', truncate(f.filesize_M,2) AS '磁盘文件大小(MB)' FROM information_schema.TABLES a left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, substring(b.file_name,locate('/',b.file_name,3)+1,(LENGTH(b.file_name)-locate('/',b.file_name,3)-4)) as tb_name, b.file_name, (total_extents*extent_size)/1024/1024 filesize_M from information_schema.FILES b order by filesize_M desc limit 20 ) f on ( a.TABLE_SCHEMA= f.db_name and a.TABLE_NAME=f.tb_name ) ORDER BY ( data_length + index_length ) DESC LIMIT 10; MySQL [tpcc]> SELECT -> table_schema AS '数据库', -> table_name AS '表名', -> a.TABLE_TYPE, -> a.`ENGINE`, -> a.CREATE_TIME, -> a.UPDATE_TIME, -> a.TABLE_COLLATION, -> table_rows AS '记录数', -> TRUNCATE(a.DATA_LENGTH / 1024 / 1024, 2 ) AS '数据容量(MB)', -> TRUNCATE( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)', -> TRUNCATE( ( data_length + index_length ) / 1024 / 1024, 2 ) AS '总大小(MB)', -> TRUNCATE( a.DATA_FREE / 1024 / 1024, 2 ) AS '空闲空间(MB)', -> truncate(f.filesize_M,2) AS '磁盘文件大小(MB)' -> FROM information_schema.TABLES a -> left outer join -> (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, -> substring(b.file_name,locate('/',b.file_name,3)+1,(LENGTH(b.file_name)-locate('/',b.file_name,3)-4)) as tb_name, -> b.file_name, -> (total_extents*extent_size)/1024/1024 filesize_M -> from information_schema.FILES b -> order by filesize_M desc limit 20 ) f -> on ( a.TABLE_SCHEMA= f.db_name and a.TABLE_NAME=f.tb_name ) -> ORDER BY( data_length + index_length ) DESC -> LIMIT 10; +--------+----------------------+------------+--------+---------------------+-------------+--------------------+---------+--------------+--------------+------------+--------------+------------------+ | 数据库 | 表名 | TABLE_TYPE | ENGINE | CREATE_TIME | UPDATE_TIME | TABLE_COLLATION | 记录数 | 数据容量(MB) | 索引容量(MB) | 总大小(MB) | 空闲空间(MB) | 磁盘文件大小(MB) | +--------+----------------------+------------+--------+---------------------+-------------+--------------------+---------+--------------+--------------+------------+--------------+------------------+ | tpcc | stock | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 574749 | 210.84 | 0.00 | 210.84 | 4.00 | 220.00 | | tpcc | order_line | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 1953661 | 186.92 | 0.00 | 186.92 | 4.00 | 196.00 | | tpcc | customer | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 170574 | 112.78 | 13.56 | 126.34 | 5.00 | 136.00 | | tpcc | orders | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 214828 | 13.54 | 8.50 | 22.04 | 4.00 | 30.00 | | tpcc | history | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 198901 | 16.51 | 0.00 | 16.51 | 4.00 | 24.00 | | tpcc | item | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 99798 | 9.51 | 0.00 | 9.51 | 4.00 | 17.00 | | mysql | time_zone_transition | BASE TABLE | InnoDB | 2020-08-05 09:59:22 | NULL | utf8_general_ci | 125248 | 4.51 | 0.00 | 4.51 | 4.00 | 12.00 | | tpcc | new_order | BASE TABLE | InnoDB | 2021-01-19 21:26:51 | NULL | latin1_swedish_ci | 55906 | 3.26 | 0.00 | 3.26 | 4.00 | 11.00 | | db1 | sbtest5 | BASE TABLE | InnoDB | 2021-01-27 09:02:51 | NULL | utf8mb4_general_ci | 9680 | 2.51 | 0.26 | 2.78 | 4.00 | 10.00 | | sbtest | sbtest9 | BASE TABLE | InnoDB | 2021-02-05 15:52:24 | NULL | utf8mb4_general_ci | 9936 | 2.51 | 0.21 | 2.73 | 4.00 | 10.00 | +--------+----------------------+------------+--------+---------------------+-------------+--------------------+---------+--------------+--------------+------------+--------------+------------------+ 10 rows in set (0.32 sec) |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
