MySQL

发表于 更新于 分类于

MySQL

设计理论

术语

关系模型是一种基于表的数据模型，以下为关系学生信息，该表有很多不足之处，本文研究内容就是如何改进它

image

下面是一些重要术语：

  • ​属性（attribute） ：列的名字，上图有学号、姓名、班级、兴趣爱好、班主任、课程、授课主任、分数。
  • ​依赖（relation） ：列属性间存在的某种联系。
  • ​元组（tuple） ：每一个行，如第二行 （1301，小明，13班，篮球，王老师，英语，赵英，70） 就是一个元组
  • ​表（table） ：由多个属性，以及众多元组所表示的各个实例组成。
  • ​模式（schema） ：这里我们指逻辑结构，如 学生信息（学号，姓名，班级，兴趣爱好，班主任，课程，授课主任，分数） 的笼统表述。
  • ​域（domain） ：数据类型，如string、integer等，上图中每一个属性都有它的数据类型（即域）。
  • ​键（key） ​：由关系的一个或多个属性组成，任意两个键相同的元组，所有属性都相同。需要保证表示键的属性最少。一个关系可以存在好几种键，工程中一般从这些候选键中选出一个作为​主键（primary key） 。
  • ​候选键（candidate key） ：由关系的一个或多个属性组成，候选键都具备键的特征，都有资格成为主键。
  • ​超键（super key） ​：包含键的属性集合，无需保证属性集的最小化。每个键也是超键。可以认为是​键的超集。
  • ​外键（foreign key） ：如果某一个关系A中的一个（组）属性是另一个关系B的键，则该（组）属性在A中称为外键。
  • ​主属性（prime attribute） ：所有候选键所包含的属性都是主属性。
  • ​投影（projection） ：选取特定的列，如将关系学生信息投影为学号、姓名即得到上表中仅包含学号、姓名的列
  • ​选择（selection） ​：按照一定条件选取特定元组，如选择上表中分数>80的元组。
  • ​笛卡儿积（交叉连接Cross join） ：第一个关系每一行分别与第二个关系的每一行组合。
  • ​自然连接（natural join） ：第一个关系中每一行与第二个关系的每一行进行匹配，如果得到有交叉部分则合并，若无交叉部分则舍弃。
  • ​连接（theta join） ：即加上约束条件的笛卡儿积，先得到笛卡儿积，然后根据约束条件删除不满足的元组。
  • ​外连接（outer join） ：执行自然连接后，将舍弃的部分也加入，并且匹配失败处的属性用NULL代替。
  • 除法运算（division） ：关系R除以关系S的结果为T，则T包含所有在R但不在S中的属性，且T的元组与S的元组的所有组合在R中。

函数依赖

通过函数依赖关系，来帮助你确定表中的合理主外键等；这里只是简介，有这么个概念就可以了，因为大多数情况你不用那些所谓的推倒关系，你也是可以凭借直觉设计出来的。

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，… ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A’-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。

对于 A->B，B->C，则 A->C 是一个传递函数依赖。

异常

介绍 不符合范式的关系，会产生很多异常，为了引出范式的内容。

以下的学生课程关系的函数依赖为 Sno, Cname -> Sname, Sdept, Mname, Grade，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

Sno Sname
 Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

不符合范式的关系，会产生很多异常，主要有以下四种异常:

  • 冗余数据: 例如 学生-2 出现了两次。
  • 修改异常: 修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
  • 删除异常: 删除一个信息，那么也会丢失其它信息。例如删除了 课程-1​ 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
  • 插入异常: 例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

image

第一范式（1NF）

属性不可分。

第二范式（2NF）

每个非主属性完全函数依赖于键码。

可以通过分解来满足。

分解前

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 90
2 学生-2 学院-2 院长-2 课程-2 80
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖:

  • Sno -> Sname, Sdept
  • Sdept -> Mname
  • Sno, Cname -> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

  • 关系-1

    Sno Sname Sdept Mname
    1 学生-1 学院-1 院长-1
    2 学生-2 学院-2 院长-2
    3 学生-3 学院-2 院长-2

    有以下函数依赖:

    • Sno -> Sname, Sdept
    • Sdept -> Mname

  • 关系-2

    Sno Cname Grade
    1 课程-1 90
    2 课程-2 80
    2 课程-1 100
    3 课程-2 95

    有以下函数依赖:

    • Sno, Cname -> Grade

