基础知识复习--数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

基础知识

数据库三大范式

1NF：第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，并且有一个主键

2NF：第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关

3NF：第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关，不能产生传递依赖。

MySql如何执行一条SQL语句

1. 客户端请求->
2. 连接器（验证用户身份，给予权限） ->
3. 查询缓存（存在缓存则直接返回，不存在则执行后续操作）->
4. 分析器（对SQL进行词法分析和语法分析操作） ->
5. 优化器（主要对执行的sql优化选择最优的执行方案方法） ->
6. 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）->
7. 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

索引

索引的分类

单一索引：给单独的一个字段添加索引

复合索引：给多个字段联合起来添加索引

主键索引：主键上自动添加的索引

唯一索引：有unique约束的字段上会自动添加索引

索引的优点

索引类似于一本书的目录，可以通过索引快速找到对应的资源；

在数据库中，查询一张表的时候有两种检索方式：全表扫描和索引检索（效率高）

索引的设计原则

数据量庞大时使用

索引字段很少进行DML操作（因为字段进行修改操作时，索引也需要维护）

经常根据某个字段进行查询

尽量使用数据量较少的字段作为索引

限制索引的数量，删除不再使用或者很少使用的索引

索引的数据结构

索引的数据结构和具体的存储引擎实现有关，MySql中常用的是B+树和Hash索引。

Hash索引底层就是哈希表，查询时调用哈希函数获取相应的键值，然后查询表获得实际数据

B+树索引底层实现原理是多路平衡查找树，对于每次查询都从根节点出发，查询到叶子节点才能查询到键值，最后判断是否要回表查询

Hash和B+树索引的区别

Hash进行等值查询更快，但是无法进行范围查询（hash原理决定的无序），而B+树本质上是一棵查找树，自然支持范围查询和排序

Hash任何时候都要回表查询数据，在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询

Hash虽然在等值查询时效率比较高，但是性能不稳定，当某个键值存在大量重复时产生Hash碰撞效率反倒会降低，B+树查询效率比较稳定，因为每次查询都是从根节点到叶子节点

采用B+树作为索引的原因：

一、从内存角度上说，数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。

二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。

为什么使用B+树而不使用二叉树或B树做索引

二叉树

如果索引数据量很大时，数的高度会很大，查询会变慢，查找效率是O（logn）

二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数太多

B树

不再是二叉搜索，而是N叉搜索，树的高度会降低，查询速度块

叶子节点和非叶子节点都可以存储数据，并且可以存储多个数据，每个节点存储子节点数-1的关键字，子节点数>=阶数/2

通过中序遍历可以访问树上所有的节点

B+树

B树的叶子节点和非叶子节点都存储数据，因此查询数据的时候时间复杂度最好为O(1)，最差为O(logn)，而B+树只有叶子节点存储数据，非叶子节点存储关键字，时间复杂度固定在O(logn)。

B+树叶子节点之间用链表连接，因此只需要扫描叶子节点的链表就可以完成一次遍历操作，B树只能通过中序遍历。

为什么B+树比B树更适合用于数据索引？

1. B+树会减少磁盘IO次数：由于索引文件很大，因此索引文件会存储在磁盘上，B+树的非叶子节点只存关键字不存数据，单个页会存储更多的关键字，一次性读入内存的关键字越多，磁盘IO会减少
2. B+树查询效率更稳定
3. B+树更适合范围查找：B+树叶子节点之间用链表有序链接，所以扫描全部数据可以扫描叶子节点，有助于数据库范围查询，而B树非叶子节点也会存储数据，所以只能通过中序遍历扫描；对于范围查询和有序遍历来言B+树效率更高

什么是回表？

Mysql回表指的是在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)。简单来说，回表就是 MySQL 要先查询到主键索引，然后再用主键索引定位到数据

存储

MySQL存储引擎（MyISAM和InnoDB）

区别：

1. InnoDB支持事务，MyISAM不支持
2. InnoDB支持外键，MyISAM不支持
3. 二者都支持B+树索引，但是InnoDB是聚集索引（聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页），MyISAM非聚集索引（辅助索引，数据不存储在索引上，索引指向数据存放位置）
4. InnoDB不记录表的行数，MyISAM会用一个变量记录整个表的行数
5. InnoDB支持表、行（默认）级锁，MyISAM只支持表级
6. InnoDB必须有唯一索引，如果没有会寻找或者自动生成一个隐藏列来充当主键，MuISAM可以没有主键

默认使用InnoDB，MyISAM适合插入为主的情况，比如博客系统、新闻门户等经常更新的场景

表锁

不会出现死锁，发生锁冲突几率高，并发低。

MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：表共享读锁和表独占写锁。

读锁会阻塞写，写锁会阻塞读和写

• 对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
• 对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

行锁

会出现死锁，发生锁冲突几率低，并发高。

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

行锁的实现需要注意：

• 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
• 如果是共享锁，两个事务可以锁同一个索引，排它锁则不能。
• insert，delete，update在事务中都会自动默认加上排它锁。

行锁的适用场景：

A用户消费，service层先查询该用户的账户余额，若余额足够，则进行后续的扣款操作；这种情况查询的时候应该对该记录进行加锁。

否则，B用户在A用户查询后消费前先一步将A用户账号上的钱转走，而此时A用户已经进行了用户余额是否足够的判断，则可能会出现余额已经不足但却扣款成功的情况。

