nikkie-ftnextの日記

イベントレポートや読書メモを発信

MySQLでINとサブクエリの組合せを多用してきたnikkieに激震走る。より効率がよいEXISTSと挫折結合を知りました（『Effective SQL』項目23）

はじめに

穴掘って埋まってますぅ nikkieです。

SQLの書き方について衝撃が走りました。
あんまり効率がよくない書き方をこれまで堂々としてしまっていたのです...

目次

『Effective SQL』

表紙には「RDBMSのパフォーマンスを最大限引き出す61の手法と思考」とあります。
ソースコードも公開されています。

項目23 条件と一致しないレコードや欠けているレコードを特定する

Dockerコンテナで動かす

Docker公式のmysqlイメージを使うことにしました。
https://hub.docker.com/_/mysql

  • 今回は書籍の写経用途なので、rootユーザを使い、パスワードだけ設定します
    • 環境変数MYSQL_ROOT_PASSWORD（docker run -eで指定）
  • /docker-entrypoint-initdb.dディレクトリに置いた、拡張子が.sqlのファイルを起動時に実行してくれる仕組みを利用します
    • docker run -vでマウントします
    • 実行順はファイル名のアルファベット順です1

以下のコマンドでmysqlのコンテナを準備できます！

% mkdir sql
% wget https://raw.githubusercontent.com/TexanInParis/Effective-SQL/master/MySQL/Sample%20Databases/SalesOrdersStructure.SQL -O sql/1-SalesOrdersStructure.sql
% wget https://raw.githubusercontent.com/TexanInParis/Effective-SQL/master/MySQL/Sample%20Databases/SalesOrdersData.SQL -O sql/2-SalesOrdersData.sql

% docker run --rm --name mysql-db -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 13306:3306 -v $PWD/sql:/docker-entrypoint-initdb.d -d mysql:8.0

接続コマンド
mysql -h 127.0.0.1 -P 13306 -uroot -p SalesOrdersSample
（パスワードはdocker run -eで指定しているmy-secret-pwです）

SalesOrdersSampleデータベース

いくつかのテーブルが作られ、データが投入されています。
項目23で扱うのは以下の2つのテーブルです

  • Products
  • Order_Details

項目23としては、Order_Detailsにない（＝注文されていない）Products（＝製品）を求めることを考えます。

こんな感じのデータが入っています。

mysql> select * from Products limit 1;
+---------------+-------------------------+--------------------+------------+-------------+----------------+------------+
| ProductNumber | ProductName             | ProductDescription | ProductUPD | RetailPrice | QuantityOnHand | CategoryID |
+---------------+-------------------------+--------------------+------------+-------------+----------------+------------+
|             1 | Trek 9000 Mountain Bike | NULL               | NULL       |     1200.00 |              6 |          2 |
+---------------+-------------------------+--------------------+------------+-------------+----------------+------------+
1 row in set (0.00 sec)

mysql> select * from Order_Details limit 1;
+-------------+---------------+-------------+-----------------+
| OrderNumber | ProductNumber | QuotedPrice | QuantityOrdered |
+-------------+---------------+-------------+-----------------+
|           1 |             1 |     1200.00 |               2 |
+-------------+---------------+-------------+-----------------+
1 row in set (0.01 sec)

これまでのnikkie：INとサブクエリ！

Order_DetailsにないProductsを求めるクエリでまず浮かんだのは以下でした2。

select
    p.ProductNumber,
    p.ProductName
from
    Products as p
where
    p.ProductNumber not in (
        select
            ProductNumber
        from
            Order_Details
    );

+---------------+--------------------------------+
| ProductNumber | ProductName                    |
+---------------+--------------------------------+
|             4 | Victoria Pro All Weather Tires |
|            23 | Ultra-Pro Skateboard           |
+---------------+--------------------------------+
2 rows in set (0.01 sec)

『Effective SQL』の評は、分かりやすいが効率があまりよくないというものです。

Order_Detailsテーブル全体のすべてのレコードにアクセスし、重複している値を振るい落とした上で、ProductsテーブルのProductNumber列の各値をこのリストと照合しなければならない (Kindle版 p.202)

今回の学び：より効率がよい2つのやり方

EXISTS

NOT INをNOT EXISTSで書き直します3。

select
    p.ProductNumber,
    p.ProductName
from
    Products as p
where
    not exists (
        select
            *
        from
            Order_Details as od
        where
            od.ProductNumber = p.ProductNumber
    );

これで結果は一致します。
EXISTS演算子は「サブクエリが少なくとも1行のデータを返すかどうかをチェックする (Kindle版 p.202)」ため、効率がよいという説明でした。
これで効率がよいという理解は非常にふんわりしています（私の伸びしろです）

挫折結合

LEFT JOINを使ったやり方です4。
JOINする右側のテーブルにない行だけに制限するから挫折（frustrated）とのことです（詳しくは書籍をどうぞ）

select
    p.ProductNumber,
    p.ProductName
from
    Products as p
    left join Order_Details od on p.ProductNumber = od.ProductNumber
where
    od.ProductNumber is null;

過去の私はこのように書くこともありましたが、分かりやすさからIN+サブクエリで書くことが多かったです

終わりに

『Effective SQL』項目23「条件と一致しないレコードや欠けているレコードを特定する」の学びをアウトプットしました。

  • これまで多用してきたIN+サブクエリ
    • わかりやすい！
    • 効率があまりよくないことをこのたび知った（ごめんなさい〜）
  • よりよい方法1：EXISTS
    • 今回始めて知った
  • よりよい方法2：挫折結合
    • LEFT JOINに使ったフィールドがnullの行を除く
    • これまでも書くことはあったが、第一の選択肢はIN+サブクエリだった

『Effective SQL』のコードをMySQLのDockerイメージで実行して動作確認しました！

IN+サブクエリはSQLの入門書で出会った記憶があります。
拾い読みだったのでよりよい方法に気づかなかったのかなと思います。
これまで多用してきましたが、今後はEXISTSや挫折結合がすっと出るように練習あるのみですね

Effective SQL

Effective SQL

Amazon

  1. Docker公式mysqlイメージのドキュメント中にありました。「Initializing a fresh instance」↩
  2. https://github.com/TexanInParis/Effective-SQL/blob/8047973838c2780da1795742793016faff36315c/MySQL/Chapter%2004/Listing%204.004.sql↩
  3. https://github.com/TexanInParis/Effective-SQL/blob/8047973838c2780da1795742793016faff36315c/MySQL/Chapter%2004/Listing%204.005.sql↩
  4. https://github.com/TexanInParis/Effective-SQL/blob/8047973838c2780da1795742793016faff36315c/MySQL/Chapter%2004/Listing%204.006.sql↩