メリークリスマス!🎅🎄
このエントリはMySQL Casual Advent Calendar 2016の24日目です。
今日はこれの話です!
並び順が実装と実行計画に依存するとはどういうことか
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狙いのキーとはなんだったのか
twitter.com
MySQLでOLTP系クエリのチューニングをすることはつまるところ No using temporary, No using filesort を狙いに行くことですが、 No using filesort を狙うというのは欲しい結果セットの順序とMySQLが自然に返せる結果セットの順序を合わせるように実行計画を選択するということに他ならないので、もしMySQLの気持ちが分からなくなったときは、結果セットの順序に思いを馳せてみるのも良いかもしれません。