面试题：MySQL中索引如何影响行级锁的性能

Question

在MySQL数据库中，阐述索引的存在与否以及不同类型的索引（如普通索引、唯一索引等），对行级锁的加锁机制和性能会产生怎样的影响。请结合实际场景举例说明。

Answer 1

索引存在与否对行级锁加锁机制和性能的影响

1. 有索引时
   • 加锁机制：MySQL行级锁是基于索引实现的。当有索引时，数据库可以精准定位到需要操作的行，从而只对这些行加锁。例如，执行UPDATE users SET age = 30 WHERE id = 1;，如果id列上有索引，MySQL会通过索引快速找到id为1的行并对其加锁。
   • 性能影响：由于锁的粒度精确到行，并发性能较好。其他事务可以同时访问表中未被锁定的行，减少了锁争用。比如在一个电商订单表中，不同用户同时修改各自订单的状态，因为订单号有索引，各自的修改操作可以并发执行，互不干扰。
2. 无索引时
   • 加锁机制：如果没有索引，MySQL会对全表加锁。例如执行UPDATE users SET age = 30 WHERE name = 'John';，若name列无索引，MySQL无法快速定位到具体行，只能对整个users表加锁，防止其他事务对表进行修改。
   • 性能影响：这会极大地降低并发性能。因为全表加锁期间，其他事务无法对表进行读写操作，容易造成锁争用和性能瓶颈。例如在一个高并发的论坛帖子表中，若对帖子标题无索引，当一个用户更新某个帖子标题时，全表加锁会导致其他用户无法查看或更新其他帖子，严重影响系统性能。

不同类型索引对行级锁加锁机制和性能的影响

1. 普通索引
   • 加锁机制：普通索引允许重复值。加锁时，会根据索引定位到符合条件的行并加锁。例如，在一个员工表employees中，有一个普通索引department_index在department列上。执行UPDATE employees SET salary = salary * 1.1 WHERE department = 'HR';，MySQL会通过department_index找到所有部门为HR的员工行并加锁。
   • 性能影响：普通索引能提高查询和加锁的效率，但由于可能存在重复值，锁定的行数可能较多，在一定程度上会增加锁争用的可能性。比如在员工表中，如果多个员工在同一个部门，对该部门员工操作时，锁定的行数较多，其他事务等待锁的概率增加。
2. 唯一索引
   • 加锁机制：唯一索引不允许重复值。加锁时，由于索引值唯一，能更精准地定位到特定行。例如，在用户表users中，email列有唯一索引。执行UPDATE users SET phone = '1234567890' WHERE email = '[e-mail]';，MySQL通过唯一索引email能快速且精准地定位到对应的用户行并加锁。
   • 性能影响：因为定位精准，锁定的行数少，锁争用的概率相对较低，并发性能更好。比如在一个用户注册系统中，对邮箱设置唯一索引，当用户修改自己的联系方式时，由于邮箱唯一，不会影响其他用户，并发操作时的性能更优。