mysql中DEFAULT_STORAGE_ENGINE:選擇正確的MySQL儲存引擎

現在讓我們把MySQL儲存引擎的問題放在一邊。如果你的MySQL表都是用的InnoDB而你不需要關心InnoDB是如何運作的，你已經設定了，但不確定是否生效。這些問題將在下面會提到。

關於儲存引擎

MySQL自20多年前成立以來一直支援可插拔儲存引擎，但在一段相當長的時間裡MyISAM一直是預設的儲存引擎，許多人運行MySQL甚至對底層儲存引擎一點都不瞭解。畢竟，MySQL剛開始是為小型網站的小型資料庫設計的，許多應用已經習慣使用MyISAM儲存引擎。

剛開始沒什麼問題，一切正常，但現在的問題是：MyISAM沒有考慮到應用到高並發高負載，多核CPU和RAID陣列的情境，也不能彈性擴充。所以網站流量越來越多後，他們不能擴充，因為MySQL查詢會在表級鎖上等待數秒(MyISAM只支援這種鎖機制)。他們不想每次MySQL崩潰時損壞他們的業務資料。

INNODB儲存引擎

許多人並不知道，自MySQL存在以來MyISAM儲存引擎就有一個兄弟叫InnoDB。並且高並發負載，效能和彈性(也包括原子性，一致性和隔離)正是它的特長。
當然，在InnoDB發展過程中也有過一些問題(尤其是2006年5.0.30之前的版本的效能問題)，但在這之後的10年時間裡，InnoDB已經在你能想到的領域(或者沒有)得到了證明，而MyISAM已經很少被關注了。
因此，從MySQL 5.5.5開始，InnoDB成為預設的儲存引擎，現在你幾乎找不到大型MySQL資料庫的安裝使用MyISAM而不是InnoDB。
下面讓我來告訴你如何快速地統計和列出在你系統的所有MyISAM表，方便你開始計劃遷移。

你使用的儲存引擎

下面的查詢展示你所用的儲存引擎以及它們的一些統計資訊，包括表數量，大小等。

mysql> SELECT engine,
  count(*) as TABLES,
  concat(round(sum(table_rows)/1000000,2),'M') rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  round(sum(index_length)/sum(data_length),2) idxfrac
 FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+--------+--------+---------+--------+--------+------------+---------+
| engine | TABLES | rows    | DATA   | idx    | total_size | idxfrac |
+--------+--------+---------+--------+--------+------------+---------+
| InnoDB |    181 | 457.58M | 92.34G | 54.58G | 146.92G    |    0.59 |
| MyISAM |     13 | 22.91M  | 7.85G  | 2.12G  | 9.97G      |    0.27 |
+--------+--------+---------+--------+--------+------------+---------+
2 rows in set (0.22 sec)

擷取以大小排序的MyISAM表列表，執行如下查詢：

SELECT
    concat(table_schema, '.', table_name) tbl,
    engine,
    concat(round(table_rows/1000000,2),'M') rows,
    concat(round(data_length/(1024*1024*1024),2),'G') DATA,
    concat(round(index_length/(1024*1024*1024),2),'G') idx,
    concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
    round(index_length/data_length,2) idxfrac
 FROM information_schema.TABLES
WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
  AND engine = 'MyISAM'
ORDER BY data_length+index_length DESC;

需要記住的是，更改預設的儲存引擎為InnoDB或者升級MySQL並不會自動把你的錶轉換為InnoDB。目前為止，你需要一個表一個表地轉換，或者使用指令碼。
需要注意的是，小的MyISAM表也一樣需要轉換，因為只要有一個MyISAM用在join語句裡，那麼整個查詢都是用表級鎖，所以這將對並發有很大影響。所以確保你把所有的MyISAM錶轉為InnoDB表。

轉換為INNODB

建議在你著手轉換引擎為InnoDB之前，最好先熟悉理解一下InnoDB的配置。準備好後，執行如下查詢來轉換：

SET @DB_NAME = 'your_database';
 
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DB_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;