数据库底层原理实现和操作细节（包含详细的sql语句）_数据库是怎么实现的

一、MySQL 的基本定义：

术语介绍

1. 数据库（Database）

是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制所保存的数据。

2. RDBMS（Relational Database Management System）

关系数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
特点：

• 数据以表格的形式出现。
• 每行为各种记录名称。
• 每列为记录名称所对应的数据域。
• 许多的行和列组成一张表单。
• 若干的表单组成Database。

RDBMS术语：

• 数据库： 数据库是一些关联表的集合。
• 数据表： 表示数据的矩阵。
• 列： 一列（数据元素）包含了相同类型的数据。
• 行： 一行是一组相关的数据。
• 冗余： 存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键： 主键是唯一的，一个数据表中只能包含一个主键。
• 外键： 外键用于关联两个表。
• 复合键： 复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引： 使用索引可快速访问数据库表中的特定信息，索引是对数据库表中一列或多列的值进行排序的一种结构。
• 参照完整性： 参照完整性要求关系中不允许引用不存在的实体，与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL常用命令

1. 管理MySQL的命令

• MySQL的用户设置，只需要在MySQL数据库中的user表中添加新用户即可。

use mysql; insert into user() values()。
1

• USE 数据库名 ：选择要操作的MySQL数据库，使用该命令后所有MySQL命令都只针对该数据库。
• SHOW DATABASES：列出MySQL数据库管理系统的数据库列表。
• SHOW TABLES：显示指定数据库的所有表，使用该命令前需要使用use命令来选择要操作的数据库。
• SHOW COLUMNS FROM 数据表名 ：显示数据表的属性，属性类型，主键信息，是否NULL，默认值等其它信息。
• SHOW INDEX FROM 数据表名： 显示数据表的详细索引信息，包括 - PRIMARY KEY（主键）。
• SHOW TABLE STATUS FROM 数据库名 LIKE ‘pattern’\G ：该命令输出MySQL数据库管理系统的性能及统计信息。

2. MySQL的基本命令

• CREATE DATABASE 数据库名：创建数据库。（或者使用root权限创建：mysqladmin -u root -p create数据库名）。
• DROP DATABASE 数据库名：删除数据库。（或者使用root权限删除：mysqladmin -u root -p drop 数据库名）。
• SHOW CREATE TABLE 数据库名：获取创建数据表（CREATE TABLE）语句，该语句包含了原数据表的结构、索引。

创建数据表

