MySQLでORDER BYをつけないときの並び順

メリークリスマス！🎅🎄

このエントリはMySQL Casual Advent Calendar 2016の24日目です。

今日はこれの話です！

@eagletmt 実装と実行計画依存です(たとえばInnoDBで単一カラムのインデックスが使われた場合のsort orderはprimary keyになるはずです)

— Ryuta Kamizono (@kamipo) December 4, 2016

@eagletmt すいません、すこし間違いがありました。もし hoge_id = ? のような絞り込みで単一カラムのインデックスが採用された場合はsort orderはprimary keyになるはずです。InnoDB前提なら基本的に実行計画依存です。

— Ryuta Kamizono (@kamipo) December 4, 2016

@eagletmt MySQLでorder by無しのときの結果セットの並び順はその実装とその実行計画で自然に取り出せる順になるので、たとえば単一カラムインデックスでも hoge_id IN (?,?) や OR を含むクエリだとprimary key順にならなくなります。

— Ryuta Kamizono (@kamipo) December 4, 2016

並び順が実装と実行計画に依存するとはどういうことか

MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。なのでORDER BYがついてないときMySQLはとくに何もせず自然に返せる順序で結果セットを返します。MySQLはストレージエンジンがプラガブルになっていてストレージエンジン毎にデータ構造や実装が異なるので自然に返せる順序もストレージエンジン毎に異なりうる。それを確かめるために、InnoDBとMyISAMでORDER BYをつけないときの並び順がどう違うのか見てみましょう。

CREATE TABLE myisam_casual_2016 (
 date date PRIMARY KEY,
 author varchar(250) NOT NULL,
 title varchar(250) NOT NULL,
 INDEX idx_author (author)
) ENGINE=MyISAM CHARSET=utf8mb4;

