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，而不需要更改任何应用程序"的能力。