CREATE TABLE tb_name （column_name column_type）；
例：
create tale tb_student(
    s_id  int  PRIMARY KEY  AUTO_INCREMENT,
    s_name varchar(20)  NOT NULL,
    s_sex varchar(10),
    s_age int
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8

NOT NULL ：在操作数据库时如果输入该字段的数据为NULL，则报错。

AUTO_INCREMENT ：定义列为自增的属性，一般用于主键，数值会自动加1。

PRIMARY_KEY ：定义列为主键，（也可以使用多列定义主键,以逗号分隔）（PRIMARY_KEY s_id, s_sex）。

ENGINE ：设置存储引擎，不定义则使用默认存储引擎（MySQL默认存储引擎是 InnoDB）。

CHARSET ：设置编码格式，默认是UTF-8。
1
2
3
4
5
6
7
8
9

删除数据表

DROP TABLE tb_name；
1

删除表中字段

ALTER TABLE tb_name DROP column;
1

向表中添加字段

ALTER TABLE tb_name ADD column 类型;          # 添加到最后一列。
ALTER TABLE tb_name ADD column 类型 FIRST;    # 添加到第一列。
ALTER TABLE tb_name ADD column 类型 AFTER column;  # 添加到某个字段之后。
1
2
3

修改字段类型及名称

ALTER TABLE tb_name MODIFY column 类型;    # 使用MODIFY修改。
ALTER TABLE tb_name CHANGE old_column new_column 类型；  # 使用CHANGE修改。
ALTER TABLE tb_name MODIFY column 类型 NOT NULL DEFAULT 100; # 指定字段非空，默认值100。
1
2
3

修改字段默认值

ALTER TABLE tb_name ALTER column SET DEFAULT 1000;  # 修改字段默认值。
ALTER TABLE tb_name ALTER column DROP DEFAULT;     # 删除字段默认值。
1
2

修改表名

ALTER TABLE old_tb_name RENAME TO new_tb_name;
1

修改存储引擎

ALTER TABLE tb_name engine=InnoDB;
1

修改外键约束

ALTER TABLE tb_name DROP FOREIGN KEY keyname;
1

插入数据

INSERT INTO table_name (field1, field2, ……, fieldN)  VALUES （value1, value2, ……, valueN）;
1

更新数据

UPDATE tb_name SET field1=new-value1, field2=new-value2 WHERE … ；
WHERE子句中可以指定任何条件。
1
2

删除数据

DELETE FROM table_name WHERE … ;
WHERE 子句中可以指定任何条件。
1
2

查询数据

SELECT column_name1, column_name2 FROM table_name1, table_name2 
WHERE [condition1 [AND [OR]] condition2 …… ；
1
2

查询语句中可以使用一个或者多个表，表之间使用逗号（，）分割，并使用WHERE语句设定查询条件。

WHERE 子句中可以指定任何条件。

可以使用AND或者OR指定一个或多个条件。

可以使用LIKE子句代替等号（=），LIKE通常与（%）一同使用，类似于一个元字符的搜索（LIKE“ACC%”）。

WHERE子句也可以运用SQL的DELETE或UPDATE命令。

可以使用LIMIT属性来设定返回的记录数。

可以使用OFFSET指定SELECT语句开始查询的数据偏移量，默认偏移量为0。

WHERE子句类似于程序语言中的if条件，根据MySQL表中的字段值来读取指定的数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

WHERE子句操作符：
=        ：等于
<>, !=   ：不等于
>        ：大于
<        ：小于
>=      ：大于等于
<=      ：小于等于
1
2
3
4
5
6
7

UNION操作符

UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。
多个SELECT语句会删除重复的数据。
1
2

SELECT expression1, expression2, ……, expressionN FROM tb_name1 WHERE ……
UNION  [ ALL | DISTINCT ]
SELECT expression1, expression2, ……, expressionN FROM tb_name2 WHERE ……;
1
2
3

ALL ：可选项，返回所有结果集，包含重复数据。

DISTINCT ：可选项，删除结果集中重复的数据。
1
2
3

排序

ORDER BY ：对查询结果进行排序。
SELECT column1, column2 FROM tb_name WHERE …… ORDER BY field1 [ASC/DESC]，field2 [ASC/DESC]。

ASC/DESC ：升序/降序，默认ASC。
1
2
3
4

分组

GROUP BY ：对查询结果进行分组。
SELECT column1, column2 FROM tb_name WHERE …… GROUP BY column1, column2。
1
2

连接

JOIN  ：在两个或多个表中查询数据。

INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录。

 内连接使用比较运算符对两个表中的数据进行比较，
 并列出与连接条件匹配的数据行，组合成新的记录，
 结果只保留满足条件的记录。
1
2
3
4
5
6
7

SELECT column1, … FROM tb_name1 INNER JOIN tb_name2 ON tb_name1.id = tb_name2.id;
1

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
左表保持不动，右表在右侧滑动，用右表匹配左表，结果保留左表的所有行，右表中不匹配的行默认填充为空值NULL。

SELECT column1, … FROM tb_name1 LEFT JOIN tb_name2 ON tb_name1.id=tb_name2.id;

RIGHT JOIN（右连接）：获取右表所有记录，即使左表没有对应匹配的记录。
右表保持不动，左表在左侧滑动，用左表匹配右表，结果保留右表的所有行，左表中不匹配的行默认填充为空值NULL。

SELECT column1, … FROM tb_name1 RIGHT JOIN tb_name2 ON tb_name1.id=tb_name2.id;
1
2
3
4
5
6
7
8
9

空值

IS NULL ：当列的值是NULL，此运算符返回true。

IS NOT NULL ：当列的值不为NULL，此运算符返回true。

<=> ：比较操作符（不同于 = 运算符） ：当比较的两个值相等或者都为NULL时，返回true。
1
2
3
4
5

3. MySQL数据类型

数值类型

MySQL中支持所有标准SQL数值数据类型。
包括严格数值数据类型（INTEGER，SMALLINT，DECIMAL，NUMERIC）。
以及近似数值数据类型（FLOAT，REAL，DOUBLE PRECISION）。
1
2
3

日期和时间类型

表示时间值的日期和时间类型（DATETIME，DATE，TIMESTAMP，TIME，YEAR）。
每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。
1
2

字符串类型

字符串类型（CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM，SET）。
1

4. MySQL索引

索引是应用在SQL查询语句的条件，一般作为WHERE子句的条件。

• 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
• 普通索引：基本的索引类型，没有唯一性限制，允许为NULL值。
• 唯一索引：索引列的值必须唯一，但允许有空值；如果是组合索引，则列值的组合必须唯一。
• 前缀索引：用列的前缀代替整个列作为索引key，比如：like‘xxx%’。
• Hash索引：采用一定的哈希算法，把键值换算成新的哈希值，只需一次哈希算法即可定位到相应的位置，查询速度非常快。

创建索引的三种方式

CREATE INDEX indexName ON tb_name(column(length));   # 直接创建索引。

ALTER TABLE tb_name ADD INDEX indexName(column);    # 修改表结构时添加索引。

CREATE TABLE tb_name(                                  # 创建表的时候直接指定。
    id int NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX  [indexName] (username(length))
);
1
2
3
4
5
6
7
8
9

创建唯一索引的三种方式

CREATE UNIQUE INDEX indexName ON tb_name(column(length))；    # 直接创建索引。

ALTER TABLE tb_name ADD UNIQUE [indexName] (column(length))；  # 修改表结构时创建索引。

CREATE TABLE tb_name(                                             # 创建表时添加索引。
    id int NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE [indexName] （username(length)）
);
1
2
3
4
5
6
7
8
9

删除索引

DROP INDEX [indexName] ON tb_name;
1

使用ALTER命令添加、删除索引

ALTER TABLE tb_name ADD PRIMARY KEY (column);   # 该语句添加一个主键，索引值必须是唯一的，且不能为NULL。

ALTER TABLE tb_name ADD UNIQUE indexName (column);  # 该语句创建索引的值必须是唯一的（除NULL外）。

ALTER TABLE tb_name ADD INDEX indexName (column);     # 添加普通索引，索引值可出现多次。

ALTER TABLE tb_name ADD FULLTEXT indexName (column);  # 该语句指定索引为FULLTEXT，用于全文索引。

ALTER TABLE tb_name DROP INDEX column;   # 删除索引。

ALTER TABLE tb_name ADD PRIMARY KEY (column);   # 添加主键。

ALTER TABLE tb_name DROP PRIMARY KEY;      # 删除主键。

SHOW INDEX FROM tb_name;    # 显示索引信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

explain

查看执行计划，使用explain关键字可以模拟优化器执行SQL语句，查看使用到的索引列及其它信息。

explain select * from tb_name;
1

5. 索引使用策略及优化

最常用的索引底层存储结构是棵 B- Tree 或 B+ Tree。

索引有两种：单列索引 和 组合索引

• 单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引：一个索引包含多个列。
• 单列索引：一个索引只能有一个字段。
• 组合索引：也称复合索引，相对于单列索引，组合索引可以为多个字段创建一个索引。

最左前缀原理

• 最左匹配就是最左边优先；创建组合索引时，要根据业务要求，where子句中使用最频繁的一列放在最左边。
• 组合索引的查找是先根据第一个字段查，然后再根据第二个字段查，或者只根据第一个字段查，但是不能跳过第一个字段、直接从第二个字段开始查，这就是所谓的最左前缀原理。

第一个字段是有序的。
当第一个字段值相等的时候，第二个字段也是有序的。
当第一个字段值相等、第二个字段值也相等时，第三个字段也是有序的。

例：在字段 a, b, c上创建一个联合索引，索引顺序会首先按照a字段排序，然后再按照b字段排序，最后是c字段。
下面的SQL语句是按照（（a），（a, b），（a, b, c））的顺序用到索引。

select * from tb_name where a = 0;
select * from tb_name where a = 0 and b = 1;
select * from tb_name where a = 0 and b = 1 and c = 2;

下面的SQL语句只用到一个索引a 。
select * from tb_name where a = 0 and c = 2;

下面的SQL语句未使用到索引，因未遵循最左匹配原理。
select * from tb_name where b = 1 and c = 2;

以MySQL为例，下面的SQL语句也能使用到索引，查询优化器会重新编译，不建议这样使用。
select * from tb_name where b = 1 and c = 2 and a = 0;
1
2
3
4
5
6
7
8
9
10
11
12

索引优化

1. 主键、外键要建索引。
2. 对where, on, group by, order by中出现的列使用索引。
3. 最左匹配原则（重中之重）。
4. 尽量扩展索引；例：已经有a字段索引，现在要使用（a, b）字段的索引，只需修改原来的索引即可。
5. 不要过多创建索引，索引过多会影响插入、删除数据的速度。
6. 对于like查询，“%”不要放在前面。
7. where条件数据类型不匹配也无法使用索引。
8. 为较长的字符串使用前缀索引。
9. 对索引列进行函数运算时索引也会失效。

6. 聚集索引 与 非聚集索引

聚集索引 与 非聚集索引底层引用的都是 B+ 树索引。

• 聚集索引（clustered）
  也叫聚簇索引，数据行的物理顺序与列值（主键）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
• 非聚集索引（unclustered）
  该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

7. MySQL数据导入、导出（导入、导出文件需用户有FILE权限）

常用导出数据语句

SELECT … INTO OUTFILE
1

将一个数据库的数据写入一个文件，输出不能是一个已存在的文件。

SELECT * FROM tb_name INTO OUTFILE ‘/tmp/tmp.txt’;
1

LOAD DATA INFILE
将文件读回数据库。

常用导入数据语句

mysql命令导入
mysql  -u用户名  -p密码   <   要导入的数据库数据（tmp.sql）
例：mysql -uroot -p123456 < tmp.sql     # 将备份的整个数据库tmp.sql导入

source 命令导入
create database abc;           # 创建数据库。
use abc;                       # 使用已创建的数据库。
set names utf8;                # 设置编码。
source  /home/abc/abc.sql;    # 导入备份数据库。

LOAD DATA命令导入
LOAD DATA LOCAL INFILE ‘tmp.txt’ INTO TABLE myTable
FIELDS TERMINATED BY ‘:’
LINES TERMINATED BY ‘\r\n’;     # 将tmp.txt文件中的数据导入myTable表中
1
2
3
4
5
6
7
8
9
10
11
12
13
14

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。
如果没有指定，则文件在服务器上按路径读取文件。

二、数据库事务与并发性：

数据库事务

在MySQL中，只有使用了Innodb引擎的数据库或表才支持事务。

1. 数据库事务定义

事务处理可以用来维护数据库的完整性，保证SQL语句要么全部执行，要么全部不执行。

事务用来管理 insert, update, delete 语句。

1、原子性（Atomicity， 或称不可分割性）

一个事务中的操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
事务在执行过程中发生错误，会被回滚到事务开始前的状态。

2、一致性（Consistency）

在事务开始之前和事务结束之后，数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则。

3、隔离性（Isolation，又称独立性）

数据库允许多个并发事务同时对其数据进行读写和修改的能力。
隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

4、持久性（Durability）

事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。

2. 事务控制语句

MySQL数据库事务默认都是自动提交的，即执行完SQL语句后就会马上执行COMMIT操作。然后显式地开启、提交事务

BEGIN / START TRANSACTION ： 开启一个事务。
COMMIT / COMMIT WORK ：提交事务，使已对数据库进行的所有修改成为永久性的。
ROLLBACK / ROLLBACK WORK ：回滚事务，撤销正在进行的所有未提交的修改。

SAVEPOINT identifier ：允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
RELEASE SAVEPOINT identifier ：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
ROLLBACK TO identifier ：把事务回滚到标记点。
SAVEPOINT 是在数据库事务处理中实现“子事务”，也称嵌套事务的方法。

事务可以回滚到SAVEPOINT而不影响SAVEPOINT创建前的变化，不需要放弃整个事务。
SAVEPOINT savepoint_name;     # 声明一个savepoint 。
ROLLBACK TO savepoint_name;   # 回滚到 savepoint 。
RELEASE SAVEPOINT savepoint_name;   # 删除指定保留点 。

SET TRANSACTION ：用来设置事务的隔离级别。

MySQL事务处理的两种方法
1） 用BEGIN, ROLLBACK, COMMIT实现。
    BEGIN 开始一个事务。
    ROLLBACK 回滚事务。
    COMMIT 提交事务。

2）直接用SET来改变MySQL的自动提交模式
SET AUTOCOMMIT = 0  ：禁止自动提交。
SET AUTOCOMMIT = 1  ：开启自动提交。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

数据库并发性

不同的数据库隔离级别不同，使用加锁的方式也不同。
MySQL支持四种事务隔离级别，默认隔离级别是（RR， Repeatable Read）。
Oracle 支持两种事务隔离级别（RC 与 Serializable），默认隔离级别是（RC， Read Committed）。

1. 读数据的概念

1) 脏读（Dirty Reads）

就是对脏数据的读取，脏数据指的是未提交的数据。

2) 不可重复读（Non-Repeatable Reads）

