性能优化专题（三）MySQL数据库

﹏ヽ暗。殇╰゛Y 2023-02-17 03:53 16阅读 0赞

**目录**

数据库三大范式是什么

MySQL逻辑架构

MySQL查询过程

MySQL存储引擎

MyISAM与InnoDB区别

InnoDB引擎的4大特性

查询缓存

性能优化建议

Scheme设计与数据类型优化

创建高性能索引

MySQL索引

索引原理

索引的数据结构

b+树

b+树的查找过程

b+树性质

b树和b+树的区别

索引有哪几种类型？

建索引的几大原则

特定类型查询优化

优化COUNT()查询

优化关联查询

优化LIMIT分页

优化UNION

优化WHERE子句

事务

什么是数据库事务？

事物的四大特性(ACID)介绍一下?

什么是事务的隔离级别？MySQL的默认隔离级别是什么？

什么是脏读？不可重复读？幻读？

锁

从锁的类别上分MySQL都有哪些锁呢？

隔离级别与锁的关系

MySQL中InnoDB引擎的行锁是怎么实现的？

数据库的乐观锁和悲观锁是什么？怎么实现的？

--------------------

# 数据库三大范式是什么 #

第一范式：每个列都不可以再拆分，1NF是对属性的**原子性**。

第二范式：非主键列完全依赖于主键，2NF是对记录的**惟一性**。

第三范式：非主键列不依赖于其他非主键列，3NF是对字段的**冗余性**，要求任何字段不能由其他字段派生出来。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

# **MySQL逻辑架构** #

如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。

![format_png][]

MySQL逻辑架构，来自：高性能MySQL

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。  
  
MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。  
  
最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

# **MySQL查询过程** #

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：**很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已**。  
  
当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？

![format_png 1][]

*  客户端向MySQL服务器发送一条查询请求
 *  服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
 *  服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
 *  MySQL根据执行计划，调用存储引擎的API来执行查询
 *  将结果返回给客户端，同时缓存查询结果

# MySQL存储引擎 #

常用的存储引擎有以下：

*  **Innodb引擎**：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
 *  **MyIASM引擎**(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

## MyISAM与InnoDB区别 ##

<table> 
 <thead> 
  <tr> 
   <th>&nbsp;</th> 
   <th>MyISAM</th> 
   <th>Innodb</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储结构</td> 
   <td>每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件</td> 
   <td>每张表被存放在两个文件：frm-表格定义，ibd-数据和索引文件，都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB</td> 
  </tr> 
  <tr> 
   <td>存储空间</td> 
   <td>MyISAM可被压缩，存储空间较小</td> 
   <td>InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引</td> 
  </tr> 
  <tr> 
   <td>可移植性、备份及恢复</td> 
   <td>由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作</td> 
   <td>免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了</td> 
  </tr> 
  <tr> 
   <td>文件格式</td> 
   <td>数据和索引是分别存储的，数据.MYD，索引.MYI</td> 
   <td>数据和索引是集中存储的，.ibd</td> 
  </tr> 
  <tr> 
   <td>记录存储顺序</td> 
   <td>按记录插入顺序保存</td> 
   <td>按主键大小有序插入</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>锁支持</td> 
   <td>表锁</td> 
   <td>行锁、表锁，锁定力度小并发能力高</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>MyISAM更优</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>INSERT、UPDATE、DELETE</td> 
   <td>&nbsp;</td> 
   <td>InnoDB更优</td> 
  </tr> 
  <tr> 
   <td>select count(*)</td> 
   <td>myisam更快，因为myisam内部维护了一个计数器，可以直接调取。</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>索引的实现方式</td> 
   <td>B+树索引，myisam 是堆表</td> 
   <td>B+树索引，Innodb 是索引组织表</td> 
  </tr> 
  <tr> 
   <td>哈希索引</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>聚簇索引</td> 
   <td>MyISAM索引是非聚簇索引</td> 
   <td>InnoDB索引是聚簇索引</td> 
  </tr> 
  <tr> 
   <td>叶子节点</td> 
   <td>MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据</td> 
   <td>InnoDB主键索引的叶子节点存储着行数据，因此主键索引非常高效；InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效</td> 
  </tr> 
 </tbody> 
</table>

## InnoDB引擎的4大特性 ##

*  插入缓冲（insert buffer)
 *  二次写(double write)
 *  自适应哈希索引(ahi)
 *  预读(read ahead)

