查询重写

查询重写

​ Query Rewrite Plugin 通过规则匹配的方式，将符合条件的 SQL 进行重写，从而达到调整执行计划或者其他的目的。

一、安装

​ 在 $mysqlhomt/share 目录下执行安装脚本，创建 query_rewrite 数据库和 rewrite_rules 规则表

mysql -uroot -p -S /home/mysql/mysql.sock < /home/mysql/mysql/share/install_rewriter.sql

mysql> show global variables like 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.02 sec)

​ 注意： SQL 重写插件安装之后，即使关闭插件，仍然会有一定的额外开销，SQL 重写插件可以动态安装和打开。

二、示例

​ 例子 1

1)增加匹配规则
mysql> insert into query_rewrite.rewrite_rules(pattern,replacement) values('select ?','select ? + 1');
Query OK, 1 row affected (0.00 sec)

2)刷新使规则生效
mysql> call query_rewrite.flush_rewrite_rules();
Query OK, 0 rows affected, 1 warning (0.05 sec)

3)测试效果，注意改写对函数无效。
mysql> select 1;
+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set, 1 warning (0.00 sec)

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

​ 例子 2

​ 1、准备数据

mysql> create table `tab_test_rewrite`(
    -> `order_id` varchar(20) not null,
    -> `user_id` varchar(40) not null,
    -> `status` smallint(5) not null,
    -> primary key(`order_id`),
    -> key `idx_user_id`(`user_id`),
    -> key `idx_status`(`status`));
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql>
mysql> create procedure `p_test1`()
    -> begin
    -> declare v_user int;
    -> declare v_order int;
    -> set @v_user=1;
    -> while @v_user<101
    -> do
    -> set @v_order=1;
    ->  while @v_order<1001
    ->  do
    ->   insert into tab_test_rewrite(order_id,user_id,status) values(concat('order_',@v_user,'_',@v_order),concat('user_',@v_user),1);
    ->   set @v_order=@v_order+1;
    ->  end while;
    -> set @v_user=@v_user+1;
    -> end while;
    ->  insert into tab_test_rewrite(order_id,user_id,status) values('order_1_1001','user_1',0);
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call p_test1();
Query OK, 1 row affected (59.02 sec)

​ 2、查询某个用户的状态为 1 的数据，从下可以看到默认的执行计划是扫描了 idx_user_id 和 idx_status 两个索引，并做了 index_merge , 而实际上，绝大多数的 status 都等于 1，所以 idx_status 对于状态是 1 的数据的选择读很差，一般没有必要去扫描这个索引。

mysql> explain select * from tab_test_rewrite where user_id='user_1' and status=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_test_rewrite
   partitions: NULL
         type: index_merge
possible_keys: idx_user_id,idx_status
          key: idx_user_id,idx_status
      key_len: 122,2
          ref: NULL
         rows: 500
     filtered: 100.00
        Extra: Using intersect(idx_user_id,idx_status); Using where; Using index
1 row in set, 1 warning (0.00 sec)

​ 3、使用 force index 的执行计划，可以看出这个更符合我们的预期。

mysql> explain select * from tab_test_rewrite force index(idx_user_id) where user_id='user_1' and status=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tab_test_rewrite
   partitions: NULL
         type: ref
possible_keys: idx_user_id
          key: idx_user_id
      key_len: 122
          ref: const
         rows: 1001
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

​ 4、使用查询重写

#1) 改写这个查询
mysql> insert into query_rewrite.rewrite_rules(pattern_database,pattern,replacement) values('test','select * from tab_test_rewrite where user_id=? and status=1','select * from tab_test_rewrite force index(idx_user_id) where user_id=? and status=1');
Query OK, 1 row affected (0.01 sec)

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

#2) 执行查询，可以看到，查询已经正确被改写。这种通过字符串匹配和替换的方式来做的改写，可能造成一些查询被错误的修改。使用这一特性时，需要做好充分的测试。
mysql> select * from tab_test_rewrite where user_id='user_1' and status=1;
......
mysql> show warnings \G;
*************************** 1. row ***************************
  Level: Note
   Code: 1105
Message: Query 'select * from tab_test_rewrite where user_id='user_1' and status=1' rewritten to 'select * from tab_test_rewrite force index(idx_user_id) where user_id='user_1' and status=1' by a query rewrite plugin
1 row in set (0.00 sec)