一个事务先后读取同一条记录，两次读取的数据不同。

3) 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其它事务插入了满足其查询条件的新数据。

例：存在两个事务（T1, T2）同时运行

T1读取了已经被T2修改但还未提交的字段，由于某种原因，T2事务回滚，则T1读取的内容是临时且无效的；这就是脏读。
T1读取一个字段，之后T2更新了该字段，T1再次读取该字段值时则读取到的是被T2更新后的新值；这就是不可重复读。
T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后T1再次读取该表时会多出几行。这就是幻读。

2. 数据库隔离级别

1) Read UnCommitted（读未提交数据）

• 允许事务读取未被其它事务提交的变更数据。
• 会出现脏读、不可重复读和幻读问题，隔离级别最低（读不锁）。

2) Read Committed（读已提交数据）

• 允许事务读取已经被其它事务提交的变更数据。
• 可避免脏读，扔会出现不可重复读和幻读问题（读锁）。

3) Repeatable Read（可重复读）

• 确保事务可以多次从一个字段中读取相同的值。
• 在此事务持续期间，禁止其它事务对此字段的更新。
• 可以避免脏读和不可重复读，扔会出现幻读问题，RR隔离级别对读取到的记录加锁（写锁）。

4) Serializable（序列化）

• 确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其它事务对该表执行插入、更新和删除操作。
• 可避免所有并发问题，但性能非常低。
• 所有的SELECT语句都被隐士的转换成SELECT …… LOCK IN SHARE MODE。
• 即读取使用表级共享锁，读写相互都会阻塞，隔离级别最高（表级锁）。

