はじめに

こちらのそーだいさんの記事にある、履歴テーブルから最新の１件を取ってくる方法のTiDB(MySQL)版です。

TiDB/MySQLは特殊な関数がないので、オーソドックスにWindow関数を使ったやり方を試してみます。
それだけだと面白くないので、本記事ではより大きなデータセットを用いて、実行計画の改善を試し、改善度合いを見てみることにします。また、これはTiFlash(HTAP)のアクセラレーションの良い例でもあるので、TiFlashも使ってみました。

準備

まずは、大量のデータセットを用意します。テーブル定義はそーだいさんのブログに記載のテーブル定義をそのまま利用し、それにあわせたデータを作成するプログラムを作成しました。

言語は何でもいいと思いますが、ダミーデータを作成するライブラリがあると楽です。ここではgo言語の gofakeit を使いました。

大量データの場合、多くのケースにおいて直接INSERTするよりCSV等をバルクインサートする方が速いです。
このプログラムもCSVを書き出しているだけです。

main.go

package main

import (
	"encoding/csv"
	"fmt"
	"math/rand"
	"os"
	"strconv"
	"time"

	"github.com/brianvoe/gofakeit/v7"
)

func main() {
	if len(os.Args) != 5 {
		fmt.Println("Usage: datagen <seed> <number_of_rows> <max_id> <output_file>")
		return
	}

	seed, err := strconv.ParseInt(os.Args[1], 10, 64)
	if err != nil {
		fmt.Println("Invalid seed:", os.Args[1])
		return
	}

	numRows, err := strconv.Atoi(os.Args[2])
	if err != nil {
		fmt.Println("Invalid number of rows:", os.Args[2])
		return
	}

	maxID, err := strconv.Atoi(os.Args[3])
	if err != nil {
		fmt.Println("Invalid max_id:", os.Args[3])
		return
	}

	outputFile := os.Args[4]

	gofakeit.Seed(seed)

	file, err := os.Create(outputFile)
	if err != nil {
		fmt.Println("Could not create file:", outputFile)
		return
	}
	defer file.Close()

	writer := csv.NewWriter(file)
	defer writer.Flush()

	// Write CSV header
	writer.Write([]string{"id", "user_id", "data", "created_at"})

	for i := 0; i < numRows; i++ {
		id := i + 1
		user_id := rand.Intn(maxID) + 1
		data := gofakeit.Sentence(10)
		created_at := gofakeit.Date().Format(time.RFC3339)

		writer.Write([]string{strconv.Itoa(id), strconv.Itoa(user_id), data, created_at})
	}
}

とりあえずこれで10000ユーザー/1千万行くらいのCSVファイルを生成します。900MB程度ありますが、M1Macでの生成時間は数分といったところです。これをS3に保存します。

TiDB Cloud側であらかじめテーブルを作成しておきます。テーブル定義はそーだいさんのブログのものをそのまま利用しました。

history.sql

-- MySQL
CREATE TABLE history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    data TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

このテーブルに対し、S3からデータをimportしておきます。これで準備は完了です。

ベースラインのクエリパフォーマンス

では、何もせずそのままの状態でクエリを実行してみることにします。10000ユーザーのそれぞれの最新の1件を取得するクエリです。10000件出力すると長いので、100件にしぼります。

window 関数

window.sql

SELECT *
FROM (SELECT history.*,
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS ranking
      FROM history) latest
WHERE ranking = 1
limit 100;

+---------+---------+---------------------------------------------------------------------------------+---------------------+---------+
| id      | user_id | data                                                                            | created_at          | ranking |
+---------+---------+---------------------------------------------------------------------------------+---------------------+---------+
| 8644123 |       3 | You some however enchanted example troop riches Eastern this along.             | 2024-12-11 20:17:55 |       1 |
| 4604397 |      14 | It is what recently colorful out dynasty water besides very.                    | 2024-12-07 09:03:51 |       1 |
| 8960784 |      24 | Tense whereas Italian yearly first sandwich yourself data it about.             | 2024-12-29 09:05:30 |       1 |
...

| 4613743 |     494 | Example always you each occasionally out down troupe hers thoughtfully.         | 2024-11-15 03:28:30 |       1 |
| 7343243 |     498 | Result those many joy his were today in you substantial.                        | 2024-12-30 11:11:45 |       1 |
| 7143736 |     501 | Plane where often in upon across batch whom point near.                         | 2024-11-19 03:43:41 |       1 |
+---------+---------+---------------------------------------------------------------------------------+---------------------+---------+
100 rows in set (23.42 sec)

23.42秒かかっています。

実行計画も見てみます。

