MySQL 對 MyISAM、InnoDB 使用 Optimize Table

标签: My_Note-Unix mysql | 发表时间:2012-03-27 01:17 | 作者:Tsung
出处:http://blog.longwin.com.tw

系統用久了, 自然就會有不連續的碎片(fragmented)產生, 以前 Dos 使用 defrag, Windows 使用磁碟重組, 而 MySQL 則是使用 Optimize table.

以往都是使用: 使用 PHP 對所有 MySQL Database 做 Optimize / Repair 的動作 - 這裡面的那隻程式來跑.

這次突然想到對特定的 Table 手動執行一下, 意外看到下述的訊息:

mysql> OPTIMIZE TABLE foo;

+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

雖然結果一樣, 但是還是查查看差別為何~

Table does not support optimize, doing recreate + analyze instead 原因 與 處理

MySQL 5.1 Reference Manual :: 12.7.2.5 OPTIMIZE TABLE Syntax 此文件有寫

For InnoDB tables, *OPTIMIZE TABLE is mapped to ALTER TABLE*, which rebuilds the table to update index statistics and free unused space in the clustered index.

問題

  • OPTIMIZE TABLE 與 ALTER TABLE 如何對應?
  • 到底是怎麼對應的? 那如果用 ALTER Table 要如何使用?

此篇: Table does not support optimize, doing recreate + analyze instead 有講對應的作法:

Optimize 正常都會出現 status ok (MyISAM), 但是 InnoDB 的都會出現此訊息: Table does not support optimize, doing recreate + analyze instead.

所以對 InnoDB 的話, 就使用下述語法: (請自行將 table.name 改成自己的 Table 名稱)

  • ALTER TABLE table.name ENGINE='InnoDB';

這語法的作用會是: 建立一個新的 Table, 由舊的 Table 將資料拷貝進來, 然後再把舊的 Table 砍掉, 但是, 作者建議先備份後再來執行此動作比較好.

結論

  • 目前還是使用 Optimize Table 就好, MySQL 現在都還是會對應處理好.
  • 以後如果有問題, 確定硬碟空間夠大, 再來考慮使用 ALTER TABLE 處理.

OPTIMIZE TABLE shell script

  1. 此作者有另外一篇文章, 也是 Optimize 的 Script, 看起來也不錯用: Optimize only fragmented tables in MySQL
  2. vim mysql_optimize.sh

    #!/bin/sh
    echo -n "MySQL username: " ; read username
    echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

    mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
    mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
    if [ "$datafree" -gt 0 ] ; then
    fragmentation=$(($datafree * 100 / $datalength))
    echo "$database.$name is $fragmentation% fragmented."
    mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
    fi
    done
    done

  3. chmod +x ./mysql_optimize.sh
  4. ./mysql_optimize.sh # 輸入 MySQL 帳號、密碼 就會自動執行囉~

相关 [mysql myisam innodb] 推荐:

MySQL MyISAM Engine 轉換成 InnoDB

- - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
如果對於 InnoDB 不了解的讀者們,可以參考作者之前寫的 MySQL 預設儲存引擎: InnoDB 介紹,最近開始把原本 MySQL 5.1 預設 MyISAM Table 全部轉換成 InnoDB,MySQL 5.5 版本開始預設的儲存引擎就是 InnoDB,InnoDB 現在也非常完整,也支援 Full Text (5.6.4 開始支援).

MySQL MyISAM/InnoDB高并发优化经验

- jinbiaozhao - 服务器运维与网站架构|Linux运维|互联网研究
最近做的一个应用,功能要求非常简单,就是 key/value 形式的存储,简单的 INSERT/SELECT,没有任何复杂查询,唯一的问题是量非常大,如果目前投入使用,初期的单表 insert 频率约 20Hz(次/秒,我喜欢这个单位,让我想起国内交流电是 50Hz),但我估计以后会有 500Hz+ 的峰值.

MySQL 對 MyISAM、InnoDB 使用 Optimize Table

- - Tsung's Blog
系統用久了, 自然就會有不連續的碎片(fragmented)產生, 以前 Dos 使用 defrag, Windows 使用磁碟重組, 而 MySQL 則是使用 Optimize table.. 以往都是使用: 使用 PHP 對所有 MySQL Database 做 Optimize / Repair 的動作 - 這裡面的那隻程式來跑..

MySQL存储引擎--MyISAM与InnoDB区别

- - CSDN博客互联网推荐文章
MyISAM 和InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定. 基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持. MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能.

MySQL存储引擎MyISAM与InnoDB的主要区别对比

- timo - 服务器运维与网站架构|Linux运维|互联网研究
本文整理了Mysql 两大常用的存储引擎MyISAM,InnoDB的六大常见区别,来源于Mysql手册以及互联网的资料. InnoDB与Myisam的六大区别. MyISAM InnoDB 构 成上的区别: 每个MyISAM在磁盘上存储成三个文件. 第一个 文件的名字以表的名字开始,扩展名指出文件类型.

InnoDB还是MyISAM 再谈MySQL存储引擎的选择

- - OurMySQL
   两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用.    我作为使用MySQL的用户角度出发,Innodb和MyISAM都是比较喜欢的,但是从我目前运维的数据库平台要达到需求:99.9%的稳定性,方便的扩展性和高可用性来说的话,MyISAM绝对是我的首选.

MySQL两种引擎Myisam,innodb单表大小的限制

- - 操作系统 - ITeye博客
MySQL两种引擎Myisam,innodb单表大小的限制. MySQL单表大小的限制在目前的技术环境中,由所在主机的OS上面的文件系统来界定而不是由MySQL数据库本身来决定了. 在老版本的MySQL 3.22中,MySQL单表大小为4GB,当时的MySQL的存储引擎还是MYISAM存储引擎. 但是,当出现MyISAM存储引擎之后,也就是从MySQL 3.23开始,MySQL单表最大限制就已经扩大到了64TB了(官方文档显示).

mysql存储引擎:InnoDB和MyISAM的差别/优劣评价/评测/性能测试

- - CSDN博客数据库推荐文章
InnoDB和MyISAM简介. MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的 顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键.

谈谈innodb和myisam的区别

- - CSDN博客推荐文章
MyISAM 和 InnoDB 讲解.   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定. 基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持. MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能.

向左走向右走: InnoDB or MyISAM

- - CSDN博客数据库推荐文章
欢迎访问个人原创网址 :  http://www.phpthinking.com/archives/456. MyISAM 是MySQL中默认的存储引擎,一般来说不是有太多人关心这个东西. 决定使用什么样的存储引擎是一个很tricky的事情,但是还是值我们去研究一下,这里的文章只考虑 MyISAM 和InnoDB这两个,因为这两个是最常见的.