Shulou(Shulou.com)06/01 Report--

Starting with MySQL 5.7.6, the MySQL server supports query rewriting plug-ins that can check and possibly modify received statements before the server executes them. MySQL contains a query rewriting plug-in called Rewriter and a script to install the plug-in and its related components. These components work together to provide the ability to rewrite select:

. The server-side plug-in is called Rewriter to check select statements and may override them based on rewriting rules cached in memory. Select statements in standard select statements and prepared statements may be overridden. Select statements that appear in the view definition or in stored procedures are not overridden.

The .Rewriter plug-in uses an query_rewrite database that contains rewrite_rules tables. The table provides permanent storage of rules, which the plug-in uses to finalize whether to rewrite statements. Allows the user to communicate with the plug-in through the rule set stored in the table. Let the user communicate with the plug-in by setting the message column recorded in the table.

The .query _ rewrite database contains a stored procedure called flush_rewrite_rules () to load the contents of the rule table into the plug-in.

. The user-defined function load_rewrite_rules () is called by the flush_rewrite_rules () stored procedure.

The .Rewriter plug-in shows that system variables allow plug-in configuration and state variables to provide runtime operation information.

The following describes how to install and use the Rewriter plug-in and provide information about its related components.

Install or uninstall the Rewriter query rewriting plug-in

To install or uninstall the Rewriter query rewriting plug-in, select the appropriate script in the share directory under the MySQL installation directory:

.install _ rewriter.sql: use this script to install the Rewriter plug-in and its related components.

.uninstall _ rewriter.sql: use this script to uninstall the Rewriter plug-in and its related components.

Run the installation script

[mysql@localhost share] $mysql-uroot-p

Show databases;+-+ | Database | +-+ | information_schema | | binlog | | mysql | | performance_schema | | query_rewrite | | sys | +-+ 6 rows in set (0.00 sec) mysql > show global variables like 'rewriter_enabled' +-+-+ | Variable_name | Value | +-+-+ | rewriter_enabled | ON | +-+-+ 1 row in set (0.00 sec)

Use the Rewriter query rewriting plug-in

To enable or disable the Rewriter query rewriting plug-in, you can do this by enabling or disabling the rewriter_enabled system variable. The default is enabled when you install the Rewriter query rewriting plug-in. To explicitly set the state of initializing the Rewriter query rewriting plug-in, you can set the rewriter_enabled variable when the server starts. For example, to enable the Rewriter plug-in in the options file, you can make the following settings:

[mysqld]

Rewriter_enabled=ON

You can also enable or disable the Rewriter plug-in at run time:

Mysql > set global rewriter_enabled=OFF;Query OK, 0 rows affected (0.00 sec) mysql > show global variables like 'rewriter_enabled' +-+-+ | Variable_name | Value | +-+-+ | rewriter_enabled | OFF | +-+-+ 1 row in set (0.00 sec) mysql > set global rewriter_enabled=ON Query OK, 0 rows affected (0.00 sec) mysql > show global variables like 'rewriter_enabled' +-+-+ | Variable_name | Value | +-+-+ | rewriter_enabled | ON | +-+-+ 1 row in set (0.01sec) mysql > desc query_rewrite.rewrite_rules +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | NULL | auto_increment | | pattern | varchar (10000) | NO | | NULL | | pattern_database | varchar (20) | YES | | NULL | | replacement | varchar (10000) | NO | NULL | enabled | enum ('YES') | 'NO') | NO | | YES | | message | varchar (100) | YES | | NULL | | pattern_digest | varchar (32) | YES | | NULL | | normalized_pattern | varchar (1000) | YES | | NULL | | +-+-- -+ 8 rows in set (0.00 sec) mysql > select * from query_rewrite.rewrite_rules Empty set (0.00 sec)

Assuming that the Rewriter plug-in is enabled, it examines and possibly modifies every select statement received by the server. The plug-in determines whether to rewrite statements based on in-memory cached rewrite rules (loaded from the rewrite_ rules table in the query_rewriter database).

Add rewrite rule

To add rules to the Rewriter plug-in, add records to the rewrite_rules table, and then call the flush_rewrite_rules () stored procedure to load the rules from the table into the plug-in. The following example creates a simple rule to match a query statement with a single text value.

Mysql > insert into query_rewrite.rewrite_rules (pattern,replacement) values ('select?', 'select? + 1'); Query OK, 1 row affected (0.03 sec) mysql > commit;Query OK, 0 rows affected (0.00 sec) mysql > select * from query_rewrite.rewrite_rules +-+-+ | id | pattern | pattern_database | replacement | enabled | Message | pattern_digest | normalized_pattern | +-+-+ | 1 | select | NULL | select? + 1 | YES | NULL | NULL | NULL | + -- + 1 row in set (0.00 sec) mysql > select * from query_rewrite.rewrite_rules\ G * * 1. Row * * id: 1pattern: select? pattern_database: NULLreplacement: select? + 1enabled: YESmessage: NULLpattern_digest: NULLnormalized_pattern: NULL1 row in set (0.00 sec)

