标签：事务 查询 索引 查找 MySQL id 主键

数据库三大范式

什么是范式？

范式是数据库设计时遵循的一种规范，不同的规范要求遵循不同的范式。

最常用的三大范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

举例说明3NF

第一范式1NF

属性不可再分，即表中的每个列都不可以再进行拆分。

如下学生信息表(student)：

id、name(姓名)、sex_code(性别代号)、sex_desc(性别描述)、contact(联系方式)

primary key(id)

如果在查询学生表时经常用到学生的电话号，则应该将联系方式(contact)这一列分为电话号(phone)和地址(address)两列，这样才符合第一范式。

修改使表满足1NF后：

判断表是否符合第一范式，列是否可以再分，得看需求，如果将电话号和地址分开才能满足查询等需求时，那之前的表设计就是不满足1NF的，如果电话号和地址拼接作为一个字段也可以满足查询、存储等需求时，那它就满足1NF。

第二范式 2NF

在满足1NF的前提下，表中不存在部分依赖，非主键列要完全依赖于主键。(主要是说在联合主键的情况下，非主键列不能只依赖于主键的一部分)

如下学生成绩表(score)：

stu_id(学生id)、kc_id(课程id)、score(分数)、kc_name(课程名)

primary key(stu_id, kc_id)

表中主键为stu_id和kc_id组成的联合主键。满足1NF；非主键列score完全依赖于主键，stu_id和kc_id两个值才能决定score的值；而kc_name只依赖于kc_id，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。

修改使表满足2NF后：

成绩表(score)   primary key(stu_id)

课程表(kc)   primary key(kc_id)

将原来的成绩表(score)拆分为成绩表(score)和课程表(kc)，而且两个表都符合2NF。

第三范式 3NF：

在满足2NF的前提下，不存在传递依赖。(A -> B, B -> C, A->C)

如下学生信息表(student)：

primary key(id)

表中sex_desc依赖于sex_code，而sex_code依赖于id(主键)，从而推出sex_desc依赖于id(主键)；sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。

修改表使满足3NF后：

学生表(student)   primary key(id)

性别代码表(sexcode)   primary key(sex_code)

将原来的student表进行拆分后，两个表都满足3NF。

什么样的表越容易符合3NF？

非主键列越少的表。(1NF强调列不可再分；2NF和3NF强调非主属性列和主属性列之间的关系)

如代码表(sexcode)，非主键列只有一个sex_desc；

或者将学生表的主键设计为primary key(id,name,sex_code,phone)，这样非主键列只有address，更容易符合3NF。

什么是索引覆盖？什么是索引下推？

一. 什么是索引覆盖？

在执行某个查询语句时，在一颗索引数上就能够获取sql所需要的所有列的数据，无需回表。这就是索引覆盖。

当发起一个索引覆盖的查询时，在explain的extra列会显示Using index

如何实现索引覆盖呢？

常见方法：将被查询的字段建立到联合索引里去。

举个例子，先建立一张表，表结构如下：

create table user(

    id int primary key,

    name varchar(20),

    sex varchar(5),

    index(name)

)engine=innodb;
1234567891011

然后执行sql语句：

select id, name, sex from user where name='zhangsan'
1

显而易见，这个sql是可以命中name索引的，但是这个sql 不符合索引覆盖，原因就是name索引的叶子节点只存储了id和name字段，没有存储sex，sex字段必须回表查询才能获取到，需要拿到id值到主键索引获取sex字段

这时如果把（name）单列索引换成联合索引（name, sex），那就不同了，索引的叶子节点存储了主键id、name、sex那么上面的sql 语句就可以命中索引覆盖无需回表，查询效率更高。

二. 什么是索引下推？

索引条件下推 也被称为 索引下推（Index Condition Pushdown）ICP，MySQL5.6新添加的特性，用于优化数据查询的。

5.6之前通过非主键索引查询时，存储引擎通过索引查询数据，然后将结果返回给MySQL server层，在server层判断是否符合条件，在以后的版本可以使用索引下推，当存在索引列作为判断条件时，Mysql server 将这一部分判断条件传递给存储引擎，然后存储引擎会筛选出符合传递传递条件的索引项，即在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果再返回给Mysql server，有了索引下推的优化，在满足一定条件下，存储 引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

假如有一张表user
表有四个字段 id,name,level,tool

id	name	level	tool
id	name	level	tool
1	大王	1	电话
2	小王	2	手机
3	小李	3	BB机
4	大李	4	马儿

