做日志系统的人十有八九会被两个问题同时卡住日志表里那些 request_id、trace_id、message 全是字符串字段怎么加索引才能让查询不慢一条更新语句下去数据库内部到底发生了什么为什么有时候一条 UPDATE 会卡上好几秒。这两个问题一个管读、一个管写看起来是两个方向实际上是一条链路的两端——你加索引的方式决定了写入时索引维护的开销而更新语句的执行链路又决定了你的写入能不能撑得起这么多索引。这篇文章适合正在设计日志库、排查慢更新语句、或者被字符串索引选择折磨过的朋友我会把索引选型和更新执行流程放在一起讲清楚最后附上我自己的踩坑记录和排查经验。1. 日志系统里的字符串索引到底难在哪1.1 先看一张典型的日志表常见日志系统表结构差不多是这样CREATE TABLE operation_log ( id bigint unsigned NOT NULL AUTO_INCREMENT, app_name varchar(32) NOT NULL, -- 应用名 level varchar(10) NOT NULL, -- INFO / WARN / ERROR trace_id varchar(64) DEFAULT NULL, -- 链路追踪ID request_id varchar(64) DEFAULT NULL, -- 请求ID message text, -- 日志内容 created_at datetime NOT NULL, PRIMARY KEY (id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这种表写入很快因为基本是 append-only。真正出问题的是查询比如按 trace_id 查一条请求的完整调用链按 request_id 查某个业务单据的处理日志或者按 message 模糊搜关键词。一旦日志量到了几千万、上亿行没有合适索引的字符串查询就是全表扫描。我在生产环境见过一张 8000 万行的日志表WHERE request_id 某个64位字符串跑了 20 多秒直接把一个排查超时问题的小工具拖垮。1.2 直接加普通索引问题更大吗很多人第一反应是“字符串查询慢那就给字符串加普通索引”。实验做法是ALTER TABLE operation_log ADD KEY idx_request_id (request_id);索引确实有效但要注意代价。request_id 是 varcher(64)utf8mb4 字符集下每个字符最多 4 字节一个索引键最长 256 字节。再加上主键bigint8 字节二级索引里的每一行记录大约要占 264 字节。8000 万行日志这个索引本身就要吃掉 20GB 左右。更要命的是写入放大日志表每秒可能写入几百上千条每次 INSERT 除了写主键聚簇索引还要同步维护 idx_request_id 这个二级索引。索引建得越多更新的代价越大。日志表不比业务表它天生就是高吞吐写入场景索引策略必须精打细算。所以问题就变成了既要让字符串字段能走索引又要控制索引体积和写入损耗这个平衡点在哪里2. 给字符串字段加索引的四种实操方案2.1 前缀索引性价比最高的选择前缀索引的原理很简单不索引整个字符串只索引字符串的前 N 个字符。因为二级索引存储的是索引键值前缀越短索引占用的空间越小缓存命中率越高写入时维护索引的开销也越低。给 operation_log 的 trace_id 加前缀索引ALTER TABLE operation_log ADD KEY idx_trace_id_prefix (trace_id(12));关键问题是 N 怎么选。N 太小区分度不够查询时扫描行数太多甚至退化N 太大空间节省不明显。常规做法是做一个区分度测试SELECT COUNT(DISTINCT LEFT(trace_id, 8)) AS prefix8, COUNT(DISTINCT LEFT(trace_id, 12)) AS prefix12, COUNT(DISTINCT LEFT(trace_id, 16)) AS prefix16, COUNT(DISTINCT trace_id) AS total FROM operation_log;把 prefix8、prefix12、prefix16 分别除以 total得到一个比例。比如 total 是 1000 万prefix12 的区分度是 99.7%prefix16 是 99.9%。那选 12 就够用了没必要用 16 甚至完整 64 位。我自己常用的选型标准是区分度达到 99% 以上同时前缀长度尽量不要超过字段定义长度的 1/3。trace_id 这种随机字符串前 12 位往往就能达到很高的区分度索引体积直接从原来的 200 多字节降到了 50 字节左右。前缀索引也有明显短板你必须清楚前缀索引不能用于 ORDER BY 和 GROUP BY因为索引里根本没有完整的字符串值。前缀索引无法实现覆盖索引查询最终还是要回表拿完整字段。如果 WHERE 条件里还要对同一个字段做 LIKE 后缀匹配前缀索引帮不上忙。所以前缀索引适合“查询时只做等值匹配、对排序没要求”的日志场景这是最典型的应用。2.2 倒序存储 前缀索引解决末尾区分度问题前缀索引有一个天然痛点如果字符串的开头部分区分度低而是末尾才有区分度前缀索引效果就很差。典型的例子是文件路径和 URL。比如日志表里记录了文件路径/usr/local/app/backend/business-server/2024/06/18/xxx.log。所有路径前缀几乎都一样取前 20 个字符做前缀索引区分度可能只有百分之几这种索引等于白建。思路是倒过来应用在写入时把字符串反转之后存到一列比如path_reverse然后对这个反转后的字段建立前缀索引。查询的时候也先把查询条件反转再去匹配-- 存储时额外维护一列 ALTER TABLE operation_log ADD COLUMN path_reverse VARCHAR(255); -- 查询时反转条件 SELECT * FROM operation_log WHERE path_reverse REVERSE(/usr/local/app/backend/business-server/2024/06/18/xxx.log);如果用的是 MySQL 8.0.13 及以上可以直接用函数索引省掉手动维护那一列CREATE INDEX idx_path_reverse ON operation_log ((REVERSE(path)));查询语句写成WHERE REVERSE(path) REVERSE(...)就能命中这个索引。这一招在处理 URL、文件路径、手机号这类“尾部才具备高区分度”的字符串字段时效果非常明显。2.3 哈希字段索引用空间换速度另一种思路是压根不给大字符串建索引而是给它算一个定长哈希值存下来再给哈希值建索引。查询时先按哈希值定位再用原字符串做精确过滤。实际操作中我用得最多的是 CRC32配合生成列和普通索引ALTER TABLE operation_log ADD COLUMN request_id_crc INT UNSIGNED GENERATED ALWAYS AS (CRC32(request_id)) STORED, ADD KEY idx_req_crc (request_id_crc);查询改写成SELECT * FROM operation_log WHERE request_id_crc CRC32(202406181234567890abc) AND request_id 202406181234567890abc;这算是一种“空间换查询速度”的玩法。request_id 一个 64 字节的字符串哈希后只有 4 字节索引体积极低扫描行数非常小。注意 CRC32 会碰撞所以必须把原始字段也放进 WHERE 条件里做二次过滤不能只按哈希值匹配。有人会问为什么不用 MD5 或者 SHA1。我建议日志表这类高频写入场景优先用 CRC324 字节就够了。碰撞概率在业务可接受范围内而且多一层精确匹配兜底。如果你担心 CRC32 碰撞率可以用两个哈希值组合但那是极少数安全敏感场景才需要做的事。哈希索引方案也有缺点不能做范围查询不能排序生成列是 STORED 时也会占用存储空间。但在 trace_id、request_id 这类“只要等值匹配”的日志场景里它非常好用。2.4 方案对比四个方向怎么选把几种方案放一张表里看思路会清晰很多方案索引体积写入额外开销支持范围/排序适用场景普通全字段索引最大高支持字段短、更新少、查询多前缀索引较小中不支持 ORDER BY / GROUP BY前缀区分度高倒序存储 前缀索引较小中不支持后缀区分度高哈希索引CRC32很小低不支持范围/排序等值查询、日志追踪完整索引8.0 函数索引中中看表达式需要表达式精确匹配给日志系统选索引我目前的习惯是如果需要精确查 trace_id、request_id优先走“哈希列 精确过滤”如果是按可读前缀做筛选用前缀索引如果查询场景复杂到要模糊匹配、全文检索那别硬刚普通索引考虑上 ES、Loki 这类专门的日志检索组件更合适。3. 日志系统里的更新语句一条 UPDATE 的执行链路3.1 一条 UPDATE 会经过哪些组件很多人会背 MySQL 执行流程但真到了生产环境一条 UPDATE 卡住能迅速定位到具体环节的人不多。一条更新语句从客户端发出来依次经过连接器、分析器、优化器、执行器最后进入 InnoDB 存储引擎。连接器负责鉴权和建立连接分析器做词法语法解析优化器决定走哪个索引、用什么 join 方式执行器真正调用存储引擎接口去操作数据。这些环节和 SELECT 一样区别在于 UPDATE 到了存储引擎层之后还有一系列写操作。我经常拿“审批流程”来类比连接器是门卫分析器是秘书优化器是审批领导执行器是干活的人InnoDB 是仓库管理员。前面少了任何一环单子都批不下来。而到了仓库管理员这一步他还要做登记台账undo log、修改货架buffer pool、写工作日志redo log、给上级汇报binlog这些就是“一条更新语句真正消耗时间的地方”。3.2 WAL 机制先写日志还是先改数据InnoDB 采用的是 WAL 机制也就是预写日志。更新一条记录时不是直接把磁盘上的数据文件改掉而是先把这个更新记录到 redo log 里然后再改内存中的 buffer pool 数据页等后台线程选择合适的时机再刷到磁盘。这里有个反直觉的点更新一条数据磁盘 I/O 反而主要花在写 redo log 上而不是写数据文件本身。因为 redo log 是追加写属于顺序 I/O比随机更新一个数据页要快得多。redo log 在 InnoDB 里是固定大小的循环写结构由一组文件组成。从头写到尾之后会覆盖老日志所以它绝对不能无限增长必须有 checkpoint 机制。如果数据库崩溃内存里的脏页丢了重启后靠 redo log 就能把数据重新刷出来不会丢已提交事务的数据。3.3 undo log 和锁的关系更新之前InnoDB 会把旧值先写到 undo log。它的作用有两个一是事务回滚时用它把数据恢复成原样二是 MVCC 多版本控制其他事务需要读取旧版本数据时通过 undo log 构造快照。还要注意锁。执行 UPDATE 时InnoDB 会对命中的记录加排他锁X 锁直到事务提交或回滚才会释放。如果这条记录已经被其他事务锁住当前 UPDATE 就会进入锁等待状态表现就是“明明一条简单更新就是执行不完”。日志表虽然主要是 append但如果有并发更新同一行或者范围更新跨越了间隙照样会锁冲突。3.4 redo log、binlog 和两阶段提交这里必须说清楚两个日志的区别项目redo logbinlog属于哪一层InnoDB 存储引擎MySQL Server 层记录内容物理日志记录“在哪个数据页哪个偏移做了什么修改”逻辑日志记录 SQL 语句或行数据变化作用崩溃恢复、保证持久性主从复制、数据恢复写入时机事务执行过程中持续写事务提交时写redo log 是 InnoDB 自己的binlog 是 Server 层的。为什么需要两阶段提交因为这两个日志是分别写入的如果先写 redo log 再写 binlog写完 redo log 后 binlog 没写就崩溃了主库能恢复数据但是从库拿不到这个事务主从数据就不一致了。反过来先写 binlog 再写 redo log崩溃时 binlog 有记录但 redo log 丢了也会不一致。所以 InnoDB 用两阶段提交来协调修改内存数据页写 undo log记录旧值。写 redo log状态设为 prepare。写 binlog。事务提交时把 redo log 状态改为 commit。崩溃恢复时的判定规则也很明确redo log 是 prepare 状态binlog 里能找到这个事务且完整那就正常提交。redo log 是 prepare 状态binlog 里没有这个事务或 binlog 不完整那就回滚。redo log 是 commit 状态直接提交。这个反证逻辑的核心是只要 binlog 完整主库和从库都能查到这笔数据变更事务就必须提交。以前有人问过我为什么要这么绕其实本质上就是要保证 binlog 和 redo log 记录的事务边界一致。3.5 日志落盘参数与性能取舍既然更新语句的耗时和日志落盘直接相关那必须关注两个参数innodb_flush_log_at_trx_commit控制 redo log 的刷盘策略。值为 1 时每次事务提交都要把 redo log 刷到磁盘最安全但最慢值为 0 时每秒刷一次崩溃可能丢 1 秒数据值为 2 时每次提交只写到操作系统缓存由系统决定何时落盘比 0 安全一点性能也不错。sync_binlog控制 binlog 刷盘策略。值为 1 时每次提交都刷盘最安全值为 N 时每 N 次提交刷一次盘性能更好但可能丢 N 个事务的 binlog。日志系统如果允许极少量数据丢失来换取高吞吐可以把innodb_flush_log_at_trx_commit设为 2sync_binlog设为 1 或 N。如果业务要求不能丢数据那就必须保持 1 和 1同时接受写入性能下降。这个选择没有对错只取决于你的日志数据价值。4. 实操记录一条 UPDATE 卡顿排查实录4.1 现象日志表更新突然变慢有一次生产日志库出现怪问题查读很快但偶尔一条 UPDATE 要跑好几秒。刚开始怀疑是 SQL 写得烂看执行计划没问题后来怀疑是锁冲突查了information_schema.innodb_trx之后发现有个“幽灵事务”一直不提交。那一瞬间就明白了前面有个应用改了一条日志后事务既不提交也不回滚持有行锁不放。后面的更新语句全被挡住一个个排队等锁。排查的时候用SHOW ENGINE INNODB STATUS里的事务列表可以看到锁等待关系再用SELECT * FROM information_schema.innodb_lock_waits就能定位到是哪个事务堵了路。处理办法很直接通知对应应用方提交或回滚事务如果是僵尸事务kill 掉对应连接。之后为了避免再发生日志写入服务的数据库连接改成了短事务事务体量压缩到只包含必要操作不再在同一个事务里做日志写入和业务状态更新。4.2 索引过多导致的更新放大另一个案例更隐蔽。日志表为了支撑各种查询前后建了 7 个索引。写入日志时一句 INSERT 实际上要同时维护 7 个二级索引。后来做压测发现去掉两个使用率极低的索引之后写入吞吐提升了将近 30%。这里分享一个我总结的经验日志表是写多读少的表索引尽量控制在 3 个以内。主键索引必留时间字段索引支撑常规扫描再留一个支撑你最高频查询的字符串字段索引即可。再多就要掂量一下写入代价了。如果需要支撑的查询场景很多建议从 MySQL 里把日志数据同步到 ES 或 Loki 里做检索MySQL 只保留原始数据与简单查询。4.3 事务提交时 fsync 瓶颈的影响更新语句的提交环节还有一个隐性瓶颈磁盘 fsync。前面说过innodb_flush_log_at_trx_commit1时每个事务提交都要把 redo log 刷到磁盘。如果用的是机械盘或者本地 SSD 性能一般高并发下 fsync 会成为更新吞吐的天花板。我实测过一个场景同样的更新语句放在 SSD 上提交耗时只有机械盘的 1/5。后来改造方案是把频繁更新的操作合并成批量事务一次提交处理多行变更每秒的事务数下降每事务的数据量上升磁盘 fsync 次数减少了整体吞吐反而上去。这就是典型的“合并小事务减少刷盘次数”策略。5. 常见问题与避坑清单5.1 字符串索引“建了却不走”的几个原因字符串字段加索引之后查询却不走索引的情况我见到最多的是这四类隐式类型转换。字段是 varchar查询条件传了整型数字MySQL 会把字符串转成数字比较索引直接失效。字符集不一致。表字段用的是 utf8mb4连接字符集是 latin1字符串比较时就会做转换导致无法高效使用索引。LIKE 前缀带通配符。%abc这种后缀匹配普通索引和前缀索引都没办法。在索引列上使用函数。WHERE LEFT(trace_id, 6) abcdef不走普通索引除非你建函数索引。排查这类问题很简单在慢日志里找到语句EXPLAIN看type和key字段。如果 type 是ALL或者 indexkey 是空基本就是上面几种情况之一。5.2 日志系统设计上的三点建议第一日志表按时间做分区。以天或月为单位分区天然帮助清理历史数据和加快时间范围查询。第二冷热分离。热日志存 MySQL老日志定期归档或者只保留检索摘要不要让冷数据拖累热数据的索引命中率。第三字符串字段到底加哪种索引先看查询模式。等值查询多用哈希列索引前缀查询多用前缀索引模糊搜索和全文检索多别硬扛直接引搜索引擎组件。5.3 更新语句常见故障速查现象可能原因检查命令UPDATE 卡住不返回行锁或表锁冲突information_schema.innodb_trx、innodb_lock_waits提交很慢redo log 刷盘压力大SHOW ENGINE INNODB STATUS里的 log 部分更新结果对不上隐式类型转换或字符集问题EXPLAIN 看 type 和 Extra高峰期写入吞吐下降二级索引过多、IO 竞争SHOW GLOBAL STATUS LIKE Innodb_rows_inserted压测对比崩溃恢复后数据不一致binlog 或 redo log 配置不当检查innodb_flush_log_at_trx_commit和sync_binlog写在最后的一个体会给字符串字段加索引和搞懂更新语句的执行流程看起来是两个知识点实际是同一个决策树上的两个分支。我在给日志系统选索引方案时每次都要先问自己这个字段会被怎么查这个表每秒会有多少写入如果索引把写入拖垮了查询再快也没用。理解了 redo log、binlog、两阶段提交之后你再去看更新语句的性能瓶颈会发现那些迟迟不提交的事务、过多的 fsync、不必要的二级索引都是同一个链路在给你传递信号。最后分享一个小技巧每次给日志表加索引之前先拿生产环境的数据量做一次区分度测试和空间估算把结果贴给团队其他人看很多“能不能加这个索引”的争论就不存在了。