返回 "ProxySQL系列文章：http://www.cnblogs.com/f ck need u/p/7586194.html" 1.不同類型的讀寫分離資料庫中間件最基本的功能就是實現讀寫分離，ProxySQL當然也支持。而且ProxySQL支持的路由規則非常靈活，不僅可以實現 ...

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

1.不同類型的讀寫分離

資料庫中間件最基本的功能就是實現讀寫分離，ProxySQL當然也支持。而且ProxySQL支持的路由規則非常靈活，不僅可以實現最簡單的讀寫分離，還可以將讀/寫都分散到多個不同的組，以及實現分庫sharding(分表sharding的規則比較難寫，但也能實現)。

本文只描述通過規則制定的語句級讀寫分離，不討論通過 ip/port, client, username, schemaname 實現的讀寫分離。

下麵描述了ProxySQL能實現的常見讀寫分離類型。

1.1 最簡單的讀寫分離

如圖。

這種模式的讀寫分離，嚴格區分後端的master和slave節點，且slave節點必須設置選項read_only=1。在ProxySQL上，分兩個組，一個寫組HG=10，一個讀組HG=20。同時在ProxySQL上開啟monitor模塊的read_only監控功能，讓ProxySQL根據監控到的read_only值來自動調整節點放在HG=10(master會放進這個組)還是HG=20(slave會放進這個組)。

這種模式的讀寫分離是最簡單的，只需在mysql_users表中設置用戶的預設路由組為寫組HG=10，併在mysql_query_rules中加上兩條簡單的規則(一個select for update，一個select)即可。

例如，下麵實現的就是這種讀寫分離模式。

mysql_replication_hostgroups: 
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment  |
+------------------+------------------+----------+
| 10               | 20               | cluster1 |
+------------------+------------------+----------+

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | master   | 3306 | ONLINE | 1      |
| 20           | slave1   | 3306 | ONLINE | 1      |
| 20           | slave2   | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_users: 
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root     | 10                |
+----------+-------------------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT              |
+---------+-----------------------+----------------------+

這種讀寫分離模式，在環境較小時能滿足絕大多數需求。但是需求複雜、環境較大時，這種模式就太過死板，因為一切都是monitor模塊控制的。

1.2 多個讀組或寫組的分離模式

前面那種讀寫分離模式，是通過monitor模塊監控read_only來調整的，所以每一個後端集群必須只能分為一個寫組，一個讀組。

但如果想要區分不同的select，並將不同的select路由到不同的節點上。例如有些查詢語句的開銷非常大，想讓它們獨占一個節點/組，其它查詢共用一個節點/組，怎麼實現？

例如，下麵這種模式。

看上去非常簡單。但是卻能適應各種需求。例如，後端做了分庫，對某庫的查詢要路由到特定的主機組(後文專門分析這種情況)。

至於各個組機組是同一個主從集群(下圖左邊)，還是互相獨立的主從集群環境(下圖右邊)，要看具體的需求，不過這種讀寫分離模式都能應付。

在實現這種模式時，前提是不能開啟monitor模塊的read_only監控功能，也不要設置 mysql_replication_hostgroup 表。

例如，下麵的配置實現的是上圖左邊的結構：寫請求路由給HG=10，對test1庫的select語句路由給HG=20，其它select路由給HG=30。

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | host1    | 3306 | ONLINE | 1      |
| 20           | host2    | 3306 | ONLINE | 1      |
| 30           | host3    | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_users: 
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root     | 10                |
+----------+-------------------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT.*test1\..*   |
| 3       | 30                    | ^SELECT              |
+---------+-----------------------+----------------------+

1.3 sharding後的讀寫分離

ProxySQL對sharding的支持比較弱，要寫sharding的路由規則真心覺得有點繁瑣。但無論如何，ProxySQL通過定製路由規則是能實現簡單的sharding的。這也算是讀寫分離的一種情況。

如下圖，將課程所在庫分為三個庫："MySQL"、"python"和"Linux"。當查詢條件中的篩選條件是MySQL時，就路由給MySQL庫所在的主機組HG=20，篩選條件是Python時，就路由給HG=10，同理HG=30。

關於ProxySQL如何實現sharding的具體細節，我後面的文章會介紹。

2.找出需要特殊對待的SQL語句

有些SQL語句執行次數較多、性能開銷較大、執行時間較長等等，這幾類語句都需要特殊對待。例如，將它們路由到獨立的節點/主機組，或者為它們開啟緩存功能。

詳細內容參見官方手冊里的一篇文章，我已經把它翻譯過了：ProxySQL Read Write Split (HOWTO)。

本文通過sysbench來模擬，以便為官方手冊里的這篇文章提供測試環境。當然，如果您會sysbench或其它性能測試工具，可無視。

1.首先創建測試資料庫sbtest。這裡我直接連接到後端的MySQL節點創建庫和表。

mysqladmin -h192.168.100.22 -uroot -pP@ssword1! -P3306 create sbtest;

2.準備測試表，假設以2張表為例，每個表中10W行數據。填充完後，兩張表表名為sbtest1和sbtest2。

SYSBENCH=/usr/share/sysbench/
sysbench --mysql-host=192.168.100.22 \
         --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=P@ssword1! \
         $SYSBENCH/oltp_common.lua \
         --tables=1 \
         --table_size=100000 \
         prepare

3.sysbench連接到ProxySQL，做只讀測試。註意下麵的選項--db-ps-mode必須設置為disable，表示禁止ProxySQL使用prepare statement，目前ProxySQL還不支持對prepare語句的緩存。不過ProxySQL作者已經將此功能提上日程了。

sysbench --threads=4 \
         --time=20 \
         --report-interval=5 \
         --mysql-host=127.0.0.1 \
         --mysql-port=6033 \
         --mysql-user=root \
         --mysql-password=P@ssword1! \
         --db-ps-mode=disable \
         $SYSBENCH/oltp_read_only.lua \
         --skip_trx=on \
         --tables=1 \
         --table_size=100000 \
         run

由於這時候還沒有設置sysbench的測試語句的路由，所以它們全都會路由到同一個主機組，例如預設的組。

4.查看stats_mysql_query_digest表，按照各種測試指標條件進行排序，例如按照總執行時間欄位sum_time降序以便找出最耗時的語句，按照count_star降序排序找出執行次數最多的語句，還可以按照平均執行時間降序等等。請參照上面列出的官方手冊文章。

例如，此處按照sum_time降序排序：

Admin> SELECT count_star,sum_time,digest,digest_text 
       FROM stats_mysql_query_digest 
       ORDER BY sum_time DESC 
       LIMIT 4;
+------------+----------+--------------------+---------------------------------------------+
| count_star | sum_time | digest             | digest_text                                 |
+------------+----------+--------------------+---------------------------------------------+
| 72490      | 17732590 | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=?            |
| 7249       | 9629225  | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 XXXXXXXXXXXX |
| 7249       | 6650716  | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id XXXXXXXXXXXX |
| 7249       | 3235986  | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id yyyyyyyyyyyy |
+------------+----------+--------------------+---------------------------------------------+

5.對那些開銷大的語句，制定獨立的路由規則，並決定是否開啟查詢緩存以及緩存過期時長。
6.寫好規則後進行測試。

MySQL中間件之ProxySQL(10)：讀寫分離方法論

1.不同類型的讀寫分離

1.1 最簡單的讀寫分離

1.2 多個讀組或寫組的分離模式

1.3 sharding後的讀寫分離

2.找出需要特殊對待的SQL語句