数据库面试相关内容

数据库

1、初始数据库

1.1 什么是数据库

概念：数据仓库，安装在操作系统之上的软件。可以存储大量的数据。

作用：存储数据，管理数据。

1.2 数据库分类

关系型数据库（SQL）

• 通过表和表之间，行和列之间的关系进行数据的存储。
• MySQL、oracle、SqlServer

非关系型数据库（NoSQL）

• 对象存储，通过对象的自身属性来决定。
• Redis、mongdb

1.3 数据库管理系统（DBMS）

• 数据库管理软件，科学有效的管理、维护和获取数据
• MySQL是数据库管理系统

DDL 数据库定义语言

CREATE	创建
ALTER	修改
DROP	删除
TRUNCATE 删除

DML 数据库操作语言

INSERT  插入
UPDATE	更新
DELETE	删除

delete 和 truncate 区别

• 相同点：都能删除数据，都不会删除表结构
• 不同：
  • truncate 重新设置自增列，计数器会归1
  • truncate 不会影响事务

DQL 数据库查询语言

SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

DCL 数据库控制语言

GRANT 授权
ROLLBACK 回滚
COMMIT 提交

2、事务

这是一个超链接

事务：程序中一系列严密的逻辑操作，所有操作必须全部成功完成，否则在每个操作中所作的所有更改都会被撤消。（要么都成功，要么都失败）

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

2.1 事务的产生

数据库中的数据是共享资源，因此数据库系统通常要支持多个用户或多个应用程序的访问，并且各个访问进程都是独立执行的，这样就有可能出现并发存取数据的现象，如果不采取一定的措施则会出现数据异常的情况。为了避免数据库的不一致性，这种处理机制称之为“并发控制”，其中事务就是为了保证数据的一致性而产生的一种概念和手段（事务不是唯一手段）。

2.2 事务四大属性

事务原则（ACID原则）：原子性、一致性、隔离性、持久性

• 原子性（Atomicity）

  要么都成功，要么都失败。

• 一致性（Consistency）

  事务前后的数据完整性保持一致。

  举例：A有10元，B有20元，二者总和30元，A转给B5元，二者总和还是30元

• 隔离性（Isolation）

  多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

• 持久性（Durability）

  事务一旦提交不可逆，被持久化到数据库中。

2.3 隔离所导致的一些问题

脏读

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。

2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。

这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。

虚读（幻读）

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

第一类更新丢失

事务A和事务B都对数据进行更新，但是事务A由于某种原因回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。

第二类更新丢失

其实跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。

2.4 事务隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交，读已提交，可重复读，串行化。事务隔离级别越高，越能保证数据的一致性和完整性，但是执行效率也越低，所以在设置数据库的事务隔离级别时需要做一下权衡，mysql默认是可重复读

读未提交

读未提交(Read Uncommitted)，是最低的隔离级别，**事务A可以读取到事务B修改过但未提交的数据。**只能防止第一类更新丢失，不能解决脏读，不可重复读，幻读，所以很少应用于实际项目。

读已提交

读已提交(Read Committed)，在该隔离级别下，**事务A只能在事务B修改过并且已提交后才能读取到事务B修改的数据。**可以防止脏读和第一类更新丢失，但是不能解决不可重复读和幻读的问题。

可重复读（重要）

可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束前，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。

InnoDB为什么选用可重复读？

在InnoDB存储引擎中，使用可重复读可以解决脏读、不可重复读，而幻读也有可能发生，但是可以避免的，通过加Next-Key Lock锁可以解决幻读问题。并且并非隔离级别越高越好，隔离级别越高的话，并发性能越低，所以在实际的开发中，需要根据业务场景进行选择事务的隔离级别。

串行化

串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

总结

隔离级别	脏读	不可重复读	幻读	第一类更新丢失	第二类更新丢失
读未提交	允许	允许	允许	不允许	允许
读已提交	不允许	允许	允许	不允许	允许
可重复读	不允许	不允许	允许	不允许	不允许
串行化	不允许	不允许	不允许	不允许	不允许

2.5 事务提交方式

显示提交

用COMMIT命令直接完成的提交为显式提交。

BEGIN;
INSERT ...
COMMIT; 

隐式提交

命令操作的事务是不可以回滚（ROLLBACK）的。这些命令主要是一些DDL（数据定义语言）和DCL（数据控制语言）：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT, QUIT，REVOKE，RENAME等

自动提交

插入、修改、删除等DML（数据管理语言）语句执行后，系统将自动进行提交。

insert,update,delete
SET AUTOCOMMIT=0; -- 禁止自动提交
SET AUTOCOMMIT=1; -- 开启自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

2.6 三种日志

redo log（重做日志）：恢复提交事务修改的页操作；通常是物理日志，记录的是页的物理修改操作。

undo log（回滚日志）：回滚记录到某个特定版本；通常是逻辑日志，根据每行记录进行记录。