为了避免此情况，需要在A用户操作该记录的时候进行for update加锁

存储结构

InnoDB的页、区和段：

• 页：将物理磁盘划分为页，每页的大小默认为16kb，页是最小的存储单位
• 区：如果只有页一个层次，页的个数是非常多的，存储空间的分配和回收都很麻烦，因为要维护这么多页的状态非常麻烦，因此就引入了区（Extent）的概念，一个区默认是64个连续的页组成
• 段：B+树的叶子节点存放的是具体的数据，非叶子节点是索引页，因此每个索引都会创建两个Segment去存放对应的数据

事务

什么是数据库的事务？

一个事务是一个完整的业务逻辑单元，不可再分：

#比如A向B转账：
update account set balance = balance - 100 where cusno = '00001';
update account set balance = balance + 100 where cusno = '00002';

DML(Data Manipulation Language)语句:数据操纵语句

以上DML语句必须同时成功或者失败，不允许一条成功另一条失败；为了保证同时成功或失败，就需要使用数据库的事务机制。

事务的四大特征

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1 、原子性。事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

2 、一致性。执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

如何保障数据库事务的ACID特性？

原子性

主要利用InnoDB的回滚日志，当事务回滚时能够撤销所有已经成功执行的SQL语句，undo log记录了回滚需要的信息，当事务执行失败就利用日志中的信息去将数据rollback修改回之前的样子

一致性

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

隔离性

脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

不可重复读：一个事务中两次读取的数据内容不一致。事务A多次读同一数据，事务B在事务A多次读取过程中对数据做了更新提交，导致A多次读取同一数据时结果不一致。

幻读：一个事务中两次读取的数据量不一致。系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

数据库的隔离级别：

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED 未提交读	√	√	√
READ-COMMITTED 提交读	×	√	√
REPEATABLE-READ 重复读	×	×	√
SERIALIZABLE 可串行化读	×	×	×

未提交读：对方事务未提交但是我们可以读到

读已提交：对方事务提交之后我方可以读

可重复读：解决一个事务中两次读取的数据内容不一致的问题

可串行化：解决所有问题，但是效率低

oracle 数据库默认的隔离级别是读已提交；mysql 数据库默认的隔离级别是可重复读。

InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别 下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，达到串行化读

持久性

利用InnoDB的redo log，当数据修改时，不仅在内存中操作，还会在redo log中记录这次操作，当事务提交时，会将redo log进行刷盘，当数据库宕机时会将redo log中的内容恢复到数据库，再根据undo log和bin log内容决定提交数据还是回滚数据

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

锁

数据库锁的分类及作用

锁是用来解决并发事务的数据不一致问题的

锁的粒度

行级锁：是最细粒度的一种锁，表示只针对当前操作的行进行加锁，可以大大减少数据库操作的冲突，开销最大，加锁慢，会出现死锁，但是锁冲突概率低，并发度高

表级锁：粒度最大的一种锁，表示对当前操作的整张表进行加锁，实现简单，资源消耗少，被大部分MySQL引擎支持

页级锁：粒度介于以上之间的一种锁，折中，一次锁定相邻的一组记录

使用性质

共享锁：又称为S锁、读锁，用于对所有的只读数据操作；S锁非独占，允许多个并发事务对同一资源加锁，但是加S锁的同时不允许加X锁，即资源不能被修改，S锁读取结束立即释放

排他锁：X锁、写锁，表示对数据进行写操作，X锁只允许一个事务对同一资源加锁，事务结束才释放，其他事务必须等待X锁释放才能对该页进行访问

更新锁：U锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。当被读取的页将要被更新时，则升级为 X 锁，U 锁一直到事务结束时才能被释放。故 U 锁用来避免使用共享锁造成的死锁现象。

主观

乐观锁：从主观上认定资源是不会被修改的，所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否被修改。
乐观锁适用于多读的应用类型，可以系统提高吞吐量。

悲观锁：具有强烈的独占和排它特性，每次读取数据时都会认为会被其它事务修改，所以每次操作都需要加上锁

隔离级别和锁的关系

读未提交级别下读数据不需要加共享锁，这样就不会跟修改数据上的排他锁冲突

读已提交级别下读数据需要加共享锁，语句执行完之后释放

可重复读级别下读需要加共享锁，但是事务提交之前不释放共享锁，必须等待事务执行完毕释放共享锁

串行化限制最强，锁定整个范围的键，并一直持有锁，直到事务完成

快照读和当前读

快照读读取的就是快照数据，不加锁的简单select都是快照读

当前读就是读取最新数据而不是历史数据

MVCC及实现

MVCC多版本并发控制，可以做到读写相互不阻塞，用于解决不可重复读和幻读是提高并发效率

原理是通过多个版本管理来实现数据库的并发控制，保存数据的历史版本，通过比较版本号决定数据是否显示，读取数据时候不用加锁就可以保证事务的隔离效果

语句

语句书写和执行顺序

(7)     SELECT 
(8)     DISTINCT <select_list> 
(1)     FROM <left_table> 
(3)     <join_type> JOIN <right_table> 
(2)     ON <join_condition> 
(4)     WHERE <where_condition> 
(5)     GROUP BY <group_by_list> 
(6)     HAVING <having_condition> 
(9)     ORDER BY <order_by_condition> 
(10)    LIMIT <limit_number>

序号代表执行顺序