数据库原理
1.
数据库原理
1.1
数据库面经
1.1.1 hive和mysql的区别，以及hive的介绍
1.1.2 关系型数据库和非关系数据库的理解
1.1.3 聚合函数与非聚合函数的区别
常见聚合函数bai，max（最大）、dumin（最小zhi）、sum（求和）、avg（平均dao）等
1.1.4 自增长列
MySQL可以通过AUTO_INCREMENT关键字，实现列数据自增。一般用于单表的唯一标识列，常见的有主键。每张表最多只能有一个自增列。
1.1.5 聚集索引和非聚集索引的区别
MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。
聚簇索引的解释是：聚簇索引的顺序就是数据的物理存储顺序
非聚簇索引的解释是：索引顺序与数据物理排列顺序无关
InnoDB——聚簇索引
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。
1.1.6 MySQL索引的数据结构
https://www.cnblogs.com/nijunyang/p/11406688.html
有一道 MySQL 的面试题，为什么 MySQL 的索引要使用 B+ 树而不是其它树形结构？
比如 B 树？
现在这个问题的复杂版本可以参考本文:
因为 B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变低。
1.1.7 数据库索引，联合索引是怎么工作的
https://blog.csdn.net/tongdanping/article/details/79878302
索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。
单列索引：只有位置为查询条件第一个，其他两个都没有用上
劣势：索引本身也是表，因此会占用存储空间；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左。
在检索数据时从联合索引的最左边开始匹配。
1.1.7.1
索引的使用策略
1.
什么时候要使用索引？
主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
2.
什么时候不要使用索引？
经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。
3.
索引失效的情况：
在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
<>、NOT、in、not exists
索引列上做操作（计算，函数，（自动或者手动）类型装换）
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
多列索引只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
1.1.8 MySQL索引长度计算
https://juejin.cn/post/6946321350877249550
对于所有的索引字段, 如果没有设置not null, 则加1个字节.
int占4个字节, date占3个字节, char(n)占n个字符,
varchar(n)占n个字符+2个字节.
对于不同的字符集, 一个字符所占用的字节数也不一样.
latin1编码一个字符占用一个字节
gbk编码一个字符占用两个字节
utf8编码一个字符占用三个字节
1.1.9
聚合索引
https://juejin.cn/post/6844904073955639304#heading-3
对于联合索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。对于联合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，横着看，如，1 1 5 12 13....他是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树，上图中的b列都等于1时，则根据c排序，此时c列也相等则按d列排序，如：1 1 4 ，1 1 5，c=4在c=5前面，以及13 12 4,13 16 1,13 16 5就可以说明这种情况。
1.1.10 数据库的隔离级别
SQL 标准定义了四个隔离级别：
l READ-UNCOMMITTED(读取未提交)：
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
l READ-COMMITTED(读取已提交)：
允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
l REPEATABLE-READ(可重复读)：
对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
l SERIALIZABLE(可串⾏化)：
最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。
MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）。我们可以
通过show variables like
'%tx_isolation%'或select @@tx_isolation;命令来查看
|
隔离级别 |
脏读 |
不可重复读 |
幻影读 |
|
READ-UNCOMMITTED |
√ |
√ |
√ |
|
READ-COMMITTED |
× |
√ |
√ |
|
REPEATABLE-READ |
× |
× |
√ |
|
SERIALIZABLE |
× |
× |
× |
这⾥需要注意的是：与
SQL 标准不同的地⽅在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）
事务隔离级别下使⽤的是Next-Key
Lock 锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如 SQL Server)
是不同的。所以说InnoDB
存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）
已经可以完全保证事务的隔离性要求，即达到了
SQL标准的
SERIALIZABLE(可串⾏化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容)
，但是你要知道的是
InnoDB 存储引擎默认使⽤
REPEAaTABLE-READ（可重读） 并不会有任何性能损失。
InnoDB 存储引擎在
分布式事务 的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别。
1.1.11 数据库慢访问
https://zhuanlan.zhihu.com/p/149338523
1.1.11.1
索引没起作用的情况
l 使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。
l 使用多列索引的查询语句
MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。
1.1.11.2
优化数据库结构
合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
1.1.11.3
将字段很多的表分解成多个表
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
l 增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
l 分解关联查询
将一个大的查询分解为多个小查询是很有必要的。
很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效
1.1.11.4
优化LIMIT分页
1.1.12 为什么使用B+树而不是使用b树/ B+Tree对比BTree的优点：
1. 查询性能更高
因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
B+树是在在叶子节点存放数据，查找速度快除了树的层数不高外，叶子节点还维持了一个链表，查询时相当于顺序查找这个链表。更重要的是，查询时对磁盘的IO操作不仅仅查询了这个数据所在块的信息，也将这个块附近的块也加进来了，这样下次查询时，就不用再次IO，变相加快了查询速度。
2. 磁盘读写代价更低
一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。
3. 查询速度更稳定
由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
1.1.13
索引的优化
1、
最左前缀
索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，
col2，col3等等都是不能使用索引的。
根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。
2、
带索引的模糊查询优化
在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。
3、
使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的
列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
1.1.14 mysql可以放多少条数据，多少条需要分表
https://www.cnblogs.com/leefreeman/p/8315844.html
假设一行内存为1k,可以放2000w条
我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。
数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。
那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。
根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，这就能满足千万级的数据存储。在查找数据时，一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查到数据。
阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过
2GB，才推荐进行分库分表
1.1.15 where和having的区别
“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
1.1.16 mysql主从复制
https://www.cnblogs.com/fengzheng/p/13401783.html
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程
：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程
：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
1.1.17 mysql读写分离
主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
读写分离能提高性能的原因在于：
主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
1.1.18 多版本并发控制 Mysql MVCC
多版本并发控制（Multi-Version
Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用
MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
1.1.19 分布式CAP
分布式系统不可能同时满足一致性（C：Consistency）、可用性（A：Availability）和分区容忍性（P：Partition
Tolerance），最多只能同时满足其中两项。
1.1.19.1
一致性
一致性指的是多个数据副本是否能保持一致的特性，在一致性的条件下，系统在执行数据更新操作之后能够从一致性状态转移到另一个一致性状态。
对系统的一个数据更新成功之后，如果所有用户都能够读取到最新的值，该系统就被认为具有强一致性。
1.1.19.2
可用性
可用性指分布式系统在面对各种异常时可以提供正常服务的能力，可以用系统可用时间占总时间的比值来衡量，4 个
9 的可用性表示系统 99.99% 的时间是可用的。
在可用性条件下，要求系统提供的服务一直处于可用的状态，对于用户的每一个操作请求总是能够在有限的时间内返回结果。
1.1.19.3
分区容忍性
网络分区指分布式系统中的节点被划分为多个区域，每个区域内部可以通信，但是区域之间无法通信。
在分区容忍性条件下，分布式系统在遇到任何网络分区故障的时候，仍然需要能对外提供一致性和可用性的服务，除非是整个网络环境都发生了故障。
1.1.19.4
权衡
在分布式系统中，分区容忍性必不可少，因为需要总是假设网络是不可靠的。因此，CAP 理论实际上是要在可用性和一致性之间做权衡。
可用性和一致性往往是冲突的，很难使它们同时满足。在多个节点之间进行数据同步时，
1. 为了保证一致性（CP），不能访问未同步完成的节点，也就失去了部分可用性；
2. 为了保证可用性（AP），允许读取所有节点的数据，但是数据可能不一致。
1.1.19.5
CAP为什么只能满足两个
https://www.cnblogs.com/fengli9998/p/8866483.html
现在开始分析：
1：满足C，所有的机器上的数据都是一样，这样的情况下会有什么需求呢？每当一个新数据新增到其中一个服务器上，这个数据要同步到其它服务器，这样的情况下才可以保证C
2：满足A，这样的情况下会有什么需求呢？用户随时都在访问，都能在可控的时间内返回正确的数据
3：满足P，非常可靠，怎么能可靠呢？那必须是机器越多越可靠，为啥？我有1亿台服务器，挂了几万台，完全没影响嘛。
现在我们对这几个理论有了一定的了解，现在开始分析为啥只能同时满足两个
1：满足C和A，那么P能不能满足呢？
满足C需要所有的服务器的数据要一样，也就是说要实现数据的同步，那么同步要不要时间？肯定是要的，并且机器越多，同步的时间肯定越慢，这里问题就来了，我们同时也满足了A，也就是说，我要同步时间短才行。这样的话，机器就不能太多了，也就是说P是满足不了的
2：满足C和P，那么A能不能满足呢？
满足P需要很多服务器，假设有1000台服务器，同时满足了C，也就是说要保证每台机器的数据都一样，那么同步的时间可就很大，在这种情况下，我们肯定是不能保证用户随时访问每台服务器获取到的数据都是最新的，想要获取最新的，可以，你就等吧，等全部同步完了，你就可以获取到了，但是我们的A要求短时间就可以拿到想要的数据啊，这不就是矛盾了，所以说这里A是满足不了了
3：满足A和P，那么C能不能满足呢？
满足P需要很多服务器，同时也满足了A，也就是说，我要同步时间短才行,那么C一致性就不能保证
1.1.20 Mysql的回表
https://www.jianshu.com/p/8991cbca3854
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL
unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
画外音：所以PK查询非常快，直接定位行记录。
InnoDB普通索引的叶子节点存储主键值。
画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。
1.1.20.1
回表索引
如粉红色路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）在通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。
1.1.20.2
聚集索引：
一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致。查询速度贼快，聚集索引的叶子节点上是该行的所有数据 ，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致)。主键!=聚集索引。
1.1.20.3
辅助索引(非聚集索引)：
一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个'书签'，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据。
1.1.20.4
联合索引（复合索引）：
就是由多列组成的的索引。遵循最左前缀规则。对where，order
by，group by 都生效。
1、 复合主键：指表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键。
1.1.20.5
覆盖索引：
指从辅助索引中就能获取到需要的记录，而不需要查找聚簇索引中的记录。使用覆盖索引的一个好处是因为辅助索引不包括一条记录的整行信息，所以数据量较聚集索引要少，可以减少大量io操作。
1.1.20.6
聚集索引与辅助索引的区别：
叶子节点是否存放的为一整行数据
1.1.20.7
最左前缀规则：
假设联合索引由列(a,b,c)组成，则一下顺序满足最左前缀规则：a、ab、abc；selece、where、order
by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引。
1.1.20.8
DDL更改表的语句,,DML是更改表中数据的语句
1.1.20.9
建立索引的列原则
以下列上适合建立索引：
（1） 表的主键、外键必须有索引。
（2） 经常与其它表进行连接的表，在连接字段上应该建立索引。
（3） 经常出现在WHERE子句中的字段，特别是大表的字段，应该建立索引。
（4） 索引应该建在选择性高的字段上。
（5） 索引应该建在小字段上，对于大的文本字段甚至超长字段，不适合建索引。
（6） 复合索引的建立需要进行仔细分析。
（7） 正确选择复合索引中的主列字段，一般是选择性较好的字段。
（8） 如果单字段查询很少甚至没有，那么可以建立复合索引；否则考虑单字段索引。
（9） 如果复合索引中包含的字段经常单独出现在WHERE子句中，那么分解为多个单字段索引。
（10） 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。
（11） 如果既有单字段索引，又有这几个字段上的复合索引，那么一般可以删除复合索引。
（12） 频繁进行DML操作的表，不要建立太多的索引。
（13） 删除无用的索引，避免对执行计划造成负面影响。
1.1.21
1.2
并发一致性问题
1.2.1 丢失修改
丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1
和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。
1.2.2 读脏数据
读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1
修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
1.2.3 不可重复读
不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2
读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
1.2.4 幻影读
幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2
在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
1.3
隔离级别
1.3.1
未提交读（READ UNCOMMITTED）
事务中的修改，即使没有提交，对其它事务也是可见的。
1.3.2
提交读（READ COMMITTED）
一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
1.3.3
可重复读（REPEATABLE READ）
保证在同一个事务中多次读取同一数据的结果是一样的。
1.3.4
可串行化（SERIALIZABLE）
强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。
1.3.5
各个隔离级别解决
|
隔离级别 |
脏读 |
不可重复读 |
幻影读 |
|
READ-UNCOMMITTED |
√ |
√ |
√ |
|
READ-COMMITTED |
× |
√ |
√ |
|
REPEATABLE-READ |
× |
× |
√ |
|
SERIALIZABLE |
× |
× |
× |
1.4
存储引擎
1.4.1 概念
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同 的存储引擎，还可以
获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。存储引擎主要有： 1. MyIsam , 2. InnoDB, 3.
Memory, 4. Archive, 5. Federated 。
1.4.2 InnoDB（B+树）/ msql b+树实现
1.4.2.1
 |