# **查询缓存** #

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。  
  
MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。  
  
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果  
都不会被缓存。比如函数NOW()或者CURRENT\_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT\_USER或者CONNECION\_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。  
  
既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。

# **性能优化建议** #

## **Scheme设计与数据类型优化** ##

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。  
  
这里总结几个可能容易理解错误的技巧：

*  通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
 *  对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用16为存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
 *  UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
 *  schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

## **创建高性能索引** ##

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

# MySQL索引 #

## 索引原理 ##

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

## 索引的数据结构 ##

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

![b+树][b]

### b+树 ###

如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

### b+树的查找过程 ###

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，**3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的**，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

### b+树性质 ###

1. 通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2. 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

### b树和b+树的区别 ###

*  在b树中，你可以将键和值存放在内部节点和叶子节点；但在b+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
 *  b+树的叶子节点有一条链相连，而b树的叶子节点各自独立。
    
    ![img][]

## 索引有哪几种类型？ ##

**主键索引：**数据列不允许重复，不允许为NULL，一个表只能有一个主键。

**唯一索引：**数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

**普通索引：**基本的索引类型，没有唯一性的限制，允许为NULL值。

**全文索引：**目前搜索引擎使用的一种关键技术。

## 建索引的几大原则 ##

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到**范围查询**(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3. 尽量选择区分度高的列作为索引，若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)。

