数据库学习（进阶）

分为入门（基础、操作）、进阶（底层实现原理学习、优化）进行总结。

---

Mysql结构:

Mysql体系结构:

连接层、服务层、存储引擎层、系统文件层。

连接层：

主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是MySQL所特有的技术。

服务层（核心层）：

根据体系结构图，可以可以看到服务器主要包含：

连接池：由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能

管理工具和服务：系统管理和控制工具，例如备份恢复、Mysql复制、集群等

SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。

解析器: SQL命令传递到解析器的时候会被解析器验证和解析。作用：将SQL语句分解成数据结构

优化器：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。

缓存器： 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层。

SQL语句在服务层内部执行顺序：查询缓存（解析查询之前，服务器会检查查询缓存，如果能找到对应的查询，直接返回缓存中的结果集）——>解析器解析（ MySQL会解析查询，并创建了一个内部数据结构）——>查询优化器（多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划）——>查询引擎（查询执行引擎根据执行计划给出的指令调用存储引擎的接口得出结果。）。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构：

存储引擎层：

负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异（这也就说明，在第二层服务层时，暂时不需要考虑引擎的差异）

系统文件层：

该层主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。

Mysql内存结构:（本章节不深入，主要在InnoDB底层那一章中，了解InnoDB的内存结构）

全局内存：缓冲池里面有数据缓存、索引缓存、锁信息、插入缓存等等。此外还有重做日志缓存、额外的内存池。
线程内存：Master Thread、IO Thread、Purage Thread、Page Cleaner Thread。

存储引擎（概述）:

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式，每个表都有设置自己的存储引擎;

存储引擎是属于表的一种类型。

//存储引擎设置
CREATE TABLE 表名
(字段1 字段1类型[COMMENT 字段1注释],字段2 字段2类型[COMMENT 字段2注释],字段2 字段2类型[COMMENT 字段2注释]           ) ENGINE =引擎类型 [COMMENT 表注释]
//默认引擎是INNODB

 SHOW ENGINES;//数据库查看

存储引擎特点：

存储结构特点：（详细见本文：InnoDB引擎底层（重点）这一章）

tablespace---->segment---->extent---->page---->row;

表空间---->段---->区---->页---->行；

InnoDB存储引擎：(为并发条件下必选)

INNODB是兼顾高可靠性、高性能的通用存储引擎，为Mysql默认存储引擎

InnoDB存储引擎特点：

1.DML操作遵循事务的ACID属性：原子性、一致性、隔离性、持久性。

2.行级锁，提高并发访问

3.支持外键约束，保证数据完整性。

MyISAM与innodb的区别：

（具体的对比，在此次进阶篇学习完成后，作者会深入总结回归，另外进行学习笔记）

索引

索引（index）是帮助MYSQL高效获取数据的数据结构（有序）

优点：

提高数据检索效率，降低数据库成本；
通过索引列对数据进行排序和分组，可以降低数据排序成本，降低CPU消耗。>通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

索引列需要占用额外的物理空间；
并且创建索引和维护索引需要花费一定的时间
索引大大提高了查询效率，但更新表时，索引需要重建，这降低更新表的速度。
没有索引时，Mysql使用全表查询，一般当索引列覆盖的数据大于全表数据的30%时，会触发mysql使用全表检索；

索引结构：

索引是在数据库结构的第三层：存储引擎层实现的

主要的索引结构主要有以下几种：（平时默认就是B+tree索引）

索引结构	描述
B+Tree索引	最常见的索引类型
Hash索引	底层数据结构通过哈希表来实现，只有精确匹配索引查询才有效，不支持范围查询
R-tree索引（空间索引）	空间索引是MySAM引擎的一个特殊索引类型，适用于地理空间数据类型
Full-text（全文索引）	一种通过建立倒排索引，快速匹配文档的方式

索引引擎支持的索引结构：

索引结构	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引（空间索引）	不支持	支持	不支持
Full-text（全文索引）	5.6版本之后支持	支持	不支持

B+tree索引机构

//来说说树：（一些类型的树，自己后面再整理一下，放这里，先埋个坑）

数据库使用B-tree与B+tree的原因：

相对与一般二叉搜索树，B树层级更少，搜索效率更高

二叉搜索树（BST）：缺点，在插入数据的过程中，树的节点不能保证平衡性，在一些极端情况下，比如连续插入有序的序列，容易产生近似链表的结构，搜索复杂度近似为线性（o(N)）

红黑树：大数据量情况下，层次会很深，检索速度下降。

了解一下B-tree和B+tree;（详细可以看看这篇博客：http://t.csdn.cn/3UZcZ）

B-tree（多路平衡查找树）:

类似于普通的二查搜索树

特点：

1.,所有键值分布在整个树中
2,任何关键字出现且只出现在一个节点中
3,搜索有可能在非叶子节点结束
4,在关键字全集内做一次查找，性能逼近二分查找算法（logn）

B+tree:

B+树是B树的变体，也是一种多路平衡查找树

与B-tree的区别在于：1.所有的数据都会出现在叶子节点；2.叶子节点形成一个单向链表。

而在mysql中，在原有的B+tree的基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+tree，提高区间访问性能。

B+tree相对于B-tree的优点：
1.B+tree数据存储在叶子节点上，使得内存页可以存储更多的key，相同数据下，B++tree的深度会比B-tree小，这使得B+tree的搜索效率上升。
2.B+tree叶子节点是相连的（通过增加一个指针），可以方便数据在内存连续存储，便于底层的区间查找，提高缓存命中性。

哈希索引（Memory支持）：

采用一定的哈希算法，将键值换算成新的hash值，映射到对应的槽位上，然后存在hash表中；

当发生哈希冲突时，可以通过链表来解决。

哈希索引特点：

优点：效率高于B+tree索引

缺点：hash索引不支持范围查询，只能通过等值（=）来查询；其存储是无序的，无法利用索引来完成排序操作。

索引分类：

通过约束关键字控制

