mysql实践_Mysql实践

勋南星

于 2021-02-08 07:35:02 发布

阅读量113

点赞数

文章标签： mysql实践

本文链接：https://blog.csdn.net/weixin_42500720/article/details/113949961

版权

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中共享锁(读锁)和排他锁(写锁)

先加读锁(LOCK IN SHARE MODE), 写锁(FOR UPDATE)会等读锁释放后再获得. 先加写锁, 读锁会被block到写锁释放.

读锁

写锁

读锁

兼容

冲突

写锁

冲突

3. mysql复合索引如何建立

mysql复合索引遵循最左原则, idx(A, B, C) =》 A, A+B, A+B+C都会走索引.

当有范围查询时候比如0 < A < 2这种, 会导致A走索引做范围查询但是之后的B和C都不会再走索引. 所以一定要先找到等值查询走索引然后再范围查询.

order by的字段也是需要排序的,但是大概率是在内存中排序sort buffer. group by的字段如果走索引或者有序可以大大提高效率,否则会在内存中建临时表和sort buffer等来分组和计数.可以用explain来查看group by语句的Extra字段

看是否用了临时表. union语句需要唯一限制,也可能建临时表. distinct和group by, union逻辑上有一定的相同,都是建临时表,加唯一索引,然后插入得到唯一的结果.

解释:

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个A字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的B字段进行排序。其实就相当于实现了类似 order by A B这样一种排序规则。

所以：第一个A字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个B字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

在B字段的索引数据也是有序的情况下才能使用复合索引，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，B才是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。

在二级索引里，对于相同的索引键值，索引行将会按照附带的主键值顺序升序存储。

4. mysql查看有没有走索引

用expalin关键字查出来的结果,type字段包含有index和ref.

index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

对表访问方式(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在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

另外也需要关注possible keys和key来看最终选择了哪个索引.

5. 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，没有查询到数据，主键字段产生表锁。

8. 意向锁有啥用

事务A锁住了表中的一行，让这一行只能读，不能写。

之后，事务B申请整个表的写锁。