MySQL中間件之ProxySQL(8)：SQL語句的重寫規則

来源:https://www.cnblogs.com/f-ck-need-u/archive/2018/07/14/9309760.html
-Advertisement-
Play Games

1.為什麼要重寫SQL語句 ProxySQL在收到前端發送來的SQL語句後，可以根據已定製的規則去匹配它，匹配到了還可以去重寫這個語句，然後再路由到後端去。 什麼時候需要重寫SQL語句？ 對於下麵這種簡單的讀、寫分離，當然用不上重寫SQL語句。 這樣的讀寫分離，實現起來非常簡單。如下： 但是，複雜一 ...


1.為什麼要重寫SQL語句

ProxySQL在收到前端發送來的SQL語句後，可以根據已定製的規則去匹配它，匹配到了還可以去重寫這個語句，然後再路由到後端去。

什麼時候需要重寫SQL語句？

對於下麵這種簡單的讀、寫分離，當然用不上重寫SQL語句。

這樣的讀寫分離，實現起來非常簡單。如下：

mysql_replication_hostgroups: 
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment  |
+------------------+------------------+----------+
| 10               | 20               | cluster1 |
+------------------+------------------+----------+

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | master   | 3306 | ONLINE | 1      |
| 20           | slave1   | 3306 | ONLINE | 1      |
| 20           | slave2   | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT              |
+---------+-----------------------+----------------------+

但是，複雜一點的，例如ProxySQL實現sharding功能。對db1庫的select_1語句路由給hg=10的組，將db2庫的select_2語句路由給hg=20的組，將db3庫的select_3語句路由給hg=30的組。

在ProxySQL實現sharding時，基本上都需要將SQL語句進行重寫。這裡用一個簡單的例子來說明分庫是如何進行的。

假如，電腦學院it_db占用一個資料庫，裡面有一張學生表stu，stu表中有代表專業的欄位zhuanye(例子只是隨便舉的，請無視合理性)。

it_db庫: stu表
+---------+----------+---------+
| stu_id  | stu_name | zhuanye |
+---------+----------+---------+
| 1-99    | ...      | Linux   |
+---------+----------+---------+
| 100-150 | ...      | MySQL   |
+---------+----------+---------+
| 151-250 | ...      | JAVA    |
+---------+----------+---------+
| 251-550 | ...      | Python  |
+---------+----------+---------+

分庫時，可以為各個專業創建庫。於是，創建4個庫，每個庫中仍保留stu表，但只保留和庫名對應的學生數據：

Linux庫：stu表
+---------+----------+---------+
| stu_id  | stu_name | zhuanye |
+---------+----------+---------+
| 1-99    | ...      | Linux   |
+---------+----------+---------+

MySQL庫：stu表
+---------+----------+---------+
| stu_id  | stu_name | zhuanye |
+---------+----------+---------+
| 100-150 | ...      | MySQL   |
+---------+----------+---------+

JAVA庫：stu表
+---------+----------+---------+
| stu_id  | stu_name | zhuanye |
+---------+----------+---------+
| 151-250 | ...      | JAVA    |
+---------+----------+---------+

Python庫：stu表
+---------+----------+---------+
| stu_id  | stu_name | zhuanye |
+---------+----------+---------+
| 251-550 | ...      | Python  |
+---------+----------+---------+

於是，原來查詢MySQL專業學生的SQL語句：

select * from it_db.stu where zhuanye='MySQL' and xxx;

分庫後，該SQL語句需要重寫為：

select * from MySQL.stu where 1=1 and xxx;

至於如何達到上述目標，本文結尾給出了一個參考規則。

sharding而重寫只是一種情況，在很多使用複雜ProxySQL路由規則時可能都需要重寫SQL語句。下麵將簡單介紹ProxySQL的語句重寫，為後文做個鋪墊，在之後介紹ProxySQL + sharding的文章中有更多具體的用法。

2.SQL語句重寫

在mysql_query_rules表中有match_pattern欄位和replace_pattern欄位，前者是匹配SQL語句的正則表達式，後者是匹配成功後(命中規則)，將原SQL語句改寫，改寫後再路由給後端。

需要註意幾點：

  1. 如果不設置replace_pattern欄位，則不會重寫。
  2. 要重寫SQL語句，必須使用match_pattern的方式做正則匹配，不能使用match_digest。因為match_digest是對參數化後的語句進行匹配。
  3. ProxySQL支持兩種正則引擎：RE2和PCRE，預設使用的引擎是PCRE。這兩個引擎預設都設置了caseless修飾符(re_modifiers欄位)，表示匹配時忽略大小寫。還可以設置其它修飾符，如global修飾符，global修飾符主要用於SQL語句重寫，表示全局替換，而非首次替換。
  4. 因為SQL語句千變萬化，在寫正則語句的時候，一定要註意"貪婪匹配"和"非貪婪匹配"的問題。
  5. stats_mysql_query_digest表中的digest_text欄位顯示了替換後的語句。也就是真正路由出去的語句。

