MySQL基础知识

最新推荐文章于 2021-01-27 11:25:57 发布

_LittleFish

最新推荐文章于 2021-01-27 11:25:57 发布

阅读量223

点赞数

分类专栏： MySQL 文章标签： MySQL 事务锁

本文链接：https://blog.csdn.net/qq_34414876/article/details/87076180

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

数据库两种重要的锁策略：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。只有在存储引擎层实现，而MySQL服务器层没有实现

锁粒度：一种提高共享资源的并发性的方式就是让锁定的对象更具有选择性。

死锁：死锁一般是事务相互等待对方资源，最后形成环路，而无法继续运行。InnoDB目前处理死锁的方法是：将持有最少的行级排他所的事务进行回滚。

基于事务类型的锁：

共享锁：如果事务T对数据A加上共享锁，则其他事务只能对A再加共享锁，不能加排它锁。只有事务T可以对A进行读取和修改，其他事务只能读取数据而不能修改数据，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排它锁：如果事务T对数据A加上排它锁，只有事务T可以对A进行读取和修改，其他任何事务都不能对A进行读取和修改。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。

产生死锁的原因：

系统资源不足；
进程运行推进的顺序不合适；
资源分配不当等；

如何有效降低死锁：

按同一顺序访问资源；
避免事务中的用户交互；
保持事务简短并在一个批处理中；
使用低隔离级别；
使用绑定连接；

一、InnoDB与MyISAM的区别

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)
1. InnoDB支持事务，MyISAM不支持。
2. InnoDB支持外键，而MyISAM不支持。
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

MyISAM结构：

  每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

  .frm文件存储表定义。

  数据文件的扩展名为.MYD (MYData)。

  索引文件的扩展名是.MYI (MYIndex)。

InnoDB结构：

基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

每个InnoDB在磁盘上存储成两个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。

数据索引文件的扩展名为.ibd(InnoDB data)

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. Innodb不支持全文索引（MySQL 5.6以后开始支持），而MyISAM支持全文索引，查询效率上MyISAM要高；
6. InnoDB支持行级锁，MyISAM支持表级锁。
如何选择：
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受

二、事务

什么是 MySQL 的事务？事务都有那些特性？

事务是一个序列操作，其中的操作要么都执行，要么都不执行，它是一个不可分割的工作单位，ACID 四大特性是事务的基础。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

说起 MySQL 的事务，就会谈起并行事务而引发的问题，比如脏读、幻读和不可重复读。

什么是脏读、幻读和不可重复读？会产生什么问题？该如何解决？

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务再修改数据。那么第一个事务两次读到的的数据可能是不一样的，因此称为是不可重复读。
幻读：当第一个事务正在执行插入或删除操作同时，第二个事务也在操作此表的数据，此事第二个事务第一次读取的数据和第二次读取的数据会因为第一个事物的插入或删除导致相同的查询条件，查询出来的条数不同，产生幻觉，因此成为幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以操作此数据，则可避免该问题。

所以针对以上的问题，事务有不同的隔离级别来解决这个问题

MySQL事务隔离级别（是否对应着该级别是否会产生对应的问题）：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）没有提交事务，对其他事务可见	是	是	是
读已提交（read-committed）没有提交事务，对其他事务不可见	否	是	是
可重复读（repeatable-read） MySQL默认级别	否	否	是
串行化（serializable）最高级别	否	否	否