MariaDB 10.0 亮眼特性詳解

很多同學(xué)一定聽說過MariaDB。作為MySQL的重要分支之一，它繼續(xù)秉承完全開源的姿態(tài)（MySQL也有不少好用功能是收費(fèi)的喲），被很多大型互聯(lián)網(wǎng)企業(yè)廣泛使用（如Google、Twitter）。同時(shí)，紅帽7（包括CentOS）也將默認(rèn)數(shù)據(jù)庫(kù)由MySQL更改為MariaDB。在這種情況下，一定要了解一下這個(gè)逆生長(zhǎng)的MariaDB。

本文，著重介紹MariaDB 10.0 GA版中的非常吸引人的若干特性，方便大家和MySQL進(jìn)行比較。

1. Feedback插件--不推薦：

a) 開啟方式：my.cnf配置文件的[mysqld]模塊，加入feedback=on

b) 作用：會(huì)發(fā)送使用數(shù)據(jù)給開發(fā)人員，幫助其優(yōu)化代碼。

2. InnoDB和XtraDB轉(zhuǎn)換：

a) MDB默認(rèn)使用XtraDB。

b) 開啟方式：

1） 停止mysql服務(wù)。

2） 在my.cnf配置文件的[mysqld]模塊，加入ignore_builtin_innodb、 

plugin_load=innodb=ha_innodb.so

3. MYSQL客戶端報(bào)告：

a) 控制參數(shù)：global.progress_report_time 值范圍要大于5.單位秒。5以下的值被忽略。

b) 關(guān)閉的方法：

1） 啟動(dòng)服務(wù)時(shí)加入--disable-progress-reports

2） 設(shè)置global.progress_report_time 值等于0.

c) MariaDB附帶的mytop腳本一樣也支持此功能

4. SHOW EXPLAIN FOR query_id：Explain命令的變種。

可以在得知QID的情況下查詢執(zhí)行計(jì)劃。

5. LIMIT ROWS EXAMINED：LIMIT命令的進(jìn)化版本。

語法：LIMIT [m] ROWS EXAMINED n （m可選）

作用：普通的LIMIT語句在查詢到指定數(shù)量的分頁(yè)結(jié)果后還會(huì)繼續(xù)執(zhí)行。如果是大表的話這樣的分頁(yè)操作會(huì)消耗過多的資源。

舉例：LIMIT 100 ROWS EXAMINED 10000 將從一個(gè)含有10000條記錄的分頁(yè)中再次篩選出前100條記錄。

6. INSTALL SONAME：安裝指定插件、引擎。INSTALL PLUGIN命令變種。

語法：INSTALL SONAME engine_name.

舉例：安裝BLACKHOLE引擎。

INSTALL SONAME ‘ha_blackhole’;

INSTALL PLUGIN Blackhole SONAME ‘ha_blackhole’;

卸載某插件使用UNINSTALL替換INSTALL

7. 生成HTML/XML文件：

舉例：mysql --html/--xml isfdb < isfdb-001.sql > isfdb-001.html/ isfdb-001.xml

會(huì)將isfdb-001.sql中的查詢語句返回的結(jié)果生成相應(yīng)的html或者xml文件。

8. MYISAM引擎轉(zhuǎn)換為ARIA引擎：

ARIA引擎與MYISAM引擎的主要區(qū)別是ARIA引擎自帶crash safe功能，在災(zāi)難性斷電或其他意想不到的表錯(cuò)誤導(dǎo)致的表?yè)p壞時(shí)，可以恢復(fù)數(shù)據(jù)。

使用ALTER TABLE命令轉(zhuǎn)換引擎。

轉(zhuǎn)換引擎操作步驟和MYSQL一致：

a) 首先創(chuàng)建新表

b) 導(dǎo)入數(shù)據(jù)

c) Rename操作

因此對(duì)于千萬級(jí)的大表來說這一過程將相當(dāng)漫長(zhǎng)。建議不要在生產(chǎn)數(shù)據(jù)庫(kù)上進(jìn)行此操作。

9. 控制MariaDB查詢優(yōu)化器策略：

a) 查看當(dāng)前查詢優(yōu)化器中的優(yōu)化策略狀態(tài)：

SELECT @@optimizer_switch\G

開啟或關(guān)閉某個(gè)優(yōu)化策略：

如：SET [GLOBAL] optimizer_switch="mrr=on";

或者在my.cnf配置文件中的[mysqld]模塊中添加：

[mysqld] 

optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"

b) 在INNODB和XTRADB上打開優(yōu)化器extended keys策略：

1） 打開方法如上文所示，參數(shù)名為：extended_keys=on