建立联合索引（name,level）
匹配姓名第一个字为“大”，并且level为1的用户，sql语句为:

select * from user where name like "大%" and level = 1；
1

在5.6之前，执行流程是如下图：

5.6及之后，执行流程图如下：

使用索引下推后由两次回表变为一次，提高了查询效率。

为什么 B+ 树比 B 树更适合应用于数据库索引？

1.由一个例子总结索引的特点

加索引是数据库加速查询的一种方式，那么为什么用索引可以加快查询呢？

讲到索引，其实我们经常会听到一个图书馆的例子，图书馆里的书目繁杂，我们如何从若干本书里面找到一本我们想要的书呢？
我们根据图书馆系统检索，可以找到某本书对应的图书编号。在基于书籍按照一定规则排列的前提下，我们可以根据图书编号找到这本书。

例如，假设图书编号根据：
第几个书架 - 书架上第几个格子 - 从左到右数第几个位置这样的规则编排，我们就可以轻松的获取到我们想要的书籍。

你也许发现了，这个例子中，藏着两个信息：

1. 按照一定的规则排列
2. 有序

按照一定的规则，建立一定的映射关系，这让你联想到了什么？
没错，就是哈希表。

2.基于哈希表实现的哈希索引

在 Mysql 的 InnoDB 引擎中，自适应哈希索引就是用哈希表实现的。
哈希索引是数据库自身创建并使用的，DBA 本身不能对其进行干预，但是可以通过参数来禁止或者启用此特性。
显然用哈希表实现索引的好处是非常明显的，查找单个指定数据只需要 O(1)O(1) 的时间复杂度。

例如下面的 sql 语句：

select id from tablename where id == 1;
1

但是对于这种查找指定范围的 sql 语句，哈希索引就无能为力了。

select id from tablename where id BETWEEN 20 AND 23;
1

说明：因为哈希表本身是无序的，所以不利于范围查询

再次思考
到这里我们遇到了一个问题，就是哈希表虽然从查找效率上满足了我们查找单个数据的要求，但是显然，当遇到范围查询时，由于哈希表本身的无序性，不利于指定范围查找。

也就是说，我们的需求增加了，我们希望数据的组织方式，既要有一定规则，又要有序。

在引出这种数据结构之前，我们首先来看一种查找方式：二分查找。

3.高效的查找方式：二分查找

二分查找的核心思想是给定一个 有序 的数组，在查找过程中采用跳跃式的方式查找，即先以有序数列的中点位置为比较对象，如果要查找的元素小于中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过每次比较，将查找区间减少一半，直到找到所需元素。

比如要从以下序列中查找到数字 4

[1,3,4,5,6,7,8]

需要经过下面的查找步骤：

• 取中心位置对应元素，显然 5 大于 4，在左边区间 [1,3,4] 进行查找
• 继续取中心位置对应元素 3，显然 3 大于 4,在右边区间 [4] 进行查找
• 4 等于 4，所以我们查找成功。

可以看到二分查找的效率是 O(log n)。
由于有序数组自身的有序性，所以范围查询依然可以通过二分查找的方式查找区间的边界来实现。
这样看来，如果单从查询效率上来说，有序的数组是一种很好的选择。

但是显然有序数组对于插入和删除并不友好，假设我们要插入元素或者删除元素，都需要把部分元素全部向后或者向前移动，最糟糕的时间复杂度是 O(n)O(n)。

有没有这样一种数据结构，既有一定顺序，又方便插入和删除呢？事实上，基于二分查找的思想，诞生了这样一种数据结构：二分查找树。

4.基于二分查找思想的二叉查找树

二叉查找树（Binary Search Tree）即BST树是这样的一种数据结构,如下图：

在二叉搜索树中：
1). 若任意结点的左子树不空，则左子树上所有结点的值均不大于它的根结点的值。
2). 若任意结点的右子树不空，则右子树上所有结点的值均不小于它的根结点的值。
3). 任意结点的左、右子树也分别为二叉搜索树。

这样的结构非常适合用二分查找的思维查找元素。

比如我们需要查找键值为8的记录：

1. 先从根找起，找到 6； 显然 8>6，
2. 所以接着找到 6 的右子树，找到 7；
3. 显然 8>7, 所以找 7 的右子树，找到了8，查找结束。

这样一棵子树高度差不大于 1 的二叉查找树的查找效率接近与 O(log n)O(logn);

