SQL & DBMS & Mysql & Redis 相关知识

0_rdg1MHbby7BWV2_d.jpg

SQL：连接查询与分组查询、连接查询与子查询比较、drop与delete、truncate的比较。 DBMS：ACID、隔离级别、锁机制、MVCC、范式原理、NoSql数据库。 MySq：lnnoDB 与 MyISAM 比较、B+ Tree原理、Mysql索引优化、查询优化。 Redis：使用场景、Memchached比较、集群与分布式、线程安全问题、数据淘汰机制、字典和跳跃表、RDB和AOF持久化机制。

SQL Optimization

连接查询与分组查询

Linked query

所谓的连接查询，就是利用各个表之间的共同列的关联性来查询数据。

连接查询是关系型数据库查询最主要的特征。

连接查询分为两种类型：

1、内连接查询（交集）

内连接查询是最典型，最常用的连接查询，它根据表中共同的列来进行匹配，特别是两个表存 在主外键关系时通常会使用到内连接查询。

举个栗子，从 student 表和 score 表中查询学生姓名和成绩，id 是 student 表的主键，是 score 表的外键（student.id 和 score.studentID 是同一个值）。

  • 基于 = 的内连接查询
1
2
3
select student.sname, score.courseID, score.sscore 
from student, score 
where student.id = score.studentID
  • 基于 inner join ... on 的内连接查询，上面的查询语句可以另外写成如下形式：
1
2
3
4
select student.sname, score.courseID, score.sscore 
from student as s
inner join score as sc
on (student.id=score.studentID)

相当于把上面的 , 改成了 inner join，把 where 改成了 on。

insner join: 用来连接两个表 inner: 可以省略 on: 用来设置条件 as: 指定表或者列的“别名” ，如果查询的列名在用到两个或多个表中不重复，则对这一列的引用不必用表名来限定。

2、外连接查询（差集）

外连接查询，参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合联接条件的数据直接返回到结果集中；对那些不符合连接条件的数据，将被填上 NULL 值（空值）后再返回到结果集中。

  • 左连接，A,B 表的交集 和 A 表的数据 （A left join B on A.id=B.id）其中A是主表，如果 A 表中有的数据 B 表没有找到相等的，会显示所有A表的数据，B表中的部分会为 null 。

    举个栗子：要统计所有学生的考试情况，要求显示所参加考试学生的每次考试分数，没有参加考试的学生（有这个学生但是没有成绩）也要显示出来，但是数值为null。这时候，以学生信息表为主表（有时也叫做左表），学生成绩表为从表，做外连接查询如下：

    1
    2
    3
    4
    select studet.name, score.CourseID, score.sscore
    from student
    left outer join score
    on sudent.id=score.studentID

  • 右连接，A,B表的交集 和 B表的数据（A right join B on A.id=B.id）其中B是主表，如果 B 表中有的数据 A 表没有找到相等的，会显示所有B表的数据，A表中的部分会为 null 。

    再举个栗子：要统计所有学生的考试情况，要求显示所参加考试学生的每次考试分数，有成绩但是没有这个学生（旁听生）的记录也要显示出来，但是数值为null。这时候，以学生信息表为从表（有时也叫做左表），学生成绩表为主表，做外连接查询如下：

    1
    2
    3
    4
    select student.name, score.CourseID, score.sscore
    from student
    right outer join score
    on student.id=score.studentID

    on 后面还可以有条件，and 或者 where，详情参看 SQL 连接（内连接，外连接）

  • 全连接，返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值。AB表的差集之和。

    1
    2
    3
    4
    select student.name, score.CourseID, score.sscore
    from student
    full join score
    on student.id=score.studentID

3、交叉连接查询

我们把 "没有任何限制条件的连接方式" 称之为 "交叉连接"，"交叉连接" 后得到的结果跟线性代数中的 "笛卡尔乘积" 一样。使用交叉连接时，任意一张表中的记录多出一行，"交叉连接" 的数量都会增长很多。参考链接

1
2
3
select student.name, score.CourseID, score.sscore
from student
cross join score

等效于

1
2
select student.name, score.CourseID, score.sscore
from student, score

举个栗子：新建 t1 和 t2 两张表格，存入几条数据。 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | jack |
|  3 | li   |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | name |
+----+------+
|  4 | get  |
|  5 | post |
+----+------+
2 rows in set (0.00 sec)

执行交叉查询 
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
mysql> select * from t1 cross join t2;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1 | tom  |  4 | get  |
|  1 | tom  |  5 | post |
|  2 | jack |  4 | get  |
|  2 | jack |  5 | post |
|  3 | li   |  4 | get  |
|  3 | li   |  5 | post |
+----+------+----+------+
6 rows in set (0.00 sec)

mysql> select * from t1,t2;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1 | tom  |  4 | get  |
|  1 | tom  |  5 | post |
|  2 | jack |  4 | get  |
|  2 | jack |  5 | post |
|  3 | li   |  4 | get  |
|  3 | li   |  5 | post |
+----+------+----+------+
6 rows in set (0.00 sec)

可以看到，t1有3条记录，t2有2条记录。交叉查询就会有5条记录，就是 \(2 \times 3 = 5\)。

4、总结

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。 连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

Group query

1、使用 group by 关键字进行单列查询，group by 后面就一个关键字。

语法

1
select column_name from table_name group by column_name;

举个栗子

查询每个性别的学生人数各是多少？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from t2;
+----+-------+------+
| id | name  | ssex |
+----+-------+------+
|  1 | sam   |    0 |
|  2 | kitty |    1 |
|  3 | Laho  |    0 |
|  4 | Laho  |    0 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql> select ssex,count(*) as '人数' from t2 group by ssex;
+------+--------+
| ssex | 人数   |
+------+--------+
|    0 |      3 |
|    1 |      1 |
+------+--------+
2 rows in set (0.00 sec)

查询每个科目的平均分，并按照降序（默认是升序）排列显示，decs 关键字可以指定降序排列。

1
select couseID, avg(score) as '课程平均成绩' from score group by avg(score) decs;

2、使用 group by 关键字进行多列查询，group by 后面多个关键字。

语法

1
select column_name from table_name group by column_name1, column_name2;

举个栗子

查询每个年级、每个性别的学生人数各是多少？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from t2;
+----+-------+------+-------+
| id | name  | ssex | grade |
+----+-------+------+-------+
|  1 | sam   |    0 |     1 |
|  2 | kitty |    1 |     2 |
|  3 | Laho  |    0 |     3 |
|  4 | Laho  |    0 |     3 |
|  5 | kaka  |    0 |     2 |
+----+-------+------+-------+
5 rows in set (0.00 sec)