2） 作用：由于優(yōu)化器是基于成本的（CBO），因此執(zhí)行計(jì)劃中會(huì)出現(xiàn)有很多索引但是優(yōu)化器無法使用的查詢。打開優(yōu)化器extended keys策略將會(huì)使優(yōu)化器在出現(xiàn)這一情況時(shí)，盡量使用索引來返回，而不是掃描全表。

10. 配置Aria引擎兩步死鎖監(jiān)測(cè)：

a) 原理：

當(dāng)Aria引擎無法在表上創(chuàng)建鎖時(shí)，它首先會(huì)依據(jù)deadlock_search_depth_short的值為可能出現(xiàn)的死鎖創(chuàng)建一個(gè)深度搜索等待圖（WFG）。當(dāng)搜索結(jié)束后如果還無法創(chuàng)建鎖，那么Aria引擎并不急著判斷出死鎖，而是會(huì)等待deadlock_timeout_short定義的微秒后，再搜索一遍。如果還是無法創(chuàng)建鎖，那么Aria引擎會(huì)使用deadlock_search_depth_long參數(shù)的值，創(chuàng)建一個(gè)深度搜索等待圖。當(dāng)搜索結(jié)束后，如果還沒有定義死鎖，那么Aria引擎將等待deadlock_timeout_long定義的微秒后，返回超時(shí)錯(cuò)誤。

b) 查看當(dāng)前設(shè)置：SHOW VARIABLES LIKE ‘deadlock_%’\G

c) 修改參數(shù)值，如：

d) 或?qū)懭肱渲梦募?span>my.cnf的[mysqld]模塊中。

e) 注意：上述timeout參數(shù)中的單位是微秒。

11. 配置MYISAM引擎鍵緩存段（key_cache_segments）：

作用：增強(qiáng)MYISAM引擎表性能。高并發(fā)環(huán)境中提高MYISAM引擎性能。

a) 原理：

當(dāng)MYISAM引擎的線程使用鍵緩存時(shí)（key cache），線程將首先獲得一把鎖。在高并發(fā)場(chǎng)景中，大量的線程同時(shí)申請(qǐng)鎖。對(duì)大表或者熱點(diǎn)表來說，完整的鍵緩存會(huì)成為性能瓶頸。分割后的鍵緩存可以減少鎖競(jìng)爭(zhēng)，獲得鎖的線程只需要鎖定相應(yīng)的鍵緩存段就可以了，無需鎖定整個(gè)鍵緩存。

b) 查看當(dāng)前設(shè)置：SHOW VARIABLES LIKE ‘key_cache_segments’\G

c) 修改參數(shù)值：

d) 或?qū)懭肱渲梦募?span>my.cnf的[mysqld]模塊中。

12. 配置線程池(重中之重啊，核心功能啊)：

MariaDB的線程池提供了更強(qiáng)大的將線程集中使用的功能。并替換了mysql之前的one tread per client connection方法。該方法在典型的web壓力場(chǎng)景（數(shù)量多但是返回結(jié)果小的場(chǎng)景）中對(duì)線程的復(fù)用效果并不理想。

a) 修改參數(shù)值：

在my.cnf中的[mysqld]模塊中添加thread_handling = pool-of-threads。

b) 重啟服務(wù)

c) 作用：

生產(chǎn)場(chǎng)景中出現(xiàn)大量的短查詢或者CPUload過高時(shí)，線程池是處理這一問題的最好方案。但是在短時(shí)間出現(xiàn)的大量長(zhǎng)查詢時(shí)，就不適用了。通常這種情況，linux下可以使用thread_pool_idle_timeout參數(shù)來緩解壓力。

d) 其他重要的參數(shù)：

1） thread_pool_stall_limit：?jiǎn)挝缓撩搿?

用于出現(xiàn)大量長(zhǎng)連接查詢，該參數(shù)控制線程被stall的時(shí)間。默認(rèn)值500。如果查詢已經(jīng)被stall，MDB將創(chuàng)建一個(gè)新線程，線程池中的最大線程數(shù)由thread_pool_max_threads參數(shù)控制。默認(rèn)值500。當(dāng)線程池中的線程數(shù)達(dá)到最大值后，將不會(huì)在創(chuàng)建新線程，即使線程已經(jīng)被stall。

這一情況可以使用參數(shù)extra_port來解決。該參數(shù)將打開一個(gè)額外的端口，來保持查詢進(jìn)行連接。該參數(shù)的值必須和默認(rèn)端口不同。

2） thread_pool_idle_timeout：?jiǎn)挝幻搿?

