mysql 引擎，锁

mysql 引擎，锁

引擎

概述

存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。存储引擎的概念是MySQl的特点，而且是一个插入式的存储引擎概念。这就决定了MySQl数据库中的表可以使用不同的存储方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

引擎分类

1. InnoDB存储引擎

   InnoDB是Mysql数据库的一种存储引擎。InnoDB给Mysql的表提供了 事务、回滚、崩溃修复能力、多版本并发控制的事务安全、间隙锁（可以有效的防止幻读的出现）、支持辅助索引、聚簇索引、自适应hash索引、支持热备、行级锁。还有InnoDB是Mysql上唯一一个提供了外键约束的引擎。

   InnoDB存储引擎中，创建的表的表结构是单独存储的并且存储在.frm文件中。数据和索引存储在一起的并且存储在表空间中。但是默认情况下mysql会将数据库的所有InnoDB表存储在一个表空间中的。其实这种方式管理起来非常的不方便而且还不支持高级功能所以建议每个表存储为一个表空间实现方式为：使用服务器变量innodb_file_per_table = 1。

   如果需要频繁的进行更新、删除操作的数据库也可选择InnoDB存储引擎。因为该存储引擎可以实现事务提交和回滚。

2. MyISAM存储引擎

   MyISAM存储引擎是Mysql中常见的存储引擎，MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM支持全文索引、压缩存放、空间索引（空间函数）、表级锁、延迟更新索引键。但是MyISAM不支持事务、行级锁、更无法忍受的是崩溃后不能保证完全恢复（只能手动修复）。

   MyISAM存储引擎的表存储成3个文件。文件的名字和表的名字相同。扩展名包含frm、MYD、MYI。其中frm为扩展名的文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其为MYIndex的缩写。

   MyISAM存储引擎的插入数据很快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM存储引擎能够实现处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。

3. MEMORY存储引擎

   MEMORY存储引擎是Mysql中的一类特殊的存储引擎。其使用存储在内存中的内存来创建表，而且所有数据保存在内存中。数据安全性很低，但是查找和插入速度很快。如果内存出现异常就会影响到数据的完整性，如果重启或关机，表中的所有数据就会丢失，因此基于MEMORY存储引擎的表的生命周期很短，一般都是一次性的。适用于某些特殊场景像查找和映射，缓存周期性的聚合数据等等。

4. NDB 集群引擎

   作为sql和NDB元素协议之间的接口,用于mysql,ndb集群存储引擎,分布式等

其他引擎:了解即可

1. CSV

   CSV是将数据文件保存为CSV格式的的文件的，可以方便的导入到其他数据库中去（例如：excel表格，SQLserver等等），由此需要在数据库间自由共享数据时才偶尔建议使用此存储引擎。并且它也不支持索引；个人认为仅适用于数据交换。

2. ARCHIVE

   ARCHIVE，见名之意可看出是归档，所以归档之后很多的高级功能就不再支持了仅支持插入（insert）和查询（select）两种功能， ARCHIVE存储引擎之前还不支持索引（在Mysql5.5以后开始支持索引了），但是它拥有很好的压缩机制。通常用于做仓库使用。

   ARCHIVE存储引擎适用于存储日志信息或其他按时间序列实现的数据采集类的应用场景中。

3. BLACKHOLE

   BLACKHOLE叫做黑洞，也就是说没有存储机制，任何数据都会被丢弃，但是会记录二进制日志。一般在Mysql复制（中继服务器）中经常用到

4. FEDERATED

   FEDERATED可以实现跨服务器整理表，简单说就是它可以访问远程服务器上数据的存储引擎，所以说它不再本地创建数据只会自动的建立一个连接到其他服务器上链接，有点类似于代理的功能，默认都是禁用的。

mysql 优化建议（记住）:

1. 尽可能让所有的数据检索都通过索引来完成
2. 合理设计索引，使索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
3. 尽量控制事务的大小，减少锁定的资源量和锁定时间长度
4. 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
5. 尽可能按照相同的访问顺序来访问，防止产生死锁
6. 尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

锁

概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

读写锁

在处理并发的读或者写时,可以通过实现一个由两种类型的锁组成的锁系统来解决上面概述中的问题。这两种锁通常称为共享锁 排它锁,或者叫做读锁,写锁

颗粒度

1. 表级锁定（table-level）
   表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
   当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
   使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
2. 行级锁定（row-level）
   行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
   虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
   使用行级锁定的主要是InnoDB存储引擎。
3. 页级锁定（page-level）
   页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
使用页级锁定的主要是BerkeleyDB存储引擎。
总的来说，MySQL这3种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

表级锁

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎。

1. MySQL表级锁的锁模式

   MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性：

   对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

   对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

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

2. 如何加表锁

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

3. MyISAM表锁优化建议
   对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小，锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。所以，在优化MyISAM存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

   1. 查询表级锁争用情况

      
      mysql root@localhost:(none)> show status like 'table_lock%'
      +-----------------------+-------+
      | Variable_name         | Value |
      +-----------------------+-------+
      | Table_locks_immediate | 101   |
      | Table_locks_waited    | 0     |
      +-----------------------+-------+
      
      

      这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：
      Table_locks_immediate：产生表级锁定的次数；
      Table_locks_waited：出现表级锁定争用而发生等待的次数；
      两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

   2. 缩短锁定时间

      如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。

      1. 尽两减少大的复杂Query，将复杂Query分拆成几个小的Query分布进行；

   3. 尽可能的建立足够高效的索引，让数据检索更迅速；

   4. 尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；

   5. 利用合适的机会优化MyISAM表数据文件。

   6. 分离能并行的操作

行级锁

1. –>