The rule specifies a pattern template that indicates which query statements will be matched, and the replacement template indicates how to rewrite matching statements. However, adding a rule to the rewrite_ rules table is not enough to cause the Rewriter plug-in to use this rule. We must call the flush_rewrite_rules () procedure to load the contents of the rule table into the plug-in memory cache:

Mysql > call query_rewrite.flush_rewrite_rules (); Query OK, 0 rows affected, 1 warning (0.04 sec)

As the plug-in reads each rule from the rule table, it calculates a schema in a standardized (statement summary) format and a summary hash value and uses them to update the normalized_pattern and pattern_digest columns:

Mysql > select * from query_rewrite.rewrite_rules\ Gbomber * 1. Row * * id: 1pattern: select? pattern_database: NULLreplacement: select? + 1enabled: YESmessage: NULLpattern_digest: 3d4fc22e33e10d7235eced3c75a84c2cnormalized_pattern: select? 1 row in set (0.00 sec)

The pattern uses the same syntax as the prepared statement. Use pattern templates,? Characters are actually used as parameter markers to match data values. Parameter markers can only be used where the data value should appear, not for SQL keywords, identifiers, etc. Characters should not be enclosed in quotation marks.

Like patterns, substitutions can include? Characters. For statements that match a pattern template, the rewrite plug-in will rewrite it and replace it with the data values matched by the relevant tags in the pattern? Character marks. The result of the replacement is a complete statement character. The rewrite plug-in requires the server to parse it and execute the rewritten statement to return the result.

After adding and loading rewrite rules, check to see if statements that match the rule pattern will be rewritten:

Mysql > select pi (); +-+ | pi () | +-+ | 3.141593 | +-+ 1 row in set (0.02 sec) mysql > select 10 sec + | 10% row in set | +-+ 11 | +-+ 1 row in set, 1 warning (0.00 sec)

Judging from the above execution results, the first query statement was not rewritten, but the second query was rewritten. Because the second query statement, the Rewriter plug-in rewrites the statement, generates a warning message. To view this warning message, you can use show warnings:

Mysql > show warnings\ Gaught Musashi + | Level | Code | Message | +- -+ | Note | 1105 | Query 'select 10' rewritten to 'select 10' by a query rewrite plugin | +- -+ 1 row in set (0.00 sec)

To enable or disable existing rules, you can modify the enabled column and reload the rule table to the rewrite plug-in.

To disable rule 1

Mysql > update query_rewrite.rewrite_rules set enabled='NO' where id=1;Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > call query_rewrite.flush_rewrite_rules (); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select * from query_rewrite.rewrite_rules\ G * * 1. Row * * id: 1pattern: select? pattern_database: NULLreplacement: select? + 1enabled: NOmessage: NULLpattern_digest: 3d4fc22e33e10d7235eced3c75a84c2cnormalized_pattern: select? 1 row in set (0.00 sec) mysql > select 10 | 10 | +-+ | 10 | +-+ 1 row in set (0.00 sec)

This allows you to disable rewriting rules by removing them from the table.

To re-enable rewrite rule 1:

Mysql > update query_rewrite.rewrite_rules set enabled='YES' where id=1;Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > call query_rewrite.flush_rewrite_rules (); Query OK, 0 rows affected (0.02 sec) mysql > select * from query_rewrite.rewrite_rules\ G * * 1. Row * * id: 1pattern: select? pattern_database: NULLreplacement: select? + 1enabled: YESmessage: NULLpattern_digest: 3d4fc22e33e10d7235eced3c75a84c2cnormalized_pattern: select? 1 row in set (0.00 sec) mysql > select 10 +-+ | 10cm 1 | +-+ | 11 | +-+ 1 row in set, 1 warning (0.00 sec)

The rewrite_rules table contains a pattern_database column that is used by Rewriter to match table names that are not qualified with database names:

. If the relevant database and table names are the same, the statement qualifies the table name to match the qualified name in the pattern.

. Only unqualified table names in the default database that are the same as pattern_database and the table names in the same statement match unqualified names in the pattern

Suppose table mysql.cs has a column named id and the application selects one of the following forms of query to query records from the table, where the second query can only be executed if the default database is mysql:

Select * from mysql.cs where id=id_value

Select * from cs where id=id_value

Now suppose the id column is renamed to user_id. This modification means that the application must reference user_id instead of id. But if the old applications cannot be modified, they will not work. The Rewriter rewriting plug-in can solve this problem. To match and rewrite queries with or without limited names, add the following two rules and reload the rule table:

