在這篇文章中,我將重新探究ProxySQL中的Query Rewrite
功能,因為query rewriting是創(chuàng)建ProxySQL的最根本初衷。
創(chuàng)新互聯(lián)專注于網(wǎng)站建設(shè),為客戶提供成都網(wǎng)站建設(shè)、網(wǎng)站制作、網(wǎng)頁設(shè)計開發(fā)服務(wù),多年建網(wǎng)站服務(wù)經(jīng)驗,各類網(wǎng)站都可以開發(fā),成都品牌網(wǎng)站建設(shè),公司官網(wǎng),公司展示網(wǎng)站,網(wǎng)站設(shè)計,建網(wǎng)站費用,建網(wǎng)站多少錢,價格優(yōu)惠,收費合理。
為什么我們需要重寫查詢?
這兒舉例你作為DBA發(fā)現(xiàn)了一個“壞查詢”,你確認是它導(dǎo)致了服務(wù)緩慢,并且可能會導(dǎo)致服務(wù)不可用。那這個查詢必須被優(yōu)化,你和開發(fā)溝通要修正這個SQL,但是開發(fā)反饋回來的信息是能改,但是由于技術(shù)的非技術(shù)的種種原因吧,沒有那么快。這時你怎么辦,等著?顯然不能,你可以在開發(fā)完成修正之前通過ProxySQL的Query Rewrite
功能重寫某些查詢來完成優(yōu)化同時對應(yīng)用保持透明。
如何重寫查詢?通過ProxySQL有兩種方式來完成(譯者注:其實應(yīng)該理解為兩種匹配查詢的方式)。
Query rewrite其實就是通過 MySQL_query_rules
表中一個 match_pattern + replace_pattern
的過程,而match_digest
(注意區(qū)分 match_pattern 和 match_digest )僅用來匹配一個查詢,而非重寫它。邏輯上講,match_digest
和 username
,schemaname
,proxy_addr
等字段的作用是一樣的,僅用來匹配查詢。
這兩種不同的機制為不同的查詢類型(例如DML操作,SELECT等)提供了靈活高效匹配方式。注意如果你希望重寫查詢,那么規(guī)則中的match_pattern
必須能匹配到原始的查詢。查詢規(guī)則按照rule_id字段的升序順序處理,并且只有在active字段為1的前提下才會處理。
下面是我們?nèi)绾卧谖覀兊臏y試環(huán)境演示 match_digest
mysql> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+----+-----------+------------+-----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+-----------+------------+-----------------------------------+
| 0 | 243549572 | 85710 | SELECT c FROM sbtest10 WHERE id=? |
| 0 | 146324255 | 42856 | COMMIT |
| 0 | 126643488 | 44310 | SELECT c FROM sbtest7 WHERE id=? |
| 0 | 126517140 | 42927 | BEGIN |
| 0 | 123797307 | 43820 | SELECT c FROM sbtest1 WHERE id=? |
| 0 | 123345775 | 43460 | SELECT c FROM sbtest6 WHERE id=? |
| 0 | 122121030 | 43010 | SELECT c FROM sbtest9 WHERE id=? |
| 0 | 121245265 | 42400 | SELECT c FROM sbtest8 WHERE id=? |
| 0 | 120554811 | 42520 | SELECT c FROM sbtest3 WHERE id=? |
| 0 | 119244143 | 42070 | SELECT c FROM sbtest5 WHERE id=? |
+----+-----------+------------+-----------------------------------+
10 rows in set (0.00 sec)
mysql> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest, match_pattern,replace_pattern,apply) VALUES (10,1,'root','SELECT.*WHERE id=?','sbtest2','sbtest10',1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 593 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)
如果想清空 query rules 的統(tǒng)計信息,使用下列方法
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
接下來是 match_pattern 示例:
mysql> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 5;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 0 | 98753983 | 16292 | BEGIN |
| 0 | 84613532 | 16232 | COMMIT |
| 1 | 49327292 | 16556 | SELECT c FROM sbtest3 WHERE id=? |
| 1 | 49027118 | 16706 | SELECT c FROM sbtest2 WHERE id=? |
| 1 | 48095847 | 16396 | SELECT c FROM sbtest4 WHERE id=? |
+----+----------+------------+----------------------------------+
5 rows in set (0.01 sec)
mysql> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (20,1,'root','DISTINCT(.*)ORDER BY c','DISTINCT1',1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 |
| 0 | 20 | NULL | 1 | root | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | NULL | 1 |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.01 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 9994 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 |
| 6487 | 20 | NULL | 1 | root | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | NULL | 1 |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
路由規(guī)則中一個關(guān)鍵點是 mysql_query_rules 的 apply 字段
(譯者注:類似于nginx rewrite 指令中的 break 參數(shù))
如下面測試中所展示的,所有匹配rule_id = 10 或 rule_id = 20 的查詢都準確的匹配上了。實際上,現(xiàn)在所有的規(guī)則在 runtime_mysql_query_rules 表中都是激活的。如果我們想禁用 mysql_query_rules 表中某條規(guī)則,設(shè)置 active = 0
mysql> update mysql_query_rules set apply = 1 where rule_id in (10);
Query OK, 1 row affected (0.00 sec)
mysql> update mysql_query_rules set apply = 0 where rule_id in (20);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
| 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 1 |
| 0 | 20 | NULL | 1 | root | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | NULL | 0 |
+------+---------+--------+--------+----------+--------------------+------------------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | flagIN | apply |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| 10195 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | 0 | 1 |
| 6599 | 20 | NULL | 1 | root | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | 0 | 0 |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
2 rows in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, match_digest, match_pattern, replace_pattern, flagIN, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| hits | rule_id | digest | active | username | match_digest | match_pattern | replace_pattern | flagIN | apply |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
| 20217 | 5 | NULL | 1 | root | NULL | DISTINCT(.*)ORDER BY c | DISTINCT1 | 0 | 1 |
| 27020 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | 0 | 0 |
+-------+---------+--------+--------+----------+--------------------+------------------------+-----------------+--------+-------+
2 rows in set (0.00 sec)
mysql> update mysql_query_rules set active = 0 where rule_id = 5;
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 0 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 0 |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.00 sec)
mysql> SELECT hits, mysql_query_rules.rule_id,digest,active,username, 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 | digest | active | username | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
| 4224 | 10 | NULL | 1 | root | SELECT.*WHERE id=? | sbtest2 | sbtest10 | NULL | 0 |
+------+---------+--------+--------+----------+--------------------+---------------+-----------------+-----------+-------+
1 row in set (0.01 sec)
另外,ProxySQL還能幫忙識別出“低效的查詢”,登錄管理界面按如下操作
找出總耗時最多的查詢
mysql> SELECT SUM(sum_time), SUM(count_star), digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM(sum_time) DESC LIMIT 3G
*************************** 1. row ***************************
SUM(sum_time): 95053795
SUM(count_star): 13164
digest_text: BEGIN
*************************** 2. row ***************************
SUM(sum_time): 85094367
SUM(count_star): 13130
digest_text: COMMIT
*************************** 3. row ***************************
SUM(sum_time): 52110099
SUM(count_star): 13806
digest_text: SELECT c FROM sbtest3 WHERE id=?
3 rows in set (0.00 sec)
找出平均耗時最高的查詢
mysql> SELECT SUM(sum_time), SUM(count_star), SUM(sum_time)/SUM(count_star) avg, digest_text FROM stats_mysql_query_digest GROUP BY digest ORDER BY SUM(sum_time)/SUM(count_star) DESC limit 1;
+---------------+-----------------+--------+--------------------------------+
| SUM(sum_time) | SUM(count_star) | avg | digest_text |
+---------------+-----------------+--------+--------------------------------+
| 972162 | 1 | 972162 | CREATE INDEX k_5 ON sbtest5(k) |
+---------------+-----------------+--------+--------------------------------+
1 row in set (0.00 sec)
我發(fā)現(xiàn)關(guān)于ProxySQL query rewrite 的“最好”的文檔在IBM,這里介紹了查詢重寫的原理和示例,值得一讀。
還有一些別的場景你可能需要重寫查詢,試想有一張表的自增ID列已經(jīng)達到了int類型的最大值,你可以將新插入的數(shù)據(jù)重定向到另一張表同時你通過alter命令來修正原表的問題,在這期間所有的查詢還將訪問原表,等alter原表完成后,將新表的數(shù)據(jù)導(dǎo)入的原表,即可達到不停機修D(zhuǎn)DL的效果。
從MySQL 5.7.6 起,MySQL以插件形式提供了 query rewrite 功能,你可以在這里找到相關(guān)文檔。MySQL內(nèi)建的查詢重寫功能的一個最大的劣勢在于重寫規(guī)則僅作用于當(dāng)前MySQL實例,這也是相比之下ProxySQL 的優(yōu)勢所在:它處在應(yīng)用和數(shù)據(jù)庫之間,所以它的重寫規(guī)則是全局的。
原文鏈接
文章名稱:ProxySQLQueryRewrite使用示例
文章來源:http://muchs.cn/article24/piphce.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供關(guān)鍵詞優(yōu)化、品牌網(wǎng)站建設(shè)、面包屑導(dǎo)航、網(wǎng)站維護、靜態(tài)網(wǎng)站、軟件開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)