特定のSQL処理で、GROUP BYなどの集合関数を利用していて、
“Using temporary”,”Using filesort”などが出て処理時間がかかり過ぎたり、
サブクエリーによる結果をJOINしてindexが利用出来無かったりと、
困難な場面に遭遇する事があるかと思います。

基本的には、物理的に変更しても良くて数倍だと思いますので、
アプリケーションやクエリーを工数かけて書き換えて対応するのが良いと思いますが、
なかなか出来ない場合は、可能な範囲でサーバーパラメータを変更したり、
クエリーを若干変更してメモリーテーブルやTEMPORARY TABLEなどでワークテーブルを作成し、
サブクエリーなどの結果を随時集計しIndexを使えるように処理する方法もあるかと思います。
もちろんハードで短期、中期的に解決する為に、力技で対応した場合もありましたが。。。

過去にもMEMORY STORAGE ENGINEについて、書きましたが改めて再確認してみました。
MEMORY ストレージエンジン

その他ソリューション:
Entperprise Edition:Query Rewrite Plugins
検証:MySQL Query Rewrite Plugins

MEMORYストレージエンジンによるワークテーブル
以下、メモリーストレージエンジンを利用した一時テーブル例 (インデックス利用可)


root@localhost [test]> CREATE TABLE T_Work_mem01 (
    ->   id int(11) DEFAULT NULL,
    ->   text varchar(100) DEFAULT NULL,
    ->   KEY idx_T_MEM01_id (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_mem01;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_mem01 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_mem01 where id = 8;
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_mem01 | ref  | idx_T_MEM01_id | idx_T_MEM01_id | 5       | const |    2 | NULL  |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 


root@localhost [test]> CREATE TABLE T_Work_mem02 (
    -> id int(11) DEFAULT NULL,
    -> text varchar(100) DEFAULT NULL,
    -> INDEX USING HASH (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_mem02;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

root@localhost [test]> insert into T_Work_mem02 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_mem02 where id = 8;
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_mem02 | ref  | id            | id   | 5       | const |    2 | NULL  |
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

memory

メモリーテーブルなので、再起動したらデータは無くなりますが、
ワークテーブルとして一時的に利用するのであれば問題無いかと。


[root@GA01 admin]# /etc/init.d/mysql.server restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@GA01 admin]# exit
exit
[admin@GA01 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
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 [test]> select count(*) from T_Work_mem01;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> show tables like 'T_Work%';
+--------------------------+
| Tables_in_test (T_Work%) |
+--------------------------+
| T_Work_mem01             |
| T_Work_mem02             |
+--------------------------+
2 rows in set (0.00 sec)

root@localhost [test]>

※ データは空ですが、テーブル定義は残ります。

memory_table_after_reboot

参考 (レプリケーション時の注意点等):
8.3.8 Comparison of B-Tree and Hash Indexes

17.4.1.21 Replication and MEMORY Tables

15.3 The MEMORY Storage Engine

[留意点] MEMORY Tables and Replication
A server’s MEMORY tables become empty when it is shut down and restarted.
If the server is a replication master, its slaves are not aware that these
tables have become empty, so you see out-of-date content if you select data
from the tables on the slaves. To synchronize master and slave MEMORY tables,
when a MEMORY table is used on a master for the first time since it was started,
a DELETE statement is written to the master’s binary log, to empty the table on the slaves also.
The slave still has outdated data in the table during the interval
between the master’s restart and its first use of the table.
To avoid this interval when a direct query to the slave could return stale data,
use the –init-file option to populate the MEMORY table on the master at startup.

今回のデモ環境では,SLAVEにて除外してあります。

ignor

TEMPORARYテーブルの利用例(インデックス利用可)


root@localhost [test]> CREATE temporary TABLE T_Work_temp01 (
    ->  id int(11) DEFAULT NULL,
    ->  text varchar(100) DEFAULT NULL,
    ->  KEY idx_T_MEM01_id (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_temp01;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_temp01 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_temp01 where id = 8;
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_temp01 | ref  | idx_T_MEM01_id | idx_T_MEM01_id | 5       | const |    2 | NULL  |
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> CREATE temporary TABLE T_Work_temp02 (
    ->  id int(11) DEFAULT NULL,
    ->  text varchar(100) DEFAULT NULL,
    ->  INDEX USING HASH (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> desc T_Work_temp02;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_temp02 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_temp02 where id = 8;
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_temp02 | ref  | id            | id   | 5       | const |    2 | NULL  |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

TEMPORARYテーブルは、Show Tablesをしても表示されません。
作成したセッションでは、SELECTする事は可能です。


root@localhost [test]> show tables;
+--------------------+
| Tables_in_test     |
+--------------------+
| FW_DEMO            |
| T_MEM              |
| T_MEM01            |
| T_ONLINE_DDL       |
| T_ONLY_FULL        |
| T_UNION01          |
| T_UNION02          |
| T_Work_mem01       |
| T_Work_mem02       |
| card_info          |
| employees          |
| tbl_partition      |
| tbl_partition2     |
| tbl_partition_year |
| users              |
+--------------------+
15 rows in set (0.00 sec)

root@localhost [test]> select count(*) from T_Work_temp01;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> select count(*) from T_Work_temp02;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> 

オラクル等では、Global Session Temp Tableなどを利用して
他のセッションからもTEMPORARY TABLEにアクセス出来ますが、
こちらは、他のセッションからアクセスする事は出来ません。


[admin@GA02 ~]$ mysql -h 192.168.56.201 -u admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin@192.168.56.201 [(none)]> use test
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
admin@192.168.56.201 [test]> select count(*) from T_Work_temp01;
ERROR 1146 (42S02): Table 'test.T_Work_temp01' doesn't exist
admin@192.168.56.201 [test]>

temporary_table

ログオフ、ログインし直しても、当然無くなっています。
作成した、セッションのみで有効


root@localhost [test]> exit
Bye
[admin@GA01 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
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 [test]> select count(*) from T_Work_temp01;
ERROR 1146 (42S02): Table 'test.T_Work_temp01' doesn't exist
root@localhost [test]> 

その他、参考情報
Materialized Views with MySQL
MySQLにおけるJOINのチューニングの定石


MS SQLなどだとSP_RENAMEなどのストアドプロシジャーを利用してテーブル名を変更していましたが、
MYSQLはやはり、オラクルのよりのDBなのでオラクルと同じように
”ALTER TABLE 既存テーブル名 RENAME TO 新規テーブル名;”で変更してあげるようです。
テーブル名の変更はあまり頻度の高い操作ではありませんが、データのフラグメンテーションを
解消するために別テーブルを作成しておいて、メンテナンスと同時に名前を変更して切り替える…………
年度毎にテーブル名を変更して、空の新しいテーブルに新年度からのデータを入れるなど……..
色々と便利ば利用方法もありますので、いざという時に直ぐに思い出せるようにメモしておきます。

——————————————————————
MYSQLでのテーブルのRENAMEの方法①
——————————————————————


mysql> use STUDY
Database changed
mysql> show tables;
+------------------+
| Tables_in_STUDY |
+------------------+
| T1 |
| TIMESTAMP_FUNC |
| Y2008 |
| Y2009 |
| Y2010 |
+------------------+
16 rows in set (0.13 sec)

mysql> desc T1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.06 sec)

mysql> ALTER TABLE T1 RENAME TO K1;
Query OK, 0 rows affected (0.04 sec)

mysql> desc K1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_STUDY |
+------------------+
| K1 |
| TIMESTAMP_FUNC |
| Y2008 |
| Y2009 |
| Y2010 |
+------------------+
16 rows in set (0.01 sec)

mysql>

alter_rename

——————————————————————
MYSQLでのテーブルのRENAMEの方法②
——————————————————————


mysql> desc STUDY.test_col;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| col1 | int(4) unsigned | YES | | NULL | |
| col2 | int(4) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> RENAME TABLE STUDY.test_col TO STUDY.RENAME_TEST;
Query OK, 0 rows affected (0.01 sec)

mysql> desc STUDY.RENAME_TEST;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| col1 | int(4) unsigned | YES | | NULL | |
| col2 | int(4) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

rename_table

※ MYISAM, INNODBともに*.frmファイルの名前はRENAMEと同時に自動で変わります。
尚、MYISAMのデータファイルなども同じくファイル名も自動で変わるようです。

RENAME_TEST.frm
RENAME_TEST.MYD
RENAME_TEST.MYI

————————————————
参考サイト
————————————————
12.1.19. RENAME TABLE 構文

このステートメントは1つ、または複数のテーブルのリネームをします。

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

リネームは自動的に行われますので、その他のスレッドはリネーム作業中はどのテーブルにも
アクセスできません。例えば、もし既存テーブル old_table があるとしたら、同じ構成で中身が
空の別のテーブル new_table を作成し、その後、次のように既存テーブルと空のテーブルを
入れ替える事ができます。(backup_table は存在していないと仮定する):


CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

もしこのステートメントが複数のテーブルをリネームすると、残りの作業は左から右に行われます。
もし2つのテーブル名の入れ替えをしたければ、このように行う事ができます。(tmp_table が存在しないと仮定):


RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;

2つのデータベースが同じファイル システム上にある限り、テーブルを1つのデータベースから
別のDBに移動するのに RENAME TABLE を利用する事ができます。

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

リネームされたテーブルやビューに与えられた権限は、新しい名前に移動しません。
それらは手動で変更しなければいけません。

※ 実証確認
権限テーブルは、テーブル名変更前も変更後も同じ名前で残っている事を確認。

mysql> select Host,User,DB,Table_name,Table_priv from tables_priv where User = 'restrict_user';
+------+---------------+-------+-------------+------------+
| Host | User | DB | Table_name | Table_priv |
+------+---------------+-------+-------------+------------+
| % | restrict_user | STUDY | RENAME_TEST | Select |
+------+---------------+-------+-------------+------------+
1 row in set (0.00 sec)

mysql> RENAME TABLE STUDY.RENAME_TEST TO TEST.RENAME_TEST;
Query OK, 0 rows affected (0.00 sec)

mysql> select Host,User,DB,Table_name,Table_priv from tables_priv where User = 'restrict_user';
+------+---------------+-------+-------------+------------+
| Host | User | DB | Table_name | Table_priv |
+------+---------------+-------+-------------+------------+
| % | restrict_user | STUDY | RENAME_TEST | Select |
+------+---------------+-------+-------------+------------+
1 row in set (0.00 sec)

mysql>

rename_to_other_db

テーブルは別DBに移動していて、ファイル自体もフォルダーが移動している事を確認。
rename_test

RENAMEにて関わるその他の情報

複数テーブルQuery1つでまとめてリネーム


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A1 |
| T_SiteResponse |
| innodb_monitor |
+----------------+
3 rows in set (0.00 sec)

mysql> rename table A1 to A10,T_SiteResponse to T10;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A10 |
| T10 |
| innodb_monitor |
+----------------+
3 rows in set (0.00 sec)

rename

TEMPORARY TABLEをリネームするにはRENAME TABLE は使えない


mysql> CREATE TEMPORARY TABLE t1 (i INT);
ERROR 1050 (42S01): Table 't1' already exists
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE T1 (i INT);
Query OK, 0 rows affected (0.00 sec)

mysql> rename table T1 to T100;
ERROR 1017 (HY000): Can't find file: './test/T1.frm' (errno: 2)
mysql> ALTER TABLE T1 RENAME TO T100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

alter_rename1


MYSQLが稼動中に作成したtemporary tableの推移をグラフにして

モニタリングする方法。

① MYSQL ADMINISTRATORを開いてグラフエリアで「ADD A GROUP」として名前を付ける。

② 新しいグラフグループにて、「ADD A GRAPH」を選択。

③ lineグラフを選択して  ^[created_tmp_tables] をformulaに追加する。

以下の図のようにtemp tableが作成された様子がグラフになってモニタリング

出来るので、パフォーマンスの調整に利用出来ます。

tmp_tables_graph