单表五亿数据的查询优化 | Mysql、StarRocks
本次测试所用服务器硬件配置如下，此机器除了 Mysql 和 StartRocks 还部署了其它很多 Docker 服务。
CPU：
AMD Ryzen™ 7 8745H w/ Radeon™ 780M Graphics × 16内存：
DDR5 5600 MT/S 32G(16G*2)磁盘性能：
Timing cached reads: 64174 MB in 1.99 seconds = 32256.62 MB/sec Timing buffered disk reads: 3562 MB in 3.00 seconds = 1186.39 MB/sec初始化数据库环境
Mysql：
CREATE TABLE `users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL, `phone` char(11) NOT NULL, `email` varchar(64) NOT NULL, `gender` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '0未知 1男 2女', `age` tinyint(3) unsigned NOT NULL DEFAULT 0, `status` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1正常 2禁用 3注销', `province_id` smallint(5) unsigned NOT NULL DEFAULT 0, `city_id` mediumint(8) unsigned NOT NULL DEFAULT 0, `register_source` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1web 2ios 3android 4api', `score` int(10) unsigned NOT NULL DEFAULT 0, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, `last_login_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_phone` (`phone`), KEY `idx_status_created_id` (`status`,`created_at`,`id`), KEY `idx_created_at_id` (`created_at`,`id`), KEY `idx_email` (`email`), KEY `users_created_at_index` (`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=935300001 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMICStarRocks：
CREATE TABLE `users` ( `id` bigint NOT NULL COMMENT "", `created_at` datetime NOT NULL COMMENT "", `username` varchar(32) NOT NULL COMMENT "", `phone` varchar(11) NOT NULL COMMENT "", `email` varchar(64) NOT NULL COMMENT "", `gender` tinyint NOT NULL DEFAULT "0" COMMENT "", `age` tinyint NOT NULL DEFAULT "0" COMMENT "", `status` tinyint NOT NULL DEFAULT "1" COMMENT "", `province_id` smallint NOT NULL DEFAULT "0" COMMENT "", `city_id` int NOT NULL DEFAULT "0" COMMENT "", `register_source` tinyint NOT NULL DEFAULT "1" COMMENT "", `score` int NOT NULL DEFAULT "0" COMMENT "", `updated_at` datetime NOT NULL COMMENT "", `last_login_at` datetime NULL COMMENT "" ) ENGINE=OLAP PRIMARY KEY(`id`, `created_at`) PARTITION BY RANGE(`created_at`) ( START ("2020-01-01") END ("2026-12-31") EVERY (INTERVAL 1 MONTH) ) DISTRIBUTED BY HASH(`id`) BUCKETS 8 PROPERTIES ( "replication_num" = "1" );Python 写入数据的脚本：
from concurrent.futures import ThreadPoolExecutor, as_completed from datetime import datetime, timedelta import pymysql HOST = "192.168.1.1" PORT = 3306 // 或 9030 USER = "root" PASSWORD = "123456" DATABASE = "testdata" START_ID = 1 TOTAL_ROWS = 500_000_000 WORKERS = 8 BATCH_SIZE = 10_000 BASE_TIME = datetime(2024, 1, 1, 0, 0, 0) INSERT_SQL = """ INSERT INTO users ( id, username, phone, email, gender, age, status, province_id, city_id, register_source, score, created_at, updated_at, last_login_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) """ def make_conn(): return pymysql.connect( host=HOST, port=PORT, user=USER, password=PASSWORD, database=DATABASE, charset="utf8mb4", autocommit=False, read_timeout=300, write_timeout=300, connect_timeout=30, ) def build_rows(start_id: int, end_id: int): rows = [] for n in range(start_id, end_id): created_at = BASE_TIME + timedelta(seconds=n % 31_536_000) updated_at = created_at last_login_at = created_at + timedelta(days=n % 30) rows.append(( n, f"user_{n}", f"1{n:010d}", f"user_{n}@test.local", n % 3, 18 + (n % 43), 2 if n % 20 == 0 else 1, (n % 34) + 1, (n % 340) + 1, (n % 4) + 1, n % 100000, created_at.strftime("%Y-%m-%d %H:%M:%S"), updated_at.strftime("%Y-%m-%d %H:%M:%S"), last_login_at.strftime("%Y-%m-%d %H:%M:%S"), )) return rows def worker(worker_no: int, start_id: int, end_id: int): conn = make_conn() inserted = 0 try: with conn.cursor() as cur: current = start_id while current <= end_id: next_id = min(current + BATCH_SIZE, end_id + 1) rows = build_rows(current, next_id) cur.executemany(INSERT_SQL, rows) conn.commit() inserted += len(rows) current = next_id if inserted % 100000 == 0 or current > end_id: print(f"worker={worker_no} inserted={inserted} range={start_id}-{end_id}") finally: conn.close() def split_ranges(start_id: int, total_rows: int, workers: int): base = total_rows // workers remain = total_rows % workers current = start_id result = [] for i in range(workers): size = base + (1 if i < remain else 0) s = current e = current + size - 1 result.append((i + 1, s, e)) current = e + 1 return result def main(): ranges = split_ranges(START_ID, TOTAL_ROWS, WORKERS) print("ranges:", ranges) with ThreadPoolExecutor(max_workers=WORKERS) as pool: futures = [pool.submit(worker, worker_no, s, e) for worker_no, s, e in ranges] for future in as_completed(futures): future.result() print("done") if __name__ == "__main__": main()5亿条数据到底占多大空间
本次测试中，MySQL实际写入数据量为4.6亿条（因测试过程中未完成5亿条写入），StarRocks按计划写入5亿条数据，以下为两者的存储占用详情。
对于 Mysql：
8.0K ./testdata/users.frm 136G ./testdata/users.ibd 4.0K ./testdata/db.opt 136G ./testdataStarRocks 是 256G。
Mysql 表里面创建了比较多的索引，通过以下 SQL 可以获取表的索引以及索引数据占用的空间：
SELECT TABLE_NAME AS 表名, CONCAT(ROUND((INDEX_LENGTH / 1024 / 1024), 2), ' MB') AS 索引大小, CONCAT(ROUND((DATA_LENGTH / 1024 / 1024), 2), ' MB') AS 数据大小, CONCAT(ROUND(((INDEX_LENGTH + DATA_LENGTH) / 1024 / 1024), 2), ' MB') AS 总大小 FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdata' AND TABLE_NAME = 'users';| 指标 | 数值 | 换算 |
|---|---|---|
| 索引大小 | 79943.56 MB | ≈78.07 GB |
| 数据大小 | 51887.00 MB | ≈50.67 GB |
| 总大小 | 131830.56 MB | ≈128.74 GB |
笔者这台机器部署了很多服务，所以实际上日常运行 Mysql、StarRocks 两个数据库应该不需要 10G 内存。
统计数据量
大数据量下，全表数据量统计（select count(*)）是业务中常见场景，以下为MySQL与StarRocks的性能对比，每组测试重复3次，取平均值以减少偶然误差。
select count(*) from users;在 Mysql 里面，统计数据量是大坑，需要 1-2 分钟。
[2026-04-11 14:21:22] 在 1 m 11 s 165 ms (execution: 1 m 11 s 153 ms, fetching: 12 ms) 内检索到从 1 开始的 1 行StarRocks 只需要 260ms。
[2026-04-11 14:20:29] 在 261 ms (execution: 254 ms, fetching: 7 ms) 内检索到从 1 开始的 1 行所以对在业务系统中统计数据量是非常麻烦的一个事情，如果只是需要知道全表数据量，有很多方法可以做，例如单独设计数据量统计表、Redis 记录数据量等，但是往往页面显示数据量时需要分页、搜索、筛选，会导致在大数据量下耗时非常长。
因此在大数量时读写分离很有必要，统计数据量、分页查询大小、join 条件等通过 StarRocks 来操作。
有索引会多块
数据库本身已有以下索引：
一开始 AI 给我生成表的时候，我在想为什么有的索引只包含字段，有的列把id也包进去了，查了资料发现 InnoDB 二级索引默认含主键，所以实际上(created_at,id)跟(created_at)是一样的，但是在排序方面有区别，因为索引的数据是会排序的。
以下场景显式定义(col, id)有明显收益：
- 查询包含
ORDER BY col, id； - 查询需要按
col分页（如WHERE col > x ORDER BY col, id LIMIT n），id可用于稳定分页顺序； col的区分度很低（如大量重复值），加上id可提高索引的 “区分度”，优化索引查找效率。
回归正题，在使用主键的情况下，Mysql 读取 1000 条数据：
select * from users where id in (...);[2026-04-12 08:52:56] 在 113 ms (execution: 79 ms, fetching: 34 ms) 内检索到从 1 开始的 64 行所以在表数据量非常大时，完全可以在 StarRocks 执行一些查询操作，最终获取一份数据 id 后在 Mysql 业务数据库查询数据做业务处理。
对于手机号这种字符串字段，如果做了索引，其实各种查询操作也不会慢的。
select * from users where phone like '1000%' order by phone desc limit 100 offset 10; select * from users where phone like '%1000%' order by phone desc limit 100 offset 10;Mysql:
[2026-04-11 14:23:05] 在 48 ms (execution: 13 ms, fetching: 35 ms) 内检索到从 1 开始的 100 行 [2026-04-12 09:11:44] 在 375 ms (execution: 347 ms, fetching: 28 ms) 内检索到从 1 开始的 100 行StarRocks：
[2026-04-12 09:08:09] 在 576 ms (execution: 546 ms, fetching: 30 ms) 内检索到从 1 开始的 100 行 [2026-04-12 09:11:36] 在 822 ms (execution: 789 ms, fetching: 33 ms) 内检索到从 1 开始的 100 行上面的测验可以说明几个问题。
对于字符串，走前缀区配时，like 'xxx%'性能性能也会非常好，4.6 亿数据执行时间只需要 13ms。
只有前缀匹配like 'xxx%'，才能真正利用索引做范围扫描（range）B + 树可以直接定位到前缀匹配的起始位置，只扫描符合范围的索引节点，所以%xxx%这种走不了索引会导致全盘扫描，导致 MySQL 执行耗时从 13ms 暴涨到 347ms，性能下降了 26 倍。
不过对于 StarRocks，StarRocks 是 OLAP 引擎，默认的前缀索引对like '%x%'完全无效，所以查询都会比 Mysql 慢。
对于字符串等场景，如果设计的查询方案可以走索引，那么即使数据量很大，其实也不需要担心查询时间。
优化筛选查询
单是用户表，在业务需求中往往需要对手机号、用户名、邮箱等进行模糊查询，like %xxx%这种情况必然会出现，我们不可能让产品经理改需求，但是无论在 Mysql 还是 StarRocks 使用like %xxx%在大数据量时耗时都会变大，所以我们需要找到一种方式，既可以满足产品对于订单、用户表等多个动态字段模糊搜索，又要让查询速度变快。
select * from users where phone like '%1000%' or email like '%user_10%' or username like '%user_11%' order by phone desc limit 100 offset 100;Mysql:
[2026-04-12 09:31:37] 在 912 ms (execution: 880 ms, fetching: 32 ms) 内检索到从 1 开始的 100 行StarRocks:
[2026-04-12 09:32:24] 在 1 s 588 ms (execution: 1 s 557 ms, fetching: 31 ms) 内检索到从 1 开始的 100 行StarRocks/Doris 支持ngram 分词倒排索引，原理和 ES 类似，但直接集成在数仓引擎中，避免了数据同步的麻烦，适合分析场景。但是经过笔者测试，where phone like '%1000%' or email like '%user_10%' or username like '%user_11%'走不了索引，查询速度也好慢。
无论是 Mysql 还是 StarRocks 在多条件模糊查询时，由于索引机制，都会导致查询速度缓慢，最后只能使用 ElasticSearch 做模糊查询，ElasticSearch 这方面非常强。
POST /users/_search { "from": 0, "size": 10, "query": { "bool": { "should": [ { "match": { "phone": "1000" }}, { "match": { "email": "user_10" }}, { "match": { "username": "user_11" }} ], "minimum_should_match": 1 } } }一个查询只能使用一个索引
在 Mysql 的 users 表中，我们给很多字段设置了索引，包括创建时间idx_created_at_id。
PRIMARY KEY (`id`), UNIQUE KEY `uk_phone` (`phone`), KEY `idx_status_created_id` (`status`,`created_at`,`id`), KEY `idx_created_at_id` (`created_at`,`id`), KEY `idx_email` (`email`)如果我们按时间来排查范围，有idx_created_at_id的加持，下面的 SQL 执行速度会不会非常快？
select * from users where phone like '1000%' and created_at > '2024-04-25 17:46:20' order by phone desc limit 100 offset 10;然而实际测试，Mysql：
[2026-04-11 11:55:19] 在 24 s 86 ms (execution: 24 s 66 ms, fetching: 20 ms) 内检索到从 1 开始的 9 行StarRocks：
在 19 s 578 ms (execution: 19 s 561 ms, fetching: 17 ms) 内检索到 0 行但是我不是给 phone、create_at 都创建索引了嘛，为什么还会这么慢？先看执行计划。
确实有两个索引：
uk_phone(phone)idx_created_at_id(created_at, id)
但问题是 MySQL 通常只能选一个 “最有用” 的索引，然后回表过滤其他条件。所以