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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類