Mysql事务和锁——常见问题总结

数据库事务隔离

• 读未提交：一个事务可以读到另一个事务修改但是未提交的数据，会产生脏读、幻读；
• 读已提交：一个事务可以读取到另一个事务修改并且提交了的数据；会产生幻读；
• 可重复读：不会产生幻读，在一个事务中读取不到被别的事务修改了的数据，避免了幻读；
• 串行化读：事务一个一个执行，同一个数据只能由一个事务来访问。每次读取都需要获得表级共享锁，读写相互都会阻塞。

在InnoDB索引下，查询操作通过MVCC多版本并发控制来实现，事务修改数据后就会产生一个数据版本，版本之间使用指针形成版本链，存在一个ReadView主要包含当前系统中未提交的事务id列表、当前未提交事务的最小id、分配给下一个事务的id、还有生成该ReadView的事务id；对于读未提交隔离级别每次只读最新版本的数据；对于串行化隔离级别使用加锁的方式来访问记录；对于读已提交每次读数据都会生成一个ReadView，查看当前未提交事务列表中是否存在要访问的事务id，如果存在则不能访问；对于可重复读，只在一个事务内第一次查询数据的时候生成一个ReadView，以后查询只访问第一个版本。

因为对于隔离级别的读的概念有误解，以为读就只是查询，其实读包含select、insert、update等等，所以MVCC又可分为快照读和当前读：

快照读就是可以读取历史版本；

当前读是只能读取最新的版本；

对于普通的select语句来说在RU、RC、RR隔离级别下都是快照读，在串行化级别下select属于当前读需要加锁。而对于像insert、update、delete、select…for update、select…lock in share mode这种语句语句来说都是当前读；并且读取之后防止防止记录被修改，除了最后一个加S锁，其余都要加X锁；对于串行化读已经从基于mvcc控制退化成基于lock锁来控制；

对于这种写操作或加锁操作来说是基于加锁来控制隔离级别的。在判断一条语句加了什么锁的时候需要知道：当前隔离级别、语句涉及的范围是全文扫描还是索引扫描、语句的条件是主键索引、唯一索引还是非唯一索引。

案例delete from t1 where id=10 对于RC和RR级别下有以下组合：

场景	加锁情况
RC+id主键	会对id=10这条记录加上X锁
RC+id唯一索引	会在id索引记录上加上X锁，并且对id=10对应主键对应的记录上也加上X锁
RC+id非唯一索引	会对所有满足id=10的索引记录上加X锁，并且对其所对应的主键对应的所有记录上加X锁
RC+id无索引	会进行全表扫描，全表所有的记录上都加上X锁，但是mysql做了优化，对于不满足条件的记录上的锁会释放
RR+id主键、RR+id唯一索引，由于这两个条件都只查询出一条记录，而RR级别都是针对幻读，所以这两种情况下与RC情况下一样
RR+id非唯一索引	与RC级别不同的是，RR级别下不仅会对满足的记录加上X锁，而且对满足的记录范围内还会加上gap间隙锁，用来防止幻读
RR+id无索引	所有记录都加上X锁，并且加上间隙锁，不过mysql也做了优化可以释放不满足条件记录上的锁，但是全表所有记录加锁这个过程是有的

使用索引应该注意的事项

• 建立索引会占用额外的空间；
• 索引会影响数据的增删改；
• 建立索引的列需要有高的区分度；
• 建立索引的列需要添加not null约束；
• 尽量使用主键索引查询，否则会出现回表，需要查询两次，先通过普通索引找到数据对应的主键，再通过主键查找主键索引。

索引和主键的区别

• 主键一定是唯一性索引，唯一性索引并不一定是主键；
• 一个表中可以建立多个索引，但只能有一个主键；
• 主键不允许出现空值，索引允许空值；

inner join、right join、left join

• inner join：内连接，在进行表连接的时候只保留两张表中完全匹配的结果集；
• right join：右连接，返回包括右表的所有记录和左表连接字段相等的记录；
• left join：左连接，返回包括左表的所有记录和右表连接字段相等的记录；

数据库表内连接和外链接的区别

内连接连接的两个表相匹配的行才能在结果集中出现，舍弃不匹配的数据；外连接又分为左外连接、右外连接和全外连接，对于左右外连接即使左右表中的行不匹配也会出现。对于全外连接，所有的行都会出现。

