DBMSと実行計画

はじめに

最近ISUCONに参加するべく、いろんな高速化技術について勉強しています。
今回は実行計画ついて学んだので、簡単ですが紹介したいと思います。

インデックスについてはSQLチューニングとインデックスにまとめました

結論

• 負荷の高いSQLが発生したときに最初に調べるべき対象は実行計画(データのアクセス方法)
• 実行計画をみて、どういったデータのアクセス方法なのかを確認してSQLを改善する
  • 狙い通りインデックスが使われているか
  • どれくらいの行を読み込んでいるか　など

DBMSのクエリ処理の流れ

引用:クエリ評価エンジンと実行計画―“シェフおまかせ”はいつも美味しいのか（1）

• パーサ
  • 構文解析
  • SQLが構文的に正しいかの整合性チェック
• オプティマイザ
  • 「最適」なデータアクセスの方法（実行計画）が決定する
  • インデックス有無、データの分散や偏りの度合い、DBMSの内部パラメータなどの条件を考慮して、選択可能な多くの実行計画を作成を利用するかどうかもオプティマイザが決定する
  • 実行計画を作成する際に、④のカタログマネージャーを参照する
  • ②でいくつかの実行計画を作成して、③で一番低コストな実行計画を選択する
• カタログマネージャ
  • DBMSの内部情報を集めたもの、テーブル、インデックスの統計情報が格納
• プラン評価
  • オプティマイザが1つの実行計画に絞り込んだら、それを受け取って実行
  • 実行計画は人が読める形になっている

注意事項

• テーブルに対してデータの挿入・更新・削除が行われたのにカタログ情報が更新されていないと、オプティマイザは古い情報をもとに実行計画を作ろうとして、最適なプランが選ばれないことがある
• て０ブルのデータが大きく更新されたらカタログの統計情報もセットで更新するべき
• 統計情報が最新でも、SQLが複雑すぎて、構成がよくなくて、オプティマイザが最適なアクセスパスを生成できないこともあります。SQLの遅延が発生したとき、最初に調べるべき対象は実行計画

実行計画の見方

• 実行計画とは
  • explain を使うことで該当SQLがインデックスを使うのかや、どうやってテーブルスキャンしているかなどがわかる

題材

private-isuにでてきた遅いSQLの実行計画をみてみました。

遅いsqlをexplainしてみた

8系を使用

$ mysql --version
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on aarch64 ((Ubuntu))

commentsテーブルが10万行近く

mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'isuconp';
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| comments   |      99633 |
| posts      |       9396 |
| users      |       1000 |
+------------+------------+

テーブル定義

mysql> desc comments;
+------------+-----------+------+-----+-------------------+-------------------+
| Field      | Type      | Null | Key | Default           | Extra             |
+------------+-----------+------+-----+-------------------+-------------------+
| id         | int       | NO   | PRI | NULL              | auto_increment    |
| post_id    | int       | NO   |     | NULL              |                   |
| user_id    | int       | NO   |     | NULL              |                   |
| comment    | text      | NO   |     | NULL              |                   |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-----------+------+-----+-------------------+-------------------+

インデックスは自動生成される主キーのみ

mysql> show index from comments\G;
*************************** 1. row ***************************
        Table: comments
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 99633
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

explain実行

スロークエリログから性能が悪いSQLを見つけ出します。今回は下記SQLが対象になります。explainを使用して見つけ出さした性能の悪いSQLの実行計画をみていく。

mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ALL     
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99633
     filtered: 10.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

Mysql8.0 explain output
MySQLのEXPLAINを徹底解説!!

• type: ALL
  • フルスキャン
• rows: 99633
  • フルスキャンなのでテーブルの全行を読み取る
• key: NULL
  • インデックス利用なし
• Extra: Using where
  • WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示
• Extra: Using filesort
  • ソート処理が実行されている。ソート処理重い処理なので、基本的にはUsing filesortは出力させないほうがよさそう

10万行近いテーブルをフルスキャンしているので、SQLの改善が必要だということがわかる

インデックス追加後のexplain

• SLQ自体はシンプルなので問題なし。commentsテーブルには主キー以外のインデックスがなかったので、インデックスによる効果は大きそう。

• where条件のpost_idにインデックス追加

  • post_idのカーディナリティは問題ない
  • カーディナリティについてまとめてみた

mysql> alter table comments add index index_comments_on_post_id(post_id);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

インデックス追加後のexplain

mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ref
possible_keys: index_comments_on_post_id
          key: index_comments_on_post_id
      key_len: 4
          ref: const
         rows: 9
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

• type: ref
  • インデックスを使った検索
• possible_keys
  • 使えるインデックスの候補
• key
  • 使ったインデックス
• rows
  • 9行の読み取り
• Extra: Using filesort
  • ソート処理が実行されている

狙った通りにインデックスを利用できていて、読み取りも9行のみにとどまり改善できているように見える。

ただ、Using filesortが出力されているので、まだ改善の余地がありそう。
ソート処理は重い処理なので、ソートを使わないようにすると高速化できそう。

マルチカラムインデックス作成してみる

Extra: Backward index scanを避けるためdescを指定

mysql> alter table comments add index index_comments_on_multi_post_id_created_at(post_id, created_at desc);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT * FROM `comments` WHERE `post_id` = '9993' ORDER BY `created_at` DESC LIMIT 3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comments
   partitions: NULL
         type: ref
possible_keys: index_comments_on_multi_post_id_created_at
          key: index_comments_on_multi_post_id_created_at
      key_len: 4
          ref: const
         rows: 9
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

• フルスキャンなし、ファイルソートなし。狙った通りにマルチカラムインデックスを利用できている
• Extra: NULL　問題になりそうな指摘もなし

まとめ

• まず、スロークエリから遅いSQLを見つけ出します。次に実行計画を見てデータアクセス方法を確認します。最後にSQLをチューニング(今回はインデックス利用)します。
• 実行計画をみて遅いSQLを高速にすることができました