Use of MySQL query rewrite plugin

Query Rewrite Plugin

As of MySQL 5.7.6, MySQL Server supports query rewrite plugins that can inspect and potentially modify statements received by the server before the server executes them.

The following is the official document introduction:

The pre-parse rewrite plugin has the following features:

1. This plugin allows rewriting SQL statements that arrive at the server before they are processed by the server.

2. The plugin receives a statement string and may return a different string.

A post-parse query rewrite plugin has the following characteristics:

1. The plugin supports statement rewriting based on parse tree.

2. The server parses each statement and passes its parse tree to the plugin, which can traverse the tree. The plugin can return the original tree to the server for further processing, or construct a different tree and return that one.

Generally speaking, the plug-in supports two rewriting methods. One is to directly modify the SQL string before syntax parsing, and the other is to rewrite by manipulating the syntax parsing tree after syntax parsing. This feature is very useful. For example, if a SQL statement is uploaded incorrectly, but the entire database cannot be queried because the index cannot be found, or you may be using a third-party compiled software, but the SQL statement may be executed incorrectly, and you cannot modify the application directly. This feature will be very useful, and you can also write plug-ins that meet user requirements.

Install or Uninstall

The simplest installation process is as follows:

shell> mysql -u root -p < install_rewriter.sql Enter password: (enter root password here) 

It can be found that a new library query_rewrite has been added to the database. Check the database:

Check if the plugin is currently installed:

Practical Operation

For example, the following statement forces the use of primary key query:

SELECT DBA_no, name from DBA_inf where DBA_no = ?

Rewrite it as:

SELECT DBA_no, name from DBA_inf force index(primary) where DBA_no = ?

To add rules for the Rewriter plugin, there are two steps:

1. Add corresponding rules to the rewrite_rules table;

2. Call the flush_rewrite_rules() stored procedure to load the rules in the table into the plugin.

The following example creates a simple rule to match statements that select a single literal value, performing the following actions:

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ​​("SELECT DBA_no, name from DBA_inf where DBA_no = ?","SELECT DBA_no, name from DBA_inf force index(primary) where DBA_no = ?","DBAs");

Query the rules just inserted:

The content of each column of the output rule is as follows:

Rewriter query rewriting plugin process

Adding a rule to the rewrite_rules table is not sufficient for the Rewriter plugin to use that rule. flush_rewrite_rules() must also be called to load the table contents into the plugin memory cache:

The Rewriter plugin operation uses a stored procedure to load the rules table into its memory cache. Under normal operation, the user simply calls flush_rewrite_rules() to load the contents of the rewrite_rules table into the Rewriter memory cache. After loading the table, it also clears the query cache.

When the rules table is modified, this procedure needs to be called again to have the plugin update its cache from the new table contents:

Use the statement pattern defined in the rewrite plugin to query the corresponding records:

SELECT DBA_no, name from DBA_inf where DBA_no =8; 

By using the explain statement, the current SQL has used the index

Rewrite plugin operation information

The Rewriter plugin provides information about its operation through several status variables:

Notes on these variables:

• Rewriter_number_loaded_rules: The number of rewrite plugin rewrite rules that were successfully loaded into memory from the rewrite_rules table for use by the Rewriter plugin.
• Rewriter_number_reloads: The number of times rewrite_rules are loaded into the Rewriter plugin cache.
• Rewriter_number_rewritten_queries: The number of queries rewritten by the Rewriter query rewriting plugin since it was loaded.
• Rewriter_reload_error: Whether an error occurred while loading the rewrite_rules table into the in-memory cache used by the Rewriter plugin recently. If the value is OFF, no error occurs. If the value is ON, an error occurs; check the message column of the rewriter_rules table for an error message.

When loading the rules table by calling the flush_rewrite_rules() stored procedure, if an error occurs in some rules, the CALL statement will generate an error and the plugin will set the Rewriter_reload_error status variable to ON:

In this case, check the rewrite_rules table for rows that contain non-NULL message column values ​​to see what the problem is.

Rewriter plugin uses character set

When the rewrite_rules table is loaded into the Rewriter plugin, the plugin uses the current global value of the character_set_client system variable to interpret statements. If character_set_client subsequently changes the global value, the rules table must be reloaded.

The client's session character_set_client value must be the same as the global value when the rule table was loaded, otherwise rule matching will not apply to that client.

The above is the detailed content of using MySQL query rewrite plug-in. For more information about MySQL query rewrite plug-in, please pay attention to other related articles on 123WORDPRESS.COM!