4. 索引列不能参与计算，保持列“干净”，比如from\_unixtime(create\_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create\_time = unix\_timestamp(’2014-05-29’)。

5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6. 较频繁作为查询条件的字段才去创建索引，更新频繁字段不适合创建索引。

# **特定类型查询优化** #

## **优化COUNT()查询** ##

最简单的就是当使用COUNT(\*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。  
  
我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用COUNT(\*)，意义清晰，且性能更好。

## **优化关联查询** ##

在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

*  确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，**如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引**。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，**只需要在关联顺序中的第二张表的相应列上创建索引**（具体原因下文分析）。
 *  确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。  
  
太抽象了？以上面的示例来说明，比如有这样的一个查询：

SELECT A.xx,B.yy   
    FROM A INNER JOIN B USING(c)  
    WHERE A.xx IN (5,6)

假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);  
    outer_row = outer_iterator.next;  
    while(outer_row) {  
        inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;  
        inner_row = inner_iterator.next;  
        while(inner_row) {  
            output[inner_row.yy,outer_row.xx];  
            inner_row = inner_iterator.next;  
        }  
        outer_row = outer_iterator.next;  
    }

可以看到，最外层的查询是根据A.xx列来查询的，**A.c上如果有索引的话，整个关联查询也不会使用**。再看内层的查询，很明显**B.c上如果有索引的话，能够加速查询**，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

## **优化LIMIT分页** ##

当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，如果有对应的索引（覆盖索引），效率很高，因为索引默认在B+树的叶子节点（从左到右）从小到大的使用单向链表排序好了；否则，MySQL需要做大量的文件排序操作。

## **优化UNION** ##

除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。

## 优化WHERE子句 ##

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
    -- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
    select id from t where num=0

3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。  
4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
    -- 可以这样查询：
    select id from t where num=10 union all select id from t where num=20

5. in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 
    -- 对于连续的数值，能用 between 就不要用 in 了：
    select id from t where num between 1 and 3

6. 下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。  
7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num
    -- 可以改为强制查询使用索引：
    select id from t with(index(索引名)) where num=@num

8.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将无法使用索引。如：

-- 使用函数
    select id from t where substring(name,1,3)=’abc’
    -- 应改为：
    select id from t where name like ‘abc%’
    
    -- 使用算术运算
    select id from t where num/2=100
    -- 应改为:
    select id from t where num=100*2

# 事务 #

## 什么是数据库事务？ ##

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

## 事物的四大特性(ACID)介绍一下? ##

关系性数据库需要遵循ACID规则，具体内容如下：

*  **原子性**： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
 *  **一致性**： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
 *  **隔离性**： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
 *  **持久性**： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

## 什么是事务的隔离级别？MySQL的默认隔离级别是什么？ ##

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>隔离级别</td> 
   <td>脏读</td> 
   <td>不可重复读</td> 
   <td>幻读</td> 
  </tr> 
  <tr> 
   <td>READ-UNCOMMITTED</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>READ-COMMITTED</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>REPEATABLE-READ</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>SERIALIZABLE</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

这里需要注意的是：MySQL 默认采用的 REPEATABLE\_READ 隔离级别 Oracle 默认采用的 READ\_COMMITTED隔离级别

## 什么是脏读？不可重复读？幻读？ ##

*  **脏读：**【针对**未提交**数据】如果一个事务中对数据进行了更新，但**事务还没有提交**，另一个事务可以“看到”该事务没有提交的更新结果
 *  **不可重复读：【**针对其他提交前后，读取数据**本身**的对比】不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同
 *  **幻读：【**针对其他提交前后，读取数据**条数**的对比**】**幻读是指同样一笔查询在整个事务过程中多次执行后，查询所得的结果集是不一样的

# 锁 #

## 从锁的类别上分MySQL都有哪些锁呢？ ##

*  **共享锁：**又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
 *  **排他锁：**又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

## 隔离级别与锁的关系 ##

*  **Read Uncommitted：**读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
 *  **Read Committed：**读操作需要加共享锁，但是在语句执行完以后释放共享锁；
 *  **Repeatable Read：**读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
 *  **SERIALIZABLE：**限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

## MySQL中InnoDB引擎的行锁是怎么实现的？ ##

答：InnoDB是基于索引来完成行锁

1.  对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；
2.  对于普通SELECT语句，InnoDB不会加任何锁，当然我们也可以显示的加锁；  
    共享锁：select \* from tableName where id = 1 lock in share more  
    排他锁：select \* from tableName where id = 1 for update  
    并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

## 数据库的乐观锁和悲观锁是什么？怎么实现的？ ##

**悲观锁：**假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：**使用数据库中的锁机制【适用场景：写多读少】**

**乐观锁：**假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：**一般会使用版本号机制或CAS算法实现【适用场景：写少读多】**

// 乐观锁Version版本实现
    // 1.查询出商品信息
    select status,version from t_goods where id=#{id}
    // 2.根据商品信息生成订单
    // 3.修改商品status为2
    update t_goods 
    set status=2,version=version+1
    where id=#{id} and version=#{version}

参考文章：  
[https://tech.meituan.com/2014/06/30/mysql-index.html][https_tech.meituan.com_2014_06_30_mysql-index.html]  
[https://www.iteye.com/news/32381][https_www.iteye.com_news_32381]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC82NTA0LzZkMDkwYzdjLTQyODMtM2VlMC04NGM1LTZkNGY5YmFiMzdmZi5wbmc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cDovL2RsMi5pdGV5ZS5jb20vdXBsb2FkL2F0dGFjaG1lbnQvMDEyNC82NTA2LzBiYzAxZGQ0LTM1NGUtMzNmYi04MzIxLTc2ZTBmZGQ4OWNkOS5wbmc?x-oss-process=image/format,png
[b]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9hd3BzLWFzc2V0cy5tZWl0dWFuLm5ldC9taXQteC9ibG9nLWltYWdlcy1idW5kbGUtMjAxNC83YWYyMjc5OC5qcGc?x-oss-process=image/format,png
[img]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOC85LzIxLzE2NWZiNjgyZTc1OWNmMTI?x-oss-process=image/format,png
[https_tech.meituan.com_2014_06_30_mysql-index.html]: https://tech.meituan.com/2014/06/30/mysql-index.html
[https_www.iteye.com_news_32381]: https://www.iteye.com/news/32381