bin log（二进制日志）：用来进行Point-In-Time(PIT)的恢复及主从复制环境的建立。

四种特性如何保证？

redo log用来保证事务的原子性和持久性。undo log用来保证事务的一致性。而隔离性是通过锁实现的。

bin log和redo log的区别？

1. 重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库上层产生的，二进制日志不仅仅针对InnoDB存储引擎，任何存储引擎都会产生二进制日志。

2. 两种日志的记录内容形式不同。二进制日志是一种逻辑日志，记录的是SQL语句；而重做日志是物理格式日志，记录的是对于每个页的修改。

3. 写入磁盘的时间不同，二进制日志只在事务提交完成后一次写入，而redo log在事务进行中不断的写入。

扩展：回滚机制

在mysql中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在堆数据库中的对应进行写入。

mysql的事务是有redo和undo的，redo操作的所有信息都是记录到重做日志（redo log）中，也就是说当一个事务做commit操作时，需要先把这个事务的操作写到redo log中，然后再把这些操作flush到磁盘上，当出现故障时，只需要读取redo log，然后再重新flush到磁盘就行了。

而对于undo就比较麻烦，mysql在处理事务时，会在数据共享表空间里申请一个段就做segment段，用来保存undo信息，当在处理rollback，不是完完全全的物理undo，而是逻辑undo，也就是说会在之前的操作进行反操作（对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。），但是这些共享表空间是不进行回收的。这些表空间的回收需要由mysql的master thread进程进行回收。

【扩展】Rollback Segment

Rollback Segment是用来临时的保存当数据库数据发生改变时的先前值，它占据一定的存储空间。

Rollback Segment作用：

• 取消某个数据操作，将数据复原至改变之前的值。这种情况只在事务执行过程中生效，一旦执行了COMMIT命令，那么Rollback Segment里面的值就会标识为失效的，数据改变将永久化。
• 如果一个SELECT语句开始读取一个表同时一个事务也在修改这个表的值（尚未提交），那么修改前的值就会保存到Rollback Segment里面，SELECT语句也是从Rollback Segment里面读取表的值。

3、MySQL存储引擎

这是一个超链接

3.1 四种常用存储引擎

• MyISAM存储引擎

  支持3种存储格式：

  • 静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的。
  • 动态表：记录不是固定长度的。
  • 压缩表：每个记录是被单独压缩的，所以只有非常小的访问开支。

• InnoDB存储引擎：写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

• MEMORY存储引擎：将表中的数据保存在内存里，默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

• MERGE存储引擎：一组MyISAM表的组合，这些MyISAM表必须结构完全相同。

3.2 MyISAM和InnoDB区别

	MyISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM的2倍
使用索引	非聚集索引	聚集索引
最小锁粒度	表级锁	行级锁
表的具体行数	用一个变量保存	不保存

MyISAM和INNODB的特点

• MyISAM 节约空间，速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

InnoBD和MyISAM如何选择

1. 如果要支持事务，选择InnoDB
2. 表的绝大多数都是读查询，考虑MyISAM
3. 系统崩溃后，MyISAM恢复更困难

数据库在物理空间存在的位置

一个文件夹就对应一个数据库，本质还是文件的存储！

重启数据库后：

• innoDB 自增列会从1开始（存在内存当中的，断电即失）
• MyISAM 继续上一个自增量开始（存在文件中，不会丢失）

4、数据库的锁

什么是锁，锁的作用是什么？

锁是数据库系统区别文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问，保持数据的完整性和一致性。

数据库锁的分类

使用模式划分	乐观锁、悲观锁
锁的粒度划分	行锁、表锁、页锁
算法锁	临间锁、间隙锁、记录锁
锁级别划分	共享锁（读锁）、排他锁（写锁）
状态锁	意向共享锁、意向排他锁

4.1 悲观锁和乐观锁

这是一个超链接

悲观锁

悲观锁：对外界修改数据持保守态度。总认为读取数据时外界会修改数据。

悲观锁会在修改记录之前，对数据进行加锁。成功加锁后，开始修改记录，事务结束后才解锁。
悲观锁的实现往往依靠数据库提供的锁机制。

悲观锁按照使用性质分为：

• 共享锁（S锁）【shared locks】：就是读锁。多个事务可以一起获得共享锁，可以对数据进行读但不可修改。
• 排他锁（X锁）【exclusive locks】：就是写锁。当一个事务获得了一个数据行的排他锁后，其他事务都不可再获得该数据行的排他锁和共享锁。获得排他锁的事务可以对数据进行读取和修改。
• 更新锁（U锁）：用来预定要对此对象施加写锁，它允许其他事务读，但不允许再施加更新锁或写锁；当被读取的对象将要被更新时，则升级为写锁，主要是用来防止死锁的。

悲观锁按照使用范围分为：

