Mysql Innodb 表碎片整理

你的名字 2022-12-11 10:27 293阅读 0赞

**目录**

*  [一、为什么会产生碎片][Link 1]
 *  [二、Innodb 表碎片整理][Innodb]
    
     *  [1、查看表行数][1]
     *  [2、删除部分数据][2]
     *  [3、表数据碎片整理][3]

--------------------

[回到顶部][Link 2]

## 一、为什么会产生碎片 ##

简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

[回到顶部][Link 2]

## 二、Innodb 表碎片整理 ##

### 1、查看表行数 ###

1）行数

MariaDB \[agent\_platform\]> select count(1) from corporationprofit\_old;  
\+----------+  
| count(1) |  
\+----------+  
| 18198196 |  
\+----------+  
1 row in set (54.35 sec)

2）所占磁盘大小

9218180KB

cat test.sh   
a=\`ls /u02/mysql/data/agent\_platform/corporationprofit\_old\*|xargs  du|awk '\{print $1\}'\`  
m=0  
for i in $a  
do  
  echo $i  
  ((m=$m+$i))  
done  
echo $m

3）查看表的信息

### 2、删除部分数据 ###

1）总共删除了8157318行

MariaDB \[agent\_platform\]> delete from corporationprofit\_old  where date\_created>='2018-10-01' ;  
Query OK, 8157318 rows affected (2 min 52.18 sec)

2）查看表的行数

MariaDB \[agent\_platform\]> select count(1) from corporationprofit\_old;  
\+----------+  
| count(1) |  
\+----------+  
| 10040878 |  
\+----------+  
1 row in set (11.66 sec)

3）查看表的信息

MariaDB \[agent\_platform\]> select ROW\_FORMAT,TABLE\_ROWS,DATA\_LENGTH,INDEX\_LENGTH,MAX\_DATA\_LENGTH,DATA\_FREE,ENGINE   
    -> from information\_schema.TABLES where TABLE\_SCHEMA='agent\_platform' and TABLE\_NAME='corporationprofit\_old' limit 1;   
\+------------+------------+-------------+--------------+-----------------+------------+--------+  
| ROW\_FORMAT | TABLE\_ROWS | DATA\_LENGTH | INDEX\_LENGTH | MAX\_DATA\_LENGTH | DATA\_FREE  | ENGINE |  
\+------------+------------+-------------+--------------+-----------------+------------+--------+  
| Compact    |    9779701 |  5360599040 |   2138177536 |               0 | 4380950528 | InnoDB |  
\+------------+------------+-------------+--------------+-----------------+------------+--------+  
1 row in set (0.30 sec)

4）查看表删除后所占磁盘大小

9218180KB，没有改变

### 3、表数据碎片整理 ###

1）表数据整理

MariaDB \[agent\_platform\]> OPTIMIZE table corporationprofit\_old;  
\+--------------------------------------+----------+----------+-------------------------------------------------------------------+  
| Table                                | Op       | Msg\_type | Msg\_text                                                          |  
\+--------------------------------------+----------+----------+-------------------------------------------------------------------+  
| agent\_platform.corporationprofit\_old | optimize | note     | Table does not support optimize, doing recreate + analyze instead |  
| agent\_platform.corporationprofit\_old | optimize | status   | OK                                                                |  
\+--------------------------------------+----------+----------+-------------------------------------------------------------------+  
2 rows in set (5 min 34.79 sec)

2）查看表信息

MariaDB \[agent\_platform\]> select ROW\_FORMAT,TABLE\_ROWS,DATA\_LENGTH,INDEX\_LENGTH,MAX\_DATA\_LENGTH,DATA\_FREE,ENGINE   
    -> from information\_schema.TABLES where TABLE\_SCHEMA='agent\_platform' and TABLE\_NAME='corporationprofit\_old' limit 1;   
\+------------+------------+-------------+--------------+-----------------+-----------+--------+  
| ROW\_FORMAT | TABLE\_ROWS | DATA\_LENGTH | INDEX\_LENGTH | MAX\_DATA\_LENGTH | DATA\_FREE | ENGINE |  
\+------------+------------+-------------+--------------+-----------------+-----------+--------+  
| Compact    |    9863806 |  2996649984 |    854638592 |               0 |   6291456 | InnoDB |  
\+------------+------------+-------------+--------------+-----------------+-----------+--------+

3）产看整理后所占磁盘大小

3881468KB

碎片整理后，表的大小比之前减少了5GB。

[Link 1]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_label0
[Innodb]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_label1
[1]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_label1_0
[2]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_label1_1
[3]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_label1_2
[Link 2]: https://www.cnblogs.com/xibuhaohao/p/10216078.html#_labelTop