MySQL 5.6 新功能介紹（1）

在MySQL 5.6 中，做了很多效能上的改善，分別介紹如下：

1. 更好的效能和擴充性：提昇InnoDB儲存引擎。

MySQL 5.6在多執行緒和高線程CPU的系統中，提供了線性的效能和擴充性。效能提昇的主要關鍵是在於Oracle InnoDB儲存引擎的效率和同步處理的能力提昇而移除在InnoDB核心中過時的線程競爭和互斥鎖定。這樣的提昇使得MySQL可以完全的使用，現今以x86為架構的COTS(註一)多線程處理器的運算能力。

註一：COTS，commodity-off-the-shelf的縮寫，泛指通用在市場容易購得的商品，而非客制，價格昂貴且難以取得的硬體。

在Oracle內部的SysBench讀／寫和唯讀測試報告中，在同步處理從12個到60個讀寫交易的CPU線程環境中，5.6擴充表現仍是線性比率，遠比5.5的還穩定。

圖一：SysBench 讀／寫 TPS線性擴充性測試 － 60個CPU線程。
（圖來源：MySQL 5.6 新功能介紹白皮書）

圖二：SysBench 唯讀TPS線性擴充性測試 － 60個CPU線程
（圖來源：MySQL 5.6 新功能介紹白皮書）

更好的交易處理量

MySQL 5.6 提昇了InnoDB的效能及高同步的擴充性，交易能力和讀取的能力。這些使用案例是藉由測量應用程式在同時多人使用時，是如何的執行和擴充來取得效能的提昇。在支持這樣案例下，InnoDB有一個新重構後的架構可以減少互斥鎖的競爭，降低效能瓶頸和提供一個在資料存取中更一致的路徑。包括有：
• 在核心中，移除單一點的互斥鎖的競爭。
• 為flush的行為建立一個新的線程。
• 多線程的purge。
• 新的雜湊演算法。
• 減少緩衝池的競爭。
• 較好且更一致性的查詢執行，與更頻繁且可預測的時間間隔來持續統計資料的優化器。

上述的MySQL改善，反應在Sysbench的讀／寫的測試報告中：

圖三：SysBench讀寫比較 – MySQL 5.6 vs. MySQL 5.5
（圖來源：MySQL 5.6 新功能介紹白皮書）

較好的唯讀負載處理

針對InnoDB在唯讀狀態下的效能調校，讓網頁系統在同時處理大量的查詢及產生報表的交易行為中，有顯著的效能提昇。這些最佳化省略了自動提交的步驟，除非在設定了環境變數autocommit=1時才會再被使用，或是在開發者使用新的語法START_TRANSACTION_READ_ONLY的情況下，也可以省略自動提交。

SET autocommit = 0;
START_TRANSACTION_READ_ONLY;
SELECT * from test;
COMMIT;

圖四：SysBench唯讀比較 – MySQL 5.6 vs. MySQL 5.5
（圖來源：MySQL 5.6 新功能介紹白皮書）

在Linux作業系統中，MySQL 5.6 在唯讀交易展現出來的效能是MySQL 5.5 的230％，相同的測試在Windows 2008則多出65%。

提供固態硬碟更好的效能

旋轉式磁盤硬碟是系統中最常見的瓶頸，最主要的因為是硬碟本身機械的部分，限制了系統擴展的能力。

結果，許多MySQL的應用程式開始佈署在可以提供類似記憶體讀取速度的SSD上，並在今日要求高可靠度的網頁應用系統中，提供了最高等級的同步處理能力。MySQL5.6包含了一些專門為SSD準備的重要增強功能，包括：
• 支援4K和8K page size以符合SSD的標準儲存演算法。
• 可攜式的 .ibd(InnoDB的資料)檔案。允許InnoDB的資料表可以容易的從預設的MySQL資料目錄上搬到SSD或網路儲存裝置
• 將undo日誌與表空間分開。將undo日誌從系統表空間搬到一個或多個獨立的表空間。undo日誌的密集讀取模式，讓這些新的表空間成為適合搬到SSD儲存的檔案，而讓系統表空間繼續留在一般硬碟中。

2. 更好的執行時間和診斷能力：提昇優化器。

在MySQL 5.6中，已經重構優化器，以取得更好的效率和性能，並提供了改進的功能，以獲得更好的查詢執行時間和自我診斷能力。MySQL 5.6主要的優化改進包括：

• 最佳化子查詢。MySQL優化器使用semi-join及子查詢實現(materialization)的方式來提昇子查詢的效能。具體來說，優化器是更有效率地處理FROM子句。目前，FROM子句中的子查詢實現並不會在執行SQL時立刻執行，而會延遲到當需要子查詢的內容時才執行，這樣就產生了極大地效能提昇。此外，優化器可能會添加一組索引來加快執行檢索的時間。

Oracle內部也使用DBT-3的第13號查詢來測試MySQL的運行效能，結果，執行時間從數天縮短到幾秒鐘，效能有顯著的提昇。

select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 313
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;

• 小限制值（limit）資料文件排序的優化。對於查詢指令的ORDER BY和小的限制值(limit)，現在，優化程序會產生一個已排序的結果集，再將其視為單一表格般的掃描資料。這樣的查詢，最常出現在Web的應用程序，從一個大的結果集中只顯示其中幾行，如：

SELECT ID, FIRTNAME, LASTNAME FROM STUDENT ORDER BY ID LIMIT 10;

經ORACLE內部測試後表示，這一類的查詢，可以提昇約4倍的執行速度，有助於改善整體用戶的體驗和減少反應時間。