3. 数据库的存储引擎

常见的数据库存储引擎有：（1）MyISAM （2）InnoDB 。

1) MyISAM

• 支持表级锁。
• 适用场景：读多写少，硬件配置不高。

2) InnoDB （MySQL默认存储引擎）

• 支持表级、行级（默认）锁。
• 适用场景：支持事务、支持外键，即有读又有写的业务中。

4. 数据库锁

锁主要用于多用户环境下保证数据库完整性和一致性。

1) 锁按使用方式划分

乐观锁

每次去拿数据的时候都认为别人不会修改，所以不会上锁。
但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。
如果发生冲突了，则返回用户错误信息，让用户决定如何去做。
大多是基于数据版本（Version）记录机制实现。

悲观锁

每次去拿数据的时候都认为别人会修改，所以每次拿数据的时候都会上锁。

2) 锁按级别划分

共享锁

共享锁（Share Lock），S锁，也叫读锁，用于所有的只读数据操作。
共享锁是非独占的，允许多个并发事务读取其锁定的资源。

共享锁性质：

• 多个事务可封锁同一个共享页。
• 任何事务都不能修改该页。
• 通常是该页被读取完毕，S锁立即被释放。

排他锁

排他锁（Exclusive Lock），X锁，也叫写锁，用于对数据进行写操作。
如果一个事务对对象加了排他锁，其它事务就不能再给它加任何锁了。

