MySQL Query Rules Here is the statement used to create the mysql_query_rules 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, client_addr VARCHAR, proxy_addr VARCHAR, proxy_port INT, digest VARCHAR, match_digest VARCHAR, match_pattern VARCHAR, negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0, re_modifiers VARCHAR DEFAULT 'CASELESS', 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, retries INT CHECK (retries>=0 AND retries <=1000), delay INT UNSIGNED, mirror_flagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, sticky_conn INT CHECK (sticky_conn IN (0,1)), multiplex INT CHECK (multiplex IN (0,1)), log INT CHECK (log IN (0,1)), apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0, comment VARCHAR) The fields have the following semantics 語(yǔ)義: rule_id - the unique id of the rule. Rules are processed in rule_id order 唯一的規(guī)則id編號(hào)，規(guī)則按照rule_id的順序執(zhí)行的 active - only rules with active=1 will be considered by the query processing module active=1 的時(shí)候，查詢模塊才會(huì)考慮這一條規(guī)則 username - filtering criteria 準(zhǔn)確的 matching username. If is non-NULL, a query will match only if the connection is made with the correct username username 用來(lái)匹配精確的用戶名，如果它是一個(gè)非空的值，查詢將會(huì)只匹配從這個(gè)username發(fā)起的查詢 schemaname - filtering criteria matching schemaname. If is non-NULL, a query will match only if the connection uses schemaname as default schema 用來(lái)指定連接進(jìn)來(lái)匹配的shcemaname flagIN, flagOUT, apply - these allow us to create "chains of rules" that get applied one after the other. An input flag value is set to 0, and only rules with flagIN=0 are considered at the beginning. When a matching rule is found for a specific query, flagOUT is evaluated and if NOT NULL the query will be flagged with the specified flag in flagOUT. If flagOUT differs from flagIN , the query will exit the current chain and enters a new chain of rules having flagIN as the new input flag. If flagOUT matches flagIN, the query will be re-evaluate again against the first rule with said flagIN. This happens until there are no more matching rules, or apply is set to 1 (which means this is the last rule to be applied) 這三個(gè)是重要的配置。它們是用來(lái)創(chuàng)建各個(gè)規(guī)則之間的規(guī)則鏈的，也就是用來(lái)進(jìn)行連接多個(gè)規(guī)則的。如果一條規(guī)則中定義的輸入標(biāo)識(shí)符為0，那么只有包含輸入標(biāo)識(shí)符為0 的規(guī)則才會(huì)在一開始就應(yīng)用這條規(guī)則。當(dāng)被匹配到的規(guī)則被發(fā)現(xiàn)是一個(gè)特定的查詢，在輸出標(biāo)識(shí)符是非空的情況下，這個(gè)查詢會(huì)被打上對(duì)應(yīng)的輸出標(biāo)識(shí)符。如果輸出標(biāo)識(shí)符和輸入標(biāo)識(shí)符不一致，查詢將離開當(dāng)前的規(guī)則鏈并且進(jìn)入到下一個(gè)規(guī)則中（這個(gè)規(guī)則的輸入標(biāo)識(shí)符和前面的輸出標(biāo)識(shí)符一致），它將會(huì)成為下一條規(guī)則的輸入標(biāo)識(shí)符。如果輸出標(biāo)識(shí)符和輸入標(biāo)識(shí)符一致，查詢將會(huì)回到前面標(biāo)記有同樣標(biāo)識(shí)符的規(guī)則中。這種情形會(huì)循環(huán)發(fā)生，直到?jīng)]有更多的匹配規(guī)則，如果apply被設(shè)置為1，就意味著這是規(guī)則鏈中的最后一條規(guī)則，規(guī)則將在這里被終止。 client_addr - match traffic from a specific source 匹配發(fā)起查詢的應(yīng)用的IP proxy_addr - match incoming traffic on a specific local IP 匹配查詢指定的本地的proxysql的IP proxy_port - match incoming traffic on a specific local port 指定本地proxysql的查詢端口 digest - match queries with a specific digest, as returned bystats_mysql_query_digest.digest 匹配查詢指定的摘要，可以在stats_mysql_query_digest.diges列中查到，不指定的化會(huì)被自動(dòng)分配一個(gè) match_digest - regular expression that matches the query digest. See also mysql-query_processor_regex 匹配查詢摘要的正則表達(dá)式 類型有兩種 PCRE 和 RE2 默認(rèn)是PCRE match_pattern - regular expression that matches the query text. See also mysql-query_processor_regex 匹配文本內(nèi)容的正則表達(dá)式 negate_match_pattern - if this is set to 1, only queries not matching the query text will be considered as a match. This acts as a NOT operator in front of the regular expression matching against match_pattern or match_digest 如果這個(gè)參數(shù)被設(shè)置為1，只有沒(méi)有被匹配到的查詢規(guī)則的查詢會(huì)被執(zhí)行。它是一個(gè)正則相反的操作，和match_pattern or match_digest兩個(gè)參數(shù)相對(duì)應(yīng)。 re_modifiers - comma separated list of options to modify the behavior of the RE engine. WithCASELESS the match is case insensitive. With GLOBAL the replace is global (replaces all matches and not just the first). For backward compatibility, only CASELESS is the enabled by default. See also mysql-query_processor_regex for more details.逗號(hào)隔開的列表選項(xiàng)來(lái)用來(lái)修改正則引擎的操作。caseless 選項(xiàng)表明不進(jìn)行遞歸，只能夠匹配到查詢中的第一個(gè)匹配到的字符串。global選項(xiàng)下，也就是全局選項(xiàng)下，能夠替換到查詢語(yǔ)句中所有的陪匹配到的文本。為了向后兼容，默認(rèn)設(shè)置的是 caseless. replace_pattern - this is the pattern with which to replace the matched pattern. It's done using RE2::Replace, so it's worth taking a look at the online documentation for that:https://github.com/google/re2/blob/master/re2/re2.h#L378. Note that this is optional, and when this is missing, the query processor will only cache, route, or set other parameters without rewriting. 被替換成為的字符。根據(jù)規(guī)則，將被匹配到的內(nèi)容替換成為這個(gè)參數(shù)下面的內(nèi)容。這是一個(gè)可選項(xiàng)目，如果沒(méi)有值，查詢過(guò)程將被緩存、路由或者設(shè)置為其他沒(méi)有重寫的參數(shù)，就是不進(jìn)行任何的替換。 destination_hostgroup - route matched queries to this hostgroup. This happens unless there is a started transaction and the logged in user has the transaction_persistent flag set to 1 (seemysql_users table). 指定目的組的編號(hào)，只有在起了事務(wù)和登錄的用戶ransaction_persistent flag被設(shè)置為1 的時(shí)候才生效。ransaction_persistent flag 默認(rèn)就是被設(shè)置為1 cache_ttl - the number of milliseconds for which to cache the result of the query. Note: in ProxySQL 1.1 cache_ttl was in seconds 緩沖查詢結(jié)果的毫秒數(shù) proxysql是按秒為單位的 reconnect - feature not used 現(xiàn)在還沒(méi)有使用 timeout - the maximum timeout in milliseconds with which the matched or rewritten query should be executed. If a query run for longer than the specific threshold, the query is automatically killed. If timeout is not specified, global variable mysql-default_query_timeoutapplies 匹配或者重定向能夠占用的最大的超時(shí)時(shí)間。如果一個(gè)查詢執(zhí)行了超過(guò)設(shè)置閾值的大小，它會(huì)被自動(dòng)kill。如果時(shí)間沒(méi)有被設(shè)置，會(huì)默認(rèn)使用mysql-default_query_timeout；默認(rèn)10個(gè)小時(shí) mysql-default_query_timeout | 36000000 retries - the maximum number of times a query needs to be re-executed in case of detected failure during the execution of the query. If retries is not specified, global variable mysql-query_retries_on_failure applies 當(dāng)一個(gè)查詢失敗后，它重新去執(zhí)行的最大次數(shù)。如果沒(méi)有設(shè)置，默認(rèn)是 1 | mysql-query_retries_on_failure | 1 | delay - number of milliseconds to delay the execution of the query. This is essentially 基本上 a throttling 壓制 mechanism and QoS, allowing to give priority to some queries instead of others. This value is added to the mysql-default_query_delay global variable that applies to all queries. Future version of ProxySQL will provide a more advanced throttling mechanism.延遲執(zhí)行查詢的毫秒數(shù)。這是一個(gè)限制機(jī)制，用來(lái)提高某些查詢的優(yōu)先級(jí)別。默認(rèn)是0，以后的版本會(huì)更高級(jí)的延遲機(jī)制。 mirror_flagOUT and mirror_hostgroup - setting related to mirroring . 沒(méi)查到這個(gè)，沒(méi)文檔 error_msg - query will be blocked, and the specified error_msg will be returned to the client 查詢被阻斷后，返回給客戶端的錯(cuò)誤信息 sticky_conn - not implemented yet 未使用 multiplex - If 0, multiplex will be disabled. If 1, multiplex could be re-enabled if there are is not any other conditions preventing this (like user variables or transactions). Default is NULL, thus not modifying multiplexing policies 如果是0 會(huì)禁止多路復(fù)用，如果是1，會(huì)使用多路復(fù)用（當(dāng)其他的條件和多路復(fù)用沒(méi)有沖突的時(shí)候，比如用戶變量或者事務(wù)）。