第三范式（3NF）

非主属性不传递函数依赖于键码。

上面的 关系-1 中存在以下传递函数依赖:

  • Sno -> Sdept -> Mname

可以进行以下分解:

关系-1-1

Sno Sname Sdept
1 学生-1 学院-1
2 学生-2 学院-2
3 学生-3 学院-2

关系-1-2

Sdept Mname
学院-1 院长-1
学院-2 院长-2

基础语法

模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。

主键的值不允许修改，也不允许复用(不能使用已经删除的主键值赋给新数据行的主键)。

SQL(Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。

SQL 支持以下三种注释:

1
2
3
4
# 注释
SELECT * FROM mytable; -- 注释
/* 注释1
   注释2 */

数据库创建与使用:

1
2
CREATE DATABASE test;
USE test;

创建表

1
2
3
4
5
6
CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

修改表

添加列

1
ALTER TABLE mytable ADD col CHAR(20);

修改列和属性

1
2
---ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件 默认值
ALTER TABLE mytable CHANGE col col1 CHAR(32) NOT NULL DEFAULT '123';

删除列

1
ALTER TABLE mytable DROP COLUMN col;

删除表

1
DROP TABLE mytable;

插入

普通插入

1
INSERT INTO mytable(col1, col2) VALUES(val1, val2);

插入检索出来的数据

1
INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;

将一个表的内容插入到一个新表

1
CREATE TABLE newtable AS SELECT * FROM mytable;

更新

1
UPDATE mytable SET col = val WHERE id = 1;

删除

1
DELETE FROM mytable WHERE id = 1;

TRUNCATE TABLE 可以清空表，也就是删除所有行。

1
TRUNCATE TABLE mytable;

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

查询

DISTINCT

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

1
SELECT DISTINCT col1, col2 FROM mytable;

LIMIT

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

返回前 5 行:

1
2
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;

返回第 3 ~ 5 行:

1
SELECT * FROM mytable LIMIT 2, 3;

排序

  • ASC : 升序(默认)
  • DESC : 降序

可以按多个列进行排序，并且为每个列指定不同的排序方式:

1
SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

1
SELECT * FROM mytable WHERE col IS NULL;

下表显示了 WHERE 子句可用的操作符

操作符 说明
= 等于
< 小于
> 大于
<> != 不等于
<= !> 小于等于
>= !< 大于等于
BETWEEN 在两个值之间
IS NULL 为 NULL 值

应该注意到，NULL 与 0、空字符串都不同。

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT 操作符用于否定一个条件。

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

  • % 匹配 >= 0 个任意字符；
  • _ 匹配 == 1 个任意字符；
  • [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

1
SELECT * FROM mytable WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

1
SELECT col1 * col2 AS alias FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

1
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col FROM mytable;

函数

各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。

汇总

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

1
SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

文本处理

函数 说明
LEFT() 左边的字符
RIGHT() 右边的字符
LOWER() 转换为小写字符
UPPER() 转换为大写字符
LTRIM() 去除左边的空格
RTRIM() 去除右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

1
SELECT * FROM mytable WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和时间处理

  • 日期格式: YYYY-MM-DD
  • 时间格式: HH:MM:SS
函 数 说 明
AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分 
1
mysql> SELECT NOW();
1
2018-4-14 20:25:11

数值处理

函数 说明
SIN() 正弦
COS() 余弦
TAN() 正切
ABS() 绝对值
SQRT() 平方根
MOD() 余数
EXP() 指数
PI() 圆周率
RAND() 随机数

分组

分组就是把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

1
SELECT col, COUNT(*) AS num FROM mytable GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

1
SELECT col, COUNT(*) AS num FROM mytable GROUP BY col ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

1
SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2 GROUP BY col HAVING num >= 2;

分组规定:

  • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
  • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
  • NULL 的行会单独分为一组；
  • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件:

1
SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次:

1
2
3
4
5
6
SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

1
2
3
SELECT A.value, B.value 
FROM tablea AS A 
INNER JOIN tableb AS B ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

1
2
SELECT A.value, B.value 
FROM tablea AS A, tableb AS B WHERE A.key = B.key;

在没有条件语句的情况下返回笛卡尔积。

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本

1
2
3
4
5
6
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本

1
2
3
4
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别: 内连接提供连接的列，而自然连接自动连接所有同名列。

1
2
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

1
2
3
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

customers 表:

cust_id cust_name
1 a
2 b
3 c

orders 表:

order_id cust_id
1 1
2 1
3 3
4 3

结果:

cust_id cust_name order_id
1 a 1
1 a 2
3 c 3
3 c 4
2 b Null

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

UNION 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

1
2
3
SELECT col FROM mytable WHERE col = 1
UNION
SELECT col FROM mytable WHERE col =2;

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处:

  • 简化复杂的 SQL 操作，比如复杂的连接；
  • 只使用实际表的一部分数据；
  • 通过只给用户访问视图的权限，保证数据的安全性；
  • 更改数据格式和表示。
1
2
3
CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable WHERE col5 = val;

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处:

  • 代码封装，保证了一定的安全性；
  • 代码复用；
  • 由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

1
2
3
4
5
6
7
8
9
10
11
12
delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;
1
2
call myprocedure(@ret);
select @ret;

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤:

  1. 声明游标，这个过程没有实际检索出数据；
  2. 打开游标；
  3. 取出数据；
  4. 关闭游标；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

触发器

触发器会在某个表执行以下语句时而自动执行: DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

1
2
3
4
CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

事务管理

基本术语:

  • 事务(transaction)指一组 SQL 语句；
  • 回退(rollback)指撤销指定 SQL 语句的过程；
  • 提交(commit)指将未存储的 SQL 语句结果写入数据库表；
  • 保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，你可以对它发布回退(与回退整个事务处理不同)。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

1
2
3
4
5
6
7
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

字符集

基本术语:

  • 字符集为字母和符号的集合；
  • 编码为某个字符集成员的内部表示；
  • 校对字符指定如何比较，主要用于排序和分组。

除了给表指定字符集和校对外，也可以给列指定:

1
2
3
CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对:

1
SELECT * FROM mytable ORDER BY col COLLATE latin1_general_ci;

权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

1
2
USE mysql;
SELECT user FROM user;

创建账户

新创建的账户没有任何权限。

1
CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名

1
RENAME myuser TO newuser;

删除账户

1
DROP USER myuser;

查看权限

1
SHOW GRANTS FOR myuser;

授予权限

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

1
GRANT SELECT, INSERT ON mydatabase.* TO myuser;

删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限:

  • 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
  • 整个数据库，使用 ON database.*；
  • 特定的表，使用 ON database.table；
  • 特定的列；
  • 特定的存储过程。
1
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

更改密码

必须使用 Password() 函数

1
SET PASSWROD FOR myuser = Password('new_password');

核心基础

ACID

ACID是衡量事务的四个特性：

  • 原子性（Atomicity）
  • 一致性（Consistency）
  • 隔离性（Isolation）
  • 持久性（Durability）

A (Atomicity) - 原子性

定义

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

原理

Undo Log InnoDB引擎提供

Undo Log属于逻辑日志，它记录的是sql执行相关的信息，提供回滚操作和多版本并发控制（MVCC）。

当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

C (Consistency) - 一致性

定义

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

实现

可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

实现一致性的措施包括：

  • 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
  • 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
  • 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

I (Isolation) - 隔离性

定义

隔离性是指事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明)，那么隔离性的探讨，主要可以分为两个方面：

  • (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
  • (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

锁机制

行锁与表锁

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

脏读，不可重复读和幻读

脏读

当前事务A中可以读到其他事务B未提交的数据（脏数据），这种现象是脏读。

不可重复读

在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。

脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

幻读

在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。

不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

事务隔离级别

SQL标准中定义了四种隔离级别（默认是RR），并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

image

InnoDB 实现的 Repeatable Read 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

快照读（Snapshot Read）

由 MVCC 机制来保证不出现幻读

对于一致性非锁定读（Consistent Nonlocking Reads）的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见。

在 InnoDB 存储引擎中，多版本控制 (Multi-Version Concurrency Control) 就是对非锁定读​的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (Snapshot Read)。

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select … lock in share mode ,select … for update）则会使用 一致性非锁定读（MVCC），并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读。

当前读（Current Read）

使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁

如果执行的是下列语句，就是锁定读（Locking Reads）

  • select … lock in share mode
  • select … for update
  • insert、update、delete 操作

在锁定读​下，读取的是数据的最新版本，这种读也被称为当前读（Current Read）。锁定读会对读取到的记录加锁：

  • select … lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞
  • select … for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁

在一致性非锁定读下，即使读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。上面说了，在 Repeatable Read 下 MVCC 防止了部分幻读，这边的“部分”是指在一致性非锁定读情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。但是，如果是当前读，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。所以，InnoDB 在实现Repeatable Read 时，如果执行的是当前读，则会对读取的记录使用 Next-key Lock ，来防止其它事务在间隙间插入数据。

MVCC

RR解决脏读、不可重复读、部分幻读的问题，使用的是MVCC：MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。

image

ReadView 机制

ReadView 机制就是用来判断当前事务能够看见哪些版本的，一个 ReadView 主要包含如下几个部分：

  • m_ids：生成 ReadView 时有哪些事务在执行但是还没提交的（称为 “活跃事务”），这些活跃事务的 id 就存在这个字段里
  • min_trx_id：m_ids 里最小的值
  • max_trx_id：生成 ReadView 时 InnoDB 将分配给下一个事务的 ID 的值（事务 ID 是递增分配的，越后面申请的事务 ID 越大）
  • creator_trx_id：当前创建 ReadView 事务的 ID

事务通过用当前事务（或语句，取决于隔离级别）的ReadView来判断一个事务id的操作是否对当前事务可见。判断可见性的伪代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
IsVisible(trx_id) {
    if (trx_id == creator_trx_id)    // 当前事务
        return true;
    else if (trx_id <= min_trx_id)   // ReadView创建时，事务已提交或者就是当前最小事务
        return true;
    else if (trx_id >= max_trx_id)   // ReadView创建时，事务还未被创建
        return false;
    else if (min_trx_id < trx_id < max_trx_id)  // ReadView创建时，事务正在执行，但未提交
        return false
    else                             // ReadView创建时，事务已提交
        return true;
}

RC 和 RR 隔离级别下 MVCC 的差异

在事务隔离级别 RC 和 RR （InnoDB 存储引擎的默认事务隔离级别）下，InnoDB 存储引擎使用 MVCC（非锁定一致性读），但它们生成 Read View 的时机却不同

  • 在 RC 隔离级别下每次 select 查询前都生成一个Read View ，所以导致不可重复读
  • 在 RR 隔离级别下只在第一次 select 查询前生成一个Read View，实现了可重复读和防止部分幻读

MVCC + Next-Key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题

  • 执行普通 select，此时会以 MVCC 快照读的方式读取数据

    在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的“幻读”。

  • 执行 select…for update/lock in share mode、insert、update、delete 等当前读

    在当前读下的情况下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读。InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读。

D (Durability) - 持久性

定义

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

原理

Redo Log InnoDB引擎提供

Redo Log属于物理日志，它记录的是数据库中每个页的修改，用来确保事务的持久性。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

MySQL Log

Redo Log 和 Undo Log

Redo Log

在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中(redo:ib_logfileN文件，undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。

也就是说，从redo log buffer写日志到磁盘的redo log file中，过程如下：

image

在此处需要注意一点，一般所说的log file并不是磁盘上的物理日志文件，而是操作系统缓存中的log file，官方手册上的意思也是如此(例如：With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second)。但说实话，这不太好理解，既然都称为file了，应该已经属于物理文件了。所以在本文后续内容中都以os buffer或者file system buffer来表示官方手册中所说的Log file，然后log file则表示磁盘上的物理日志文件，即log file on disk。另外，之所以要经过一层os buffer，是因为open日志文件的时候，open没有使用O_DIRECT标志位，该标志位意味着绕过操作系统层的os buffer，IO直写到底层存储设备。不使用该标志位意味着将日志进行缓冲，缓冲到了一定容量，或者显式fsync()才会将缓冲中的刷到存储设备。使用该标志位意味着每次都要发起系统调用。比如写abcde，不使用o_direct将只发起一次系统调用，使用o_object将发起5次系统调用。

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

  • 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
  • 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
  • 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

image

注意：有一个变量 innodb_flush_log_at_timeout 的值为1秒，该变量表示的是刷日志的频率，很多人误以为是控制 innodb_flush_log_at_trx_commit 值为0和2时的1秒频率，实际上并非如此。测试时将频率设置为5和设置为1，当 innodb_flush_log_at_trx_commit 设置为0和2的时候性能基本都是不变的。关于这个频率是控制什么的，在后面的”刷日志到磁盘的规则”中会说。

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

  • 如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
  • 总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

Undo Log

Undo Log有两个作用：提供回滚操作和多版本并发控制（MVCC） 。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段（segment）的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

Redo Log 和 Bin Log

Bin Log

Bin Log属于二进制日志文件，它记录了数据库所有执行的更新语句，主要用于数据恢复和数据复制。 MySQL Server层提供

  • 数据恢复：如果MySQL意外停止，可以通过该日志进行恢复，备份
  • 数据复制：master把它的二进制日志传递给slaves来达到master-slave数据的一致性

区别

redo log不是二进制日志。虽然二进制日志中也记录了innodb表的很多操作，也能实现重做的功能，但是它们之间有很大区别。

  1. 二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录。具体的见后文group commit小结。
  2. 二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。
  3. 二进制日志只在每次事务提交的时候一次性写入缓存中的日志”文件”(对于非事务表的操作，则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。
  4. 因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。例如事务T1，可能在redo log中记录了 T1-1,T1-2,T1-3，T1* 共4个操作，其中 T1* 表示最后提交时的日志记录，所以对应的数据页最终状态是 T1* 对应的操作结果。而且redo log是并发写入的，不同事务之间的不同版本的记录会穿插写入到redo log文件中，例如可能redo log的记录方式如下： T1-1,T1-2,T2-1,T2-2,T2 ,T1-3,T1 。
  5. 事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多。例如插入一行记录一次，删除该行又记录一次。

MySQL锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）；InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁（table-level locking），但默认情况下是采用行级锁。

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

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM表锁

MySQL的表级锁有两种模式：

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

表共享读锁（Table Read Lock）

session1 对 table1 加读锁后，

  • session1 和 session2 都能查询 table1 数据，
  • session1 不能新增或修改 table1 数据，会提示Table ‘table1’ was locked with a READ lock and can’t be updated；session2 新增或修改 table1 数据时，会等待获得锁，才能继续操作，
  • session1 不能查询 table2 的数据，会提示Table ‘table2’ was not locked with LOCK TABLES；session2 可以查询 table2 的数据。
session1 session2
获得表的read锁定 lock table mylock read;
当前session可以查询该表记录：select * from mylock; 当前session可以查询该表记录：select * from mylock;
当前session不能查询没有锁定的表select * from personTable ‘person’ was not locked with LOCK TABLES 当前session可以查询或者更新未锁定的表select * from mylockinsert into person values(1,’zhangsan’);
当前session插入或者更新表会提示错误insert into mylock values(6,’f’)Table ‘mylock’ was locked with a READ lock and can’t be updatedupdate mylock set name=’aa’ where id = 1;Table ‘mylock’ was locked with a READ lock and can’t be updated 当前session插入数据会等待获得锁insert into mylock values(6,’f’);
释放锁unlock tables; 获得锁，更新成功

表独占写锁（Table Write Lock）

session1 对 table1 加写锁后，

  • session1 可以查询 table1 数据；session2不可以，会等待获取锁，
  • session1 可以新增或修改 table1数据；session2 不可以，会等待获取锁，才能继续操作，
  • session1 不能查询 table2 的数据，会提示Table ‘table2’ was not locked with LOCK TABLES；session2 可以查询 table2 的数据。
session1 session2
获取表的write锁定lock table mylock write;
当前session对表的查询，插入，更新操作都可以执行select * from mylock;insert into mylock values(5,’e’); 当前session对表的查询，插入，更新会被阻塞select * from mylock；
释放锁：unlock tables； 当前session能够立刻执行，并返回对应结果

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果。

MyISAM的并发插入问题

MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行

session1 session2
获取表的read local锁定lock table mylock read local
当前session不能对表进行更新或者插入操作insert into mylock values(6,’f’)Table ‘mylock’ was locked with a READ lock and can’t be updatedupdate mylock set name=’aa’ where id = 1;Table ‘mylock’ was locked with a READ lock and can’t be updated 其他session可以查询该表的记录select* from mylock
当前session不能查询没有锁定的表select * from personTable ‘person’ was not locked with LOCK TABLES 其他session可以进行插入操作，但是更新会阻塞update mylock set name = ‘aa’ where id = 1;
当前session不能访问其他session插入的记录；
释放锁资源：unlock tables 当前session获取锁，更新操作完成
当前session可以查看其他session插入的记录

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

1
2
3
4
5
6
7
8
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+
--如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

INNODB锁

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

  • 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
  • 一致性（Consistency）：在事务开始和完成时，数据都必须保持一致状态。
  • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。
  • 持久性（Durability）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

  • 脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
  • 不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
  • 幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。
数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

隔离级别 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed √ √
repeatable read √
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

1
2
3
4
5
6
7
8
9
10
11
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

INNODB的行锁模式及加锁方法

  • 共享锁（S）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
  • 排他锁（X）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）
1
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
  • 排他锁（X）
1
SELECT * FROM table_name WHERE ... FOR UPDATE

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

  • 意向共享锁（IS） ：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁（IX） ：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

INNODB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

  1. 在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

    1
    2
    create table tab_no_index(id int,name varchar(10)) engine=innodb;
    insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
    session1 session2
    set autocommit=0;select * from tab_no_index where id = 1; set autocommit=0;select * from tab_no_index where id =2;
    select * from tab_no_index where id = 1 for update; -
    - select * from tab_no_index where id = 2 for update;

    session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

  2. 创建带索引的表进行条件查询，innodb使用的是行锁

    1
    2
    3
    create table tab_with_index(id int,name varchar(10)) engine=innodb;
    alter table tab_with_index add index id(id);
    insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
    session1 session2
    set autocommit=0;select * from tab_with_index where id = 1; set autocommit=0;select * from tab_with_index where id =2;
    select * from tab_with_index where id = 1 for update; -
    - select * from tab_with_index where id = 2 for update;

  3. 由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据

    1
    insert into tab_with_index  values(1,'4');
    session1 session2
    set autocommit=0; set autocommit=0;
    select * from tab_with_index where id = 1 and name=’1’ for update; -
    - select * from tab_with_index where id = 1 and name=’4’ for update;虽然session2访问的是和session1不同的记录，但是因为使用了相同的索引，所以需要等待锁

总结

对于MyISAM的表锁，主要讨论了以下几点：

  • 共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
  • 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
  • MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
  • 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于INNODB表，本文主要讨论了以下几项内容：

  • InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
  • 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解INNODB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

  • 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
  • 选择合理的事务大小，小事务发生锁冲突的几率也更小；
  • 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
  • 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
  • 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
  • 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

存储引擎

image

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

​不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

索引结构

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现

基础知识

红黑树

image

B树

image

B+树

image

索引结构

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制:

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引(R-Tree)，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据

索引类型

主键索引

一种特殊的唯一索引，不允许有空值（NULL）。每个表只能有一个主键索引。

1
2
3
4
5
6
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    ...
);

ALTER TABLE table_name ADD PRIMARY KEY (column);

唯一索引

索引列的值必须唯一，但允许有空值（多个 NULL 值视为不同）。确保数据唯一性。

1
2
CREATE UNIQUE INDEX index_name ON table_name (column);
ALTER TABLE table_name ADD UNIQUE index_name (column);

普通索引

最基本的索引类型，没有唯一性限制，允许重复值和空值。主要用于加速查询。

1
2
CREATE INDEX index_name ON table_name (column);
ALTER TABLE table_name ADD INDEX index_name (column);

全文索引

用于全文搜索，支持对文本内容（如 CHAR、VARCHAR、TEXT 类型）进行单词或短语的模糊匹配。
在 MyISAM 和 InnoDB（MySQL 5.6 以上）引擎中可用。

1
2
CREATE FULLTEXT INDEX index_name ON table_name (column);
ALTER TABLE table_name ADD FULLTEXT index_name (column);

组合索引

在多个列上建立的索引，查询条件需遵循最左前缀原则（即必须使用索引的最左侧列才能有效利用索引）。

1
2
CREATE INDEX index_name ON table_name (column1, column2, ...);
ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引:

1
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

组合索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

1
SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

image

索引列的顺序

让选择性最强的索引列放在前面，索引的选择性是指: 不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

1
2
3
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*) FROM payment;
1
2
3
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

对于前缀长度的选取需要根据索引选择性来确定。

覆盖索引

索引包含所有需要查询的字段的值。

具有以下优点:

  • 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
  • 一些存储引擎(例如 MyISAM)在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用(通常比较费时)。
  • 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

  • 大大减少了服务器需要扫描的数据行数。
  • 帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。
  • 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)。

索引的使用场景

  • 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
  • 对于中到大型的表，索引就非常有效。
  • 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

性能优化

mysql执行计划

在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。

可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

执行计划中包含的信息

Column Meaning
id The SELECT identifier
select_type The SELECT type
table The table for the output row
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key The index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
extra Additional information

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

id号分为三种情况：

  1. 如果id相同，那么执行顺序从上到下
1
explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;
  1. 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
1
explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');
  1. id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
1
explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

​select_type Value Meaning
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) 
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
26
27
28
--sample:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:从union表获取结果的select
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在select或者where列表中包含子查询
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:subquery的子查询要受到外部表查询的影响
explain select * from emp e where e.deptno in (select distinct deptno from dept);

--DERIVED: from子句中出现的子查询，也叫做派生类，
explain select staname,ename supname from (select ename staname,mgr from emp) t join emp on t.mgr=emp.empno ;

--UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
 explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);
 
