まだ、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-no


上記のように、思ったようにインデックスが使われているかどうか確認してみましたが、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-yes
Explain抜きの場合は、Workbenchの実行プランViewで確認出来るかどうか次回確認してみます。

上記確認してみました:GENERAL LOGを確認してみると、きちんとRewriteが有効になっている事が確認出来ました。
final

以下エラーログ (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 &#91;sakila&#93;>truncate table query_rewrite.rewrite_rules;
Query OK, 0 rows affected (0.37 sec)

root@localhost [sakila]>

The Query Rewrite Plugins


InnoDB_Pluginについて

13.6. The InnoDB Storage Engineからの抜粋
At the 2008 MySQL User Conference, Innobase announced availability of an InnoDB Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL.
The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
The version of the InnoDB Plugin is 1.0.6 as of MySQL 5.5.1 and is considered of Release Candidate (RC) quality.

The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability
and new capabilities for flexibility and ease of use.
Among the features of the InnoDB Plugin are ….. 以下プラグイン概要….

————————————————————————-
① Fast index creation
② Table and index compression
③ File format management
④ New INFORMATION_SCHEMA tables
⑤ Capacity tuning
⑥ Multiple background I/O threads
⑦ Group commit.
————————————————————————-

詳細は以下のinnodb.comを参照
InnoDB Plugin 1.0 for MySQL 5.1 (Early Adopter Release) User’s Guide

先日、MYSQL5.5をインストールしたので、Innodb_pluginはディフォルトで有効になっている。
以下のコマンドでバージョンを確認する事が出来ます。


select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
from information_schema.plugins;

SELECT @@innodb_version;

innodb_plugin_version

メモ: INFORMATION_SCHEMA.PLUGINSテーブルにはマイナーバージョン番号は表示されないようです。
Note that the PLUGIN_VERSION column in the table INFORMATION_SCHEMA.PLUGINS does not
display the third component of the version number, only the first and second components, as in 1.0.

その他留意事項
———————————————————————————————————-
1.5. Operational Restrictions
———————————————————————————————————-
Because the InnoDB Plugin introduces a new file format, with new on-disk data structures within
both the database and log files, there are important restrictions on the use of the plugin in typical user
environments. Specifically, you should pay special attention to the information presented here about
file format compatibility with respect to the following scenarios:

1) Downgrading from the InnoDB Plugin to the built-in InnoDB, or otherwise using different
versions of InnoDB with database files created by the InnoDB Plugin
2) Using mysqldump
3) Using MySQL replication
4) Using InnoDB Hot Backup

WARNING: Once you use the InnoDB Plugin on a set of database files, care must be taken to avoid
crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by
opening the database with MySQL when the plugin is not installed. It is strongly recommended that
you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server
when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the
plugin will not cause problems when using a prior version of InnoDB.
11.3, “How to Downgrade”.

WARNING:If you dump a database containing compressed tables with mysqldump,
the dump file may contain CREATE TABLE commands that attempt to create compressed tables,
or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure
the new database is running the InnoDB Plugin, with the proper settings for innodb_file_format and innodb_file_per_table, if you want to have the tables re-created as they exist in the original database.
Typically, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore
CREATE TABLE options they do not recognize, and the table(s) will be created in a format
used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured
with the InnoDB Plugin, with the same settings for innodb_file_format and innodb_file_per_table.
If you do not do so, and you create tables that require the new “Barracuda” file format, replication
errors may occur. If a slave MySQL server is running the built-in InnoDB, it will ignore the
CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC,
and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file
format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause
unpredictable behavior. A future version of InnoDB Hot Backup will support databases used
with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.

———————————————————————————————————-

Innovative Technologiesfor Performance andData Protection

