ProxySQL官档翻译__15_ProxySQL读写分离

15_ProxySQL读写分离

备注：文章编写时间201904-201905期间，后续官方在github的更新没有被写入

~

ProxySQL读写分离设置[ProxySQL Read Write Split]

查询路由是ProxySQL的核心功能之一。读写分离可能是最常用的查询路由之一，而另一种最常用的查询路由实现是用于分片(sharding)。

一、使用不同的端口实现读写分离[Read/write split using different ports]

如果使用类似haproxy的代理，可以将其配置为监听两个端口：一个端口作为写入的端口，另一个端口作为读取的端口。人们经常会问如何使用相同的方法配置ProxySQL，以及如何现实基于传入端口的路由查询。

下面是一个关于如何基于传入端口实现查询路由的示例，要在ProxySQL的Admin管理界面运行下面的内容。这里假设您已经在正确的hostgroups中配置了master和slaves：

hostgroup 10中的mysql writer，hostgroup 20中的mysql readers。如果您正在使用Galera或组复制，则将应用类似的方法。步骤如下：

1、修改接入端口相关参数

将ProxySQL配置为监听两个端口并重新启动：mysql-interfaces 是少数几个在运行时无法动态更改，需要重新启动的变量之一。

1）当前参数值

Admin> SELECT * FROM global_variables WHERE variable_name ='mysql-interfaces';
+------------------+----------------+
| variable_name    | variable_value |
+------------------+----------------+
| mysql-interfaces | 0.0.0.0:6033   |
+------------------+----------------+
1 row in set (0.00 sec)

或用SHOW命令
Admin> SHOW VARIABLES LIKE 'mysql-interfaces';
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+--------------+
1 row in set (0.00 sec)           

2）修改配置

Admin> UPDATE global_variables SET variable_value='0.0.0.0:6401;0.0.0.0:6402' WHERE variable_name ='mysql-interfaces';
或
Admin> SET mysql-interfaces = '0.0.0.0:6401;0.0.0.0:6402';           

3）查看修改结果

Admin> SELECT * FROM global_variables where variable_name ='mysql-interfaces';
+------------------+---------------------------+
| variable_name    | variable_value            |
+------------------+---------------------------+
| mysql-interfaces | 0.0.0.0:6401;0.0.0.0:6402 |
+------------------+---------------------------+
1 row in set (0.00 sec)           

4）将修改结果持久化

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.00 sec)           

5）重启ProxySQL服务

Admin> PROXYSQL RESTART;           

2、根据传入端口添加路由规则

1）查看当前后端MySQL主机组配置

Admin> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)           

2）清除旧的分组

Admin> DELETE FROM mysql_servers ;
Query OK, 6 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)           

3）新增mysql writer组10和mysql readers组20

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES(10,'188.188.0.68',3306),(20,'188.188.0.69',3306),(20,'188.188.0.70',3306);
Query OK, 3 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)           

4）查看当前拥有的规则

Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 5       | 1      | NULL       | NULL                  | 1     |
| 10      | 1      | NULL       | 2                     | 1     |
| 20      | 1      | NULL       | 2                     | 0     |
| 30      | 1      | NULL       | NULL                  | 1     |
+---------+--------+------------+-----------------------+-------+
4 rows in set (0.00 sec)           

5）删除旧规则

Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
Empty set (0.00 sec)           

6）插入路由规则

Admin> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
    -> VALUES (1,1,6401,10,1), (2,1,6402,20,1);
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1       | 1      | 6401       | 10                    | 1     |
| 2       | 1      | 6402       | 20                    | 1     |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)           

7）将修改后的配置加载到RUNTIME层

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status       | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 188.188.0.70 | 3306 | 0         | OFFLINE_HARD | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.70 | 3306 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.69 | 3306 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 188.188.0.68 | 3306 | 0         | ONLINE       | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM runtime_mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1       | 1      | 6401       | 10                    | 1     |
| 2       | 1      | 6402       | 20                    | 1     |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)           

8）将修改后的配置持久化到DISK层

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.02 sec)

Admin> SELECT * FROM disk.mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 188.188.0.68 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.69 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 188.188.0.70 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM disk.mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1       | 1      | 6401       | 10                    | 1     |
| 2       | 1      | 6402       | 20                    | 1     |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)           

到此配置完成！

现在，所有到 6401 端口的查询都将发送到hostgroup 10中的mysql服务器，而所有到 6402 端口的查询都将发送到hostgroup 20中的一个mysql服务器。

3、验证结果

1）查看当前规则命中信息

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply 
    -> FROM mysql_query_rules 
    -> NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 0    | 1       | NULL         | NULL          | NULL            | NULL      | 1     |
| 0    | 2       | NULL         | NULL          | NULL            | NULL      | 1     |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.01 sec)           

2）在后端可写MySQL上新建测试表

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)           

3）通过读写账户 6401 连入 ProxySQL 执行写入操作：

[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6401
mysql> use test;
mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)           

4）查看ProxySQL中的信息

查看Proxy SQL中进程信息

Admin> show full processlist;
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user    | db   | cli_host     | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2        | 134       | test_rw | test | 188.188.0.70 | 37072    | 10        |            |            |          |          | Sleep   | 10010   |      |              |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)           

