目录
1 索引的概述
1.1 什么是索引
索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。
这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。
1.2 索引的优点
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
- 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
- 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。
1.3 索引的缺点
- 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
- 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
2 常见的索引概念
2.1 聚簇索引
特点：
- 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
- B+树的叶子节点存储的是完整的用户记录。
- 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度非常快
- 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
- 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据
2.2 二级索引（辅助索引，非聚簇索引）
概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2 棵B+树！
问题：为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？
回答：
聚簇索引与非聚簇索引的不同点：
- 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
- 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
- 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。
2.3 联合索引
也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：
- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下，采用c3列进行排序
注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
- 建立联合索引只会建立如下图一样的1棵B+树。
- 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。
3 InnoDB的B+树索引的注意事项
3.1根页面位置晚年不动
B+树的形成过程：
- 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
- 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
- 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者b中，而根节点便升级为存储目录项记录的页。
这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。
3.2内节点中目录项记录的唯一性
B+树索引的内节点中目录项记录的内容是索引列＋页号的搭配，但是这个搭配对于二级索引来说有点不严谨，所以把主键值也添加到二级索引内节点中的目录项记录，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的:
3.3一个页面最少存储2条记录
一条记录无意义
4 MyISAM索引的原理
- 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
- 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。
5 MyISAM与InnoDB对比
MyISAM的索引方式都是非聚簇的，与InnoDB包含一个聚簇索引是不同的：
- 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MylSAM中建立的索引相当于全部都是二级索引。
- InnoDB的数据文件本身就是索引文件，而MylISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- InnoDB的非聚簇索引data域存储相应记录主键的值，而MylSAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
- MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
- InnoDB要求表必须有主键(MyISAM可以没有）。如果没有显式指定，则MysQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MysQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
6 索引的代价
索引是个好东西，但是不能乱建，它在空间和时间上都会有消耗
空间上的代价：
- 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
时间上的代价：
- 每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位， 页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。
一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。
7 索引的创建与设计原则
7.1索引的声明与使用
7.1.1 索引的分类
MysQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。·
- 从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引。·
- 按照物理实现方式，索引可以分为2种:聚簇索引和非聚簇索引。
- 按照作用字段个数进行划分，分成单列索引和联合索引.
1.普通索引
在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。
2.唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。
3.主键索引
主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
Why?这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
4.单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。
5.多列(组合、联合)索引
多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引`idx_id_name_gender`，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。I
不同的存储引擎支持的索引类型也不一样
- InnoDB:支持B-tree、Full-text等索引，不支持Hash索引;
- MylSAM :支持B-tree、Full-text 等索引，不支持Hash索引;
- Memory : 支持B-tree、Hash等索引，不支持Full-text索引;
- NDB:支持Hash 索引，不支持B-tree、Full-text等索引;
- Archive :不支持B-tree、Hash、Full-text等索引;
7.2 创建索引
7.2.1 创建表的时候创建索引
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]- UNIQUE 、FULLTEXT 和SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
- INDEX 与KEY 为同义词，两者的作用相同，用来指定创建索引；
- index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
- col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
- length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
- ASC 或DESC 指定升序或者降序的索引值存储。
7.2.2 在已经存在的表上创建索引
1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]7.2.3 删除索引
1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：
ALTER TABLE table_name DROP INDEX index_name;2. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：
DROP INDEX index_name ON table_name;提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
7.3 索引的设计原则
7.3.1 哪些情况适合创建索引
1. 字段的数值有唯一性的限制
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
2. 频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
3. 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。
4. UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
6. 多表 JOIN 连接操作时，创建索引注意事项
- 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
- 对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
- 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引
9. 区分度高(散列性高)的列适合作为索引
10. 使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引
7.3.2 哪些情况不适合创建索引
1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
- 在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。
3. 有大量重复数据的列上不要建立索引
- 当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引。
4. 避免对经常更新的表创建过多的索引
- 第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成烫担，从而影响效率。
- 第二层含义:避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。
5. 不建议用无序的值作为索引
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引
8 范式
第一范式主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。
第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键，其实可以拓展替换为候选键)。
第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。(即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键C的情况，即存在"A一B→C"的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。
总结：
- 第一范式(1NF)，确保每列保持原子性
- 数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项。
- 第二范式(2NF)，确保每列都和主键完全依赖
- 尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。
- 第三范式（3NF）确保每列都和主键列直接相关，而不是间接相关
范式的优点:数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF)通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。
范式的缺点:范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。
范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询,join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。
9 ER模型
9.1 ER模型的要素
ER 模型中有三个要素，分别是实体、属性和关系。
实体，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。在 ER 模型中，用矩形来表示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。
属性，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用椭圆形来表示。
关系，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用菱形来表示。
注意：实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。
9.2 关系的类型
在 ER 模型的 3 个要素中，关系又可以分为 3 种类型，分别是 一对一、一对多、多对多。
- 一对一：指实体之间的关系是一一对应的，比如个人与身份证信息之间的关系就是一对一的关系。一个人只能有一个身份证信息，一个身份证信息也只属于一个人。
- 一对多：指一边的实体通过关系，可以对应多个另外一边的实体。相反，另外一边的实体通过这个关系，则只能对应唯一的一边的实体。比如说，我们新建一个班级表，而每个班级都有多个学生，每个学生则对应一个班级，班级对学生就是一对多的关系。
- 多对多：指关系两边的实体都可以通过关系对应多个对方的实体。比如在进货模块中，供货商与超市之间的关系就是多对多的关系，一个供货商可以给多个超市供货，一个超市也可以从多个供货商那里采购商品。再比如一个选课表，有许多科目，每个科目有很多学生选，而每个学生又可以选择多个科目，这就是多对多的关系。
10 事务
10.1 基本概念
- 事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。
- 事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。
10.2 事务的ACID特性
原子性（atomicity）：
原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
一致性（consistency）：
一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态
是语义上的而不是语法上的，跟具体的业务有关。
隔离型（isolation）：
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）：
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。
10.3 事务的状态
活动的（active）
事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。
部分提交的（partially committed）
当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘
时，我们就说该事务处在部分提交的状态。
失败的（failed）
当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统
错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失
败的状态。
中止的（aborted）
如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执
行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称
之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事
务处在了中止的状态。
提交的（committed）
当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处
在了提交的状态。
10.4 事务的隔离级别
MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称为一个会话（ Session ）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就看二者如何权衡取舍了。
10.4.1 数据并发问题
1. 脏写（ Dirty Write ）
对于两个事务 Session A、Session B，如果事务Session A 修改了另一个未提交事务Session B 修改过的数据，那就意味着发生了脏写
2. 脏读（ Dirty Read ）
对于两个事务 Session A、Session B，Session A 读取了已经被 Session B 更新但还没有被提交的字段。之后若 Session B 回滚，Session A 读取的内容就是临时且无效的。
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为'张三'，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。
3. 不可重复读（ Non-Repeatable Read ）
对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。 之后Session A 再次读取同一个字段， 值就不同了。那就意味着发生了不可重复读。
我们在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了studentno列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。
4. 幻读（ Phantom ）
对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。 之后, 如果 Session A 再次读取同一个表, 就会多出几行。那就意味着发生了幻读。
Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为'张三'的记录；之后Session B中提交了一个隐式事务，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。我们把新插入的那些记录称之为幻影记录。
11 锁
11.1概述
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时锁机制也为实现MySQL的各个隔离级别提供了保证。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。
11.2 MySQL并发事务访问相同记录
11.2.1 读-读情况
读-读情况，即并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。
11.2.2 写-写情况
在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。
11.2.3 读-写或写-读情况
读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重复读、幻读的问题。
各个数据库厂商对SQL标准的支持都可能不一样。比如MySQL在REPEATABLE READ 隔离级别上就已经解决了幻读问题。
11.2.4 并发问题的解决方案
方案一：读操作利用多版本并发控制（ MVCC ），写操作进行加锁。
普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。
- 在READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象；
- 在REPEATABLE READ 隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。
方案二：读、写操作都采用加锁的方式。
小结对比发现：
- 采用MVCC 方式的话， 读-写操作彼此并不冲突， 性能更高。
- 采用加锁方式的话， 读-写操作彼此需要排队执行，影响性能。
一般情况下我们当然愿意采用MVCC 来解决读-写操作并发执行的问题，但是业务在某些特殊情况
下，要求必须采用加锁的方式执行。下面就讲解下MySQL中不同类别的锁。
11.3 锁的不同角度分类
11.3.1 从数据操作的类型划分：读锁、写锁
- 读锁：也称为共享锁、英文用S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
- 写锁：也称为排他锁、英文用X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。
11.3.2 从数据操作的粒度划分：表级锁、页级锁、行锁
1.表锁
该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎（不管你是MySQL的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。
- 表级别的S锁、X锁
- 意向锁 （intention lock）
- 自增锁（AUTO-INC锁）
2. InnoDB中的行锁
行锁(Row Lock)也称为记录锁，顾名思义，就是锁住某一行（(某条记录row)。需要的注意的是，MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。
- 优点:锁定力度小，发生锁冲突概率低，可以实现的并发度高。
- 缺点:对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。
InnoDB与MylISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。
- 记录锁（Record Locks）
- 记录锁也就是仅仅把一条记录锁上
- 间隙锁（Gap Locks）
- gap锁的提出仅仅是为了防止插入幻影记录而提出的。
- 临键锁（Next-Key Locks）
- 既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提出了一种称之为Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为next-key锁
- 插入意向锁（Insert Intention Locks）
3. 页锁
页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
11.4 从对待锁的态度划分:乐观锁、悲观锁
11.4.1悲观锁（Pessimistic Locking）
悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。
悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中synchronized 和ReentrantLock 等独占锁就是悲观锁思想的实现。
11.4.2 乐观锁（Optimistic Locking）
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量。在Java中java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的。
1. 乐观锁的版本号机制
在表中设计一个版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行UPDATE ... SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
2. 乐观锁的时间戳机制
时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或者时间戳），从而证明当前拿到的数据是否最新。
3. 两种锁的适用场景
从这两种锁的设计思想中，我们总结一下乐观锁和悲观锁的适用场景：
- 乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
- 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。
11.5 死锁
1.产生原因：
- 系统资源不足；
- 进程推进顺序非法。
2.产生死锁的四个必要条件：
- 互斥条件:一个资源每次只能被一个进程使用;
- 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;
- 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺;
- 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系
3.如何处理死锁
- 预防：通过设置某些限制条件，以破坏产生死锁的四个条件中的一个或者几个，来防止发生死锁。
- 避免：系统在分配资源时根据资源的使用情况提前作出预测，从而避免死锁的发生。
- 检测：允许系统在运行的过程中产生死锁，但是，系统中有相应的管理模块可以及时检测出已经产生的死锁，并且精确地确定与死锁有关的进程和资源，然后采取适当措施，清除系统中已经产生的死锁。InnoDB提供了wait-for graph算法来主动进行死锁检测
- 解除：与检测死锁相配套的一种措施，用于将进程从死锁状态下解脱出来。
11.6 锁的内部结构
1. 锁所在的事务信息：
不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的信息。
此锁所在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。
2. 索引信息：
对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。
3. 表锁／行锁信息：
表锁结构和行锁结构在这个位置的内容是不同的：
- 表锁：
- 记载着是对哪个表加的锁，还有其他的一些信息。
- 行锁：
- 记载了三个重要的信息：
- Space ID ：记录所在表空间。
- Page Number ：记录所在页号。
- n_bits ：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits 属性代表使用了多少比特位。
4. type_mode ：
这是一个32位的数，被分成了lock_mode 、lock_type 和rec_lock_type 三个部
5. 其他信息：
为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。
6. 一堆比特位：
如果是行锁结构的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的n_bits 属性表示的。InnoDB数据页中的每条记录在记录头信息中都包含一个heap_no 属性，伪记录Infimum 的heap_no 值为0 ， Supremum 的heap_no 值为1 ，之后每插入一条记录， heap_no 值就增1。锁结构最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个heap_no ，即一个比特位映射到页内的一条记录。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