但是当二叉树的构造变成这样时,

此时我们再查找 8 时,查找效率就沦为接近顺序遍历查找的效率。

5.升级版的BST树：AVL 树

我们对二叉查找树做个限制，限制必须满足任何节点的两个子树的最大差为 1，也是AVL 树的定义，这样我们的查找效率就有了一定的保障。AVL 树 是一种自平衡二叉查找树(self-balancing binary search tree)。

当然，维护AVL 树也是需要一定开销的，即当树插入/更新/删除新的数据时假设破坏了树的平衡性，那么需要通过左旋和右旋来维护树的平衡。当数据量很多时，同样也会出现二叉树过高的情况。我们知道AVL 树的查找效率为 O(log n)，也就是说，当树过高时，查找效率会下降。

另外由于我们的索引文件并不小，所以是存储在磁盘上的。文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。
将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

6.更加符合磁盘特征的B树

前面我们看到，虽然AVL树既有链表的快速插入与删除操作的特点，又有数组快速查找的优势，但是这并不是最符合磁盘读写特征的数据结构。

也就是说，我们要找到这样一种数据结构，能够有效的控制树高，那么我们把二叉树变成m叉树，也就是下图的这种数据结构:B 树。

B树是一种这样的数据结构：

• 根结点至少有两个子结点;
• 每个中间节点都包含 k-1 个元素和k个子结点，其中 m/2 <= k <= m;
• 每一个叶子结点都包含 k-1个元素，其中 m/2 <= k <= m;
• 所有的叶子结点都位于同一层;
• 每个结点中关键字从小到大排列，并且当该结点的孩子是非叶子结点时，该 k-1 个元素正好是 k 个子结点包含的元素的值域的分划。

可以看到，B树在保留二叉树预划分范围从而提升查询效率的思想的前提下，做了以下优化：
二叉树变成 m 叉树，这个 m 的大小可以根据单个页的大小做对应调整，从而使得一个页可以存储更多的数据，从磁盘中读取一个页可以读到的数据就更多，随机 IO 次数变少，大大提升效率。

但是我们看到，我们只能通过中序遍历查询全表，当进行范围查询时，可能会需要中序回溯。

7.不断优化的B树：B+ 树

基于以上的缺陷，又诞生了一种新的优化B树的树: B+ 树

B+树在B树的基础上加了以下优化：
1.叶子结点增加了指针进行连接，即叶子结点间形成了链表；
2.非叶子结点只存关键字 key，不再存储数据，只在叶子结点存储数据；

说明：叶子之间用双向链表连接比单向链表连接多出的好处是通过链表中任一结点都可以通过往前或者往后遍历找到链表中指定的其他结点。

这样做的好处是：

1. 范围查询时可以通过访问叶子节点的链表进行有序遍历，而不再需要中序回溯访问结点。
2. 非叶子结点只存储关键字key，一方面这种结构相当于划分出了更多的范围，加快了查询速度，另一方面相当于单个索引值大小变小，同一个页可以存储更多的关键字，读取单个页就可以得到更多的关键字，可检索的范围变大了，相对 IO 读写次数就降低了。

8.一些总结

B+ 树和 B 树的区别？

1. B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1),最坏为 O(log n)。B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。
2. B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

1. B+ 树更加适应磁盘的特性，相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
2. B+ 树的查询效率相比B树更加稳定，由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)。
3. B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

数据库事务与锁详解

1. 什么是事务（Transaction）？

是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的一个逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

举个例子加深一下理解：

同一个银行转账，A转1000块钱给B，这里存在两个操作，一个是A账户扣款1000元，两一个操作是B账户增加1000元，两者就构成了转账这个事务。

两个操作都成功，A账户扣款1000元，B账户增加1000元，事务成功
两个操作都失败，A账户和B账户金额都没变，事务失败

最后思考一下，怎么样会出现A账户扣款1000元，B账户金额不变？如果你是把两个操作放在一个事务里面，并且是数据库提供的内在事务支持，那就不会有问题，但是开发人员把两个操作放在两个事务里面，而第二个事务失败就会出现中间状态。现实中自己实现的分布式事务处理不当也会出现中间状态，这并不是事务的错，事务本身就是规定不会出现中间状态，是事务实现者做出来的方案有问题。

2. 事务的4个特性