mysql> select grade as 年级, ssex as 性别, count(*) as 人数 from t2 group by grade, ssex order by grade;
+--------+--------+--------+
| 年级   | 性别   | 人数   |
+--------+--------+--------+
|      1 |      0 |      1 |
|      2 |      0 |      1 |
|      2 |      1 |      1 |
|      3 |      0 |      2 |
+--------+--------+--------+
4 rows in set (0.00 sec

3、使用 hving 关键字在 group by 的后面，添加条件。

语法

1
select column_name from table_name group by column_name having condition;

having、where、orde by 和 group by 句子可以在同一个 select 语句中一起使用，使用顺序如下: Where -> group by -> having -> order by

简单例题

查询人数大于2人的年级。

规律：

的年级，年级就是group的条件。的什么，什么就是group的条件。

1
2
3
4
5
6
7
8
mysql> select grade as 年级, count(*) as 人数 from t2 group by grade having count(*) > 1;
+--------+--------+
| 年级   | 人数   |
+--------+--------+
|      2 |      2 |
|      3 |      2 |
+--------+--------+
2 rows in set (0.00 sec)

查询每门课程及格人数和平均分在 80 分以上的课程，的课程，课程就是group条件。

1
2
3
4
5
6
select count(*) as 人数, avg(score) as 平均分, CourseID as 课程 
from score_table 
where score >= 60 
group by courseID 
having avg(score) > 80 
order by courseID;

连接查询与子查询比较

定义

所谓的连接查询，就是利用各个表之间的共同列的关联性来查询数据。

所谓子查询（嵌套查询），就是把一个查询的结果作为另一个查询的条件。

1
select grada from t1 where id=(select id from t2 where name='sam');

比较

1、子查询（嵌套查询）就如递归函数一样，思路直接，实现简单，有时候使用起来能达到事半功倍之效，只是其执行效率较低，有时用自身连接可代替某些子查询。[实现难易和效率]

2、连接查询都可以用子查询替代，但不是所有子查询都能用连接查询替换，子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件，而连接查询更适合于查看多表的数据。[替换关系和用途]

3、子查询是本质上就是一个完整的SELECT 语句，它可以是一个 SELECT、SELECT..FROM 语句、INSERT..INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。[细分类型的区别]

连接查询 子查询
实现难易 难 易
效率高低 高 低
替换关系 都可用子查询代替 一部分可用连接查询代替
适用情况 比较灵活，方便，形式多样，适合用于作为查询的筛选条件。 更适合于查看多表的数据。
细分类型 一个完整的select语句 内连接、外连接和交叉连接

drop & truncate & delete

  • drop： drop table table_name 彻底删除表，包括内容和定义，并释放空间。
  • truncate [trʌŋˈkeɪt]： truncate table table_name 清空表中的数据，保留表的数据结构 。
  • delete： delete from table_name where column_name = target_value） 删除符合条件的行数据，不影响其他数据，也不删除表结构。

其他区别： 1、执行速度一般来说： drop>truncate>delete。 2、delete 语句是数据库操作语言 (dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger ，执行的时候将被触发。 3、truncate 、 drop 是数据库定义语言 (ddl)，操作立即生效，原数据不放到 rollback segment中，不能回滚，操作不触发 trigger 。 4、truncate 语句执行以后， id 标识列还是按顺序排列，保持连续；而 delete 语句执行后，ID 标识列不连续 。

视图的作用以及何时能更新视图

定义

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

视图具有如下好处

  • 简化复杂的 SQL 操作，比如复杂的连接查询；
  • 只使用实际表的一部分数据；
  • 通过只给用户访问视图的权限，保证数据的安全性；
  • 通过创建view，可以自定义虚拟表的数据格式和表示。

创建

视图的sql语句如下，创建一个视图，查询人数大于1的年级：

1
2
3
4
create view count_people as 
select grade as 年级, count(*) as 人数 
from t2 
group by grade having count(*) >  1;

修改

1
2
alter view view_name as
select column_names form table_names + conditions;(新的select语句)

删除

1
drop view view_name;

使用

直接当成一个表来用就行

1
select * from view_name;

利用视图更新数据的条件

只由一个基表定义且只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性的视图，才允许更新 。

1、只由一个基表定义，例如 create view test_view as select id from A，视图test_view 只由 A 表定义。

2、只含有基表的主键或候补键

3、视图中没有用表达式（distinct、limit、order）或函数（max、min、avg）定义的属性。

只有满足以上条件，基于 view 拿到的数据（一个基表的一个主键/候补键）才能用于更新数据。

不允许更新的情况

1、若视图是由两个以上基本表导出的，则此视图不允许更新。

2、若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。

3、若视图的字段来自集函数，则此视图不允许更新。

4、若视图定义中含有GROUP BY子句，则此视图不允许更新。

5、若视图定义中含有DISTINCT短语，则此视图不允许更新。

6、若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC： CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 　　导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。

7、一个不允许更新的视图上定义的视图也不允许更新。

理解存储过程、触发器等作用

存储过程

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

使用存储过程的好处：

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

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

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

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

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

从 table1 中取出所有 col1 字段的和，将该和的平方放入 ret 表中。

1
2
3
4
5
6
7
8
9
10
delimiter //
create procedure testprocedure(out, ret, int)
    begin
        declare y int;
        select sum(col1)
        from table1
        into y;
        select y*y into ret;
    end //
delimiter;

使用该存储过程

1
2
call testprocedure(@ret);
select @ret;

PS：现在，在高并发、大数据的场景下，业界都不流行在数据库里面搞这些花样了。类似的逻辑，都是在后端完成，数据库只是存数据。

触发器

定义

触发器是一组在某个表执行DELETE、INSERT、UPDATE 语句时而自动执行的 SQL 操作，保证了复杂的参照完整性和数据的一致性。

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

触发器的主要作用是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。它能够对数据库中的相关表进行级联修改，强制比CHECK约束更复杂的数据完整性，并自定义操作消息，维护非规范化数据以及比较数据修改前后的状态。

创建多行触发器，当插入，更新、删除多行数据时，必须编写一个处理多行数据的触发器。执行级联更新或级联删除这样的动作。级联修改数据库中所有相关表。撤销或者回滚违反引用完整性的操作，防止非法修改数据。

优点：

1、通过 BEFORE 关键字实现数据验证和净化。

2、通过 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 的虚拟表，并且是只读的。

1
2
3
4
create trigger mytrigger before delete on mytable
for each row select OLD.col into @result;

select @reslut;

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

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建update触发器
create trigger trig_update
on student
after update
as
begin
    declare @stuCount int;
    select @stuCount=count(*) from student;
    update student_sum set stuCount =@stuCount;
    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from old
    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted
end

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

DBMS

数据库系统原理（Principles of DataBase Manage System ）

主键和外键

主键是能唯一确定当前表的一条记录的字段 外键用于当前表与另一张表的关联，能唯一确定另一张表记录的字段，用于保持数据的一致性。 A表中的一个字段，是B表的主键，该字段就可以是A表的外键。

学生表(学号，姓名，性别，班级) 其中每个学生的学号是唯一的，学号就是一个主键

课程表(课程编号,课程名,学分) 其中课程编号是唯一的,课程编号就是一个主键

成绩表(学号,课程号,成绩) 成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键。

综上，学号是成绩表的外键，课程号也是成绩表的外键。

事务管理与ACID的作用以及实现原理（重点）

事务管理

  • 事务（transaction）符合 ACID 特性的一组SQL语句组成的一个程序执行单元(Unit)，该执行单元要么成功然后Commit，要么失败然后Rollback。。
  • 回退（rollback）指撤销指定事务的过程。
  • 提交（commit）指将未存储的事务结果写入数据库表。
  • 保留点（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

ACID

ACID是数据库事务正常执行的四个基本原则，分别指原子性、一致性、独立性及持久性。

原子性 Atomicity

Atomicity [ˌætəˈmɪsəti]

所谓原子性是指一个事物中所有的语句要么全执行，要么全不执行，是事务最核心的特性。事务本身就是以原子性来定义的。

实现主要基于 undo log。undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

例如

  • 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
  • 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
  • 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

一致性 Consistency

Consistency [kənˈsɪstənsi]

指事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。 通俗的说：我和你的钱加起来一共是2000，那么不管我和你之间如何转账，转几次账，事务结束后我们的钱相加起来应该还得是2000，这就是事务的一致性。

事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

隔离性 Isolation

Isolation [ˌaɪsəˈleɪʃn]

多个事务并发访问数据库时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。 通俗的说：多个用户并发访问操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

lnnoDB默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log 和 类next-key lock 机制。

主要利用的还是锁机制和MVCC机制。

锁机制：还是拿转账例子来说明，有一个账户表 t_balance 如下:

id user_id balance
1 A 200
2 B 0

其中id是主键，user_id为账户名，balance为余额。以转账两次为例，如下图所示

img

当事务1对tableA操作时会获取tableA中事务1正在操作的行的行锁，当且仅当tableA的行锁被事务1释放时，事务2才能获得tableA的改行的行锁，进而操作tableA中这一行的数据。

MVCC，即多版本并发控制(Multi Version Concurrency Control)，一个行记录数据有多个版本对的快照数据，这些快照数据在undo log中。

如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同，就不赘述了。

但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

带你了解数据库中事务的ACID特性 / MySQL的ACID原理 / 程序员，知道Mysql中ACID的原理吗？

可持久性 Durability

Durability [ˌdjʊərəˈbɪləti]

保证事务提交后不会因为宕机等原因导致数据丢失，实现主要基于 redo log。

Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上，内存就相当于一个缓冲区。如果此时突然宕机，内存中的数据就会丢失。

这么做有什么问题？

  • 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
  • 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

采用 redo log 解决上面的问题，redo log 记录的是数据页的物理修改。当做数据修改的时候，不仅在内存中操作，还会在 redo log中记录这次操作。当事务提交的时候，会将 redo log 日志进行刷盘( redo log 一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将 redo log 中的内容恢复到数据库中，这样就保证了数据提交之后，即使遇上宕机，数据也不会丢失。（再根据undo log和bin log内容决定回滚数据还是提交数据。）

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下

  • redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
  • redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

四大隔离级别以及不可重复读和幻影读的出现原因（重点）

四大隔离级别

Read uncommitted（读未提交）

事务中的修改，即使没有提交，对其它事务也是可见的。

直接在磁盘上修改，就可以实现读未提交的隔离级别。

Read committed（读已提交）

事务中的修改，只有已经提交了，对其它事务才是可见的。

将数据读取到内存，完成修改之后，在写回磁盘，就可以实现读已提交的隔离级别，也可以通过MVCC实现。

Repeatable read（可重复读）

同一个事务中多次读取同一数据的结果是一样的。

基于锁机制，在事务第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读的隔离级别，也可以通过MVCC实现。

Serializable（序列化/串行化）

序列化/串行化隔离级别下，各个事务绝对不会相互干扰。

通过锁机制，保证同一时间只有一个事务在对数据进行读写，实现了多事务的串行执行，这样多个事务互不干扰，不会出现任何并发一致性问题，以此来实现序列化/串行化的隔离级别。

三种并发一致性问题

脏读

所谓脏读是指一个事物读取了另一个事务还没提交的修改。举个例子，如果你正在读数据库内容，而我现在修改了数据库内容还没有提交，接着我修改后的内容没有提交的情况下被你读到了，就叫脏读。

通过设置读已提交的隔离级别，即可解决脏读问题。

不可重复读

所谓不可重复读是指一个数据在被一个事务前后两次读取的中间，被另一个事务修改了。导致该事务前后两次读取结果不一样的情况。举个例子，比如你正在读数据库内容，而我update数据库后提交了，你又读了一次数据库内容，这时出现两个内容不同的结果,这叫不可重复读.

通过设置可重复读取的隔离级别，即可解决不可重复度问题。

幻读

所谓幻读是指一个数据库在被一个事务前后两次读取的中间，另一个事务执行了insert操作。导致该事务第二次读取数据库是发现有新的记录。举个例子，比如你正在读数据库内容，而我insert 数据库后提交了，你又读了一次数据库内容，这时你看到内容出现了多一条数据,这叫幻读。

通过设置序列化/串行化的隔离级别，即可解决幻读问题。

基于隔离级别解决并发一致性问题

脏读 不可重复读 幻读
读未提交 ✘ ✘ ✘
读已提交 ✔ ✘ ✘
可重复读 ✔ ✔ ✘
串行化/序列化 ✔ ✔ ✔

封锁的类型以及粒度，两段锁协议，隐式和显式锁定

锁级别（锁粒度）

  • 行级锁

  • 表级锁

锁级别越小，发生锁争用的可能就越小，系统的并发程度就越高。

锁级别越大，加锁需要消耗资源越少，系统开销就越小。

选择锁级别（锁粒度）时，需要在系统开销和并发程度之间做一个权衡。

锁类型

1、读写锁

  • 互斥锁（Exclusive），简写为 X 锁，又称写锁。
  • 共享锁（Shared），简写为 S 锁，又称读锁。

有以下两个规定：

  • 一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
  • 一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

2、意向锁

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

  • 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
  • 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下：

1.png

解释如下：

  • 任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
  • 这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度，一起对所有数据加锁，再一起对所有数据解锁。

1
lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。加锁A，解锁A，加锁B，解锁B，......

1
lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

隐式和显示锁定

MySQL 的 InnoDB 存储引擎采用两段锁协议，

所谓隐式锁是指根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放。

InnoDB 也可以使用特定的语句进行显式的锁定：

1
2
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

乐观锁与悲观锁（重点）

乐观锁

所谓乐观锁就是假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

实现机制：乐观锁不会使用数据库提供的锁机制，一般是增加version参数，记录数据版本号（版本号机制），提交事务前比对等待修改的数据的版本号和之前保存的版本号是否一致，如果一致，就说明未被修改，正常提交事务更新数据。或者基于CAS算法，CAS需要有3个操作数：内存地址V，旧的预期值A，即将要更新的目标值B，当且仅当内存地址V的值与预期值A相等时，将内存地址V的值修改为B，否则就什么都不做，整个比较并替换的操作是一个原子操作。。

悲观锁

悲观锁：所谓悲观锁就是认为数据并发修改的概率比较大，所以需要在修改之前先加锁。

之所以叫做悲观锁，是因为抱有悲观的态度去修改数据的并发控制方式，

实现机制：依靠数据库提供的锁机制，流程如下：

  • 在对数据修改前，尝试增加排他锁。
  • 加锁失败，意味着数据正在被修改，进行等待或者抛出异常。
  • 加锁成功，对数据进行修改，提交事务，锁释放。

乐观锁和悲观锁区别

乐观锁不是真的加锁，效率高，但是要控制好锁的力度。悲观锁依赖数据库锁，效率低，但是更加安全。

MVCC 原理，当前读以及快照读，Next-Key Locks 解决幻读

MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式。

用于实现 读已提交 和 可重复读 这两种隔离级别 以及事务的 独立性原则。 而 读未提交 隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。 可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基本思想

加锁能解决多个事务同时执行时出现的并发一致性问题。

在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的。

而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

具体实现

版本号

  • 系统版本号 SYS_ID ：全局的，针对所有事务，是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
  • 事务版本号 TRX_ID ：全局的，针对当前事务，当前事务开始时的系统版本号。

Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

1
2
3
INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制（隐式提交），每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

  • TRX_ID < TRX_ID_MIN，表示该数据行快照是在当前所有未提交事务之前进行更改的，因此可以使用。
  • TRX_ID > TRX_ID_MAX，表示该数据行快照是在其他事务启动之后被更改的，因此不可使用。
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
    • 读已提交：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
    • 可重复读：不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

当前读以及快照读

快照读

快照读是基于MVCC和 undo log 来实现的，适用于简单 select 语句。

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

1
SELECT * FROM table ...;

当前读

读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。适用于 insert，update，delete，select ... for update，selec ... lock in share mode 语句，以及加锁了的 select 语句。

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

1
2
3
INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

1
2
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

Next-Key Locks解决幻读

next-key lock 是由Record Lock+Gap Lock组合而成，锁定一个范围，并且锁定记录本身。

Record Locks

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

1
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

1
2
3
4
5
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

这样一来就能对查询范围进行加锁，在另一个事务执行插入操作时是不被运行的，从而避免了幻读。

范式理论

背景知识

实体 就数据库而言，实体往往指某类事物的集合。把每一类数据对象的个体称为实体。

函数依赖 记 A->B 表示 A 属性集决定B属性集，也可以说 B 函数依赖于 A。 对于 A->B，如果能找到A的真子集A'，使得A'->B，那么A->B 是B部分函数依赖于A，否则就是B完全函数依赖A。 对于 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 是最低级别的范式。

第一范式

属性不可分（列不可分）：指数据库的每一列都是不可分割的基本数据项，同一列不能有多个值，即实体中的某个属性不能有多个相同的值或者不能有重复的属性。

指导原则 （1）记录的每个属性只能包含一个值。 （2）表中的每个记录必须包含相同数量的值。 （3）表中不能出现完全相同的两个记录。

第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式

在满足第一范式的基础上，每个非主属性必须完全函数依赖于主属性(主键/键码)，不能部分函数依赖于主属性。

如果一个数据表已经满足第一范式，而且该数据表中的任何一个非主键字段的数值都完全函数依赖于该数据表的主键字段，那么该数据表满足第二范式。

可以通过将不满足第二范式的表分解成多个表，使得其满足第二范式。

举个栗子

分解前

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/表-1（主键Sno）

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

有以下函数依赖：

  • Sno -> Sname, Sdept （ Sname, Sdept 直接完全函数依赖于主键Sno）
  • Sdept -> Mname（Mname 间接完全函数依赖于主键Sno）

关系-2/表-1 （主键Sno, Cname ）

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

有以下函数依赖：

  • Sno, Cname -> Grade （Grade 完全函数依赖于主键Sno, Cname ）

这样虽然之前的表不满足第二范式，但是分解之后的两个表就满足第二范式了。

第三范式

在满足第二范式的基础上，非主属性不传递函数依赖于主属性(主键/键码)，那么该数据表满足第三范式。 换而言之，如果一个数据表已经满足第二范式，而且该数据表中的任何两个非主键字段的数据值之间不存在函数依赖关系，那么该数据表满足第三范式。 简而言之，第三范式（3NF）要求一个数据库表中不包含己在其它表中已包含的非主关键字信息。 上面三行是同一个意思，你细品。

可以通过将不满足第二范式的表分解成多个表，使得其满足第二范式。

举个栗子

下面的 关系-1/表-1 中存在以下传递函数依赖：Sno -> Sdept -> Mname，Mname是其他表中已包含的非主关键字信息。

关系-1/表-1（主键Sno）

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

可以进行以下分解：

关系-11/表-11（主键Sno）

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

关系-12/表-12（主键Sdept）

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

这样，所有表都满足第三范式了，就不会出现任何异常了。

SQL 与 NoSQL 的比较

SQL（Structured Query Language）数据库

指关系型数据库，主要代表：SQL Server，Oracle，MySQL，PostgreSQL。

NoSQL（Not Only sQL）数据库

泛指非关系型数据库，主要代表：MongoDB，Redis，CouchDB。

SQL 与 NoSQL 的比较表

SQL NoSql
存储基础 基于表的存储 基于类Json 对象的存储
存储关系 记录 \(\in\) 表 \(\in\) 数据库 条目 \(\in\) 文档 \(\in\) 集合
预定义要求 表结构需要预定义 无需预定义
支持连接查询 支持，允许使用一条SQL语句从多张表中取出相关的数据。 不支持
完整性要求 需要满足数据完整性约束规则，使用事务来保证数据的一致性。 不需要，允许数据不用通过验证就可以存储到任意的位置。
其他 用户多，工具多。 性能强，可扩展性强。
应用场景 逻辑关系需要事先定义，数据一致性是必要条件。 不相关，不确定和逐步发展的数据需求 速度和可扩展性至关重要的

SQL 数据库

  • 使用表存储相关的数据。
  • 在使用表之前需要先定义表的模式，鼓励使用规范化来减少数据的冗余。
  • 支持使用 join 操作（连接查询），使用一条SQL语句从多张表中取出相关的数据。
  • 需要满足数据完整性约束规则，使用事务来保证数据的一致性。
  • 能够大规模的使用。
  • 使用强大的SQL 语言进行查询操作，提供大量的支持，专业技能和辅助工具。

NoSQL 数据库

  • 使用类 JSON 格式的文档来存储键值对信息。
  • 存储数据不需要特定的模式。
  • 使用非规范化的标准存储信息，以保证一个文档中包含一个条目的所有信息。
  • 不需要使用 join 操作
  • 允许数据不用通过验证就可以存储到任意的位置
  • 保证更新的单个文档，而不是多个文档
  • 提供卓越的性能和可扩展性
  • 使用 JSON 数据对象进行查询
  • 是一种新型的技术

适合使用SQL开发的项目

  • 可以预先定义逻辑相关的离散数据的需求
  • 数据一致性是必要的
  • 具有良好的开发者经验和技术支持的标准的成熟技术

适合使用NoSQL开发的项目

  • 不相关，不确定和逐步发展的数据需求
  • 更简单或者更宽松的能够快速开始编程的项目
  • 速度和可扩展性至关重要的

Mysql

常用命令

login

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql -h 0.0.0.0 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1719
Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

logout

1
2
mysql> exit
Bye

show databases

1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| brl_checkIn        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

select database

1
2
3
4
mysql> use brl_checkIn
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

show tables

1
2
3
4
5
6
7
8
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1                |
| t2                |
+-------------------+
2 rows in set (0.00 sec)

show table detail

1
2
3
4
5
6
7
8
9
10
mysql> desc app_group;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI | NULL    | auto_increment |
| name     | varchar(500)  | YES  |     | NULL    |                |
| remarks  | varchar(1000) | YES  |     | NULL    |                |
| admin_id | int(11)       | NO   | MUL | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

create database

1
2
3
4
5
6
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

create databas [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

create table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> use test_db;
Database changed
mysql> create table t1(
    -> id int not null primary key,
    -> name char(50) not null
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(50) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

delete table

1
2
3
4
5
6
7
8
9
10
11
12
13
/* 清空表 */
mysql> truncate table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t1;
Empty set (0.00 sec)

/* 删除表 */
mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
Empty set (0.00 sec)

change table

1
2
/* 范式 */
alter table table_name operation_name[add/drop/change]
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
32
33
34
35
36
37
38
39
40
41
42
43
/* add new field 注意：这里添加内容外面有括号，column和data type之间是空格，不是逗号。*/
mysql> alter table t1 add(score int not null);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(50) | NO   |     | NULL    |       |
| score | int(11)  | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

/* delete field */
mysql> alter table t1 drop column score;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(50) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

/* change field 注意：这里添加内容外面没有括号*/
mysql> alter table t1 change score grand char(20) not null;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(50) | NO   |     | NULL    |       |
| grand | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

add data into table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> insert into t1 value(001, 'tom', '1');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 1     |
+----+------+-------+
1 row in set (0.00 sec)

/* insert table with only a few values */
mysql> select * from t1;
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 1     |
|  2 | NULL | NULL  |
+----+------+-------+
2 rows in set (0.00 sec)

delete data from table

1
2
3
4
5
6
7
8
9
10
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 1     |
+----+------+-------+
1 row in set (0.00 sec)

update data from table

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
32
33
34
35
mysql> update t1 set grand='2' where name='tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 2     |
+----+------+-------+

/* 多条件更新 */
mysql> select * from t2;
+----+-------+------+-------+
| id | name  | ssex | grade |
+----+-------+------+-------+
|  1 | sam   |    0 |     1 |
|  2 | kitty |    1 |  NULL |
|  3 | Laho  |    0 |  NULL |
|  4 | Laho  |    0 |  NULL |
+----+-------+------+-------+
4 rows in set (0.00 sec)
update t2 set grade=3 where id=3 or id=4;

mysql> select * from t2;
+----+-------+------+-------+
| id | name  | ssex | grade |
+----+-------+------+-------+
|  1 | sam   |    0 |     1 |
|  2 | kitty |    1 |     2 |
|  3 | Laho  |    0 |     3 |
|  4 | Laho  |    0 |     3 |
+----+-------+------+-------+
4 rows in set (0.00 sec)

simple query data from table

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
/* 单表全字段查询 */
select * form t1;

/* 单表个别字段查询 */
select id from t1;

/* 多表查询 */
mysql> select * from t1;
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 2     |
+----+------+-------+
1 row in set (0.00 sec)

mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | sam   |
|  2 | kitty |
+----+-------+
2 rows in set (0.00 sec)

mysql> select t1.name,t2.name from t1,t2;
+------+-------+
| name | name  |
+------+-------+
| tom  | sam   |
| tom  | kitty |
+------+-------+

/* 单条件查询 */
mysql> select * from t1 where name='tom';
+----+------+-------+
| id | name | grand |
+----+------+-------+
|  1 | tom  | 2     |
+----+------+-------+
1 row in set (0.00 sec)

/* 多表条件查询 */
mysql> select t1.name, t2.name from t1, t2 where t1.id=t2.id;
+------+------+
| name | name |
+------+------+
| tom  | sam  |
+------+------+
1 row in set (0.00 sec)

/* 子查询/嵌套查询 t1中id和t2中sam的id一样的那个同学的年级是？ */
mysql> select grand from t1 where id=(select id from t2 where name='sam');
+-------+
| grand |
+-------+
| 2     |
+-------+
1 row in set (0.00 sec)

/* 交查询 t1和t2都有的id? */
mysql> select id from t1 where id in (select id from t2);
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

/* 并查询 t1和t2所有的id都有哪些?*/
mysql> (select id from t1) union (select id from t2);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

union 和 union all的区别

union 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，union 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 union：对两个结果集进行并集操作，不包括重复行，也就是会去重，同时进行默认规则的排序； union all：对两个结果集进行并集操作，包括重复行，不会去重，不进行排序；

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> select * from Table1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | jack |
|  3 | lili |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from Table2;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | Loha  |
|  3 | Kitty |
|  4 | Tite  |
+----+-------+
4 rows in set (0.00 sec)

/* 可以发现Table1和Table2都有 id=1 name=tom 这条记录 */
mysql> select * from Table1  
    -> union   
    -> select * from Table2;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jack  |
|  3 | lili  |
|  2 | Loha  |
|  3 | Kitty |
|  4 | Tite  |
+----+-------+
6 rows in set (0.00 sec)
/* 由于 tom 这条记录在两个表中都出现过，所以这里使用union求并集，去重，就会出现了一次。 */

mysql> select * from Table1  
    -> union all  
    -> select * from Table2; 
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jack  |
|  3 | lili  |
|  1 | tom   |
|  2 | Loha  |
|  3 | Kitty |
|  4 | Tite  |
+----+-------+
7 rows in set (0.00 sec)
/* 由于 tom 这条记录在两个表中都出现过，所以这里使用union all求并集，不去重，就会出现了两次。 */

Common modifier

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/* 去重 */
mysql> select * from t2;
+----+-------+
| id | name  |
+----+-------+
|  1 | sam   |
|  2 | kitty |
|  3 | Laho  |
|  4 | Laho  |
+----+-------+
4 rows in set (0.00 sec)

mysql> select distinct name from t2;
+-------+
| name  |
+-------+
| sam   |
| kitty |
| Laho  |
+-------+
3 rows in set (0.00 sec)

/* 限制查询结果数量 */
mysql> select * from t2 limit 2;
+----+-------+
| id | name  |
+----+-------+
|  1 | sam   |
|  2 | kitty |
+----+-------+
2 rows in set (0.01 sec)

/* 排序 默认是升序 */
mysql> select * from t2 order by id;
+----+-------+
| id | name  |
+----+-------+
|  1 | sam   |
|  2 | kitty |
|  3 | Laho  |
|  4 | Laho  |
+----+-------+
4 rows in set (0.00 sec)

/* 降序 */
mysql> select * from t2 order by id desc;
+----+-------+
| id | name  |
+----+-------+
|  4 | Laho  |
|  3 | Laho  |
|  2 | kitty |
|  1 | sam   |
+----+-------+
4 rows in set (0.00 sec)

Common functions

1
select operation[max/min/count/avg/sum](column_name) from table_name;
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
/* 求最大值 */
/* 注： max(colunm) 返回字母序最大的，返回数值最大的 */
mysql> select max(id) from t2;
+---------+
| max(id) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)

/* 求最小值 */
/* 注： max(colunm) 返回字母序最小的，返回数值最小的 */
mysql> select min(id) from t2;
+---------+
| min(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

/* 计数 */
mysql> select count(name) from t2;
+-------------+
| count(name) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

/* 求均值 */
mysql> select avg(id) from t2;
+---------+
| avg(id) |
+---------+
|  2.0000 |
+---------+
1 row in set (0.00 sec)

/* 求和 */
mysql> select sum(id) from t2;
+---------+
| sum(id) |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

Mysql常用命令总结

  • 求并集用 union 或者 union all 关键字；

  • 求交集（就是连接查询）用 =、inner join ... on 实现不同表之间查询结果的交集；

  • 求差集可以用 left join on、right join on 来实现。 例如：已知 Table1 和 Table2 如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    mysql> select * from Table1;
    +----+------+
    | id | name |
    +----+------+
    |  1 | tom  |
    |  2 | jack |
    |  3 | lili |
    +----+------+
    3 rows in set (0.00 sec)

    mysql> select * from Table2;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | tom   |
    |  2 | Loha  |
    |  3 | Kitty |
    |  4 | Tite  |
    +----+-------+
    4 rows in set (0.00 sec)

    求 Table1 相对于 Table2 的差集，也就是 Table1(主表) 中相对于 Table2 中没有的部分，用 Table1 left join Table2 来实现。可以发现 jack 和 lili 这两条记录就是 Table1 相对于 Table2 的差集，后面的Null意味着这两条记录，只在Table1中出现，没有在Table2中出现。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> select * from Table1 left join Table2 on Table1.name = Table2.name;
    +----+------+------+------+
    | id | name | id   | name |
    +----+------+------+------+
    |  1 | tom  |    1 | tom  |
    |  2 | jack | NULL | NULL |
    |  3 | lili | NULL | NULL |
    +----+------+------+------+
    3 rows in set (0.00 sec)

    同理，求 Table2 相对于 Table1 的差集，也就是 Table2(主表) 中相对于 Table1 中没有的部分，用 Table1 right join Table2 来实现。可以发现 Loha、Kitty 和 Tite 这三条记录就是 Table2 相对于 Table1 的差集，前面的Null意味着这三条记录，只在Table2中出现，没有在Table1中出现。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> select * from Table1 right join Table2 on Table1.name = Table2.name;
    +------+------+----+-------+
    | id   | name | id | name  |
    +------+------+----+-------+
    |    1 | tom  |  1 | tom   |
    | NULL | NULL |  2 | Loha  |
    | NULL | NULL |  3 | Kitty |
    | NULL | NULL |  4 | Tite  |
    +------+------+----+-------+
    4 rows in set (0.00 sec)

MySQL 索引以及优化

索引的定义

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引是在表的列上创建，所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请记住记住这一点：索引是一种数据结构 。

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

索引的执行语句

1
2
3
4
5
6
7
8
/* 创建索引 */
create index t1Index on t1 (name);

/* 创建唯一索引 */
create unique t1Index on t1 (id);

/* 移除索引 */
drop index t1Index on t1;

Mysql索引类型

B+ Tree索引

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

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

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

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

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录（叶子节点，1、2，3、4，5、6），这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

img

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

img

哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

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

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

全文索引

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

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

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

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

空间数据索引

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

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

索引优化

独立的列

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

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

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

多列索引

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

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

索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

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

1
2
3
4
5
6
7
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
   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 索引是有序的，会将相邻的数据都存储在一起）。

索引的使用条件

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

哪些字段适合建立索引

参考链接

哪些字段适合建立索引

经常需要进行更新操作的属性

1、表的主键、外键必须有索引； 2、数据量达到300+以上的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 8、频繁进行数据操作的表，不要建立太多的索引； 9、删除无用的索引，避免对执行计划造成负面影响；

B+ Tree 原理，与其它查找树的比较

B+ Tree 基本数据结构

B Tree

B Tree 是指 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。平衡树是改进的二叉查找树。一般的二叉查找树的查询复杂度取决于目标结点到树根的距离（即深度），因此当结点的深度普遍较大时，查询的均摊复杂度会上升。为了实现更高效的查询，产生了平衡树。

在这里，平衡指所有叶子的深度趋于平衡，更广义的是指在树上所有可能查找的均摊复杂度偏低。

240px-AVLtreef.svg.png

B+ Tree

B+树是B树的一种变形，它把数据都存储在叶子节点，内部的非叶子节点只存索引和孩子指针，以叶子节点的最小值作为索引（3，5），简化了内部节点。B+树的遍历高效，将所有叶子节点串联成链表即可从头到尾遍历。

B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。下图是把键1-7连接到值 d1-d7 的B+树。链表指针（红色）用于快速顺序遍历叶子节点，空指针（黄色）为null，有效指针（白色）用于指向子节点或者数据（\(d_1\), \(d_2\), ..., \(d_7\)）。

1024px-Bplustree.png

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。（重点内容）

在 B+ Tree 中，一个节点中的 \(key\)（\(key\) 就是索引） 从左到右非递减排列，如果某个指针的左右相邻的 \(key\) 分别是 \(key_i\) (3) 和 \(key_{i+1}\) (5)，且不为 null，则该指针指向节点的所有 \(key\) (3、4) 大于等于 \(key_i\) (3) 且小于等于 \(key_{i+1}\) (5)。

Differences of B Tree and B+ Tree

  • 所有data都存储在叶子节点，非叶子节点不存储真正的data。
  • 为所有叶子节点增加了一个链指针，将所有叶子节点串联成链表即可从头到尾遍历。

B+ Tree 操作

查找操作

首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。 直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入操作

M 阶B+树插入：

  • 若为空树直接插入。
  • 对于叶子结点：根据key找到叶子结点，对叶子结点进行插入操作。插入后如果当前叶子结点的key值数 b 不大于m-1，则插入结束。
  • 对于索引结点：如果当前结点的key个数小于等于 m-1 ，插入结束。

删除操作

插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作（待详细补充）来维护平衡性。

与其它查找树的比较

B+树与B树比较

  • B+树的磁盘读写代价更低

因为B+树内部结点没有指向关键字具体信息的指针，内部结点相对B树小，B+树的磁盘读写代价就更低。

  • B+树的查询更加稳定

因为非终端结点并不是指向文件内容的结点，仅仅是作为叶子结点的关键字索引，因此所有的关键字查询都会走一条从根节点到叶子结点的路径。即所有关键字查询的长度是一样的，查询效率稳定。不会出现一会儿查询快、一会儿查询慢的情况。

B+树与红黑树的比较

  • B+树查找次数更少

平衡树查找操作的时间复杂度和树高 h 相关，\(O(h)=O(logdN)\)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。

  • B+树能够利用磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

查询优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。Explain可以分析某条select语句会查询多少条记录、以怎样的方式查询，以及复杂select的执行顺序，借此可以了解到select语句的性能和查询是如何执行的

比较重要的字段有：

  • select_type : 查询类型，有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数

优化数据访问

减少请求的数据量

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

减少服务器端扫描的行数

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

重构查询方式

切分大查询

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

1
2
3
4
5
6
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
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
5
6
7
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
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);

汇总

  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
  • 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id fromt where num=0
  • 应尽量避免在where子句中使用！=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  • 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20 可以这样查询：select id from t where num=10 union all - select id from t where num=20
  • in 和 not in 也要慎用，否则会导致全表扫描，如：select id fromt where num in(1, 2, 3)
  • 索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  • 应尽可能的避免更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引：
  • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  • 尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  • 任何地方都不要使用select*fromt，用具体的字段列表代替“”，不要返回用不到的任何字段。
  • 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
  • 避免频繁创建和删除临时表，以减少系统表资源的消耗。
  • 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
  • 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert：
  • 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
  • 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
  • 与临时表一样，游标并不是不可使用。对小型数据集使用FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
  • 在所有的存储过程和触发器的开始处设置SET NOCOUNTON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。
  • 尽量避免大事务操作，提高系统并发能力。
  • 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

InnoDB 与 MyISAM 比较

Innodb

My Indexed Sequential Access Method （我的有索引的顺序访问方法） 读音类似 米森

共同点

都是数据库引擎

基本的差别

Innodb MySIAM
速度 慢 快
体积 大，Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不少。 小，MyISAM的索引和数据是分开的，并且支持压缩表和空间数据索引。内存使用率就对应提高了不少，能加载更多索引。
高级功能 支持，InnoDB提供事务管理支持、外键、包括行级锁在内的锁机制等高级数据库功能。 不支持事务管理、外键等功能，MyISAM 只支持表级锁，
内存缓存 而lnnoDB缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大。 MyISAM缓存在内存的是索引，不是数据。
备份 InnoDB 支持在线热备份 关闭服务器执行离线备份冷备份
崩溃恢复 不易崩溃，基于日志系统能够快速恢复。 MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多
Mysql选择 从MySQL5.5.5以后，InnoDB是默认引擎。 MyISAM是MySQL5.5.5以前的默认引擎。
应用场景 Innodb支持外键、行锁、事务是Innodb的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。 MySIAM是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。

Innodb，功能全面更高级，写操作（update、insert）大量多于读操作（select）的场景。

MySIAM，功能简单，更快，读操作（select）大量多于写操作（update、insert）的场景。

其他差别

  • 从MySQL5.6版本开始，InnoDB开始支持FULLTEXT类型的索引，之前的版本不支持全文索引。
  • InnoDB中不保存表的行数，如select count（0）from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count（0）语句包含where条件时MyISAM也需要扫描整个表.
  • 对于自增长的字段，在MylSAM表中可以和其他字段一起建立联合索引。在InnoDB中，可以创建只有该字段的索引，也可以建立联合索引，但自增键必须在最左侧。
  • 清空整个表（delete from）时，InnoDB是一行一行的删除，效率非常慢。MylSAM则会重建表。（这个区别貌似不是很重要哈）
  • 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
  • Innodb实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

参考链接

MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比

水平切分与垂直切分

主从复制原理、作用、实现

一致性 Hash

https://www.jianshu.com/p/e968c081f563

https://www.cnblogs.com/lpfuture/p/5796398.html

https://zhuanlan.zhihu.com/p/34985026

redo、undo、binlog 日志的作用

Redis

Redis 是一个高性能的key-value数据库。 redis的出现，很大程度补偿了memcached这类keyvalue存储的不足，在部 分场合可以对关系数据库起到很好的补充作用。

使用场景

计数器

在内存中对数字进行自增自减运算，从而实现计数器功能。

Redis 这种内存型数据库的读写性能非常高，很适合存储频繁读写的计数量。

通用缓存

将热点数据放到内存中，设置内存的最大使用量以及淘汰策略来保证缓存的命中率。

查找表

例如 DNS 记录就很适合使用 Redis 进行存储。

查找表和缓存类似，也是利用了 Redis 快速的查找特性。但是查找表的内容不能失效，而缓存的内容可以失效，因为缓存不作为可靠的数据来源。

消息队列

Redis 的 List 是一个双向链表，可以通过 lpush 和 rpop 写入和读取消息

不过最好使用 Kafka、RabbitMQ 等消息中间件。

会话缓存

可以使用 Redis 来统一存储多台应用服务器的会话信息（session catch）。

当应用服务器不再存储用户的会话信息，也就不再具有状态，一个用户可以请求任意一个应用服务器，从而更容易实现高可用性以及可伸缩性。

Django项目中使用Redis连接和配置都已经完成了，那么在项目中该如何使用呢？接下来看下面这段例子吧。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
from django.conf import settings
from django.core.cache import cache

# read cache user id
def read_from_cache(self, user_name):
    key = 'user_id_of_'+user_name
    value = cache.get(key)
    if value == None:
        data = None
    else:
        data = json.loads(value)
    return data

# write cache user id
def write_to_cache(self, user_name):
    key = 'user_id_of_'+user_name
    cache.set(key, json.dumps(user_name), settings.NEVER_REDIS_TIMEOUT)

通过上面的这两个方法就可以实现对redis的读取操作了，只需要将需要的字段当参数传入到方法中就好了。

分布式锁实现

在分布式场景下，无法使用单机环境下的锁来对多个节点上的进程进行同步。

可以使用 Redis 自带的 SETNX 命令实现分布式锁，除此之外，还可以使用官方提供的 RedLock 分布式锁实现。

Redis Setnx（SET if Not eXists） 命令在指定的 key 不存在时，为 key 设置指定的值。

其它

Set 可以实现交集、并集等操作，从而实现共同好友等功能。

ZSet 可以实现有序性操作，从而实现排行榜等功能。

Redis 与 Memchached 的比较

相同点

两者都是非关系型内存键值数据库

不同点

Redis Memchached
数据类型 Redis 支持五种不同的数据类型（String、Hash、List、Set、Zset），可以更灵活地解决问题。 Memcached 仅支持字符串类型
数据持久化 Redis 支持两种持久化策略：RDB 快照和 AOF 日志 Memcached 不支持持久化
分布式 Redis Cluster 实现了分布式的支持。 Memcached 不支持分布式，只能通过在客户端使用一致性哈希来实现分布式存储，这种方式在存储和查询时都需要先在客户端计算一次数据所在的节点。
内存管理机制 在 Redis 中，并不是所有数据都一直存储在内存中，可以将一些很久没用的 value 交换到磁盘。 而 Memcached 的数据则会一直在内存中。
网络I/O 单线程、I/O复用模型 多线程、I/O阻塞模型
查询/操作类型 1、批量操作。2、事务支持。3、每个类型不同的CRUD。 常用CRUD、少量的命令
适用场景 复杂数据结构、持久化、高可用、Value存储较大。 纯key-value，数据量非常大，并发非常大的业务。

集群与分布式

线程安全问题

Redis 是线程安全的吗？

Redis 是个单线程程序，所以它是线程安全的。

Redis 单线程为什么还能这么快？

  • Redis 是基于内存的，内存的读写速度非常快。
  • Redis 是单线程的，避免了不必要的上下文切换和竞争条件（不需要线程同步和线程通信，因此更节省时间）。
  • Redis 使用多路复用技术，可以处理并发的连接，非阻塞 I/O 内部实现采用 epoll。采用了 epoll + 自己实现的简单的事件框架。 epoll 中的读、写、关闭、连接都转化成了事件，然后利用 epoll 的多路复用特性，绝不在 I/O上浪费一点时间。

为什么 Redis 是单线程的？

1、官方回答

因为Redis 是基于内存的操作， CPU 不是 Redis 的瓶颈， Redis 的瓶颈最有可能是机器内存的大小或者网络带宽。既然单线程容易实现，而且 CPU 不会成为瓶颈，那就顺理成章地采用单线程的方案了，而且避免了线程同步、线程通信、线程切换带来的时间开销。

2、性能更好

关于Redis 的性能，官方网站也有，即使在普通笔记本这样的硬件上，Redis依然可以轻松处理每秒几十万的请求。

3、详细原因

3.1、不需要各种锁的性能消耗

Redis的数据结构并不全是简单的Key-Value，还有list，hash等复杂的结构，这些结构有可能会进行很细粒度的操作，比如在很长的列表后面添加一个元素，在hash当中添加或者删除一个对象。这些操作可能就需要加非常多的锁，导致的结果是同步开销大大增加。

总之，在单线程的情况下，就不用去考虑各种锁的问题，不存在加锁释放锁操作，没有因为可能出现死锁而导致的性能消耗。

3.2、单线程多进程集群方案

单线程的威力实际上非常强大，核心效率也非常高，多线程自然是可以比单线程有更高的性能上限，但是在今天的计算环境中，即使是单机多线程的上限也往往不能满足需要了，需要进一步摸索的是多服务器集群化的方案，这些方案中多线程的技术照样是用不上的。

所以单线程、多进程的集群不失为一个合适的解决方案。

3.3、CPU消耗

采用单线程，避免了不必要的上下文切换和竞争条件，也不存在多进程或者多线程导致的切换而消耗CPU。 但是如果CPU成为Redis瓶颈，或者不想让服务器其他CUP核闲置，那怎么办？

可以考虑多起几个Redis 进程，Redis是key-value数据库，不是关系数据库，数据之间没有约束。只要客户端分清哪些key放在哪个Redis进程上就可以了。

字典和跳跃表原理分析

字典

Dictht 是一个散列表结构（hash-map），Redis使用拉链法解决哈希冲突，也可以用再次hash或者开放地址hash来解决。

开放定址法：当冲突发生时，使用某种探查(亦称探测)技术在散列表中形成一个探查(测)序列。沿此序列逐个单元地查找，直到找到给定的关键字，或者碰到一个开放的地址(即该地址单元为空)为止（若要插入，在探查到开放的地址，则可将待插入的新结点存人该地址单元）。查找时探查到开放的 地址则表明表中无待查的关键字，即查找失败。

链地址法：将所有关键字为同义词的结点链接在同一个单链表中。若选定的散列表长度为m，则可将散列表定义为一个由m个头指针组成的指针数 组T[0..m-1]。凡是散列地址为i的结点，均插入到以T[i]为头指针的单链表中。T中各分量的初值均应为空指针。

字典又称符号表，关联数组或者映射，是一种用于保存键值对的抽象数据结构。字典中的每个键都是独一无二的，程序可以在字典中根据键值查找与之关联的值，或者通过键来更新值，删除等。

字典实现：Redis的字典使用哈希表作为底层实现，一个哈希表里面有多个哈希节点，而每个哈希表节点就保存了字典中的一个键值对。

拉链法

将所有关键字为同义词的结点链接在同一个单链表中。若选定的散列表长度为m，则可将散列表定义为一个由m个头指针组成的指针数组t[0..m-1]。凡是散列地址为i的结点，均插入到以t为头指针的单链表中。t中各分量的初值均应为空指针。在拉链法中，装填因子α可以大于1，但一般均取α≤1。

换句话说：HashCode是使用Key通过Hash函数计算出来的，由于不同的Key，通过此Hash函数可能会算的同样的HashCode，所以此时用了拉链法解决冲突，把HashCode相同的Value连成链表. 但是get的时候根据Key又去桶里找，如果是链表说明是冲突的，此时还需要检测Key是否相同

v2-56a807e5ed10b5a2a025a7eacc58c3b2_720w 1.jpg

在解释下，Java中HashMap是利用“拉链法”处理HashCode的碰撞问题。在调用HashMap的put方法或get方法时，都会首先调用hashcode方法，去查找相关的key，当有冲突时，再调用equals方法。hashMap基于hasing原理，我们通过put和get方法存取对象。当我们将键值对传递给put方法时，他调用键对象的hashCode()方法来计算hashCode，然后找到bucket（哈希桶）位置来存储对象。当获取对象时，通过键对象的equals()方法找到正确的键值对，然后返回值对象。HashMap使用链表来解决碰撞问题，当碰撞发生了，对象将会存储在链表的下一个节点中。hashMap在每个链表节点存储键值对对象。知乎 安安熊

详情可参看 Redis 字典详解

跳表

跳表原理可参看这个链接 Redis 跳表详解

Redis的几种数据类型

类型 简介 特性 场景
String(字符串) 二进制安全 可以包含任何数据,比如jpg图片或者序列化的对象,一个键最大能存储512M ---
Hash(字典) 键值对集合,即编程语言中的Map类型 适合存储对象,并且可以像数据库中update一个属性一样只修改某一项属性值(Memcached中需要取出整个字符串反序列化成对象修改完再序列化存回去) 存储、读取、修改用户属性
List(列表) 链表(双向链表) 增删快,提供了操作某一段元素的API 1,最新消息排行等功能(比如朋友圈的时间线) 2,消息队列
Set(集合) 哈希表实现,元素不重复 1、添加、删除,查找的复杂度都是O(1) 2、为集合提供了求交集、并集、差集等操作 1、共同好友 2、利用唯一性,统计访问网站的所有独立ip 3、好友推荐时,根据tag求交集,大于某个阈值就可以推荐
Sorted Set/Zset(有序集合) 将Set中的元素增加一个权重参数score,元素按score有序排列 数据插入集合时,已经进行天然排序 1、排行榜 2、带权重的消息队列

RDB 和 AOF 持久化机制

Redis 是内存型数据库，为了保证数据在断电后不会丢失，需要将内存中的数据持久化到硬盘上。

RDB 持久化

将某个时间点的所有数据都存放到硬盘上。

可以将快照复制到其它服务器从而创建具有相同数据的服务器副本。

如果系统发生故障，将会丢失最后一次创建快照之后的数据。

如果数据量很大，保存快照的时间会很长。

AOF 持久化

将写命令添加到 AOF 文件（Append Only File）的末尾。

使用 AOF 持久化需要设置同步选项，从而确保写命令同步到磁盘文件上的时机。这是因为对文件进行写入并不会马上执行（将内容同步到磁盘上），而是先存储到缓冲区，然后由操作系统决定什么时候同步到磁盘。有以下同步选项：

选项 同步频率
always 每个写命令都同步
everysec 每秒同步一次
no 让操作系统来决定何时同步
  • always 选项会严重减低服务器的性能；
  • everysec 选项比较合适，可以保证系统崩溃时只会丢失一秒左右的数据，并且 Redis 每秒执行一次同步对服务器性能几乎没有任何影响；
  • no 选项并不能给服务器性能带来多大的提升，而且也会增加系统崩溃时数据丢失的数量。

随着服务器写请求的增多，AOF 文件会越来越大。Redis 提供了一种将 AOF 重写的特性，能够去除 AOF 文件中的冗余写命令。

事件驱动模型

Redis 服务器是一个事件驱动程序。

文件事件

服务器通过套接字与客户端或者其它服务器进行通信，文件事件就是对套接字操作的抽象。

实现方法

Redis 基于 Reactor 模式开发了自己的网络事件处理器，使用 I/O 多路复用程序来同时监听多个套接字，并将到达的事件传送给文件事件分派器，分派器会根据套接字产生的事件类型调用相应的事件处理器。

img

时间事件

服务器有一些操作需要在给定的时间点执行，时间事件是对这类定时操作的抽象。

时间事件又分为：

  • 定时事件：是让一段程序在指定的时间之内执行一次；
  • 周期性事件：是让一段程序每隔指定时间就执行一次。

实现方法

Redis 将所有时间事件都放在一个无序链表中，通过遍历整个链表查找出已到达的时间事件，并调用相应的事件处理器。

事件的调度与执行

服务器需要不断监听文件事件的套接字才能得到待处理的文件事件，但是不能一直监听，否则时间事件无法在规定的时间内执行，因此监听时间应该根据距离现在最近的时间事件来决定。

事件调度与执行由 aeProcessEvents 函数负责，伪代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def aeProcessEvents():
    # 获取到达时间离当前时间最接近的时间事件
    time_event = aeSearchNearestTimer()
    # 计算最接近的时间事件距离到达还有多少毫秒
    remaind_ms = time_event.when - unix_ts_now()
    # 如果事件已到达，那么 remaind_ms 的值可能为负数，将它设为 0
    if remaind_ms < 0:
        remaind_ms = 0
    # 根据 remaind_ms 的值，创建 timeval
    timeval = create_timeval_with_ms(remaind_ms)
    # 阻塞并等待文件事件产生，最大阻塞时间由传入的 timeval 决定
    aeApiPoll(timeval)
    # 处理所有已产生的文件事件
    procesFileEvents()
    # 处理所有已到达的时间事件
    processTimeEvents()

将 aeProcessEvents 函数置于一个循环里面，加上初始化和清理函数，就构成了 Redis 服务器的主函数，伪代码如下：

1
2
3
4
5
6
7
8
def main():
    # 初始化服务器
    init_server()
    # 一直处理事件，直到服务器关闭为止
    while server_is_not_shutdown():
        aeProcessEvents()
    # 服务器关闭，执行清理操作
    clean_server()

从事件处理的角度来看，服务器运行流程如下：

img

数据淘汰机制

淘汰过程

内存淘汰的过程：

  • 客户端发起了需要申请更多内存的命令（如set）
  • Redis 检查内存使用情况，如果已使用的内存大于 max memory 则开始根据用户配置的不同淘汰策略来淘汰内存（key），从而换取一定的内存。
  • 如果上面都没问题，则这个命令执行成功。

LRU（最近最久未使用算法 Least Recently Used）

在一小部分缓存的key-value中找到最近最久未使用的那个key-value，将其淘汰。

TTL（生存时间值 Time To Live）

把存在时间大于TTL的key-value全部淘汰掉。

Redis 具体有 6 种淘汰策略

策略 描述
volatile-lru 从已设置过期时间的数据集中挑选最近最少使用的数据淘汰
volatile-ttl 从已设置过期时间的数据集中挑选将要过期的数据淘汰
volatile-random 从已设置过期时间的数据集中任意选择数据淘汰
allkeys-lru 从所有数据集中挑选最近最少使用的数据淘汰
allkeys-random 从所有数据集中任意选择数据进行淘汰
noeviction 禁止驱逐数据

总结

Redis中的淘汰机制（LRU和TTL）都是非精确算法实现的，主要从性能和可靠性上做平衡，所以并不是完全可靠，在了解Redis 淘汰策略之后还应在平时多主动设置或更新key的expire时间，主动删除没有价值的数据，提升Redis整体性能和空间。

作为内存数据库，出于对性能和内存消耗的考虑，Redis 的淘汰算法实际实现上并非针对所有 key，而是抽样一小部分并且从中选出被淘汰的 key。（这个很关键）

使用 Redis 缓存数据时，为了提高缓存命中率，需要保证缓存数据都是热点数据。可以将内存最大使用量设置为热点数据占用的内存量，然后启用 allkeys-lru 淘汰策略，将最近最少使用的数据淘汰。

Redis 4.0 引入了 volatile-lfu 和 allkeys-lfu 淘汰策略，LFU 策略通过统计访问频率，将访问频率最少的键值对淘汰。

事务原理

Redis的一个事务包含了多个不可分割的命令，要么一起执行要么都不执行，服务器在执行事务期间，不会改去执行其它客户端的命令请求。

事务中的多个命令被一次性发送给服务器，而不是一条一条发送，这种方式被称为流水线，它可以减少客户端与服务器之间的网络通信次数从而提升性能。

redis事务从开始到结束，一般会经过三个阶段：

  • 事务开始
  • 命令入队
  • 事务执行

Redis 最简单的事务实现方式是使用 MULTI 和 EXEC 命令将事务操作包围起来。

1
2
3
4
5
6
7
8
9
10
11
12
127.0.0.1:6379> multi 
OK
127.0.0.1:6379> lpush fruits orange
QUEUED
127.0.0.1:6379> lpush fruits nut
QUEUED
127.0.0.1:6379> lpush fruits apple
QUEUED
127.0.0.1:6379> exec
1) (integer) 1
2) (integer) 2
3) (integer) 3

出现并发问题，因为有多个客户端会并发进行操作。我们可以通过 Redis 的分布式锁来避免冲突，这是一个很好的解决方案。分布式锁是一种悲观锁。

那是不是可以使用乐观锁的方式来解决冲突呢？ Redis 提供了这种 watch 的机制，它就是一种乐观锁。有了 watch 我们又多了一种可以用来解决并发修改的方法。 watch 的使用方式如下：

1
2
3
4
5
6
7
8
9
10
11
127.0.0.1:6379> watch msg
OK
127.0.0.1:6379> multi
OK
127.0.0.1:6379> set msg "hello wolrd"
QUEUED
127.0.0.1:6379> exec
(nil)
127.0.0.1:6379> get msg
"12345"
127.0.0.1:6379>

注意事项 Redis 禁止在 multi 和 exec 之间执行 watch 指令，而必须在 multi 之前做好盯住关键变量，否则会出错。

总结

事务队列以先进先出的方法保存命令，先入队的命令会被放到队列的前面，而较后入队的命令则会被放到队列的后面。通过悲观锁机制对队列上锁，或者通过watch命令在开始时就盯住关键变量（使用乐观锁），可以解决并发问题，保证事务队列中的命令一起执行。

参考连接

Redis实战 - 5事务：multi、exec和watch

主从复制原理

面试真题：Redis哨兵模型、部署、原理，怎么选从服务器？