ProxySQL 读写分离方法

转载自：https://www.jianshu.com/p/597b840bf70c

(使用正则表达式实现基本的读/写分离)
在这一部分，我将通过一个示例来演示如何通过正则表达式来实现读/写分离。
首先，我们需要将之前创建的查询规则删除：
DELETE FROM mysql_query_rules;

然后，为读/写分别创建对应的基本规则：
UPDATE mysql_users SET default_hostgroup=10; # 所有的查询将默认路由到 HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;     # 如果想要将配置持久化保存到磁盘，就执行该语句
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);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;    # 如果想要将配置持久化保存到磁盘，就执行该语句

现在，将按照如下规则对查询语句进行路由：

    所有SELECT FOR UPDATE语句将路由给HG10。
    其它所有的SELECT语句都将路由给HG20。
    其它所有查询也都将路由给HG10(这是默认主机组)
    注意，我并不认为上面的读/写分离规则是多好的方法。我经常使用这个例子来描述如何配置规则，但它经常被误解为配置读/写分离的方式 。稍后我将演示更好的读/写方法。
    现在，将上面的规则全部移除：

DELETE FROM mysql_query_rules;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # 如果想将配置持久化到磁盘，执行该语句
read/write split using regex and digest

(使用正则表达式和digest配置读写分离)
有效地设置读/写分离的配置过程如下:

    首先配置ProxySQL，让所有的请求都发送给单个MySQL节点master(既读又写)
    检查stats_mysql_query_digest表中开销最大的SELECT语句
    然后决定这些大开销的语句中哪些允许路由给负责读的节点
    然后配置mysql_query_rules表(创建规则)，将大开销的SELECT语句路由给读组
    这个方案的本质非常简答：只发送那些想要发送给slaves/readers的查询，而不仅仅通过SELECT语句来判断。

Find expensive queries using stats_mysql_query_digest

(从stats_mysql_query_digest表中找出开销较大的查询语句)
这里给出了几个示例，用来解释如何找出哪些应该路由给读组的潜在查询。
由于ProxySQL将所有的统计数据和指标都导出到了各种表中，因此可以根据这些表创建复杂的查询来收集想要的信息。
下面是在一个非常繁忙的ProxySQL实例下收集的数据，这个实例已经持续运行了几个月，处理的数据有上千亿。

    首先查找出总执行时间排在前5的查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 |
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  |
| 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.01 sec)

    再找出执行次数排在前5的查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 |
| 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  |
| 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.00 sec)

这些查询可以被缓存吗？ProxySQL的查询缓存可以实现该功能。

    找出最长执行时间排在前5的查询(译注：每个查询都有最长执行时间max_time和最短执行时间min_time)：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 |
| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 |
| 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
5 rows in set (0.01 sec)

这个具体的结果表明，一些查询具有非常高的最大执行时间，而最短执行时间却非常小，并且平均执行速度也相当慢。
例如，digest值为0x36CE5295726DB5B4的查询，平均执行时间为1.27秒，但最小执行时间才0.4毫秒，最大时间久到了237.34秒。对于这样的查询，也许有必要研究一下为什么执行时间不均匀。

    找出总执行时间排在前5，且最短执行时间至少为1毫秒的查询：

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+-------------+----------+----------+----------+
| digest             | SUBSTR(digest_text,0,20) | count_star | sum_time    | avg_time | min_time | max_time |
+--------------------+--------------------------+------------+-------------+----------+----------+----------+
| 0x9EED412C6E63E477 | SELECT a.id as acco      | 961733     | 24115349801 | 25074    | 10994    | 7046628  |
| 0x8DDD43A9EA37750D | Select ( Coalesce((      | 107069     | 3156179256  | 29477    | 1069     | 24600674 |
| 0x9EED412C6E63E477 | SELECT a.id as acco      | 91996      | 1883354396  | 20472    | 10095    | 497877   |
| 0x08B23A268C35C08E | SELECT id as reward      | 49401      | 244088592   | 4940     | 1237     | 1483791  |
| 0x437C846F935344F8 | SELECT Distinct i.e      | 164        | 163873101   | 999226   | 1383     | 7905811  |
+--------------------+--------------------------+------------+-------------+----------+----------+----------+
5 rows in set (0.01 sec)

    找出总执行时间排在前5，且平均执行时间至少为1秒的查询。同时输出每个查询的总时间占所有查询总时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+--------------+----------+-------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | pct   |
+--------------------+--------------------------+------------+--------------+----------+-------+
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 2.11  |
| 0xD38895B4F4D2A4B3 | SELECT instance.name as  | 9783       | 12409642528  | 1268490  | 0.141 |
| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 0.015 |
+--------------------+--------------------------+------------+--------------+----------+-------+
3 rows in set (0.00 sec)

    找出总执行时间排在前5，且平均执行时间至少为15毫秒的查询。同时输出每个查询的总时间占所有查询总时间的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+----------+--------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      | avg_time | pct    |
+--------------------+--------------------------+------------+---------------+----------+--------+
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59360371   | 1096562204931 | 18472    | 13.006 |
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994  | 1270249  | 2.205  |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592281     | 40215136635   | 67898    | 0.477  |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265   | 562935   | 0.295  |
| 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768     | 24116011513   | 25074    | 0.286  |
+--------------------+--------------------------+------------+---------------+----------+--------+
5 rows in set (0.00 sec)

难道所有这些查询都应用路由到master上去执行吗？如果一个查询的平均执行时间在1秒以上，那么答案可能是no。对于某些非常繁忙且对性能要求较高的应用程序，甚至可以考虑将平均执行时间在15毫秒的查询路由给slave。
例如，我们可以决定将digest=0x38BE36BDFFDBE638的查询路由给slave：
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES(1,1,'0x38BE36BDFFDBE638',20,1);

类似地，在检查了如下语句的输出后：
SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT COUNT%' ORDER BY sum_time DESC;

我们决定将所有以SELECT COUNT(*)开头的语句路由给slave：
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES(1,1,'^SELECT COUNT\(\*\)',20,1);

最后，将配置加载到RUNTIME，并将它持久化保存到磁盘数据库中。
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; 
Conclusion

(结论：)
ProxySQL可以非常有效、非常弹性地对各种查询进行选择性地路由。
虽然对于某些应用程序来说，将所有SELECT语句路由给slaves/readers，其余都路由给writers/masters是可以接受的，但对于很多应用程序来说，其实并没这么简单。作为DBA，应当具备"使用复杂的规则配置ProxySQL，只将那些不应该路由给master的查询路由给slaves，而不需要更改任何应用程序"的能力。