排他锁性质

• 仅允许一个事务封锁此页。
• 其它任何事务必须等到X锁被释放才能对该页进行访问。
• X锁一直到事务结束才能被释放。

3) 锁按粒度划分（MySQL）

表级锁

• 锁的作用范围是整张表。
  -开销小、加锁快，不会出现死锁。
• 锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁

• 锁的作用范围是行级别。
• 开销大、加锁慢，会出现死锁。
• 锁定粒度最小，发生锁冲突的概率最低，并发度最高。

页级锁

• 锁的作用范围是整个页面。
• 开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

4) 意向锁

意向锁是InnoDB自动加的，不需要用户干预。

意向共享锁（Intention Shared Lock）

表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。

意向排它锁（Intention Exclusive Lock）

表示事务准备给数据行加入排它锁，也就是说一个数据行加排它锁前必须先取得该表的IX锁。

5. 锁实现方式

• 当一个事务获得对一个表的写锁后，只有持有锁的事务可以对表进行更新操作，其它事务的读、写操作都会等待，直到锁被释放为止。
• 当一个事务获取对一个表的读锁后，其它事务也可以获取此表的读操作权限，但其它事务不能获取此表的写操作权限，直到锁被释放为止。

行级锁实现方式

行级锁不是锁记录，而是锁索引；只有通过索引条件检索数据，才能使用行级锁。

隐式加锁

• 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁。
• 对于普通SELECT语句，InnoDB不会加任何锁。

显式加锁

SELECT * FROM tb_name WHERE …… LOCK IN SHARE MODE;    # 加共享锁。
SELECT * FROM tb_name WHERE …… FOR UPDATE;              # 加排他锁。
1
2

表级锁实现方式

隐式加锁

• 在执行查询语句SELECT前，会自动给涉及的所有表加读锁。
• 在执行更新操作UPDATE、DELETE、INSERT前，会自动给涉及的表加写锁。

显式加锁

LOCK TABLE tb_name WRITE;     # 加写锁
LOCK TABLE tb_name READ；    # 加读锁

UNLOCK TABLES；     # 释放锁
1
2
3
4

6. InnoDB锁机制

1 Record Lock

行锁，单条索引记录加锁，Record Lock锁住的是索引，而非记录本身。

2 Gap Lock

• 间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
• Gap Lock 是针对事务隔离级别为RR或以上。
• Gap Lock在InnoDB的唯一作用就是防止其它事务的插入操作，以此防止幻读的发生。
• Gap Lock 一般针对非唯一索引而言。

3)Next-key Lock

• Next-key Lock是Record Lock和Gap Lock的结合，即锁住了记录本身，还要锁住索引之间的间隙。
• MySQL 的事务隔离级别默认是RR，若innodb_locks_unsafe_for_binlog参数为0，默认采用Next-key Lock。
• Next-key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。
• 加上间隙锁之后，其它事务就不能在这个间隙修改或者插入记录。