索引分类 含义 特点 关键字（约束）
主键索引 针对表中主键创建的索引 唯一，默认自动创建 PRIMARPY
唯一索引 避免一个表中的某一数据列有重复值 可以多个 UNIQUE
常规索引 可以快速定位 可以多个
全文索引 全文索引查找文本中的关键词 可以多个 FULLTEXT
数据库主键与索引区别和联系：

主键一定是唯一索引，在创建主键时，数据库管理系统会自动给主键生成一个唯一索引，所以主键索引就是一个特殊的唯一索引。

唯一索引是标识索引的值唯一，但在表中可以有多个唯一索引，主键却只能有一个。

主键列不能为空，但唯一索引可以为空

个人理解：索引就像是书的目录，而主键就相当于一本书的页码。

innoDB中：

分类 含义 特点
聚集索引(clustered index) 数据与索引一起存储，索引结构的叶子节点保存了行数据 必须有，并且只能有一个
二级索引(secondary index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个
聚集（聚簇）索引选取规则：
如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用唯一（UNIQUE）索引作为聚集索引；

如果表没有主键、以及唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。

索引语法
创建索引：

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tablename(index_col_name);
//UNIQUE:唯一索引
//联合索引创建：CREATE INDEX index_name ON tablename(index_col_name1,index_col_name2,……);

//主键索引创建：
ALTER TABLE table_name ADD PRIMARY KEY ( column );
//在建表时，建立主键索引，例子
CREATE TABLE testIndex(
i_testID INT NOT NULL AUTO_INCREMENT,
vc_Name VARCHAR(16) NOT NULL,
PRIMARY KEY(i_testID)
);
1
2
3
4
5
6
7
8
9
10
11
12
查看索引：

SHOW INDEX FROM tablename;
1
删除索引

DROP INDEX index_name ON tablename;
1
索引使用原则与失效问题（重点）：
1.最左前缀法则：

如果索引了多列（联合索引），就是指查询从索引的最左列开始，并且不跳过索引中的列，如果不从最左边列开始查询，则就不会使用索引查询，索引失效，如果从最左列开始查询，但跳过其中一列，那么索引将部分失效（后面字段索引失效）。

例如：p1、p2、p3设置了联合索引，p1为最左
select * from table1 where p1=‘xx’ and p2=‘yy’;//按索引查询
select * from table1 where p2=‘yy’ and p3=‘zz’;/不满足最左前缀法则，不按索引查询
select * from table1 where p1=‘xx’ and p3=‘zz’;//p1按照索引查询，p3不按照索引查询
1
2
3
4
2.范围查询：

联合索引中，出现范围查询（<,>）这会使得右侧的列索引失效。（这篇可以帮助理解原因：http://t.csdn.cn/ydo6N ：总结就是，范围查询无法保证，范围获取的数据在后面条件中是有序的，导致索引失效）

3.索引列运算

索引列运算操作，将会导致索引失效，(+，-，*，/，!)。

4.字符串不加引号：会导致索引失效（原因是类型不一致导致索引失效）

5.模糊查询：（模糊查询链接：http://t.csdn.cn/QkCu8）

头部模糊匹配会导致索引失效。

6.使用or导致索引失效：or两侧都有索引才能生效，否则都不生效。

7.函数导致索引失效

8.数据分布导致索引失效：

9 !=也会导致索引失效

10.当MYSQL判断使用索引比全表更慢，则不使用索引。

比如在选择一个字段数据为null，在一般情况（绝多数情况下）下mysql会判断全表会更快，索引失效；如果判断全表慢，索引也不会失效。（因此尽量避免mysql where语句中出现null的判断）

比如当数据重复率过大的时候。

在适合索引查询时，尽量避免索引失效。

下列几种情况，是不适合创建和使用索引的：

频繁更新的字段不适合建立索引；
where条件中用不到的字段不适合建立索引；
数据比较少的表不需要建索引；
数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；
参与列计算的列不适合建索引
SQL 提示
SQL 提示是优化数据库的一个重要手段，简单来说，就是在SQL 语句中加入一些 人为的提示来达到优化操作的目的。

USE INDEX：在查询语句中表名的后面，添加USE INDEX 来提供希望MySQL 去参考的索引列表

IGNORE INDEX：让MySQL 忽略一个或者多个索引。

FORCE INDEX：为强制MySQL 使用一个特定的索引。

select * from table1 use index(idx_p1) where p1=‘xx’;
select * from table1 ignore use index(idx_p1) where p1=‘xx’;
select * from table1 force use index(idx_p1) where p1=‘xx’;
1
2
3
覆盖索引与回表查询：
如果索引包含所有满足查询需要的数据的索引就称为覆盖索引(Covering Index)，也就是不需要回表查询操作。

比如，一个表table2里面有a,b,c,d字段，a为主键，b设置单列索引，c,d设置联合索引

select * from table2 where b.val=1;//此时需要select全部字段，而b二级索引不能覆盖全部字段，需要回表查询，大大降低了效率
select a,c,d from table2 where c.val=1;//此时虽然c，d联合索引是二级索引，但由于所选字段a（就是索引叶子节点存储的值），b，c都包括在该索引中，就不需要回表查询了。
1
2
回表查询：

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

因此在查询时，尽量减少回表查询的情况。

前缀索引：
当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量磁盘IO，影响查询的效率。此时可以将字符串的一部分前缀作为索引，节约索引空间，从而提高索引效率。

create index idx_xxx on tablename(字段名(n));//将前n个字符作为索引,
1
前缀长度的选择：

通过索引的选择性：不重复的索引值与数据总数的比值，选择性越大查询效率越高

比如唯一索引，其没有重复值，选择性为1，查询效率最大。

前缀索引的查询流程：

select * from tablename where 字段名=‘snsjsdas’(全部);//语法上与正常的查询语句一样
1
流程：提取该字段（已经设置了前缀索引）的前缀部分，在第二索引/辅助索引中寻找到对应的值，判断是否需要回表，需要就到聚集索引中查找。

单列索引与联合索引
单列索引：即一个索引只包含单个列

联合索引：一个索引包含多个列

补充：尽量使用联合索引来保证覆盖索引，可以减少回表查询，提高查询效率

重复索引与冗余索引：
重复索引：在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复索引。

冗余索引：两个索引所覆盖的列有重叠

当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引

索引设计原则：
（1）对查询频繁的字段，建议创建索引。

（2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。

（3）避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。

（4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。

（5）不要在重复率高的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。

（6）对于批量数据获取，尽量使用联合索引，保证覆盖索引，减少回表查询次数，提高效率

（6）当唯一性是某字段本身的特征时，尽量建立唯一索引能提高查询速度。

（7）对频繁进行查询（where）、分组（ group by） 或排序（ order by）操作的列上建立索引。

SQL优化
SQL性能分析
SQL性能分析可以获取数据库查询、插入、删除、更新次数，为性能优化提供参考。

SQL执行频率：

SHOW GLOBAL STATUS LIKE ‘Com_______’;//查询数据库执行频率
1

慢查询日志：

记录了所有执行时间超过指定参数（默认是10s）的所有sql语句的日志；

慢查询日志默认关闭，需要手动开启，开启后注意要重启数据库

show profile：(了解)

show profiles;//查看一系列语句资源消耗信息
show profile from query 语句编号;//查询具体语句消耗信息
1
2
show profile 命令用于跟踪执行过的sql语句的资源消耗信息，可以帮助查看sql语句的执行情况，可以在做性能分析或者问题诊断的时候作为参考。

explain命令
通过explain 命令我们可以学习到该条 SQL 是如何执行的

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件
//直接在SELECT之前+EXPLAIN
1
2

获取字段：

id:

值越大，越先执行，同等值，就从上往下执行；

select_type:

表示SELECT的类型（SIMPLE：简单表，不使用表连接、子查询），PRIMARY（主查询），UNION、SUBQUERY（SELECT以及WHERE包含的子查询）

type:表示连接类型（ALL表示全表查询）

possible_key:显示可能用到的索引

Key:实际所用索引（NULL就是没有加索引）

Key_len:索引字节数

rows:执行查询所需行数，innodb中认为是一个估计值

filtered:返回结果行数占所需读取行数的比列，该值越大越好

Extra:额外信息

插入数据sql优化：
每次使用插入语句时，都要与数据库建立连接，对于大量数据进行单个插入，效率太低；

优化方法：

insert优化：
1.1批量插入

可以进行插入，但插入语句要在1000行以下，对于上万以上的插入，采用分段批量插入。

1.2手动提交事务

1.3主键顺序插入

使用load指令进行大批量数据插入：
主键优化：
在innoDB中，表数据都是根据主键顺序组织存放的，这种存储方式的表就是索引组织表（IOT）

在主键乱序插入的情况下，就会出现页分裂的现象。

页分裂：

当页中删除记录达到MERGE——THRESHOLD（默认为页的50%），innoDB回寻找该页最靠近的前后的页，能否进行页合并操作，以此来优化空间。

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是被记录标记为删除，使得它的空间可以被其他记录申明使用。

主键设计原则：
1.在满足业务需求下，尽量降低主键长度。

2.插入数据时，尽量选择顺序插入，选择使用AUTO——INCREMENT自增主键

3.尽量不要用无序的作为主键，比如身份证号（太长且无序），UUID（无序），使用AUTO_INCREAMENT，可以保证自动顺序添加

4.业务操作时，避免对主键的修改。

ordered by 优化：
在使用排序条件时，尽量使用索引，（不是通过索引直接返回排序结果的排序叫做FileSort排序）

group by 优化：
采用索引，多字段分组并满足最前缀法则。

limit 优化：
当遇到大批量数据分页操作时，操作时间长（比如对于一个200万的数据，在查询时只需要第100w之后的10个数据，但直接用limit进行查询，会浪费大量时间），为了保证效率可以采用：

通过创建覆盖索引（减少回表查询），然后加上子查询的方式进行优化（就是对覆盖索引查询后的结果进行分页查询）。

count优化：
count()为一个聚合函数，计数时，如果对象不为NULL，则计数+1；

count的基本作用是有两个：

统计某个列的数据的数量；
统计结果集的行数。
select count() from table_cols;//count 计数
//count()、count(字段)…………
1
2
MyISAM 引擎回把一个表总行记录在磁盘里，使用count(*)时，会直接返回，效率高；

InnoDB 引擎，只能读取，然后累积计数；

优化方法：
1.count(主键)：InnoDB引擎遍历整个表，把每个主键id取出，并返回服务层，服务层直接进行累加（不需要进行null的判断）；

2.count(字段)：

对于有not null约束的：InnoDB引擎遍历整个表，把每行字段取出，并返回服务层，服务层直接进行累加（不需要进行null的判断）；

对于没有not null 约束的：InnoDB引擎遍历整个表，把每行字段取出，并返回服务层，然后服务层存在一个判断null的过程（不为null，计数加1）；

3.count(*)

InnoDB引擎做了优化，不需要取值,服务器直接进行累加。

4.count(1)：表示的是直接查询符合条件的数据库表的行数（会包含值为NULL的行数），count(*)是标准语法，MYSQL优化更多。

InnoDB会遍历整张表，但不取值，服务层对于返回的每一行，就放一个数字‘1’，直接进行累加

效率方面：count(*)>count(1)>count(主键)>count(字段);

updata优化：
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，如果索引失效或者没有使用索引进行updata；那么行锁就会变为表锁，从而影响并发性能。

在执行update时，需要根据索引来进行数据更新，保证并发性能需求。

视图/存储过程/触发器（暂时不做重点）
视图
视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

http://t.csdn.cn/kjStA（详细学习链接）

视图是存储在数据库中的查询的SQL语句；

它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。

存储过程：
大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有的操作都那么简单。经常会有一个完整的操作需要多条语句才能完成。

存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合，可将其视为批处理文件。虽然他们的作用不仅限于批处理。

存储过程思想上来看，就是数据库SQL语言层面代码的封装和重用。

特点：

封装，复用；可以接受、返回数据；减少网络交互，提升效率。

详细见（http://t.csdn.cn/bsXGl）

触发器：
触发器是与表有关的数据库对象，是一种特殊类型的存储过程，主要通过事件触发来执行，而不是主动调用来执行；

触发器使用往往是为某张表绑定一段代码，当表中相关内容发生变化（增、删、改）的时候，系统会自动触发这段代码并执行。

注意：目前mysql只支持行级触发器，不支持语句触发器。

触发器作用：

触发器语法:

创建：
delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
–目前mysql只支持行级触发器，不支持语句触发器
begin
– 触发器内容主体，每行用分号结尾
end
–自定义的结束符合

delimiter ;//恢复为默认的结束符号

–查看：
SHOW TRIGGERS;
删除：
DROP TRIGGER[数据库名字]触发器名字;//没有指定数据库名字，就会默认选当前所在数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
触发时间

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态
after：表中数据发生改变后的状态
触发事件：

触发器是针对数据发送改变才会被触发，对应的操作只有这三种

INSERT
DELETE
UPDATE
每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器：before/after insert、before/after delete、before/after update

触发器应用：详细见（http://t.csdn.cn/EYmVD）

–创建表
create table sp_table(
id int comment ‘商品编号’,
name varchar(10) comment ‘商品名字’,
val FLOAT comment ‘商品价格’,
) comment ‘商品单’;
—创建进货表
create table jinhuo_biao
(
id int comment ‘商标编号’,
nums int comment ‘数量’
) comment ‘进货表’;

–创建触发器
delimiter ##
create trigger insert_trigger after insert on sp_table for each row
begin
update jinhuo_biao set nums=nums+1 where id=new.id;
end##
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> show TRIGGERS;
±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±-------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±-------------------+
| insert_trigger | INSERT | sp_table | begin
update jinhuo_biao set nums=nums+1 where id=sp_table.id;
end | AFTER | 2022-03-25 15:22:35.03 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
±---------------±-------±---------±---------------------------------------------------------------------±-------±-----------------------±------------------------------------------------------------------------------------------------------------------------------------------±---------------±---------------------±---------------------±-------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
–示例：
INSERT INTO jinhuo_biao (id,nums) VALUES(1,0);
INSERT INTO jinhuo_biao (id,nums) VALUES(2,0);
INSERT INTO jinhuo_biao (id,nums) VALUES(3,0);
INSERT INTO sp_table (id,name,val) VALUES(1,‘coke’,7.8);
INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5);
INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5);
INSERT INTO sp_table (id,name,val) VALUES(2,‘apple’,5);
INSERT INTO sp_table (id,name,val) VALUES(3,‘milk’,3.3);

