MySQL的query_rewrite中文手册

MySQL的query_rewrite中文手册

author：陈镇坤27

创建时间：2021年11月25日09:56:11

编辑时间：

转载请注明出处

——————————————————————————————

时间有限，随着学习深入，会不断更新本文档。

参考资料是官方文档：https://dev.mysql.com/doc/search/?d=12&p=1&q=query_rewrite

将参考资料的原文贴在最后面，用“——”隔开，可跳过不看，如有错误，感谢在评论区指正。

——————————————————————————————

查询重写插件的匹配原理

查询重写插件利用statement摘要和摘要的哈希值去分阶段匹配传入的statement并应用重写规则。系统变量max_digest_length决定用于计算statement摘要的buffer大小，该值越大，越有利于区分更长的statement摘要，该值越小，占用内存则越小，但同时statement发生摘要哈希冲突的可能性就越高。

该插件的statement匹配规则如下：

1、计算statement摘要哈希值，并与规则摘要哈希值做匹配。虽然匹配可能在意义上错误，但这只是一个快速验收检查步骤而已。

2、如果statement摘要的哈希值与规则摘要哈希值匹配，则匹配校验statement摘要的规范化形式和规则摘要的规范化形式（简单来说：匹配语句模板）。

3、如果statement的规范式与规则相匹配，则比较statement的所有的字面值与规则的值匹配符——“？”。例如该statement是一个select语句，则规则中的？也会匹配statement中的？，否则两者的字面值必须相同。（简单来说，有通配符？就匹配，规则也可以没有通配符，使用具体的值）

此外，当匹配命中多个规则时，会随机使用其中一个规则。

如果pattern规则的标记比实际的replacement多，插件会丢弃多余的数据值。反之，则报错。插件会在加载规则表事进行校验。将这些错误信息写在规则行的message列，并且将Rewriter_reload_error参数设置为ON。

——————————————————————————————

How Statement Matching Works

The Rewriter plugin uses statement digests and digest hash values to match incoming statements against rewrite rules in stages. The max_digest_length system variable determines the size of the buffer used for computing statement digests. Larger values enable computation of digests that distinguish longer statements. Smaller values use less memory but increase the likelihood of longer statements colliding with the same digest value.

The plugin matches each statement to the rewrite rules as follows:

1. Compute the statement digest hash value and compare it to the rule digest hash values. This is subject to false positives, but serves as a quick rejection test.
2. If the statement digest hash value matches any pattern digest hash values, match the normalized (statement digest) form of the statement to the normalized form of the matching rule patterns.
3. If the normalized statement matches a rule, compare the literal values in the statement and the pattern. A ? character in the pattern matches any literal value in the statement. If the statement prepares a SELECT statement, ? in the pattern also matches ? in the statement. Otherwise, corresponding literals must be the same.

If multiple rules match a statement, it is nondeterministic which one the plugin uses to rewrite the statement.

If a pattern contains more markers than the replacement, the plugin discards excess data values. If a pattern contains fewer markers than the replacement, it is an error. The plugin notices this when the rules table is loaded, writes an error message to the message column of the rule row to communicate the problem, and sets the Rewriter_reload_error status variable to ON.