• 行锁：行锁就是锁某行记录。mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引。

  行锁的三种算法：

  Record Lock 记录锁：单条索引记录上的锁

  Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。gap锁的关键就是锁住索引树叶子节点之间的间隙，不让新的记录插入到间隙之中

  Next-Key Lock：Gap+Record Lock锁定一个范围，并且锁定记录本身。

• 表锁：表锁就是锁一整张表，表锁响应的是非索引字段，即全表扫描。

行锁和表锁是排他锁的两种实现，主要用于update,delete,insert这些语句，区别在于是否命中索引。

悲观锁的实现：

1. 在对记录进行修改前，先尝试为该记录加上排他锁。
2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。具体响应方式由开发者根据实际需要决定。
3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
4. 其间如果有其他事务对该记录做加锁的操作，都要等待当前事务解锁或直接抛出异常。

悲观锁适合于写操作较多的情况

• 优点：为数据处理的安全提供了保证。
• 缺点：因为加锁会让数据库产生额外的开销，降低了效率，还有增加了产生死锁的机会，降低了并行性。

乐观锁

乐观锁：认为外界修改数据是概率很小的事件，在提交的时候才去判断是不是存在冲突。

乐观锁是先对数据进行修改，然后判断是不是存在冲突，不冲突就提交事务进行更新。

乐观锁的实现不依靠数据库，而是依靠数据本身

乐观锁的实现：

• 使用时间戳

  在需要乐观锁控制的表中增加一个字段，字段类型使用时间戳，在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则更新，否则就是版本冲突。

  举例：商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，那么我们对某个商品下单时必须确保该商品status为1。

• 版本号控制

  一般在数据表加上一个数据版本号version字段，表示数据被修改的次数。线程A更新数据，读取数据的同时也会读取version值，提交更新时会比较读取的version和当前的version是否一致，相等时才更新，否则重试更新操作，直到更新成功。

乐观锁适合于读操作较多的情况

• 优点：响应效率高，较好的实现了并行，不会产生死锁。
• 缺点：如果冲突效率较高，乐观锁会反复重试，时间效率很低。

并发控制造成的锁

• 活锁

  T1封锁了数据R，T2同时也请求封锁数据R，T3也请求封锁数据R，当T1释放了锁之后，T3会锁住R，T4也请求封锁R，则T2就会一直等待下去。
  解决方法：采用“先来先服务”策略可以避免。

• 死锁

  两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。

  解决方法：一种是超时回滚，一种是采用死锁检测机制

系统判定死锁的方法

• 超时法：如果某个事物的等待时间超过指定时限，则判定为出现死锁；
• 等待图法：如果事务等待图中出现了回路，则判断出现了死锁。

4.2 意向锁

• 意向共享锁(IS Lock)：事务想要获得一张表中某几行的共享锁。
• 意向排他锁(IX Lock)：事务想要获得一张表中某几行的排他锁。

5、索引

5.1 索引原理

这是一个超链接

进阶版超链接

工作机制

索引是对数据库表中一列或多列的值进行排序的一种数据结构，以协助快速查询、更新数据库表中的数据。索引的实现通常使用B树及其B+树。

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。

二叉查找树

性质：左子树键值小于根键值，右子树键值大于根键值

深度为1的节点查找次数为1，深度为2的节点查找次数为2，深度为3的节点查找次数为3，深度为n的节点查找次数为n，上述平均查找次数(1+2+2+3+3+3)/6=2.3次

平衡二叉树（AVLTree）

在二叉查找树的条件下，任何一个节点的两个子树高度差最大为1

如果在平衡二叉树插入或者删除节点就可能导致AVL树失去平衡，失去平衡的二叉树可以分为4种姿态：

• LL（左左）：根节点的左孩子的左孩子还有非空节点，导致根节点左子树高度比右子树高度高2。
• RR（右右）：根节点的右孩子的右孩子还有非空节点，导致根节点右子树高度比左子树高度高2。
• LR（左右）：根节点的左孩子的右孩子还有非空节点，导致根节点左子树高度比右子树高度高2。
• RL（右左）：根节点的右孩子的左孩子还有非空节点，导致根节点右子树高度比左子树高度高2。

AVL树失去平衡后可以通过四种旋转方法使其恢复平衡：

• LL旋转：

  1. 将根节点的左孩子作为新根节点
  2. 将新根节点的右孩子作为原根节点的左孩子
  3. 将原根节点作为新根节点的右孩子

  

• RR旋转：

  1. 将根节点的右孩子作为新根节点
  2. 将新根节点的左孩子作为原根节点的右孩子
  3. 将原根节点作为新根节点的左孩子

  

• LR旋转：

  1. 围绕根节点的左孩子进行RR旋转
  2. 围绕根节点进行LL旋转

  

• RL旋转：

  1. 围绕根节点的右孩子进行LL旋转
  2. 围绕根节点进行RR旋转

  