mysql> select Variable_name from
-> information_schema.GLOBAL_VARIABLES
-> where Variable_name like ‘innodb_%’;
+———————————+
| Variable_name |
+———————————+
| INNODB_VERSION |
| INNODB_LOCKS_UNSAFE_FOR_BINLOG |
| INNODB_BUFFER_POOL_SIZE |
| INNODB_CONCURRENCY_TICKETS |
| INNODB_OLD_BLOCKS_PCT |
| INNODB_LOG_BUFFER_SIZE |
| INNODB_MAX_PURGE_LAG |
| INNODB_DOUBLEWRITE |
| INNODB_IO_CAPACITY |
| INNODB_TABLE_LOCKS |
| INNODB_AUTOEXTEND_INCREMENT |
| INNODB_THREAD_SLEEP_DELAY |
| INNODB_REPLICATION_DELAY |
| INNODB_STATS_ON_METADATA |
| INNODB_ROLLBACK_ON_TIMEOUT |
| INNODB_CHANGE_BUFFERING |
| INNODB_FILE_FORMAT |
| INNODB_DATA_FILE_PATH |
| INNODB_STRICT_MODE |
| INNODB_MAX_DIRTY_PAGES_PCT |
| INNODB_AUTOINC_LOCK_MODE |
| INNODB_COMMIT_CONCURRENCY |
| INNODB_MIRRORED_LOG_GROUPS |
| INNODB_SUPPORT_XA |
| INNODB_SYNC_SPIN_LOOPS |
| INNODB_ADAPTIVE_FLUSHING |
| INNODB_ADAPTIVE_HASH_INDEX |
| INNODB_DATA_HOME_DIR |
| INNODB_READ_IO_THREADS |
| INNODB_WRITE_IO_THREADS |
| INNODB_FORCE_RECOVERY |
| INNODB_LOG_FILES_IN_GROUP |
| INNODB_OPEN_FILES |
| INNODB_FILE_FORMAT_CHECK |
| INNODB_READ_AHEAD_THRESHOLD |
| INNODB_LOG_GROUP_HOME_DIR |
| INNODB_FAST_SHUTDOWN |
| INNODB_THREAD_CONCURRENCY |
| INNODB_STATS_SAMPLE_PAGES |
| INNODB_FLUSH_LOG_AT_TRX_COMMIT |
| INNODB_FLUSH_METHOD |
| INNODB_CHECKSUMS |
| INNODB_LOG_FILE_SIZE |
| INNODB_SPIN_WAIT_DELAY |
| INNODB_FILE_PER_TABLE |
| INNODB_LOCK_WAIT_TIMEOUT |
| INNODB_OLD_BLOCKS_TIME |
| INNODB_ADDITIONAL_MEM_POOL_SIZE |
| INNODB_USE_SYS_MALLOC |
+———————————+
49 rows in set (0.00 sec)

mysql>

    新機能として

[innodb_file_format」
InnoDBのファイルフォーマットを指定可能
Antelope  従来のファイルフォーマット
Barracuda 圧縮機能サポートファイルフォーマット

innodb_file_format

検証
CREATE DATABASE `T_INNODB` /*!40100 DEFAULT CHARACTER SET utf8 */


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

ROW_FORMAT

* REDUNDANT
* COMPACT
* DYNAMIC (Barracuda format & No Compress)
* COMPRESSED: (Barracuda format & Compress)

innodb_plugin_test

よく見てみると、上記QueryはWarrningが出ていてテーブルが圧縮されていない事が分かった。

オプションファイルに以下の設定を入れて再度、MYSQLを再起動して設定を反映させた。
innodb_file_format= Barracuda

innodb_file_format_bara

mysql> show variables like 'innodb_file%';

barracuda

ファイルフォーマットがBarracudaに変わったので再度テーブルを作成してみる。


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 DEFAULT CHARSET=utf8
/* BLOCKサイズを4に変更した */;


select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT
from information_schema.tables
where TABLE_SCHEMA = 'T_INNODB';

innodb_file_formatを変更した事でwarrningが消えた事を確認
barracuda_confirm

実際のファイルを確認してみるとファイルサイズが違う。
※ ブロックサイズを8で作成してもファイルサイズはBarracudaの方が小さい。

compare

information_schema.INNODB_CMPの変化の確認。
圧縮を行った回数(compress_ops), 圧縮が成功した回数(compress_ops_ok)
などを見て圧縮への対応を検討していく事が可能です。


CREATE TABLE `T_Antelope_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

select * from information_schema.INNODB_CMP;

innodb_cmp

【メモ:パフォーマンス検証の際は、以下の値を確認】
innodb_buffer_pool_size
innodb_flush_methodをO_DIRECT

参考サイト
——————————————
INNOBASE
InnoDB Plugin Change History
13.6. The InnoDB Storage Engine
InnoDB Plugin 1.0.4 – InnoDB史上極めて重要なリリース