Mysql存储引擎以及锁机制

一、常用命令

1、查看引擎(默认为InnoDB)

  • 查看mysql提供的存储引擎:show engienes
  • 查看mysql当前默认的存储引擎:show variables like '%storage_engine%'
  • 查看某张表用了什么引擎:show create table 表名

2、修改引擎

  • 修改表的存储引擎:alter table 表名 engine = 存储引擎

二、mysql存储引擎

1、mysql所支持的存储引擎

2、四种常用的存储引擎

如果要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB是一个好的选择

如果数据表主要用来插入和查询记录,MyISAM拥有较高的插入、查询速度,则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果

如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive

使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能

三、MyISAM与InnoDB区别

1、构成上的区别(底层实现)

MyISAM(B+树):每个MyISAM在磁盘上存储成三个文件。每个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)。索引文件的扩展名是.MYI(MYIndex)。

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

2、auto_increment

(对于AUTO_INCREMENT类型的字段,InnoDB中必须单独建索引,而在MyISAM中可以和其他字段一起建立联合索引。)

innodb引擎下自增id

  • innodb引擎下,如果显示insert了最大值,那么下次的AUTO_INCREMENT值就是这个最大值+1
  • 如果这时候再把其中一个id值update成105,那么下次的AUTO_INCREMENT却还是不变
  • 如果这时再利用自增段去插入,到了105的时候是会报错的
  • 这时继续插入,不会报错,因为刚才即使报错了,AUTO_INCREMENT值依旧会增加
  • 重启mysql服务后,AUTO_INCREMENT变为1

Myisam引擎下自增id

  • myisam引擎下,如果显示insert了最大值,那么下次的AUTO_INCREMENT值就是这个最大值+1
  • 如果这时候再把其中一个id值update成105,那么下次的AUTO_INCREMENT就会变成106(这和innodb是不同的!)
  • 当db重启后,myisam引擎的AUTO_INCREMENT值不变

3、事物处理

MyISAM类型支持的表强调的是性能,其执行的速度比InnoDB类型更快,但是不提供事务支持

InnoDB提供事物支持,外部键等高级数据库功能

4、锁机制

在执行数据库写入操作(insert、update、delete)的时候,
MyISAM会锁表,
而Innodb默认会锁行,但也会出现锁表的情况

5、读取行数

没有where的SELECT COUNT(*):MyISAM始终保留一张表的行数,因此这条语句几乎瞬间就可以执行完,而Innodb会一行行的累加,要扫描一张表来计算有多少行

四、mysql锁机制

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。
加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

1、基本锁类型

锁包括行级锁和表级锁

行级锁:开销大,加锁慢;会出现死锁;锁定力度最小,发生锁冲突的概率最低,并发度也最高

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

2、MyISAM表锁

MySQL表级锁有两种模式:表共享锁(Table Read Lock)和表独占写锁(Table Write Lock)。

  • 对MyISAM的读操作,不会阻塞其他session对同一表请求,但会阻塞对同一表的写请求;
  • 对MyISAM的写操作,则会阻塞其他session对同一表的读和写操作;
  • MyISAM表的读操作和写操作之间,以及写操作之间是串行的。

当一个线程获得对一个表的写锁后,只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

加锁方式

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

显式加锁

(1)表独占写锁(lock table A write)

  • 获得表A的WRITE锁定
  • 当前session对锁定表的查询、更新、插入操作都可以执行,其他session对锁定表的查询被阻塞,需要等待锁被释放,陷入等待状态
  • 释放锁后,其他session获得锁,查询结果返回

(2)表共享读锁(lock table A read)

  • 获得表A的READ锁定
  • 当前session可以查询该表记录,其他session也可以查询该表的记录
  • 当前session不能查询没有锁定的表,其他session可以查询或者更新未锁定的表
  • 当前session插入或者更新锁定的表都会提示错误,其他session更新锁定表会等待获得锁,陷入等待状态
  • 释放锁后,其他session获得锁,更新操作完成