定義線程在撤銷之前的等待時(shí)間。默認(rèn)值60.

如果發(fā)現(xiàn)在某線程被撤銷后會(huì)定期的創(chuàng)建新的線程，那么應(yīng)當(dāng)增加該參數(shù)的值。

3） thread_pool_size：LINUX平臺(tái)定義線程池大小。

13. 配置Aria引擎pagecache：

Aria引擎的PAGE是row format型。它的pagecache由三個(gè)參數(shù)控制：

a) aria_pagecache_buffer_size：?jiǎn)挝?span>byte。默認(rèn)值128M-512M。

該參數(shù)不能動(dòng)態(tài)修改。

b) aria_pagecache_age_threshold：定義在pagecache中的數(shù)據(jù)塊留存長(zhǎng)度。

c) aria_pagecache_division_limit：?jiǎn)挝话俜直?。定義pagecache中溫?cái)?shù)據(jù)所占的百分比。

14. 使用子查詢緩存（subquery cache）優(yōu)化查詢：MariaDB獨(dú)占功能！

a) subquery cache極大提升子查詢性能。

b) 默認(rèn)開啟.

c) 兩個(gè)狀態(tài)值變量：

1） subquery_cache_hit：子查詢緩存命中次數(shù)

2） subquery_cache_miss：未命中次數(shù)

15. 優(yōu)化半連接子查詢（semijoin query）：

半連接子查詢中常見的是帶where條件的IN子句。這種子句在MYSQL中是很難被優(yōu)化的。而MDB提供了一個(gè)可以開啟的優(yōu)化器參數(shù)，指導(dǎo)優(yōu)化器對(duì)這種類型的查詢進(jìn)行優(yōu)化。

a) 參數(shù)：exists_to_in=on。默認(rèn)不開啟。

b) 對(duì)EXISTS 型的查詢也可以起到優(yōu)化作用。

c) 修改參數(shù)值：

1） SET optimizer_switch='exists_to_in=on';

2） 或者修改配置文件：

16. 創(chuàng)建全文索引(full-text index)--不推薦：

a) full-text index是一個(gè)特殊類型的索引,用來搜索基于text類型的列.

b) 只可以為char, varchar, text類型創(chuàng)建全文索引.

c) 全文索引允許我們使用"MATCH() ... AGAINST"語法來查詢數(shù)據(jù).

語法的MATCH部分包含一個(gè)以逗號(hào)分隔的待查詢columns列表.

語法的AGAINST部分包含需要搜索的字符串也包含一個(gè)可選的修飾符來表明所執(zhí)行查詢的類型.

查詢類型有:

IN NATURAL LANGUAGE MODE, IN BOOLEAN MODE, WITH QUERY EXPANSION

默認(rèn)類型為: IN NATURAL LANGUAGE MODE.

17. 開啟用戶統(tǒng)計(jì)：

a) 修改配置文件：

b) 或SET GLOBAL USERSTAT = 1;

c) 開啟后可以查看統(tǒng)計(jì)數(shù)據(jù)。如：

SHOW INDEX_STATISTICS;

18. 使用全局事務(wù)ID（global transaction IDs）：
更直觀的全局事務(wù)。比5.6逗比般的GTID更容易理解。

a) 全局事務(wù)ID（GTID）是在10.0.2版本中新添加的特性，它將使復(fù)制機(jī)能更穩(wěn)定、更靈活。這里需要注意的是：MariaDB和MYSQL 5.6中提到的GTID不能通用，而且差別較大。

b) GTID信息的存放位置在mysql.gtid_slave_pos表，在更新數(shù)據(jù)的同一事務(wù)中更新此表的最新位置信息。

c) GTID的組成：0-1-12345

1） 第一位是domain ID。這是由MariaDB特有的多源復(fù)制場(chǎng)景決定的。是32位的無符號(hào)整型。單源場(chǎng)景下，該值為0.domain id 的值在my.cnf中設(shè)定，如gtid-domain-id = 1。

2） 第二位是server_id。是32位的無符號(hào)整型。

3） 第三位是Commit_id。是64位的無符號(hào)整型。事務(wù)在master提交階段指定的提交id號(hào)，這是一個(gè)遞增的值，每次提交都不一樣，而在group commit中所有被group的事務(wù)所指定的commit id都是相同的。

d) 默認(rèn)開啟，查看GTID的值使用show binlog events命令，結(jié)果如下所示：

GTID_LIST列中的值就是GTID。

其實(shí)值為[]。

查看從庫(kù)的當(dāng)前GTID 值，使用命令 SELECT @@global.gtid_slave_pos，主庫(kù)上返回空值。

