mysql创建索引的代价

最新推荐文章于 2023-01-04 16:00:36 发布

carterslam

最新推荐文章于 2023-01-04 16:00:36 发布

阅读量335

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 索引数据库

本文链接：https://blog.youkuaiyun.com/carterslam/article/details/116452826

mysql索引并非万能，不到万不得已，不要随意建聚簇索引，建索引要牺牲存储空间

以下语句可以看到具体细节

select table_schema,table_name
,table_rows '总行数'
,concat(truncate(sum(data_length)/1024/1024,2),'M') as data_length_M
,concat(truncate(sum(index_length)/1024/1024,2),'M') as index_length_M
,concat( truncate(SUM(data_length)+SUM(index_length),2)/1024/1024,'M' ) as all_length_M
,SUM(data_length)+SUM(index_length) as all_length
from information_schema.tables
group by table_name
order by all_length desc;

也可以以G为单位查询

select table_schema,table_name
,table_rows '总行数'
,concat(truncate(sum(data_length)/1024/1024/1024,2),'G') as data_length_G
,concat(truncate(sum(index_length)/1024/1024/1024,2),'G') as index_length_G
,concat( truncate(SUM(data_length)+SUM(index_length),2)/1024/1024/1024,'G' ) as all_length_G
,SUM(data_length)+SUM(index_length) as all_length
from information_schema.tables
group by table_name
order by all_length desc;