大表加索引怎么不锁表
时间:2026-11-16 19:45:25 来源:本站整理 作者:testai 热度: 0 次
几千万行的表上加一个索引,白天直接跑,页面转圈客服电话就来了。多数时候不是索引有多重,而是建的过程中把业务请求挡在了门外。想做到不锁表,得先弄清是哪种锁在挡路、数据库版本支持到什么程度、磁盘和内存还剩多少余量,这几件事弄明白再动手,很多问题能提前避开。
几千万行的表上加一个索引,白天直接跑,页面立刻转圈,客服电话就跟着来了。这种场面大多不是索引本身有多重,而是加索引期间把业务请求挡在了门外。想做到不锁表,得先弄清是哪种锁在挡路、数据库版本支持到什么程度、磁盘和内存还剩多少余量,这几件事弄明白再动手,很多问题能在上线前就避开。
加索引为什么会卡住业务
建索引的过程要扫全表、排序、写新页,这段动作本身不吃业务锁,真正卡人的是开始和收尾那两个瞬间。开始时要拿表的元数据锁,收尾时要把新索引挂到表定义上,这两个时刻一旦有长事务占着,等待队列就会一路排下去。,这段时间业务能正常读写,但数据库的 CPU 和 IO 会被吃掉一块,QPS 高的库能感觉到响应慢半拍。
排队最怕放大。元数据锁是排队的,一个请求等不到就压着后面的所有请求,本来是几毫秒的事,几秒钟就能演变成整站超时。业务侧看到的现象往往是数据库连接数突然打满,而不是某条 SQL 变慢。,这类故障排查起来容易跑偏,看数据库日志只会看到元数据锁等待,看不到是哪条业务语句在前面挡着。
老版本的建索引是复制表。先建一张空表,把数据一行行拷过去,拷完再改名换回来。这期间写入会被阻塞,表越大停得越久,几千万行停十几分钟很常见,这也是很多人对加索引有心理阴影的来源。,中间还需要额外的磁盘空间,表大的时候光拷数据就要走很久。
新版本改成了原地建。数据页边扫边写,中间留出日志记录变更,收尾时再把变更追平。阻塞窗口从几十分钟缩到秒级,前提是版本到位、参数配好,缺一样就退回到老路径。
临时空间要算够。建索引过程中产生的排序文件和临时表都落在磁盘上,索引列多、数据量大时占用能到表体积的一两倍,空间不够会中途失败,失败后还得清理残留文件。,空间估算可以按表数据量的一点五倍给,压缩表再适当放宽。
先弄清是锁表还是锁元数据
看当前的等待事件。会话列表里如果大量请求停在等元数据锁的状态,说明卡在开始或收尾那一下;如果停在等行锁,那是业务自己的事务在互相等,跟建索引没关系。两种等待的处理方式完全不同,前者要错开建索引的时间,后者得回去改业务 SQL。判断不准就两种手段一起上,先把建索引的窗口挪开,再看行锁有没有减少,现象变化能反推出真实原因。查询语句里带上等待时长和来源主机,一眼就能看出是哪台应用机在排队,省去逐个排查的功夫。等待时长超过五秒的会话优先处理,这类请求已经在影响用户,几分钟的锁定就够让监控报警。
找占着锁的长事务。按事务开启时间排序,把跑了很久还没提交的会话列出来,它们就是挡路的那个。建索引之前先确认没有长事务,比建的时候再救火容易得多。长事务的来源通常是几类:大批量更新的定时任务、没设超时的外部接口调用、手动开着的交互式会话。前两类可以调时间或加超时,第三类最容易忽略,某个人开着窗口跑了条查询去吃饭,锁就一直挂着,等到有人反馈卡顿才想起来。把这些来源列成一张清单,定期巡检,建索引前的检查就变成了照着清单核对,不用临场慌。清单之外再设一道自动检查,执行改表脚本前先跑一次,有长事务就拦住,人工失误的概率能压到很低。
在线加索引的几种做法
用数据库自带的在线建索引能力。指定算法和锁模式,让它走不阻塞写的路径,执行时如果发现条件不满足会直接报错,而不是悄悄退化成锁表版本,这一点比默认行为安全。
第三方在线改表工具适合更复杂的场景。它先建影子表、同步增量、再原子切换,整个过程业务无感。代价是要多一份磁盘空间,还要盯住增量同步的延迟,延迟太大切换时会长时间锁表。
低峰期直接建也是可行路线。数据量在千万级以内、写入不密集的表,挑凌晨跑一次,几分钟就完事,没必要上工具。关键是提前在测试库按真实数据量跑一遍,拿到真实耗时。
先把索引建在从库上。从库建完再切主从角色,主库那边的窗口就省下来了。这个做法要求复制链路健康,切换前确认从库追平,切换动作本身也有风险,得演练过。
分区表可以逐个分区建。一次只动一个分区,锁的范围小,失败重来的代价也小。缺点是分区数量多时操作次数翻倍,管理成本上升。
建索引和改表结构尽量别塞在一次发布里。两件事叠加,锁的持有时间会互相放大,回滚也更麻烦。分开做,出问题时能一眼看出是哪一步引起的。工具和版本要匹配。某些在线改表工具在老版本上对字符集、外键的支持有坑,升级前先在测试库验证,别拿生产当试验田。
建索引的机器配置别抠。这套动作吃 CPU 排序、吃内存缓冲、吃磁盘顺序写,64G 内存、20 核、千兆以上带宽的机型按 这个渠道 配下来,跑在线改表不会成为瓶颈,业务侧的抖动也小很多。
大表加索引前先量什么
量表的真实体积。行数只是估算,真正决定耗时的是数据页数量和索引列的选择度。用统计信息看平均行长度,再乘行数,得到的数字比想象中更接近实际。,统计信息不准时就抽几页看实际占用,别拿建表时预估的数字当依据。
量磁盘剩余。临时空间、影子表、日志文件三处都要算进去,留出两倍余量比较稳。空间紧张时先清归档日志,别等到建到一半失败。,临时空间最好单独一块盘,和业务数据盘分开,避免互相拖累。,临时空间不够时宁可推迟操作,也别拿生产库冒险。,留不够就别开工,先腾地方再排期。
量业务低峰。把最近一周的 QPS 曲线拉出来,找真正的最低点。很多团队以为凌晨三点最闲,实际凌晨有批量任务,反而是白天的午休时段更空。,把发布窗口、备份任务、定时报表的时间一起标在图上,重叠的时段要避开。,低峰这个结论要用数据支撑,不能凭作息习惯判断。
量长事务的分布。统计一下超过十秒的事务有多少、集中在哪些接口。这些事务是元数据锁等待的主要来源,建索引前把它们改掉或错开,比任何工具都有效。,改不掉的长事务至少要在建索引期间临时停掉,或者把执行时间挪到窗口之外。,分布图按小时画,能看出哪些时段天生不适合动手。,这份统计放在监控里比放在文档里更管用。
内存和临时空间要留够
缓冲池要给足。建索引的排序过程尽量在内存里完成,落到磁盘就会慢一个数量级。把缓冲池按机器内存的六到七成配,剩下的留给系统和连接。,缓冲池不够时排序会落盘,耗时可能差三五倍,窗口一旦拉长,出问题的概率就上去了。
临时表空间单独挂盘。和业务数据盘分开,避免排序文件把数据盘的 IO 打满,进而拖慢正常查询。独立盘哪怕小一点,隔离效果也很明显。,盘不必很大,独立比容量更重要,隔离住 IO 才是目的。
日志文件别设太小。建索引期间产生的变更要写日志,日志空间不足会强制刷盘,写入抖动会直接传导到业务。按平时写入量的两三倍留。,建索引期间的日志量比平时高,预留不足会让刷盘频率突然抬升。
连接数提前压一压。建索引时数据库的并发能力会下降,把应用侧的最大连接数临时调低,宁可排队也别把数据库压垮,排队能被限流兜住,压垮就是全站不可用。,这一步在应用侧配置中心改就行,建完再调回来,恢复动作要写进操作单。
监控盯三个指标。磁盘剩余、临时空间占用、元数据锁等待时长,三个一起看能提前发现问题。等业务侧报错才看,已经晚了。,告警阈值按平时基线给,不要用绝对值,不同库的体量差太多。,三个指标放在同一块面板上,值班的人一眼就能对上号。,阈值一旦触发就暂停操作,别抱着侥幸继续跑。
加完怎么确认生效
先看索引有没有真的建出来。查表结构,确认索引名称、列顺序、索引类型都对。列顺序错了索引等于白建,执行计划根本不会走它。,同时确认索引统计信息已更新,没更新的统计会让优化器继续走旧路径。
再看执行计划有没有变。把目标查询拿出来跑一次,看是否命中新索引、扫描行数降了多少。只建索引不看计划,很容易出现建了不用的情况。,扫描行数、使用的索引、回表次数,三项一起对比才算看全。
对比建索引前后的慢查询清单。原来排在前面的几条是否消失,新增的慢查询有没有出现。索引会改变优化器的选择,偶尔会把别的查询带偏。,清单按累计耗时排,比按单次耗时排更能反映真实影响。
观察写入延迟的变化。多一个索引,每次写入要多维护一棵树,写入 QPS 高的表能感觉到差异。延迟上升明显的话,要考虑索引是不是可以合并或去掉。,延迟曲线在建索引之后的几个小时内看最准,等一天过去再回头看,波动已经混进其他因素。
清理临时残留。在线改表工具留下的影子表、触发器、临时文件都要确认清掉,留着会持续消耗写入性能,还会在下次操作时冲突。,清理前先确认没有正在运行的改表任务,误删会导致操作中断。
把这次的耗时和参数记下来。下次同量级的表可以直接估算,不用再赌一次。运维经验就是这么一条条攒出来的。,包括表体积、索引列、并发设置和实际耗时,写成一张对照表,下次直接查表估时间。
演练环境要常备一台。改表、加索引这类操作先在演练库跑通,8 核 8G、NVMe 的规格按 这里 看就够中小库用,提前跑一遍能把大部分意外挡在生产之外。
热门新闻
-
MySQL慢查询日志怎么看
接口变慢先别急着加机器,多半是数据库那层出了问题:同一条 SQL 昨天几十毫秒今天两秒,数据量涨了、索引失效了、执行计划换了个更差的都可能。慢查询日志能把这些翻出来,但参数设错等于白开,日志收上来不会读也等于没开。这里从开关、阈值、筛选一直讲到压测验证,把常见的漏配点和误判一并说清。
发布日期:2026-11-16 08:25:04
-
CDN防盗链怎么防资源被刷
图片站最怕的不是被打,是账单莫名其妙涨上去:流量跑得比访问量快好几倍,翻日志才发现热门图被别的站直接挂走了,或者有人换着 UA 批量拉原图。防盗链看着像个开关,实际要配一整套判断逻辑——谁可以拉、拉多久、超了怎么处理。这里按排查顺序把 Referer 白名单、签名 URL、源站带宽与流量口径、回源保护、上线自测串起来讲。
发布日期:2026-11-16 11:46:15
-
缓存击穿和雪崩怎么应对
接口平时二十毫秒,某天开始偶尔跳到八百毫秒,数据库 QPS 突然翻几倍,缓存命中率从九成掉到三成,重启能缓过来,第二天又复发。这类症状多半不是数据库扛不住,而是缓存那一层在某个瞬间集体失守。这里按现象确认、热点键兜底、几种失效形态的区别、过期时间设计与预热降级来讲,尽量给能直接落地的做法。
发布日期:2026-11-16 15:43:10
-
大表加索引怎么不锁表
几千万行的表上加一个索引,白天直接跑,页面转圈客服电话就来了。多数时候不是索引有多重,而是建的过程中把业务请求挡在了门外。想做到不锁表,得先弄清是哪种锁在挡路、数据库版本支持到什么程度、磁盘和内存还剩多少余量,这几件事弄明白再动手,很多问题能提前避开。
发布日期:2026-11-16 19:45:25
我要评论 查看全部评论 (0)>>



