7. 死锁

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

1 产生死锁的原因

• 系统的资源不足。
• 代码执行的顺序不合适。
• 资源分配不当。

2 产生死锁的必要条件

• 互斥条件：一个资源每次只能被一个进程使用。
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
• 不剥夺条件：进程已获得的资源，在未使用完之前，不能强行剥夺。
• 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

3 减少死锁发生

• 按同一顺序访问对象。
• 避免事务中的用户交互。
• 保持事务简短并在一个批处理中。
• 使用低隔离级别。
• 使用绑定连接。

8. MVCC多版本并发控制

MVCC：Multi-Version Concurrency Control 多版本并发控制。
MVCC是一种并发控制的方法，一般在数据库管理系统中实现对数据库高并发场景下的吞吐性能。

1 MVCC原理

• MVCC的实现，通过保存数据在某个时间点的快照来实现的。
• 在每行记录后面保存两个隐藏的列，一列保存了行的创建时间，另一列保存了行的过期时间（或删除时间）。
• 这里存储的时间并不是实际的时间值，而是系统版本号。
• 每开始一个新事物，系统版本号都会自动递增。
• 事务开始时刻的系统版本号会作为事务的版本号，用来与查询到的每行记录的版本号进行比较。

2 MVCC特征

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时copy出当前版本进行修改，各个事务之间互不干扰。
• 保存时比较版本号，如果成功（commit），则覆盖原纪录，失败则放弃copy（rollback）。

3 MVCC实现

• 在每一行数据中额外保存两个隐藏的列：（1）DATA_TRX_ID （2）DATA_ROLL_PTR。
• DATA_TRX_ID
  记录最近一次修改（insert / update）本行纪录的事务id，大小为6字节。
• DATA_ROLL_PTR
  指向该行回滚段（rollback segment）的undo log record（撤销日志记录）指针，大小为7字节。
  如果一行记录被更新，则undo log record 包含“重建该行记录被更新之前内容”所必须的信息。
  InnoDB便是通过这个指针找到之前版本的数据。
  若该行记录上存储所有的旧版本，在undo中都通过链表的形式组织。
• 如果表没有主键，则还会有一个隐藏的主键列 DB_ROW_ID。
• DB_ROW_ID
  行标识（隐藏单调自增ID），大小为6字节，如果表没有主键，InnoDB会自动生成一个隐藏主键。

例：事务1、事务2， DATA_TRX_ID,   DATA_ROLL_PTR， DB_ROW_ID。
事务1。
执行新增一条数据 insert操作。
此时DB_ROW_ID = 1， DATA_TRX_ID = 1（系统版本号）,  DATA_ROLL_PTR = NULL。

事务 2 执行update操作过程。
对DB_ROW_ID = 1这行记录加排它锁。
把该行copy前的值拷贝到undo log中。
修改该行的值，这时会产生一个新版本号，更新DATA_TRX_ID为修改记录的事务ID。
将DATA_ROLL_PTR指向刚刚copy到undo log 链中的旧版本记录，这样就能通过DATA_ROLL_PTR找到这条记录的历史版本；如果对同一行记录执行连续的UPDATE, undo log会组成一个链表，遍历这个链表可以看到这条记录的变迁。
记录redo log，包括undo log中的修改。
1
2
3
4
5
6
7
8
9
10
11

4 RR隔离级别下，MVCC具体的操作流程。

• SELECT ：InnoDB只查找版本早于当前事务版本的数据行；行的删除版本，要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
• INSERT ：InnoDB为插入的每一行保存当前系统版本号作为行版本号。
• DELETE ：InnoDB为删除的每一行保存当前系统版本号作为删除标识，标记为删除、而不是实际删除。
• UPDATE ：InnoDB会把原来的行复制一份到回滚段中，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为删除标识。

三、InnoDB引擎的底层实现方式：

InnoDB核心：（1）日志 （2）内存（缓存池（Buffer Pool）） (3) 磁盘（Datafile）
InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池。
后台线程主要负责刷新内存池中的数据，将已修改的数据刷新到磁盘。

当某个事务进行一次写操作时，InnoDB引擎将数据写入redo log后就会提交事务。
而非写入到磁盘（Datafile），之后InnoDB再异步地将新事务的数据异步地写入Datafile，真正存储起来。

redo log 和 undo log 和 bin log

用来恢复事务所对应的脏数据块的日志文件。

1. 前滚 与 回滚

前滚

• 未完全提交的事务，即该事务已经被执行commit命令了。
• 该事务所对应的脏数据块中只有一部分被写到磁盘上的数据文件中，一部分还在内存中。
• 若此时数据库实例崩溃，就需要用前滚来完成事务的完全提交。

