【数据库】 面试复习

1. C++

2. 计算机网络

3. 操作系统

4. 数据库

5. 数据结构

6. 杂项

1.SQL

1.1 数据库分页

SQL的分页语法：
MySQL 中， SELECT语句默认返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，以实现分页查询。 LIMIT子句的语法如下：

--在所有的查询结果中，返回前5行记录。
SELECT prod_name FROM products LIMIT 5; 
--在所有的查询结果中，从第5行开始，返回5行记录。 
SELECT prod_name FROM products LIMIT 5,5; 

总之，带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始。

1.2 聚合函数

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()，下面以MySQL为例，说明这些函数的作用。

1. COUNT()函数：
   COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数，它有两种用法：
   COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
   COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
   COUNT()函数可以与GROUP BY一起使用来计算每个分组的总和。
2. AVG()函数：
   AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
   AVG()函数可以与GROUP BY一起使用，来计算每个分组的平均值。
3. SUM()函数
   SUM()是一个求总和的函数，返回指定列值的总和。
   SUM()可以与GROUP BY 起使用，来计算每个分组的总和。
4. MAX(）函数：
   MAX(）返回指定列中的最大值。
   MAX(）也可以和GROUP BY关键字一起使用，求每个分组中的最大值。
   MAX(）函数不仅适用于查找数值类型，也可应用于字符类型。
5. MIN()函数：
   MIN(）返回查询列中的最小值。
   MIN(）也可以和GROUP BY关键字一起使用，求出每个分组中的最小值
   MIN(）函数 MAX(）函数 似，不 于查找数值类型，也 应用于字符类型

1.3 外连接

把悬浮数组也保存在结果关系中，而在其他属性上填空值（NULL）的连接。
外连接通过OUTER JOIN来实现，它会返回两张表中满足连接条件的数据，同时返回不满足连接条件的数据。常见的外连接有两种形式：左外连接 CLEFT OUTER JOIN) 、右外连接 (RIGHT OUTER JOIN)
左外连接：可以简称为左连接 (LEFT JOIN) ，它会返回左表中的所有记录和右表中满足连接条件的记录。
右外连接：可以简称为右连接 (RIGHT JOIN) ，它会返回右表中的所有记录和左表中满足连接条件的记录。

1.4 SQL注入

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码， 服务端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的 “SQL" 拼接起来，导致会执行一些预期之外的操作。

# 输入用户名密码时，在用户名处输入'or 1=1 #' 作为输入参数，服务端构建的SQL语句如下：
# SQL会将#之后的内容注释掉
select * from user where username=''or l=l # 'and password='123456'

防止SQL注入

1. 严格的参数校验：参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可
2. SQL预编译：将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中做了转义操作

1.5 WHERE HAVING区别

WHERE是一个约束声明，使用WHERE约束来自数据库的数据， WHERE是在结果返回之前起作用的， WHERE不能使用聚合函数。
HAVING是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在HAVING中可以使用聚合函数。另一方面， HAVING子句不能使用除了分组字段和聚合函数之外的其他字段。
从性能的角度来说， HAVING子句中如果使用了分组字段作为过滤条件 应该替换成WHERE子句。因为WHERE以在执行分组操作和计算聚合函数之前过滤掉不需要的数据 性能会更好。

1.6 SQL语句

基本表相关

# 创建表
CREATE TABLE STUDENT(
	Sno CHAR(9) PRIMARY KEY, 	# 列级完整性约束条件，Sno是主码
	Sname CHAR(20) UNIQUE,		# Sname取唯一值
	Ssex CHAR(2) NOT NULL,		# 非空
	Sage SMALLINT,
	Sdept CHAR(20),
	FOREIGN KEY (Sno) REFERENCES Course(Cno)	# 表级完整性约束，So是外码，被参照表是Course
);
# 修改表
ALTER TABLE STUDENT ADD S_entrance DATE;	# 增加入学时间
ALTER TABLE SUTDENT ALTER COLUMN Sage INT;	# 修改类型为int
# 删除表
DROP TABLE STUDENT RESTRICT|CASCADE;		# RESTRECT-删除有限制条件，有依赖、视图、触发器等（默认）
											# CASCADE-级联删除，相关依赖、视图、触发器均被删除

索引相关

# 建立索引
CREATE [UNIQUE] [CLUSTER] INDEX Stusno ON STUDENT(Sno); 	# UNIQUE-此索引的每一个索引值只对应唯一的数据记录
															# CLUSTER-聚集索引
# 修改索引
ALTER INDEX Stusno RENAME TO SCSno;		# 重命名为SCSno
# 删除索引
DROP INDEX SCSno;						# 删除索引SCSno

查询相关

# 一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [,目标列表达式]...   			# DISTINCT-去除表中重复行
FROM <表明或视图名> [,<表明或视图名>] (<SELECT 语句> [AS]<别名>
[WHERE <条件表达式>]				
[GROUP BY <列名1> [HAVING <条件表达式>]							# GROUP BY将结果按某一列或多列的值分组，值相等的为一组
[ORDER BY <列名2> [ASC|DESC]										# ASC-升序 DESC-降序

# 条件表达式
WHERE Sage [NOT] BETWEEN 20 AND 23;						# 闭区间
WHERE Sdept [NOT] IN ('CS', 'IS', 'MA');
WHERE Sname [NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>'] 	# 匹配串通配符 '%'-任意长度 '_'-一个字符
														# 如果查询的字符串本身包含通配符，需要使用ESCAPE '<>'短语对通配符转义
WHERE Sdept='CS' OR Sdept='MA';

# 聚集函数
COUNT(*) 	COUNT([DISTINCT|ALL] <列名>)	 	SUM() 	AVG() 	MAX() 	MIN()

# 连接查询
SELECT * FROM STUDENT, SC WHERE STUDENT.Sno=SC.Sno;							# 等值连接
> < >= <= != <> 															# 非等值连接
SELECT FIRST.CnO, SECOND.CpNO FROM COUTSE FIRST, COURSE SECOND WHERE...		# 自身连接
SELECT * FROM STUDENT LEFT OUTER JOIN SC ON(STUDENT.Sno=SC.sNO);			# 外连接
SELECT * FROM STUDENT, SC, COURSE WHERE...									# 多表连接		

# 嵌套查询
# 子查询出现在where子句
SELECT Sname, Sage
FROM Student
WHERE Sage < ANY(
	SELECT Sage
	FROM SC
	WHERE Sdept='CS')
ADN Sdept <> 'CS'; 		# 这是父查询		
# 子查询出现在from子句
SELECT Sname
FROM STUDENT, (SELECT Sno FROM SC WHERE Cno='A') AS SC1		# AS关键词可省略
WHRE STUDENT.Sno=SC1.Sno；						

数据更新

# 插入数据
INSERT INTO STUDENT VALUES();			# 插入元祖
INSERT INTO DEPT_AGE(Sdept, avg_age)	# 插入子查询结果
	SELECT SDEPT,AVG(Sage)
	FROM STUDENT
	GROUP BY Sdept;
# 修改数据
UPDATE Student SET age='12' WHERE Sno='20020';	# 修改一个元祖数据
UPDATE Student SET age=age+1;					# 修改多个数据
UPDATE SC SET Grade=0							# 带子查询修改数据
WHERE Sno IN(		
	SELECT Sno
	FROM Student
	WHERE Sdept='CS';
# 删除数据 
DELETE FROM Student WHERE Sno='1929929';

视图

视图是一个虚表，数据库只存放视图的定义，不存放视图对应的数据

# 建立视图
CREATE VIEW IS_Student(SNO,GAVG)	# 只有在视图查询时，才执行select子句
AS
SELECT Sno,AVG(Grade)
FROM Student
GROUP BY Sno;

# 删除视图
DROP VIEW IS_Student [CASCADE]		# 使用CASCADE删除该视图和由它导出的所有视图

# 查询视图
# 定义视图之后和基本表一样查询即可

# 更新视图
# 最终转换为对表的更新，但是并非所有视图皆可更新
WITH CHECK OPTION					# 检查视图定义中的条件，若不满足条件则拒绝执行该操作

数据库权限

# 授权 GRANT
GRANT UPDATE(Sno), SELECT			# 授权给U4用户对Student表查询,对Sno列的更更新权限
ON TABLE Student
TO U4;

# 收回 REVOKE
REVOKE UPDATE 
ON TABLE Student
FROM U4 CASCADE;

2. 锁

2.1 悲观锁&乐观锁（如何实现隔离性）

• 悲观锁（悲观并发控制）select … for update（所有扫描过的行都会被上锁，所以要走索引，不能全表扫描）
  当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，直接对该数据进行加锁以防止并发。主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。加锁消耗资源，并且可能出现死锁。
• 乐观锁（乐观并发控制）
  乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做（回滚）。一般不用锁机制，用记录数据版本号来实现，数据更新，版本号更新，提交更新的时候，将当前版本信息与第一次的相比较，相等，则提交更新，否则认为是过期数据。但是锁的粒度（锁的东西多少）掌握不好，更新失败的概率会比较高，容易发生业务失败。

2.2 排它锁&共享锁

• 排它锁（eXclusive lock，简记为X锁）（写锁）
  若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁
• 共享锁（Share lock，简记为S锁）（只读，不能修改）
  若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁

2.3 锁的算法

InnoDB存储引擎有3种行锁的算法 其分别是

• Record Lock 记录锁。Record Lock 总是会去锁住索引记录，记录锁锁的是表中的某一条记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引。如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
• Gap Lock 间隙锁。每次锁定都是锁定一个左开右闭区间 。当我们查询数据用范围查询而不是相等条件查询时，查询条件命中索引，并且没有查询到符合条件的记录，此时就会将查询条件中的范围数据进行锁定。
• Next-Key Lock 临键锁。Gap Lock+Record Lock 锁定一个范围，并且锁定记录本身。Next-Key Lock是结合了 Gap Lock+Record Lock 的一种锁定算法，其设计的目的是为了解决Phantom Problem （幻读）。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁 (predict lock) 的一种改进。在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。
• 临键锁锁定区间和查询范围后匹配值很重要，如果后匹配值存在，则只锁定查询区间，否则锁定查询区间和后匹配值与它的下一个值的区间。（存疑）

# 数据库中id记录有1 5 7
select * from table_tmp where id > 1 and id < 7
# 右区间边界7命中，只锁定(1, 7]

2.4 死锁

概念： 死锁是指两个或两个以上的事务在执行过程 ，因争夺锁资源而造成的一种互相等待的现象。
死锁预防：

• 一次封锁法：事务将需要的数据一次性全部加锁
• 顺序封锁法：预先对数据对象规定封锁顺序，所有事物按照顺序加锁

诊断与解除：

• 超时法。问题：时间太短容易误伤；时间太长不能及时发现。
• 等待图法。数据库保存以下两种信息：锁的信息链表，事务等待链表，在这个图中若存在回路，就代表存在死锁 （死锁检测）

3. 数据库索引

3.1 概念

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

索引的优点

1. 大大加快数据的检索速度，这也是创建索引的最主要的原因；
2. 加速表和表之间的连接；
3. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
4. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

什么样的字段适合创建索引？

1. 经常作查询选择的字段
2. 经常作表连接的字段
3. 经常出现在order by, group by, distinct 后面的字段

3.2 索引的底层实现原理

1）使用B树
在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。
4阶B树：

2） 为什么用B+树：

1. B+树是对B树的一种变形树，它与B树的差异在于：
   有k个子结点的结点必然有k个关键码；
   非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
   树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。

2. 为什么说B+树比B 树更适合实际应用中操作系统的文件索引和数据库索引？

• B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
• B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
• 数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

3. 不使用红黑树原因：

• 红黑树深度比B树大得多，需要更多次IO；
• 扫库时B+树更加方便
• B+树设计时将一个节点设计为CPU读取的页大小（利用空间局部性原理），一个节点一次IO即可完成，红黑树相邻节点可能物理上并不相邻，无法利用局部性原理

B+树：

3.3 聚集索引和非聚集索引区别？

联合索引是指对表上的多个列进行索引，联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2.
最左匹配原则：只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。key index (a,b,c). 可以支持a | a,b| a,b,c

聚集索引和非聚集索引
根本区别：表记录的排列顺序和与索引的排列顺序是否一致。

• 聚集索引：
  数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，叶子结点存储的是表里面的所有行数据，一个表中只能拥有一个聚集索引。可以直接获取到对应的全部列的数据。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
  

• 非聚集索引（除了聚集索引以外的所有索引，有普通索引、唯一索引、全文索引）：
  该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子结点存储的是聚集索引键，而不存储表里面所有的行数据，所以在查找的时候，只能查找到聚集索引键，再通过聚集索引去表里面查找到数据。
  

3.4 避免索引失效

1. 使用组合索引时，需要遵循“最左前缀“原则；
2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
3. MySQL在使用不等于（ != 或者<>）的时候无法使用索引会导致全表扫描；
4. LIKE 以通配符开头 (%abc) MySQL索引会失效变成全表扫描的操作；
5. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换） ;
6. 少用or ，用它来连接时会索引失效。

4.数据库事务

4.1 概念

事务：是数据库恢复和并发控制的基本单位，一条或者一组语句要么全部成功，对数据库中的某些数据成功修改; 要么全部不成功，数据库中的数据还原到这些语句执行。
定义事务的语句：

BEGIN TRANSACTION; //开始
COMMIT; // 结束
ROLLBACK; // 结束

4.2 事务类型

事务可以分为以下几种类型

1. 扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK ROLLBACK WORK结束。处于之间的操作是原子的，要么都执行，要么都回滚。
2. 带有保存点的扁平事务 除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点 (savepoint) 用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。
3. 链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的。
4. 嵌套事务：是一个层次结构框架。有一个顶层事务 (top-level transaction) 控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务 (subtransaction) ，其控制每一个局部的变换。MySQL不支持嵌套事务
5. 分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，同样需要满足ACID特性，要么都发生，要么都失效。

4.3 四大属性ACID

• 原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行
• 一致性（Consistency）：数据库中只包含成功事务提交的结果。一致性是事务追求的最终目标
• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，并发执行的各个事务之间不能相互干扰
• 永久性（Durability）：也叫持久性，事务一旦提交，它对数据库的修改被永久保持

MYSQL的ACID怎样实现的

• 利用undo log保障原子性。该log保存了事务发生之前的数据的一个版本，可以用于回滚，从而保证事务原子性。
• 利用redo log保证事务的持久性。该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行重做，从而使事务有持久性。
• 利用锁机制保证事务的隔离性。事务T对某数据对象操作之前，先向系统请求加锁，T释放锁之前，其他事务不能更新此数据对象。排它锁，共享锁，意向锁。
• 利用锁和log保障一致性。事务中的执行需要redo log，如果执行失败，需要undo 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, 预写式日志），所有修改先写入日志，再更新到BufferPool, 保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

4.4 事务并发控制

事务并发带来的问题

1. 脏读：当前事务 (A) 中可能读到其他事务 (B) 未提交的数据（脏数据）。
2. 不可重复读：在事务A中先后读取同一数据，两次读取结果不一样。事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，先后两次读到的数据结果会不一致。
3. 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，
   在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
   上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

区别

• 脏读和不可重复读的区别在于：前者读到的是其他事务未提交的数据 后者读到的是其他事务已提交的数据。
• 幻读和不可重复读都是读取了另一条已提交的事务，所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表（还是锁相应的索引范围？）

事务隔离

事务隔离是为了解决脏读、不可重复读、幻读问题

MySQL定义了四种隔离级别，包括一些具体规则，用于限定事务内外哪些改变是可见的，哪些改变是不可见的。低级别的隔离一般支持更高的并发处理，并且拥有更低的系统开销。

• 未提交读：事务中的修改即使没提交，对其他事务也是可见的（导致脏读）
• 提交读：一个事务只能读取已经提交的事务所做的修改，在修改提交前对其他事务不可见
• 可重复读：保证在同一个事务中多次读取同样的数据的结果是一样的（mysql默认级别）
• 可串行化：事务串行执行
  ####
  行锁（Record Lock）：锁直接加在索引记录上面。
  间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
  **Next-Key Lock：**行锁与间隙锁组合起来用就叫做Next-Key Lock。

如何实现可重复读？

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁；

在事物A中不用id更新test表（id是主键，所以是有索引的）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set name='TaoLiu' where name='ygz';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在事物B中更新另外一行：事物A中的更新没有基于索引（name没加索引），所以这里由行锁会降级成表锁，所以在事物B中不能对该表进行任何更新，只能读。

mysql> update test set name='ZangLiu' where id=2;

原因：
MySQL InnoDB 引擎，在默认的REPEATABLE READ 的隔离级别下，实现了可重复读，同时也解决了幻读问题.它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外， 该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)（左开右闭区间）。

MySQL InnoDB 引擎，在默认的REPEATABLE REA 的隔离级别下，实现了可重复读，同时也解决了幻读问题。它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外该算法包含了间隙锁，会锁定一个范围，因此解决了幻读的问题。

4.5 MVCC（详解）

Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题：

1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
2. 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

可以形成两个组合，这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题：

1. MVCC + 悲观锁
   MVCC解决读写冲突，悲观锁解决写写冲突
2. MVCC + 乐观锁
   MVCC解决读写冲突，乐观锁解决写写冲突

实现原理

主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。所以我们先来看看这个三个point的概念

隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

• DB_TRX_ID
  6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
• DB_ROLL_PTR
  7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
• DB_ROW_ID
  6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  实际还有一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

undo log

Read View
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本

trx_list（名字随便取的）
// 一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
up_limit_id
// 记录trx_list列表中事务ID最小的ID
low_limit_id
// ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

整体流程

5. 数据库范式

• 第一范式：列不可分，eg:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；
• 第二范式：有主键，保证完全依赖。eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；
• 第三范式：无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

6. 杂项

6.1 分布式全局唯一ID的实现

特点：

• 全局唯一性（核心）：作为唯一标识，不可以出现重复ID
• 趋势递增：在MySQL InnoDB引擎中使用的是聚集索引，由于多数RDBMS使用B-tree的数据结构来存储索引数据，在主键的选择上面我们应该尽量使用有序的主键保证写入性能。
• 单调递增：保证下一个ID一定大于上一个ID，例如事务版本号、IM增量消息、排序等特殊需求。
• 信息安全：如果ID是连续的，恶意用户的扒取工作就非常容易做了，直接按照顺序下载指定URL即可；如果是订单号就更危险了，竞对可以直接知道我们一天的单量。所以在一些应用场景下，会需要ID无规则、不规则。
  同时除了对ID号码自身的要求，业务还对ID号生成系统的可用性要求极高，想象一下，如果ID生成系统瘫痪，这就会带来一场灾难。

运用场景：

• 分布式全局唯一ID（数据库的分库分表后需要有一个唯一ID来标识一条数据或消息；特别一点的如订单、骑手、优惠券也都需要有唯一ID做标识；MQ中消息的高可用性（确认消息是否发送成功，是否已发送等）等）
  其实分布式全局ID是一个比较复杂，重要的分布式问题（什么问题涉及真正的分布式，高并发后都会比较复杂）。常见解决方案有UUID，Snowflake，Flicker，Redis，Zookeeper，Leaf等。

6.1.1 UUID

机器识别码+当前时间 16B
生成一个32位16进制字符串（16字节的128位数据，通常以32位长度的字符串表示）（结合机器识别码（全局唯一的IEEE机器识别号，如果有网卡，从网卡MAC地址获得，没有网卡以其他方式获得），当前时间，一个随机数）。
优点：

• 性能好；
• 扩展性高；
• 本地生成；
• 无网络消耗；
• 不需要考虑性能瓶颈；
• 不需要提前商定，各自为政，但绝对不会冲突

缺点：

• 无法保证趋势递增（由于数据库MySQL的InnoDB采用聚簇索引，有序的ID可以保证写入速度）；
• UUID过长（消耗内存，带宽等。更重要的是如果存储在数据库中，作为主键建立索引效率低）

适用场景：

• 不需要考虑空间占用，不需要生成有递增趋势的ID，且不在MySQL中存储。

6.1.2 Snowflake

时间戳+机器ID（地点标识+机器标识）+序列号 8B
Twitter开源，生成一个64bit（0和1）字符串（1bit不用，41bit表示存储时间戳，10bit表示工作机器id（5位数据标示位，5位机器标识位），12bit 序列号）
优点：

• 趋势递增，且按照时间有序；
• 性能高，稳定性高，不依赖数据库等第三方系统；
• 可以按照自身业务特性灵活分配bit位（比如机器位改为15bit，序列位改为7bit）。

缺点：

• 依赖机器时钟（虽然UUID也根据当前时间，但其非时间部分波动太大了（重新组织措辞）），时钟回拨会造成暂不可用或重复发号（分布式系统中，每台机器上的时钟不可能完全同步。在同步各个服务器的时间时，有一定几率发生时钟回拨（时间超了，往回拨））

适用场景：

• 要求高性能，可以不连续，数据类型为long型。

6.1.3 Flicker

数据库的自增ID+replace_into，来生成全局ID。
前置补充 ：replace into跟insert功能类似，不同点在于：replace into首先尝试插入数据列表中，如果发现表中已经有此行数据（根据主键或唯一索引判断）则先删除，再插入。否则直接插入新数据。
优点：

• 非常简单，充分利用了数据库系统的功能实现，成本小，有DBA专业维护；
• ID号单调自增，可以实现一些对ID有特殊要求的业务。

缺点：

• 强依赖DB，当DB异常时，整个系统不可用，属于致命问题（配置主从复制可以尽可能地增加可用性，但是数据一致性在特殊情况下难以保证。主从切换时的不一致可能导致重复发号）；
• 水平扩展困难（定义好了起始值，步长和机器台数之后，如果要添加机器就比较麻烦（为什么我想到了REDIS的哈希一致原理））；
  ID发号性能瓶颈限制在单台MySQL的读写性能。

适用场景：

• 数据量不大，并发量不大。

6.1.4 Redis

Redis的所有命令是单线程的，所以可以利用Redis的原子操作INCR和INCRBY，来生成全局唯一的ID。
Incrby:将key中储存的数字加上指定的增量值。这是一个“INCR AND GET”的原子操作，业务方可以定义一个自己的key值，通过INCR命令来获取对应的ID
优点：

• 不依赖数据库，灵活方便，且性能优于基于数据库的Flicker方案。

缺点：

• 扩展性低，Redis集群需要设置号初始值与步长（与Flicker方案一样）；
• Redis宕机可能生成重复的ID；如果系统中没有Redis，还需要引入新的组件，增加系统复杂度；
• 需要编码和配置的工作量比较大。

适用场景：

• Redis集群高可用，并发量高。
• 举例：利用Redis来生成每天从0开始的流水号。如订单号=日期+当日自增长号。可以每天在Redis中生成一个Key，适用INCR进行累加。

6.1.5 Zookeeper

通过其znode 数据版本来生成序列号，可以生成32位和64位的数据版本号，客户端可以使用这个版本号来作为唯一的序列号。
小结： 很少会使用zookeeper来生成唯一ID。主要是由于需要依赖zookeeper，并且是多步调用API，如果在竞争较大的情况下，需要考虑使用分布式锁。因此，性能在高并发的分布式环境下，也不甚理想。

6.1.6 Leaf

美团的Leaf分布式ID生成系统，在Flicker策略与Snowflake算法的基础上做了两套优化的方案：Leaf-segment数据库方案（相比Flicker方案每次都要读取数据库，该方案改用proxy server批量获取，且做了双buffer的优化）与Leaf-snowflake方案（主要针对时钟回拨问题做了特殊处理。若发生时钟回拨则拒绝发号，并进行告警）。

总结

方案主要分为两种：第一有中心（如数据库，包括MYSQL，REDIS等），其中可以会利用事先的预约来实现集群（起始步长）。第二种就是无中心，通过生成足够散落的数据，来确保无冲突（如UUID等）。站在这两个方向上，来看上述方案的利弊就方便多了。

中心化方案：

优点：

• 数据长度相对小一些；
• 数据可以实现自增趋势等。
  缺点：
• 并发瓶颈处理；
• 集群需要实现约定；横向扩展困难（当然有的方案看起来后两者没有那么问题，是因为，这些方案利用其技术特性，早就一定程度上解决了这些问题，如Redis的横向扩展等）。

非中心化方案：

优点：

• 实现简单（因为不需要与其他节点存在这方面的约定，耦合）；
• 不会出现中心节点带来的性能瓶颈；
• 扩展性较高（扩展的局限往往集中于数据的离散问题）。
  缺点：
• 数据长度较长（毕竟就是通过这一特性来实现无冲突的）；
• 无法实现数据的自增长（毕竟是随机的）；
• 依赖数据生成方案的优劣（数据生成方案的优劣会全盘接收，但可以推成出新）。

7. 数据库优化

7.1 EXPLAIN（知乎）

7.1.1 基本概念

1. EXPLAIN是什么？
　　使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理你的SQL语句的。分析你的查询与或是表结构的性能瓶颈。
2. EXPLAIN的如何使用?
EXPLAIN的用法比较简单，只要要查询语句前面加上EXPLAIN即可

explain select * from tbl_emp;

运行结果如下

7.1.2 字段介绍

1. id
   select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。其中id的取值分为三种情况：

• id相同，执行顺序由上往下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在
  
  id值如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

2. select_type

• SIMPLE
  简单的select查询，查询中不包含子查询或者UNION。
• PRIMARY
  查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
• SUBQUERY
  在select或where列表中包含了子查询。
• DERIVED
  在from列表中包含的子查询被标记为DERIVER(衍生)，Mysql会递归执行这些子查询，把结果放在临时表中。
• UNION
  若第二个select出现在UNION之后，则被标记为UNION；
  若union包含在from字句的查询中，外层select将被标记为：DERIVER
• UNION RESULT
  从UNION表获取结果的select。

3. table
   显示这一行的数据是关于哪张表的

4. partitions

5. type
   字段type的取值有如下几种:

• ALL
  Full Table Scan，将遍历全表以找到匹配的行。
  
• index
  Full Index Scan,index与All的区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表,但index是从索引中读取的，而all是从硬盘中读取的）
  
• range
  只索引给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、< 、>、in等查询，这种范围扫描索引扫描比权标扫描要好，因为它只需要开始与索引的某一点，而结束于另一点，不用扫描全部索引。
  
• ref
  非唯一性索引扫描，返回匹配某个单独值的所有行。
  本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它会可能找到多个符合条件的行，所以他应该属于查找和扫描的混合体
  
• const
  表示通过索引一次就找到了，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快。如将
  主键置于where列表中，Mysql就能将该查询转化为一个常量。

性能的最好到最差依次是：
system > const > eq_ref > ref > range > index > ALL

6. possible_keys & key

• possible_keys显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
• key为实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
  

7. key_len
   表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精度性的情况下，长度越长越好。key_len显示的值为索引字段的最大可能长度，并非实际使用的长度，即key_len是根据表定义计算而得，不是通过表内检索出来的。
   
   由表可以知道，Mysql以key(col1,col2）为索引，第一个表表示以col1为条件进行查询，精度没有那么高，所需的索引长度为13,第二个表表示以col1,col2为条件进行查询，精度比较高，所需的索引长度为26。

8. ref
   显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
   
   ref列的const表示使用了常数(‘ac’),shared.t2.col1表示使用了share库t2表的col1字段对应查询语句的t2.col1。

9. rows
   根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
   

10. Extra
    包含不适合在其他列中显示但十分重要的信息。
    Extra有如下几个取值：

• Using filesort
  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为“文件排序”
• Using temporary
  使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
• Using index
  表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where,表明索引用来执行索键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
• Using where
  表明使用了where过滤
• using join buff
  表明使用了连接缓存
• impossible where
  where字句的值总是false，不能用来获取任何元组
• select tables optimized away
  在没有group by子句的情况下，基于索引化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct
  优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作