本文的替換規則出於入門的目的，很簡單，只需掌握最基本的正則知識即可。但想要靈活運用，需要掌握PCRE的正則，如果您已有正則的基礎，可參考我的一篇總結性文章：pcre和正則表達式的誤點。

例如，將下麵的語句1重寫為語句2。

select * from test1.t1;
select * from test1.t2;

插入如下規則：

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,match_pattern,replace_pattern,destination_hostgroup,apply) 
values (1,1,"^(select.*from )test1.t1(.*)","\1test1.t2\2",20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

select rule_id,destination_hostgroup,match_pattern,replace_pattern from mysql_query_rules;
+---------+-----------------------+------------------------------+-----------------+
| rule_id | destination_hostgroup | match_pattern                | replace_pattern |
+---------+-----------------------+------------------------------+-----------------+
| 1       | 20                    | ^(select.*from )test1.t1(.*) | \1test1.t2\2    |
+---------+-----------------------+------------------------------+-----------------+

然後執行：

$ proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e"
$ $proc "select * from test1.t1;"
+------------------+
| name             |
+------------------+
| test1_t2_malong1 |
| test1_t2_malong2 |
| test1_t2_malong3 |
+------------------+

可見語句成功重寫。

再看看規則的狀態。

Admin> select rule_id,hits from stats_mysql_query_rules; 
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
| 2       | 0    |
+---------+------+

Admin> select hostgroup,count_star,digest_text from stats_mysql_query_digest;
+-----------+------------+------------------------+
| hostgroup | count_star | digest_text            |
+-----------+------------+------------------------+
| 20        | 1          | select * from test1.t2 |  <--已替換
+-----------+------------+------------------------+

更簡單的，還可以直接替換單詞。例如：

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,match_pattern,replace_pattern,destination_hostgroup,apply) 
values (1,1,"test1.t1","test1.t2",20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

select rule_id,destination_hostgroup,match_pattern,replace_pattern from mysql_query_rules;
+---------+-----------------------+---------------+-----------------+
| rule_id | destination_hostgroup | match_pattern | replace_pattern |
+---------+-----------------------+---------------+-----------------+
| 1       | 20                    | test1.t1      | test1.t2        |
+---------+-----------------------+---------------+-----------------+

3.sharding：重寫分庫SQL語句

以本文前面sharding示例中的語句為例，簡單演示下sharding時的分庫語句怎麼改寫。更完整的sharding實現方法，見後面的文章。

#原來查詢MySQL專業學生的SQL語句：
select * from it_db.stu where zhuanye='MySQL' and xxx;
             |
             |
             |
            \|/
#改寫為查詢分庫MySQL的SQL語句：
select * from MySQL.stu where 1=1 and xxx;

以下是完整語句：關於這個規則中的正則部分，稍後會解釋。

delete from mysql_query_rules;
select * from stats_mysql_query_digest_reset where 1=0;

insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,replace_pattern) 
values (1,1,1,20,"^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$","\1 \3.\2 where 1=1 \4");

load mysql query rules to runtime;
save mysql query rules to disk;

