mysql中的索引与事务处理性能的关联

索引会拖慢事务写入速度，因每次 DML 需同步更新所有相关索引，导致写放大、B+ 树分裂、唯一性校验开销及锁竞争加剧。

索引会拖慢事务的写入速度

事务中涉及 INSERT、UPDATE、DELETE 操作时，每修改一行数据，MySQL 都要同步更新所有相关索引。索引越多，写放大越严重——不是只改 1 行数据，而是可能触发 B+ 树分裂、页合并、二级索引回表更新等额外开销。

• 单个 UPDATE 语句若命中 1000 行，且该表有 5 个二级索引，实际磁盘写入量可能接近原始数据的 6 倍（主键 + 5 个索引）
• UNIQUE 索引在写入前必须做唯一性校验，会引发额外的索引查找，尤其在高并发下容易成为锁竞争 热点
• 使用 bulk insert 时，先 DROP INDEX 再重建，常比逐条插入快 3–10 倍（需权衡维护窗口）

事务隔离级别影响索引扫描范围和锁粒度

不同隔离级别下，MySQL 对索引的访问方式和加锁行为差异极大。比如 REPEATABLE READ 下的 SELECT …… FOR UPDATE 不仅锁住匹配行，还可能锁住索引间隙（Gap Lock），而 READ COMMITTED 默认只锁行（Record Lock），不锁间隙。

• 在 WHERE status = ? 上建了索引，但 status 值分布极不均匀（如 95% 是 'pending'），会导致大范围索引扫描 + 大量行锁，事务容易被阻塞
• SELECT * FROM t WHERE id > 1000 ORDER BY id LIMIT 1 在 id 主键上有索引，但若事务中先执行了未提交的 DELETE FROM t WHERE id = 1001，在 REPEATABLE READ 下可能触发间隙锁，把 (1000, 1001) 区间也锁住
• 用 EXPLAIN FORMAT=tree 查看执行计划时，注意 access_type 是否为 range 或 index；如果是全索引扫描（type: index），即使走了索引，也可能因锁太多导致事务吞吐骤降

长事务让索引统计信息过期，优化器选错执行计划

MySQL 的索引基数（cardinality）统计依赖采样，且不会在事务运行中动态刷新。一个持续数分钟的事务，期间大量 DML 修改数据分布，但优化器仍按旧统计估算成本，可能放弃本该走的索引，转而用全表扫描。

• 现象：某 SELECT 在事务内第一次执行很快（走了索引），第二次变慢（type: ALL），SHOW INDEX FROM t 显示 Cardinality 值明显滞后于实际
• 临时缓解：手动执行 ANALYZE TABLE t，但会加 MDL 锁，生产环境慎用；更稳妥的是避免长事务，把大事务拆成小批次（例如每次处理 1000 行）
• 监控线索：观察 information_schema.INNODB_TRX 中 TRX_STARTED 时间戳，结合 performance_schema.events_statements_current 定位长时间未提交的查询

唯一索引冲突直接导致事务回滚或死锁

当两个并发事务尝试插入相同 UNIQUE KEY 值时，MySQL 必须在索引层面检测冲突。这个过程涉及对索引记录加 S 锁（共享锁）再升级为 X 锁（排他锁），极易与其它事务形成循环等待。

• 典型死锁日志里会出现类似 lock_mode X locks gap before rec insert intention waiting，说明两个事务都在等对方释放唯一索引的间隙锁
• 应用层不要依赖 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 来“兜底”唯一冲突，它们只是掩盖问题；应提前用 SELECT …… FOR UPDATE 加锁检查，或改用分布式 ID + 应用层幂等控制
• 如果业务允许，把 UNIQUE 约束从数据库下推到应用层缓存（如 Redis SETNX），可显著降低索引锁争用

SELECT    trx_id,   trx_state,   trx_started,   trx_weight,   trx_mysql_thread_id FROM information_schema.INNODB_TRX  WHERE trx_state = 'RUNNING' AND TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

索引不是越全越好，事务不是越长越稳。真正卡住性能的，往往是那条没走索引的 SELECT 拿着锁不放，或是那个被忽略的 UNIQUE 索引在高并发下成了锁瓶颈。