MySQL - 锁
[TOC]
锁机制
计算机协调 多个进程或线程 并发访问某一资源的机制。
锁的类型及其特点
开销、加锁、颗粒度、冲突、并发等角度分析
表级锁(table-level locking) 开销大，加锁快，不会出现死锁，颗粒度大，锁冲突概率高，并发度小；
页面锁(page-level locking) 开销、加锁效率、颗粒度介于表锁和行锁之间，会出现死锁，并发度一般；
行级锁(row-level locking) 开销大，加速慢，会出现死锁，颗粒度小，锁冲突概率小，并发度高；
锁的类型应用场景
表锁 - 适合 以查询为主，只有少量按索引条件更新数据的应用，如web应用；
行锁 - 适合 大量按索引条件并发 更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统；
锁的支持度及情况
MyISAM 只支持 表锁
InnoDB 默认支持 行锁，也支持表锁
1 | -- 查询表锁争用情况 |
锁的维度划分
共享锁(S) - 读锁
排他锁(X) - 写锁
意向共享锁(IS)
意向排他锁(IX)
间隙锁
死锁
死锁的产生原因
- 系统资源不足
- 进程运行 推进的顺序/速度 不同
- 资源分配不当
死锁的必要条件
- 互斥 一个资源每次只能被一个进程使用
- 请求与保持 一个进程因请求资源而阻塞时，对已获得的资源保持不放
- 不可剥夺 进程已获得的资源，在未使用完之前，不能强行剥夺
- 循环等待 若干进程之间形成一种头尾相接的循环等待资源关系
避免死锁
合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
避免大事务，将大事务拆成多个小事务
以固定的顺序访问表和行。
比如两个更新数据的事务，
事务 A 更新数据的顺序为 1，2;
事务 B 更新数据的顺序为 2，1。
这样更可能会造成死锁。
在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。
如 select … for update 语句，
如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,
那么就会锁定所查找到的记录。
尽量用主键/索引去查找记录
优化 SQL 和表设计，减少同时占用太多资源的情况。
比如说，避免多个表join，将复杂 SQL 分解为多个简单的 SQL。
死锁的解除与预防
打破必要条件之一即可解锁，预防必要条件即可预防死锁。
- 按同一顺序访问对象
- 避免事务中的用户交互
- 保持事务简短并在一个批处理中
- 使用低隔离级别
- 使用绑定连接
总结
| 维度|锁类型 | 表级锁 | 页面锁 | 行级锁 |
|---|---|---|---|
| 内存开销 | 大 | 中 | 大 |
| 加锁效率 | 快 | 中 | 慢 |
| 颗粒度 | 大 | 中 | 小 |
| 锁冲突概率 | 高 | 中 | 低 |
| 是否会死锁 | 否 | 是 | 是 |
| 并发度 | 小 | 一般 | 高 |
| 应用特点 | 查询&少量索引条件的更新 | - | 并发查询&大量索引条件的不同数据更新 |
| 应用场景 | Web | - | 在线事务处理系统 |
| 存储引擎 | MyISAM、InnoDB | - | InnoDB |
| 锁争用参数 | show status like 'table%'Table_locks_waited | - | show status like 'InnoDB_row_lock%';InnoDB_row_lock_waits InnoDB_row_lock_time_avg |
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2019-03-20
控制并发
Mysql内部通过锁机制实现对资源的并发访问控制，保证数据的一致性，锁机制的类型和引擎的种类有关，MyISAM中默认支持的表级锁有两种：共享读锁和独占写锁。表级锁在MyISAM和InnoDB的存储引擎中都支持，但是InnoDB默认支持的是行锁。 MyISAM锁机制Mysql中可以通过以下sql来显示的在事务中显式的进行加锁和解锁操作： 123456// 显式的添加表级读锁LOCK TABLE 表名 READ// 显示的添加表级写锁LOCK TABLE 表名 WRITE// 显式的解锁（当一个事务commit的时候也会自动解锁）unlock tables; （1）MyISAM表级写锁：当一个线程获取到表级写锁后，只能由该线程对表进行读写操作，别的线程必须等待该线程释放锁以后才能操作。 （2）MyISAM表级共享读锁：当一个线程获取到表级读锁后，该线程只能读取数据不能修改数据，其它线程也只能加读锁，不能加写锁。 InnoDB锁机制InnoDB和MyISAM不同的是，InnoDB支持行锁和事务，InnoDB中除了有表锁和行级锁的概念，还有Gap Lock（间隙锁）、Next-key ...
2022-05-20
MySQL优化经验总结
线上SQL的调优经验 slow_query_log 日志中收集到的慢 SQL ，结合 explain 分析是否命中索引。 减少索引扫描行数，有针对性的优化慢 SQL。 建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。 还可以使用虚拟列和联合索引来提升复杂查询的执行效率。 监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql 打开数据库慢查询日志功能 简化业务逻辑 代码重构、优化 异步处理 sql优化 索引优化 SQL优化 分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条 尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询 SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。 where子句 避免对字段进行null值判断、表达式操作等； where表之间的连接，必须写在其他where条件之前...
2019-03-20
SQL语句性能分析工具 - explain
通过explain，如以下例子： 1EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26'; id select_type table partitions type possible_keys key key_len ref filtered rows Extra 1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1 id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如 explain select * from s1 where id = (select id from s1 where name = 'egon1'); 第一个select的id是1，第二个select的id是2。 有时候会出现两个sel...
2019-03-20
MySQL主键
主键使用自增ID还是UUID?能说说原因吗？ 自增ID和UUID作为主键的考虑主要有两方面， 一个是性能 另一个就是存储的空间大小， 一般没有特定的业务要求都不推荐使用UUID作为主键。 因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。 而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。 另一方面就是存储空间， 自增主键一般整形只要4个字节，长整形才占8字节的大小空间， 而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘， 在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低， 所以不推荐使用。 自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。 所以尽量不要使用字符串（如：UUID）作为主键。
2019-05-20
索引失效的 12 种原因
一句话 索引失效 = MySQL 优化器决定不用你建的索引去执行。 永远用 EXPLAIN 验证，不要靠记忆。 12 种失效场景1. WHERE 列上做函数 / 表达式1234567-- ❌ 索引失效SELECT * FROM users WHERE YEAR(created_at) = 2024;SELECT * FROM users WHERE id + 1 = 100;-- ✅ 改写：把函数挪到右边SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';SELECT * FROM users WHERE id = 99; MySQL 8.0+ 支持 函数索引：ALTER TABLE users ADD INDEX idx_year ((YEAR(created_at))) 2. 隐式类型转换123456-- 字段是 VARCHAR，传了 INT-- ❌ 走全表扫SELECT * FROM...
2019-03-20
分库分表
分库分表 并发量决定是否需要分库， 数据量决定是否需要分表。 分区分片 按时间范围归档分区 按用户ID取模分表， 按shardingkey来分片； 数据量太大的场景 mysql表的数据量一般控制在千万级别，如果再大的话，就要考虑分库分表。 除了分表外，列举了面对海量数据业务的一些常见优化手段 缓存加速 读写分离 垂直拆分 分库分表 冷热数据分离 ES助力复杂搜索 NoSQL NewSQL 分表后ID如何保证全局唯一 分库分表后，多张表共用一套全局id，原来单表主键自增方式满足不了要求。 我们需要重新设计一套id生成器。 特点：全局唯一、高性能、高可用、方便接入。 UUID 数据库自增ID 数据库的号段模式，每个业务定义起始值、步长，一次拉取多个id号码 基于Redis，通过incr命令实现ID的原子性自增。 雪花算法（Snowflake） 市面的一些开源框架，如：百度（uid-generator），美团（Leaf）， 滴滴（Tinyid）等 分表后可能遇到的问题 分表后，与单表的最大区别是有分表键sharding_key，用来路由具...
评论