• 原子性(Atomic)：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
• 一致性(Consistency)：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。
• 隔离性(Isolation)：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，到底是另一个事务执行之前的状态还是中间某个状态，相互之间存在什么影响，是可以通过隔离级别的设置来控制的。
• 持久性(Durability)：事务结束后，事务处理的结果必须能够得到固化，即写入数据库文件中即使机器宕机数据也不会丢失，它对于系统的影响是永久性的。

3. 事务并发控制

我们从另外一个方向来说说，如果不对事务进行并发控制，我们看看数据库并发操作是会有那些异常情形，有些使我们可以接受的，有些是不能接受的，注意这里的异常就是特定语境下的，并不一定就是错误什么的。假设有一个order表，有个字段叫count，作为计数用，当前值为100

1. 第一类丢失更新（Update Lost）：此种更新丢失是因为回滚的原因，所以也叫回滚丢失。此时两个事务同时更新count，两个事务都读取到100，事务一更新成功并提交，count=100+1=101，事务二出于某种原因更新失败了，然后回滚，事务二就把count还原为它一开始读到的100，此时事务一的更新就这样丢失了。
2. 脏读（Dirty Read）：此种异常时因为一个事务读取了另一个事务修改了但是未提交的数据。举个例子，事务一更新了count=101，但是没有提交，事务二此时读取count，值为101而不是100，然后事务一出于某种原因回滚了，然后第二个事务读取的这个值就是噩梦的开始。
3. 不可重复读（Not Repeatable Read）：此种异常是一个事务对同一行数据执行了两次或更多次查询，但是却得到了不同的结果，也就是在一个事务里面你不能重复（即多次）读取一行数据，如果你这么做了，不能保证每次读取的结果是一样的，有可能一样有可能不一样。造成这个结果是在两次查询之间有别的事务对该行数据做了更新操作。举个例子，事务一先查询了count，值为100，此时事务二更新了count=101，事务一再次读取count,值就会变成101，两次读取结果不一样。
4. 第二类丢失更新（Second Update Lost）：此种更新丢失是因为更新被其他事务给覆盖了，也可以叫覆盖丢失。举个例子，两个事务同时更新count，都读取100这个初始值，事务一先更新成功并提交，count=100+1=101，事务二后更新成功并提交，count=100+1=101,由于事务二count还是从100开始增加，事务一的更新就这样丢失了。
5. 幻读（Phantom Read）：幻读和不可重复读有点像，只是针对的不是数据的值而是数据的数量。此种异常是一个事务在两次查询的过程中数据的数量不同，让人以为发生幻觉，幻读大概就是这么得来的吧。举个例子，事务一查询order表有多少条记录，事务二新增了一条记录，然后事务一查了一下order表有多少记录，发现和第一次不一样，这就是幻读。

4. 数据库事务隔离级别

看到上面提到的几种问题，你可能会想，我擦，这么多坑怎么办啊。其实上面几种情况并不是一定都要避免的，具体看你的业务要求，包括你数据库的负载都会影响你的决定。不知道大家发现没有，上面各种异常情况都是多个事务之间相互影响造成的，这说明两个事务之间需要某种方式将他们从某种程度上分开，降低直至避免相互影响。这时候数据库事务隔离级别就粉墨登场了，而数据库的隔离级别实现一般是通过数据库锁实现的。

• 读未提交（Read Uncommitted）：该隔离级别指即使一个事务的更新语句没有提交,但是别的事务可以读到这个改变，几种异常情况都可能出现。极易出错，没有安全性可言，基本不会使用。
• 读已提交（Read Committed）：该隔离级别指一个事务只能看到其他事务的已经提交的更新，看不到未提交的更新，消除了脏读和第一类丢失更新，这是大多数数据库的默认隔离级别，如Oracle,Sqlserver。
• 可重复读（Repeatable Read）：该隔离级别指一个事务中进行两次或多次同样的对于数据内容的查询，得到的结果是一样的，但不保证对于数据条数的查询是一样的，只要存在读改行数据就禁止写，消除了不可重复读和第二类更新丢失，这是Mysql数据库的默认隔离级别。
• 串行化（Serializable）：意思是说这个事务执行的时候不允许别的事务并发执行，完全串行化的读，只要存在读就禁止写,但可以同时读，消除了幻读。这是事务隔离的最高级别，虽然最安全最省心，但是效率太低，一般不会用。

下面是各种隔离级别对各异常的控制能力：