1
2
3
4
5
6
7
8
9
10
11
结果发现，触发器生效，插入使得jinhuo_biao的nums获得更新。

对于数据在操作前后都有一个状态（old/new）,操作前状态为old,操作后为new;

old与new不是所有触发器都有

触发器类型 new old
INSERT 没有 有
UPDATE 没有 有
DELETE 没有 有
锁（重点）
锁是计算机协调多个进程或者线程并发访问某一资源的机制。

锁的主要作用:管理共享资源的并发访问，用于实现事务的隔离性

分类：

（图片来源：http://t.csdn.cn/zZEOv）

MySQL中的锁，按锁粒度分：

全局锁、表级锁、行级锁。

全局锁
全局锁就是对整个数据库实例加锁。

典型使用场景：做全局的逻辑备份，对所有表进行锁定。

语法：

flush tables with read lock;–让整个库处于只读状态的时候

解锁：unlock tables;

表级锁
每次操作该锁，都会锁住整张表，锁粒度越大，发生锁冲突概率越大，并发度越低。

表级锁分类：表锁，元数据锁（mata data lock,MDL），意向锁.

表锁
表锁分类：表共享读锁，表独占写锁。

语法：

lock tables 表名 read;//加了读锁后，除了当前进程，其它进程不能读.
lock tables 表名 write;//加了写锁后，除了当前进程，其它进程不能写.
1
2
InnoDB只支持用行锁，如果一定要加表锁，就进行以下操作
–用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, …;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;//解锁

