如果我们想要大概知道某个表的数据有多少的时候,除了使用select count(*) from test_delete_tmp;还有其他方式吗?
使用explain命令
笔者也是某次无意中发现,通过explain可以秒得结果,虽然结果不一定正确,但是速度快呀。
如下:
mysql> explain select * from test_delete_tmp;
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | test_delete_tmp | ALL | NULL | NULL | NULL | NULL | 100245 | |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------+
其中,rows就是当前查询需要读取的行数。读者可以试试,count(*)会使用很久,explain则瞬间得到结果。
查询所有数据库占用磁盘空间大小
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;
查询单个库中所有表磁盘占用大小
select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'test2'
group by TABLE_NAME
order by data_length desc;