--uncacheable union:表示union的查询结果不能被缓存：sql语句未验证

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

  1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
  2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
  3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般情况下，得保证查询至少达到range级别，最好能达到ref

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
26
27
28
29
30
31
--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：利用索引来关联子查询，不再扫描全表
explain select * from emp where emp.job in (select job from t_job);

--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引
 explain select * from emp e where e.deptno in (select distinct deptno from dept);
 
--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
explain select * from emp e where  e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找
 create index idx_3 on emp(deptno);
 explain select * from emp e,dept d where e.deptno =d.deptno;

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp,emp2 where emp.empno = emp2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

1
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

1
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

1
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

1
explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

1
explain select * from emp;

extra

包含额外的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:使用where进行条件过滤
explain select * from t_user where id = 1;

--using join buffer:使用连接缓存，情况没有模拟出来

--impossible where：where语句的结果总是false
explain select * from emp where empno = 7469;

优化数据访问

减少请求的数据量

  • 只返回必要的列: 最好不要使用 SELECT * 语句。
  • 只返回必要的行: 使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据: 使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

1
DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
1
2
3
4
5
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有:

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  • 减少锁竞争；
  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
  • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
1
2
3
4
SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
1
2
3
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

分库分表

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

将数据库数据按照量级划分，切分成相同结构的几张表。适用于单表数据量很大的场景。例如：1 ~ 1000000放在表user1, 1000000 ~ 2000000放在表user2。