mysql> explain analyze SELECT * FROM (SELECT history.*,              ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS ranking       FROM history) latest WHERE ranking = 1 limit 100;
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                 | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info                                                                                                                                           | memory   | disk    |
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Limit_14                           | 100.00      | 100      | root      |               | time:16.6s, loops:2, RU:20481.047892                                                                                                                                                                                                                                                                                                                                                                                                                       | offset:?, count:?                                                                                                                                       | N/A      | N/A     |
| └─Selection_15                     | 100.00      | 100      | root      |               | time:16.6s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                        | eq(Column#6, ?)                                                                                                                                         | 122.2 KB | N/A     |
|   └─Shuffle_20                     | 100.00      | 99328    | root      |               | time:16.6s, loops:97, ShuffleConcurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                 | execution info: concurrency:5, data sources:[TableReader_18]                                                                                            | N/A      | N/A     |
|     └─Window_16                    | 100.00      | 103424   | root      |               | time:1m41.4s, loops:102                                                                                                                                                                                                                                                                                                                                                                                                                                    | row_number()->Column#6 over(partition by firsthist.history.user_id order by firsthist.history.created_at desc rows between current row and current row) | N/A      | N/A     |
|       └─Sort_19                    | 10000000.00 | 107520   | root      |               | time:1m41.4s, loops:106                                                                                                                                                                                                                                                                                                                                                                                                                                    | firsthist.history.user_id, firsthist.history.created_at:desc                                                                                            | 248.3 MB | 0 Bytes |
|         └─ShuffleReceiver_21       | 10000000.00 | 10000000 | root      |               | time:1m8.7s, loops:9774                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                         | N/A      | N/A     |
|           └─TableReader_18         | 10000000.00 | 10000000 | root      |               | time:53.5ms, loops:9782, cop_task: {num: 291, max: 68.6ms, min: 1.05ms, avg: 39.1ms, p95: 59.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 8.71s, tot_wait: 25ms, copr_cache: disabled, build_task_duration: 31.5µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:291, total_time:11.4s}}                                                                                                                                             | data:TableFullScan_17                                                                                                                                   | 49.6 MB  | N/A     |
|             └─TableFullScan_17     | 10000000.00 | 10000000 | cop[tikv] | table:history | tikv_task:{proc max:50ms, min:0s, avg: 26.3ms, p80:40ms, p95:50ms, iters:10920, tasks:291}, scan_detail: {total_process_keys: 10000000, total_process_keys_size: 1142807139, total_keys: 10000291, get_snapshot_time: 18.4ms, rocksdb: {key_skipped_count: 10000000, block: {cache_hit_count: 39879}}}, time_detail: {total_process_time: 8.71s, total_suspend_time: 18.6ms, total_wait_time: 25ms, total_kv_read_wall_time: 7.64s, tikv_wall_time: 8.79s} | keep order:false                                                                                                                                        | N/A      | N/A     |
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
8 rows in set (22.65 sec)

ここで、cop[tikv] はTiKVレイヤーの処理を、root はTiDBレイヤーでの処理を示しています。全量TiKVから読み出して、TiDBサーバ側でフィルタ処理をしているようです。元のSQLからみても想像がつく実行計画ではないでしょうか。

集計関数

今回のように、日付型など大小比較が可能なものについては、集計演算を使う方法もあります（Window関数がそれほど広まってなかったときは、こちらのやり方が主流だったと思います）
ただこちらは厳密には先のwindow関数とは異なっていて、最新のレコードが複数あった場合、複数出てきてしまいます。

max.sql

select h1.*
from history h1 join 
  (select  user_id, max(created_at) as latest from history group by user_id) h2 
on h1.created_at = h2.latest and h1.user_id = h2.user_id
limit 100;

+---------+---------+---------------------------------------------------------------------------------+---------------------+
| id      | user_id | data                                                                            | created_at          |
+---------+---------+---------------------------------------------------------------------------------+---------------------+
| 1894623 |    2150 | Nevertheless of will an hers late it anyway tomorrow so.                        | 2024-12-05 09:49:01 |
| 5684905 |    9067 | It how nest place he rather muster electricity soon where.                      | 2024-07-12 18:13:00 |
|  948625 |     878 | Party this caravan untie mob Congolese these turn kiss library.                 | 2024-12-04 09:58:19 |
...
|  999449 |    5354 | Gossip heavily child lean may next class hmm it others.                         | 2024-12-10 01:56:49 |
|  999484 |    2252 | It dance hundreds respects regularly from had tonight that where.               | 2024-12-15 12:05:14 |
| 1003066 |    9095 | Now you then handsome consequently neither hence oops daily whenever.           | 2024-11-30 04:09:12 |
+---------+---------+---------------------------------------------------------------------------------+---------------------+
100 rows in set (1.43 sec)

