慢查询日志里挂在最上面的那条语句,我通常不急着看它写得多绕,先 SHOW CREATE TABLE 把建表语句拉出来。快照增量表这种结构,一次备份追加一批记录,看着给个自增 ID 就能跑得挺欢实,真到表里攒到一定体量、查询开始转圈,问题十有八九埋在 PRIMARY KEY 后面那半行。主键选错不是语法层面的错,是数据在磁盘上排队的方式错了,后面你再加三五个索引也很难补回来。

快照增量表的主键,决定了数据在磁盘上怎么排队
InnoDB 是聚簇索引,行数据本身就挂在主键底下,主键相邻的记录在物理页上也挨着,这个常识大家都知道,可用到自己表上就忘了。快照增量表最省事的写法无非 id BIGINT AUTO_INCREMENT PRIMARY KEY,再挂 snapshot_id、device_id、file_path、created_at 几个普通列,写入顺着追加,页分裂少,看着挺美。麻烦在于你平时根本不按 id 查——业务要的是「某次快照里某台机器某个路径的最新一条增量」,或者「这台设备最近几天的变更记录」,条件全落在二级索引上;二级索引的叶子节点只存主键值,拿到值还要回表,回表是随机的。表一大、buffer pool 装不下,随机 IO 能把一次本该毫秒级返回的查询拖到让人怀疑网络。主键顺序决定读放大。
说白了,主键就是行数据在磁盘上的门牌号。门牌号排乱了,找谁都得挨家敲门。把主键直接设成 (snapshot_id, device_id, created_at),同样一条查询,就是一次范围扫描,物理相邻,读几个页就完事。主键顺序选对了,范围扫描才走得顺;不然,每次查询都在跳页。就这么回事。
二级索引回表,主键越长亏得越多
主键除了决定顺序,还决定体积。用 UUID 或者一长串 varchar 当主键,看着「全局唯一、迁移方便」,代价是每一个二级索引的叶子节点都要把这个 36 字节的东西抄一遍,一张表挂四五个二级索引,等于凭空多存几份主键。同样的行数,索引页多了,能缓存在内存里的热数据就少了,物理读自然往上走。自增 BIGINT 只占 8 字节,这个账不难算吧。
还有页分裂这档子事。UUID 是随机的,新插入的行落在 B+ 树中间某个位置的概率很大,页满了就得拆,拆完留下半空的页,碎片一多,范围扫描读的页数比数据量本身还夸张。自增主键顺序追加,几乎不拆页。这一条在写入阶段的差别就很明显了,别等查询慢了才回头怪索引没建对嘛。
复合主键的列顺序,别按习惯从左往右随手写
确定要把主键做成复合的,那顺序就得跟着查询走,不是跟着字段表走。快照增量表的查询模式通常就两种:按 snapshot_id 拉一批,或者按 device_id 加时间取最近。你要是写成 (device_id, snapshot_id),一次快照的全量增量在磁盘上是散的,扫描要跳着读;换成 (snapshot_id, device_id, created_at),同一次快照的记录挨在一起,范围扫描读的页数能少一大截,差距不是一点半点啊。
最左前缀这个老规矩也得提一句。复合主键 (snapshot_id, device_id),你只用 device_id 去查,索引用不上,就是全表扫。这不是数据库脾气不好,是你让人家没法用啊。要是业务里两种查询都要跑,与其纠结主键顺序,不如老老实实建一个二级索引,别在这上头抠这点空间呗。
时间戳当主键,是另一种常见错法
有人图省事,直接拿 created_at 做主键,觉得天然有序。可同一秒里写进来的一批增量,时间戳是一样的,要么冲突,要么加毫秒、加序号凑合,凑到最后主键长度又上去了。更麻烦的是归档——按时间删老数据本来是最顺手的操作,主键前缀如果不是 snapshot_id 或者时间,DELETE FROM t WHERE snapshot_id < ? 这种语句就得全表扫,删一批数据把库拖住半天,这事儿我见过不止一次啊。归档删除同样吃主键。
顺带说一句,增量表的数据不是一直留着,老快照迟早要清,删除路径在建表那天就该一起想清楚,别等磁盘吃紧了临时抱佛脚。
我一般怎么定位这张表的问题
上手先开慢查询,long_query_time 调低一点,跑一天,用 pt-query-digest 把摘要拉出来,看哪条 SQL 的平均耗时和扫描行数最离谱;接着 EXPLAIN 那条语句,type 是 ALL 还是 ref、rows 估出来是多少、Extra 里有没有 Using filesort 和 Using temporary,基本就能看出索引有没有被用上。然后 SHOW INDEX FROM 表名,看 Cardinality 那几个数是不是常年不更新,统计信息过期也会让优化器选错路,ANALYZE TABLE 跑一下也不丢人嘛。再往下就是 iostat 和 performance_schema,看物理读是不是集中在某几个数据文件上,buffer pool 命中率掉没掉,心里就有数了呗。这套流程走完,主键该不该改,心里基本就有谱了。
老表要改主键,别指望一句 ALTER 收工
线上表几千万行,直接 ALTER TABLE … DROP PRIMARY KEY, ADD PRIMARY KEY 是能写,可锁和时长得提前摸清楚,MySQL 5.6 之后虽然有 online DDL,主键变更这类操作未必都能走 INPLACE,具体还得看版本和存储引擎,拿不准就以官方文档为准。稳妥点的做法是建一张新表,主键按新顺序定好,业务双写一段时间,再用脚本按主键区间回填历史数据,最后切读、切写、改表名。数据量不算夸张的,夜里找个窗口停机导一遍反而省心,别硬撑着上。
改完也别急着收工,统计信息重建一下,慢查询再盯两天,确认那条语句真的走范围扫描了呢。
快照增量表这东西,写的时候图省事,查的时候就得还债。主键选的是数据的物理顺序——不是给主键列看的,是给你那几条高频查询看的。上线前,把查询模式写下来,对照着定主键。比事后加索引、拆表、迁数据都便宜。这道理放别的表上也一样成立,只是快照增量表的数据涨得快,债来得更早一点吧。
相关阅读
【版权声明】:本站内容若无意侵犯到您的权利,请及时与我们联系将尽快删除相关内容!