ProxySQL 配置ProxySQL
转载自：https://www.jianshu.com/p/212397a1be67
假定你已经对ProxySQL的架构有所了解。本文对ProxySQL的所有配置都是使用Admin管理接口完成的，该管理接口的默认端口号为6032，admin:admin是admin接口默认的具有读、写权限的用户、密码：
$ mysql -u admin -padmin -h 127.0.0.1 -P6032
首先确定当前没有任何配置项：mysql_servers、mysql_replication_hostgroups和mysql_query_rules表中都没有任何记录。
mysql> \R Admin>
PROMPT set to 'Admin> '
Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)
Add backends
在本实验中，我在本地启动了3个MySQL节点作为后端。首先将它们添加到ProxySQL中。
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21891);
Query OK, 1 row affected (0.01 sec)
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21892);
Query OK, 1 row affected (0.01 sec)
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21893);
Query OK, 1 row affected (0.00 sec)
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1 | 127.0.0.1 | 21891 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21892 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21893 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)
目前看来，一切正常。
注意：默认情况下，MySQL复制的slave节点read_only=0，所以需要先将各slave节点设置为read_only=1。
Configure monitoring
ProxySQL会持续不断地监控它已经配置好的后端。为此，配置一些变量很重要。
添加Monitor模块监控后端所使用的用户凭据(要求后端mysql节点已经创建好这些用户)。
(译注：下面的语句中使用的用户名和密码都是"monitor")
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
然后配置各种监控的时间间隔：
Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)
Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name | variable_value |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 2000 |
| mysql-monitor_connect_timeout | 200 |
| mysql-monitor_ping_interval | 2000 |
| mysql-monitor_ping_timeout | 100 |
| mysql-monitor_read_only_interval | 2000 |
| mysql-monitor_read_only_timeout | 100 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_username | monitor |
| mysql-monitor_password | monitor |
| mysql-monitor_query_variables | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_timer_cached | true |
| mysql-monitor_writer_is_also_reader | true |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)
有很多相关的变量，但在本HOWTO文档中并不全都用上。在此文档中，只需考虑已经使用过的变量即可。
对global_variables表修改后，需要先允许LOAD MYSQL VARIABLES TO RUNTIME才会加载到runtime中并生效。如果想要这些配置永久保存到磁盘数据库中，则允许SAVE MYSQL VARIABLES TO DISK。
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)
Backend's health check
现在，对后端做健康检查，确定ProxySQL是否能和这些后端节点通信。ProxySQL使用了几张表来存储这些监控信息。
Admin> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
Admin> SHOW TABLES FROM monitor;
+----------------------------------+
| tables |
+----------------------------------+
| mysql_server_connect |
| mysql_server_connect_log |
| mysql_server_ping |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+----------------------------------+
6 rows in set (0.00 sec)
现在，我们可以使用下面的查询语句来查询相关的表数据。
Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-----------+-------+------------------+----------------------+---------------+
| hostname | port | time_start_us | connect_success_time | connect_error |
+-----------+-------+------------------+----------------------+---------------+
| 127.0.0.1 | 21891 | 1456968814253432 | 562 | NULL |
| 127.0.0.1 | 21892 | 1456968814253432 | 309 | NULL |
| 127.0.0.1 | 21893 | 1456968814253432 | 154 | NULL |
| 127.0.0.1 | 21891 | 1456968812252146 | 689 | NULL |
| 127.0.0.1 | 21892 | 1456968812252146 | 424 | NULL |
| 127.0.0.1 | 21893 | 1456968812252146 | 174 | NULL |
| 127.0.0.1 | 21891 | 1456968810251585 | 569 | NULL |
| 127.0.0.1 | 21892 | 1456968810251585 | 316 | NULL |
| 127.0.0.1 | 21893 | 1456968810251585 | 155 | NULL |
| 127.0.0.1 | 21891 | 1456968808250762 | 570 | NULL |
+-----------+-------+------------------+----------------------+---------------+
10 rows in set (0.00 sec)
Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+-----------+-------+------------------+-------------------+------------+
| hostname | port | time_start_us | ping_success_time | ping_error |
+-----------+-------+------------------+-------------------+------------+
| 127.0.0.1 | 21891 | 1456968828686787 | 124 | NULL |
| 127.0.0.1 | 21892 | 1456968828686787 | 62 | NULL |
| 127.0.0.1 | 21893 | 1456968828686787 | 57 | NULL |
| 127.0.0.1 | 21891 | 1456968826686385 | 99 | NULL |
| 127.0.0.1 | 21892 | 1456968826686385 | 46 | NULL |
| 127.0.0.1 | 21893 | 1456968826686385 | 42 | NULL |
| 127.0.0.1 | 21891 | 1456968824685162 | 135 | NULL |
| 127.0.0.1 | 21892 | 1456968824685162 | 61 | NULL |
| 127.0.0.1 | 21893 | 1456968824685162 | 57 | NULL |
| 127.0.0.1 | 21891 | 1456968822684689 | 215 | NULL |
+-----------+-------+------------------+-------------------+------------+
10 rows in set (0.01 sec)
我们可以得出结论，配置的所有后端服务器节点都是健康的。
这里需要注意的一件重要的事情是，对connect和ping的监控是基于mysql_servers表的内容执行的，甚至是在其加载到RUNTIME之前。这是有意为之的：通过这种方式，可以在节点添加到生产环境之前执行一些基本的健康检查。
现在我们已经知道了所有的节点都被合理监控且它们都正常，现在启用它们。
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1 | 127.0.0.1 | 21891 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21892 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21893 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)
MySQL replication hostgroups
现在监控monitor库中的另一个表monitor.mysql_server_read_only_log。
Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
Empty set (0.00 sec)
当前该表为空。因为ProxySQL只会监控那些已经配置在mysql_replication_hostgroups表中的节点的read_only属性。而该表当前还是空表。
Admin> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)
表有何作用？有了该表，可以指定该表中的组哪些是写组，哪些是读组。
ProxySQL将会监控特定组中节点的read_only属性，并根据节点的read_only值将节点分配到读组、写组。
例如：
Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
Admin> INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)
现在，所有的节点都配置在hostgroup 1或2中，且根据read_only的值将节点移到合理的组中：
如果某节点的read_only=0，它们将移到hostgroup 1。这个组是写组。
如果某节点的read_only=1，它们将移到hostgroup 2。这个组是读组。
但此刻，它们还没有加载到RUNTIME，所以还未生效：
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1 | 127.0.0.1 | 21891 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21892 | ONLINE | 1 | 0 | 1000 | 0 |
| 1 | 127.0.0.1 | 21893 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)
执行LOAD MYSQL SERVERS TO RUNTIME将mysql_replication_hostgroups中的配置加载到RUNTIME使之生效。
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
等待几秒后，再检查状态：
Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; +-----------+-------+------------------+--------------+-----------+-------+
| hostname | port | time_start_us | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 127.0.0.1 | 21891 | 1456969634783579 | 762 | 0 | NULL |
| 127.0.0.1 | 21892 | 1456969634783579 | 378 | 1 | NULL |
| 127.0.0.1 | 21893 | 1456969634783579 | 317 | 1 | NULL |
| 127.0.0.1 | 21891 | 1456969632783364 | 675 | 0 | NULL |
| 127.0.0.1 | 21892 | 1456969632783364 | 539 | 1 | NULL |
| 127.0.0.1 | 21893 | 1456969632783364 | 550 | 1 | NULL |
| 127.0.0.1 | 21891 | 1456969630783159 | 493 | 0 | NULL |
| 127.0.0.1 | 21892 | 1456969630783159 | 626 | 1 | NULL |
| 127.0.0.1 | 21893 | 1456969630783159 | 572 | 1 | NULL |
| 127.0.0.1 | 21891 | 1456969628782328 | 433 | 0 | NULL |
+-----------+-------+------------------+--------------+-----------+-------+
10 rows in set (0.01 sec)
此时，ProxySQL正在监视后端服务器的read_only值。此外还创建了hostgroup 2，并将read_only=1(表示读节点)的节点从hostgroup 1移动到hostgroup2。
(译注：所以，这里的21891所在MySQL实例是master节点，其它两个是slave节点)
Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1 | 127.0.0.1 | 21891 | ONLINE | 1 | 0 | 1000 | 0 |
| 2 | 127.0.0.1 | 21892 | ONLINE | 1 | 0 | 1000 | 0 |
| 2 | 127.0.0.1 | 21893 | ONLINE | 1 | 0 | 1000 | 0 |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)
一切都OK后，将这些配置持久化保存到磁盘数据库中。
Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.00 sec)
MySQL Users
配置好mysql_servers后，我们可以继续配置mysql users，相关的表是mysql_users。
Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)
Admin> SHOW CREATE TABLE mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
初始时该表为空表。现在来配置它。
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);
Query OK, 1 row affected (0.00 sec)
Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | | 1 | 0 | 1 | NULL | 0 | 0 | 0 | 1 | 1 | 10000 |
| msandbox | msandbox | 1 | 0 | 1 | NULL | 0 | 0 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
这里我们留空了很多字段，它们将使用默认值。该表需要配置的最关键的字段为：
username
password
default_hostgroup
username 和 password字段的意义很明显。关于default_hostgroup字段，它是默认的路由目标主机组，当该用户发出的查询语句没有匹配到任何查询规则时，该查询语句将路由到该字段指定的默认组中的节点。
将其加载到RUNTIME并持久化到DISK。
Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
现在尝试从另一个终端使用这里创建的用户连接进来：
vagrant@ubuntu-14:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT 1"
Warning: Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+
vagrant@ubuntu-14:~$ mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 21891 |
+--------+
看上去一切OK。毫无疑问，该查询路由给了监听在21891端口的后端节点上，这是master节点，因为用户msanbox的默认组为hostgroup1，而hostgroup1是写组。
功能测试
现在我们进行一些测试("benchmark")来验证ProxySQL的功能一切正常。
假定你已经创建好了测试表，执行以下压力测试语句：
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
[ 输出结果省略 ]
所有的查询都通过ProxySQL进行。ProxySQL会导出这些语句相关的指标吗？Yes...
sysbench --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 --db-ps-mode=disable run
[ 输出结果省略 ]
ProxySQL Statistics
ProxySQL会收集很多实时的统计数据放到stats库中：
Admin> SHOW SCHEMAS;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables |
+--------------------------------+
| stats_mysql_query_rules |
| stats_mysql_commands_counters |
| stats_mysql_processlist |
| stats_mysql_connection_pool |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_global |
+--------------------------------+
7 rows in set (0.00 sec)
在stats中有很多表，我们将一一分析。
stats.stats_mysql_connection_pool
Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| 1 | 127.0.0.1 | 21891 | ONLINE | 0 | 4 | 5 | 0 | 144982 | 7865186 | 278734683 |
| 1 | 127.0.0.1 | 21892 | OFFLINE_HARD | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 127.0.0.1 | 21893 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 127.0.0.1 | 21892 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
4 rows in set (0.00 sec)
一个小插曲：此时如果将一个节点移除掉(完全移除，或从组中移出)，在内不会真的移出，而是将其标记为OFFLINE_HARD。这也是为什么上面监听在21892的节点在hostgroup 1中显示为OFFLINE_HARD的原因。
该表中记录了大量和发送到每个后端节点相关查询请求的信息。正如预料的结果，所有的查询都发送给了hostgroup 1中监听在21891的节点，它是master节点。
stats_mysql_commands_counters
那么每种类型的查询精确的统计数据呢？表stats_mysql_commands_counters回答了这个问题：
Admin> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN | 1921940 | 7249 | 4214 | 2106 | 570 | 340 | 14 | 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| COMMIT | 5986400 | 7249 | 119 | 3301 | 1912 | 1864 | 44 | 8 | 1 | 0 | 0 | 0 | 0 | 0 |
| DELETE | 2428829 | 7249 | 325 | 5856 | 585 | 475 | 5 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 2260129 | 7249 | 356 | 5948 | 529 | 408 | 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| SELECT | 40461204 | 101490 | 12667 | 69530 | 11919 | 6943 | 268 | 149 | 13 | 1 | 0 | 0 | 0 | 0 |
| UPDATE | 6635032 | 14498 | 333 | 11149 | 1597 | 1361 | 42 | 16 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)
表stats_mysql_commands_counter返回关于执行的语句类型以及执行时间分布的详细信息!
(译注：从以上结果中可看到，大多数查询是SELECT语句，SELECT的执行时间基本都小于5毫秒，其中100微秒到500微秒的最多(69530个查询在此范围内))
stats_mysql_query_digest
stats_mysql_commands_counters表中提供了非常有用的信息。但可以从stats_mysql_query_digest表获取关于查询的更多更详细信息。
Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1 | sbtest | msandbox | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=? | 72490 | 1456971810 | 1456971830 | 17732590 | 23 | 58935 |
| 1 | sbtest | msandbox | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249 | 1456971810 | 1456971830 | 9629225 | 20 | 121604 |
| 1 | sbtest | msandbox | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249 | 1456971810 | 1456971830 | 6650716 | 26 | 76159 |
| 1 | sbtest | msandbox | 0x5DBEB0DD695FBF25 | COMMIT | 7249 | 1456971810 | 1456971830 | 5986400 | 64 | 59229 |
| 1 | sbtest | msandbox | 0xCCB481C7C198E52B | UPDATE sbtest1 SET k=k+? WHERE id=? | 7249 | 1456971810 | 1456971830 | 3948930 | 44 | 47860 |
| 1 | sbtest | msandbox | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? | 7249 | 1456971810 | 1456971830 | 3235986 | 22 | 24624 |
| 1 | sbtest | msandbox | 0xE75DB8313E268CF3 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? | 7249 | 1456971810 | 1456971830 | 3211197 | 51 | 29569 |
| 1 | sbtest | msandbox | 0x5A23CA36FB239BC9 | UPDATE sbtest1 SET c=? WHERE id=? | 7249 | 1456971810 | 1456971830 | 2686102 | 23 | 27779 |
| 1 | sbtest | msandbox | 0x55319B9EE365BEB5 | DELETE FROM sbtest1 WHERE id=? | 7249 | 1456971810 | 1456971830 | 2428829 | 29 | 11676 |
| 1 | sbtest | msandbox | 0x10634DACE52A0A02 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | 7249 | 1456971810 | 1456971830 | 2260129 | 61 | 13711 |
| 1 | sbtest | msandbox | 0x4760CBDEFAD1519E | BEGIN | 7249 | 1456971810 | 1456971830 | 1921940 | 30 | 39871 |
| 1 | information_schema | msandbox | 0x9DD5A40E1C46AE52 | SELECT ? | 1 | 1456970758 | 1456970758 | 1217 | 1217 | 1217 |
| 1 | information_schema | msandbox | 0xA90D80E5831B091B | SELECT @@port | 1 | 1456970769 | 1456970769 | 273 | 273 | 273 |
| 1 | information_schema | msandbox | 0x52A2BA0B226CD90D | select @@version_comment limit ? | 2 | 1456970758 | 1456970769 | 0 | 0 | 0 |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
14 rows in set (0.00 sec)
上面所有的查询请求都发送给了hostgroup1。现在，我们想要发送一些请求给其它的slaves节点。
MySQL Query Rules
表mysql_query_rules中有很多个字段，它们能够非常弹性地控制每个查询请求如何通过ProxySQL进行路由。
该表的定义语句为：
Admin> SHOW CREATE TABLE mysql_query_rules\G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
delay INT UNSIGNED,
error_msg VARCHAR,
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0)
1 row in set (0.01 sec)
现在，我们配置将某些语句发送给slave，其它发送给master。
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)
一些注意事项：
查询规则是根据rule_id的顺序进行处理的。
只有active=1的查询规则才会被处理。因为查询规则功能太强大了，如果规则配置失误，会很难调试。所以，在多次检查后，就可以将默认的active=0改为active=1来启用它们。
第一个示例规则中使用了脱字符^和美元符$。这些是正则表达式中的元字符，分别表示开头的位置和结尾的位置。此示例的匹配对象是match_digest或match_pattern。(译注：通常需要通过正则匹配的也就是这两种字段类型)
请熟练使用正则表达式，以避免出现错误匹配。
应该注意到了上面将问号"?"进行了转义，它是正则中的一个元字符。所以说，请熟练使用正则表达式。
apply=1意味着当该规则匹配成功的话，就立即应用该规则，不再评估后面的规则。
将mysql_query_rules表简化输出：
Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2 |
| DISTINCT c FROM sbtest1 | 2 |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)
能匹配这两个规则的查询语句都会路由给slave。如果语句不能匹配这两个规则，它们将路由到默认的主机组(由defalut_hostgroup决定，对于msanbox用户而言，默认的主机组是hostgroup 1)。
下面，重置stats_mysql_query_digest表的内容，可以向stats_mysql_query_digest_reset表随便发出一个查询，例如：
SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;
查询stats_mysql_query_digest_reset表时，会自动从stats_mysql_query_digest中临时抓取数据，并truncate。
将它们加载到RUNTIME：
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
再次用sysbench进行负载测试：
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
验证一下stats_mysql_query_digest表的内容：
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------------------------------------------+
| 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? |
| 2 | 3203582 | 5001 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 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 |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)
意料之中的是，成功匹配到规则的查询语句路由给了hostgroup 2(它们是slave)。
可以将表stats_mysql_query_digest的结果进行聚合。例如：
Admin> SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
+----+---------------+-----------------+
| hg | SUM(sum_time) | SUM(count_star) |
+----+---------------+-----------------+
| 1 | 21523008 | 59256 |
| 2 | 23915965 | 72424 |
+----+---------------+-----------------+
2 rows in set (0.00 sec)
Query Caching
ProxySQL的一个常用用法是作为查询缓存服务器。默认情况下，查询结果不会进行缓存，但是可以通过设置mysql_query_rules表的cache_ttl字段(单位毫秒)来启用缓存功能。
假设我们想要将发送给slave的查询的结果缓存5秒钟。
Admin> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
Query OK, 2 rows affected (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; -- we reset the counters
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
我们再次用sysbench进行负载测试：
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
查看表stats_mysql_query_digest中的内容：
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------------------------------------------+
| 1 | 7457441 | 5963 | COMMIT |
| 1 | 6767681 | 5963 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2 | 4891464 | 8369 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4573513 | 5963 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 4531319 | 5963 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3993283 | 5963 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3482242 | 5963 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 3209088 | 5963 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 2959358 | 5963 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2415318 | 5963 | BEGIN |
| 2 | 2266662 | 1881 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 4082 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 51261 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)
我们可以看到哪些查询语句发送给了hostgroup2，现在：
它们仍然发送给hostgroup2
如果该查询的结果已经缓存，该查询将不会路由出去，而是直接从缓存中取数据返回，并将hostgroup字段标记为"-1"
命中缓存的查询的总执行时间sum_time值为0(这意味着请求在相同的事件循环中进行处理)
注意：当前还无法定义缓存所使用的最大内存空间，也无法强制对查询缓存进行选择性的flush，也无法全部flush。到目前为止，只能通过mysql_query_rules表的cache_ttl字段来控制缓存结果的缓存时间。所以在对查询缓存能做更多控制之前，请合理选择cache_ttl的缓存时间。
Query Rewrite
ProxySQL支持多种匹配查询的方式，例如flagIN、username、schemaname。
最常用的查询匹配方式是通过写正则表达式匹配查询语句的文本内容。对于文本内容的匹配，ProxySQL提供了两个不同的字段来实现两种匹配机制：
match_digest：对查询的digest部分进行正则表达式匹配，digest的字段为stats_mysql_query_digest.query_digest。
match_pattern：对未修改的语句文本进行正则表达式匹配。
为什么要提供两种不同的匹配机制？因为"对查询语句的digest部分做正则匹配的速度"比"对整个查询语句做匹配的速度"要快的多得多(例如，通过INSERT语句插入好几MB的数据时)。因此，如果可以不用对查询语句的文本进行匹配，建议使用match_digest进行匹配，它的效率要高的多。
但是，如果你想重写查询语句，就必须对原始查询语句进行匹配(使用match_pattern)，因为只有原始语句才需要进行重写。
一个查询重写示例：
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'msandbox','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
Query OK, 1 row affected (0.00 sec)
Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 1 |
| 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
测试下这个新的规则。
Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------------------------------------------+
| 1 | 8150528 | 5307 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 7341765 | 5304 | COMMIT |
| 2 | 5717866 | 7860 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4807609 | 5307 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 4164131 | 5308 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3731299 | 5307 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3156638 | 5305 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 3074430 | 5306 | UPDATE sbtest1 SET c=? WHERE id=? |
| 2 | 2857863 | 1705 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 2732332 | 5304 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2165367 | 5310 | BEGIN |
| -1 | 0 | 3602 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 45235 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)
看上去没有正确允许，因为好像没有对查询语句进行重写。
我们可以对此进行故障排除(troubleshoot)。在troubleshoot时，表stats.stats_mysql_query_rules提供的信息非常有用。
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 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 54670 | 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 5467 | 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 1 |
| 0 | 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
问题很明显：rule_id=30的规则没有被命中。
能被rule_id=30规则匹配的查询也能被rule_id=20的规则匹配，但rule_id=20在前面，且它的apply=1，使得被rule_id=20匹配后不会再做其它的匹配。
所以，将rule_id=20的那条规则的apply设置为0：
Admin> UPDATE mysql_query_rules SET apply=0 WHERE rule_id=20;
Query OK, 1 row affected (0.00 sec)
Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
现在应该能进行查询重写了。
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
注意：当运行LOAD MYSQL QUERY RULES TO RUNTIME时，不仅会重置内部的查询处理结构，还会重置stats.stats_mysql_query_rules中的计数器。
Admin> SELECT * FROM stats.stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 10 | 0 |
| 20 | 0 |
| 30 | 0 |
+---------+------+
3 rows in set (0.00 sec)
再次测试：
Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
验证一下结果：
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 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 4856 | 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 4856 | 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
显然，查询重写功能已经正常运行。
那么查询执行的情况呢？
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+-------------------------------------------------------------+
| 1 | 7240757 | 4856 | COMMIT |
| 1 | 6127168 | 4856 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2 | 4264263 | 7359 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4081063 | 4856 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 3497644 | 4856 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3270527 | 4856 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 3193123 | 4856 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3124698 | 4856 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 2866474 | 4856 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2538840 | 4856 | BEGIN |
| 2 | 1889996 | 1633 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| -1 | 0 | 3223 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| -1 | 0 | 41201 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+-------------------------------------------------------------+
13 rows in set (0.00 sec)
注意：rule_id=20和rule_id=30这两条规则可以合并为一条。这里将它们分开是为了描述"apply"字段的重要性：多个规则可以匹配同一个查询。
关于查询重写的更多示例。
想要将下面这种查询语句进行重写：
SELECT c FROM sbtest1 WHERE id=?
想要重写的结果为：
SELECT c FROM sbtest2 WHERE id=?
但只对1000 <= id < 3999的查询进行重写。我知道，这毫无意义，只是为了展示一些本质，包括一些复杂的分片(sharding)功能!
这样的正则表达式如何写？
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (5,1,'msandbox','^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$','SELECT c FROM sbtest2 WHERE id=\3\4',1);
Query OK, 1 row affected (0.00 sec)
注意，这里选择"c"和"1"(sbtest1中的1)仅仅只是为了描述语法。
Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+---------------------------------------------+------------------------------------+-----------+-------+
| rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+---------------------------------------------+------------------------------------+-----------+-------+
| 5 | NULL | ^SELECT (c) FROM sbtest(1) WHERE id=1(...)$ | SELECT c FROM sbtest2 WHERE id=1\3 | NULL | 1 |
| 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+---------+-------------------------------------+---------------------------------------------+------------------------------------+-----------+-------+
4 rows in set (0.00 sec)
现在我们试试。
Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
vagrant@ubuntu-14:~/sysbench/sysbench-0.5/sysbench$ ./sysbench --report-interval=5 --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='msandbox' --mysql-password='msandbox' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
显然它已经正常工作了。
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;
+-------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+-------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| 2579 | 5 | NULL | ^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$ | SELECT c FROM sbtest2 WHERE id=\3\4 | NULL | 1 |
| 83091 | 10 | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL | NULL | 5000 | 1 |
| 8567 | 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 8567 | 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+-------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
4 rows in set (0.00 sec)
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+-------------------------------------------------------------+
| 1 | 9417428 | 8567 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 6282654 | 8567 | COMMIT |
| 1 | 5560850 | 8567 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 5360637 | 8567 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 4447573 | 8567 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 4040300 | 8567 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 3378990 | 8567 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 3046664 | 8567 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2974559 | 8596 | SELECT c FROM sbtest1 WHERE id=? |
| 2 | 2805758 | 2376 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 2480409 | 8567 | BEGIN |
| 1 | 1152803 | 2579 | SELECT c FROM sbtest2 WHERE id=? |
| -1 | 0 | 6191 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| -1 | 0 | 74495 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+-------------------------------------------------------------+
14 rows in set (0.01 sec)
