mysql创建索引的代价

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;