返回 "ProxySQL系列文章：http://www.cnblogs.com/f ck need u/p/7586194.html" 1.關於ProxySQL路由的簡述當ProxySQL收到前端app發送的SQL語句後，它需要將這個SQL語句(或者重寫後的SQL語句)發送給後端的M ...

返回ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html

1.關於ProxySQL路由的簡述

當ProxySQL收到前端app發送的SQL語句後，它需要將這個SQL語句(或者重寫後的SQL語句)發送給後端的MySQL Server，然後收到SQL語句的MySQL Server執行查詢，並將查詢結果返回給ProxySQL，再由ProxySQL將結果返回給客戶端(如果設置了查詢緩存，則先緩存查詢結果)。

ProxySQL可以實現多種方式的路由：基於ip/port、username、schema、SQL語句。其中基於SQL語句的路由是按照規則進行匹配的，匹配方式有hash高效匹配、正則匹配，還支持更複雜的鏈式規則匹配。

本文將簡單演示基於埠、用戶和schema的路由，然後再詳細介紹基於SQL語句的路由規則。不過需要說明的是，本文只是入門，為後面ProxySQL的高級路由方法做鋪墊。

在閱讀本文之前，請確保：

已經理解ProxySQL的多層配置系統，可參考：ProxySQL的多層配置系統
會操作ProxySQL的Admin管理介面，可參考：ProxySQL的Admin管理介面
已經配置好了後端節點、mysql_users等。可參考：ProxySQL管理後端節點

如果想速成，可參考；ProxySQL初試讀寫分離

本文涉及到的實驗環境如下：

角色	主機IP	server_id	數據狀態
Proxysql	192.168.100.21	null	無
Master	192.168.100.22	110	剛安裝的全新MySQL實例
Slave1	192.168.100.23	120	剛安裝的全新MySQL實例
Slave2	192.168.100.24	130	剛安裝的全新MySQL實例

該實驗環境已經在前面的文章中搭建好，本文不再贅述一大堆的內容。環境的搭建請參考前面給出的1、2、3。

2.ProxySQL基於埠的路由

我前面寫了一篇通過MySQL Router實現MySQL讀寫分離的文章，MySQL Router實現讀寫分離的方式就是通過監聽不同埠實現的：一個埠負責讀操作，一個埠負責寫操作。這樣的路由邏輯非常簡單，配置起來也很方便。

雖然基於埠實現讀寫分離配置起來非常簡單，但是缺點也很明顯：必須在前端app的代碼中指定埠號碼。這意味著MySQL的一部分流量許可權被開發人員掌控了，換句話說，DBA無法全局控制MySQL的流量。此外，修改埠號時，app的代碼也必須做出相應的修改。

雖說有缺點，但為了我這個ProxySQL系列文章的完整性，本文還是要簡單演示ProxySQL如何基於埠實現讀寫分離。

首先修改ProxySQL監聽SQL流量的埠號，讓其監聽在不同埠上。

admin> set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
admin> save mysql variables to disk;

然後重啟ProxySQL。

[root@xuexi ~]# service proxysql stop
[root@xuexi ~]# service proxysql start

[root@xuexi ~]# netstat -tnlp | grep proxysql
tcp  0  0 0.0.0.0:6032  0.0.0.0:*   LISTEN  27572/proxysql
tcp  0  0 0.0.0.0:6033  0.0.0.0:*   LISTEN  27572/proxysql
tcp  0  0 0.0.0.0:6034  0.0.0.0:*   LISTEN  27572/proxysql

監聽到不同埠，再去修改mysql_query_rules表。這個表是ProxySQL的路由規則定製表，後文會非常詳細地解釋該表。

例如，插入兩條規則，分別監聽在6033埠和6034埠，6033埠對應的hostgroup_id=10是負責寫的組，6034對應的hostgroup_id=20是負責讀的組。

insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) 
values(1,1,6033,10,1), (2,1,6034,20,1);