回滚

未提交的事务，即该事务未被执行commit命令。

2. Redo log

重做日志，提供前滚操作。
Redo log 通常是物理日志，记录的是数据页的物理修改，用来恢复提交后的物理数据页。
恢复数据页、且只能恢复到最后一次提交的位置。

Redo log 包括两部分

内存中的日志缓冲（redo log buffer），该部分日志是易失性的。
磁盘上的重做日志文件（redo log file），该部分日志是持久的。

3. Undo log

• 回滚日志，提供回滚操作。
• Undo log用来回滚行记录到某个版本。
• undo log一般是逻辑日志，根据每行记录进行记录。

4. Bin log

二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中。
可以用来查看数据库的变更历史（具体的时间点所做的操作），数据库增量备份和恢复。

Show variables like %log_bin% ; 
1

InnoDB内存

1. InnoDB线程

Master Thread

最核心的线程，主要负责将缓存池中的数据异步刷新到磁盘，保证数据的一致性。

IO Thread

IO Thread 主要负责大量的异步IO来处理写IO请求。

Purge Thread

Purge Thread回收已经使用并分配的undo页，InnoDB支持多个Purge Thread，这样做可以加快undo页的回收。

Page Cleaner Thread

Page Cleaner Thread是将之前版本中脏页的刷新操作都放入单独的线程中来完成，减轻Master Thread的工作及对于用户查询线程的阻塞。

2. InnoDB内存模型

InnoDB引擎使用缓存池技术来提高数据库的整体性能。
InnoDB中缓存池页的大小默认为16KB。

计算机科学中著名的局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中。

内存 与 磁盘

• 主存和磁盘是以页为单位交换数据。
• 当程序要读取的数据不在主存中时，会触发一个缺页异常。
• 此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中。
• 然后异常返回，程序继续运行。
• 数据库、内存、磁盘在数据库中进行读取页的操作时，首先将从磁盘读到的页存放在缓存池中。
• 下一次读取相同的页时，首先判断页是不是在缓存池中。
• 若在、称该页在缓存池中被命中，直接读取该页。
• 否则、读取磁盘上的页。

对于数据库中页的修改操作，首先修改在缓存池中的页，然后再以一定的频率刷新到磁盘。

3. 缓存池（Buffer Pool）

为了更好的管理这些被缓存的页，InnoDB为每一个缓存页都创建了一些控制信息（控制块）。
这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址、锁信息、LSN信息等。
每个缓存页对应一个控制块，每个控制块占用的内存大小是相同的，它们都被放到Buffer Pool中，如下图。

4. Free List（空闲链表）

启动MySQL服务器时，需要对Buffer Pool进行初始化，将Buffer Pool划分成若干对控制块和缓存页。
随着程序的运行，会不断的将磁盘上的页缓存到Buffer Pool中，但如何管理Buffer Pool中空闲的缓存页呢？
使用Free List（空闲链表）来管理空闲的缓存页，如下图。

• Free List控制信息：包含链表的头结点地址、尾结点地址、以及当前链表中结点的数量。

• 每个Free List的结点中都记录了某个缓存页控制块的地址。

• 每个缓存页控制块都记录着对应的缓存页地址。

• 每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free List中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free List结点从Free List链表中删除。

5. Buffer Pool 清理机制

• 缓存命中率：假设一共访问了n次页，那么被访问的页已经在缓存中的次数除以n就是缓存命中率。
• InnoDB Buffer Pool采用经典的LRU算法进行页面淘汰，以提高缓存命中率。
• LRU（Least Recently Used）：最近最少使用，用来管理缓存池中页的可用性。
• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成结点塞到链表的头部。
• 如果该页在Buffer Pool中，则直接把该页对应的LRU链表结点移动到链表的头部。

缺点

• 若遇一次全表扫描就把热数据给冲完了，就会导致Buffer Pool污染问题，严重的降低了缓存命中率。
• Buffer Pool中的所有数据页都被换了一次血，其它查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作。

midpoint insertion stategy

InnoDB存储引擎对传统的LRU算法做了一些优化。
在InnoDB中加入了midpoint，新读到的页，虽然是最新访问的页。
但并不直接插入到LRU列表的首部，而是插入到LRU列表的midpoint位置。
默认配置插入到列表长度的 5/8 处，midpoint由参数innodb_old_blocks_pct控制。

Midpoint之前的列表称之为new列表，之后的列表称之为old列表。
可以简单的将new列表中的页理解为最活跃的热点数据。

InnoDB存储引擎还引入了 innodb_old_blocks_time 来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端，可以通过设置该参数保证热点数据不轻易被刷出。
1
2
3
4
5
6
7
8
9

6. FLUSH链表（Flush List）

