👏

データベースにおけるオプティマイザー（optimizer）とは

2023/12/27に公開

SQL

optimizer

tech

■ まず optimize とは

「最適化する」という意味

つまり、optimizer は

「最適化してくれるやつ」ということになる。

■ IT における optimizer とは

効率よくしてくれる機能・ソフトウェアのこと。
ここでいう「効率よく」とは、

速く: 処理速度の向上
楽に: メモリの節約など
を表す。

「効率よくする」ために、対象の設定や構造などを調整してくれるもの。

https://wa3.i-3-i.info/word1553.html

■ データベースにおける optimizer とは

効率よく、「最適化」されたクエリの実行計画を決めて実行してくれるもの。

つまり、SQL文を元に最も効率よく最適なデータ取得処理を生成し、実行してくれるもの。

よい実行計画と悪い実行計画を瞬時に判断して実行してくれる

※ optimizer も万能ではない。
そのため、意図したように最も高速な実行計画が常に作成される訳では無い。

◎ SQL文でデータ取得処理が決定するわけではない

SQL文はどのテーブルからデータを取得するかは記述されているものの、どのように取得するかは記述されていない。

SELECT * FROM companies WHERE id = 1

上記SQL文の場合、下記のようにいくつかの方法で取得処理が考えられる。実際にどのように取得するかという実行計画は optimizer が決定している。

フルテーブルスキャン: テーブルのすべての行を一行ずつ検査して、条件に合致する行を見つける
- どんな条件で実行される？: 特にテーブルが小さいか、適切なインデックスが存在しない場合に使用される
インデックスを使用した検索: id カラムにインデックスが存在する場合、オプティマイザはこのインデックスを使用して効率的に行を検索する
- どんな条件で実行される？: id がプライマリーキーである、またはユニークインデックスがある場合、または通常のインデックスが存在する場合に使用される
インデックスのみを使用した検索（カバリングインデックス）: クエリで要求されているすべてのデータがインデックス内に存在する場合、テーブルのデータを直接参照することなく、インデックスだけを使用してクエリを解決する
- どんな条件で実行される？: SELECT 文がインデックスに含まれるカラムのみを要求し、それらのカラムがインデックスに含まれている場合に使用される
インデックスレンジスキャン: インデックスが範囲条件（例えば id > 1）に適用される場合、オプティマイザはインデックスレンジスキャンを使用することがある
- どんな条件で実行される？: ※ 今回のクエリでは適用されないものの、WHERE 句に範囲条件が含まれるクエリの場合に適用される

実行計画を EXPLAIN コマンドで確認する

explain select * from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4047 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

今回実行した SQL では、type=ALLとなっており、フルテーブルスキャンで実行されている。

※ 取得処理でなく、書き込み処理時の働きは大きくないらしい

INSERT, UPDATE, DELETE 時にも optimizer は役割を果たすものの、その効果は大きくない。

なぜなら、書き込み操作の最適化は、読み取り操作（SELECT文）の効用に比べると一般に単純だからである。
これは、書き込み操作が通常は特定の行または行群に対して直接行われ、複雑な結合やサブクエリが少ないためである。

◎ optimizer が実行計画策定に利用している統計情報

optimizer は次のような統計情報をもとにSQLから実行計画を作成する。

テーブルの（およその）行数・列数
各列の列長とデータ型
テーブルのサイズ
列に対する主キーや NOT NULL 制約の情報
列の値の分散や偏り

この統計情報は、コマンドで確認できる。

show table status;
# 表示結果は一部省略している
+-----------------+------+----------------+-------------+--------------+---------------------+---------------------+
| Name            | Rows | Avg_row_length | Data_length | Index_length | Create_time         | Update_time         |
+-----------------+------+----------------+-------------+--------------+---------------------+---------------------+
| city            | 4047 |            101 |      409600 |       114688 | 2023-12-30 09:42:31 | 2023-12-30 10:42:56 |
| country         |  239 |            479 |      114688 |            0 | 2023-12-30 09:42:32 | 2023-12-30 09:42:32 |
| countrylanguage |  984 |             99 |       98304 |        65536 | 2023-12-30 09:42:32 | 2023-12-30 09:42:32 |
+-----------------+------+----------------+-------------+--------------+---------------------+---------------------+

# show index from テーブル名;
show index from city;
# 表示結果は一部省略している
+-------+------------+-------------+--------------+-------------+-----------+-------------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+-------+------------+-------------+--------------+-------------+-----------+-------------+------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4046 | BTREE      |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 | BTREE      |
+-------+------------+-------------+--------------+-------------+-----------+-------------+------------+

◎ SQL分を受け取ってから処理が実行されるまでのフロー

optimizer は2. 実行計画作成で統計情報を利用している。