Mysql实践
1. mysql只是update加锁么?
UPDATE rent_contacts SET contacts_mobile='11111' WHERE rent_unit_code in (SELECT rent_unit_code FROM rent_unit );
第一种情况: 条件无索引
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ('3424342' );
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二执行受阻,sql一commit之后,sql二可执行成功
结论:条件无索引,锁表
第二种情况: 条件有索引,in语句是确定的值
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ('3424342' );
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二正常执行,sql二不依赖sql一的commit
结论:条件有索引,锁行
第三种情况: 条件有索引,in语句是复杂查询
sql一执行:
BEGIN;
UPDATE contacts SET mobile='11111' WHERE code in ( select code from user where id=1);
sql二执行 UPDATE contacts SET mobile='11111' WHERE code in ('1111' );
sql二执行受阻,sql一commit,sql二正常执行
结论:条件有索引,in语句是不确定的值,锁表
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
2. mysql中共享锁(读锁)和排他锁(写锁)
| 读锁 | 写锁 | |
| 读锁 | 兼容 | 冲突 |
| 写锁 | 冲突 | 冲突 |
在B字段的索引数据也是有序的情况下才能使用复合索引，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，B才是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。
在二级索引里，对于相同的索引键值，索引行将会按照附带的主键值顺序升序存储。
4. mysql查看有没有走索引
用expalin关键字查出来的结果,type字段包含有index和ref.
对表访问方式(type)，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
select * from table where id = 1 for update;
id 是主键的时候，本条sql在Index Key阶段可以确定唯一一条数据，所以会在聚簇索引上加Record Lock
id 是普通索引的时候，本条sql在Index Key阶段筛选出的数据不具有唯一性，所以Innodb为了防止幻度，会加Gap Lock+Next-Key Lock(Repeatable Read 事务隔离级别下,在Table Filter阶段对相应的聚簇索引上加Record Lock
id 不是索引的时候，本条sql在Table Filter阶段进行全表扫描，会在所有的聚簇索引上加锁，相当于全表锁，这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么innodb引擎层面就会将所有记录对应的聚簇索引加锁后返回，然后由MySQL Server层进行过滤，在高版本的mysql中会将不符合的记录再解锁
如果用到了主键索引，mysql会锁定主键索引，如果用到了非主键索引，msyql会先锁定非主键索引，再锁定主键索引。如果两条sql执行间隔时间非常短的话就会出现资源争夺的情况，从而造成死锁。
1、InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
2、由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。
3、当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
4、即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
5、检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。
lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
锁是加在索引上的, 如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段.
6. Mysql的写锁必须加在事务里
假设商品表单products 内有一个存放商品数量的quantity ，在订单成立之前必须先确定quantity 商品数量是否足够(quantity>0) ，然后才把数量更新为1。代码如下:
SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;
这是不安全的, 少量的状况下或许不会有问题，但是大量的数据存取「铁定」会出问题。如果我们需要在quantity>0 的情况下才能扣库存，假设程序在第一行SELECT 读到的quantity 是2 ，看起来数字没有错，但
是当MySQL 正准备要UPDATE 的时候，可能已经有人把库存扣成0 了，但是程序却浑然不知，将错就错的UPDATE 下去了。因此必须透过的事务机制来确保读取及提交的数据都是正确的。
7. for update怎么加锁
for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。
如果for update没有命中索引,会锁表,命中索引则是行锁. update更新语句和for update机制一样，没有命中索引，同样会锁表。
RR级别, select for update 锁不存在的行,会产生间隙锁,此时如果有其他的for update语句可能产生死锁,需要特别注意. 以下查不到数据情况暂不考虑RR级别.
- 只根据主键进行查询，并且查询到数据，主键字段产生行锁。
- 只根据主键进行查询，没有查询到数据，不产生锁。
- 根据主键、非主键含索引（name）进行查询，并且查询到数据，主键字段产生行锁，name字段产生
- 根据主键、非主键含索引（name）进行查询，没有查询到数据，不产生锁。
- 根据主键、非主键不含索引（name）进行查询，并且查询到数据，如果其他线程按主键字段进行再次查询，则主键字段产生行锁，如果其他线程按非主键不含索引字段进行查询，则非主键不含索引字段产生表锁，如果其他线程按非主键含索引字段进行查询，则非主键含索引字段产生行锁，如果索引值是枚举类型，mysql也会进行表锁。
- 根据主键、非主键不含索引（name）进行查询，没有查询到数据，不产生锁。
- 根据非主键含索引（name）进行查询，并且查询到数据，name字段产生行锁。
- 根据非主键含索引（name）进行查询，没有查询到数据，不产生锁。
- 根据非主键不含索引（stock）进行查询，并且查询到数据，stock字段产生表锁。
- 根据非主键不含索引（stock）进行查询，没有查询到数据，stock字段产生表锁。
- 只根据主键进行查询，查询条件为不等于，并且查询到数据，主键字段产生表锁。
- 只根据主键进行查询，查询条件为不等于，没有查询到数据，主键字段产生表锁。
- 只根据主键进行查询，查询条件为 like，并且查询到数据，主键字段产生表锁。
- 只根据主键进行查询，查询条件为 like，没有查询到数据，主键字段产生表锁。
事务A锁住了表中的一行，让这一行只能读，不能写。
之后，事务B申请整个表的写锁。
如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。
数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。
数据库要怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。
注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。
于是就有了意向锁。
在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。
在意向锁存在的情况下，上面的判断可以改成
step1：不变
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。
- 记录锁、间隙锁、临键锁，都属于排它锁；
- 记录锁就是锁住一行记录；
- 间隙锁只有在事务隔离级别 RR 中才会产生；
- 唯一索引只有锁住多条记录(between)或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
- 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
- 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
- 普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；
- 事务级别是RC（读已提交）级别的话，间隙锁将会失效。
- session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
- session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
- session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
- session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。
11. RR隔离级别先update再select可以得到最新数据么
可以.即使是MVC一致性读, 所有update语句都是先用“当前读”得到最新版本数据然后再更改,之后的select得到的是更新后的最新数据. for update加锁的话, 所有select也都是当前读得到的最新值.
12. in查询可以用到索引么
如果in查询是在符合最左原则的前提下，是可以正常使用的索引的。但是如果in查询的前面有范围查询，那么联合索引失效，自然我们的in查询也就用不到索引了.
select * from tableA where a in (
select a from tableB
)
另外如果如以上sql, 内层数据过大则外层可能选择扫表, 此时可以选择用join
13. 为什么事务只能在一个数据库上生效?
本地应用连接数据库实例是通过connection做到的, 一个事务注定只能有一个connection, 否则跨多个connection就是分布式的了, 需要分布式事务.
14. 一次死锁排查
三个线程同时去做以下事情：
a. 唯一键加读锁
b. 如果没读到记录，则insert一条
出现了死锁问题，数据库隔离级别为Read-Commited。RR级别下有间隙锁概念，对不存在的唯一键加写锁可能导致死锁。如果是RC级别，没有间隙锁，加读锁没问题，但是并发的insert且唯一键相同时，如果其中一个insert失败回滚则会导致死锁。
15. 互联网公司一般设置隔离级别为Read Committed而不是Repeatable Read
a. RR级别下存在间隙锁，大大增加死锁概率
b. RR级别下为命中索引的查询select for update锁全表
c. RC级别不解决幻读问题，所以对于并发的行需要主动加读锁或者写锁
d. 互联网业务场景下，需要加入间隙锁进而不允许插入新数据的情况比较少
e binlog同步选择Row模式而不是mixed或者statement，其他两种都可能导致主从不一致问题
16. 死锁问题
并发插入时候，如果同时又三个或以上的唯一键相同的插入会导致死锁
并发更新一组数据时候如果顺序不一致有可能导致死锁，解决方式可以是把这组数据排序一个个更新。
update table set A = ? where B = ? and C != ?如果B和C都在二级索引里，这次update如果C值不匹配数据库不会加锁，可以提高性能。
开事务去更新一组数据，被数据库设置的超时kill掉，事务有可能不会正确回滚，导致大量空事务等待锁，使数据库CPU超负荷。
17. 避免扫库SQL
在流量或并发很高的时候，扫库类的sql会给数据库带来极大的负荷。一方面数据库一定要加超时kill来避免过慢的sql，另一方面，sql不能扫全表或者大半个表且走很慢的索引。
【推荐】还在用 ECharts 开发大屏？试试这款永久免费的开源 BI 工具！
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步