Mysql > select * from cs where mysql.id=1;ERROR 1054 (42S22): Unknown column 'mysql.id' in' where clause'mysql > insert into query_rewrite.rewrite_rules (pattern,replacement)-> values ('select * from mysql.cs where id=?', 'select * from mysql.cs where user_id=?') Query OK, 1 row affected (0.05sec) mysql > insert into query_rewrite.rewrite_rules (pattern,replacement,pattern_database)-> values ('select * from cs where id=?','select * from cs where user_id=?','mysql'); Query OK, 1 row affected (0.05sec) mysql > call query_rewrite.flush_rewrite_rules (); Query OK, 0 rows affected, 1 warning (0.02sec) mysql > select * from query_rewrite.rewrite_rules\ G * * 1. Row * * id: 1pattern: select? pattern_database: NULLreplacement: select? + 1enabled: YESmessage: NULLpattern_digest: 3d4fc22e33e10d7235eced3c75a84c2cnormalized_pattern: select? * 2. Row * * * id: 2pattern: select * from mysql.cs where id=? pattern_database: NULLreplacement: select * from mysql.cs where user_id=? enabled: YESmessage: NULLpattern_digest: 45281da14b71c1357dd053a4fe49dfacnormalized_pattern: select `* `from `mysql`.`cs` where (`id` =?) * * 3. Row * * id: 3pattern: select * from cs where id=?pattern_database: mysqlreplacement: select * from cs where user_id=?enabled: YESmessage: NULLpattern_digest: 0da2491bc4c0e1462cc020e4fcfde16bnormalized_pattern: select `* `from `mysql`.`cs` where (`id` =?) 3 rows in set (0.00 sec) mysql > select * from mysql.cs where id=1 +-+ | id | name | user_id | +-+ | 1 | jy | 1 | +-+ 1 row in set, 1 warning (0.00 sec) mysql > show warnings +- -+ | Level | Code | Message | + -+ | Note | 1105 | Query 'select * from mysql.cs where id=1' rewritten to' select * from mysql.cs where user_id= 1' by a query rewrite plugin | +-- -+ 1 row in set (0.00 sec) mysql > select * from cs where id=1 +-+ | id | name | user_id | +-+ | 1 | jy | 1 | +-+ 1 row in set, 1 warning (0.00 sec) mysql > show warnings +- -+ | Level | Code | Message | +- -+ | Note | 1105 | Query 'select * from cs where id=1' rewritten to' select * from cs where user_id=1' by a query rewrite plugin | +-- -+ 1 row in set (0.00 sec)

The Rewriter plug-in uses the first rule to match queries with limited names, and the second rule to match without qualified names, but the default database must be mysql for query rewriting.

How to do pattern matching

The Rewriter plug-in uses statement summary and summary hash values to match input statements using rewrite rules. The max_digest_length system variable determines the size of the buffer used to calculate the statement summary. The higher the value, the more the computed summary can distinguish between longer statements. The smaller the value, the smaller the memory used, but increases the likelihood that longer statements will use the same summary.

Mysql > show global variables like 'max_digest_length';+-+-+ | Variable_name | Value | +-+-+ | max_digest_length | 1024 | +-+-+ 1 row in set (1024 sec)

The rules for rewriting plug-in matching statements are as follows:

1. Calculates the hash value of the statement summary and compares it with the rule summary hash value. This may cause false positives, but it can be used as a quick rejection test.

two。 If the statement summary hash value matches any one of the pattern summary hash values, the matching normalized hash value (statement summary) converts the form of the statement into the form normalized by the matching rule pattern.

3. If the normalized statement matches the rule, compare the text values in the statement and the schema. One of the patterns? The number value matches any literal value in the statement. If the statement prepares a SELECT statement, the? It also matches the? in the statement. Otherwise, the corresponding text must be the same.

If multiple rules match a statement, it is not clear which rule the plug-in uses to rewrite the statement.

If a schema contains more tags than replacements, the plug-in will discard excess data values. If a pattern contains fewer tags than it replaces, this is a mistake. The plug-in notices this when loading the rule table, writes an error message to the message column of the rule row to pass the problem, and sets the Rewriter_reload_error status variable to ON.

Rewrite prepared statement

Prepared statements are rewritten at parsing time, not at execution time. The difference between prepared statements and unprepared statements is that they may contain? Characters are used as parameter markers. In order to match what is in the prepared statement? Character, the rewrite pattern must be contained in the same place? Characters. Suppose the rewriting rule has this pattern.

Select?, 3

Several preparatory statements and patterns that match them are listed below

Whether the prepared statement pattern matches the statement

Prepare s as' select 3,3' Yes

Prepare s as' select?, 3' Yes

Prepare s as' select 3,?' No

Prepare s as' select?' No

Rewriter plug-in operation information

The Rewriter plug-in represents its operation information through several state variables:

Mysql > show global status like 'Rewriter%' +-- +-+ | Variable_name | Value | +-+-+ | Rewriter_number_loaded_rules | 3 | | Rewriter_number_reloads | 5 | | Rewriter_number_rewritten_queries | | 5 | | Rewriter_reload_error | OFF | +-- +-+ 4 rows in set (0.01 sec) |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.