データベースに関する処理の中で、SQLでパフォーマンスを上げるためにヒント句を記述するという方法を聞いたことがある方も多いと思います。
今回は、SQLのヒント句とはどういったものなのかといった基本情報から、MySQLで利用するヒント句の使い方についてご紹介していきたいと思います。
SQLのヒント句って何？
SQLのヒント句は、データベースが実行計画を作成する際、意図しない実行計画が作成されないよう、ユーザーがオプティマイザを導くために利用されるものです。
オプティマイザはデータベース毎に搭載されており、ある程度的確な実行計画をユーザーが指定しなくても作成してくれます。
しかし、オプティマイザが常にユーザーの意図した実行計画を作成出来るとは限らないため、ヒント句を用いることで実行計画の作成を補助することが可能となります。
MySQLでのヒント句利用方法とは？！
SQLのオプティマイザといえば、Oracleデータベースがサンプルとして挙げられることが多いようですが、MySQLでもヒント句を使ってオプティマイザの実行計画作成を補助することが可能です。
上述したように、オプティマイザはデータベース毎に異なるシステムが搭載されているため、記述方法は各データベースにより異なります。
MySQLでのヒント句の記述方法
MySQLではヒント句を「インデックスヒント」とも呼びますが、主な記述方法として「USE」「IGNORE」「FORCE」の3種類が存在します。
USE
USEは名前の通り、指定したインデックスを使用するようにオプティマイザに推奨する方法です。
ただし、オプティマイザがインデックスを使用するよりもフルスキャンした方が早いと判断した場合には、USEで指定したインデックスを使用せず、フルスキャンによる処理が実行される可能性もあります。
FORCE
FORCEは上述したUSEと同じく、指定したインデックスを使用するようにオプティマイザに推奨する方法ですが、より強制力の強い指定方法となります。
オプティマイザがインデックスを使用するよりもフルスキャンの方が早いと判断した場合でも、FORCEを指定した場合にはインデックスを利用した処理が実行されます。
つまり場合によっては、インデックスヒントを指定しない場合よりも処理が遅くなってしまう可能性があるということです。
IGNORE
IGNOREでは、指定したインデックスをオプティマイザに使用しないよう推奨する方法です。
明らかに処理が遅くなるインデックスを指定しておくことで、オプティマイザが採用する可能性を除外することが可能となります。
SQL構文
SQLでインデックスヒントを利用するにはFROM句の後に記述します。
SELECT カラム名[, カラム名, ...] FROM テーブル名 USE[or INGORE or FORCE] [FOR JOIN or ORDER BY or GROUP BY] (インデックス名) [WHERE 条件式];
FORを指定しなかった場合には、JOIN・ORDER BY・GROUP BY全てに対してインデックスが適用されます。
SQLのヒント句を実際に使ってみよう
ではSQLのヒント句を実際に使ってみましょう。
今回は下記コマンドでサンプル用のテーブルを用意しました。
CREATE TABLE test1
(
id INTEGER PRIMARY KEY
,name VARCHAR(30)
,price BIGINT
,category VARCHAR(20)
);
インデックスは下記のコマンドで設定しています。
CREATE INDEX index_name ON test1 (name); CREATE INDEX index_category ON test1 (category); CREATE INDEX index_price ON test1 (name, price);
サンプルとしてデータを下記のように登録しました。
INSERT INTO test1 VALUES (1,'商品1',10000,'カテゴリー1') ,(2,'商品2',30000,'カテゴリー1') ,(3,'商品3',20000,'カテゴリー2') ,(4,'商品4',60000,'カテゴリー2') ,(5,'商品5',50000,'カテゴリー3') ,(6,'商品6',4000,'カテゴリー3') ,(7,'商品7',5000,'カテゴリー3') ,(8,'商品8',6000,'カテゴリー3');
サンプルテーブルとデータはヒント句の動きを確認するためだけのもので、簡易的に作成しています。
実行計画を確認してみよう
実行計画を確認するには「EXPLAIN句」を利用します。
EXPLAIN SELECT * FROM test1 WHERE name = '商品4' AND price > 50000;
実行した結果が下記の通りです。
+----+-------------+-------+------------+------+------------------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ref | index_name,index_price | index_name | 123 | const | 1 | 33.33 | Using where | +----+-------------+-------+------------+------+------------------------+------------+---------+-------+------+----------+-------------+
ヒント句で指定しない場合、実行計画では利用可能なインデックスに「index_name」「index_price」が表示され、実際に利用されたのは「index_name」だったことが確認出来ます。
USE INDEX
EXPLAIN SELECT * FROM test1 USE INDEX (index_price) WHERE name = '商品4' AND price > 50000;
実行した結果が下記の通りです。
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test1 | NULL | range | index_price | index_price | 132 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
USE INDEXで「index_price」を指定していますので、利用可能なインデックス・利用されたインデックス共に「index_price」が表示されています。
FORCE INDEX
EXPLAIN SELECT * FROM test1 FORCE INDEX (index_name) WHERE name = '商品4' AND price > 50000;
実行した結果が下記の通りです。
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ref | index_name | index_name | 123 | const | 1 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
今回はFORCE INDEXで「index_name」を指定していますので、利用可能なインデックス・利用されたインデックス共に「index_name」が表示されています。
IGNORE INDEX
EXPLAIN SELECT * FROM test1 IGNORE INDEX (index_name) WHERE name = '商品4' AND price > 50000;
実行した結果が下記の通りです。
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | test1 | NULL | range | index_price | index_price | 132 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
IGNORE INDEXで「index_name」を指定していますので、オプティマイザが「index_name」以外で利用可能と判断した「index_price」を表示していることが確認出来ます。
さいごに：SQLのヒント句を活用してパフォーマンスを向上させよう！
本記事では、SQLのヒント句の使い方について、MySQLをサンプルとしてご紹介してきました。
今回のサンプルは少量のデータしか投入していないため、インデックスを変更したところで処理速度はほとんど変わりません。
しかし大容量のデータベース処理となるほど、適切なインデックスが利用されているかにより大きく処理速度が変わりますので、日頃から実行計画を確認し、意図したインデックスが利用されているか確認しておく癖を付けておくことは大切です。
ヒント句の記述方法はデータベース毎に異なります。
ご利用のデータベースに合わせて適宜読み替えをお願いします。