1.43秒です。なんか間違ってるんじゃないかと思ってcount(distinct)してみましたが、結果は正しかったです。

こちらも実行計画をみてみます。

mysql> explain analyze select h1.* from history h1 join    (select  user_id, max(created_at) as latest from history group by user_id) h2  on h1.created_at = h2.latest and h1.user_id = h2.user_id limit 100;
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                 | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info                                                                                                                            | memory   | disk    |
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Limit_16                           | 100.00      | 100      | root      |               | time:1.39s, loops:2, RU:30174.050516                                                                                                                                                                                                                                                                                                                                                                                                                       | offset:?, count:?                                                                                                                        | N/A      | N/A     |
| └─HashJoin_17                      | 100.00      | 1024     | root      |               | time:1.39s, loops:1, build_hash_table:{total:806.9ms, fetch:804ms, build:2.93ms}, probe:{concurrency:5, total:6.96s, max:1.39s, probe:952.7ms, fetch and wait:6.01s}                                                                                                                                                                                                                                                                                       | inner join, equal:[eq(firsthist.history.created_at, Column#9) eq(firsthist.history.user_id, firsthist.history.user_id)]                  | 878.7 KB | 0 Bytes |
|   ├─Selection_21(Build)            | 8000.00     | 10000    | root      |               | time:804.4ms, loops:11                                                                                                                                                                                                                                                                                                                                                                                                                                     | not(isnull(Column#9))                                                                                                                    | 19.0 KB  | N/A     |
|   │ └─HashAgg_26                   | 10000.00    | 10000    | root      |               | time:803.7ms, loops:12, partial_worker:{wall_time:801.523879ms, concurrency:5, task_num:88, tot_wait:3.896902186s, tot_exec:110.531283ms, tot_time:4.007506382s, max:801.508052ms, p95:801.508052ms}, final_worker:{wall_time:804.546583ms, concurrency:5, task_num:25, tot_wait:17.448µs, tot_exec:9.896434ms, tot_time:4.020676143s, max:804.534267ms, p95:804.534267ms}                                                                                 | group by:firsthist.history.user_id, funcs:max(Column#10)->Column#9, funcs:firstrow(firsthist.history.user_id)->firsthist.history.user_id | 12.5 MB  | N/A     |
|   │   └─TableReader_27             | 10000.00    | 332777   | root      |               | time:798.6ms, loops:89, cop_task: {num: 90, max: 721.9ms, min: 1.36ms, avg: 90ms, p95: 713.4ms, max_proc_keys: 915864, p95_proc_keys: 915737, tot_proc: 7.75s, tot_wait: 14.9ms, copr_cache: disabled, build_task_duration: 15.7µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:90, total_time:8.09s}}                                                                                                                                            | data:HashAgg_22                                                                                                                          | 340.6 KB | N/A     |
|   │     └─HashAgg_22               | 10000.00    | 332777   | cop[tikv] |               | tikv_task:{proc max:720ms, min:0s, avg: 86ms, p80:10ms, p95:710ms, iters:9774, tasks:90}, scan_detail: {total_process_keys: 10000000, total_process_keys_size: 1142807139, total_keys: 10000090, get_snapshot_time: 13.2ms, rocksdb: {key_skipped_count: 10000000, block: {cache_hit_count: 39153}}}, time_detail: {total_process_time: 7.75s, total_suspend_time: 14.2ms, total_wait_time: 14.9ms, total_kv_read_wall_time: 6.75s, tikv_wall_time: 7.79s} | group by:firsthist.history.user_id, funcs:max(firsthist.history.created_at)->Column#10                                                   | N/A      | N/A     |
|   │       └─TableFullScan_25       | 10000000.00 | 10000000 | cop[tikv] | table:history | tikv_task:{proc max:649ms, min:0s, avg: 75ms, p80:10ms, p95:630ms, iters:9774, tasks:90}                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                         | N/A      | N/A     |
|   └─TableReader_20(Probe)          | 125000.00   | 4875872  | root      |               | time:402ms, loops:4767, cop_task: {num: 183, max: 71.1ms, min: 983µs, avg: 34ms, p95: 66.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 4.4s, tot_wait: 20.5ms, copr_cache: disabled, build_task_duration: 34.1µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:183, total_time:6.22s}}                                                                                                                                                | data:TableFullScan_19                                                                                                                    | 18.0 MB  | N/A     |
|     └─TableFullScan_19             | 125000.00   | 4905568  | cop[tikv] | table:h1      | tikv_task:{proc max:50ms, min:0s, avg: 20.8ms, p80:40ms, p95:50ms, iters:5503, tasks:183}, scan_detail: {total_process_keys: 4905568, total_process_keys_size: 560603018, total_keys: 4905751, get_snapshot_time: 16.3ms, rocksdb: {key_skipped_count: 4905568, block: {cache_hit_count: 19694}}}, time_detail: {total_process_time: 4.4s, total_suspend_time: 9.64ms, total_wait_time: 20.5ms, total_kv_read_wall_time: 3.81s, tikv_wall_time: 4.49s}     | keep order:false                                                                                                                         | N/A      | N/A     |
+------------------------------------+-------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
9 rows in set (1.40 sec)

こちらは、サブクエリ部分の集計演算と、それを元にHashJoinを行う二段階に分かれています。TiKVは集計演算をpushdownで処理できる機能を備えており、集計演算は効率的に分散処理されます。HashJoinも同様に分散処理されます。

このようにうまくTiKVに処理を散らすことで、高速に処理ができたのではないかと考えられます。

パフォーマンス改善

次に、パフォーマンスの改善方法について考えてみます。今回の例は created_at で最新のものを取るという簡単な検索条件ですので、そーだいさんのブログで言及されているように、インデックスの効果がありそうです。
とりあえずどちらのクエリでも使われている {created_at, user_id} のインデックスを作ってみます。

index.sql

mysql> alter table history add index idx_created_at_user_id (created_at, user_id);
Query OK, 0 rows affected (48.05 sec)

window関数

window関数の実行計画に変更はなく、実行時間も変わりません。

mysql> explain analyze SELECT * FROM (SELECT history.*,              ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS ranking       FROM history) latest WHERE ranking = 1 limit 100;
+------------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                 | estRows     | actRows  | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                               | operator info                                                                                                                                           | memory   | disk    |
+------------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Limit_14                           | 100.00      | 100      | root      |               | time:17s, loops:2, RU:20467.833327                                                                                                                                                                                                                                                                                                                                                                                                                           | offset:?, count:?                                                                                                                                       | N/A      | N/A     |
| └─Selection_15                     | 100.00      | 100      | root      |               | time:17s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                            | eq(Column#6, ?)                                                                                                                                         | 122.2 KB | N/A     |
|   └─Shuffle_20                     | 100.00      | 99328    | root      |               | time:17s, loops:97, ShuffleConcurrency:5                                                                                                                                                                                                                                                                                                                                                                                                                     | execution info: concurrency:5, data sources:[TableReader_18]                                                                                            | N/A      | N/A     |
|     └─Window_16                    | 100.00      | 103424   | root      |               | time:1m44.4s, loops:102                                                                                                                                                                                                                                                                                                                                                                                                                                      | row_number()->Column#6 over(partition by firsthist.history.user_id order by firsthist.history.created_at desc rows between current row and current row) | N/A      | N/A     |
|       └─Sort_19                    | 10000000.00 | 107520   | root      |               | time:1m44.4s, loops:106                                                                                                                                                                                                                                                                                                                                                                                                                                      | firsthist.history.user_id, firsthist.history.created_at:desc                                                                                            | 248.3 MB | 0 Bytes |
|         └─ShuffleReceiver_21       | 10000000.00 | 10000000 | root      |               | time:1m9.8s, loops:9774                                                                                                                                                                                                                                                                                                                                                                                                                                      |                                                                                                                                                         | N/A      | N/A     |
|           └─TableReader_18         | 10000000.00 | 10000000 | root      |               | time:49ms, loops:9782, cop_task: {num: 291, max: 63.9ms, min: 483.8µs, avg: 38.8ms, p95: 58.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 8.68s, tot_wait: 11.9ms, copr_cache: disabled, build_task_duration: 34.6µs, max_distsql_concurrency: 13}, rpc_info:{Cop:{num_rpc:291, total_time:11.3s}}                                                                                                                                              | data:TableFullScan_17                                                                                                                                   | 49.6 MB  | N/A     |
|             └─TableFullScan_17     | 10000000.00 | 10000000 | cop[tikv] | table:history | tikv_task:{proc max:50ms, min:0s, avg: 26.7ms, p80:40ms, p95:50ms, iters:10920, tasks:291}, scan_detail: {total_process_keys: 10000000, total_process_keys_size: 1142807139, total_keys: 10000291, get_snapshot_time: 5.32ms, rocksdb: {key_skipped_count: 10000000, block: {cache_hit_count: 39879}}}, time_detail: {total_process_time: 8.68s, total_suspend_time: 18.9ms, total_wait_time: 11.9ms, total_kv_read_wall_time: 7.78s, tikv_wall_time: 8.74s} | keep order:false                                                                                                                                        | N/A      | N/A     |
+------------------------------------+-------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
8 rows in set (23.38 sec)

集計関数

集計関数では、作成したインデックスが利用されていました。

mysql> alter table history drop index idx_user_id_created_at;
Query OK, 0 rows affected (0.55 sec)

mysql> explain analyze select h1.* from history h1 join    (select  user_id, max(created_at) as latest from history group by user_id) h2  on h1.created_at = h2.latest and h1.user_id = h2.user_id limit 100;
+------------------------------------+-------------+----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                 | estRows     | actRows  | task      | access object                                                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | operator info                                                                                                                                                                                                                                                       | memory   | disk |
+------------------------------------+-------------+----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Limit_16                           | 100.00      | 100      | root      |                                                                  | time:1.13s, loops:2, RU:11177.269251                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | offset:?, count:?                                                                                                                                                                                                                                                   | N/A      | N/A  |
| └─IndexHashJoin_22                 | 100.00      | 512      | root      |                                                                  | time:1.13s, loops:1, inner:{total:122.5ms, concurrency:5, task:8, construct:13ms, fetch:108.7ms, build:2.3ms, join:777.7µs}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | inner join, inner:IndexLookUp_19, outer key:Column#9, firsthist.history.user_id, inner key:firsthist.history.created_at, firsthist.history.user_id, equal cond:eq(Column#9, firsthist.history.created_at), eq(firsthist.history.user_id, firsthist.history.user_id) | 3.53 MB  | N/A  |
|   ├─Selection_30(Build)            | 100.00      | 10000    | root      |                                                                  | time:1.1s, loops:15                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | not(isnull(Column#9))                                                                                                                                                                                                                                               | 19.0 KB  | N/A  |
|   │ └─HashAgg_36                   | 100.00      | 10000    | root      |                                                                  | time:1.1s, loops:13, partial_worker:{wall_time:1.10178037s, concurrency:5, task_num:98, tot_wait:5.395027701s, tot_exec:113.363927ms, tot_time:5.508474641s, max:1.101718142s, p95:1.101718142s}, final_worker:{wall_time:1.10493501s, concurrency:5, task_num:25, tot_wait:16.524µs, tot_exec:10.326862ms, tot_time:5.522236628s, max:1.104834178s, p95:1.104834178s}                                                                                                                                                                                                                                                                                                                                                                                                             | group by:firsthist.history.user_id, funcs:max(Column#10)->Column#9, funcs:firstrow(firsthist.history.user_id)->firsthist.history.user_id                                                                                                                            | 12.5 MB  | N/A  |
|   │   └─IndexReader_37             | 100.00      | 363373   | root      |                                                                  | time:1.1s, loops:99, cop_task: {num: 100, max: 1.06s, min: 1.44ms, avg: 83.1ms, p95: 748.1ms, max_proc_keys: 1292773, p95_proc_keys: 928256, tot_proc: 8.12s, tot_wait: 21.8ms, copr_cache: disabled, build_task_duration: 27.1ms, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:100, total_time:8.3s}}                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | index:HashAgg_31                                                                                                                                                                                                                                                    | 312.9 KB | N/A  |
|   │     └─HashAgg_31               | 100.00      | 363373   | cop[tikv] |                                                                  | tikv_task:{proc max:1.06s, min:0s, avg: 80.4ms, p80:10ms, p95:750ms, iters:9776, tasks:100}, scan_detail: {total_process_keys: 10000000, total_process_keys_size: 550000000, total_keys: 10000100, get_snapshot_time: 19.7ms, rocksdb: {key_skipped_count: 10000000, block: {cache_hit_count: 712, read_count: 19000, read_byte: 175.8 MB, read_time: 120.9ms}}}, time_detail: {total_process_time: 8.12s, total_suspend_time: 18.6ms, total_wait_time: 21.8ms, total_kv_read_wall_time: 7.06s, tikv_wall_time: 8.18s}                                                                                                                                                                                                                                                             | group by:firsthist.history.user_id, funcs:max(firsthist.history.created_at)->Column#10                                                                                                                                                                              | N/A      | N/A  |
|   │       └─IndexFullScan_35       | 10000000.00 | 10000000 | cop[tikv] | table:history, index:idx_created_at_user_id(created_at, user_id) | tikv_task:{proc max:910ms, min:0s, avg: 70.6ms, p80:10ms, p95:650ms, iters:9776, tasks:100}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | keep order:false                                                                                                                                                                                                                                                    | N/A      | N/A  |
|   └─IndexLookUp_19(Probe)          | 100.00      | 960      | root      |                                                                  | time:94.6ms, loops:12, index_task: {total_time: 37.4ms, fetch_handle: 37.4ms, build: 5.42µs, wait: 12.4µs}, table_task: {total_time: 22.4ms, num: 4, concurrency: 5}, next: {wait_index: 37.8ms, wait_table_lookup_build: 863.5µs, wait_table_lookup_resp: 21.5ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |                                                                                                                                                                                                                                                                     | 32.4 KB  | N/A  |
|     ├─IndexRangeScan_17(Build)     | 100.00      | 1408     | cop[tikv] | table:h1, index:idx_created_at_user_id(created_at, user_id)      | time:37.3ms, loops:12, cop_task: {num: 9, max: 8.75ms, min: 0s, avg: 5.09ms, p95: 8.75ms, max_proc_keys: 288, p95_proc_keys: 288, tot_proc: 28.8ms, tot_wait: 270.3µs, copr_cache: disabled, build_task_duration: 268µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:9, total_time:59ms}, rpc_errors:{context canceled:2}}, tikv_task:{proc max:10ms, min:0s, avg: 6.25ms, p80:10ms, p95:10ms, iters:23, tasks:8}, scan_detail: {total_process_keys: 1184, total_process_keys_size: 65120, total_keys: 2370, get_snapshot_time: 113.3µs, rocksdb: {key_skipped_count: 1184, block: {cache_hit_count: 8302}}}, time_detail: {total_process_time: 28.8ms, total_suspend_time: 479.1µs, total_wait_time: 270.3µs, total_kv_read_wall_time: 50ms, tikv_wall_time: 31.6ms}      | range: decided by [eq(firsthist.history.created_at, Column#9) eq(firsthist.history.user_id, firsthist.history.user_id)], keep order:false                                                                                                                           | N/A      | N/A  |
|     └─TableRowIDScan_18(Probe)     | 100.00      | 960      | cop[tikv] | table:h1                                                         | time:21.4ms, loops:8, cop_task: {num: 44, max: 5.3ms, min: 0s, avg: 899.6µs, p95: 3ms, max_proc_keys: 59, p95_proc_keys: 53, tot_proc: 12.7ms, tot_wait: 18.4ms, copr_cache: disabled, build_task_duration: 262.1µs, max_distsql_concurrency: 10, max_extra_concurrency: 1, store_batch_num: 24}, rpc_info:{Cop:{num_rpc:20, total_time:39.3ms}}, tikv_task:{proc max:10ms, min:0s, avg: 227.3µs, p80:0s, p95:0s, iters:54, tasks:44}, scan_detail: {total_process_keys: 960, total_process_keys_size: 109651, total_keys: 960, get_snapshot_time: 17.6ms, rocksdb: {block: {cache_hit_count: 1979}}}, time_detail: {total_process_time: 12.7ms, total_wait_time: 18.4ms, total_kv_read_wall_time: 10ms, tikv_wall_time: 16.5ms}                                                   | keep order:false                                                                                                                                                                                                                                                    | N/A      | N/A  |
+------------------------------------+-------------+----------+-----------+------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
10 rows in set (1.17 sec)

今回の例では、インデックスは集計でもJOINのキーとしても利用されているので、IndexHashJoinの両側で使われているのがわかります。

ただ、性能はそこまで変わりません。TiDBの場合、IndexもKey-Valueとして保存されるので、今回のようにデータ量がそんなに変わらなければ、Index Full ScanもTable Full Scanもコストはほぼ一緒です。実際のレコードはもっと大きいのでIndex Full ScanとTable full Scanの差は出るかもしれません。

TiFlash(HTAP)

さて、TiDBはTiFlashというカラムナストレージも持っています。これは特に集計演算などの列を軸とする演算に向いています。また、多くの関数を実装しておりpushdownも可能です。こちらの効果を見てみましょう。

まずはTiflashにテーブルをレプリケーションします。

mysql> alter table history set tiflash replica 1;

window関数

mysql> explain analyze SELECT * FROM (SELECT history.*,              ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS ranking       FROM history) latest WHERE ranking = 1 limit 100;
+--------------------------------------+-------------+---------+--------------+---------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                   | estRows     | actRows | task         | access object | execution info                                                                       | operator info                                                                                                                                                            | memory   | disk |
+--------------------------------------+-------------+---------+--------------+---------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Limit_14                             | 100.00      | 100     | root         |               | time:1.17s, loops:2, RU:0.000000                                                     | offset:?, count:?                                                                                                                                                        | N/A      | N/A  |
| └─TableReader_35                     | 100.00      | 1024    | root         |               | time:1.17s, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache: disabled} | MppVersion: 2, data:ExchangeSender_34                                                                                                                                    | 105.3 KB | N/A  |
|   └─ExchangeSender_34                | 100.00      | 0       | mpp[tiflash] |               |                                                                                      | ExchangeType: PassThrough                                                                                                                                                | N/A      | N/A  |
|     └─Selection_17                   | 100.00      | 0       | mpp[tiflash] |               |                                                                                      | eq(Column#6, ?), stream_count: 8                                                                                                                                         | N/A      | N/A  |
|       └─Window_33                    | 100.00      | 0       | mpp[tiflash] |               |                                                                                      | row_number()->Column#6 over(partition by firsthist.history.user_id order by firsthist.history.created_at desc rows between current row and current row), stream_count: 8 | N/A      | N/A  |
|         └─Sort_23                    | 10000000.00 | 0       | mpp[tiflash] |               |                                                                                      | firsthist.history.user_id, firsthist.history.created_at:desc, stream_count: 8                                                                                            | N/A      | N/A  |
|           └─ExchangeReceiver_22      | 10000000.00 | 0       | mpp[tiflash] |               |                                                                                      | stream_count: 8                                                                                                                                                          | N/A      | N/A  |
|             └─ExchangeSender_21      | 10000000.00 | 0       | mpp[tiflash] |               |                                                                                      | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: firsthist.history.user_id, collate: binary], stream_count: 8                                           | N/A      | N/A  |
|               └─TableFullScan_20     | 10000000.00 | 0       | mpp[tiflash] | table:history |                                                                                      | keep order:false                                                                                                                                                         | N/A      | N/A  |
+--------------------------------------+-------------+---------+--------------+---------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
9 rows in set (1.25 sec)

TiKV、TiDBで行っていた処理のほとんどをTiFlashで実行しています。処理時間も20秒あまりから1.25秒と、劇的に改善しました。

集計関数

mysql> explain analyze select h1.* from history h1 join    (select  user_id, max(created_at) as latest from history group by user_id) h2  on h1.created_at = h2.latest and h1.user_id = h2.user_id limit 100;
+--------------------------------------------+-------------+----------+--------------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                         | estRows     | actRows  | task         | access object                                               | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | operator info                                                                                                                                                                                                                                                       | memory  | disk |
+--------------------------------------------+-------------+----------+--------------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Limit_16                                   | 100.00      | 100      | root         |                                                             | time:158.4ms, loops:2, RU:2033.104218                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | offset:?, count:?                                                                                                                                                                                                                                                   | N/A     | N/A  |
| └─IndexHashJoin_24                         | 100.00      | 1024     | root         |                                                             | time:158.4ms, loops:1, inner:{total:362.5ms, concurrency:5, task:6, construct:9.05ms, fetch:352.2ms, build:1.62ms, join:1.15ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | inner join, inner:IndexLookUp_21, outer key:Column#9, firsthist.history.user_id, inner key:firsthist.history.created_at, firsthist.history.user_id, equal cond:eq(Column#9, firsthist.history.created_at), eq(firsthist.history.user_id, firsthist.history.user_id) | 2.77 MB | N/A  |
|   ├─TableReader_90(Build)                  | 100.00      | 10000    | root         |                                                             | time:88.8ms, loops:18, cop_task: {num: 9, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache: disabled}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | MppVersion: 2, data:ExchangeSender_89                                                                                                                                                                                                                               | 19.7 KB | N/A  |
|   │ └─ExchangeSender_89                    | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:80.2ms, loops:8, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | ExchangeType: PassThrough                                                                                                                                                                                                                                           | N/A     | N/A  |
|   │   └─Selection_51                       | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:80.2ms, loops:8, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | not(isnull(Column#9))                                                                                                                                                                                                                                               | N/A     | N/A  |
|   │     └─Projection_85                    | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:80.2ms, loops:8, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | Column#9, firsthist.history.user_id                                                                                                                                                                                                                                 | N/A     | N/A  |
|   │       └─HashAgg_86                     | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:80.2ms, loops:8, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | group by:firsthist.history.user_id, funcs:max(Column#18)->Column#9, funcs:firstrow(firsthist.history.user_id)->firsthist.history.user_id, stream_count: 8                                                                                                           | N/A     | N/A  |
|   │         └─ExchangeReceiver_88          | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:80.2ms, loops:8, threads:8}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | stream_count: 8                                                                                                                                                                                                                                                     | N/A     | N/A  |
|   │           └─ExchangeSender_87          | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:86ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: firsthist.history.user_id, collate: binary], stream_count: 8                                                                                                                                      | N/A     | N/A  |
|   │             └─HashAgg_83               | 100.00      | 10000    | mpp[tiflash] |                                                             | tiflash_task:{time:86ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | group by:firsthist.history.user_id, funcs:max(firsthist.history.created_at)->Column#18                                                                                                                                                                              | N/A     | N/A  |
|   │               └─TableFullScan_41       | 10000000.00 | 10000000 | mpp[tiflash] | table:history                                               | tiflash_task:{time:36ms, loops:159, threads:8}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:13, remote_regions:0, tot_learner_read:2ms, region_balance:{instance_num: 1, max/min: 13/13=1.000000}, delta_rows:0, delta_bytes:0, segments:11, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:1ms, min_local_stream:9ms, max_local_stream:29ms, dtfile:{data_scanned_rows:10000000, data_skipped_rows:0, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:0ms, tot_read:119ms}}                                                                                                                                                                                | keep order:false                                                                                                                                                                                                                                                    | N/A     | N/A  |
|   └─IndexLookUp_21(Probe)                  | 100.00      | 1250     | root         |                                                             | time:340.3ms, loops:8, index_task: {total_time: 172.8ms, fetch_handle: 172.8ms, build: 3.65µs, wait: 16.6µs}, table_task: {total_time: 10.9ms, num: 3, concurrency: 5}, next: {wait_index: 107.9ms, wait_table_lookup_build: 930.4µs, wait_table_lookup_resp: 7.8ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                                                                                                     | 73.2 KB | N/A  |
|     ├─IndexRangeScan_19(Build)             | 100.00      | 4387     | cop[tikv]    | table:h1, index:idx_created_at_user_id(created_at, user_id) | time:172.7ms, loops:7, cop_task: {num: 17, max: 44.6ms, min: 0s, avg: 19.8ms, p95: 44.6ms, max_proc_keys: 480, p95_proc_keys: 480, tot_proc: 84.9ms, tot_wait: 5.8ms, copr_cache: disabled, build_task_duration: 9.08ms, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:17, total_time:344ms}, rpc_errors:{context canceled:1}}, tikv_task:{proc max:10ms, min:0s, avg: 6.47ms, p80:10ms, p95:10ms, iters:50, tasks:17}, scan_detail: {total_process_keys: 3907, total_process_keys_size: 214885, total_keys: 7823, get_snapshot_time: 5.43ms, rocksdb: {key_skipped_count: 3907, block: {cache_hit_count: 19577, read_count: 1, read_byte: 32.0 KB, read_time: 46µs}}}, time_detail: {total_process_time: 84.9ms, total_suspend_time: 3ms, total_wait_time: 5.8ms, total_kv_read_wall_time: 100ms, tikv_wall_time: 103.9ms}  | range: decided by [eq(firsthist.history.created_at, Column#9) eq(firsthist.history.user_id, firsthist.history.user_id)], keep order:false                                                                                                                           | N/A     | N/A  |
|     └─TableRowIDScan_20(Probe)             | 100.00      | 1250     | cop[tikv]    | table:h1                                                    | time:8.96ms, loops:5, cop_task: {num: 33, max: 3.92ms, min: 0s, avg: 1.09ms, p95: 3.63ms, max_proc_keys: 112, p95_proc_keys: 104, tot_proc: 15.4ms, tot_wait: 13.4ms, copr_cache: disabled, build_task_duration: 230.5µs, max_distsql_concurrency: 11, store_batch_num: 8}, rpc_info:{Cop:{num_rpc:25, total_time:48.3ms}, rpc_errors:{context canceled:11}}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:38, tasks:22}, scan_detail: {total_process_keys: 1250, total_process_keys_size: 142970, total_keys: 1250, get_snapshot_time: 12.7ms, rocksdb: {block: {cache_hit_count: 3239, read_count: 1, read_byte: 32.0 KB, read_time: 16.1µs}}}, time_detail: {total_process_time: 15.4ms, total_suspend_time: 75.3µs, total_wait_time: 13.4ms, tikv_wall_time: 19.8ms}                                         | keep order:false                                                                                                                                                                                                                                                    | N/A     | N/A  |
+--------------------------------------------+-------------+----------+--------------+-------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
14 rows in set (0.21 sec)

なんと200msになりました。集計演算はTiFlashで行い、HashJoinのテーブル側はTiKVで行うという合せ技です。TiFlashのデータ断面はTiKVと一致しているため、同じ実行計画の中で両方のエンジンを使うことも可能です。

おわりに

最新のレコードを引くというテーマをTiDBで実行してみました。今回おこなった改善案はごく一部に過ぎませんし、汎用的なものではありませんが、様々な効率化手段を試してみるきっかけになればと思います。