i-plug Advent Calendar 2019 の【12日目】の記事です
4日目にスクラム開発の話をしました。
今日は趣を変えて、SQLの話です。
基礎的なことのようで、意外と知らない人も多いっぽいので、啓蒙していきます。
（想定はMySQL、バージョンは5.7です）
なにがゆるふわなのかは知りません。
前提：テーブル
-- 商品マスタ
create table m_goods (
ID int(11) auto_increment
, goods_name varchar(255) not null
, goods_category varchar(32) not null
, price int(11) not null
, primary key(ID)
, index(goods_category)
)
;
-- 会員マスタ
create table m_member (
ID int(11) auto_increment
, member_name varchar(32) not null
, prefecture varchar(32) not null
)
;
-- 受注トラン
create table `t_order` (
ID int(11) auto_increment
, goods_id int(11) not null -- m_goodsテーブルのID
, member_id int(11) not null -- m_memberテーブルのID
, order_date datetime not null
, primary key(ID)
)
;
※あくまで、説明用
Lesson１：とりあえず条件付けずに検索してみる
mysql> select * from m_goods;
53957 rows in set (4.13 sec)
mysql> select * from m_member;
6274 rows in set (0.28 sec)
結構、実行時間に差がありますね。
この差は、テーブルに入っているレコード数によるもの。
「そんなの、実行する前にはわからないよ！」というあなた。
実行するSQLの前にexplainを付けてみてください。
mysql> explain select * from m_goods;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | m_goods | NULL | ALL | NULL | NULL | NULL | NULL | 63369 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
mysql> explain select * from m_member;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | m_member | NULL | ALL | NULL | NULL | NULL | NULL | 5911 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
わちゃわちゃ出てきたー！
これは【実行計画】と言って、SQLを実行する際に、DBがどうやってデータを検索するかの手順のようなものになります。
なので、これを見ると、どの辺にボトルネックが出てきそうかが、見えてくる代物！
色々書いてますが、一旦、typeとrowsに注目。
typeはテーブルの検索の仕方で、ALLは「テーブルの全レコードにアクセスしている」＝一番遅くて負荷が高いことを、rowsは検索対象になるレコード数を示しています。
これを見ると、どっちの方がレコードが多いか一目瞭然！
selectの結果と差があるよ
するどい！
実は、実行計画は、リアルタイムの状況ではなく、定期的にDBが取得している、統計情報というものに従っています。
そのために、差が出てくるんですよねー。
（詳しいことは他の方の解説に任せます）
まとめ
- チューニングのためには、
explainで、SQLがどう実行されるか確認！ - 対象のレコード数で、実行時間が大きく変わる！
Lesson2：INDEXを使ってみよう
次は、カラムを指定した検索をしてみます。
mysql> select * from m_goods where goods_category = 1;
254 rows in set (0.05 sec)
は、はやい...！！
何が起こっているのか、実行計画を見てみましょうー
> explain select * from m_goods where goods_category = 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | m_goods | NULL | ref | goods_category | goods_category | 5 | const | 254 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
さっきと比べ、
- typeがrefになっている
- だいぶrowsが少ないですね！
ということが見えるかと思います。
WHERE句で指定したgoods_categoryカラムにINDEXが付与されていて、検索対象のレコードを限定出来ていることを示しています。
次は、指定する値を、変えてみましょう。
mysql> select * from m_goods where goods_category = 999;
35522 rows in set (1.96 sec)
あれ？実行時間が増えた...
こんなときは実行計画！
mysql> explain select * from m_goods where goods_category = 999;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | m_goods | NULL | ref | goods_category | goods_category | 5 | const | 31684 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+-------+----------+-------+
rowsが増えてます。
INDEXには、goods_category = 1と、goods_category = 999のレコードがそれぞれ記録されていて、指定されたSQLによって、それぞれ該当のレコードを１行ずつ精査しているんですね。
だから、INDEXが効いているレコードを使っても、条件により実行時間に差が出ると。
ちなみに、カラムが取りうる値の種類数をカーディナリティと言います。
都道府県だと４７、血液型だと4、〜フラグだと２になります。
一般的に、カーディナリティが大きいほどINDEXの効果が高いと言われています。
１つの値に該当するレコード件数が少なくなるからですね。
でもあくまで、「１つの値に該当するレコード件数が少なくなるから」なので、カーディナリティが２でも、極端に片方に値が寄っているような状態で、レコード件数が少ない方を検索する場合は有効ですよ！
最後に、goods_categoryが999以外のものを取得するSQLを見てみましょうー
mysql> explain select * from m_goods where goods_category != 999;
+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | m_goods | NULL | ALL | goods_category | NULL | NULL | NULL | 63369 | 50.00 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+-------+----------+-------------+
おっと、typeがALLになってる！？
否定形だと、INDEXは有効でないんですねー
まとめ
- INDEXが付与されたカラムを指定すると、その条件に該当するレコードだけが対象になる
- カーディナリティが高いものを指定して上げると効果が高くなりやすい
- 否定形だと、INDEXは使われない
Lesson3：テーブルを結合してみる
mysql> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> ;
何分待っても、返ってこない・・・
実行計画チェック！！
mysql> explain
-> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | m | NULL | ALL | NULL | NULL | NULL | NULL | 5770 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 83981 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
２行出てきましたね。
これは２つのテーブルを利用していることを示しています。
着目するのは、rowsとExtra。
ExtraでBlock Nested Loopと言ってますね。
ふむふむ。つまり、m_memberの5770行の１行毎に、t_orderの83981行と結合している、ということになります。
つまり、対象は5770*83981=484570370行。。。
終わるわけないね！（こんなの本番環境で実行したら、トラブルの元です。気をつけましょうｗ）
ちゃんとINDEXが付与されているカラムを結合条件に指定しましょうね。ということなのですが、m_memberには、まともにINDEXが指定されていないので、今回は、Primary Keyを指定しちゃいます。
（実行時、テーブルロックがかかるので、本番環境へ実行するときは気をつけましょう）
mysql> alter table m_member add primary key(ID);
Query OK, 0 rows affected (1.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
はい、INDEX付与できた。
もう１回確認。
mysql> explain
-> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 83981 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | adventcalendar.o.member_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------------+-------+----------+-------+
INDEX参照するようになって、rowsも減りましたね。
実行してみましょう。
mysql> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> ;
100834 rows in set (11.57 sec)
んー。まだ時間かかりますが、一旦現実的な時間で結果を取得できました。
まとめ
- １つのSQLで複数テーブルを使うと、実行計画は複数行になる
- そのときの対象レコードは、rowsの掛け算
- 適切な結合条件を指定しないと、パフォーマンス劣化しやすいので、注意しましょう
Lesson4：内部結合とサブクエリ
そういえば、よくMySQLは、サブクエリが重いから結合使おうね、という話を聞きます。
本当？
確認してみましょう。
mysql> explain
-> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> and m.prefecture = '大阪府'
-> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 83981 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | adventcalendar.o.member_id | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
mysql> explain
-> select
-> *
-> from t_order o
-> where o.member_id in (
-> select c.id
-> from m_member m
-> where m.prefecture = '大阪府'
-> )
-> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 83981 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | adventcalendar.o.member_id | 1 | 10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
実行計画は変わらないですね。
実際に実行してみましょう。
mysql> select
-> *
-> from t_order o
-> inner join m_member m
-> on m.id = o.member_id
-> and m.prefecture = '大阪府'
-> ;
24294 rows in set (1.57 sec)
mysql> select
-> *
-> from t_order o
-> where o.member_id in (
-> select c.id
-> from m_member m
-> where m.prefecture = '大阪府'
-> )
-> ;
24294 rows in set (0.42 sec)
おぉ。サブクエリの方が早い！
まぁ、m_memberの件数が少ないから、結合のコストよりin句に含めるコストの方が少なかっただけ、という話かな。
でも、少なくとも、MySQLにおいて、結合>サブクエリという定説は覆されたのです！！
（5.1とか、古いバージョンではわかりません）
まとめ
- 一概に、結合がサブクエリより優れている、ということはない
- 利用するテーブル次第でどっちが早いかは変わる
- ２つ目のテーブルのカラムを結果に含めたい→結合、条件だけに使いたい→サブクエリ、みたいに使い分けた方が、可読性は高いかも
Lesson５：内部結合と外部結合
そういや、基本left joinにして、inner joinしない人、結構いますよね。
その辺も明らかにしましょうか。
mysql> explain
-> select
-> *
-> from t_order o
-> left join m_member m
-> on m.id = o.member_id
-> where m.prefecture = '大阪府'
-> ;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 83981 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | eq_ref | PRIMARY | PRIMARY | 4 | adventcalendar.o.member_id | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+-------+----------+-------------+
実行計画はかわらず。
実際に実行してみましょう。
mysql> select
-> *
-> from t_order o
-> left join m_member m
-> on m.id = o.member_id
-> where m.prefecture = '大阪府'
-> ;
24294 rows in set (2.24 sec)
結構、時間変わった！
まとめ
- 同じ結果を求めるなら、外部結合と内部結合使い分けた方が、パフォーマンスも可読性もいい感じ
最後に
ぶっちゃけ、ここで説明したことはデータ量や、他のINDEXなどにより変わってきます。
（バージョン辺りでも違いでるかも）
なので、大筋は参考にしてもらいつつ、実際には、実行計画と実行結果を見て、修正していく必要があると思います。
あと、細かい理屈、もっと詳細なパフォーマンス調査は省いています。この辺がゆるふわですね。
興味のある人は、RDBMSのアーキテクチャやSHOW PROFILEとか調べてみてください。
ちなみに
この記事はi-plug Advent Calendar 2019 の【12日目】の記事です。
今は、13日です。
AdventCalendarもスクラムもSQLも、計画と、実践結果を受けての適応が大事ですね。