当对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。因为该结构下删除了大量的行,此时索引会重组并且会释放相应的空间因此不必优化。