• 索引條件下推（Index Condition Pushdown, ICP）。預設的情況下，優化器會將WHERE條件下推到存儲引擎以進行評估／表掃描，而將已排序的結果集返回到MySQL的服務器中。

SELECT ID, FIRSTNAME, LASTNAME FROM STUDENT
WHERE ID BETWEEN 10 AND 100;

經ORACLE內部測試後表示，這一類的查詢，在預設ICP的行為下，可以提昇約15倍的執行速度。

• 批次的索引鍵的讀取（Batched Key Access, BKA）和多範圍讀取（Multi-Range Read, MRR）。優化器批次提供主鍵給儲存引擎，使得儲存引擎可以讀取／排序，能更有效地縮短了查詢的執行時間。

圖五：MySQL 5.6 批次／區間的鍵讀取效能比較
（圖來源：MySQL 5.6 新功能介紹白皮書）

在Oracle內部作相同的測試，在硬碟是效能瓶頸的系統下使用DBT-3第十三號查詢，BKA和MMR的架構可以提昇查詢的效能約280倍。

• 更好的優化診斷。優化的MySQL5.6提供下列更好的診斷和調試：
• 解釋INSERT，UPDATE，DELETE的操作行為，
• 解釋計劃輸出更精確的優化指標和更好的JSON格式可讀性，
• 跟踪優化決策過程的優化軌跡。

3. 更好的應用系統可用性：線上DDL/Schema變更。

今天的Web應用程序大多都被設計為快速發展和滿足業務與產生營餘的要求。其結果是，系統的服務水平協議（Service Level Agreement，SLA）最多以分鐘測量基礎，而不是以數天或數週來計算。因此，當一個應用程序需要快速支持新的生產線或新產品時，後端數據庫綱要（schema）必須是一種符合一般性的架構設計，應用程式仍然可以進行正常的業務操作。 MySQL5.6支援了這樣的資料庫靈活性和敏捷性，並提供了以下新的ALTER TABLE DDL語法補充：

• CREATE INDEX
• DROP INDEX
• Change AUTO_INCREMENT value for a column
• ADD/DROP FOREIGN KEY
• Rename COLUMN
• Change ROW FORMAT, KEY_BLOCK_SIZE for a table
• Change COLUMN NULL, NOT_NULL
• Add, drop, reorder COLUMN

DBA和開發人員可以添加索引和執行標準的InnoDB表格改變而應用系統仍然可以對數據庫作資料更新。MySQL提供綱要的靈活性，以適應不斷變化的業務需求，這對特別需要快速發展應用程序的開發人員有很大的幫助。

4. 更好的敏捷開發：NoSQL方式存取InnoDB。

許多最新一代的網絡，雲計算，社交和移動應用需要快速行動，來處理簡單的鍵/值對。同時，他們必須保持能夠對相同的資料作複雜的查詢，並確保數據能在ACID下獲得保護。藉由InnoDB新的NoSQL API，開發者除了可以得到原本RDBMS交易保護的好處外，還可以再獲得NoSQL獲得儲存效能。

MySQL5.6通過熟悉的Memcached的API，以鍵/值方式與InnoDB溝通取得數據，並實作了一個新的Memcached的插件到mysqld中。新的memcached協議直接指向到InnoDB的API，使開發人員能夠使用現有的Memcached客戶端而省略查詢解析所需花費的時間，並直接進入InnoDB作資料查詢和交易更新。API使開發者可以重用標準的Memcached的函式庫和客戶端，並透過整合一個具備永續性，宕機保護，提供交易保護的後端數據庫，作為Memcached的功能延伸。

5. 更好的敏捷開發：延展InnoDB的使用案例。

新的MySQL5.6優化和功能，擴展InnoDB到更多的使用案例。因此，開發人員可以在單一存儲引擎的標準化下，簡化應用程式。

• 新的全文搜索 – 提供一個更好的替代MyISAM的全文檢索。現在，InnoDB可以讓開發人員在文字內容的資料表中建立全文索引，而加速了應用系統搜索的單字和片語的能力。InnoDB中的全文搜索支援自然語言/布林模式，鄰近搜尋和相關性排序。一個簡單的使用案例如下：

CREATE TABLE STUDENT (
id int unsigned auto_increment primary key,
firstname varchar(16),
lastname varchar(16),
address varchar(128),
fulltext(address)
) engine=innodb;
SELECT id, firstname, lastname, address
where match(address) against ('taipei' in natural language mode);

• 新的可攜式表空間 – 在每個資料表擁有各自的InnoDB的.ibd文件時，所創建的文件可以在任意存儲在不同的設備，不同的資料庫伺服器之中。當在創建資料表時，開發人員可以指定將.ibd的文件儲存在MySQL資料目錄之外。這可以讓無法負荷應用程式／資料庫的忙碌資料表可以很容易地轉移到外部的網絡存儲設備（SSD，HDD）。這個新功能藉由允許使用者輕易地在不同的MySQL伺服器之間匯出／匯入InnoDB資料表，而讓系統可以快速，無接縫地擴充應用程式，如下所示：

匯出範例:

mysql> use export_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> FLUSH TABLE my_table FOR EXPORT;
=> [innodb_data_home_dir]/export_database/my_table.cfg
mysql> UNLOCK TABLES;

匯入範例:

mysql> use import_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> ALTER TABLE my_table DISCARD TABLESPACE;
mysql> ALTER TABLE my_table IMPORT TABLESPACE;

後續 >> MySQL 5.6 新功能介紹（2）