image

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

将数据库表按模块或业务划分，分别部署在不同的服务器，并将请求分发到对应的服务器上即可，适用于分布式场景。

image

Sharding 策略

  • 哈希取模: hash(key) % NUM_DB
  • 范围: 可以是 ID 范围也可以是时间范围
  • 映射表: 使用单独的一个数据库来存储映射关系

Sharding 存在的问题及解决方案

事务问题

使用分布式事务来解决，比如 XA 接口。

链接

可以将原来的 JOIN 分解成多个单表查询，然后在用户程序中进行 JOIN。

ID 唯一性

  • 使用全局唯一 ID: GUID
  • 为每个分片指定一个 ID 范围
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

分区表的应用场景

分区表的含义更类似与水平切分这一概念。

  1. 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  2. 分区表的数据更容易维护
  3. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
  4. 可以使用分区表来避免某些特殊的瓶颈
  5. 可以备份和恢复独立的分区

分区表的限制

  1. 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
  2. 在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区
  3. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
  4. 分区表无法使用外键约束

分区表的原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

  • select查询

    当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

  • insert操作

    当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

  • delete操作

    当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

  • update操作

    当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

分区表的类型

  1. 范围分区 [RANGE PARTITIONING]

    根据列值在给定范围内将行分配给分区

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
    )
    PARTITION BY RANGE (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html

  2. 列表分区 [LIST PARTITIONING]

    类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )
    PARTITION BY LIST(store_id) (
        PARTITION pNorth VALUES IN (3,5,6,9,17),
        PARTITION pEast VALUES IN (1,2,10,11,19,20),
        PARTITION pWest VALUES IN (4,12,13,14,18),
        PARTITION pCentral VALUES IN (7,8,15,16)
    );

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-list.html

  3. 列分区 [COLUMNS PARTITIONING]

    • RANGE COLUMNS PARTITIONING
    • LIST COLUMNS PARTITIONING

    mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-columns.html

  4. Hash分区 [HASH PARTITIONING]

    基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
    )
    PARTITION BY HASH( YEAR(hired) )
    PARTITIONS 4;

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-hash.html

  5. Key分区 [KEY PARTITIONING]

    类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

    1
    2
    3
    4
    5
    CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
    )
    PARTITION BY KEY(s1)
    PARTITIONS 10;

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html

  6. 子分区 [SUBPARTITIONING]

    在分区的基础之上，再进行分区后存储

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

    官网资料：
    https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html

