MySQL 5.6 新功能介紹(1)

注册 Vultr VPS 送你10美金 免费玩4个月

在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的還穩定。

new-features-of-mysql-5-6-1

圖一:SysBench 讀/寫 TPS線性擴充性測試 - 60個CPU線程。
(圖來源:MySQL 5.6 新功能介紹白皮書)

new-features-of-mysql-5-6-2

圖二:SysBench 唯讀TPS線性擴充性測試 - 60個CPU線程
(圖來源:MySQL 5.6 新功能介紹白皮書)

更好的交易處理量

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

上述的MySQL改善,反應在Sysbench的讀/寫的測試報告中:

new-features-of-mysql-5-6-3

圖三:SysBench讀寫比較 – MySQL 5.6 vs. MySQL 5.5
(圖來源:MySQL 5.6 新功能介紹白皮書)

較好的唯讀負載處理

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

1
2
3
4
SET autocommit = 0;
START_TRANSACTION_READ_ONLY;
SELECT * from test;
COMMIT;

 new-features-of-mysql-5-6-4

圖四: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的運行效能,結果,執行時間從數天縮短到幾秒鐘,效能有顯著的提昇。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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的應用程序,從一個大的結果集中只顯示其中幾行,如:

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

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

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

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

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

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

new-features-of-mysql-5-6-5

圖五: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語法補充:

1
2
3
4
5
6
7
8
• 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中的全文搜索支援自然語言/布林模式,鄰近搜尋和相關性排序。一個簡單的使用案例如下:

1
2
3
4
5
6
7
8
9
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資料表,而讓系統可以快速,無接縫地擴充應用程式,如下所示:

匯出範例:

1
2
3
4
5
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;

匯入範例:

1
2
3
4
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;

注册 Vultr VPS 送你10美金 免费玩4个月