![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
まだ、Lab版ですがMySQL運用上のオプションが増えるので、MySQL Rewrite Pluginを試してみました。
主な、用途としてはApacheのmod_rewriteなどと同じで、アプリケーション側の変更無くrewriteでアクセスがコントロール可能です。
基本的には、mod_rewriteも正規表現等の文字列でコントロールしていたのでこちらも文字列コントロールしているようです。
ソースは見ていないので、検証した結果からの推測です。
短期的なスパンでのアプリケーションの変更が難しいが、即効性のあるソリューションとしてパフォーマンス改善したい場合などには、
DB側でルール作成するだけなので、かなり使えるかもしれません。もちろんGAになってからですが。
Pluginのインストール
root@localhost [mysql]>select @@version; +------------------------+ | @@version | +------------------------+ | 5.7.5-labs-preview-log | +------------------------+ 1 row in set (0.00 sec) root@localhost [mysql]>select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_DESCRIPTION from information_schema.plugins; +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ | PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_DESCRIPTION | +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ | binlog | 1.0 | ACTIVE | This is a pseudo storage engine to represent the binlog in a transaction | | mysql_native_password | 1.0 | ACTIVE | Native MySQL authentication | | sha256_password | 1.0 | ACTIVE | SHA256 password authentication | | MyISAM | 1.0 | ACTIVE | MyISAM storage engine | | MRG_MYISAM | 1.0 | ACTIVE | Collection of identical MyISAM tables | | MEMORY | 1.0 | ACTIVE | Hash based, stored in memory, useful for temporary tables | | InnoDB | 5.7 | ACTIVE | Supports transactions, row-level locking, and foreign keys | | INNODB_TRX | 5.7 | ACTIVE | InnoDB transactions | | INNODB_LOCKS | 5.7 | ACTIVE | InnoDB conflicting locks | | INNODB_LOCK_WAITS | 5.7 | ACTIVE | InnoDB which lock is blocking which | | INNODB_CMP | 5.7 | ACTIVE | Statistics for the InnoDB compression | | INNODB_CMP_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compression; reset cumulated counts | | INNODB_CMPMEM | 5.7 | ACTIVE | Statistics for the InnoDB compressed buffer pool | | INNODB_CMPMEM_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | | INNODB_CMP_PER_INDEX | 5.7 | ACTIVE | Statistics for the InnoDB compression (per index) | | INNODB_CMP_PER_INDEX_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compression (per index); reset cumulated counts | | INNODB_BUFFER_PAGE | 5.7 | ACTIVE | InnoDB Buffer Page Information | | INNODB_BUFFER_PAGE_LRU | 5.7 | ACTIVE | InnoDB Buffer Page in LRU | | INNODB_BUFFER_POOL_STATS | 5.7 | ACTIVE | InnoDB Buffer Pool Statistics Information | | INNODB_TEMP_TABLE_INFO | 5.7 | ACTIVE | InnoDB Temp Table Stats | | INNODB_METRICS | 5.7 | ACTIVE | InnoDB Metrics Info | | INNODB_FT_DEFAULT_STOPWORD | 5.7 | ACTIVE | Default stopword list for InnDB Full Text Search | | INNODB_FT_DELETED | 5.7 | ACTIVE | INNODB AUXILIARY FTS DELETED TABLE | | INNODB_FT_BEING_DELETED | 5.7 | ACTIVE | INNODB AUXILIARY FTS BEING DELETED TABLE | | INNODB_FT_CONFIG | 5.7 | ACTIVE | INNODB AUXILIARY FTS CONFIG TABLE | | INNODB_FT_INDEX_CACHE | 5.7 | ACTIVE | INNODB AUXILIARY FTS INDEX CACHED | | INNODB_FT_INDEX_TABLE | 5.7 | ACTIVE | INNODB AUXILIARY FTS INDEX TABLE | | INNODB_SYS_TABLES | 5.7 | ACTIVE | InnoDB SYS_TABLES | | INNODB_SYS_TABLESTATS | 5.7 | ACTIVE | InnoDB SYS_TABLESTATS | | INNODB_SYS_INDEXES | 5.7 | ACTIVE | InnoDB SYS_INDEXES | | INNODB_SYS_COLUMNS | 5.7 | ACTIVE | InnoDB SYS_COLUMNS | | INNODB_SYS_FIELDS | 5.7 | ACTIVE | InnoDB SYS_FIELDS | | INNODB_SYS_FOREIGN | 5.7 | ACTIVE | InnoDB SYS_FOREIGN | | INNODB_SYS_FOREIGN_COLS | 5.7 | ACTIVE | InnoDB SYS_FOREIGN_COLS | | INNODB_SYS_TABLESPACES | 5.7 | ACTIVE | InnoDB SYS_TABLESPACES | | INNODB_SYS_DATAFILES | 5.7 | ACTIVE | InnoDB SYS_DATAFILES | | CSV | 1.0 | ACTIVE | CSV storage engine | | ARCHIVE | 3.0 | ACTIVE | Archive storage engine | | BLACKHOLE | 1.0 | ACTIVE | /dev/null storage engine (anything you write to it disappears) | | PERFORMANCE_SCHEMA | 0.1 | ACTIVE | Performance Schema | | partition | 1.0 | ACTIVE | Partition Storage Engine Helper | | FEDERATED | 1.0 | ACTIVE | Federated MySQL storage engine | +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ 42 rows in set (0.00 sec) root@localhost [mysql]>system cat /usr/local/mysql2/lib/plugin/install_rewriter_plugin.sql CREATE DATABASE IF NOT EXISTS query_rewrite; CREATE TABLE IF NOT EXISTS query_rewrite.rewrite_rules ( pattern VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, pattern_database VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin, replacement VARCHAR(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, enabled CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Y', message VARCHAR(1000) CHARACTER SET utf8 COLLATE utf8_bin ) DEFAULT CHARSET = utf8 ENGINE = INNODB; GRANT SELECT ON query_rewrite.rewrite_rules TO root@localhost; GRANT INSERT ON query_rewrite.rewrite_rules TO root@localhost; GRANT UPDATE ON query_rewrite.rewrite_rules TO root@localhost; INSTALL PLUGIN rewriter SONAME 'rewriter.so'; CREATE FUNCTION load_rewrite_rules RETURNS STRING SONAME 'rewriter.so'; DELIMITER // CREATE PROCEDURE query_rewrite.flush_rewrite_rules() BEGIN COMMIT; SELECT load_rewrite_rules() INTO @v; END // DELIMITER ; root@localhost [mysql]>source /usr/local/mysql2/lib/plugin/install_rewriter_plugin.sql Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.07 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.03 sec) root@localhost [mysql]>select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_DESCRIPTION from information_schema.plugins; +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ | PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_DESCRIPTION | +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ | binlog | 1.0 | ACTIVE | This is a pseudo storage engine to represent the binlog in a transaction | | mysql_native_password | 1.0 | ACTIVE | Native MySQL authentication | | sha256_password | 1.0 | ACTIVE | SHA256 password authentication | | MyISAM | 1.0 | ACTIVE | MyISAM storage engine | | MRG_MYISAM | 1.0 | ACTIVE | Collection of identical MyISAM tables | | MEMORY | 1.0 | ACTIVE | Hash based, stored in memory, useful for temporary tables | | InnoDB | 5.7 | ACTIVE | Supports transactions, row-level locking, and foreign keys | | INNODB_TRX | 5.7 | ACTIVE | InnoDB transactions | | INNODB_LOCKS | 5.7 | ACTIVE | InnoDB conflicting locks | | INNODB_LOCK_WAITS | 5.7 | ACTIVE | InnoDB which lock is blocking which | | INNODB_CMP | 5.7 | ACTIVE | Statistics for the InnoDB compression | | INNODB_CMP_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compression; reset cumulated counts | | INNODB_CMPMEM | 5.7 | ACTIVE | Statistics for the InnoDB compressed buffer pool | | INNODB_CMPMEM_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | | INNODB_CMP_PER_INDEX | 5.7 | ACTIVE | Statistics for the InnoDB compression (per index) | | INNODB_CMP_PER_INDEX_RESET | 5.7 | ACTIVE | Statistics for the InnoDB compression (per index); reset cumulated counts | | INNODB_BUFFER_PAGE | 5.7 | ACTIVE | InnoDB Buffer Page Information | | INNODB_BUFFER_PAGE_LRU | 5.7 | ACTIVE | InnoDB Buffer Page in LRU | | INNODB_BUFFER_POOL_STATS | 5.7 | ACTIVE | InnoDB Buffer Pool Statistics Information | | INNODB_TEMP_TABLE_INFO | 5.7 | ACTIVE | InnoDB Temp Table Stats | | INNODB_METRICS | 5.7 | ACTIVE | InnoDB Metrics Info | | INNODB_FT_DEFAULT_STOPWORD | 5.7 | ACTIVE | Default stopword list for InnDB Full Text Search | | INNODB_FT_DELETED | 5.7 | ACTIVE | INNODB AUXILIARY FTS DELETED TABLE | | INNODB_FT_BEING_DELETED | 5.7 | ACTIVE | INNODB AUXILIARY FTS BEING DELETED TABLE | | INNODB_FT_CONFIG | 5.7 | ACTIVE | INNODB AUXILIARY FTS CONFIG TABLE | | INNODB_FT_INDEX_CACHE | 5.7 | ACTIVE | INNODB AUXILIARY FTS INDEX CACHED | | INNODB_FT_INDEX_TABLE | 5.7 | ACTIVE | INNODB AUXILIARY FTS INDEX TABLE | | INNODB_SYS_TABLES | 5.7 | ACTIVE | InnoDB SYS_TABLES | | INNODB_SYS_TABLESTATS | 5.7 | ACTIVE | InnoDB SYS_TABLESTATS | | INNODB_SYS_INDEXES | 5.7 | ACTIVE | InnoDB SYS_INDEXES | | INNODB_SYS_COLUMNS | 5.7 | ACTIVE | InnoDB SYS_COLUMNS | | INNODB_SYS_FIELDS | 5.7 | ACTIVE | InnoDB SYS_FIELDS | | INNODB_SYS_FOREIGN | 5.7 | ACTIVE | InnoDB SYS_FOREIGN | | INNODB_SYS_FOREIGN_COLS | 5.7 | ACTIVE | InnoDB SYS_FOREIGN_COLS | | INNODB_SYS_TABLESPACES | 5.7 | ACTIVE | InnoDB SYS_TABLESPACES | | INNODB_SYS_DATAFILES | 5.7 | ACTIVE | InnoDB SYS_DATAFILES | | CSV | 1.0 | ACTIVE | CSV storage engine | | ARCHIVE | 3.0 | ACTIVE | Archive storage engine | | BLACKHOLE | 1.0 | ACTIVE | /dev/null storage engine (anything you write to it disappears) | | PERFORMANCE_SCHEMA | 0.1 | ACTIVE | Performance Schema | | partition | 1.0 | ACTIVE | Partition Storage Engine Helper | | FEDERATED | 1.0 | ACTIVE | Federated MySQL storage engine | | Rewriter | 0.1 | ACTIVE | A query rewrite plugin that rewrites queries using the parse tree. | +----------------------------+----------------+---------------+---------------------------------------------------------------------------+ 43 rows in set (0.00 sec) root@localhost [mysql]>
Installが終わると、データベースとルール管理用のテーブルが用意されてます。
root@localhost [mysql]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| query_rewrite |
+--------------------+
4 rows in set (0.00 sec)
root@localhost [mysql]>use query_rewrite;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost [query_rewrite]>show tables;
+-------------------------+
| Tables_in_query_rewrite |
+-------------------------+
| rewrite_rules |
+-------------------------+
1 row in set (0.00 sec)
root@localhost [query_rewrite]>desc rewrite_rules;
+------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------+------+-----+---------+-------+
| pattern | varchar(10000) | NO | | NULL | |
| pattern_database | varchar(20) | YES | | NULL | |
| replacement | varchar(10000) | NO | | NULL | |
| enabled | char(1) | NO | | Y | |
| message | varchar(1000) | YES | | NULL | |
+------------------+----------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
root@localhost [query_rewrite]>select * from rewrite_rules;
Empty set (0.00 sec)
root@localhost [query_rewrite]>show create table rewrite_rules\G
*************************** 1. row ***************************
Table: rewrite_rules
Create Table: CREATE TABLE `rewrite_rules` (
`pattern` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`pattern_database` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`replacement` varchar(10000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`enabled` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Y',
`message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [query_rewrite]>
以下で、Sakilaのactorテーブルに対してのQueryに特定のIndexを強制的に使わせるようにしてみました。
root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [sakila]>ALTER TABLE actor ADD INDEX idx_fl(first_name,last_name);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [sakila]>ALTER TABLE actor ADD index idx_actor_first_name (first_name);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [sakila]>show create table actor\G
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`),
KEY `idx_fl` (`first_name`,`last_name`),
KEY `idx_actor_first_name` (`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [sakila]>
root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ref | idx_fl,idx_actor_first_name | idx_fl | 137 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
root@localhost [sakila]>
root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern, replacement ) VALUES ( 'select * from sakila.actor where first_name = ?', 'select * from sakila.actor force index(idx_actor_first_name) where first_name = ?' );
Query OK, 1 row affected (0.00 sec)
root@localhost [sakila]>select * from query_rewrite.rewrite_rules;
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| select * from sakila.actor where first_name = ? | NULL | select * from sakila.actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)
root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)
root@localhost [sakila]>select * from query_rewrite.rewrite_rules;
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
| select * from sakila.actor where first_name = ? | NULL | select * from sakila.actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
+-------------------------------------------------+------------------+-----------------------------------------------------------------------------------+---------+---------+
1 row in set (0.00 sec)
root@localhost [sakila]>select * from actor where first_name = 'JOHNNY';
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+--------------+---------------------+
| 40 | JOHNNY | CAGE | 2006-02-15 04:34:33 |
| 5 | JOHNNY | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
2 rows in set (0.07 sec)
root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ref | idx_fl,idx_actor_first_name | idx_fl | 137 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
root@localhost [sakila]>
root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern,pattern_database,replacement ) VALUES ( 'select * from actor where first_name = ?','sakila','select * from actor force index(idx_actor_first_name) where first_name = ?'); Query OK, 1 row affected (0.00 sec) root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.02 sec) root@localhost [sakila]>select * from query_rewrite.rewrite_rules; +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ | pattern | pattern_database | replacement | enabled | message | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ | select * from actor where first_name = ? | sakila | select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ 1 row in set (0.00 sec) root@localhost [sakila]> root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY'; +----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+ | 1 | SIMPLE | actor | NULL | ref | idx_fl,idx_actor_first_name | idx_fl | 137 | const | 2 | 100.00 | NULL | +----+-------------+-------+------------+------+-----------------------------+--------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) root@localhost [sakila]>
上記のように、思ったようにインデックスが使われているかどうか確認してみましたが、Explainを付けると別のQueryと判断されてしまっているようで確認出来ませんでした。
念の為に、Explainを含むQueryを登録して再確認してみました。
root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules(pattern,pattern_database,replacement ) VALUES ('explain select * from actor where first_name = ?','sakila','explain select * from actor force index(idx_actor_first_name) where first_name = ?');
Query OK, 1 row affected (0.00 sec)
root@localhost [sakila]>select * from query_rewrite.rewrite_rules;
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ? | sakila | select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
| explain select * from actor where first_name = ? | sakila | explain select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
2 rows in set (0.01 sec)
root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.02 sec)
root@localhost [sakila]>select * from query_rewrite.rewrite_rules; +--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| pattern | pattern_database | replacement | enabled | message |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
| select * from actor where first_name = ? | sakila | select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
| explain select * from actor where first_name = ? | sakila | explain select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL |
+--------------------------------------------------+------------------+------------------------------------------------------------------------------------+---------+---------+
2 rows in set (0.00 sec)
root@localhost [sakila]>explain select * from actor where first_name = 'JOHNNY';
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | actor | NULL | ref | idx_actor_first_name | idx_actor_first_name | 137 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
root@localhost [sakila]>
結論としては、問題無くRewriteは動作しているようです。Explainを含むQueryも適切に書き換えられていて、指定したIndexが使われていました。
Query変更せずに、強制的にIndexなどをコントロールしたい場合は良いですね。
Explain抜きの場合は、Workbenchの実行プランViewで確認出来るかどうか次回確認してみます。
上記確認してみました：GENERAL LOGを確認してみると、きちんとRewriteが有効になっている事が確認出来ました。
以下エラーログ (DB指定をQueryで行うか、pattern_databaseにて指定する必要がある。)
root@localhost [sakila]>INSERT INTO query_rewrite.rewrite_rules( pattern, replacement ) VALUES ( 'select * from actor where first_name = ?', 'select * from actor force index(idx_actor_first_name) where first_name = ?' ); Query OK, 1 row affected (0.00 sec) root@localhost [sakila]>select * from query_rewrite.rewrite_rules; +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ | pattern | pattern_database | replacement | enabled | message | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ | select * from actor where first_name = ? | NULL | select * from actor force index(idx_actor_first_name) where first_name = ? | Y | NULL | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+---------+ 1 row in set (0.00 sec) root@localhost [sakila]>CALL query_rewrite.flush_rewrite_rules(); Query OK, 1 row affected (0.27 sec) root@localhost [sakila]>select * from query_rewrite.rewrite_rules; +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+ | pattern | pattern_database | replacement | enabled | message | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+ | select * from actor where first_name = ? | NULL | select * from actor force index(idx_actor_first_name) where first_name = ? | N | Parse error in pattern: >>No database selected<< | +------------------------------------------+------------------+----------------------------------------------------------------------------+---------+--------------------------------------------------+ 1 row in set (0.00 sec) root@localhost [sakila]>truncate table query_rewrite.rewrite_rules; Query OK, 0 rows affected (0.37 sec) root@localhost [sakila]>