select rule_id,destination_hostgroup dest_hg,match_pattern,replace_pattern from mysql_query_rules;
+---------+---------+-----------------------------------------------------------------+-----------------------+
| rule_id | dest_hg | match_pattern                                                   | replace_pattern       |
+---------+---------+-----------------------------------------------------------------+-----------------------+
| 1       | 20      | ^(select.*?from) it_db\.(.*?) where zhuanye=['"](.*?)['"] (.*)$ | \1 \3.\2 where 1=1 \4 |
+---------+---------+-----------------------------------------------------------------+-----------------------+

然後執行分庫查詢語句：

proc="mysql -uroot -pP@ssword1! -h127.0.0.1 -P6033 -e"
$proc "select * from it_db.stu where zhuanye='MySQL' and 1=1;"

看看是否命中規則，併成功改寫SQL語句：

Admin> select rule_id,hits from stats_mysql_query_rules; 
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 1    |
+---------+------+

Admin> select hostgroup,count_star,digest_text from stats_mysql_query_digest;
+-----------+------------+-------------------------------------------+
| hostgroup | count_star | digest_text                               |
+-----------+------------+-------------------------------------------+
| 20        | 1          | select * from MySQL.stu where ?=? and ?=? |
| 10        | 1          | select @@version_comment limit ?          |
+-----------+------------+-------------------------------------------+

解釋下前面的規則：

match_pattern:
- "^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$"
replace_pattern:
- "\1 \3.\2 where 1=1 \4"

^(select.*?from)：表示不貪婪匹配到from字元。之所以不貪婪匹配，是為了避免子查詢或join子句出現多個from的情況。
it_db\.(.*?)：這裡的it_db是稍後要替換掉為"MySQL"字元的部分，而it_db後面的表稍後要附加在"MySQL"字元後，所以對其分組捕獲。
zhuanye=['""](.*?)['""]：
- 這裡的zhuanye欄位稍後是要刪除的，但後面的欄位值"MySQL"需要保留作為稍後的分庫，因此對欄位值分組捕獲。同時，欄位值前後的引號可能是單引號、雙引號，所以兩種情況都要考慮到。
- ['""]：要把引號保留下來，需要對額外的引號進行轉義：雙引號轉義後成單個雙引號。所以，真正插入到表中的結果是['"]。
- 這裡的語句並不健壯，因為如果是zhuanye='MySQL"這樣單雙引號混用也能被匹配。如果要避免這種問題，需要使用PCRE的反向引用。例如，改寫為：zhuanye=(['""])(.*?)\g[N]，這裡的[N]要替換為(['""])對應的分組號碼，例如\g3。
(.*)$：匹配到結束。因為這裡的測試語句簡單，沒有join和子查詢什麼的，所以直接匹配。
"\1 \3.\2 where 1=1 \4"：這裡加了1=1，是為了防止出現and/or等運算符時前面缺少表達式。例如(.*)$捕獲到的內容為and xxx=1，不加上1=1的話，將替換為where and xxx=1，這是錯誤的語句，所以1=1是個占位表達式。

可見，要想實現一些複雜的匹配目標，正則表達式是非常繁瑣的。所以，很有必要去掌握PCRE正則表達式。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL基本簡單操作
    MySQL基本簡單操作 學會了安裝 ，那麼就將它利用起來。（/滑稽臉） 之前想學習 （Windows下配置真麻煩），學會了 就方便了，直接使用 創建一個 服務豈不美滋滋。創建容器的步驟可以看一下 "分享04" 的 的創建過程。 首先檢查一下本地鏡像。 [root@promote ~] docker ...
  • MySQL增強半同步幾個重要參數搭配的測試
    占座 ...
  • mssql sqlserver 批量刪除所有存儲過程的方法分享
    轉自:http://www.maomao365.com/?p=6864 摘要: 下文講述採用sql腳本批量刪除所有存儲過程的方法，如下所示: 實驗環境:sqlserver 2008 R2 平常使用sql腳本，刪除存儲過程，我們只可以使用刪除命令一條一條的刪除存儲過程，下文介紹一種簡便方法，可以對系統 ...
  • 資料庫sql優化總結之2-百萬級資料庫優化方案+案例分析
    有三張百萬級數據表 知識點表（ex_subject_point）9,316條數據 試題表（ex_question_junior）2,159,519條數據 有45個欄位 知識點試題關係表（ex_question_r_knowledge）3,156,155條數據 測試資料庫為：mysql （5.... ...
  • maven添加oracle依賴失敗
    由於Oracle授權問題，Maven3不提供oracle JDBC driver 可以到maven中央倉庫去下載依賴,網址:http://repo.spring.io/plugins-release/com/oracle/ojdbc6/11.2.0.3/ 複製到本地倉庫對應目錄即可 ...
  • MySQL5.7下麵，誤操作導致的drop table db1.tb1; 的恢復方法：
    MySQL5.7下麵，誤操作導致的drop table db1.tb1; 的恢復方法： 0、停業務數據寫入。【iptables封禁】 1、從備份伺服器上拉取最新的一個全備文件，恢復到一個臨時的伺服器上，解壓並啟動mysqld。 2、在這台新的slave上執行如下命令： 2.1 先配置好複製關係， c ...
  • 資料庫崩潰恢復表結構的方法
    資料庫崩潰恢復表結構的方法 如果資料庫發生崩潰，無法登陸資料庫，想要快速恢復表結構的話有一個很方便的方法。 通過mysqlfrm工具就可以快速解析.frm文件，找到create table 語句。 安裝mysqlfrm 安裝mysqlfrm的話，需要兩個安裝包。mysql-utilities和mys ...
  • 大數據技術原理與應用——分散式文件系統HDFS
    分散式文件系統概述 相對於傳統的本地文件系統而言，分散式文件系統（Distribute File System）是一種通過網路實現文件在多台主機上進行分散式存儲的文件系統。分散式文件系統的設計一般採用“客戶/服務機”模式，客戶端以特定的通信協議通過網路與伺服器建立連接，提出文件訪問請求，客戶端和服務 ...
一周排行
    -Advertisement-
    Play Games
  • C#自增運算符詳解：++i與i++的區別及應用場景
    概述：在C#中，++i和i++都是自增運算符，其中++i先增加值再返回，而i++先返回值再增加。應用場景根據需求選擇，首碼適合先增後用，尾碼適合先用後增。詳細示例提供清晰的代碼演示這兩者的操作時機和實際應用。 在C#中，++i 和 i++ 都是自增運算符，但它們在操作上有細微的差異，主要體現在操作的 ...
  • Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET 版本
    上次發佈了：Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET Core 版本，今天計劃準備壓測一下 .NET 版本，來測試並記錄一下 Taurus.MVC 框架在 .NET 版本的性能，以便後續持續優化改進。 為了方便對比，本文章的電腦環境和測試思路，儘量和... ...
  • 一文讀懂.NET WebAPI中FromRoute、FromQuery、FromBody的關鍵角色與用法
    .NET WebAPI作為一種構建RESTful服務的強大工具，為開發者提供了便捷的方式來定義、處理HTTP請求並返迴響應。在設計API介面時，正確地接收和解析客戶端發送的數據至關重要。.NET WebAPI提供了一系列特性，如[FromRoute]、[FromQuery]和[FromBody]，用 ...
  • WPF/C#實現圖像濾鏡優化方案：打造炫目視覺體驗！
    原因：我之所以想做這個項目，是因為在之前查找關於C#/WPF相關資料時，我發現講解圖像濾鏡的資源非常稀缺。此外，我註意到許多現有的開源庫主要基於CPU進行圖像渲染。這種方式在處理大量圖像時，會導致CPU的渲染負擔過重。因此，我將在下文中介紹如何通過GPU渲染來有效實現圖像的各種濾鏡效果。 生成的效果 ...
  • Bogus 實戰：使用 Bogus 和 EFCore 生成模擬數據和種子數據【完整教程】
    引言 上一章我們介紹了在xUnit單元測試中用xUnit.DependencyInject來使用依賴註入，上一章我們的Sample.Repository倉儲層有一個批量註入的介面沒有做單元測試，今天用這個示例來演示一下如何用Bogus創建模擬數據 ，和 EFCore 的種子數據生成 Bogus 的優 ...
  • WPF隨筆收錄-實時繪製心率曲線
    一、前言 在自己的項目中，涉及到實時心率曲線的繪製，項目上的曲線繪製，一般很難找到能直接用的第三方庫，而且有些還是定製化的功能，所以還是自己繪製比較方便。很多人一聽到自己畫就害怕，感覺很難，今天就分享一個完整的實時心率數據繪製心率曲線圖的例子；之前的博客也分享給DrawingVisual繪製曲線的方 ...
  • wpf程式修改啟動入口註意事項
    如果你在自定義的 Main 方法中直接使用 App 類並啟動應用程式，但發現 App.xaml 中定義的資源沒有被正確載入，那麼問題可能在於如何正確配置 App.xaml 與你的 App 類的交互。 確保 App.xaml 文件中的 x:Class 屬性正確指向你的 App 類。這樣，當你創建 Ap ...
  • 記一次 .NET某爐膛鍋爐檢測系統 崩潰分析
    一：背景 1. 講故事 上個月有個朋友在微信上找到我，說他們的軟體在客戶那邊隔幾天就要崩潰一次，一直都沒有找到原因，讓我幫忙看下怎麼回事，確實工控類的軟體環境複雜難搞，朋友手上有一個崩潰的dump，剛好丟給我來分析一下。 二：WinDbg分析 1. 程式為什麼會崩潰 windbg 有一個厲害之處在於 ...
  • 一個.NET內置依賴註入的小型強化版
    前言 .NET生態中有許多依賴註入容器。在大多數情況下，微軟提供的內置容器在易用性和性能方面都非常優秀。外加ASP.NET Core預設使用內置容器，使用很方便。 但是筆者在使用中一直有一個頭疼的問題：服務工廠無法提供請求的服務類型相關的信息。這在一般情況下並沒有影響，但是內置容器支持註冊開放泛型服 ...
  • WPF隨筆收錄-DataGrid固定右側列
    一、前言 在項目開發過程中，DataGrid是經常使用到的一個數據展示控制項，而通常表格的最後一列是作為操作列存在，比如會有編輯、刪除等功能按鈕。但WPF的原始DataGrid中，預設只支持固定左側列，這跟大家習慣性操作列放最後不符，今天就來介紹一種簡單的方式實現固定右側列。（這裡的實現方式參考的大佬 ...
所有分類