FLUSH链表用来管理将页刷新回磁盘，缓存池中通过FLUSH 链表存储需要被刷新到磁盘上的页（脏页）。
这里的脏页指的是此页被加载进Buffer Pool后第一次修改后的页。
只有第一次修改时才需要加入FLUSH链表（第二次修改时已经存在了）。

7. LRU List, Free List, Flush List 三者关系

8. Checkpoint 技术

1 缩短数据库恢复时间。

redo log中记录了Checkpoint的位置。
这个点之前的页已经被刷新回磁盘，只需要对Checkpoint之后的redo log进行恢复。

2 缓存池不够用时，刷新脏页。

根据LRU算法，溢出最近最少使用页。
如果页为脏页，强制执行Checkpoint，将脏页刷新回磁盘。

3 Redo log不可用时，刷新脏页。

由于redo log是循环使用的，这部分对应的数据还未刷新到磁盘。
数据库恢复时，如果不需要这部分日志即可被覆盖。
如果需要，必须强制执行Checkpoint，将缓存池中的页至少刷新到当前重做日志的位置。

InnoDB存储引擎内部，有两种Checkpoint

Sharp Checkpoint（默认， innodb_fast_shutdown=1）。

Sharp Checkpoint发生在数据库关闭时，将所有的脏页都刷新回磁盘。
缺点：不适用于数据库运行时的刷新。

Fuzzy Checkpoint。

Fuzzy Checkpoint适用于数据库运行时刷新脏页，只刷新一部分脏页。
MasterThread Checkpoint
异步刷新，每秒或每10秒从缓存池脏页列表刷新一定比例的页回磁盘。

FLUSH_LRU_LIST Checkpoint
若Buffer Pool中没有足够的空间时，根据LRU算法、溢出LRU列表尾端的页。
如果这些页有脏页，需要进行Checkpoint（Page Cleaner Thread线程就是做这个事的）。
InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。
Innodb_lru_scan_dept ：控制LRU列表中可用页的数量，默认1024。

Asnc / Sync Flush Checkpoint
指重做日志不可用时，需要强制刷新页回磁盘。
此时的页是脏页列表（FLUSH LIST）中选取的。
1
2
3
4
5
6
7
8
9
10
11
12
13

LSN

事务日志中每条记录的编号。
InnoDB存储引擎，通过LSN（Log Sequence Number）来标记版本，LSN是8字节的数字。

• redo_lsn ：写入日志的 LSN。
• Checkpoint_lsn ：刷新回磁盘的最新页 LSN。

Checkpoint_age = redo_lsn – checkpoint_lsn。
Async_water_mark = 75% * total_redo_file_size。
Sync_water_mark = 90% * total_redo_file_size。
1
2
3

Dirty Page too much Checkpoint

即脏页太多，强制checkpoint，保证缓存池中有足够可用的页。
参数设置：innodb_max_dirty_pages_pct = 75。
表示：当缓存池中脏页的数量占75%时，强制checkpoint。1.0x之后默认75。

InnoDB关键特性

1. 插入缓存（Insert Buffer）

Insert Buffer的设计，对于非聚集索引的插入和更新操作，不是每次都直接插入到索引页中。
而是先判断插入非聚集索引页是否在缓存池中。
若存在则直接插入，若不存在则先放入一个Insert Buffer对象中。

数据库这个非聚集的索引并没有插入到叶子结点（因为B+树只有叶子结点才存储数据），而是存放在另一个位置。
然后再以一定的频率和情况进行Insert Buffer和辅助索引页子结点的merge（合并）操作。
这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。
1
2
3
4
5
6
7

使用Insert Buffer需要满足两个条件

• 索引是辅助索引。
• 索引不是唯一的。

2. 两次写操作

假设有这样一个场景，当数据库正在从内存向磁盘写一个数据页时，数据库宕机。

从而导致这个页只写了部分数据，这就是部分写失效，它会导致数据丢失。

这时是无法通过重做日志（redo log）恢复的。
因为重做日志记录是对页的物理修改，如果页本身已损坏，重做日志也无能为力。

如何解决以上问题 ？
为了解决以上问题，可以使用两次写操作。
因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机。
在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。

两次写原理。

• 1. 当刷新缓存池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓存区。
• 2. 接着从两次写缓存区分两次写入磁盘共享表空间中，每次写入1MB。
• 3. 待第2步完成后，再将两次写缓存区写入数据文件。

两次写需要额外添加两个部分。

• 1. 内存中的两次写缓存（doublewrite buffer），大小为2MB。
• 2. 磁盘上共享表空间中连续的128页，大小也为2MB。

InnoDB默认开启两次写功能，可以通过skip_innodb_doublewrite禁用两次写功能。