load mysql query rules to runtime;
save msyql query rules to disk;

這樣就配置結束了，是否很簡單？

其實除了基於埠進行分離，還可以基於監聽地址(修改欄位proxy_addr即可)，甚至可以基於客戶端地址(修改欄位client_addr欄位即可，該用法可用於採集數據、數據分析等)。

無論哪種路由方式，其實都是在修改mysql_query_rules表，所以下麵先解釋下這個表。

3.mysql_query_rules表

可以通過show create table mysql_query_rules語句查看定義該表的語句。

下麵是我整理出來的欄位屬性。

|       COLUMN          |  TYPE   |  NULL?   | DEFAULT    |
|-----------------------|---------|----------|------------|
| rule_id   (pk)        | INTEGER | NOT NULL |            |
| active                | INT     | NOT NULL | 0          |
| username              | VARCHAR |          |            |
| schemaname            | VARCHAR |          |            |
| flagIN                | INT     | NOT NULL | 0          |
| client_addr           | VARCHAR |          |            |
| proxy_addr            | VARCHAR |          |            |
| proxy_port            | INT     |          |            |
| digest                | VARCHAR |          |            |
| match_digest          | VARCHAR |          |            |
| match_pattern         | VARCHAR |          |            |
| negate_match_pattern  | INT     | NOT NULL | 0          |
| re_modifiers          | VARCHAR |          | 'CASELESS' |
| flagOUT               | INT     |          |            |
| replace_pattern       | VARCHAR |          |            |
| destination_hostgroup | INT     |          | NULL       |
| cache_ttl             | INT     |          |            |
| reconnect             | INT     |          | NULL       |
| timeout               | INT     |          |            |
| retries               | INT     |          |            |
| delay                 | INT     |          |            |
| mirror_flagOU         | INT     |          |            |
| mirror_hostgroup      | INT     |          |            |
| error_msg             | VARCHAR |          |            |
| sticky_conn           | INT     |          |            |
| multiplex             | INT     |          |            |
| log                   | INT     |          |            |
| apply                 | INT     | NOT NULL | 0          |
| comment               | VARCHAR |          |            |

各個欄位的意義如下：有些欄位不理解也無所謂，後面會分析一部分比較重要的。