CREATE TABLE innodb_casual_2016 (
 date date PRIMARY KEY,
 author varchar(250) NOT NULL,
 title varchar(250) NOT NULL,
 INDEX idx_author (author)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO myisam_casual_2016 VALUES
 ('2016-12-24','kamipo','MySQLでORDER BYをつけないときの並び順'),
 ('2016-12-23','yoku0825','COUNTを速くする（？）SQL1本ノック その2'),
 ('2016-12-22','i_rethi','MySQL5.6と5.7のちょっとした違いとinnodb_thread_concurrencyの影響'),
 ('2016-12-21','atsuizo','お兄さんとの約束は守ろう！（tx_isolationとbinlogの話）'),
 ('2016-12-20','ogataka50','MySQL WorkbenchのVisual Explainでのみ出てくるaccess typeの話'),
 ('2016-12-19','yoku0825','COUNTを速くする（？）SQL1本ノック その1'),
 ('2016-12-18','atsuizo','MySQLのChar型のデータサイズについて小ネタ'),
 ('2016-12-17','yut148','upscaledb-mysqlを試す'),
 ('2016-12-16','yebihara','MySQLのMyRocksストレージエンジンの話を中の人から聞いた'),
 ('2016-12-15','eshimizu','ハゲたサンタがMySQLを覗きはじめたお話'),
 ('2016-12-14','mita2','MySQL 8.0 DMR と 5.7 パラメータ比較'),
 ('2016-12-13','RKajiyama','MySQL 5.7.17が出たのであのプラグインについて書いてみました'),
 ('2016-12-12','atsuizo','JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件'),
 ('2016-12-11','yoku0825','試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた'),
 ('2016-12-10','yoku0825','MySQLのビルド環境にConoHaを選んでいる理由'),
 ('2016-12-09','meijik','MySQL 8.0 Lab版でCTE(Common Table Expression)'),
 ('2016-12-08','yoku0825','最近のMroongaさんの構成について'),
 ('2016-12-07','mita2','MySQL 8.0 新機能 Persisting configuration variables'),
 ('2016-12-06','atsuizo','SQLを繰り返し実行したら段階的に応答速度が上がった話'),
 ('2016-12-05','atsuizo','MySQLのトランザクション制御がキモい話'),
 ('2016-12-04','zurazurataicho','MySQL5.7でJSON型を試してみた'),
 ('2016-12-03','yoku0825','mysqlコマンドラインクライアントのコマンド集'),
 ('2016-12-02','kakuka4430','CentOS6.8にtpcc-mysqlを入れようとして失敗した話'),
 ('2016-12-01','yoku0825','MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか');

INSERT INTO innodb_casual_2016 SELECT * FROM myisam_casual_2016;

日付を主キーにして、日付の降順に初期データを投入しています。MyISAMは基本的に追記型のデータ構造なので実行計画がテーブルスキャンのときの自然に返せる結果セットの順序はデータの投入順ということになります。一方InnoDBのデータ構造はクラスタードインデックスなので主キーを持つテーブルの場合データは主キーのキー値でソート済みになっています。

> SELECT * FROM myisam_casual_2016 USE INDEX () WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-23 | yoku0825 | COUNTを速くする（？）SQL1本ノック その2                                                                                                   |
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう！（tx_isolationとbinlogの話）                                                                                    |
| 2016-12-19 | yoku0825 | COUNTを速くする（？）SQL1本ノック その1                                                                                                   |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

> SELECT * FROM innodb_casual_2016 USE INDEX () WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-01 | yoku0825 | MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか                                                                          |
| 2016-12-03 | yoku0825 | mysqlコマンドラインクライアントのコマンド集                                                                                               |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-19 | yoku0825 | COUNTを速くする（？）SQL1本ノック その1                                                                                                   |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

セカンダリキーを使う実行計画では当然セカンダリキーのキー値でソートされた結果セットを返しますが、同じキー値同士のレコードの順序はMyISAMとInnoDBで異なります。ここでもMyISAMはデータの投入順、InnoDBは主キーのキー値の順序になります。

> SELECT * FROM myisam_casual_2016 USE INDEX (idx_author) WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう！（tx_isolationとbinlogの話）                                                                                    |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-23 | yoku0825 | COUNTを速くする（？）SQL1本ノック その2                                                                                                   |
| 2016-12-19 | yoku0825 | COUNTを速くする（？）SQL1本ノック その1                                                                                                   |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

> SELECT * FROM innodb_casual_2016 USE INDEX (idx_author) WHERE author IN ('yoku0825','atsuizo') LIMIT 10;
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| date       | author   | title                                                                                                                                     |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-12-05 | atsuizo  | MySQLのトランザクション制御がキモい話                                                                                                     |
| 2016-12-06 | atsuizo  | SQLを繰り返し実行したら段階的に応答速度が上がった話                                                                                       |
| 2016-12-12 | atsuizo  | JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件                                        |
| 2016-12-18 | atsuizo  | MySQLのChar型のデータサイズについて小ネタ                                                                                                 |
| 2016-12-21 | atsuizo  | お兄さんとの約束は守ろう！（tx_isolationとbinlogの話）                                                                                    |
| 2016-12-01 | yoku0825 | MySQLのNOW関数はどのようにして安全にスレーブでリプレイされるのか                                                                          |
| 2016-12-03 | yoku0825 | mysqlコマンドラインクライアントのコマンド集                                                                                               |
| 2016-12-08 | yoku0825 | 最近のMroongaさんの構成について                                                                                                           |
| 2016-12-10 | yoku0825 | MySQLのビルド環境にConoHaを選んでいる理由                                                                                                 |
| 2016-12-11 | yoku0825 | 試される大地 YAPC::Hokkaido 2016 SapporoでMySQL 8.0の話をしてきた                                                                         |
+------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

ORDER BY狙いのキーとはなんだったのか

No Using temporary; No Using filesort の言い換えではあるけどカーディナリティが高くてもUNIQUEなキーじゃない限り基本的にデータは単調増加するのでどういう順番でインデックスレコードに触れてどういう順番でデータを返したいのかを一番気にしてる。

— Ryuta Kamizono (@kamipo) February 22, 2016

twitter.com

MySQLでOLTP系クエリのチューニングをすることはつまるところ No using temporary, No using filesort を狙いに行くことですが、 No using filesort を狙うというのは欲しい結果セットの順序とMySQLが自然に返せる結果セットの順序を合わせるように実行計画を選択するということに他ならないので、もしMySQLの気持ちが分からなくなったときは、結果セットの順序に思いを馳せてみるのも良いかもしれません。