网站封装成app可以做热更新,做平面设计兼职的网站有哪些,免费网络wifi连接,android手机网站开发文章目录 全局锁表级锁表锁元数据锁 MDL 如何安全的给小表添加字段1. 理解和监控长事务2. 使用NOWAIT和WAIT语法示例 3. 选择合适的时间窗口4. 分阶段执行5. 使用在线DDL工具 行锁死锁普通索引和唯一索引的选择索引基础业务场景分析性能考量实践建议索引及其选择机制索引选择错… 文章目录 全局锁表级锁表锁元数据锁 MDL 如何安全的给小表添加字段1. 理解和监控长事务2. 使用NOWAIT和WAIT语法示例 3. 选择合适的时间窗口4. 分阶段执行5. 使用在线DDL工具 行锁死锁普通索引和唯一索引的选择索引基础业务场景分析性能考量实践建议索引及其选择机制索引选择错误的示例问题出现的条件 优化器逻辑与决策因素索引的区分度和基数统计信息的角色解决方案和实践建议 结论 本文为MySQL45讲 6-10的总结 根据加锁的范围可以分为全局锁、表级锁、行锁
全局锁
定义对整个数据库实例加锁让整个库处于只读状态
命令Flush tables with read lock FTWRL
使用场景做全库逻辑备份
不建议使用set global readonlytrue的原因
在有些系统中readonly会被用为其他逻辑在异常处理机制上面有差异FTWRL若客户端异常导致断开MySQL会自动释放但是如果用此方法状态不会改变
存在的问题当全局备份时相关业务都会停摆 但是不加锁的话备份系统备份的得到的库不是一个逻辑时间点这个视图是逻辑不一致的。如果开启事务那么可重复读级别下是可以拿到一致性试图
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候导数据之前就会启动一个事务来确保拿到一致性视图。而由于MVCC的支持这个过程中数据是可以正常更新的。
表级锁
MySQL里面表级别的锁有两种一种是表锁一种是元数据锁meta data lockMDL)。
表锁
语法 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事务中行锁是在需要的时候才加上的要等到事务结束时才释放。这个就是两阶段锁协议
使用将最经常修改的数据放到最后提交能最大程度减少锁等待
死锁
定义并发系统中不同线程出现循环资源依赖涉及的线程都在等待别的线程释放资源时就会导致这几个线程都进入无限等待的状态
解决方法
直接进入等待直到超时相关参数为innodb\_lock\_wait\_timeout 默认值为50s正常情况下采用发起死锁检测主动回滚某一个事务相关参数innodb\_deadlock\_detect默认值为on
在秒杀情况下可以采取如下方法
临时关闭死锁检测但是带有风险性控制并发度使用中间件或者直接修改源码将一条记录分为多条但需要注意临界值处理
普通索引和唯一索引的选择
索引基础
在数据库设计中普通索引和唯一索引是两种常见的索引类型它们在应用时会根据业务需求和数据的特性来选择。普通索引允许索引列包含重复的值而唯一索引则保证索引列的值全局唯一。
业务场景分析
例如一个市民系统中每个人的身份证号都是唯一的。如果系统需要通过身份证号查询个人信息可以在id_card字段上建立索引。这里存在两个选择
普通索引如果业务层已经保证了身份证号的唯一性普通索引可以有效支持查找操作。唯一索引额外保证数据的唯一性防止由于错误或漏洞导致重复数据的插入。
性能考量
查询性能唯一索引和普通索引在查找数据时的性能差异微乎其微因为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数据库管理和优化中理解并正确处理索引选择问题至关重要。优化器虽然大多数时间能自动选择最优索引但在特定情况下如数据频繁更新时也可能出错。开发者需要掌握相关知识和技巧以确保数据库查询的性能和效率。