1
2
3
4
5
6
7
8
元数据锁
MySQL5.5之后版本，加入了MDL；

MDL加锁过程为系统自动控制，其主要作用是：维护元数据的数据一致性，在表中有活动事务是，不能对元数据进行写操作，避免DML与DDL冲突，保证读写正确性。

元数据其实就像是表示表的数据结构，比如如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，会报错，这就需要MDL。

当对一个表做增删改查操作的时候，加MDL读锁；

当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥（共享），因此可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
1
2
注意：事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放，这也符合事务的ACID中的原子性

意向锁：
（先看行锁，再看这个）

意向锁是一个表级锁。

意向共享锁（IS）：与共享锁（S）兼容，与排它锁（X）互斥。

意向排他锁（IX）：与共享锁（S）以及排他锁（X）都互斥，

意向锁不会互斥。

意向锁主要作用：解决innoDB中行锁与表锁的冲突问题。

比如：一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁

\ 共享锁（S） 排他锁（X） 意向共享锁（IS） 意向排他锁（IX）
共享锁（S） 兼容 冲突 兼容 冲突
排他锁（X） 冲突 冲突 冲突 冲突
意向共享锁（IS） 兼容 冲突 兼容 兼容
意向排他锁（IX） 冲突 冲突 兼容 冲突
一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放

一个表的某行数据，共享、意向共享锁可以有多个，但是意向排他或排他锁只能有一个。

个人理解：写只能有一个，读可以有多个，有排他锁的线程开启后不让别人读，但如果设置的是意向排他锁，就允许意向共享锁的来读。

行级锁
行级锁：每次操作都会锁住对应的行数据，

InnoDB的数据主要是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，不是对记录加的锁，

主要分类：

行锁、间隙锁、临建锁；

行锁/记录锁（Record Lock）：单个记录上的锁

间隙锁（Gap Lock）：间隙锁，锁定一个范围，但不包含记录本身I（就是只锁间隙）

