[MySQL] datetime 列に対する検索速度効率の違いを検証する
七色メガネ▼ はじめに
本日は満席の運びでこれはどうも
わたくし株式会社 Belong で Engineering Manager をしている七色メガネと申します
いやはや九月になったというのにまだまだ暑いですねえ
と言ってお天道様を憎む勇気もないしがない路傍石でございますから
やれ異常気象だのやれ二季の到来だの世間様と足並み揃えてあちらで騒ぎこちらで踊りなどをしておるわけですけれどもええ
氷菓片手に暦をチラとみてみますと、今は暦の上では立秋といい秋の始まりに片足を突っ込んでいるというですから驚きです
とはいえ九月の中頃まで続く暑さのことを残暑という慣わしは古くからあるそうで
立秋という言葉が涼の到来を表すものではないというのは昔から続くものというのはなんともおかしな話ではありますなあ
立という言葉から私は立てば芍薬、の句を連想しましたがそれをもじりまして一つ
立てば灼熱座れば炮烙歩く姿は湯気の中
おお万雷の拍手ありがとうございます
ということで今日のテーマは、MySQL 上の datetime 列に対して貼る index などのお話です。
▼ 背景
先日業務で数百万件のレコードを持つテーブルを日付条件で分析することになったのですが、datetime 列に対して index などがなかったので常にテーブルフルスキャンが走ってしまうような状態でした。
結局その時は datetime 列に対して index を張るようにして問題を解決したのですが、以来、以下の疑問が頭に残り続けました。
- datetime 列に生 index を張るのは正しかったのか？ 日付しか使わないのだから、 substr で YYYY-MM-DD を抽出するという選択肢もあったのではないか？
- あるいは date 型に変換した上で index を貼るという手もあったのではないか？
- partition を使った方が良かったのではないか？
今回はその疑問を解消すべく、いくつか検証を行おうと思います。ご興味あれば。
▼ 要件の整理
- test テーブルは datetime 型の created_at 列を持っている
- test テーブルには 1700 万行のデータが存在している。日付値は向こう 5 年の範囲でランダムに分布している。
- 分析を目的として、特定日付範囲において発生した test テーブルのデータを高速に抽出したい
- 分析にあたっては年月日条件 (YYYY-MM-DD) での絞り込み条件指定を行うが、時分秒(HH:MM:SS) の指定は行わない
- 使用する MySQL の version は 8.0.23.
▼ 検証内容
以下のパターンの状況を再現して、どれがもっとも効率よく検索を行えるのかなどを検証します。
- created_at に index を作成する。
- created_at の先頭 10 文字 (YYYY-MM-DD) を抽出してそれに対して index を作成する。
- date 型として created_at を抽出してそれに対して index を作成する。
- partition を作成する。
なお index の大きさや Insert 性能の違いなどは、今回の検証範囲外とします。
▼ 準備
パターン別にテーブルを作成します。まずは基本のテーブルです。自動採番の PK 列と datetime 型の created_at 列だけが存在するシンプルなテーブルです。
create table if not exists `test1` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`created_at` DATETIME,
PRIMARY KEY (`id`)
);
ここにデータを挿入します。
データ生成条件は、「現在から向こう 5 年以内のランダムな年月日時分秒の created_at 値をもつ」とします。
sys.schema_table_statistics に 65 件のデータがあったことを利用して、この 65 ^ 4 の直積計算でデータ数を稼ぎます。
これで大体 1,700 万件程度のデータができます。
insert into test1 (created_at)
select date_add(date_add(DATE_ADD(now(), INTERVAL CEIL(RAND() * 1825) DAY), INTERVAL CEIL(RAND() * 60) MINUTE), INTERVAL CEIL(RAND() * 60) second)
from
sys.schema_table_statistics s1,
sys.schema_table_statistics s2,
sys.schema_table_statistics s3,
sys.schema_table_statistics s4;
できたテーブルを以下の要領で複製します。これでデータ内容が全く同じテーブルが出来ます。index は後で貼ります。
create table if not exists `test2` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`created_at` DATETIME,
PRIMARY KEY (`id`)
) as select * from test1;
create table if not exists `test2b` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`created_at` DATETIME,
PRIMARY KEY (`id`)
) as select * from test1;
もう一つ、partition 検証用のテーブルも作成しておきます。
partition 指定をする列は Unique Key の一部でなければいけないという制約があるので、PK である id 値を絡めた複合インデックスにしておきます。
create table if not exists `test3` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`created_at` DATETIME,
PRIMARY KEY (`id`, `created_at`)
) as select * from test1;
▼ datetime VS substring(datetime, 1,10) VS date(created_at)
では検証を始めます。最初に index の性能差からみていきましょう。比較するのは以下の 3 パターンです。
- datetime 型である created_at 列に直接 index を貼る。
- substring(created_at, 1, 10) で文字列抽出し、YYYY-MM-DD の形に整形した状態で index を貼る。
- date(created_at) で date 抽出し、その状態で index を貼る。
検索条件は YYYY-MM-DD なのだから、普通に index を貼るよりも検索条件に適合した形で index を貼っておいた方が 検索効率は最適化されるのではないか？という仮説に基づいています。
同一構造で同一データをもった 3 つのテーブルに、異なる index を貼ります。
create index test1_idx on test1 (created_at);
create index test2_idx on test2 ((substring(created_at, 1, 10)));
create index test2b_idx on test2b ((date(created_at)));
今回実行したクエリと観点の組み合わせは以下の通りです。
| No | 用意した index | 観点 |
|---|---|---|
| 1 | datetime 型 | datetime 型に寄せて検索条件を設定する |
| 2 | datetime 型 | 文字列 型に寄せて検索条件を設定する |
| 3 | datetime 型 | date 型に寄せて検索条件を設定する |
| 4 | substr 型 | datetime 型に寄せて検索条件を設定する |
| 5 | substr 型 | 文字列 型に寄せて検索条件を設定する |
| 6 | substr 型 | date 型に寄せて検索条件を設定する |
| 7 | date 型 | datetime 型に寄せて検索条件を設定する |
| 8 | date 型 | 文字列 型に寄せて検索条件を設定する |
| 9 | date 型 | date 型に寄せて検索条件を設定する |
早速ですが結果です。速度という観点で、4 つのクラスに分類してみました。
| レスポンス速度クラス (最速・速・遅・最遅) | 検索速度 | No | Query | 用意した index |
|---|---|---|---|---|
| 最速 | 0.05 sec | 1 | ... from test1 where created_at between "2024-10-31" and "2024-11-01"; | datetime 型 |
| 速 | 2.0 sec | 5 | ... from test2 where substring(created_at,1,10) = "2024-10-31"; | substr 型 |
| 速 | 2.3 sec | 9 | ... from test2b where date(created_at) = "2024-10-31"; | date 型 |
| 遅 | 23.3 sec | 6 | ... from test2 where date(created_at) = "2024-10-31"; | substr 型 |
| 遅 | 24.6 sec | 3 | ... from test1 where date(created_at) = "2024-10-31"; | datetime 型 |
| 遅 | 24.9 sec | 2 | ... from test1 where substring(created_at,1,10) = "2024-10-31"; | datetime 型 |
| 遅 | 31.7 sec | 8 | ... from test2b where substring(created_at,1,10) = "2024-10-31"; | date 型 |
| 最遅 | 42.9 sec | 4 | ... from test2 where created_at between "2024-10-31" and "2024-11-01"; | substr 型 |
| 最遅 | 45.0 sec | 7 | ... from test2b where created_at between "2024-10-31" and "2024-11-01"; | date 型 |
ではこれらについてみていきましょう。
⚪︎ 最速クラス (No.1)
最も速かったのは、datetime 型である created_at 列に index を貼り、created_at 列に対して文字列で検索をかけたケースです。
検索条件としての文字列は暗黙変換されるので、datetime 列に対して datetime 条件で検索をかけることになります。
| No | Query | 用意した index | 検索速度 | 速度 (最速・速・遅・最遅) |
|---|---|---|---|---|
| 1 | ... from test1 where created_at between "2024-10-31" and "2024-11-01"; | datetime 型 | 0.05 sec | 最速 |
select created_at from test1 where created_at between "2024-10-31" and "2024-11-01";
これが最速の 0.05 sec でした。実行計画は次の通りです。
datetime 型の index が存在しているのに対し、datetime 列に対して datetime 条件を指定したため、index を使用できているのがわかります。
また Extra に Using index 表記があるのにも注目したいです。
これはカバリングインデックスが成立していること、つまり取得しようとしている列情報が index の中に存在しているため実テーブル参照フェーズが skip されていることを示しています。
index 空間で絞り込みが完結し、かつ select も index 空間内で完結しているのでこれが最速になった、と言えそうです。
⚪︎ 速クラス (No.5, No.9)
datetime 型以外の形で index を貼り、検索条件としてその index が使用されるように左辺の検索対象列の型を index の形に合わせたケースが 2 番目に早いケースとなりました。
| No | Query | 用意した index | 検索速度 | 速度 (最速・速・遅・最遅) |
|---|---|---|---|---|
| 5 | ... from test2 where substring(created_at,1,10) = "2024-10-31"; | substr 型 | 2.0 sec | 速 |
| 9 | ... from test2b where date(created_at) = "2024-10-31"; | date 型 | 2.3 sec | 速 |
select created_at from test2 where substring(created_at,1,10) = "2024-10-31";
select created_at from test2b where date(created_at) = "2024-10-31";
実行計画の構造は同じだったので、一緒に確認しましょう。
意図した通りに index は使えているようです。No1 の実行計画と比較した時に気になるのは、type が ref になっていることと、Extra が null なことでしょうか。
type: ref とは、index 内で等価検索が行われたことを表しています。 index 列が YYYY-MM-DD で where 条件も YYYY-MM-DD でありかつ対象も 1 日なので、等価検索が成立したんですね。
Extra で考えたいのは、カバリングインデックスが成立しなかったという点です。
index 空間に用意されたのは 文字列や date の型に紐づく値で、Select で呼び出されているのは
元の datetime 値なので index 空間に用意がなく実テーブルをみに行くことになった、と解釈できるでしょうか。
じゃあ select で取得する値を index の型 寄せたらカバリングインデックスが成立するのでしょうか？やってみます。
だめでした。公式 Doc を読みましたがちょっと正確な理由はわからず。
おそらくカバリングインデックスが成立するのは raw なカラムについてのみ、のようなルールがあるのだと思います。
あるいは関数を当てる前の値を取るのにやっぱり実テーブルを見に行く必要がある、とかでしょうか。
⚪︎ 遅クラス (No.2, No.3, No.6, No.8)
以下のクエリ群は実行に 20 ~ 30 sec を要しました。用意した index とは異なる形に created_at 列を整形した状態で検索をかけるケースです。
| No | Query | 用意した index | 検索速度 | 速度 (最速・速・遅・最遅) |
|---|---|---|---|---|
| 2 | ... from test1 where substring(created_at,1,10) = "2024-10-31"; | datetime 型 | 24.9 sec | 遅 |
| 3 | ... from test1 where date(created_at) = "2024-10-31"; | datetime 型 | 24.6 sec | 遅 |
| 6 | ... from test2 where date(created_at) = "2024-10-31"; | substr 型 | 23.3 sec | 遅 |
| 8 | ... from test2b where substring(created_at,1,10) = "2024-10-31"; | date 型 | 31.7 sec | 遅 |
select created_at from test1 where substring(created_at,1,10) = "2024-10-31";
select created_at from test1 where date(created_at) = "2024-10-31";
select created_at from test2 where date(created_at) = "2024-10-31";
select created_at from test2b where substring(created_at,1,10) = "2024-10-31";
実行計画は 2 種類に分かれました。
前者は No2 と No3 の計画で、後者は No6 と No8 の計画です。差分は、インデックスフルスキャンなのか、テーブルフルスキャンなのか、という点であるようです。
type: index はインデックスフルスキャンが走っていることを意味します。その名の通り、index 空間を全てスキャンしているんですね。
No1 の時はここが range であり、「index 空間での範囲検索」となっていました。つまり今回は index 空間内で適切な絞り込みができなかったことになります。なぜか？
おそらく、datetime 型の index が貼られた列であり created_at に変換処理をかけたためでしょう。
index 列に対して演算処理を行ったり型変換を行ったりした場合には index が使われないようになります。今回はそういった index 不使用条件を満たしたのではないかと。
ただ index を使った効率的な検索はできなくても、物理的な検索空間として index 空間を使用することができます。
index 空間って PK と index 列が入ったテーブルみたいなものですからね。ということは No1 と同じように Using index, つまりカバリングインデックスが成立していることからわかります。
まとめると、「index をみるよ！ただ有効活用はできないから index の中を全部見るよ！ガハハ！」って感じですかね。
後者は type:ALL とありますが、これはテーブルをフルスキャンとしたことを示します。実行計画としては最悪のケースですね。しかし No2/3 系とスピードに差がないのはなぜでしょうか？
おそらく、index 空間とテーブル空間の大きさがほぼ同じためだと思われます。今回の index は created_at 列のみを含む形で作成しましたが、index はその性質上、PK 情報も空間に保持します。
対して今回の実テーブルは、PK と created_at 列しか持たない簡素なものです。
したがって、今回の場合では index をフルスキャンしようがテーブルをフルスキャンしようが速度は変わらなかった、と言えるのかなと思います。
⚪︎ 最遅クラス (No.2, No.3 No.6, No.8)
以下のクエリ群は実行に 40 sec を要しました。先ほどのテーブルフルスキャンが底辺だと思いましたが、まだ下があるようです。
| No | Query | 用意した index | 検索速度 | 速度 (最速・速・遅・最遅) |
|---|---|---|---|---|
| 4 | ... from test2 where created_at between "2024-10-31" and "2024-11-01"; | substr 型 | 42.9 sec | 最遅 |
| 7 | ... from test2b where created_at between "2024-10-31" and "2024-11-01"; | date 型 | 45.0 sec | 最遅 |
select created_at from test2 where created_at between "2024-10-31" and "2024-11-01";
select created_at from test2b where created_at between "2024-10-31" and "2024-11-01";
テーブルフルスキャンであるのは遅クラスと同じです。今回異なるのは、filtered の値が遅クラスでは 100 だったのに今回は 11 になっているという点ですね。
私はあまり filtered の値を意識することはなかったのですが、調べたところこの値はフィルタ処理される行の割合を示しているようです。filtered 100 は、絞り込み処理が全く行われないことを意味します。
今回は between 句を指定したクエリでこの filtered 11.11 に入りました。
つまり下手にフィルター路線でデータの絞り込みをかけるアプローチをとった結果、テーブルフルスキャンよりも遅い結果になった、と言えるでしょうか。
⚪︎ ここまでの結論
この検証の結論は以下です。
- datetime 型の列に直接 index を貼った方が検索性能は良くなる。
- substr() や date() の方向性でも、実用レベルの検索速度は出る。
- 使える index がない状況で使用される between 句の性能はとても低い。
▼ datetime index VS partition
ということで datetime に直接はった index の方が優秀だという結論が出ました。 では partition と比べたらどうでしょうか。
準備フェーズで partition 用のテーブル自体は既に作成しているので、設定から始めます。
今回は YYYY-MM の範囲の RANGE 型で partition を設定します。無駄にテストデータを 5 年分にしたので alter 指定が面倒...。
ALTER TABLE `test3`
PARTITION BY RANGE COLUMNS(`created_at`) (
PARTITION p202308 VALUES LESS THAN ('2023-08-01 00:00:00'),
PARTITION p202309 VALUES LESS THAN ('2023-09-01 00:00:00'),
PARTITION p202310 VALUES LESS THAN ('2023-10-01 00:00:00'),
PARTITION p202311 VALUES LESS THAN ('2023-11-01 00:00:00'),
PARTITION p202312 VALUES LESS THAN ('2023-12-01 00:00:00'),
PARTITION p202401 VALUES LESS THAN ('2024-01-01 00:00:00'),
PARTITION p202402 VALUES LESS THAN ('2024-02-01 00:00:00'),
PARTITION p202403 VALUES LESS THAN ('2024-03-01 00:00:00'),
PARTITION p202404 VALUES LESS THAN ('2024-04-01 00:00:00'),
PARTITION p202405 VALUES LESS THAN ('2024-05-01 00:00:00'),
PARTITION p202406 VALUES LESS THAN ('2024-06-01 00:00:00'),
PARTITION p202407 VALUES LESS THAN ('2024-07-01 00:00:00'),
PARTITION p202408 VALUES LESS THAN ('2024-08-01 00:00:00'),
PARTITION p202409 VALUES LESS THAN ('2024-09-01 00:00:00'),
PARTITION p202410 VALUES LESS THAN ('2024-10-01 00:00:00'),
PARTITION p202411 VALUES LESS THAN ('2024-11-01 00:00:00'),
PARTITION p202412 VALUES LESS THAN ('2024-12-01 00:00:00'),
PARTITION p202501 VALUES LESS THAN ('2025-01-01 00:00:00'),
PARTITION p202502 VALUES LESS THAN ('2025-02-01 00:00:00'),
PARTITION p202503 VALUES LESS THAN ('2025-03-01 00:00:00'),
PARTITION p202504 VALUES LESS THAN ('2025-04-01 00:00:00'),
PARTITION p202505 VALUES LESS THAN ('2025-05-01 00:00:00'),
PARTITION p202506 VALUES LESS THAN ('2025-06-01 00:00:00'),
PARTITION p202507 VALUES LESS THAN ('2025-07-01 00:00:00'),
PARTITION p202508 VALUES LESS THAN ('2025-08-01 00:00:00'),
PARTITION p202509 VALUES LESS THAN ('2025-09-01 00:00:00'),
PARTITION p202510 VALUES LESS THAN ('2025-10-01 00:00:00'),
PARTITION p202511 VALUES LESS THAN ('2025-11-01 00:00:00'),
PARTITION p202512 VALUES LESS THAN ('2025-12-01 00:00:00'),
PARTITION p202601 VALUES LESS THAN ('2026-01-01 00:00:00'),
PARTITION p202602 VALUES LESS THAN ('2026-02-01 00:00:00'),
PARTITION p202603 VALUES LESS THAN ('2026-03-01 00:00:00'),
PARTITION p202604 VALUES LESS THAN ('2026-04-01 00:00:00'),
PARTITION p202605 VALUES LESS THAN ('2026-05-01 00:00:00'),
PARTITION p202606 VALUES LESS THAN ('2026-06-01 00:00:00'),
PARTITION p202607 VALUES LESS THAN ('2026-07-01 00:00:00'),
PARTITION p202608 VALUES LESS THAN ('2026-08-01 00:00:00'),
PARTITION p202609 VALUES LESS THAN ('2026-09-01 00:00:00'),
PARTITION p202610 VALUES LESS THAN ('2026-10-01 00:00:00'),
PARTITION p202611 VALUES LESS THAN ('2026-11-01 00:00:00'),
PARTITION p202612 VALUES LESS THAN ('2026-12-01 00:00:00'),
PARTITION p202701 VALUES LESS THAN ('2027-01-01 00:00:00'),
PARTITION p202702 VALUES LESS THAN ('2027-02-01 00:00:00'),
PARTITION p202703 VALUES LESS THAN ('2027-03-01 00:00:00'),
PARTITION p202704 VALUES LESS THAN ('2027-04-01 00:00:00'),
PARTITION p202705 VALUES LESS THAN ('2027-05-01 00:00:00'),
PARTITION p202706 VALUES LESS THAN ('2027-06-01 00:00:00'),
PARTITION p202707 VALUES LESS THAN ('2027-07-01 00:00:00'),
PARTITION p202708 VALUES LESS THAN ('2027-08-01 00:00:00'),
PARTITION p202709 VALUES LESS THAN ('2027-09-01 00:00:00'),
PARTITION p202710 VALUES LESS THAN ('2027-10-01 00:00:00'),
PARTITION p202711 VALUES LESS THAN ('2027-11-01 00:00:00'),
PARTITION p202712 VALUES LESS THAN ('2027-12-01 00:00:00'),
PARTITION p202801 VALUES LESS THAN ('2028-01-01 00:00:00'),
PARTITION p202802 VALUES LESS THAN ('2028-02-01 00:00:00'),
PARTITION p202803 VALUES LESS THAN ('2028-03-01 00:00:00'),
PARTITION p202804 VALUES LESS THAN ('2028-04-01 00:00:00'),
PARTITION p202805 VALUES LESS THAN ('2028-05-01 00:00:00'),
PARTITION p202806 VALUES LESS THAN ('2028-06-01 00:00:00'),
PARTITION p202807 VALUES LESS THAN ('2028-07-01 00:00:00'),
PARTITION p202808 VALUES LESS THAN ('2028-08-01 00:00:00'),
PARTITION p202809 VALUES LESS THAN ('2028-09-01 00:00:00'));
やってから気づいたのですが、パーティションの中身と名前が 1 ヶ月分ずつズレていました。ただ直すの面倒なのでこのまま行きます。ごめんね。
今回は 6 つのパターンで検証を行いました。変数は index か partition かという点と、日付範囲ですね。結果のサマリーは以下の通りです。
| No | Query | 検証先 index | 観点 | 検索速度 |
|---|---|---|---|---|
| 1 | select * from test1 where created_at between "2024-07-01" and "2024-07-02"; | datetime 型 | 範囲: 1 day | 0.07 sec |
| 2 | select * from test1 where created_at between "2024-07-01" and "2024-08-01"; | datetime 型 | 範囲: 1 month | 1.3 sec |
| 3 | select * from test1 where created_at between "2024-07-01" and "2025-08-01"; | datetime 型 | 範囲: 1 year | 18.7 sec |
| 4 | select * from test3 where created_at between "2024-07-01" and "2024-07-02"; | partition 型 | 範囲: 1 day | 0.8 sec |
| 5 | select * from test3 partition(p202408); | partition 型 | 範囲: 1 month | 0.8 sec |
| 6 | select * from test3 partition(p202408,p202409,p202410,p202411,p202412,p202501,p202502,p202503,p202504,p202505,p202506,p202507,p202508); | partition 型 | 範囲: 1 year | 10.6 sec |
⚪︎ No.1 と No.4 // 範囲: 1 day
日付範囲が 1 日の場合の比較です。
| No | Query | 検証先 index | 観点 | 検索速度 |
|---|---|---|---|---|
| 1 | select * from test1 where created_at between "2024-07-01" and "2024-07-02"; | datetime 型 | 範囲: 1 day | 0.07 sec |
| 4 | select * from test3 where created_at between "2024-07-01" and "2024-07-02"; | partition 型 | 範囲: 1 day | 0.8 sec |
この場合は index 型の方が早かったですね。
ある意味当然で、partition は今回 YYYY-MM のレベルで切ってあります。
ので 1 日レベルで条件指定されると、partition のなかでさらに検索を行うことになるので特にメリットがないんですね。
実行計画は面白みがなかったので割愛です。
⚪︎ No.2 と No.5 // 範囲: 1 month
次は範囲を 1 ヶ月に広げます。ここから partition 空間が火を吹きます。
| No | Query | 検証先 index | 観点 | 検索速度 |
|---|---|---|---|---|
| 2 | select * from test1 where created_at between "2024-07-01" and "2024-08-01"; | datetime 型 | 範囲: 1 month | 1.3 sec |
| 5 | select * from test3 partition(p202408); | partition 型 | 範囲: 1 month | 0.8 sec |
速度が逆転し、partition の方が早くなりました。
index の方はカバリングインデックスを使い高速処理できているとはいえ、type:range の範囲検索になっています。
partition の方は面白くて、type: index なので表記上インデックスのフルスキャンになっています。
ただ、インデックスフルスキャンなら rows が index 中の行数が表示される ( つまり 1,700 万件) のに今回は 30 万件しか表示されていません。 そしてこれはこのクエリで取得されるデータ件数にほぼ一致します。
このことから、想像の域は出ないのですが、これは区切られた partition 空間内のフルスキャンになっているのではないかなと推測します。 そう考えると、インデックスフルスキャンであっても範囲検索よりも高速に終了していることが説明できます。
ちなみに No5 の実行速度は、No4 とほぼ同じでした。
このことから、同一 partition 内にアクセスするなら 1 件取得使用が 10 万件取得しようがそれほど速度に差はない、ってことがわかります。
⚪︎ No.3 と No.6 // 範囲: 1 year
最後は範囲を 1 年に拡大します。
| No | Query | 検証先 index | 観点 | 検索速度 |
|---|---|---|---|---|
| 3 | select * from test1 where created_at between "2024-07-01" and "2025-08-01"; | datetime 型 | 範囲: 1 year | 18.7 sec |
| 6 | select * from test3 partition(p202408,p202409,p202410,p202411,p202412,p202501,p202502,p202503,p202504,p202505,p202506,p202507,p202508); | partition 型 | 範囲: 1 year | 10.6 sec |
ここは正直、No2/No5 の差が広がっただけで新しい発見はないですね。範囲が広がれば広がるほど、partition の方に軍配が上がりますよと。 1 month の時の差が 0.5 sec だったのに対して 1 year の時の差は 8.1 sec です。0.5 * 13 = 6.5 と 8.1 に近いので、 partition 1 つあたりの速度差が重なって累計差になっていると理解して良さそうです。
余談ですがこの検証をしているとき、07/01 ~ 07/31 の範囲指定での検索速度は partition の方が早かったのですが、07/01 ~ 08/02 範囲での検索速度は index の方が早かったです。 この理由ですが、検索対象になる partition 空間の数によるものでした。今回の設定だと ~ 07/31 だと 1 つの partition 空間に収まるのですが、~08/02 にしてしまうと次の partition 空間もみなければいけません。 すると検索空間が 1 つ増えることになり、検索速度も倍程度に遅くなりました。
partition を使う時は検索データが 1 つの partition に収まるようにすると早くなるんですね。 ( このように意識することをパーティション・プルーニングというらしいです )
▼ 結論
長くなってしまいましたが、今回の検証結果をまとめると次のようになるでしょうか。
- datetime 列に対して YYYY-MM-DD での検索効率をあげたいのであれば、以下の順序で有効。
- (1) partition を設定する
- (2) datetime 列に index を設定する
- (3) datetime 列に substr() や date() で index を設定する
- index は、where で使用される型が一致していないと正しく使われない。
- partition が常に index よりも高速だとは限らない。加えて partition の設定には制約条件などがある。想定しているユースケースは partition に適合するか、制約を呑む覚悟はあるか、などを検討するべき。
▼ 終わりに
ということで datetime 上の index に関する検証でした。なんだか G 線上のアリア的なクールさがありますね、この言い回し。
では、さようなら。
弊社 Belong では一緒にサービスを育てる仲間を募集しています。
もし弊社に興味を持っていただける方がおりましたら、 https://entrancebook.belonginc.dev/ をご覧いただけると。
▼ おまけ: delete 速度
「2025 年の 1 月から 12 月までのデータを削除したくてしたくてたまらない！！」
delete from test1 where created_at between "2025-01-01" and "2026-01-01";
ALTER TABLE test3 DROP PARTITION p202502,p202503,p202504,p2025105,p202506,p202507,p202508,p202509,p202510,p202511,p202512,p202601;
610 倍です！ハエー。partition の本当のメリットは削除など別の方面にあるんでしょうね。