平衡多路查找树（B-Tree）

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。磁盘的IO次数由树的高度决定，所以减少磁盘IO次数就必须压缩树的高度，B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的关键字，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree性质：

1. 每个节点最多有m个孩子
2. 枝节点最少有Ceil(m/2)个孩子
3. 枝节点关键字数量n满足：Ceil(m/2)<n<m
4. 若根节点不是叶子节点，至少有2个节点
5. 所有叶子节点都在同一层，叶子节点除了包含关键字和关键字记录的指针外也有指向其子结点的指针，只不过是null
6. 所有节点关键字是按递增次序排序，并遵循左小右大

3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
   比较关键字29在区间（17,35），找到磁盘块1的指针P2。

2. 根据P2指针找到磁盘块3，读入内存【磁盘I/O操作第2次】

   比较关键字29在区间（26,30），找到磁盘块3的指针P2。

3. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

   在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，减少了磁盘的I/O操作，从而提高了查询效率。

B-Tree相对于AVLTree怎么缩减了节点个数？

AVL-Tree一个节点只存储一个数据，B-Tree一个节点可以存储多个数据，在同等数据量的情况下，B-Tree缩减了节点个数。

缩减了节点个数使每次磁盘I/O取到内存的数据都发挥了作用？

AVL-Tree一个节点仍占用一个磁盘块（磁盘读写以块为单位），但实际有用数据远小于一个磁盘块的空间，一次磁盘IO读取到一个磁盘块的数据大部分是对索引无用的。

B+树

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B树每个节点保存不仅有关键字key，还有数据data。每个磁盘块空间大小是有限的，data较大时存储的key就会很少，导致B树的结点变多，深度变大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以加大每个节点存储的key值数量，降低B+Tree的高度。

B+树和B-树的区别

1. B树上每个结点都存储了key和data值，而B+树的叶子节点才存储key和data值，除叶子节点外的其他结点存储的是key值。
2. B树不需要到叶子结点就可以找到对应数据，而B+树因为数据都存储在了叶子节点，所以一定要跑到叶子节点才能找到，相对稳定。
3. 因为B+树的非叶子结点只存储了key值，所以一个节点能存储更多的key值，树高度较低，IO次数相对较少
4. B+树的叶子节点用链表连接了前后，支持区间查询
5. B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。

假设每个磁盘块能存储4个键值及指针信息

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

B+树索引的分类

• 聚簇（聚集）索引

  数据和索引一起存储，叶子节点存放的是整张表的行记录数据。（如上图）

• 非聚簇索引

  数据和索引分开存储，叶子节点指向数据对应的位置。

1. InnoDB中，在聚簇索引之上创建的索引称之为主键索引，非聚簇索引属于辅助索引。

   辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据，即回表查询。

2. MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的理解

聚簇索引的顺序，就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。

一张表只允许存在一个聚簇索引，因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引，为它新创建聚簇索引时，就需要对已有数据重新进行排序，所以对表进行修改速度较慢是聚簇索引的缺点，对于经常更新的列不宜建立聚簇索引。

聚簇索引性能最好，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引，所以非常珍贵，必须慎重设置，一般要根据这个表最常用的SQL查询方式选择某个（或多个）字段作为聚簇索引（或复合聚簇索引）。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替（“唯一的非空索引”是指列不能出现null值的唯一索引，跟主键性质一样）。如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。

聚簇索引与唯一索引

聚簇索引不一定是唯一索引，聚簇索引的索引值并不要求是唯一的，唯一聚簇索引才是！在一个有聚簇索引的列上是可以插入两个或多个相同值的，这些相同值在硬盘上的物理排序与聚簇索引的排序相同，仅此而已。

InnoDB常见索引

B+树索引、全文索引、哈希索引

为什么InnoDB采用B+树？

InnoDB采用B+树结构，是因为B+树能够减少单次查询的磁盘访问次数，降低IO、提升性能。B+树的key和data值都存储在叶子结点，非叶子节点只进行数据索引。

为什么要选择自增列当主键？

数据存储在一颗B+树的叶子节点上，这就要求按顺序存放，如果空间够直接放，不够就开辟新的一页存放。如果主键不是自增的，那么也就是当前数据可能会插入任意一个位置，那么原来的结点就要后移，会产生大量的时间开销。

5.2 索引的分类

• **普通索引：**针对数据库表创建索引
• 唯一索引：与普通索引类似，不同的是：MySQL数据库索引列的值必须唯一，但允许有空值
• **主键索引：**它是一种特殊的唯一索引，不允许有空值。一般在建表的时候同时创建主键索引
• **组合索引：**将数据库表中的多个字段联合起来作为一个组合索引。为了进一步榨取MySQL的效率，就要考虑建立组合索引

如何选择在哪些列上建索引？