临建锁（next-key lock） = 间隙锁 + 行锁；（对一个范围的数据以及间隙加锁）

行锁（Record Lock）：
InnoDB 有两种类型的行锁：

1.共享锁（S）：共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

2.排他锁（X）：排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

语法：

共享锁（S）
SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）
SELECT * FROM table_name WHERE … FOR UPDATE
1
2
3
4
SQL 行锁类型 说明
INSERT 排他锁 自动加锁
UPDATE 排他锁 自动加锁
DELETE 排他锁 自动加锁
SELECT 不加 不加
SELECT……LOCK IN SHARE MODE 共享锁 需要手动区添加
SELECT……FOR UPDATE 排他锁 需要手动添加
注意：

1.默认情况下，在RR事务隔离级别下，InnoDB使用next-key锁(临建锁)来进行搜索和索引扫描，以防止幻读；

2.对于普通SELECT语句，InnoDB不会加任何锁；

1.在针对唯一索引进行检索时，对已存在的记录进行等值匹配，将自动转化为行锁。

2.InnoDB行锁是针对索引加的锁，如果不通过索引检索数据，InnoDB将会对表中所有记录加锁，此时行锁就会变成表锁。

间隙锁（Gap Lock）/临建锁（next-key lock）：
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Gap Lock）。

临建锁（next-key lock） = 间隙锁 + 行锁；（对一个范围的数据以及间隙加锁）

SELECT * FROM table_name WHERE 范围条件 FOR UPDATE
1
InnoDB使用间隙锁的目的：

1.防止幻读，以满足相关隔离级别的要求。对于某个范围（比如a>50），要是不使用间隙锁，如果其他事务插入了这个范围内数据的任何记录(比如插入一条a=55的数据)，那么这个事务如果再次执行上述语句，就会发生幻读；
2.为了满足其恢复和复制的需要。

间隙锁使用情况：

1.当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加间隙锁；

2.使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁

3.间隙锁可以共存；

间隙锁缺点：

1.当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据,这将影响使用性能。

2.数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

3.使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

补充：

间隙锁锁定原理：

执行update/delete/select for update，会产生间隙锁

innodb会根据更新或查询的索引条件，寻找非唯一索引的间隙锁的上下区间

如果插入数据在区间之内，则无法插入，因为被间隙锁锁定；如果插入数据在区间的边界值，此时需要根据主键来判断锁定范围(因为除了锁二级索引，也会锁聚集索引)；
比如：一个二级索引数据 a,a,c,d,d.（对应主键为1，5,4，2,6）。如果一个事务update范围为==c,则加a-d的临建锁，若此时另一个事务插入a，只要插入的主键<‘5’（a的最大主键值）则可以自由插入，主键>‘5’全部锁定无法插入；若此时另一个事务插入d，取d的最小主键值’2’，如果插入主键>‘2’,就可以插入，其它都不能插入。

1
2
3
4
5
6
7
如何合理使用InnoDB的行级锁：

1.在实际应用开发中，尤其是并发插入比较多的应用，要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；

2.尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定；

3.合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确**，尽可能的缩小锁定范围**，避免造成不必要的锁定而影响其他事务的执行；

4.尽量控制事务的大小，减少锁定的资源量和锁定时间长度

5.在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。
1
2
3
4
5
6
7
8
9
表级锁与行级锁对比：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

从锁的角度来说：

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；

而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理。
1
2
3
4
5
6
7
8
死锁问题
InnoDB检测到死锁报错：Deadlock found when trying to get lock;

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

例如：一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

一般而言，行级锁虽然在并发处理上有很大优势，但容易发生死锁问题。

死锁产生的四个必要条件（重点）：
1**.互斥条件**：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程结束该事务，才释放。

理解：比如行锁中的排他锁

2.请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而请求的资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放

理解：比如两个并发事务互相申请资源，A要（1-3-5-7-4），B要（4-7-5-3-1），A运行到比如要申请7的位置，7被B锁；B运行到申请到5的位置，5被A锁。

3.不可抢占条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放

理解：接上个例子：A被B锁了要等B释放，自己才能运行结束去释放，B被A锁了，要等A释放，自己才能继续运行，结束后才释放。

4.环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

理解：上接例子：此时A与B就形成了环路等待条件，直接死锁了

死锁原因
（该小节例子部分copy和参考的http://t.csdn.cn/uILFu部分，感觉解释比较直接）

死锁产生原因：

进程推进顺序不当、不可抢占性资源竞争产生死锁，竞争可消耗性资源引起死锁。

死锁一般是事务相互等待对方资源，最后形成环路造成的。下面简单讲下造成相互等待最后形成环路的例子。

假设我们有一张消息表（msg），里面有3个字段。假设id是主键，token是非唯一索引，message没有索引。

1.不同表相同记录行锁冲突

这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

2相同表记录行锁冲突

这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

3不同索引锁冲突

这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

4 gap锁冲突

innodb在RR级别下，间隙锁会产生死锁，比较隐晦。(这块原作者的例子看了好久，是在没看出来哪里死锁了，只能自己试了)

回顾间隙锁加锁原理：

innodb会根据更新或查询的索引条件，寻找非唯一索引的间隙锁的上下区间

如果插入数据在区间之内，则无法插入，因为被间隙锁锁定；如果插入数据在区间的边界值，此时需要根据主键来判断锁定范围(因为除了锁二级索引，也会锁聚集索引)；
比如：一个二级索引数据 a,a,c,d,d.（对应主键为1，5,4，2,6）。如果一个事务update范围为==c,则加a-d的临建锁，若此时另一个事务插入a，只要插入的主键<‘5’（a的最大主键值）则可以自由插入，主键>‘5’全部锁定无法插入；若此时另一个事务插入d，取d的最小主键值’2’，如果插入主键>‘2’,就可以插入，其它都不能插入。
1
2
3
4
例子与上面原理所讲例子一致：