union和union all的区别

使用union合并后的表记录，会进行去重排序；而是用union all是将所有的记录都合并在一张表。

数据库事务ACID

• 原子性：事务是一个不可分割的整体，事务中包含的执行语句，要么全部执行，要么全部回滚；
• 一致性：事务执行前后数据库数据必须保持一致性状态，比如：执行转账操作后，两个账户的金额之和要保持不变；
• 隔离性：当两个或多个事务并发执行时，将事务内部的操作隔离起来，控制那些操作可以被别的事务看到，哪些操作不能被别的事务看到。
• 持久性：事务对数据库的修改操作应该是永久性的。

数据库的三范式

• 第一范式：数据表中的每一列必须是不可拆分的最小单位，保证每一列的原子性；
• 第二范式：要求表中的所有列，都必须依赖主键，而不能有任何一列与主键没关系；
• 第三范式：表中的每一列必须与主键有直接依赖，而不能存在依赖传递。
  可以避免数据冗余，减少数据库查询；

数据库分表

• 水平分表：解决表中记录太大的问题；带来的问题是通常需要多个表名，查询所有数据需要unoin操作。
• 垂直分表：一个表中有的列访问量大，有的列访问量小，可以将他们分离开，比如在之前的项目中将库存量的列和商品信息分成两个表，因为库存量经常修改，而商品信息相对改动较小。带来的问题是在查询所有数据时需要join表连接。

SQL优化

• 多使用explain来查看执行计划，分析出索引的合理性；
• 尽量避免select *;
• 使用where限定需要查询的数据，避免扫描多余行；
• 减少表连接数量；
• 尽量不使用首部模糊查询；

形成死锁的原因

事务A和事务B都需要访问a表和b表，事务A先访问a表加上X锁，事务B访问b表加上X锁，完了之后事务A等待事务B释放b表上面的锁，事务B等待事务A释放a表上面的锁，互相等待形成死锁。
对于并发情况下读锁升级为写锁形成死锁，因为读锁是共享的，所以同时可能有多个事务来查询，但是查询之后需要修改，就要等别的事务释放读锁，自己加上写锁，如果两个事务同时需要升级锁，都等待对方释放锁，形成死锁。
对于第一种情况可以顺序访问即等到A事务将两个表都访问完，B事务再进行访问。或者A事务访问完a表就释放a表上的锁，等待B事务访问完b表释放了之后再访问。

对于第二种情况可以使用乐观锁进行控制，在表中增加一个版本字段，每次修改数据都将版本+1，如果当前事务中的版本大于之前的版本就修改，如果小于则说明有别的事务进行修改，当前事务就丢弃数据，重新修改。悲观锁，可以使用更新所for update对于后续有修改的事务中的查询可以使用更新锁，别的事务只能普通查询，而不能for update查询，这样就避免了多个即将有修改操作的事务共享数据。

如何避免数据库中的死锁

• 事务按顺序访问记录；
• 使用低隔离级别；
• 大事务拆成小事务；
• 在一个事务中尽可能做到一次锁定所有需要的资源。

如何排查死锁

使用查看死锁的命令show engine innodb status

存储引擎的 InnoDB与MyISAM区别，优缺点，使用场景

• InnoDB的数据文件本身就是索引文件，其B+树的叶子节点就是记录数据；而MyISAM索引文件与数据文件分离，B+树叶子节点存储记录的地址。简单的说InnoDB根据主键引用被索引的行，而MyISAM通过数据的物理位置引用被索引的行。
• InnoDB是聚集索引，因为其索引的顺序和数据排列的数据一样；而MyISAM是非聚集索引，其索引的顺序和数据的顺序不一致。
• InnoDB提供事务支持、外部键等功能，MyISAM不支持事务，注重性能和执行速度。
• InnoDB在修改数据方面性能比较好，删除表时不会重新建立表，而是一行一行删除，而MyISAM则会重新建表，MyISAM在查询数据时比较快。
• MyISAM使用表锁，InnoDB提供行锁，不加锁读取，如果执行一个sql语句时不能确定扫描范围则会锁全表。
• 因为InnoDB是按主键聚集，所以表必须有主键，如果没有指定，则自动选择唯一标识列，如果不存在唯一标识列则生成6个字节长整形的隐含字段。

