数据库知识点总结

1.数据的ACID是什么？

ACID，是指在可靠数据库管理系统（DBMS）中，事务所应该具有的四个特性

原子性（Atomicity）：数据库中的事务执行时作为原子。即不可再分，整个语句要么执行，要么不执行
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏，体现在两个层面：①数据库机制层面②业务层面
隔离性（lsolation）：事务执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据
持久性（Durability）：事务完成以后，该事务对数据库所作的更改便持久的保存在数据库中，并不会被回滚

2.并发的事务可能导致其他事务出现什么情况？

读脏：读到了未提交事务操作的记录
可重复读：一个事物内相同的查询，得到了不同的结果
幻读：当事物不是独立执行时发生的一种现象

3.InnoDB实现了哪几种事务的隔离级别？

读未提交：select不加锁，可能会出现脏读（并发最高，一致性最差）
读提交（RC）：普通select快照读，锁select/update/delete会使用记录锁，可能出现不可重复读
可重复读（RR）：普通select快照读，锁select/update/delete，根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读到幻影记录
串行化：select 隐式转化为select ... in share mode, 会被update与delete互斥（一致性最好，并发性最差）

4.数据中有哪些常见的锁，分别有什么功能？

共享锁（弱锁）：读取数据时加了S锁（共享锁之前互不排斥，读读可以并行）
排他锁（强锁）：修改数据时加X锁（排他锁与任何锁互斥，写读，写写不可并行）
意向锁（弱锁）：未来的某个时刻，事务可能要加共享/排它锁，先提前声明一个意向（表级别）
插入意向锁：间隙锁的一种，实施在索引上，它是专门针对insert操作的（提高插入并发），功能：多个事务，同一索引，同一范围区间插入记录，如果插入的位置不冲突，不会阻塞彼此
记录锁：锁定索引记录
间隙锁：锁定间隔，防止间隔中被其他事务插入
临键锁：锁定索引记录+间隔，防止幻读
自增锁：表级别锁：针对AUTO_INCREMENT类型的列，如果一个事务正在往表中插入记录，其他事务插入必须等待，以使第一个事务插入的行是连续的主键值

5.如何提高数据库的并发呢？

数据库多版本是一种能够进一步提高并发的办法

核心原理：

思路：普通锁（本质是串行化）-->读写锁（可以实现读读并发）-->数据多版本（可以实现读写并发）

写任务发生时，将数据克隆一分，以版本号区分

写任务操作新克隆的数据，直至提交

并发读任务可以继续读取读旧版本的数据，不至于阻塞

6.什么是乐观锁和悲观锁？怎么实现？缺点？应用场景？

悲观锁：认为被它保护的数据是极其不安全的，每时每刻都有可能变动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只有等待锁被释放才可以执行

乐观锁：允许多个事务同时对数据进行变动，但是乐观不代表不负责，乐观锁通常在表中增加一个版本或时间戳来实现

实现

悲观：A不放手， B一直等，等到时已经为0

乐观：更新版本，A更新了， B也更新，但B更新的数据为0

缺点：悲观影响系统吞吐量，乐观如果有外来事物插入，可能出错

应用场景：悲观适用于写居多的场景，乐观适用于读居多的场景

7.什么是索引，为什么数据库要设计索引？

索引是对数据库表一个或多个列的值进行排序的数据结构。协助快速查询，更新数据库表中数据，索引可以提高数据的查找速度，加快表和表之间的连接, 而且索引不用遍历，直接定位

8.哈希比树更快，索引结构为什么还要设计成树型？

哈希，时间复杂度O(1) 注意：InnoDB并不支持

树，时间复杂度O(log(n))

如果单行查询，当然哈希更快，但是对于排序查询， group by, order by, <, > 哈希会退化为O(N)树形有序特性，依然O(log(n))

9.数据库索引为什么适用B+树？

第一种（X）：二叉搜索树

①数据量大的时候，树的高度会比较高，查询会比较慢 ②每个节点只存储一个记录，可能导致一次查询有很多次磁盘I/O

