MySQL中optimize优化表

当对MySQL进行大量的增删改操作的时候,很容易产生一些碎片,这些碎片占据着空间,所以可能会出现删除很多数据后,数据文件大小变化不大的现象。当然新插入的数据仍然会利用这些碎片。但过多的碎片,对数据的插入操作是有一定影响的,此时,我们可以通过optimize来对表的优化。
为了更加直观的看到数据碎片,Mysql可以使用如下命令查看

show table status [like table_name]
MySQL [live_v1]> show table status like 'course_visit_history' \G;
*************************** 1. row ***************************
           Name: course_visit_history
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 32
 Avg_row_length: 29
    Data_length: 928
Max_data_length: 8162774324609023
   Index_length: 2048
      Data_free: 368
 Auto_increment: NULL
    Create_time: 2018-04-26 17:17:35
    Update_time: 2018-12-07 11:46:40
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=FIXED
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

data_free选项代表数据碎片。

针对MySQL的不同数据库存储引擎,在optimize使用清除碎片,回收闲置的数据库空间,把分散存储(fragmented)的数据和索引重新挪到一起(defragmentation),对I/O速度有好处。
当然optimize在对表进行操作的时候,会加锁,所以不宜经常在程序中调用。

MyISAM存储引擎

针对MyISAM表,直接使用如下命令进行优化

optimize table table1[,table2][,table3]

如果同时优化多个表可以使用逗号分隔。
下面优化course_visit_history表,可以看出,优化后data_free值为0。

MySQL [live_v1]> optimize table course_visit_history;       
+---------------------------------+----------+----------+----------+
| Table                           | Op       | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------+
| j0_live_v1.course_visit_history | optimize | status   | OK       |
+---------------------------------+----------+----------+----------+
1 row in set (0.06 sec)

MySQL [live_v1]> show table status like 'course_visit_history' \G;
*************************** 1. row ***************************
           Name: course_visit_history
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 32
 Avg_row_length: 29
    Data_length: 928
Max_data_length: 8162774324609023
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-04-26 17:17:35
    Update_time: 2018-12-07 11:46:40
     Check_time: 2018-12-10 16:11:25
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=FIXED
        Comment: 
1 row in set (0.01 sec)

ERROR: 
No query specified

InnoDB存储引擎

InnoDB引擎的表分为独享表空间和同享表空间的表,我们可以通过show variables like 'innodb_file_per_table';来查看是否开启独享表空间。

MySQL [live_v1]> show variables like 'innodb_file_per_table'; 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

我本地是开启了独享表空间的。此时是无法对表进行optimize操作的,如果操作,会返回如下信息

MySQL [live_v1]> optimize table user;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| j0_live_v1.user | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| j0_live_v1.user | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.49 sec)

最后的一条Table does not support optimize, doing recreate + analyze instead。因为该结构下删除了大量的行,此时索引会重组并且会释放相应的空间因此不必优化。

赞(52) 打赏
未经允许不得转载:优客志 » 数据库
分享到:

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