MYSQL中OPTIMIZE TABLE优化使用技巧
发布时间：2022-06-20 13:20:07 所属栏目：MySql教程 来源：互联网
导读：Optimize Table是mysql中一个可以及回收更多的空间、减少碎片 （defragment）命令,但有些朋友说可以提升mysql性能这个我具体不清楚,下面找了一些相关文章大家参考一下. OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时，所占据的磁盘空间并
|
Optimize Table是mysql中一个可以及回收更多的空间、减少“碎片” （defragment）命令,但有些朋友说可以提升mysql性能这个我具体不清楚,下面找了一些相关文章大家参考一下. OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。 多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期,每周一次或每月一次,进行一次数据表优化操作即可,只对那些特定的表运行. 先了解一下OPTIMIZE TABLE对InnoDB 和 MyISAM相关知识 1. InnoDB 和 MyISAM 目前支持optimize命令的引擎有 MyISAM, InnoDB, and ARCHIVE，对于InnoDB，会将optimize命令映射为ALTER TABLE命令，该命令会重建数据表，更新索引统计信息、回收主键索引中空间。 2. InnoDB 和 MyISAM 如果你的MySQL是有备库的,如果你只希望在主库上执行的话,那么可以加上关键字NO_WRITE_TO_BINLOG,或者LOCAL,意思完全相同. OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE 这对于MM结构的MySQL数据库尤为重要,因为很多时候,你只是想在备库上执行,而不希望影响主库. 在mysql命令方式下使用如下代码: #ls -lah users_0.ibd -rwxr-xr-x 1 mysql dba 736M May 6 09:50 users_0.ibd root@test 10:10:53>optimize table users_0 测试实例 1、先来看看多次删除插入操作后的表索引情况,代码如下: mysql> SHOW INDEX FROM `tbl_name`; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_name | 0 | PRIMARY | 1 | StepID | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | FlowID | 1 | FlowID | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerCount | 1 | WagerCount | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 1 | WagerID | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 2 | StepType | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 3 | ParamResult | A | 1 | 255 | NULL | | BTREE | | | tbl_name | 1 | StepType_2 | 1 | StepType | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | StepType_2 | 2 | ParamResult | A | 1 | 255 | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 1 | WagerID | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 2 | StepType | A | 1 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 3 | ParamResult | A | 1 | 255 | NULL | | BTREE | | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 11 rows in set (0.01 sec) --phpfensi.com 2、优化表,代码如下: mysql> optimize table tbl_name; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.tbl_name | optimize | status | OK | +---------------+----------+----------+----------+ 1 row in set (40.60 sec) 3、再来看看优化后的效果,代码如下: mysql> SHOW INDEX FROM `tbl_name`; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_name | 0 | PRIMARY | 1 | StepID | A | 172462 | NULL | NULL | | BTREE | | | tbl_name | 1 | FlowID | 1 | FlowID | A | 86231 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerCount | 1 | WagerCount | A | 4311 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 1 | WagerID | A | 86231 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 2 | StepType | A | 172462 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_3 | 3 | ParamResult | A | 172462 | 255 | NULL | | BTREE | | | tbl_name | 1 | StepType_2 | 1 | StepType | A | 9 | NULL | NULL | | BTREE | | | tbl_name | 1 | StepType_2 | 2 | ParamResult | A | 86231 | 255 | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 1 | WagerID | A | 86231 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 2 | StepType | A | 172462 | NULL | NULL | | BTREE | | | tbl_name | 1 | WagerID_2 | 3 | ParamResult | A | 172462 | 255 | NULL | | BTREE | | +----------+------------+------------+--------------+-------------+-----------+-------------+--- 如果在php中使用,PHP程序,代码如下: header("Content-type: text/html; charset=utf-8"); set_time_limit(0); echo date('Y-m-d H:i:s').'Begin<br/>'; /* *使用OPTIMIZE TABLE 优化表空间 *回收空间，减少碎片 */ mysql_connect('localhost', 'root', '123456') or die('数据库连接失败'.mysql_error()); mysql_query('SET NAME UTF8'); $database = 'db3'; mysql_select_db($database); $res = mysql_query('SHOW TABLES FROM '.$database); while($row = mysql_fetch_row($res)) { $table = $row[0]; $sql = sprintf('OPTIMIZE TABLE %s.%s', $database, $table); if(mysql_query($sql)) { echo '优化表'.$table.'完成。<br>'; } else { echo '优化表'.$table.'失败。'.mysql_error(); exit; } } echo date('Y-m-d H:i:s').'End<br/>'; 看完文章后理解. 作用:回收空间，减少碎片 方法:OPTIMIZE TABLE tablename 情景:磁盘耗尽、InnoDB Tablespaces用完,先用OPTIMIZE TABLE 命令优化,再考虑扩容. 注意:OPTIMIZE 命令支持的引擎MyIsam,InnoDB,ARCHVE,对于InnoDB,它会重建数据表、更新索引统计信息、回收主键索引空间. （编辑：开发网_开封站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330459号