常见的索引类别

• 主键索引：数据库默认在主键上建立索引；
• 唯一索引：建立索引的列必须无重复值；
• 普通索引：最基本的索引；
• 全文索引：适用于关键字匹配查询。
• 组合索引：在多个列上建立一个索引；
• 哈希索引:基于哈希表实现，只有精确匹配索引才有效，对每一行数据，存储引擎会对所有的索引列计算一个哈希码，哈希索引将所有哈希码存储在索引中，同时也保存着每个数据行的指针。

建立索引的目的

索引的结构组织尽量减少查找过程中的磁盘IO存取次数。

为什么使用B+Tree作为索引结构

• 因为B+Tree的叶子节点大小设计为磁盘页的大小，根据局部性原理、磁盘预读，不用再一个数据一个数据读，而是一页一页的读，可以减少磁盘IO次数。
• B+Tree叶节点之间形成链表，适合范围查询。
• 利用红黑树也可以实现，但是由于红黑树的度比较小，导致它的高度很大，需要比较的次数也就增多，查询效率降低，而B+Tree的度可以很大。

索引失效的情况

• 查询条件中带or；
• 存在null值条件；
• 使用like 前缀匹配；
• mysql预估如果使用全表扫描更快则不使用索引。
• 如果索引列是字符串，而在语句中使用的时候不使用引号引起来，则会失效。
• 使用函数；

sql语句执行的过程

1. 客户端把语句发送给服务端；
2. 服务端进行语句解析：先查询高速缓存，将生成的hash值到缓存中检查是否存在，如果存在则直接执行。
3. 如果缓存中不存在则检查语法语义核对权限；
4. 如果sql语句中使用了绑定变量，则给绑定变量赋值；
   检查所需要的数据是否在缓冲区，对于select语句，如果存在则直接返回，不存在则从数据库文件中查询。对于insert、delete、update语句，如果不存在则将需要的数据从数据库文件中读取到缓存中，然后对需要修改的表进行行锁定，修改数据，将修改影响数据写入日志缓冲区，当日志缓冲区满了的时候会写入日志文件中，产生数据修改undo回滚日志，修改缓冲区，将修改的数据写入数据库文件。

什么是数据库分片

就是通过某种特定的条件，将存放在同一个数据库中的数据分散到多个数据库上面，以达到分散单台设备负载的效果。
有两种切分模式：

按照不同的表来切分到不同的数据库，称为垂直切分；
还有一种是按照表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库上，称为水平切分。
当数据库分片后，数据由一个数据库分散到多个数据库，此时系统要查询时需要切换不同的数据库进行查询，处理起来比较麻烦，可以使用Mycat(数据库中间件产品)来解决。mycat支持mysql集群，提供高可用性数据分片集群。

发现sql语句执行很慢的情况分析

分为两种情况：偶尔一条执行的很慢、大多数情况都执行的很慢；

偶尔很慢的情况

数据库在刷新脏页：当我们往数据库中插入或修改一条数据的时候，是在内存中将这个数据改变然后记录到日志中，等到空闲的时候将日志中的内容更新到磁盘中去，但是会出现几种情况时写入磁盘提前不必等到空闲时候，日志写满了、内存不够用了，这些情况都导致日志空间不足，所以会暂停其他操作，优先将数据同步到磁盘。导致这个sql语句执行比较慢；
这个sql语句拿不到锁：如果这个sql语句涉及到的表正在被别的事务使用，则需要等到别的事务执行完释放锁之后才能执行，这时可以用show processlist来查看当前状态。

针对一致都很慢的情况

没有索引：没建立索引或者建立了索引没有用到、函数操作导致没有用上索引。
数据库自己选错了索引：在执行sql前数据库会先判断是走索引快还是全表扫描快，因为有的区分度不大的索引会导致执行之间比全表扫描还慢，因为还存在查询索引。数据库会采用取样的方法取一部分索引来计算索引的区分度和索引的基数，如果索引字段的区分度大就走索引，否则直接扫描全表。而有可能在抽样的过程中恰好取到了很多相同的字段值，实际上索引区分度很大，就会导致数据库走全表扫描。降低效率。