PHPカンファレンス2021にオンライン参加しました。

弊社からは、菱田が「抽象のはしごの上手な登り方〜使いやすい汎用ライブラリを作るために〜」という題で登壇しました :tada::clap:
動画アーカイブ：https://www.youtube.com/watch?v=xoYamWpmKDo&t=19079s
↑より「抽象のはしごの上り下りの練習にはアウトプットが良い:bulb:」ということで早速アウトプットしますー！

SQL苦手PHPerな私には「PHPer が知るべき MySQL クエリチューニング」のトークが印象的で勉強になりました！
動画アーカイブ：https://www.youtube.com/watch?v=y8AZV1HnieA&t=5876s

そこで今回はトーク内で「EXPLAINの見方」が紹介されていたので、実際に手を動かしてみたいと思います！

[準備]クエリーの見える化

今回は、Symfony + Doctrineのプロジェクトを使い、Doctrineのログのみを出力するdoctrine_only.logを設定していきます。
デフォルトのvar/log/dev.logに出力されるログには以下のように、REQUESやSECURIログも含まれるのでDOCTRIだけにしてみます。
（実際にクエリーチューニングする場合は、デフォルトのログの方が便利だと思います。）

var/log/dev.log

[Application] Oct  5 07:08:44 |INFO   | REQUES Matched route "app_home". request_uri="https://127.0.0.1:8000/" route="app_home" route_parameters={"_controller":"App\\Controller\\DefaultController::list","_route":"app_home"}
[Application] Oct  5 07:08:44 |DEBUG  | SECURI Checking for authenticator support. authenticators=1 firewall_name="main"
[Application] Oct  5 07:08:44 |DEBUG  | DOCTRI SELECT t0.id AS id_1, t0.user_name AS user_name_2, t0.full_name AS full_name_3, FROM user t0 WHERE t0.id = ? LIMIT 1 0=1

config/packages/dev/monolog.yaml

monolog:
    handlers:
        main:
            type: stream
            path: "%kernel.logs_dir%/%kernel.environment%.log"
            level: debug
            channels: ["!event"]
...
+       doctrine_only:
+           type: stream
+           path: "%kernel.logs_dir%/%kernel.environment%_doctrine_only.log"
+           level: debug
+           channels: ["doctrine"]

これで、var/log/dev_doctrine_only.logを出力できるようになりました:raised_hands:

参考：https://tech.quartetcom.co.jp/2018/05/31/monolog/

EXPLAINとは？

EXPLAIN は、クエリー実行計画 (つまり、MySQL がクエリーをどのように実行するかの説明) を取得するために使用されます。

引用：https://dev.mysql.com/doc/refman/8.0/ja/explain.html

EXPLAINの主な見方

以下のuserテーブル(全レコード数：1000)があり、(1)と(2)のEXPLAIN結果を元に比較してみます。

mysql> describe user;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name         | varchar(255) | NO   |     | NULL    |                |
| full_name         | varchar(255) | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show index from user; # 既存indexの確認
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select count(*) from user; # 全レコード数の確認
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

(1) WHERE句より、PKかつindexのカラム(=id)で絞り込むクエリー

mysql> EXPLAIN SELECT t0.id AS id_1, t0.user_name AS user_name_2, t0.full_name AS full_name_3 FROM user t0 WHERE t0.id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t0    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(2) WHERE句より、PKではないかつindexが貼られていないカラム(=full_name)で絞り込むクエリー

mysql> EXPLAIN SELECT t0.id AS id_1, t0.user_name AS user_name_2, t0.full_name AS full_name_3 FROM user t0 where t0.full_name = '志賀彩乃';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

type

  • ALL : フルスキャン、最も遅い。インデックスが全く使用されていない
  • index : インデックスでは対応しきれてない。対象が多い。
  • range : インデックスを使った範囲検索している。
  • const : PK/UKで、1行/0行を取得している。最高速。

key

オプティマイザー(←実行時間が最小になるように処理方法を決めてくれる)が選択したインデックス
→ 狙いと違うインデックスが使われていたら注意する。

rows

検索対象のレコード数
→ 想定より多い場合は、追加の検索条件の検討をする。

比較結果

  • (1)のクエリーはPKかつindexのカラム(id)で絞り込んでいるためにtype=const(最速),key=PRIMARY(インデックス使用),rows=1(対象レコード数を絞り込めている)となっており理想的。
  • 一方、(2)のクエリーはtype=ALL(フルスキャン),key=NULL(インデックス未使用),rows=1000(全レコードが検索対象)となり改善が見込めるクエリーとなりそう。

SQLの実行順序

SQLの実行順序はFROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMITなので、膨大な量のレコード検索の場合LIMITのみで絞り込んでも負荷が高くなる。
→ SELECTやWHEREなどで絞り込みが可能な場合は絞り込みを行う。

BeforeのようにLIMIT 10のみだと、type=ALL(フルスキャン),rows=1000(全レコードが検索対象)となっている。
一方、AfterのようにWHERE id <= 10を追加するとtype=range(インデックスを使った範囲検索),rows=10となり検索対象のレコード数が小さくなり負荷が低くなる。

Before

mysql>  EXPLAIN SELECT t0.id AS id_1, t0.user_name AS user_name_2, t0.full_name AS full_name_3 FROM user t0 LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t0    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

After

mysql> EXPLAIN SELECT t0.id AS id_1, t0.user_name AS user_name_2, t0.full_name AS full_name_3 FROM user t0 WHERE id <= 10 LIMIT 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t0    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

最後に

今回初めてEXPLAINを真面目に見ることができ、とっても勉強になりました！まみーさんありがとうございます:bow:
その他のトークも面白くて大変勉強になりました！登壇者のみなさん、実行委員のみなさん本当にありがとうございました！
まだ観れていないトークもあるのでアーカイブを漁りたいと思います:fish:
ペチコン全体のタイムテーブルはこちらです。ここからYoutubeでアーカイブも視聴できるので興味がある方は観てみてください。
https://fortee.jp/phpcon-2021/timetable