update 没加索引会锁全表?

在 MySQL 中，UPDATE 语句如果没有适当的索引支持，可能会导致全表扫描，从而导致较长的执行时间和更广泛的锁定范围。然而，这并不意味着它一定会锁全表。是否锁全表取决于多种因素，如表的存储引擎、事务隔离级别和更新条件。

1. 如何影响锁定范围

1.1 InnoDB 存储引擎

InnoDB 是 MySQL 的默认存储引擎，它采用行级锁和间隙锁来实现事务的隔离性和并发控制。

• 行级锁：InnoDB 会锁定更新涉及的行。如果 UPDATE 语句能够利用索引快速定位行，则只会锁定这些行。
• 间隙锁（Gap Lock）和 Next-Key 锁：在 REPEATABLE READ 隔离级别下，InnoDB 可能会锁定范围内的索引记录和它们之间的间隙，以防止幻读。

示例：

-- 假设有一个没有索引的表
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 如果执行以下语句，没有索引的支持会导致全表扫描
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

在没有索引的情况下，InnoDB 需要扫描表中所有的行，找到满足条件的行进行更新。这会导致大量行级锁的使用，效率低下，容易导致锁冲突。

2. 锁定范围的影响

2.1 全表扫描

没有索引会导致全表扫描，InnoDB 会对所有扫描过的行加锁，这种情况接近于“锁全表”。

2.2 索引的使用

如果有适当的索引，InnoDB 只会锁定符合条件的行：

-- 为department列添加索引
CREATE INDEX idx_department ON employees(department);

-- 这时候执行同样的UPDATE语句会利用索引
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

这样，InnoDB 会通过索引快速定位 department = 'Sales' 的行，锁定范围大大减少，效率提高。

3. 可重复读隔离级别下的锁

在可重复读隔离级别下，InnoDB 会使用间隙锁和 Next-Key 锁，这不仅仅会锁定行，还可能锁定索引范围和间隙，防止幻读。

例如：

-- 假设有一张表orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 为order_date添加索引
CREATE INDEX idx_order_date ON orders(order_date);

-- 如果在REPEATABLE READ隔离级别下执行以下查询
START TRANSACTION;
UPDATE orders SET amount = amount * 0.9 WHERE order_date = '2023-11-01';
-- Next-Key Lock会锁定order_date为特定日期和其之前的间隙

4. 如何优化查询防止全表锁

为了防止 UPDATE 语句导致大范围锁定，应尽量优化查询条件，并合理使用索引：

4.1 创建合适的索引

根据查询条件添加合适的索引，可以大大提高查询和更新效率，减少锁定范围。

-- 创建组合索引
CREATE INDEX idx_department_salary ON employees(department, salary);

4.2 合理分配事务

合理设计事务，缩小事务范围，尽量避免长时间占用锁。

4.3 分批次更新

对于大表的大量更新操作，可以分批次进行，避免单次大量锁定。

-- 将更新拆分为多次小批量更新
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales' LIMIT 1000;

总结

• 是否锁全表：未加索引的 UPDATE 语句并不一定会锁全表，但会导致全表扫描，并可能锁定大量行，接近锁全表的效果。
• 索引的重要性：通过合理设计和使用索引，可以有效缩小锁定范围，提高查询和更新效率。
• 事务管理：合理管理事务，优化 SQL 语句，避免不必要的大范围锁定。

通过合理设计数据库结构、使用索引和优化查询，可以有效防止 UPDATE 语句不当导致的锁全表及其带来的性能问题。