可以看到，我们在188.188.0.70发起连接，操作的是写组 hostgroup 10，库为test；和我们预期的一样。

查看规则命中信息

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply 
    -> FROM mysql_query_rules  NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 6    | 1       | NULL         | NULL          | NULL            | NULL      | 1     |
| 0    | 2       | NULL         | NULL          | NULL            | NULL      | 1     |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)           

规则1有了命中信息。

备注:只要是向 6401 发出的指令，如show、use等都会使hits增加。

5）通过读写账户 6402 连入 ProxySQL 执行读取操作：

[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6402
mysql> SELECT * FROM test.t1 ;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)           

6）查看ProxySQL中的信息

Admin> show full processlist;
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user    | db                 | cli_host     | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 0        | 135       | test_rw | information_schema | 188.188.0.70 | 47732    | 20        |            |            |          |          | Sleep   | 18018   |      |              |
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)           

可以看到，我们在188.188.0.70发起连接，操作的是写组 hostgroup 20，库为test；和我们预期的一样。

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply 
    -> FROM mysql_query_rules  NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 6    | 1       | NULL         | NULL          | NULL            | NULL      | 1     |
| 2    | 2       | NULL         | NULL          | NULL            | NULL      | 1     |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)           

规则2也有了命中信息。

说明：如果先前连入过，则会受到连接池的影响，在连入时不会产生命中数增加。

到此，不同端口的读写分离配置完毕！！

二、基于传入端口的读写分离限制[Limitation of read/write split based on incoming port]

在前面提到过人们经常会问如何配置ProxySQL以使用基于传入端口的路由。虽然这有时是一种有效的方法，但在我看来它有一个很大的缺点：

使用数据库的应用程序需要具有内置的读写分离功能，以便区分读和写。但通常情况并非如此。

通常应用程序只使用一个连接端点，当然它是MySQL主节点。如果使用ProxySQL，则可以在单个端口中接受所有的流量，并可以根据查询类型分析流量以执行读/写拆分。这非常方便，因为它不需要任何应用程序更改。

尽管如此，其主要优势不是能够在不更改应用程序的情况下路由流量。主要的优点是DBA现在有了控制发送到数据库流量的工具。DBA是一个在午夜，由于数据库服务器过载，而被叫醒的群体；而且，因为当时周围没有开发人员，想在应用程序中进行更改是不可能的；但他们现在有了控制流量的选项。

三、(基于1个端口)使用正则表达式的实现基本读写分离[basic read/write split using regex]

在这一段中，将展示一个如何使用正则表达式来实现读写分离的示例。

-- >>>>>>>>>>>>>>>>>>配置方式一(不建议):

！！！！请不要在生产中使用下面这个示例配置！！！！

Admin> SHOW VARIABLES LIKE 'mysql_interfaces';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| mysql-interfaces | 0.0.0.0:6401;0.0.0.0:6402 |
+------------------+---------------------------+
1 row in set (0.00 sec)           

改用一个端口对外提供接入。

Admin> SET mysql-interfaces ='0.0.0.0:6033';
Query OK, 1 row affected (0.00 sec)           

将修改持久化

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.02 sec)           

重启ProxySQL服务，使配置生效

Admin> PROXYSQL RESTART;           

查看修改结果

Admin> SHOW VARIABLES LIKE 'mysql_interfaces';
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+--------------+
1 row in set (0.00 sec)           

2、实验前删除以前创建的查询规则：

Admin> DELETE FROM mysql_query_rules;
Query OK, 6 rows affected (0.00 sec)

Admin> SELECT * FROM mysql_query_rules ;
Empty set (0.00 sec)           

3、创建读/写的基本规则：

1）修改目标用户的默认组

Admin> SELECT username,active,default_hostgroup FROM mysql_users WHERE username='test_rw';
+----------+--------+-------------------+
| username | active | default_hostgroup |
+----------+--------+-------------------+
| test_rw  | 1      | 1                 |
+----------+--------+-------------------+
1 row in set (0.00 sec)

# by default, all goes to HG10
Admin> UPDATE mysql_users SET default_hostgroup=10 WHERE username='test_rw';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT username,active,default_hostgroup FROM mysql_users WHERE username='test_rw';
+----------+--------+-------------------+
| username | active | default_hostgroup |
+----------+--------+-------------------+
| test_rw  | 1      | 10                |
+----------+--------+-------------------+
1 row in set (0.00 sec)           

2）加载配置到RUNTIME层，使其生效

Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_mysql_users WHERE username='test_rw';
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 10                |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 10                |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)           

3）持久化配置到DISK层

Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM disk.mysql_users WHERE username='test_rw';
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 10                |                | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)           

4）插入读写规则

Admin> 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);
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 2       | 1      | ^SELECT              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)           

5）加载配置到RUNTIME层，使其生效

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM runtime_mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 2       | 1      | ^SELECT              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)           

6）持久化配置到DISK层

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM disk.mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 2       | 1      | ^SELECT              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)           

现在路由将会按如下工作：

1）所有 SELECT FOR UPDATE 查询都发往 HG10 ;

