面试题：MySQL中索引如何影响锁的粒度

Question

在MySQL数据库里，我们知道索引和锁机制紧密相关。请阐述普通索引、唯一索引在使用行锁时，对锁粒度产生的影响，并举例说明不同场景下如何因为索引的差异导致锁粒度变化，进而影响并发性能。

Answer 1

普通索引与行锁粒度

1. 原理：普通索引允许存在重复值。在使用行锁时，当通过普通索引进行查询并更新数据时，MySQL不仅会锁住符合条件的行，还可能会锁住相邻的行，以防止幻读。这是因为普通索引不能确定唯一性，为了保证事务的隔离性，需要扩大锁的范围。
2. 举例：假设有一张表users，包含字段id（主键），name（普通索引）。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

当执行以下事务：

START TRANSACTION;
SELECT * FROM users WHERE name = 'John' FOR UPDATE;
-- 此时不仅锁住name为'John'的行，还可能锁住相邻行
UPDATE users SET age = 30 WHERE name = 'John';
COMMIT;

由于name是普通索引，可能存在多个John，为防止幻读，锁粒度会相对较大。

唯一索引与行锁粒度

1. 原理：唯一索引要求索引列的值必须唯一。当通过唯一索引进行查询并更新数据时，MySQL能够精准定位到唯一的行，因此锁粒度就是该行，不会扩大到其他行，这大大减少了锁争用的可能性，提高了并发性能。
2. 举例：假设修改表结构，将name字段设为唯一索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    UNIQUE INDEX idx_name (name)
);

当执行事务：

START TRANSACTION;
SELECT * FROM users WHERE name = 'John' FOR UPDATE;
-- 精准锁住name为'John'的唯一行
UPDATE users SET age = 30 WHERE name = 'John';
COMMIT;

因为name是唯一索引，能精准定位，锁粒度仅为这一行，并发性能更好。

对并发性能的影响

1. 普通索引场景：在高并发场景下，由于普通索引锁粒度较大，可能会导致更多的行被锁住，其他事务需要等待锁释放，从而降低并发性能。例如多个事务同时更新name为John的相关数据，就容易产生锁争用。
2. 唯一索引场景：唯一索引锁粒度小，多个事务可以并发操作不同的行，只要这些行对应的唯一索引值不同，就不会产生锁争用，并发性能相对较高。例如多个事务分别更新不同name的用户数据，因为锁粒度小，并发执行的效率更高。