MySQL表情况概览

发表于 | 分类于 ， | 浏览

常用语句

1
show table status like '%table_x';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 SELECT
    table_name,
    engine,
    row_format,
    table_rows,
    avg_row_length AS avg_row,
    ROUND((data_length + index_length) / 1024 / 1024,
            2) AS total_mb,
    ROUND((data_length) / 1024 / 1024, 2) AS data_mb,
    ROUND((index_length) / 1024 / 1024, 2) AS index_mb
FROM
    information_schema.tables
WHERE
    table_schema = 'xxx'
AND table_name = 'yyy';
1
show index from table_x;
1
desc table_x;
1
show create table table_x;
1
SELECT COUNT(DISTINCT xxx)/COUNT(*) AS xxx_selectivity,COUNT(*) AS total  FROM table_x;

参考

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

order优化：https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

groupby优化：https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html