转载自:http://blog.csdn.net/xlgen157387/article/details/50728737
碎片产生的原因
(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;
(2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;
(3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;
例如:
<code class="hljs has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">一个表有1万行,每行10字节,会占用10万字节存储空间,执行删除操作,只留一行,实际内容只剩下10字节,但MySQL在读取时,仍看做是10万字节的表进行处理,所以,碎片越多,就会越来越影响查询性能。</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
查看表碎片大小
(1)查看某个表的碎片大小
<code class="hljs ruleslanguage has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> SHOW <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> STATUS LIKE <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'表名'</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
结果中’Data_free’列的值就是碎片大小
(2)列出所有已经产生碎片的表
<code class="hljs oxygene has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> table_schema db, table_name, data_free, engine <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> information_schema.tables <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> table_schema <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">in</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'information_schema'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'mysql'</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> data_free > <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>
清除表碎片
(1)MyISAM表
<code class="hljs lua has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;">mysql> optimize <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">table</span> 表名</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
(2)InnoDB表
<code class="hljs fix has-numbering" style="display: block; padding: 0px; background-color: transparent; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-top-left-radius: 0px; border-top-right-radius: 0px; border-bottom-right-radius: 0px; border-bottom-left-radius: 0px; word-wrap: normal; background-position: initial initial; background-repeat: initial initial;"><span class="hljs-attribute" style="box-sizing: border-box;">mysql> alter table 表名 engine</span>=<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">InnoDB</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>
Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引.
OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中information_schema
.TABLES
字段,查看 DATA_FREE 字段,大于0话,就表示有碎片
建议
清除碎片操作会暂时锁表,数据量越大,耗费的时间越长,可以做个脚本,定期在访问低谷时间执行,例如每周三凌晨,检查DATA_FREE字段,大于自己认为的警戒值的话,就清理一次。