MySQL table 碎片整理

C&O

已于 2022-03-08 11:00:21 修改

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

于 2022-01-27 18:21:45 首次发布

本文链接：https://blog.csdn.net/qq_35298337/article/details/122716086

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1.查看MySQL系统库统计表的碎片信息

SELECT CONCAT(table_schema, '.', table_name)                   AS  TABLE_NAME
      ,engine                                                  AS  TABLE_ENGINE 
      ,table_type                                              AS  TABLE_TYPE
      ,table_rows                                              AS  TABLE_ROWS
      ,CONCAT(ROUND(data_length  / ( 1024 * 1024), 2), 'M')    AS  TB_DATA_SIZE 
      ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M')    AS  TB_IDX_SIZE 
      ,CONCAT(ROUND((data_length + index_length ) 
            / ( 1024 * 1024 ), 2), 'M')                        AS  TOTAL_SIZE
      ,CASE WHEN  data_length =0 THEN 0
            ELSE  ROUND(index_length / data_length, 2) END     AS  TB_INDX_RATE
    ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB')           AS  TB_DATA_FREE 
    ,CASE WHEN (data_length + index_length) = 0 THEN 0
             ELSE ROUND(data_free/(data_length + index_length),2) 
     END                                                       AS  TB_FRAG_RATE
FROM information_schema.TABLES  
ORDER BY data_free DESC;

2.整理碎片，节省磁盘空间，降低长事务SQL资源损耗，同时提高长事务SQL执行效能

在MySQL中，可以使用optimize table和alter table tablename ENGINE = INNODB这两种方法降低碎片，关于这两种整理碎片的方法介绍如下：

使用optimize table重新构建表和索引的物理存储内部结构，减少对存储空间使用、提升事物 SQL执行效率同时降低磁盘IO损耗。对使用不同存储引擎表所做的碎片整理，效率各不相同。

optimize table整理碎片支持表类型：INNODB,MYISAM, ARCHIVE，NDB。optimize table会重构表数据和索引的物理页，相较于减少表所占物理空间和执行事务SQL时降低IO有明显成效。optimize 操作会暂时锁表,等待表数据和索引物理页重构后,表锁才会被释放。数据量越大,整理碎片耗费的时间越长。optimize table后，表的变化取决于存储引擎类型，使用innodb引擎的表优化比较明显。

对于MyISAM存储引擎, optimize table的工作原理如下：

1.表有已删除的行或拆分行（split rows），修复该表。

2.索引页面有未排序的，重构索引结构进行排序。

3.表的统计信息不是最新的，无法通过索引进行重新排序来完成修复的，进行更新。

官方文档：

For MyISAM tables, OPTIMIZE TABLE works as follows:

1.If the table has deleted or split rows, repair the table.

2.If the index pages are not sorted, sort them.

3.If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

对于InnoDB而言，optimize table的工作原理如下:

对于Innodb表，optimize table解析为alter table ... force,重建表以更新索引统计信息并释放聚簇索引未使用的空间。当您在Innodb表上运行时，它会显示在optimize table的输出中，如下所示：

mysql> OPTIMIZE TABLE tb_chick;
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| Table    | Op       | Msg_type | Msg_text                                                          |
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| db_even.tb_chick | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 
| db_even.tb_chick | optimize | status   | OK