mysql 表优化

1、检查表空间碎片率的2种方法：

方法一、空间碎片率 frag_pct

SELECT 
    table_schema, 
    table_name,  
    concat(round(data_length/1024/1024, 2), 'MB') as data_length_MB, 
    concat(round(data_free/1024/1024, 2), 'MB') as data_free_MB,
    CONCAT(ROUND((data_free / data_length) * 100, 2), '%') AS frag_pct,
    concat(round(INDEX_LENGTH/1024/1024, 2), 'MB') as index_MB,
    table_rows,
    TABLE_COMMENT
FROM 
    information_schema.tables
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND engine = 'InnoDB'
ORDER BY 
    frag_pct DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

方法二、碎片率 = data_free / Data_length

碎片率需要自己算

SHOW TABLE STATUS LIKE '你的表名';
1

2、 分析索引

SHOW INDEX FROM 你的表名;
1

字段解释:

• Table: 表的名称。
• Non_unique: 索引的唯一性。0 表示唯一索引，1 表示非唯一索引。
• Key_name: 索引的名称。
• Seq_in_index: 索引中的列序列号，从1开始。
• Column_name: 列的名称，即索引所包含的列。
• Collation: 列在索引中的排序顺序。A表示升序，D表示降序，NULL表示未定义。
• Cardinality: 索引的基数，即索引中唯一值的估计数。用于优化查询计划。
• Sub_part: 如果索引只部分使用列，则显示被索引的字符数。NULL表示整个列被索引。
• Packed: 如果列被压缩，显示如何压缩。否则为NULL。
• Null: 列是否可以包含NULL值。YES表示可以，NO表示不可以。
• Index_type: 索引的类型（例如，BTREE、FULLTEXT、HASH）。
• Comment: 任何相关的注释。
• Index_comment: 索引的注释。
• Visible: 索引的可见性，YES表示可见，NO表示不可见。

3、优化表空间的2个方法

注意：会锁表，业务低峰期操作！！

optimize table 你的表名;
1

ALTER TABLE 你的表名 ENGINE=InnoDB;
1

4、需要优化的常见情形

• 碎片率较高，超过10%或20%
• 表的性能显著下降
• 表的数据量或索引发生了较大变化
• 频繁修改（或删除数据）的表