查看master的當(dāng)前gtid值，使用命令 select @@global.gtid_current_pos;

e) 從庫(kù)采用gtid的復(fù)制，語法為：CHANGE MASTER TO master_use_gtid = { slave_pos | current_pos | no }

f) 在一般的復(fù)制場(chǎng)景中，從庫(kù)使用的是slave_pos的值。如A是B的主庫(kù)，當(dāng)A掛掉后B充當(dāng)主庫(kù)，A重新上線后想要做B的從庫(kù)時(shí)，使用的是current_pos值。因?yàn)?/span>A之前是主庫(kù)，沒有做過從庫(kù)，所以不存在slave_pos值。注：沒有做過從庫(kù)的主庫(kù)沒有slave_pos

如果之前的從庫(kù)沒有開啟binlog，那么current_pos和slave_pos的值是相等的。

g) 可以使用set global gtid_slave_pos= ‘XXXX’修改slave_pos的值。

查詢slave_pos和current_pos：

select @@global.gtid_slave_pos / gtid_current_pos

h) 實(shí)測(cè)主庫(kù)在不停寫入時(shí)崩潰，當(dāng)主庫(kù)重新上線后，從庫(kù)可以根據(jù)slave_pos追上主庫(kù)。

i) 理解current_pos和slave_pos概念：

Current_pos：值由主庫(kù)操作決定。當(dāng)機(jī)器開啟binlog，在執(zhí)行事務(wù)時(shí)就會(huì)記錄該值。使用select @@gtid_current_pos中展示的是所有的pos值。

Slave_pos： 從庫(kù)上的該值與current_pos一致。使用select @@gtid_current_pos中展示的是本機(jī)復(fù)制到的GTID。

19. 多源復(fù)制：

a) 多源復(fù)制中應(yīng)當(dāng)注意的要點(diǎn)：

1） 各源my.cnf中應(yīng)當(dāng)增加relay-log參數(shù)，如：relay_log = db01-relay-binlog。格式中建議增加主機(jī)名來區(qū)分。

2） 不同源的domain-id一定要不同。否則會(huì)出現(xiàn)復(fù)制失敗。配置domain-id在my.cnf文件中增加gtid-domain-id=n 參數(shù)。重啟服務(wù)。

3） change master 語法中可以增加源主機(jī)名來增加區(qū)分，如：

Change master [“connect_name”] to 

master_host = ‘xxx.xxx.xxx.xxx’,

master_port=3306,

master_user=’replication’,

master_password=’replication’,

master_use_gtid=current_pos;

注：使用源主機(jī)名來做change master后，只能只用start all slaves命令來啟動(dòng)復(fù)制。其余命令也類似。

4） 啟動(dòng)所有復(fù)制：start all slaves;

停止所有復(fù)制：stop all slaves;

查看所有連接復(fù)制狀態(tài)：show all slaves status;

查看某連接復(fù)制狀態(tài)：show slave [“connect_name”] status; 

清空某連接復(fù)制狀態(tài)：reset slave [“connect_name”]; 

注意：這里的清空只是將relay_log從1開始重做。不能將復(fù)制信息從slave status的展示中清除。

清空某連接復(fù)制狀態(tài)并從slave status展示中刪除：

reset slave [“connect_name”] ALL

b) 多源復(fù)制的引用場(chǎng)景：

1） 將各主庫(kù)上的數(shù)據(jù)整合到一個(gè)slave上，方便查詢。

2） 將各主庫(kù)的數(shù)據(jù)整合到一個(gè)slave上，方便備份。

20. 增加基于行復(fù)制的binlog注釋：

在配置文件中增加binlog_ annotate_row_events參數(shù)。會(huì)在mysqlbinlog命令查看binlog時(shí)，在基于行復(fù)制的部分展示SQL語句。

21. 配置binlog事件總和檢查器（binlog event checksum）：

a) 作用：用于在文件系統(tǒng)出現(xiàn)故障前快速發(fā)現(xiàn)。

b) 參數(shù)設(shè)置：

也可以通過在my.cnf配置文件中添加參數(shù)BINLOG_CHECKSUM開啟。

22. 在復(fù)制過程中跳過指定的binlog事件：

a) 參數(shù)設(shè)置：開啟。

b) 作用：

在開啟功能的這一階段中處理的所有事務(wù)都不會(huì)被從庫(kù)復(fù)制。類似的功能還有將binlog功能關(guān)閉：set @@sql_log_bin = 0。但是這樣的參數(shù)設(shè)置會(huì)停止所有事務(wù)記錄binlog。Skip_replication參數(shù)則不會(huì)。


Perry.Zhang
02.18.2016