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

下面我們來看一篇關於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;