CREATE TABLE test_dath1(
id int NOT NULL,
name varchar(15) NOT NULL COMMENT’NAME’
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE test_dath1 ADD PRIMARY KEY ( id );
CREATE INDEX index_name ON test_dath1(name);
insert into test_dath1 values (1,‘a’),(2,‘d’),(4,‘c’),(5,‘a’),(6,‘d’);

–线程1执行事务A
start transaction;
–1，执行顺序
update test_dath1 set name=‘c’ where name=‘c’;–锁死了a-d，插a锁主键>5.插b锁主键<2
–3
insert into test_dath1 values(3,‘e’); --此时d之后的已经被B的更新确定的排他锁锁住
commit;

–线程2执行事务B
start transaction;
–2
update test_dath1 set name=‘d’ where name=‘d’;–锁死了d以及d之后无穷大
–4
insert into test_dath1 values (8,‘a’);–此时A的插入锁住了主键a能插入大于5的的部分
commit;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

根据结果可以看到（具体原因看代码注释以及间隙锁原理）：发生了死锁问题

此外还有系统本身BUG、满足死锁必要条件的使用情况导致死锁（本文不在探讨）。

补充：

间隙锁解决幻读问题例子：

如下图所示，事务A在第一次查询时得到1条记录，在第二次执行相同查询时却得到两条记录。从事务A角度上看是见鬼了！这就是幻读，RC级别下尽管加了行锁，但还是避免不了幻读。

innodb的RR隔离级别可以避免幻读发生，怎么实现？当然需要借助于锁了！

为了解决幻读问题，innodb引入了gap锁。

在事务A执行：update msg set message=‘订单’ where token=‘asd’;

innodb首先会和RC级别一样，给索引上的记录添加上X锁，此外，还在非唯一索引’asd’与相邻两个索引的区间加上锁。

​ 这样，当事务B在执行insert into msg values (null,‘asd’,’hello’); commit;时，会首先检查这个区间是否被锁上，如果被锁上，则不能立即执行，需要等待该gap锁被释放。这样就能避免幻读问题。

死锁问题解决办法：
1.InnoDB中对于死锁问题

1.最直接的方法是，通过超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚（解死锁是数据库对事务的保护机制,一旦发生死锁,MySQL会选择相对小的事务(undo较少的)进行回滚。），另一个等待的事务就能继续进行。

2.除了超时机制，当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。

wait-for graph要求数据库保存以下两种信息：
锁的信息链表；
事务等待链表；
通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。
1
2
3
4
5
6
7
8
2.人员分析死锁问题（待填坑）

3.尽量预防和减少死锁：（方法很多，大致了解一下。）

预防主要就是看打破死锁产生的必要条件，其中互斥条件不能破坏（不然会导致不可重复读的并发问题），主要就是打破后三个条件。（https://blog.csdn.net/weixin_47005624/article/details/105668043，这篇介绍了部分关于三个条件打破，预防死锁的方法例子。）

此外还有：

1.尽量避免并发的执行涉及到修改数据的语句。

2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。

3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。

4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。

5.使用尽可能低的隔离级别。

6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。

7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放

摘自原文部分：https://blog.csdn.net/qq_34107571/article/details/78001309

避免死锁（等待完善）

使用银行家算法、安全性算法。

死锁的检测与解除
1.抢占资源，将一个一个或多个进程中抢占足够数量的资源，分配死锁进程，以解除死锁状态。

2.终止进程：终止系统中的一个或多个死锁进程，直至打破循环环路，使系统从死锁状态解脱出来

不同隔离级别下加锁情况
回顾一下隔离机制：
以MYSQL数据库为例，事务隔离级别：

隔离级别 脏读 不可重复读 幻读
Read uncommotted(读未提交) n n n
Read committed(读提交) y n n
Repeatable read(可重复读取)（mysql默认） y y n
Serializable(可序化) y y y
y:可以解决；n：不能解决

隔离级别查看：

SELECT @@tx_isolation;
1
第一种隔离级别：Read uncommitted(读未提交)
如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。

特点：一写多读，写的时候，其他事务也能读

却可能出现脏读，也就是说事务B读取到了事务A未提交的数据，出现不可重复读问题，B同时读取了A提交前后某一行数据，数据不一致，也避免不了幻读问题，在此就不举例了。

第二种隔离级别：Read committed(读提交)
如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

特点：一个事务写这个数据时，其它事务不可读，只能读写后的。

只解决了脏读问题

第三种隔离级别：Repeatable read(可重复读取)
可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读（即一个事务在进行读某一行数据时，另一个事务对这行数据所在表进行了数据条数的增减，导致查询结果不一致）。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

特点：一个事务执行结束前，同一行数据只能在一个事务内读写，其他事务不能操作。

解决了脏读、不可重复读，但是还会出现幻读

第四种隔离级别：Serializable(可序化/串行化)
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行。

特点：只能一个事务来读写这个表，其他事务没有读写权限。

不仅可以避免脏读、不可重复读，还避免了幻读

补充：以上四种级别，在实际效率方面，由高到低，也就是说安全级别高，也会导致效率的下降。

一般我们常用的就是第三种RR（Mysql默认级别）和第二种RC隔离级别；

不同隔离级别下加锁情况：
锁是加载索引上的。对于不同的隔离级别，不同的列情况，加锁情况都各不不同。

读未提交级别（RU）
读操作不会加锁，写操作会加排他锁，但该写操作与读操作兼容，故会导致脏读。

MVCC不发生效果

无论是否使用索引、是否添加锁，只对操作数据添加行锁（record lock），不会产生间隙锁

读已提交级别（RC）
读操作不会加锁，写操作会添加排他锁

MVCC不发生效果

无论是否使用索引、是否添加锁，只对操作数据添加行锁（record lock），不会产生间隙锁

可重复级别（RR）
读操作不会加锁，写操作会添加排他锁，有间隙锁

MVCC 会在事务开始第一次查询时生成 Read View

加锁规律：

加锁默认为 next-key lock(临键锁)

非唯一索引会向排序方向一直匹配，直到不满足条件为止。

可串行化级别
读操作加读锁，写操作加写锁，读写操作互斥（但有间隙锁）

1用到主键索引和唯一索引，会对操作数据添加 Record Lock。

2普通索引，会对操作数据以及间隙添加 Next-key Lock。

3未使用索引，会对所有数据以及两边间隙添加 Next-key Lock

加锁机制
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制采用的主要技术手段

悲观锁（Pessimistic Concurrency Control，PCC）
悲观锁，是一种并发控制方法，对于数据被外界修改保持保守（悲观）态度，在处理数据的过程中会将这个数据设置为锁定状态。

具体流程：
在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
1
2
3
4
5
6
悲观锁的优点和不足：

悲观锁实际上是采取了“先取锁再访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

乐观锁（Optimistic Concurrency Control，OCC）
相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

乐观锁并不会使用数据库提供的锁机制，一般的实现乐观锁的方式就是记录数据版本。

为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。
1
2
3
乐观锁的优点与不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。
1
InnoDB引擎底层（重点）
InnoDB索引结构与Myisam索引结构区别：
InnoDB引擎采用B+Tree结构来作为索引结构；

InnoDB（索引组织表）的索引类型分为聚簇索引和非聚簇索引；

主键索引（聚簇索引）结构特点：整张表的数据其实就是存储在聚簇索引中的，故聚簇索引就是表。其叶子节点存储的是主键的值和整行数据

（如果一个表没有主键索引，MYSQL也会自动生成一个聚簇索引（具体看本文聚簇索引生成规则））

非聚簇索引结构特点：叶子节点存储的是主键的值。

MyISAM引擎也采用B+Tree结构来作为索引结构；

MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引，其叶子节点存储的是数据的地址

逻辑存储结构：
InnoDB的存储文件有两个，后缀名分别是 .frm和 .idb；其中 .frm是表的定义文件， .idb是表的数据文件。

在存储引擎概述中，已经稍微介绍了一下存储结构，现在详细探讨一下。

表空间（table space）：表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、 临时表空间、Undo 表空间

段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等

InnoDB的数据段就是B+树的叶子节点，索引段就是B+树的非叶子节点，段就是用来管多个Extent（区）。

区：一个段由很多个区组成，每个区默认大小为1M（64个连续的页），为了保证区中页的连续性，区扩展时，InnoDB 存储引擎会一次性从磁盘申请4 ~ 5个区。

页：为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页（Page）组成的空间，一个簇中有 64 个连续的页

InnoDB 也有页的概念（也可以称为块），每个页默认 16KB（一个表空间最多拥有 2^32 个页，即最大存储64TB数据）

行：InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。

InnoDB架构：

内存架构：
Buffer Pool(缓冲池，简称BP)
为什么使用缓冲池？

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页(page)的方式进行管理。但是磁盘IO读取速度与CPU速度差距过大（目前磁盘io操作平均在100次/秒），所以基于磁盘的数据库系统需要通过使用缓冲池（位于内存）来提高数据库的整体性能。

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、更改缓冲区(change buffer)、自适应HASH索引(adaptive hash index)、锁信息(lock info)、数据字典信息等。

        Buffer Pool 主要有这三种page管理：free page ： 空闲page，未被使用clean page：被使用page，数据没有被修改过dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

针对上述三种page类型，InnoDB通过三种链表结构来维护和管理：

free list ：表示空闲缓冲区，管理free page

flush list：表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间
排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负
责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作。

lru list：表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以

midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后
面的链表称为old列表区，存放使用较少数据，占37%
参考原文链接：https://blog.csdn.net/qq_36194388/article/details/109325528

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
为了提高大容量读取操作的效率，缓冲池以页(page，默认大小16k)为单位。为了提高缓存管理的效率，缓冲池被实现为链接的页链表(linked list of pages)；使用LRU算法的变体，很少使用的数据在缓存中老化(aged out)。

LRU算法

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰

力扣与牛客上有该类型算法题（可以去练习一下，这是LRU的基本原理，做了就能理解）

改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间（下面链接文章可以看看）

http://t.csdn.cn/BORwK

Change Buffer
写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据， 并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数 据合并恢复到BP中。

ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%
调整参数：innodb_change_buffer_max_size;
1
2
写缓冲区，仅适用于非唯一普通索引页

因为：索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作

Log Buffer(日志缓存区)：
用来保存写入到磁盘中的log日志数据（定期刷新到磁盘上面），默认大小为16MB。日志缓冲区满时会自动将其刷新到磁盘

控制日志刷新频率，默认值：1； 0：每1妙刷新磁盘和日志文件，可能会丢失最多1秒的数据；1：事务提交立即写日志文件和刷盘，数据不会丢失但io操作频繁，2：事务提交后立刻写日志文件，每隔1秒进行刷盘。
innodb_flush_log_at_trx_commit
调整Log Buffer的大小
nnodb_log_buffer_size
1
2
3
4
增加日志缓存区大小可以节省磁盘io。

4.自适应哈希（Adaptive Hash index）:优化对Buffer Pool 数据的查询。

InnoDB存储引擎会监 控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。所以这是自动开启，不需要优化

磁盘架构：
主要包含：系统表空间、通用表空间、独立表空间、临时表空间、撤销（undo）表空间、双写缓冲区、重做日志（redo log）

系统表空间：系统表空间是一个共享的表空间，因为它是被多个表共享的。

1.InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buffer(脏页落盘时使用的双写缓冲区)、change buffer(修改缓冲区)、undo logs的存储区域。

2.系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据

独占表空间：每个表的文件表空间含单个InnoDB表的数据和索引 ，并存储在文件系统中自己的数据文件中。

通用表空间：类似于系统表空间，可以存储多个表的数据的一直共享表空间，支持Antelope和Barracuda文件格式。

undo表空间：undo表空间包含undo log撤销记录的集合，其中包含通过主键索引事务撤销更改的最小信息

双写缓冲区：innodb将数据页从BP刷新到磁盘前，会先把数据页写到双写缓冲区里，便于系统异常时恢复数据。

redo log（重做日志）：保证事务持久性（重做日志主要分为重做日志缓冲（在内存里）、重做日志文件（在磁盘里））

后台线程：
后台线程分为Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。

后台线程的主要作用：
从磁盘刷新内存池中的数据，保证缓冲池中缓存的数据是最新的；

将缓冲池中已修改的数据文件刷新到磁盘；

保证数据库异常时InnoDB能恢复到正常运行状态；

Master Thread:核心后台线程
核心后台线程，负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括：脏页的刷新、合并插入缓冲（Insert Buffer）、undo页的回收等等。

IO Thread
InnoDB 中使用了大量的AIO（Async IO）来处理写IO请求，以此提高数据库的性能。IO Thread负责这些IO请求的回调处理。

Purge Thread
用于回收已经分配且不再需要的undo页

Page Cleaner Thread
作用是把原本Master Thread中的脏页刷新操作独立到单独的线程中，减轻Master Thread的工作以及对于用户查询线程的阻塞，提高存储引擎的性能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
事务原理：
再回顾一下事务：
关于事务，基础篇作者整理了一部分概念，现在重新回顾，凝炼一下。

回顾一下事务的四大特性（ACID）：原子性（(Atomicity），一致性（(Consistent)），隔离性（Isolation），持久性（Durable）。

**原子性：**事务是一个原子操作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。

事务中任何一个SQL语句执行失败，已经执行成功的SQL必须撤销，数据库状态应该退回到事务前的状态。

**一致性：**指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

即：事务在开始前与结束后，数据都要保持一致性的状态，如果事务中某个动作失败了，系统可以自动撤销事务 — 返回初始化的状态

**事务隔离性：**要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常使用锁来实现。

**持久性：**事务一旦提交，其结果就是永久性的。即使发生宕机等故障也能恢复。

事务的实现原理

事务的原子性是通过undo log（日志系统：归档日志）来实现的

事务的持久性是通过redo log（日志系统：重做日志）来实现的

事务的隔离性是通过（读写锁+MVCC）来实现的

事务的一致性是通过原子性、持久性、隔离性来实现的

接下来学习三个特性如何保证实现的。

undo log（回滚日志）
回滚日志：用于记录数据被修改前岁的信息。

作用：提供回滚和MVCC（多并发控制）；

undo log属于逻辑日志，每当执行一个记录时，其就会产生一个逻辑相反的记录（比如delete某个记录，其就会产生对应insert记录），当执行rollback时，就可以借助undo loh中的逻辑记录读取到相应内容，并进行回滚；

undo log销毁：在事务执行时产生，在事务结束后，也不会立刻销毁，因为在快照读中也需要 (即该 undo log 需要提供 MVCC 机制)，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的 undo log 才会被清理 (purge) 线程删除；

undo log存储：采用段的方式进行管理

redo log（重做日志）
记录事务提交时数据页的物理修改，用来保证事务的持久性。

该日志由：重做日志缓存（在内存中）以及重做日志文件（在磁盘中）；

作用：在事务提交之后把所有修改信息存到日志文件中，在刷新脏页到磁盘发生故障时，进行数据恢复使用，保证事务的持久性。

MVCC（重点）
基本概念：
MVCC(Multi-Version Concurrency Control)，即多版本并发控制

基本概念：MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存

MVCC的作用：为了提高数据库的并发性能，即读不加锁，读写不冲突，在读多写少的系统中，读写不冲突是非常重要的，极大地提高了系统的并发性能；

快照读
快照读：简单的selsect（不加锁）就是快照读（即不加锁的非阻塞读）

快照读的前提是隔离级别不是未提交读和串行化级别，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁。

快照读读取的是记录数据的可见版本，有可能是记录数据的可见版本，也可能是历史数据

四种隔离级别下快照读的位置：

RU:会退化为当前读

RC:每次select，都会生成一个快照读

RR:开启后事务后的第一个select语句为快照读的地方。（MVCC，readiew更新机制）

S:会退化为当前读

当前读
当前读：读取是记录的最新版本，读取是要保证其它并发事务不能修改当前记录，会对读取的记录加锁。

当前读 （就是加了锁的读）
select … lock in share mode
select … for update
1
2
3
实现原理（重要）：
记录中的隐藏字段（Invisible Column）（MySQL 8.0.23 版本之后添加）
InnoDB中每行数据记录都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针（若没有主键，则还有一个隐藏主键：DB_ROW_ID：行标识（隐藏单增ID），没有主键时主动生成(6字节)）

6字节的事务ID（DB_TRX_ID ）：表示最近修改数据的事务id

7字节的回滚指针（DB_ROLL_PTR）：指向这条记录的上一个版本，配合undo log，

隐藏字段图示：

图片来源：黑马学习视频

undo log 版本链
每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

图片来源：黑马学习视频

根据图片例子，可以看到记录中的每条undo log也会指向其更早版本的undo log （4----3-------2---------1）

ReadView（读视图）
是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（为提交的）id。

包含了四个核心字段：

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务id（是自增的，是最大事务id+1）
creator_trx_id ReadVeiw创建的事务id
版本链数据访问规则：

trx_id表示当前事务id

规则1：trx_id==creator_trx_id:

可以访问该版本，说明此时数据读取是当前事务更改的（自己读自己改）

规则2：trx_id<min_trx_id:

可以访问该版本，说明该数据已经提交（是被其它已经commit的事务修改了）

规则3：trx_id>max_trx_id:

不可以访问该版本，该事务是在readeiw生成后才开启的（）

规则4：min_trx_id<=trx_id<=max_trx_id时

如果trx_id不在m_dis中，则可以访问（说明该事务已经提交了，

例子：比如事务1，2，3，4，5，6按序列依次开启，此时min_trx_id=1，max_trx_id=7，其中5先好了，此时m_dis为(1,2,3,4,6);数据a最近被5修改了，事务3来快照读这个数据时，此时trx_id=3，根据要求会先读5的，虽然其它三个条件都不满足）

不同的隔离级别，readiew生成也不同：

RC：在事务中每一次执行快照读的时候，生成readview

RR：只在事务第一次执行时执行快照读，并生成readiew，后续就复用该readview;