InnoDB 底层存储结构为B+树， B树的每个节点对应innodb 的一个page，page大小是固定的， 一般设为 16k。其中非叶子节点只有键值，叶子节点包含完成数据。
适用场景：
1）
经常更新的表，适合处理多重并发的更新请求。
2）
支持事务。
3）
可以从灾难中恢复（通过 bin-log 日志等）。
4）
外键约束。只有他支持外键。
1.4.2.2 TokuDB（Fractal
Tree-节点带数据）
TokuDB 底层存储结构为Fractal
Tree,Fractal Tree 的结构与 B+树有些类似,
在 Fractal Tree 中，每一个 child 指针除了需要指向一个 child 节点外，还会带有一个 Message Buffer ，这个Message Buffer 是一个 FIFO
的队列，用来缓存更新操作。
例如，一次插入操作只需要落在某节点的 Message Buffer 就可以马上返回了，并不需要搜索到叶子节点。这些缓存的更新会在查询时或后台异步合并应用到对应的节点中。
 |
TokuDB 在线添加索引，不影响读写操作, 非常快的写入性能， Fractal-tree 在事务实现上有优势。 他主要适用于访问频率不高的数据或历史数据归档。
1.4.2.3
MyIASM
MyIASM 是MySQL 默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键， 因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。---ISAM
是一种静态索引结构。
缺点是它不 支持事务处理。
1.4.2.4
Memory
Memory（也叫 HEAP）堆内存：使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用
HASH 索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索引，B 树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。
1.5
索引
索引（Index）是帮助 MySQL 高效获取数据的数据结构。常见的查询算法,顺序查找,二分查找,二叉排序树查找,哈希散列法,分块查找,平衡多路搜索树B
树（B-tree）
1.5.1 常见索引原则有
1. 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
越多的索引，会使更新表变得很浪费时间。
6.
如果索引的值很长，那么查询的速度会受到影响。
7.
如果索引字段的值很长，最好使用值的前缀来索引。
区分度的公式是表示字段不重复的比例
11 .索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
1.6
数据库三范式
范式是具有最小冗余的表结构。3
范式具体如下：
1.6.1
键码和函数依赖
记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。
对于 A->B，如果能找到 A 的真子集 A'，使得
A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
对于 A->B，B->C，则 A->C 是一个传递函数依赖。
1.6.2
异常
以下的学生课程关系的函数依赖为 {Sno, Cname}
-> {Sname, Sdept, Mname, Grade}，键码为 {Sno,
Cname}。也就是说，确定学生和课程之后，就能确定其它信息。
|
Sno |
Sname |
Sdept |
Mname |
Cname |
Grade |
|
1 |
学生-1 |
学院-1 |
院长-1 |
课程-1 |
90 |
|
2 |
学生-2 |
学院-2 |
院长-2 |
课程-2 |
80 |
|
2 |
学生-2 |
学院-2 |
院长-2 |
课程-1 |
100 |
|
3 |
学生-3 |
学院-2 |
院长-2 |
课程-2 |
95 |
不符合范式的关系，会产生很多异常，主要有以下四种异常：
1. 冗余数据：例如 学生-2 出现了两次。
2. 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
3. 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。
4. 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。
5.
1.6.3
第一范式 (1NF)
属性不可分。
1.6.4
第二范式 (2NF)
每个非主属性完全函数依赖于键码。
可以通过分解来满足。
I
分解前
|
Sno |
Sname |
Sdept |
Mname |
Cname |
Grade |
|
1 |
学生-1 |
学院-1 |
院长-1 |
课程-1 |
90 |
|
2 |
学生-2 |
学院-2 |
院长-2 |
课程-2 |
80 |
|
2 |
学生-2 |
学院-2 |
院长-2 |
课程-1 |
100 |
|
3 |
学生-3 |
学院-2 |
院长-2 |
课程-2 |
95 |
以上学生课程关系中，{Sno, Cname}
为键码，有如下函数依赖：
Sno -> Sname, Sdept
Sdept -> Mname
Sno, Cname-> Grade
Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。
Sname, Sdept 和
Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。
II
分解后
关系-1
|
Sno |
Sname |
Sdept |
Mname |
|
1 |
学生-1 |
学院-1 |
院长-1 |
|
2 |
学生-2 |
学院-2 |
院长-2 |
|
3 |
学生-3 |
学院-2 |
院长-2 |
有以下函数依赖：
Sno -> Sname, Sdept
Sdept -> Mname
关系-2
|
Sno |
Cname |
Grade |
|
1 |
课程-1 |
90 |
|
2 |
课程-2 |
80 |
|
2 |
课程-1 |
100 |
|
3 |
课程-2 |
95 |
有以下函数依赖：
Sno, Cname -> Grade
1.6.5
第三范式 (3NF)
非主属性不传递函数依赖于键码。
上面的 关系-1 中存在以下传递函数依赖：
Sno -> Sdept ->
Mname
可以进行以下分解：
关系-11
|
Sno |
Sname |
Sdept |
|
1 |
学生-1 |
学院-1 |
|
2 |
学生-2 |
学院-2 |
|
3 |
学生-3 |
学院-2 |
关系-12
|
Sdept |
Mname |
|
学院-1 |
院长-1 |
|
学院-2 |
院长-2 |
1.7
数据库是事务
事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元
事务必须具备以下四个属性，简称ACID
属性：
1.
事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
2. 当事务完成时，数据必须处于一致状态。
3.
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
4. 事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。
1.8
存储过程(特定功能的 SQL 语句集)
一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
1.8.1 存储过程优化思路：
1. 尽量利用一些sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2.
中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors
是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
4.
事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5.
使用try-catch 处理错误异常。
6.
查找语句尽量不要放在循环内。
1.9
触发器(一段能自动执行的程序)
触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是：
触发器是当对某一个表进行操作时触发。诸如：update、insert、delete
这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server
2005 中触发器可以分为两类：DML 触发器和DDL 触发器，其中
DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有 create、alter、drop
语句。
1.10 数据库并发策略
并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。
1.10.1 乐观锁
乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间戳就是不加锁，通过时间戳来控制并发出现的问题。
1.10.2 悲观锁
悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。
1.10.3 时间戳
时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加1，提交之前，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量，
以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）。
1.11 数据库锁
1.11.1 行级锁
行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle
会自动应用行级锁：
1.
INSERT、UPDATE、DELETE、SELECT … FOR
UPDATE [OF columns]
[WAIT n | NOWAIT];
2. SELECT … FOR
UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用COMMIT 或 ROLLBACK 语句释放锁。
1.11.2 表级锁
表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分
MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁
（排他锁）。
1.11.3 页级锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB
支持页级锁
1.12 分区分表
分库分表有垂直切分和水平切分两种。
1.12.1
垂直切分(按照功能模块)
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。
1.12.2
水平切分(按照规则划分存储)
水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。
当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。
Sharding 策略
哈希取模：hash(key) % N；
范围：可以是 ID 范围也可以是时间范围；
映射表：使用单独的一个数据库来存储映射关系。
Sharding 存在的问题
1. 事务问题
使用分布式事务来解决，比如 XA 接口。
2. 连接
可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。
3. ID 唯一性
使用全局唯一 ID（GUID）
为每个分片指定一个 ID 范围
分布式 ID 生成器 (如 Twitter 的
Snowflake 算法)
1.13 两阶段提交协议
分布式事务是指会涉及到操作多个数据库的事务,在分布式系统中，各个节点之间在物理上相互独立，通过网络进行沟通和协调。
XA 就是 X/Open DTP 定义的交易中间件与数据库之间的接口规范（即接口函数），交易中间件用它来通知数据库事务的开始、结束以及提交、回滚等。
XA 接口函数由数据库厂商提供。
二阶段提交(Two-phaseCommit)是指，在计算机网络以及数据库领域内，为了使基于分布式系统架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事务的 ACID 特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此， 二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者的反馈情报决定各参与者是否要提交操作还是中止操作。
1.13.1 准备阶段
事务协调者(事务管理器)给每个参与者(资源管理器)发送 Prepare 消息，每个参与者要么直接返回失败(如权限验证失败)，要么在本地执行事务，写本地的 redo 和 undo 日志，但不提交，到达一种“万事俱备，只欠东风”的状态。
1.13.2 提交阶段
如果协调者收到了参与者的失败消息或者超时，直接给每个参与者发送回滚(Rollback)消息；否则， 发送提交(Commit)消息；参与者根据协调者的指令执行提交或者回滚操作，释放所有事务处理过 程中使用的锁资源。(注意:必须在最后阶段释放锁资源)
1.13.3 缺点
I
同步阻塞问题
1、执行过程中，所有参与节点都是事务阻塞型的。
II
单点故障
2、由于协调者的重要性，一旦协调者发生故障。参与者会一直阻塞下去。
III
数据不一致（脑裂问题）
3、在二阶段提交的阶段二中，当协调者向参与者发送 commit 请求之后，发生了局部网络异常或者在发送 commit 请求过程中协调者发生了故障，导致只有一部分参与者接受到了
commit 请求。于是整个分布式系统便出现了数据部一致性的现象(脑裂现象)。
IV
二阶段无法解决的问题（数据状态不确定）
4、协调者再发出 commit 消息之后宕机，而唯一接收到这条消息的参与者同时也宕机了。那么即使协调者通过选举协议产生了新的协调者，这条事务的状态也是不确定的，没人知道事务是否被已经提交。
1.14 三阶段提交协议
三阶段提交（ Three-phase commit ） ， 也叫三阶段提交协议（ Three-phase commit
protocol），是二阶段提交（2PC）的改进版本。
与两阶段提交不同的是，三阶段提交有两个改动点。
1、引入超时机制。同时在协调者和参与者中都引入超时机制。
2、在第一阶段和第二阶段中插入一个准备阶段。保证了在最后提交阶段之前各参与节点的状态是一致的。也就是说，除了引入超时机制之外，3PC 把 2PC 的准备阶段再次一分为二，这样三阶段提交就有CanCommit、PreCommit、DoCommit
三个阶段。
1.14.1 CanCommit 阶段
协调者向参与者发送 commit 请求，参与者如果可以提交就返回 Yes 响应，否则返回No 响应。
1.14.2 PreCommit 阶段
协调者根据参与者的反应情况来决定是否可以继续进行，有以下两种可能。假如协调者从所有的参与者获得的反馈都是Yes
响应，那么就会执行事务的预执行假如有任何一个参与者向协调者发送了 No 响应，或者等待超时之后，协调者都没有接到参与者的响应，那么就执行事务的中断。
1.14.3 doCommit 阶段
该阶段进行真正的事务提交，主要包含 1.协调这发送提交请求 2.参与者提交事务 3.参与者响应反馈（
事务提交完之后，向协调者发送 Ack 响应。）4.协调者确定完成事务。
1.15 柔性事务
1.15.1 柔性事务
在电商领域等互联网场景下，传统的事务在数据库性能和处理能力上都暴露出了瓶颈。在分布式领域基于CAP 理论以及
BASE 理论，有人就提出了 柔性事务 的概念。CAP（一致性、可用性、分区容忍性）理论大家都理解很多次了，这里不再叙述。说一下 BASE 理论，它是在
CAP 理论的基础之上的延伸。包括 基本可用（Basically Available）、柔性状态（Soft State）、最终一致性
（Eventual Consistency）。
通常所说的柔性事务分为：两阶段型、补偿型、异步确保型、最大努力通知型几种。
1、就是分布式事务两阶段提交，对应技术上的 XA、JTA/JTS。这是分布式环境下事务处理的典型模式。
2、TCC 型事务（Try/Confirm/Cancel）可以归为补偿型。
WS-BusinessActivity 提供了一种基于补偿的 long-running 的事务处理模型。服务器 A 发起事务， 服务器 B 参与事务，服务器 A 的事务如果执行顺利，那么事务 A 就先行提交，如果事务 B 也执行顺利，则事务 B 也提交，整个事务就算完成。但是如果事务
B 执行失败，事务 B 本身回滚，这时事务 A 已经被提交，所以需要执行一个补偿操作，将已经提交的事务
A 执行的操作作反操作，恢复到未执行前事务 A 的状态。这样的 SAGA
事务模型，是牺牲了一定的隔离性和一致性的，但是提高了 long-running 事务的可用性。
 |
3、通过将一系列同步的事务操作变为基于消息执行的异步操作,
避免了分布式事务中的同步阻塞操作的影响。
 |
4、这是分布式事务中要求最低的一种, 也可以通过消息中间件实现, 与前面异步确保型操作不同的一点是, 在消息由 MQ Server 投递到消费者之后, 允许在达到最大重试次数之后正常结束事务。
1.16 CAP
CAP 原则又称 CAP 定理，指的是在一个分布式系统中，
Consistency（一致性）、 Availability（可用性）、Partition tolerance（分区容错性），三者不可得兼。
1.16.1 一致性（C）：
在分布式系统中的所有数据备份，在同一时刻是否同样的值。（等同于所有节点访问同一份最新的数据副本）
1.16.2 可用性（A）：
在集群中一部分节点故障后，集群整体是否还能响应客户端的读写请求。（对数据更新具备高可用性）
1.16.3
分区容忍性（P）：
以实际效果而言，分区相当于对通信的时限要求。系统如果不能在时限内达成数据一致性，
就意味着发生了分区的情况，必须就当前操作在C 和 A 之间做出选择。
浙公网安备 33010602011771号