在MySQL的多表关联查询场景中，Join操作引发的关联表锁等待是较为常见的性能问题，这类问题通常会导致查询响应变慢，严重时还会阻塞其他事务的正常执行。问题的核心诱因往往和关联字段的索引设计直接相关，合理的索引优化能够从根源上减少锁冲突的发生。
Join引发关联表锁等待的原理
MySQL的InnoDB引擎默认采用行级锁，锁的加锁范围和查询的扫描方式直接相关。当执行Join操作时，如果驱动表和被驱动表的关联字段没有合适的索引，被驱动表会进行全表扫描。全表扫描过程中，InnoDB会对扫描到的行加锁，若扫描范围过大，就会锁定大量不必要的行，其他事务操作这些被锁定的行时就会产生锁等待。
举个常见的场景，驱动表t1通过字段user_id关联被驱动表t2，如果t2的user_id字段没有索引，那么每次匹配t1的一行数据，都需要对t2做全表扫描，此时t2的所有行都会被加锁，一旦有其他事务要修改t2的数据，就会被阻塞。
为关联字段建立高效索引的设计原则
1. 关联字段必须单独建立索引
Join操作中用到的关联字段，必须在被驱动表上单独建立索引，不要依赖组合索引的前缀匹配，除非关联查询的条件完全符合组合索引的最左前缀规则。
2. 索引选择性要足够高
索引的选择性指的是不重复的索引值和数据表总记录数的比值，比值越高索引效率越好。如果关联字段的重复值非常多，即使建立了索引，查询时还是会扫描大量行，锁的范围依然很大。
3. 避免对关联字段做函数处理
如果Join条件中对关联字段使用了函数，比如DATE(create_time) = '2024-01-01'，即使字段上有索引也无法正常使用，会触发全表扫描，进而导致锁等待问题。
优化案例演示
我们创建两张测试表，模拟没有索引和有索引两种场景下的锁等待情况。
初始表结构
-- 创建驱动表t1
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
order_no VARCHAR(32)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建被驱动表t2，user_id字段无索引
CREATE TABLE t2 (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
score INT,
INDEX idx_score (score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入测试数据，t1插入1000行，t2插入10000行
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO t1 (user_id, order_no) VALUES (i, CONCAT('ORD', i));
SET i = i + 1;
END WHILE;
SET i = 1;
WHILE i <= 10000 DO
INSERT INTO t2 (user_id, score) VALUES (i % 1000, FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;
无索引场景下的Join锁等待模拟
开启两个会话，会话1执行Join查询但不提交事务：
-- 会话1 START TRANSACTION; SELECT t1.id, t2.score FROM t1 INNER JOIN t2 ON t1.user_id = t2.user_id WHERE t1.id < 10;
此时会话2尝试更新t2表的数据，会被阻塞：
-- 会话2 UPDATE t2 SET score = 100 WHERE id = 100; -- 该语句会一直等待，直到会话1提交事务
建立索引后的优化效果
为t2表的user_id字段添加索引：
ALTER TABLE t2 ADD INDEX idx_user_id (user_id);
再次重复上述两个会话的操作，会话1执行Join查询后，会话2的更新语句可以立即执行，不再产生锁等待。这是因为idx_user_id索引让Join操作只需要扫描匹配的行，锁的范围被大幅缩小。
优化效果验证方法
可以通过MySQL的EXPLAIN命令查看Join查询的执行计划，重点看被驱动表的type列，如果是ref或者eq_ref说明索引被正常使用，如果是ALL说明还是全表扫描，需要检查索引设计。
EXPLAIN SELECT t1.id, t2.score FROM t1 INNER JOIN t2 ON t1.user_id = t2.user_id WHERE t1.id < 10;
同时可以查询information_schema.INNODB_LOCKS和information_schema.INNODB_LOCK_WAITS两张表，查看当前是否存在锁等待，以及锁等待对应的SQL语句，进一步确认优化是否生效。
注意事项
- 不要盲目给所有关联字段加索引，索引过多会影响写入性能，需要结合查询频率权衡。
- 如果Join的关联字段是两张表的主键，不需要额外建立索引，主键本身自带聚簇索引。
- 定期分析慢查询日志，把出现锁等待的Join语句单独拎出来做索引优化，避免无差别加索引。