MySQL5.6でクッソ遅くなるSQL実行しました
MySQL5.6.3から、FROM句サブクエリにおける一時テーブルで、インデックスが作成されると聞いたので、テストしてみた。
MySQL :: MySQL 5.6 Reference Manual :: 8.13.16.3 Optimizing Subqueries in the FROM Clause (Derived Tables)
比較対象はMySQL5.5.32とMySQL5.6.12です。
SELECT * FROM (SELECT * FROM test_tbl) y WHERE col3=3;
正直こんなSQL発行するなっていう文ですが。
対象テーブルtest_tblはこんなかんじ。
CREATE TABLE `test_tbl` ( `col1` int(11) NOT NULL AUTO_INCREMENT, `col2` varchar(10) DEFAULT NULL, `col3` int(11) DEFAULT NULL, `col4` int(11) DEFAULT NULL, PRIMARY KEY (`col1`), KEY `test_idx` (`col3`,`col2`) ) ENGINE=InnoDB AUTO_INCREMENT=5636010 DEFAULT CHARSET=utf8 INSERT INTO test_tbl(col2, col3, col4) values('abc',1,1); # 3,145,728 rows INSERT INTO test_tbl(col2, col3, col4) values('abc',2,1); # 2,097,152 rows INSERT INTO test_tbl(col2, col3, col4) values('abcde',2,1); # 4 rows INSERT INTO test_tbl(col2, col3, col4) values('abcde',3,1); # 10 rows
実行結果
| MySQL5.5.32 | MySQL5.6.12 | |
|---|---|---|
| SELECT * FROM (SELECT * FROM test_tbl) y WHERE col3=3; tmp_table_size=64M |
4.43 sec | 86.38 sec |
| SELECT * FROM (SELECT * FROM test_tbl) y WHERE col3=3; tmp_table_size=1G |
3.86 sec | 11.84 sec |
| SELECT * FROM test_tbl; tmp_table_size=64M |
4.42 sec | 4.44 sec |
MySQL5.6クッソ遅い(´・ω・`)
原因
まず、先述したとおり、MySQL5.6では一時テーブルにもインデックスが作成されます。
当然、その処理には対象となるインデックスのアクセスや再構築が必要で、
その分の処理時間やサイズ増加によるI/O負荷などがボトルネックかと思われます。
まだ具体的にソースまで追っかけてないので予想ですけども。
あとoptimizer_traceしたらコストがおかしい？
とりあえずLast_query_costは0でした。
こんなSQL最適化しないってか。
他に考えられる原因あれば教えて下さい！
とりあえずここまで。