级别\异常	第一类更新丢失	脏读	不可重复读	第二类丢失更新	幻读
读未提交	Y	Y	Y	Y	Y
读已提交	N	N	Y	Y	Y
可重复读	N	N	N	N	Y
串行化	N	N	N	N	N

5. 数据库锁分类

一般可以分为两类，一个是悲观锁，一个是乐观锁，悲观锁一般就是我们通常说的数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制，比如hibernate实现的乐观锁甚至编程语言也有乐观锁的思想的应用。

5.1 悲观锁

悲观锁：顾名思义，就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁。

悲观锁按照使用性质划分：

• 共享锁（Share locks简记为S锁）：也称读锁，事务A对对象T加s锁，其他事务也只能对T加S，多个事务可以同时读，但不能有写操作，直到A释放S锁。
• 排它锁（Exclusivelocks简记为X锁）：也称写锁，事务A对对象T加X锁以后，其他事务不能对T加任何锁，只有事务A可以读写对象T直到A释放X锁。
• 更新锁（简记为U锁）：用来预定要对此对象施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的对象将要被更新时，则升级为X锁，主要是用来防止死锁的。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个对象申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请更新锁，在数据修改的时候再升级为排它锁，就可以避免死锁。

悲观锁按照作用范围划分：

• 行锁：锁的作用范围是行级别，数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。举个例子，一个用户表user，有主键id和用户生日birthday当你使用update … where id=?这样的语句数据库明确知道会影响哪一行，它就会使用行锁，当你使用update … where birthday=?这样的的语句的时候因为事先不知道会影响哪些行就可能会使用表锁。
• 表锁：锁的作用范围是整张表。

5.2 乐观锁

顾名思义，就是很乐观，每次自己操作数据的时候认为没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现。既然都有数据库提供的悲观锁可以方便使用为什么要使用乐观锁呢？对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，我们只要想办法解决极少量的更新操作的同步问题。换句话说，如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了额外的风险。

乐观锁实现方式：

• 版本号（记为version）：就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version="old version"这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。
• 时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
• 待更新字段：和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。
• 所有字段：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。

乐观锁几种方式的区别：

新系统设计可以使用version方式和timestamp方式，需要增加字段，应用范围是整条数据，不论那个字段修改都会更新version,也就是说两个事务更新同一条记录的两个不相关字段也是互斥的，不能同步进行。旧系统不能修改数据库表结构的时候使用数据字段作为版本控制信息，不需要新增字段，待更新字段方式只要其他事务修改的字段和当前事务修改的字段没有重叠就可以同步进行，并发性更高。

MySQL优化—Explain

一. 查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。它可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

show status like 'Com_______';
1

show status like 'Innodb_rows_%';
1

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read select	查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections 试图连接	MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

二. 定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

• 慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过long_query_time 秒的 SQL 语句的日志文件。
• show processlist :慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL的执行情况，同时对一些锁表操作进行优化。
  

1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4）db列，显示这个进程目前连接的是哪个数据库
5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接 （connect）等
\6) time列，显示这个状态持续的时间，单位是秒
\7) state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态
才可以完成
8）info列，显示这个sql语句，是判断问题语句的一个重要依据

三. explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划 ：

explain select * from tb_item where id = 1;
1

explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';
1

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref-------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------>all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

3.1 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种：

a) id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and
u.id = ur.user_id ;
12

b) id 不同id值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id
= (SELECT id FROM t_user WHERE username = 'stu1'))
12

c) id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` =
'2') a WHERE r.id = a.role_id ;
12

3.2 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

3.3 table

展示这一行的数据是关于哪一张表的

3.4 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
null	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将"主键" 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

结果值从最好到最坏依次是null> system > const > eq_ref > ref > range > index > all

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

3.5 key

key	含义
possible_keys	显示可能应用在这张表的索引， 一个或多个
key	实际使用的索引， 如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前 提下，长度越短越好

3.6 rows

扫描行的数量。

3.7 extra

其他的额外的执行计划信息，在该列展示 。

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为“文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和group by； 效率低
using index	表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

四. show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

set profiling=1; //开启profiling 开关；
1

通过profile，我们能够更清楚地了解SQL执行的过程。
首先，我们可以执行一系列的操作，如下图所示：

show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
12345

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

show profile for query query 6
1

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：

show profile cpu for query query 6
1

字段	含义
Status	sql语句执行的状态
Duration	执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu

标签：事务,查询,索引,查找,MySQL,id,主键
From： https://www.cnblogs.com/liurui12138/p/17416424.html