Select tables optimized away との出会い

ふと私が EXPLAIN をうったとき Extra の項目にこんなものが

「Select tables optimized away」

なんだこれはとなったので調べてみる。

クエリーにはすべてインデックスを使用して解決された集約関数 (MIN()、MAX())、または COUNT(*) のみが含まれていますが、GROUP BY 句は含まれていませんでした。オプティマイザは 1 行のみを返すべきであると判断しました。

なるほど。（お前は何をいってるんだ）
ちなみにわかりやすい英語版のマニュアルはこちらから。

公式の日本語がやばすぎてアレですが、takatoshionoさんがブログで詳しく解説されています。
MySQL の Select tables optimized away とは何か？

なるほどです。
つまりテーブルを読みに行く必要もない。つまりは高速に返せる。

でもまてよ？　レコード数が増えても「Select tables optimized away」は本当に維持されるのか？
レコード数が増えても高速に返ってくるのか？
というのを実験をしたくなりました。

理論上は「Select tables optimized away」が返ってくるクエリならばレコード数が異常に多いテーブルであっても即座に返ってくるはずです。
というわけで、適当なテーブルと1億件のレコードを作り実験してみることにしました。

早速データを作ってみる

クソ適当に PHP で書きました。
max_allowed_packet がそのまま書かれていたり色々とアレですがお許し下さい！

とりあえずテーブル構成の意図としては WHERE 句で groupid を指定し、
SELECT で MAX(id) を得たいとき Select tables optimized away で弾ける構成を考えました。
この後軽く記載しますが、弾けないテーブルも混ぜています。

create.php

<?php

if(!defined("MYSQL_USER"))   define("MYSQL_USER", "root");
if(!defined("MYSQL_PASS"))   define("MYSQL_PASS", "xxxx");
if(!defined("MYSQL_CLIENT")) define("MYSQL_CLIENT", "mysql");
if(!defined("MYSQL_DB"))     define("MYSQL_DB", "db_test");

function execute_query($query)
{
    exec("MYSQL_PWD=".MYSQL_PASS." ".MYSQL_CLIENT." -u".MYSQL_USER." -N ".MYSQL_DB." -e \"{$query}\"");
}
function execute_sql_file($file)
{
    exec("MYSQL_PWD=".MYSQL_PASS." ".MYSQL_CLIENT." -u".MYSQL_USER." -N ".MYSQL_DB." < " . $file);
}

function insert_query()
{
    for ($g = 1; $g <= 100; ++$g)
    {
        $query = "INSERT INTO tbl_test_1 (id, group_id, value) VALUES";
        for ($i = 0, $count = 1000000; $i < $count; ++$i)
        {
            if ($i !== 0) $query .= ",";
            $query .= "(0, $g, " . ($g * $count + $i) . ")";
        }
        $query .= ";";
        file_put_contents("insert.sql",$query);
        unset($query);
        execute_sql_file("insert.sql");
        echo "write {$g}\n";
    }
}

execute_query("set global max_allowed_packet = 64000000;");

execute_query("
    CREATE TABLE IF NOT EXISTS tbl_test_1(
        id       INTEGER NOT NULL AUTO_INCREMENT,
        group_id INTEGER NOT NULL DEFAULT 0,
        value    INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY( id ),
        INDEX( group_id )
    )ENGINE=InnoDB;
");


execute_query("
    CREATE TABLE IF NOT EXISTS tbl_test_2(
        id       INTEGER NOT NULL AUTO_INCREMENT,
        group_id INTEGER NOT NULL DEFAULT 0,
        value    INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY( id, group_id ),
        INDEX( group_id )
    )ENGINE=InnoDB;"
);

execute_query("
    CREATE TABLE IF NOT EXISTS tbl_test_3(
        id       INTEGER NOT NULL,
        group_id INTEGER NOT NULL DEFAULT 0,
        value    INTEGER NOT NULL DEFAULT 0,
        INDEX    idx_group_id_id( group_id, id )
    )ENGINE=InnoDB;"
);

execute_query("
    CREATE TABLE IF NOT EXISTS tbl_test_4(
        id       INTEGER NOT NULL,
        group_id INTEGER NOT NULL DEFAULT 0,
        value    INTEGER NOT NULL DEFAULT 0,
        INDEX( group_id )
    )ENGINE=InnoDB PARTITION BY RANGE( group_id ) (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20),
        PARTITION p2 VALUES LESS THAN (30),
        PARTITION p3 VALUES LESS THAN (40),
        PARTITION p4 VALUES LESS THAN (50),
        PARTITION p5 VALUES LESS THAN (60),
        PARTITION p6 VALUES LESS THAN (70),
        PARTITION p7 VALUES LESS THAN (80),
        PARTITION p8 VALUES LESS THAN (90),
        PARTITION p9 VALUES LESS THAN MAXVALUE
    );"
);

