mysql 表碎片整理
来源:互联网 发布:类似360的软件 编辑:程序博客网 时间:2024/05/22 07:48
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)
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 | |+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
最后,来看看手册中关于 OPTIMIZE 的描述:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... 如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。 在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。 OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。 注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
- mysql 表碎片整理
- MYSQL表碎片整理
- MySQL表空间碎片整理
- MySQL表空间的碎片整理
- MySQL的 data_free,表碎片整理
- mysql碎片整理
- mysql数据库碎片整理脚本
- mysql优化之碎片整理
- 表空间碎片整理
- 表碎片整理
- oracle表碎片整理
- 查看表的碎片及整理碎片
- oracle表空间碎片整理
- 如何整理ORALCE表碎片
- 整理索引及表碎片
- MySQL表空间碎片
- 碎片整理
- 碎片整理
- oracle 函数
- 解决表格设置table-layout:fixed后单元格宽度设置无效的方法
- SVN的使用
- google mock C++单元测试框架
- permission和uses-permission的区别
- mysql 表碎片整理
- web 测试(二 )性能测试(包括负载/压力测试)
- C#高级编程 读书点
- VC PlaySound
- 在GridView控件中刪除數據
- mongo参考教程链接
- xml-rpc
- Android This text field does not specify an inputType or a hint 解决
- Refactoring Game Entities with Components