数据库 mysql 库表以及索引大小查询

待我称王封你为后i 2023-01-05 12:12 157阅读 0赞

# 数据库 mysql 库表以及索引大小查询 #

数据常见的一些参数，比如库大小，表大小，索引大小等指标有助于我们了解数据库，更好的使用数据库及优化。

## 1、工具查看 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70][]

## 2、命令查看 ##

-- 数据库 mysql 库表以及索引大小查询
    
    -- 查看指定库的大小
    SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA  FROM TABLES WHERE table_schema='testschema';
    -- 查看指定库的指定表的大小
    SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA  FROM TABLES WHERE table_schema='testschema' AND table_name='t_syt_keyword_off_rep';
    -- 查看指定库的索引大小
    SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'testschema'; 
    -- 查看指定库的指定表的索引大小
    SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'testschema' AND table_name='tab_user'; 
    
    
    -- 1.查看指定库数据和索引大小总和
    SELECT CONCAT(ROUND(SUM((data_length+index_length)/1024/1024),2),'MB') AS total_data FROM information_schema.TABLES WHERE table_schema = 'testschema';
    
    -- 2.查看所有库数据和索引大小总和
    SELECT CONCAT(ROUND(SUM((data_length+index_length)/1024/1024),2),'MB') AS total_data FROM information_schema.TABLES;
    
    -- 3.查看指定数据库的某个表
    SELECT CONCAT(ROUND(SUM((data_length+index_length)/1024/1024),2),'MB') AS DATA FROM TABLES WHERE table_schema='testschema' AND table_name='tab_user';
    
    -- 4.查询一个库中每个表的数据大小，索引大小和总大小
    SELECT
    CONCAT(a.table_schema,'.',a.table_name),
    CONCAT(ROUND(table_rows/1000,4),'KB') AS 'Number of Rows',
    CONCAT(ROUND(data_length/(1024*1024),4),',') AS 'data_size',
    CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'index_size',
    CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total'
    FROM
    information_schema. TABLES a
    WHERE
    a.table_schema = 'testschema';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]: /images/20221119/785bf4365afd402396705d2f41d3675e.png