并发插入(lock table A read local)

上面提到的MyISAM表的读和写是串行的,但那时就总体而言,在一定条件下,MyISAM表也支持查询与插入操作的并发进行


MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。

  • 0:不允许并发插入
  • 1:没有空洞(即表的中间没有被删除的行),允许一个进程读表的同时,另一个进程从表尾插入记录(更新会等待),这也是默认设置,注意:空洞问题可以通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片,回收因删除记录而产生的中间空洞
  • 2:无论有没有空洞,都允许在表尾并发插入记录

例子:session1获得一个表的READ LOCAL锁,该session虽然可以对表进行查询操作,但不能对表进行更新操作;其他session虽然不能对表进行删除和更新操作,但可以对表进行并发插入操作,这里假设该表中间不存在空洞

MyISAM的锁调度问题

MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求更重要。这也是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。我们可以通过一些设置来调节MyISAM的调度行为。

  • 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
    在my.cnf的配置方法[mysqld] low-priority-updates
  • 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低
  • 通过指定INSERT UPDATE DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

3、InnoDB行锁

对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,事务可以通过以下语句显示给记录集加共享锁或排他锁。

(1)共享锁(S):SELECT * FROM table_name WHERE ...LOCK IN SHARE

允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A。其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。
 这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

  • set autocommit = 0(通过以上设置autocommit=0,则用户将一直处于某个事务中,直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。)
  • 当前session对id = 10的记录加share mode的共享锁;其他session仍然可以查询记录,并也可以对该记录加share mode的共享锁
  • 当前session对锁定的记录进行更新操作,等待锁;其他session也对该记录进行更新操作,则会导致死锁退出
  • 当前session获得锁后,可以成功更新

(2)排他锁(X):SELECT * FROM table_name WHERE ...FOR UPDATE

允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁
又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A。其他事务不能再对A加任何锁,直到T释放A上的锁。
  这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

  • set autocommit = 0
  • 当前session对id = 10的记录加for update的排他锁;其他session可以查询该记录,但是不能对该记录加排他锁,会等待获得锁
  • 当前session可以对锁定的记录进行更新操作,更新后释放锁
  • 其他session获得锁,得到其他session提交的记录

(3)行锁的实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过检索条件检索数据,InnoDB才使用行级锁,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁,也就是使用表锁

(4)什么时候使用表锁

对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表锁

  • 第一种情况:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下一颗考虑使用表锁里提高事务的执行速度。
  • 第二种情况:事务涉及多张表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁,减少数据库因事务回滚带来的开销。

当然,应用中的这两种事务不能太多,否则,就应该考虑使用MyISAM表了。

(5)关于死锁

所谓死锁,是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去,此时称系统处于死锁状态或系统产生了死锁

典型的死锁问题


两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环等待就是典型的死锁(都是for update排他锁, 一个事务select 表A id1,另一个事务select 表B id1,第一个事务又select 表B id1,第二个事务select 表A id1)

通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句,绝大多数死锁都可以避免。

解决办法

  • 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。

由于两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免

  • 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应该先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁

一个事务申请了共享锁之后无法再申请排他锁(也就是保证事务T在查询操作加了共享锁之后无法执行修改)

  • 在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT ... FOR UPDATE加排他锁,在没有符合该条件记录的情况下,两个线程都会加锁成功。程序发现记录尚不存在,就视图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTEND,就可避免问题

改进措施

  • 尽量使用较低的隔离级别;
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小
  • 给记录集显式加锁时,最好一次性请求足够级别的锁,比如要修改数据,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行,这样可以大大减少死锁的机会
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少发生死锁的几率。
    在了解InnoDB锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括以下一项
    如果出现死锁,可以用SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果包括死锁相关事务的详细信息,如引发死锁的SQL语句,事务已经获得的锁,正在等待的锁,以及被回滚的事务
原文地址:https://www.cnblogs.com/sxkgeek/p/9392733.html