insert_query();

echo "tbl_test_1 => tbl_test_2\n";
execute_query("INSERT INTO tbl_test_2 SELECT id,group_id,value FROM tbl_test_1");
echo "tbl_test_1 => tbl_test_3\n";
execute_query("INSERT INTO tbl_test_3 SELECT id,group_id,value FROM tbl_test_1");
echo "tbl_test_1 => tbl_test_4\n";
execute_query("INSERT INTO tbl_test_4 SELECT id,group_id,value FROM tbl_test_1");

my.cntも晒す

データ作成時のmy.cnf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character-set-server=utf8

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innodb_buffer_pool_size=3G
innodb_file_per_table
query_cache_type=0
innodb_log_file_size=512M

[mysql]
default-character-set=utf8

データ作成後のmy.cnf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character-set-server=utf8

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#innodb_buffer_pool_size=3G
innodb_file_per_table
query_cache_type=0
#innodb_log_file_size=512M

[mysql]
default-character-set=utf8

ちなみに実験を行ったPCの環境ですが、
Virtualbox 上に CentOS 7.4 (1708) をインストールして行っています。
CPUやメモリの割当は以下の通り。


OS	CentOS 7.4 (1708)
CPU	Core™ i5-6600 (3.90GHz)
コア割当	2
メモリ割り当て	6GB
ディスク割当	64GB (HDD)
MySQL	5.7.19
PHP	7.1.10

ときはきた

では早速試してみましょう。
……とその前に先程軽く触れましたが「Select tables optimized away」にならない構成がこの中にすでにあります。
はい。「tbl_test_4」ですね。こいつは後ほど速度を見るときの参考として使うことにします。

とりあえずレコード数が少ない状態で試してみたのがこちら

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_1 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_2 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_3 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_4 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl_test_4	p1	ref	group_id	group_id	4	const	100	100.00	NULL

ではレコード数が1億件はいっているテーブルにたいしてやってみましょう

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_1 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_2 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_3 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

mysql> EXPLAIN SELECT MAX(id) FROM tbl_test_4 WHERE group_id = 10;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl_test_4	p1	ref	group_id	group_id	4	const	2036942	100.00	NULL

では実際にEXPLAINを外して実行してみましょう

mysql> SELECT SQL_NO_CACHE MAX(id) FROM tbl_test_1 WHERE group_id=10\G

*************************** 1. row ***************************
MAX(id): 10000000
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE MAX(id) FROM tbl_test_2 WHERE group_id=10\G

*************************** 1. row ***************************
MAX(id): 10000000
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE MAX(id) FROM tbl_test_3 WHERE group_id=10\G

*************************** 1. row ***************************
MAX(id): 10000000
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE MAX(id) FROM tbl_test_4 WHERE group_id=10\G

*************************** 1. row ***************************
MAX(id): 10000000
1 row in set (2.41 sec)

なるほどね。はやい（確信）
tbl_test_4 と比較すると圧倒的じゃないか……

ちなみにですが InnoDB では COUNT はテーブルスキャン扱いになるので MAX などが Select tables optimized away だからといって COUNT が Select tables optimized away になったりはしないのでご注意ください。
Select tables optimized away がゲシュタルト崩壊しそう

話がそれますが、COUNT を高速化したい場合は kamipo さんが書かれている MIN と MAX を上手いこと使って算出する方法が有効です
MySQL(InnoDB)でCOUNTしたくないとき

ただしこの方法では問題があって、AUTO_INCREMENT であってもトランザクションをはってロールバックした場合、id はインクリメントされたままになるので正確な件数はとれません。
そのようなケースを考慮しなければならない場合は、COUNT をおとなしく使うか、別の方法を考えるかしないと駄目です。

文章だけではわかりにくいのでテストした結果がこちら。

mysql> CREATE TABLE inc_test ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) );

mysql> INSERT INTO inc_test () VALUES ();

mysql> SELECT * FROM inc_test;

id
1

mysql> BEGIN;
mysql> INSERT INTO inc_test () VALUES ();
mysql> ROLLBACK;

mysql> INSERT INTO inc_test () VALUES ();

mysql> SELECT * FROM inc_test;

id
1
3

となります。

MySQL の Select tables optimized away について気になったので遊んでみた

Select tables optimized away との出会い

早速データを作ってみる

my.cntも晒す

データ作成時のmy.cnf

データ作成後のmy.cnf

ときはきた