第二种（√）：B树

①不再是二叉搜索，而是m叉搜索，高度大大降低

②叶子结点，非叶子节点都存储数据，设置为页的大小，充分利用预读

③中序遍历，可以获得所有节点

B树能够完美的利用“局部性原理”

局部性原理：

①内存读写快，磁盘读写慢，而且慢很多

②磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次都加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘I/O,提高效率

第三种（√√）：B+树

①非叶子节点不再存储数据，数据只存储在同一层的叶子结点上

②叶子之间，增加了链表，获取所有节点，不再需要中序遍历

10.为什么B+树比B树好？

①范围查找，定位min, miax后，中间叶子节点，就是结果集，不用中序回溯

②叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储，非叶子节点存储记录的PK，用于查询加速，适合内存存储

③非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么相同内存情况下， B+树能够存储更多索引

11.MySQL常用存储引擎是什么？有什么区别？

MyISAM（默认），不支持事务，支持全文本搜索，没有主键，不支持外键

InnoDB，支持事务，不支持全本文（可是使用sphinx插件），有主键，支持外键

存储结构：①MyISAM（可被压缩，存储空间较小）②InnoDB（需要更多的内存和存储，他会在主内存内建立其专用的缓冲池用于高速缓冲数据和索引）
可移植性、备份及恢复：①MyISAM（数据以文件形式存储，跨平台的数据转移很方便）②InnoDB（拷贝数据文件，备份binlog，或者mysqldump，数据量大不好使）
事务支持：①MyISAM（强调性能，每次查询都具有原子性，其执行速度比InnoDB类型更快）②InnoDB（除了事务、外部键等高级数据库功能都支持，事务、回滚、崩溃修复的事务安全safe型表）
AUTO_INCREMENT：①MyISAM（可以和其他字段一起建立联合索引）②InnoDB（必须包含只有该字段的索引）
表锁差异：①MyISAM（只支持表级锁）②InnoDB（支持事务和行级锁）
二级索引：①MyISAM（无论主键/二次索引，叶子节点存放的都是指向数据行的指针，会页分裂）②InnoDB（二级索引存放的是主键值，查询对应的值需要回表查询，不会页分裂）

12.什么是主从复制、读写分离、作用以及场景是什么？

主从复制：建立一个和主数据库完全一样的数据库环境称为从数据库，主数据库一般是准实时的业务数据库

读写分离：一主多从，读写分离主动同步，主库提供写，从库提供读，主从之间某种机制同步，比如MySQL的binlog

作用：避免数据丢失，降低I/O访问频率，读写分离，支撑更大的并发

应用场景：写很慢，读很快，适用于海量读取的场景

binlog二进制文件，记录了所有的sql语句，复制，再执行，需要三个线程：binlog输出线程，从库I/O线程，从库SQL线程

13.索引分哪几类？

普通索引，数据可重复——index
全文索引，用来对大表的文本域索引——fulltext
唯一索引，所有记录都唯一——unique
主键索引，在唯一索引基础上相应的列必须为主键——primary key
聚簇索引，按照物理存储划分，唯一
非聚簇索引，大范围转小地图

14.数据库的范式有哪些？

第一范式：列不可分 e.g：表中电话一个联系人有家庭电话和公司电话则不符合INF
第二范式：有主键，保证完全依赖（都依赖主键）
第三范式：无传递依赖（非主键列A依赖于非主键列B，非主键列B依赖于主键的情况）

15.什么情况下设置了索引但无法使用？

以“%”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换成int型）
对于多列索引，必须满足最左匹配原则

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

经常作查询选择的字段
经常作表连接的字段
经常出现在order by， group by， distinct后面的字段

17.创建索引需要注意些什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL. 在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引，索引的统计信息以及比较运算更加复杂
取值离散大的字段：（变量名各个取值之间的差异程度）的列放到联合索引的前面，可以通过count（）函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段离散程度高
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大，效率越高