如何使用分区表

如果需要从非常大的表中查询出某一段时间的记录，而这张表中包含很多年的历史数据，数据是按照时间排序的，此时应该如何查询数据呢？  
因为数据量巨大，肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引，即使使用索引，会发现会产生大量的碎片，还会产生大量的随机IO，但是当数据量超大的时候，索引也就无法起作用了，此时可以考虑使用分区来进行解决

在使用分区表的时候需要注意的问题

  1. null值会使分区过滤无效
  2. 分区列和索引列不匹配，会导致查询无法进行分区过滤
  3. 选择分区的成本可能很高
  4. 打开并锁住所有底层表的成本可能很高
  5. 维护分区的成本可能很高

分库、分表、分区、读写分离、主从复制

分库

使用多台数据库，eg:订单表使用A库，库存表使用B库

分表

拆分表结构，eg:将订单表结构拆分成两个不同的表（太复杂）

分区

拆分表数据，eg:将订单表数据拆分成两张表，A表（1 ~ 10000）B表（10001 ~ 20000）

读写分离

  • 一主一从

  • 一主多从

    提高读性能

  • 多主一从

    将多个mysql数据库备份到一台存储性能比较好的服务器上

  • 双主复制

    互做主从复制，每个服务器既是master，又是另外一台服务器的slave。任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

  • 级联复制

    部分slave的数据同步不连接主节点，而是连接从节点。不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

主从复制

主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点。

当从节点连接主节点时，主节点会创建一个log dump线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁。一旦该事件被线程读取完之后，该锁会被释放，即使在该事件完全发送到从库之前，该锁也会被释放。

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点log dump进程发来的更新之后，保存在本地relay-log中。
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。

image

参考资料

MySQL日志之redo log和undo log的知识点有哪些

深入学习MySQL事务：ACID特性的实现原理

详细分析MySQL事务日志

关系型数据库设计理论

MySQL分库分表与水平分割取模案例

‍