rule_id：規則的id。規則是按照rule_id的順序進行處理的。
active：只有該欄位值為1的規則才會載入到runtime數據結構，所以只有這些規則才會被查詢處理模塊處理。
username：用戶名篩選，當設置為非NULL值時，只有匹配的用戶建立的連接發出的查詢才會被匹配。
schemaname：schema篩選，當設置為非NULL值時，只有當連接使用schemaname作為預設schema時，該連接發出的查詢才會被匹配。(在MariaDB/MySQL中，schemaname等價於databasename)。
flagIN,flagOUT：這些欄位允許我們創建"鏈式規則"(chains of rules)，一個規則接一個規則。
**apply`：當匹配到該規則時，立即應用該規則。
client_addr：通過源地址進行匹配。
proxy_addr：當流入的查詢是在本地某地址上時，將匹配。
proxy_port：當流入的查詢是在本地某埠上時，將匹配。
digest：通過digest進行匹配，digest的值在stats_mysql_query_digest.digest中。
match_digest：通過正則表達式匹配digest。
match_pattern：通過正則表達式匹配查詢語句的文本內容。
negate_match_pattern：設置為1時，表示未被match_digest或match_pattern匹配的才算被成功匹配。也就是說，相當於在這兩個匹配動作前加了NOT操作符進行取反。
re_modifiers：RE正則引擎的修飾符列表，多個修飾符使用逗號分隔。指定了CASELESS後，將忽略大小寫。指定了GLOBAL後，將替換全局(而不是第一個被匹配到的內容)。為了向後相容，預設只啟用了CASELESS修飾符。
replace_pattern：將匹配到的內容替換為此欄位值。它使用的是RE2正則引擎的Replace。註意，這是可選的，當未設置該欄位，查詢處理器將不會重寫語句，只會緩存、路由以及設置其它參數。
destination_hostgroup：將匹配到的查詢路由到該主機組。但註意，如果用戶的transaction_persistent=1(見mysql_users表)，且該用戶建立的連接開啟了一個事務，則這個事務內的所有語句都將路由到同一主機組，無視匹配規則。
cache_ttl：查詢結果緩存的時間長度(單位毫秒)。註意，在ProxySQL 1.1中，cache_ttl的單位是秒。
reconnect：目前不使用該功能。
timeout：被匹配或被重寫的查詢執行的最大超時時長(單位毫秒)。如果一個查詢執行的時間太久(超過了這個值)，該查詢將自動被殺掉。如果未設置該值，將使用全局變數mysql-default_query_timeout的值。
retries：當在執行查詢時探測到故障後，重新執行查詢的最大次數。如果未指定，則使用全局變數mysql-query_retries_on_failure的值。
delay：延遲執行該查詢的毫秒數。本質上是一個限流機制和QoS，使得可以將優先順序讓位於其它查詢。這個值會寫入到mysql-default_query_delay全局變數中，所以它會應用於所有的查詢。將來的版本中將會提供一個更高級的限流機制。
mirror_flagOUT和mirror_hostgroup：mirroring相關的設置，目前mirroring正處於實驗階段，所以不解釋。
error_msg：查詢將被阻塞，然後向客戶端返回error_msg指定的信息。
sticky_conn：當前還未實現該功能。
multiplex：如果設置為0，將禁用multiplexing。如果設置為1，則啟用或重新啟用multiplexing，除非有其它條件(如用戶變數或事務)阻止啟用。如果設置為2，則只對當前查詢不禁用multiplexing。預設值為NULL，表示不會修改multiplexing的策略。
log：查詢將記錄日誌。
apply：當設置為1後，當匹配到該規則後，將立即應用該規則，不會再評估其它的規則(註意：應用之後，將不會評估mysql_query_rules_fast_routing中的規則)。
comment：註釋說明欄位，例如描述規則的意義。

4.基於mysql username進行路由

基於mysql user的配置方式和基於埠的配置是類似的。

需要註意，在插入mysql user到mysql_users表中時，就已經指定了預設的路由目標組，這已經算是一個路由規則了(只不過是預設路由目標)。當成功匹配到mysql_query_rules中的規則時，這個預設目標就不再生效。所以，通過預設路由目標，也能簡單地實現讀寫分離。

例如，在後端MySQL Server上先創建好用於讀、寫分離的用戶。例如，root用戶用於寫操作，reader用戶用於讀操作。

# 在master節點上執行：
grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';
grant select,show databases,show view on *.* to reader@'192.168.100.%' identified by 'P@ssword1!';

然後將這兩個用戶添加到ProxySQL的mysql_users表中，並創建兩條規則分別就有這兩個用戶進行匹配。

insert into mysql_users(username,password,default_hostgroup) 
values('root','P@ssword1!',10),('reader','P@ssword1!',20);

load mysql users to runtime;
save mysql users to disk;

delete from mysql_query_rules;      # 為了測試，先清空已有規則

insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) 
values(1,1,'root',10,1),(2,1,'reader',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

當然，在上面演示的示例中，mysql_query_rules中基於username的規則和mysql_users中這兩個用戶的預設規則是重覆了的。

5.基於資料庫名稱進行路由

ProxySQL支持基於schemaname進行路由。這在一定程度上實現了簡單的sharding功能。例如，將後端MySQL集群中的節點A和節點B定義在不同主機組中，ProxySQL將所有對於DB1庫的查詢路由到節點A所在的主機組，將所有對DB2庫的查詢路由到節點B所在的主機組。

只需配置一個schemaname欄位就夠了，好簡單，是不是感覺很爽。但想太多了，ProxySQL的schemaname欄位只是個雞肋，要實現分庫sharding，只能通過正則匹配、查詢重寫的方式來實現。

例如，原語句如下，用於找出浙江省的211大學。

select * from zhongguo.university where prov='Zhejiang' and high=211;

按省份分庫後，通過ProxySQL的正則替換，將語句改寫為如下SQL語句：

select * from Zhejiang.university where 1=1 high=211;

然後還可以將改寫後的SQL語句路由到指定的主機組中，實現真正的分庫。

這些內容比較複雜、也比較高級，在後面的文章中我會詳細解釋。

6.基於SQL語句路由

從這裡開始，開始介紹ProxySQL路由規則的核心：基於SQL語句的路由。

ProxySQL接收到前端發送的SQL語句後，首先分析語句，然後從mysql_query_rules表中尋找是否有匹配該語句的規則。如果先被username或ip/port類的規則匹配並應用，則按這些規則路由給後端，如果是被基於SQL語句的規則匹配，則啟動正則引擎進行正則匹配，然後路由給對應的後端組，如果規則中指定了正則替換欄位，則還會重寫SQL語句，然後再發送給後端。

ProxySQL支持兩種類型的SQL語句匹配方式：match_digest和match_pattern。在解釋這兩種匹配方式之前，有必要先解釋下SQL語句的參數化。

6.1 SQL語句分類：參數化

什麼是參數化？

select * from tbl where id=?

這裡將where條件語句中欄位id的值進行了參數化，也就是上面的問號?。

我們在客戶端發起的SQL語句都是完整格式的語句，但是SQL優化引擎出於優化的目的需要考慮很多事情。例如，如何緩存查詢結果、如何匹配查詢緩存中的數據並取出，等等。將SQL語句參數化是優化引擎其中的一個行為，對於那些參數相同但參數值不同的查詢語句，SQL語句認為這些是同類查詢，同類查詢的SQL語句不會重覆去編譯而增加額外的開銷。

例如，下麵的兩個語句，就是同類SQL語句：

select * from tbl where id=10;
select * from tbl where id=20;

將它們參數化後，結果如下：

select * from tbl where id=?;

通俗地講，這裡的"?"就是一個變數，任何滿足這個語句類型的值都可以傳遞到這個變數中。

所以，對參數化進行一個通俗的定義：對於那些參數相同、參數值不同的SQL語句，使用問號"?"去替換參數值，替換後返回的語句就是參數化的結果。

無論是MySQL、SQL Server還是Oracle(這個不確定)，優化引擎內部都會將語句進行參數化。例如，下麵是SQL Server的執行計劃，其中"@1"就是所謂的問號"?"。

ProxySQL也支持參數化。當前端發送SQL語句到達ProxySQL後，ProxySQL會將其參數化並分類。例如，下麵是sysbench測試過程中，ProxySQL統計的參數化語句。

+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                 |
+----+----------+------------+-------------------------------------------------------------+
| 2  | 14520738 | 50041      | SELECT c FROM sbtest1 WHERE id=?                            |
| 1  | 3142041  | 5001       | COMMIT                                                      |
| 1  | 2270931  | 5001       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 2021320  | 5003       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?            |
| 1  | 1768748  | 5001       | UPDATE sbtest1 SET k=k+? WHERE id=?                         |
| 1  | 1697175  | 5003       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?       |
| 1  | 1346791  | 5001       | UPDATE sbtest1 SET c=? WHERE id=?                           |
| 1  | 1263259  | 5001       | DELETE FROM sbtest1 WHERE id=?                              |
| 1  | 1191760  | 5001       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)     |
| 1  | 875343   | 5005       | BEGIN                                                       |
+----+----------+------------+-------------------------------------------------------------+

ProxySQL的mysql_query_rules表中有三個欄位，能基於參數化後的SQL語句進行三種不同方式的匹配：

digest：將參數化後的語句進行hash運算得到一個hash值digest，可以對這個hash值進行精確匹配。匹配效率最高。
match_digest：對digest值進行正則匹配。
match_pattern：對原始SQL語句的文本內容進行正則匹配。

如果要進行SQL語句的重寫(即正則替換)，或者對參數值匹配，則必須採用match_pattern。如果可以，儘量採用digest匹配方式，因為它的效率更高。

6.2 路由相關的幾個統計表

在ProxySQL的stats庫中，包含了幾個統計表。

admin> show tables from stats;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| stats_memory_metrics                 |
| stats_mysql_commands_counters        |     <--已執行查詢語句的統計信息
| stats_mysql_connection_pool          |     <--連接池信息
| stats_mysql_connection_pool_reset    |     <--重置連接池統計數據
| stats_mysql_global                   |     <--全局統計數據
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist              |     <--模擬show processlist的結果
| stats_mysql_query_digest             | <--本文解釋
| stats_mysql_query_digest_reset       | <--本文解釋
| stats_mysql_query_rules              | <--本文解釋
| stats_mysql_users                    |     <--各mysql user前端和ProxySQL的連接數
| stats_proxysql_servers_checksums     |     <--ProxySQL集群相關
| stats_proxysql_servers_metrics       |     <--ProxySQL集群相關
| stats_proxysql_servers_status        |     <--ProxySQL集群相關
+--------------------------------------+

這些表的內容、解釋我已經翻譯，參見：ProxySQL的stats庫。本文介紹其中3個和路由、規則相關的表。

6.2.1 stats_mysql_query_digest

這個表對於分析SQL語句至關重要，是分析語句性能、定製路由規則指標的最主要來源。

剛纔已經解釋過什麼是SQL語句的參數化，還說明瞭ProxySQL會將參數化後的語句進行hash計算得到它的digest，這個統計表中記錄的就是每個參數化分類後的語句對應的統計數據，包括該類語句的執行次數、所花總時間、所花最短、最長時間，還包括語句的文本以及它的digest。

如下圖：

以下是各個欄位的意義：

hostgroup：查詢將要路由到的目標主機組。如果值為-1，則表示命中了查詢緩存，直接從緩存取數據返回給客戶端。
schemaname：當前正在執行的查詢所在的schema名稱。
username：MySQL客戶端連接到ProxySQL使用的用戶名。
digest：一個十六進位的hash值，唯一地代表除了參數值部分的查詢語句。
digest_text：參數化後的SQL語句的文本。
count_star：該查詢(參數相同、值不同)總共被執行的次數。
first_seen：unix格式的timestamp時間戳，表示該查詢首次被ProxySQL路由出去的時間點。
last_seen：unix格式的timestamp時間戳，到目前為止，上一次該查詢被ProxySQL路由出去的時間點。
sum_time：執行該類查詢所花的總時間(單位微秒)。在想要找出程式中哪部分語句消耗時間最長的語句時非常有用，此外根據這個結果還能提供一個如何提升性能的良好開端。
min_time, max_time：執行該類查詢的時間範圍。min_time表示的是目前為止執行該類查詢所花的最短時間，max_time則是目前為止，執行該類查詢所花的最長時間，單位都是微秒。

註意，該表中的查詢所花時長是指ProxySQL從接收到客戶端查詢開始，到ProxySQL準備向客戶端發送查詢結果的時長。因此，這些時間更像是客戶端看到的發起、接收的時間間隔(儘管客戶端到服務端數據傳輸也需要時間)。更精確一點，在執行查詢之前，ProxySQL可能需要更改字元集或模式，可能當前後端不可用(當前後端執行語句失敗)而找一個新的後端，可能因為所有連接都繁忙而需要等待空閑連接，這些都不應該計算到查詢執行所花時間內。

其中hostgroup、digest、digest_text、count_start、{sum,min,max}_time這幾列最常用。

例如：

admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;
+----+------------+----------+--------------------+------------------------+
| hg | count_star | sum_time | digest             | digest_text            |
+----+------------+----------+--------------------+------------------------+
| 10 | 4          | 2412     | 0xADB885E1F3A7A5C2 | select * from test2.t1 |
| 10 | 6          | 4715     | 0x57497F236587B138 | select * from test1.t1 |
+----+------------+----------+--------------------+------------------------+

從中分析，兩個語句都路由到了hostgroup=10的組中，第一個語句執行了4次，這4次總共花費了2412微秒(即2.4毫秒)，第二個語句執行了6次，總花費4.7毫秒。還給出了這兩個語句參數化後的digest值，以及參數化後的SQL文本。

6.2.2 stats_mysql_query_digest_reset

這個表的表結構和stats_mysql_query_digest是完全一樣的，只不過每次從這個表中檢索數據(隨便檢索什麼，哪怕where 1=0)，都會重置stats_mysql_query_digest表中已統計的數據。

6.2.3 stats_mysql_query_rules

這個表只有兩個欄位：

rule_id：對應的是規則號碼。
hits，對應的是每個規則被命中了多少次。

6.3 基於SQL語句路由：digest

digest匹配規則是對digest進行精確匹配。

例如，從stats_mysql_query_digest中獲取兩個對應的digest值。註意，現在它們的hostgroup_id=10。

admin> select hostgroup hg,count_star,sum_time,digest,digest_text from stats_mysql_query_digest;
+----+------------+----------+--------------------+------------------------+
| hg | count_star | sum_time | digest             | digest_text            |
+----+------------+----------+--------------------+------------------------+
| 10 | 4          | 2412     | 0xADB885E1F3A7A5C2 | select * from test2.t1 |
| 10 | 6          | 4715     | 0x57497F236587B138 | select * from test1.t1 |
+----+------------+----------+--------------------+------------------------+

插入兩條匹配這兩個digest的規則：

insert into mysql_query_rules(rule_id,active,digest,destination_hostgroup,apply) 
values(1,1,"0xADB885E1F3A7A5C2",20,1),(2,1,"0x57497F236587B138",10,1);

然後測試

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

再去查看規則的路由命中情況：


admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
| 2       | 1    |
+---------+------+

查看路由的目標：

admin> select hostgroup hg,count_star cs,digest,digest_text from stats_mysql_query_digest;
+----+----+--------------------+------------------------+
| hg | cs | digest             | digest_text            |
+----+----+--------------------+------------------------+
| 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 |
| 10 | 1  | 0x57497F236587B138 | select * from test1.t1 |
+----+----+--------------------+------------------------+

可見，基於digest的精確匹配規則已經生效。

6.4 基於SQL語句路由：match_digest

match_digest是對digest做正則匹配，但註意match_pattern欄位中給的規則不是hash值，而是SQL語句的文本匹配規則。

ProxySQL支持兩種正則引擎：

1.PCRE
2.RE2

老版本中預設的正則引擎是RE2，現在預設的正則引擎是PCRE。可從變數mysql-query_processor_regex獲知當前的正則引擎是RE2還是PCRE：

Admin> select @@mysql-query_processor_regex;
+-------------------------------+
| @@mysql-query_processor_regex |
+-------------------------------+
| 1                             |
+-------------------------------+

其中1代表PCRE，2代表RE2。

在mysql_query_rules表中有一個欄位re_modifiers，它用於定義正則引擎的修飾符，預設已經設置caseless，表示正則匹配時忽略大小寫，所以select和SELECT都能匹配。此外，還可以設置global修飾符，表示匹配全局，而非匹配第一個，這個在重寫SQL語句時有用。

(RE2引擎無法同時設置caseless和global，即使它們都設置了也不會生效。所以，將預設的正則引擎改為了PCRE)

在進行下麵的實驗之前，先把mysql_query_rules表清空，並將規則的統計數據也清空。

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset;

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) 
values (1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);

load mysql query rules to runtime;
save mysql query rules to disk;

然後分別執行：

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

查看規則匹配結果：

admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
| 2       | 1    |
+---------+------+

admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;
+----+----+--------------------+------------------------+
| hg | cs | digest             | dt                     |
+----+----+--------------------+------------------------+
| 10 | 1  | 0x57497F236587B138 | select * from test1.t1 |
| 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 |
+----+----+--------------------+------------------------+

顯然，命中規則，且按照期望進行路由。

如果想對match_digest取反，即不被正則匹配的SQL語句才命中規則，則設置mysql_query_rules表中的欄位negate_match_pattern=1。同樣適用於下麵的match_pattern匹配方式。

6.5 基於SQL語句路由：match_pattern

和match_digest的匹配方式類似，但match_pattern是基於原始SQL語句進行匹配的，包括參數值。有兩種情況必須使用match_pattern：

重寫SQL語句，即同時設置了replace_pattern欄位。
對參數的值進行匹配。

如果想對match_pattern取反，即不被正則匹配的SQL語句才命中規則，則設置mysql_query_rules表中的欄位negate_match_pattern=1。

例如：

## 清空規則以及規則的統計數據
delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) 
values(1,1,"^select .* test2.*",20,1),(2,1,"^select .* test1.*",10,1);

load mysql query rules to runtime;
save mysql query rules to disk;

執行查詢：

mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test1.t1;"
mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e "select * from test2.t1;"

然後查看匹配結果：

admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
| 2       | 1    |
+---------+------+

admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;
+----+----+--------------------+------------------------+
| hg | cs | digest             | dt                     |
+----+----+--------------------+------------------------+
| 20 | 1  | 0xADB885E1F3A7A5C2 | select * from test2.t1 |
| 10 | 1  | 0x57497F236587B138 | select * from test1.t1 |
+----+----+--------------------+------------------------+

再來看看匹配參數值(雖然幾乎不會這樣做)。這裡要測試的語句如下：

mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test1.t1 where name like 'malong%';"
mysql -uroot -p123456 -h127.0.0.1 -P6033 -e "select * from test2.t1 where name like 'xiaofang%';"

現在插入兩條規則，對參數"malong%"和"xiaofang"進行匹配。

## 清空規則以及規則的統計數據
delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) 
values(1,1,"malong",20,1),(2,1,"xiaofang",10,1);

load mysql query rules to runtime;
save mysql query rules to disk;

執行上面的兩個查詢語句，然後查看匹配結果：

admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
| 2       | 1    |
+---------+------+

admin> select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;
+----+----+--------------------+------------------------------------------+
| hg | cs | digest             | dt                                       |
+----+----+--------------------+------------------------------------------+
| 20 | 1  | 0x0C624EDC186F0217 | select * from test1.t1 where name like ? |
| 10 | 1  | 0xA38442E236D915A7 | select * from test2.t1 where name like ? |
+----+----+--------------------+------------------------------------------+

已按預期進行路由。

7.實用的讀寫分離

一個極簡單卻大有用處的讀、寫分離功能：將預設路由組設置為寫組，然後再插入下麵兩個select語句的規則。

# 10為寫組，20為讀組
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
       (2,1,'^SELECT',20,1);

但需要註意的是，這樣的規則只適用於小環境下的讀寫分離，對於稍複雜的環境，需要對不同語句進行開銷分析，對於開銷大的語句需要制定專門的路由規則。在之後的文章中我會稍作分析。

8.總結

ProxySQL能通過ip、port、client_ip、username、schemaname、digest、match_digest、match_pattern實現不同方式的路由，方式可謂繁多。特別是基於正則匹配的靈活性，使得ProxySQL能滿足一些比較複雜的環境。

總的來說，ProxySQL主要是通過digest、match_digest和match_pattern進行規則匹配的。在本文中，只是介紹了匹配規則的基礎以及簡單的用法，為進軍後面的文章做好鋪墊。

MySQL中間件之ProxySQL(7)：詳述ProxySQL的路由規則