2）其他所有的 SELECT 查询都发往 HG20 ;

3）除以上2种查询以外，其他的查询都将发往 HG10 (默认组);

4、结果验证

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
    -> FROM mysql_query_rules 
    -> NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest         | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 0    | 1       | ^SELECT.*FOR UPDATE$ | NULL          | NULL            | NULL      | 1     |
| 0    | 2       | ^SELECT              | NULL          | NULL            | NULL      | 1     |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)           

规则1、2都是0。

2）通过 6033 端口连入 ProxySQL 执行操作：

[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6033
mysql> use test;   ==>触发一次规则2 hits

mysql> select * from t1;    ==>触发一次规则2 hits
Empty set (0.00 sec)

mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;    ==>触发一次规则2 hits           

3）操作后，查看ProxySQL中的信息

查看连接信息

Admin> SHOW FULL PROCESSLIST;
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user    | db   | cli_host     | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2        | 3         | test_rw | test | 188.188.0.70 | 55705    | 20        |            |            |          |          | Sleep   | 22022   |      |              |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)           

最后的select请求流入了H20，与预设一致。

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
    -> FROM mysql_query_rules 
    -> NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest         | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 0    | 1       | ^SELECT.*FOR UPDATE$ | NULL          | NULL            | NULL      | 1     |
| 3    | 2       | ^SELECT              | NULL          | NULL            | NULL      | 1     |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)           

三次触发，使得hits增加了3。

4）测试FOR UPDATE

mysql> select * from t1 for update ; 
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)           

5）操作后，查看ProxySQL中的信息

Admin> SHOW FULL PROCESSLIST;                                                                                                                                                 
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user    | db   | cli_host     | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2        | 3         | test_rw | test | 188.188.0.70 | 55705    | 10        |            |            |          |          | Sleep   | 12012   |      |              |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)           

FOR UPDATE请求流入了HG10，与预设一致。

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
    -> FROM mysql_query_rules 
    -> NATURAL JOIN stats.stats_mysql_query_rules 
    -> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest         | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 1    | 1       | ^SELECT.*FOR UPDATE$ | NULL          | NULL            | NULL      | 1     |
| 3    | 2       | ^SELECT              | NULL          | NULL            | NULL      | 1     |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)           

一次请求，使得hits增加了1。

请注意，我认为上述方法不是读取/写入拆分的好方法。

我经常使用这个例子来描述如何配置规则，但它却经常被错误地解释为配置读/写拆分的方法。

！！！！请不要在生产中使用上述示例配置！！！！

-- >>>>>>>>>>>>>>>>>>配置方式二(推荐):

在下面这段中，将展示一种更好的方法。

现在，让我们删除所有规则：

Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK; 
Query OK, 0 rows affected (0.02 sec)

Admin> SELECT * FROM runtime_mysql_query_rules;
Empty set (0.00 sec)

Admin> SELECT * FROM disk.mysql_query_rules;
Empty set (0.00 sec)           

1、使用regex和digest进行读/写拆分[read/write split using regex and digest]

有效设置读/写拆分的配置过程如下：

1）配置 ProxySQL 只将所有流量发送到一个 MySQL 节点——master(写和读);

2）在 stats_mysql_query_digest 中查找执行成本较高的 SELECT 语句;

3）确定哪些成本较高的 SELECT 语句应该被移动到读节点上执行；

4）配置 mysql_query_rules 将需要移动的 SELECT 语句发送到读节点；

因此，这个想法非常简单：只发送你想发送给 Slave/Reader 的内容，而不只是任何SELECT语句。

2、使用stats_mysql_query_digest查找成本较高的查询[Find expensive queries using stats_mysql_query_digest]

下面是一个示例列表，说明如何识别可以发送给Readers的潜在查询。由于 ProxySQL 在表中记录了所有度量信息，所以可以创建复杂的查询来收集信息。

这些结果是基于一个运行了几个月的非常繁忙的 ProxySQL 实例；到目前为止，该实例已经处理了大约千亿个查询。

1）根据总执行时间查找最耗时的前5个查询：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats.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)           

2）根据计数查找前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查询缓存可以帮助您！

3）根据最长执行时间查找前5个查询：

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.4ms，最大执行时间为237.34秒。也许值得调查一下为什么执行时间不均匀。

4）查找按总执行时间排序的前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）查找按总执行时间排序的前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)           

所有这些查询都需要在master上执行？如果一个查询的平均执行时间超过1秒，则答案可能是"No"。

对于某些应用程序，即使运行平均执行时间为15毫秒的查询也可能需要转到slave。

6）编写查询规则

在与应用程序开发人员确认之后，例如，我们可以将digset为 0x38BE36BDFFDBE638 的查询转移到Slave上执行：

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)

VALUES (1,1,'0x38BE36BDFFDBE638',20,1);

7）同样，假设要将所有以SELECT COUNT(*)开头的查询都可以转到Slaves

查看该类语句的执行情况：

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;           

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT COUNT\(\*\)',20,1);           

8）最后，将每个规则加载到RUNTIME层和持久化到DISK层：

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent           

四、结论[Conclusion]