MySQL全局锁、表级锁、行锁、死锁、索引选择

本文为MySQL45讲 6-10的总结
根据加锁的范围可以分为全局锁、表级锁、行锁

全局锁

定义：对整个数据库实例加锁，让整个库处于只读状态

命令：Flush tables with read lock （FTWRL）

使用场景：做全库逻辑备份

不建议使用set global readonly=true的原因：

1. 在有些系统中readonly会被用为其他逻辑
2. 在异常处理机制上面有差异，FTWRL若客户端异常导致断开，MySQL会自动释放，但是如果用此方法，状态不会改变

存在的问题：当全局备份时，相关业务都会停摆， 但是不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。如果开启事务，那么可重复读级别下是可以拿到一致性试图

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

语法： lock tables … read/write

特点：可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象；是最常用的处理并发的方式

元数据锁 MDL

特点：不需要显示使用，在访问表时会自动加上

作用：保证读写的正确性

在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

• 事务中的MDL锁，在语句执行开始时申请，等到整个事务提交后再释放。

如何安全的给小表添加字段

1. 理解和监控长事务

在进行任何DDL操作前，我们需要监控并处理可能存在的长事务。长事务会持有元数据锁（MDL），这会阻止DDL操作的执行。您可以通过以下SQL查询来检查长事务：

SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(), trx_started)) > N;

这里的N是你定义的长事务的阈值（例如，60秒）。

2. 使用NOWAIT和WAIT语法

MySQL原生并不支持NOWAIT和WAIT语法。这是MariaDB一个特有的功能，继承自AliSQL。这些选项允许你控制DDL操作的等待行为：

• NOWAIT：尝试立即获取锁，如果无法立即获得则操作失败。
• WAIT N：等待指定的秒数以获取锁，如果指定时间内无法获取锁，操作也会失败。

示例

如果你正在使用MariaDB，可以这样使用：

ALTER TABLE tbl_name NOWAIT ADD COLUMN new_column INT;

或者：

ALTER TABLE tbl_name WAIT 10 ADD COLUMN new_column INT;

3. 选择合适的时间窗口

尽管NOWAIT和WAIT提供了更多的灵活性，选择执行DDL操作的时间仍然至关重要。选择低峰时段进行操作可以显著减少对业务的影响。

4. 分阶段执行

对于非常大或非常活跃的表，考虑分阶段进行字段添加：

• 第一阶段：添加字段，但暂不使用新字段。
• 第二阶段：逐步在应用中实施使用新字段的代码。
• 第三阶段：在确认新字段稳定运行后，清理或迁移旧数据。

5. 使用在线DDL工具

对于MySQL用户，可以考虑使用诸如pt-online-schema-change这类工具，它可以在不锁表的情况下进行表结构的变更。这对于需要24/7运行的应用尤为重要。

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。

特点： 在InnoDB事务中，行锁是在需要的时候才加上的，要等到事务结束时才释放。这个就是两阶段锁协议

使用：将最经常修改的数据放到最后提交，能最大程度减少锁等待

死锁

定义：并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态

解决方法

1. 直接进入等待，直到超时，相关参数为：innodb\_lock\_wait\_timeout 默认值为50s
2. 正常情况下采用：发起死锁检测，主动回滚某一个事务，相关参数：innodb\_deadlock\_detect默认值为on

在秒杀情况下，可以采取如下方法

1. 临时关闭死锁检测，但是带有风险性
2. 控制并发度，使用中间件，或者直接修改源码
3. 将一条记录分为多条，但需要注意临界值处理

普通索引和唯一索引的选择

索引基础

在数据库设计中，普通索引和唯一索引是两种常见的索引类型，它们在应用时会根据业务需求和数据的特性来选择。普通索引允许索引列包含重复的值，而唯一索引则保证索引列的值全局唯一。

业务场景分析

例如，一个市民系统中，每个人的身份证号都是唯一的。如果系统需要通过身份证号查询个人信息，可以在id_card字段上建立索引。这里存在两个选择：

1. 普通索引：如果业务层已经保证了身份证号的唯一性，普通索引可以有效支持查找操作。
2. 唯一索引：额外保证数据的唯一性，防止由于错误或漏洞导致重复数据的插入。

性能考量

• 查询性能：唯一索引和普通索引在查找数据时的性能差异微乎其微，因为InnoDB存储引擎的数据管理是基于数据页的，即使是普通索引在达到查询条件后的额外检索操作，也仅涉及内存中的简单计算。
• 更新性能：普通索引可以使用change buffer优化更新操作，尤其是当数据页不在内存中时。唯一索引需要检查唯一性约束，因此不能使用change buffer，可能导致更频繁的磁盘I/O。

实践建议

在选择普通索引还是唯一索引时，应该根据以下几点考虑：

• 数据唯一性需求：如果业务规则或逻辑已经确保了数据的唯一性，普通索引可能足够且更灵活。如果需要数据库层面的严格数据唯一性保证，应选择唯一索引。
• 性能需求：普通索引在某些更新操作中可能更高效，尤其是在涉及大量写操作的场景中。
• 系统资源：考虑系统的内存和存储资源，普通索引的change buffer特性可能对系统性能有正面影响。

索引及其选择机制

MySQL中的表可以有多个索引，而使用哪个索引进行查询优化通常由MySQL的查询优化器自动决定。优化器的目标是选择最小化查询成本的执行计划。

索引选择错误的示例

在文档中提供了一个实验案例：

• 创建一个简单的表t，包含字段a和b，并为这两个字段各自创建索引。
• 向表中插入100000行数据，其中a和b字段的值从1递增到100000。
• 执行查询select * from t where a between 10000 and 20000，理论上应优先使用索引a。

问题出现的条件

在对表进行大量数据删除和再次插入操作后，查询优化器未能选择最佳索引a，而是进行了全表扫描，导致查询性能显著下降。

优化器逻辑与决策因素

• 扫描行数：优化器评估不同索引方案的扫描行数，越少的扫描行数通常意味着越高的查询效率。
• 其他因素：是否需要使用临时表、是否需要排序等，也会影响优化器的索引选择。

索引的区分度和基数

• 区分度：索引的区分度高意味着通过该索引能更有效地过滤数据。

• 基数：索引的基数是指索引列上不同值的数量。基数的准确性直接影响优化器的选择准确性。
  索引选择错误的原因

• 索引的“区分度”和“基数”是优化器决定是否使用某个索引的关键。

• 基数的计算是通过采样统计得出的，这可能导致不精确的结果。

• 采样统计：为了减少计算资源消耗，MySQL通过采样部分数据来估计整个表的索引基数。

统计信息的角色

MySQL通过对表的一部分数据进行采样，来估算索引的基数。若数据分布发生变化（如频繁的插入和删除操作），采样得到的统计信息可能不再准确，从而影响优化器的索引选择。

解决方案和实践建议

• 强制索引使用：通过force index语句可以强制查询优化器使用特定索引。
• 监控和调整：通过设置long_query_time为0并检查慢查询日志，可以识别并分析由于索引选择不当导致的低效查询。
• 维护统计信息：定期更新表的统计信息，以保持优化器决策的准确性，特别是在数据变动频繁的表上。

结论

在MySQL数据库管理和优化中，理解并正确处理索引选择问题至关重要。优化器虽然大多数时间能自动选择最优索引，但在特定情况下（如数据频繁更新时）也可能出错。开发者需要掌握相关知识和技巧，以确保数据库查询的性能和效率。