1. 一张表一般都要建主键，所以主键索引必备的
2. 选择性高的列，也就是重复度低的列。比如女子学校学生表中的性别列，所有数据的值都是女，这样的列就不适合建索引。比如学生表中的身份证号列，选择性就很高，就适合建索引。
3. 经常用于查询的列（出现在where条件中的列）。不过如果不符合上一条的条件，即便是出现在where条件中也不适合建索引，甚至就不应该出现在where条件中。
4. **多表关联查询时作为关联条件的列。**比如学生表中有班级ID的列用于和班级表关联查询时作为关联条件，这个列就适合建索引。
5. **经常修改的列不适合建索引。**因为在数据发生变化时是需要针对索引做一些处理的，所以如果不是有非常必要的原因，不要值会频繁变化的列上建索引，会影响数据更新的性能。反过来也就是说索引要建在值比较固定不变的列上。
6. **一张表上不要建太多的索引。**和上一条的原因类似，如果一张表上的索引太多，会严重影响数据增删改的性能。也会耗费很大的磁盘空间。
7. 表记录少的不适合建索引。

5.3 哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希索引最适合等值查询。不支持范围查询。不支持索引完成排序。不支持最左前缀。

哈希索引和B+树索引的区别

• B+ 树可以进行范围查询，Hash 索引不能。
• B+ 树支持联合索引的最左匹配，Hash 索引不支持。
• B+ 树支持 order by 排序，Hash 索引不支持。
• Hash 索引在等值查询上比 B+ 树效率更高。
• B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

5.4 全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

InnoDB的全文索引的关键词最小索引长度为3

两种全文索引

• 自然语言的全文索引

  默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

  自然语言搜索引擎将计算每一个文档对象和查询的相关度。相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。（搜索的一般是有特殊意义的词）

• 布尔全文索引

  我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

  比如使用(*)星号通配符

  select * test where match(content) against('a*' in boolean mode);
  

5.5 最左匹配原则

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、

**组合索引的最左前缀匹配原则：**使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

5.6 覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

举例子：

假设我们只需要查询商品的名称、价格信息，我们有什么方式来避免回表呢？我们可以建立一个组合索引，即商品编码、名称、价格作为一个组合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

从辅助索引中查询得到记录，而不需要通过聚族索引查询获得，MySQL 中将其称为覆盖索引。使用覆盖索引的好处很明显，我们不需要查询出包含整行记录的所有信息，因此可以减少大量的 I/O 操作。

通常在 InnoDB 中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如， SELECT COUNT(*) 时，如果不存在辅助索引，此时会通过查询聚族索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少 I/O 操作。

6、MVCC

这是一个超链接

这是一个超链接

6.1 什么是MVCC

Multi-Version Concurrency Control（MVCC）是一种多版本并发控制机制，主要是用来实现读写的并发，并且不需要加锁，降低系统开销。

核心思想就是保存一个数据的多个版本号，使得当前读-写不会产生冲突。

上述的读指的是快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。

• **当前读：**它读取的数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据。是悲观锁的一种操作。
• **快照读：**快照读的实现是基于多版本并发控制，即MVCC，既然是多版本，那么快照读读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据。

6.2 MVCC能解决并发的问题

数据库并发的场景

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

解决的问题

• MVCC：可以做到读操作不阻塞写操作，同时写操作也不会阻塞读操作。
• MVCC+悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突
• MVCC+乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

6.3 MVCC的实现

具体实现分析

InnoDB存储引擎的MVCC通过保存数据某个时间的快照来实现，每行数据后面隐藏了两列，分别是创建版本号和删除版本号。

SELECT

InnoDB会根据以下两个条件检查每行记录:

• InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.。
• 行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.。
  只有a,b同时满足的记录，才能返回作为查询结果。

INSERT

InnoDB为每个新增行记录当前系统版本号作为创建ID，删除ID未定义。

DELETE

InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。

UPDATE

InnoDB执行UPDATE，实际上是新插入了一行记录，保存其创建时间为当前事务的ID，并在原来要修改行的删除时间列上添加本事务ID。

实现原理

它的实现原理主要是版本链，undo日志 ，Read View来实现的

版本链

我们数据库中的每行数据，除了我们肉眼看见的数据，还有几个隐藏字段。分别是db_trx_id、db_roll_pointer、db_row_id。

• db_trx_id

  6byte，最近修改/插入事务ID：记录创建这条记录/最后一次修改该记录的事务ID。

• db_roll_pointer

  7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

• db_row_id

  6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。

• 实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了

db_row_id是数据库默认为该行记录生成的唯一隐式主键，db_trx_id是当前操作该记录的事务ID，而db_roll_pointer是一个回滚指针，用于配合undo日志，指向上一个旧版本。

每次对数据库记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表。

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，在根据Read View判断版本可见性的时候会用到。

undo日志（回滚日志）

undo log主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里。

当事务进行回滚时可以通过undo log 里的日志进行数据还原。

undo log 的用途

• 保证事务进行rollback时的原子性和一致性。当事务进行回滚的时候可以用undo log的数据进行恢复。
• 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

