你好offer之--数据库

数据库

数据库的三大范式

第一范式

每个列都不可以再拆分、所有的属性必须是原子级别的

第二范式

非主属性完全依赖于主键，不能只依赖主属性的一部分

第三范式

非主键只依赖主键，不存在循环依赖

数据库连接泄露

如果在某次使用或者某段程序中没有正确地关闭 Connection、Statement 和 ResultSet 资源，那么每次执行都会留下一些没有关闭的连接，这些连接失去了引用而不能得到重新使用，因此就造成了数据库连接的泄漏

使用连接池+短连接的设置来解决 数据库连接泄露

触发器

在满足定义条件时触发，并执行触发器中定义的 语句集合

索引

索引是一种 数据结构。数据库索引是 DBMS 中一个 排序的数据结构，以 协助快速查询、更新 数据库表中数据。索引的实现通常使用 B 树以及变种 B+ 树或者哈希。

索引就相当于目录，其存在是为了方便数据内容查找，本身也占用物理空间。

索引的优点

• 可以加快数据的 检索速度
• 通过创建 唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以加速表和表之间的连接

索引的缺点

• 时间上，创建和维护索引都要耗费时间，这种时间随着数据量的增加而增加，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；
• 空间上，索引需要占 物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的数据结构

B 树索引、Hash 索引 和 位图索引

B+树的好处

• 由于 B+ 树的内部结点只存放键，不存放值，因此，一次读取，可以在同一内存页中获取更多的键，有利于更快地缩小查找范围。

• B+ 树的叶结点由一条链相连，因此当需要进行一次 全数据遍历 的时候，B+ 树只需要使用 O(logN) 时间找到最小结点，然后通过链进行 O(N) 的顺序遍历即可；或者，在找 大于某个关键字或者小于某个关键字的数据 的时候，B+ 树只需要找到该关键字然后沿着链表遍历即可。

B+树索引和Hash索引的区别

• Hash 索引进行等值查询更快（一般情况下），但是却无法进行范围查询；
• Hash 索引不支持使用索引进行排序；
• Hash 索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为 Hash 函数的不可预测；
• Hash 索引任何时候都避免不了回表查询数据，而 B+ 树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询；
• Hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 Hash 碰撞，此时效率可能极差；而 B+ 树的查询效率比较稳定，对于所有的查询都是从根结点到叶子结点，且树的高度较低。

前缀索引

有时需要索引很长的字符列，它会使索引变大并且变慢，一个策略就是索引开始的几个字符，而不是全部值，即被称为 前缀索引。
前缀索引的缺点是索引值重复性越低，查询效率也就越高。相应的，索引值重复性越高，查询效率也就越低

最左前缀匹配原则

在 MySQL 建立 联合索引（多列索引） 时会遵守最左前缀匹配原则，即 最左优先，在检索数据时从联合索引的最左边开始匹配。

添加索引的原则

• 为频繁访问的数据创建索引
• 数据经常修改的不建立索引
• 数据很少的时候也不建立索引

聚簇索引和非聚簇索引

聚簇索引就是将数据和索引放在一起，因此从聚簇索引中获取数据比非聚簇索引更快。InnoDB支持聚簇索引，非叶子节点不存放数据，叶子节点存放主键以及对应的行记录

数据库的事务

包含了一组数据库操作命令。
其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。

数据库的分布式任务：

CAP一致性可用性和分区容错性

两阶段提交：

使分布式数据库的所有节点在事务提交都保持一致性而设计的一种算法
参与者将操作失败通知告诉协调者，
再由协调者根据所有参与者的反馈决定其他参与者提交操作还是终止操作。

三阶段提交：

引入超时节点：如果长时间接不到参与者的反馈，则认为参与者失败
在第一阶段和第二阶段提交中间加入一个预准备阶段，以保证在任务提交之前参与者状态是否一致。

where、groupby、having

where先执行，再groupby分组；groupby先分组，having在执行

索引

索引分为主键索引，唯一索引，联合索引和普通索引

如果非聚簇索引查询的字段，完全命中了索引，索引就不需要回表查询，这个过程被称为索引覆盖

MySQL使用自增主键的好处：

插入数据时减少分页和叶子的移动

MVCC

MVCC并发版本控制实现了读写不阻塞降低了死锁的概率，写加锁，读不加锁。解决了不可重复读读，和一部分幻读问题。
解决幻读还是得间隙锁（一种锁定相邻行的锁）
MVCC的原理：主要通过ReadView和undoLog实现的，unlog用来保存历史数据，readView进行匹配。
将数据的历史版本进行保存，通过比较版本号来验证数据是否修改，所以不需要加锁
每一个事务都有三个隐藏字段（事务id，历史版本，隐藏id）
ReadView通过活跃事务的id和最小id和下一个事务id进行匹配

读已提交和可重复读

在于readView的时机不同

数据库连接泄露：

连接后没有关闭资源，是该资源一直处于不让其他请求访问的状态

SQL优化：

explain：type、id、possibleId

SQL的生命周期：

客户端通过TCP连接到MySQL的服务器、首先查询缓存、进行语法分析，尽心优化，交给执行器进行执行、关闭连接

一条更新语句的执行流程：

WAL 技术：先写日志，再写磁盘
更新流程还涉及两个重要的日志模块，
redolog和binlog
InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面

日志

redolog具有crash-safe的能力
binlog： binlog是服务层特有的日志
redolog是逻辑日志，binlog是物理日志
redolog是循环写，binlog是追加写

数据库的优化：

一个是SQL语言的优化

• 用具体的字段列表代替“*”
• 少使用否定句，因为否定句会使索引失效
• 多用limit 总之就是尽可能少的输出信息

另一个是结构的优化

• 将含有多个字段的表进行拆表细化
• 增加中间表

千万数据的表的优化

限定数据行数
读写分离
缓存
分库分表

MySQL的主从复制原理以及流程：

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。

第一步将DML和DDL操作放入binlog中
第二步从数据库开启一个IOThread将binlog里的数据写入到replayLog中
第三步SqL线程会读取中继日志，并按顺序执行该日志的SQL事件，从而与主数据库保持一致

删除数据的速度和创建的索引数量是成正比的，所以在面对海量数据时可以先删除索引在删除无用数据，再将索引创建回来