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语句，事务已经获得的锁，正在等待的锁，以及被回滚的事务