undo log主要分为两种

• insert undo log

  代表事务在insert新记录时产生的undo log , 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。

• update undo log（主要）

  事务在进行update或delete时产生的undo log ; 不仅在事务回滚时需要，在快照读时也需要；

  所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

Read View（读视图）

事务进行快照读操作的时候生产的读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(没有commit的事务，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大），是系统中当前不应该被本事务看到的其他事务id列表。

Read View主要是用来做可见性判断的, 即当某个事务执行快照读的时候，对该记录创建一个Read View，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo日志里面的某个版本的数据。

Read View几个属性

• trx_ids：当前系统活跃(未提交)事务版本号集合。

• low_limit_id：创建当前read view 时“当前系统最大事务版本号+1”。

• up_limit_id：创建当前read view 时“系统正处于活跃事务最小版本号”

• creator_trx_id：创建当前read view的事务版本号；

Read View可见性判断条件

1. db_trx_id < up_limit_id || db_trx_id == creator_trx_id（显示）

   • 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务开启之前就已经存在了的,所以可以显示。
   • 数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。

2. db_trx_id >= low_limit_id（不显示）

   数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。

3. db_trx_id是否在活跃事务（trx_ids）中

   是在db_trx_id < low_limit_id的前提下，说明数据是在当前read view创建之前就产生了。

   • 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。
   • 存在：则代表Read View产生的时候，这个事务还在活跃，还没有Commit，修改的数据当前事务也是看不见的。

MVCC和事务隔离级别

Read View用于支持RC（Read Committed，读已提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

RR、RC生成时机

• RC隔离级别下，是每个快照读都会生成并获取最新的Read View；

• RR隔离级别下，是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

解决幻读问题

• 快照读：通过MVCC来进行控制的，不用加锁。按照MVCC中规定的“语法”进行增删改查等操作，以避免幻读。
• 当前读：通过next-key锁（行锁+gap锁）来解决问题的。

RC、RR级别下的InnoDB快照读区别

• 在RR级别下，某个事务的对某条记录的第一次快照读会创建一个快照及Read View，只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见，而早于Read View创建的事务所做的修改均是可见。
• 在RC级别下，事务中，每次快照读都会新生成一个快照和Read View，这就是我们在RC级别下的事务中可以看到别的事务提交更新的原因。

7、数据库的范式

• 第一范式（1NF）

  原子性：保证每一列不可再分

• 第二范式（2NF）

  前提：满足第一范式

  第二范式非主属性完全依赖于码，即确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

  每张表只描述一件事情

• 第三范式（3NF）

  前提：满足第二范式

  第三范式消除传递依赖，即确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

• BC范式（BCNF）

  前提：满足第三范式

  主属性不依赖于主属性，即只有一个候选码或者每个候选码都是单属性

• 第四范式（4NF）

  要求把同一表内的多对多关系删除。

• 第五范式（5NF）

  从最终结构重新建立原始结构。

8、分区和分表

8.1 概念

为什么要分区和分表？

减少数据库的负担，提高数据库的效率，即提高表的增删查改效率。

什么是分区？

把一个数据表的文件和索引分散存储在不同的物理文件中，可以是同一块磁盘，也可以是不同的机器。

什么是分表？

数据表按照一定规则分解成多张具有独立存储空间的实体表，可以称为子表。每个表对应三个文件：.MYD数据文件、.MYI索引文件、.frm表结构文件。这些子表可以分布在同一块磁盘，也可以是不同的机器。

分区和分表的区别

分表是一张表分为多张表，分区在逻辑上只有一张表，底层是由多个物理区组成。

分区和分表的联系

• 都能提高MySQL的性能，在高并发状态下有良好的表现。
• 可以相互配合，对于访问量大的表，可以采用分表和分区相结合的方法，访问量不大的表，可以采用分区的方式。

8.2 分区

分区的类型

1. RANGE分区：把连续区间按范围划分
2. LIST分区：把离散值分成集合，按集合划分，适合有固定取值列的表
3. HASN分区：随机分配，分区数固定
4. KEY分区：类似HASH，但只支持1列或多列，且mysql提供自身的hash函数

分区的优点

1. 存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或文件系统相比，可以存储更多数据。
2. 优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
3. 更容易维护。想批量删除数据就可以清除整个分区。

分区的限制

1. 一个表最多只能有1024个分区。
2. 分区表无法使用外键约束。
3. 分区字段不能为NULL，不然怎么确定分区范围。
4. MySQL的分区适用于一个表的所有数据和索引，即所有数据和索引都进行分区。
5. 分区字段要么主键列和唯一索引列都必须包含进来，要么都不包含。
6. MySQL5.1中，分区表达式必须是整数，或者返回整数表达式。MySQL5.5提供了非整数表达式的分区的支持。

8.3 分表

分表的类型

1. 垂直分表

   将一张表的不同字段放到一张表上，原则是：

   • 常用的、不常用的字段分开放
   • 大字段独立放在一个表里

2. 水平分表

   将一张表的不同数据行分为多张表，通常采用hash、取模的方式分表。

分表的优点

减少单张表的访问压力，减少数据库的负担，缩短查询时间。

SQL优化

SQL优化策略适用于数据量较大的场景下，数据量小的就没必要

1. 避免不走索引的场景

1. 避免在字段开头模糊查询，尽量在字段后面使用模糊查询，否则会造成全表扫描（like ‘%陈%’ -> like ‘陈%’）
2. 避免使用in和not in，可以用between和exits代替
3. 避免使用or，可以用union代替
4. 避免使用null值判断，可以把字段的默认值设为0进行判断
5. 避免在where条件中等号的左侧进行表达式、函数操作，可以将表达式、函数操作移动到等号右侧
6. 当数据量大时，避免使用where 1=1的条件，可以用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
7. 查询条件不能用 <> 或者 !=，避免在此字段上建立索引，改由查询条件中其他索引字段代替
8. 隐式类型转换会造成不使用索引
9. order by 条件要与where中条件一致，否则order by不会利用索引进行排序
10. 正确使用hint优化语句

2. SELECT语句其他优化

1. 避免出现select*，而是使用具体字段
2. 避免出现不确定结果的函数
3. 多表关联查询时，小表在前，大表在后
4. 使用表的别名
5. 用where字句替换HAVING字句
6. 调整where字句中的连接顺序

3. 增删改DML语句优化

1. 大批量插入数据

   insert into T values(1,2),(1,3),(1,4);
   

2. 适当使用commit释放事务占用的资源而减少消耗

3. 避免重复查询更新的数据

4. 确定查询优先还是更新（insert、update、delete）优先

4. 查询条件优化

1. 对于复杂的查询，可以使用中间临时表暂存数据
2. 优化group by语句
3. 优化join语句
4. 优化union查询
5. 拆分复杂SQL为多个小SQL，避免大事务
6. 使用truncate代替delete
7. 使用合理的分页方式以提高分页效率

5. 建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段
2. 尽量使用数字型字段
3. 查询数据量大的表，会造成查询缓慢
4. 用varchar/nvarchar 代替 char/nchar，变长字段存储空间小，可以节省存储空间，搜索效率也更高

为什么索引能加快搜索速度？

数据库在执行一条sql语句时，默认使用全表扫描，遇到匹配条件就加入到搜索结果集合，时间复杂度为O(n)。如果对某一字段建立索引，查询就会到索引列表中一次定位到特定值的行数，如果采用B+树，查询的时间复杂度就是O(logn)，明显能加快搜索速度。

索引失效的情况

• 对索引使用左或者左右模糊匹配

  select * from t_user where name like '%林';
  

  B+树是按照索引值有序排列存储的，只能根据前缀进行比较，这样导致数据库不知道从哪个索引值开始比较。

• 对索引使用函数

  select * from t_user where name length(name) = 6;
  

  索引保存的是索引字段的原始值，而不是函数计算后的值。

  MySQL 8.0开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，这样就可以使用索引查询数据了。

• 对索引进行表达式计算

  select * from t_user where id + 1 = 10;
  

  索引保存的是索引字段的原始值，而不是表达式计算后的值。

• 对索引隐式类型转换

  //phone 是 char id 是 int
  select * from t_user where phone = 13000;//全表扫描
  select * from t_user where id = '1';//索引扫描
  

  MySQL 遇到字符串和数字比较时，会自动把字符串转化为数字，然后再进行比较

• 联合索引非最左匹配

  联合索引下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序，如果我们仅仅按照第二列搜索，肯定无法走索引。

• WHERE 子句中的 OR

  OR的含义是两个只要满足一个即可，所以只要有条件列不是索引列就会进行全表扫描。

MySQL优化（重要）

• 高频访问
  • 分表分库：将数据库表水平拆分，减少表的长度
  • 增加缓存：在web服务器和数据库之间加一层缓存层，减少数据库负担
  • 增加数据库索引：在合适的字段建立索引，解决高频访问的问题
• 并发优化
  • 主从复制读写分离：一主多从，只在主库上写，从库上读，主库会自动将数据同步到从库
  • 负载均衡集群：通过集群或者分布式的方式解决并发压力

主从复制

• 主库的更新事件会被写到二进制日志中
• 从库启用slave服务，发起连接，连接到主库
• 从库创建一个I/O线程，从主库读取bin log二进制日志的内容并写入到relay log中继日志中
• 从库创建一个SQL线程，从relay log里面读取内容，将更新内容写入到从库

聚集索引和非聚集索引的区别

• 聚集索引数据和索引一起存储，叶子节点包含行记录的所有记录，非聚集索引数据和索引分开存储，叶子节点存放数据的地址
• 聚集索引只有一个表，非聚集索引可以有多个表
• 聚集索引存储记录在物理是连续的，而非聚集索引在逻辑是连续的，物理存储并不连续
• 聚集索引物理存储按照索引排序，非聚集索引物理存储不按照索引排序

SQL注入

程序没有对用户输入数据的合法性进行判断和处理，导致攻击者可以在Web应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。

例子：

用户名：'or 1='1
name = '' or 1 ='1' and password = '123456'

原理

• 恶意拼接连接

  SELECT * FROM USERS WHERE USER_ID = 1234;DELETE FROM USERS
  

• 利用注释执行非法指令

• 传入非法参数

• 添加额外条件

产生的原因

• 传递给后端的参数是可控制的
• 参数内容会被带入到数据库查询

解决方案

• 采用预编译技术
• 严格控制数据类型
• 对特殊字符进行转义

数据库的创建过程

1. 需求分析：了解用户的数据需求、处理需求、安全和完整性需求。
2. 概念设计：通过数据抽象，设计系统的概念模型，一般为e－r模型。
3. 逻辑结构设计：设计系统的模式和外部模式，特别是关系模型的基本表和视图。
4. 物理结构设计：设计数据的存储结构和访问方法，如索引的设计。
5. 系统实现：组织数据存储，编写应用程序，试运行。
6. 运行和维护：系统投入运行，进行长期维护。

外键的优缺点

一般不建议在数据库层面使用外键，应用层面可以解决。

优点：

• 保证了数据库数据的一致性和完整性
• 级联操作方便，减轻了程序代码量

缺点：

• 增加了复杂性。每次DELETE或者UPDATE都必须考虑外键约束
• 增加了额外工作。数据库需要增加维护外键的工作，比如当我们做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，保证数据的一致性和正确性，这样会不得不消耗资源
• 会因为需要请求对其他表内部加锁而容易出现死锁情况
• 对分库分表不友好。因为分库分表下外键是无法生效的

CAS算法

意思是比较并替换。需要有3个操作数：内存地址V，旧的预期值A，要更新的目标值B。CAS指令执行时，当且仅当内存地址V的值和预期值A相等，将内存地址V的值修改为B，否则就什么都不做。整个比较并替换的操作是一个原子操作。

CAS可用于乐观锁，当多个线程尝试使用CAS同时更新共享变量时，只有其中一个线程能更新变量的值，其他线程都失败，失败的线程不会被挂起，而是被告知这次竞争失败，并可以再次尝试。

缺点：

• 循环时间长开销就大

  如果CAS一直失败，就会一直尝试，可能会给CPU带来很大的开销

• 只能保证一个共享变量的原子操作

  当对一个共享变量执行操作时，我们可以使用循环CAS的方式来保证原子操作，但是对多个共享变量操作时，循环CAS就无法保证操作的原子性，这个时候就可以用锁来保证原子性。

• 产生ABA问题

  内存地址V初次读取的值是A，期间被改为B，后再被改为A，检查的时候发现是A没错，这种就叫ABA问题，可以通过控制变量值的版本来保证CAS的正确性，就是判断变量值被修改的次数。

优点：

可以避免优先级倒置和死锁等危险，协调发生在更细的粒度级别，允许更高程度的并行机制

MySQL的架构

• Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一个通用的日志模块binlog日志模块
• 存储引擎：主要负责数据的存储和读取。server层通过api与存储引擎进行通信。

MySQL一条语句的执行过程

• 连接器：客户端首先通过连接器连接到MySQL服务器。
• 缓存：连接器经过权限验证后，先查询之前是否有执行过此语句（有缓存），若有则直接返回缓存数据，若无，进入分析器。
• 分析器：分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
• 优化器：优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
• 执行器：优化器执行完就进入执行器，执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。

怎么判断是否命中索引

使用explain命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。在 select 语句之前增加 explain 关键字MySQL会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL。

事务的两阶段提交

• 第一阶段（prepare）：写redo-log并将其标记为prepare状态
• 第二阶段（commit）：写bin-log并将其标记为commit状态

count(1)、count(*)和count(列名)的区别

执行效果上：

• count(*)和count(1)都是包含了所有列，不会忽略NULL值
• count(列名)只包含列名的那一列，忽略列值为空的

执行效率上：

• 如果有主键，count(主键)的执行效率是最优的，若无主键，count(1)比count(*)运行效率要快
• 若整个表只有一个行，count(*)的运行效率最快；若多于一个行时，count(1)要比count(*)运行效率快。因为count(*)会扫描整个表。

Linux下安装MySQL的方式

• rpm包安装
• yum安装。sudo apt-get install -y mysql-server
• 源码包安装

在Linux使用Mysql

1、打开数据库

sudo mysql -u root -p1

linux下使用c++操作mysql

编译：

g++ mysql.cpp -I/usr/include/mysql -L/usr/bin/mysql -lmysqlclient