数据库基础知识

另可参考：http://www.stevenwash.xin/2018/04/21/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/

存储引擎MyISAM和InnoDB的区别

InnoDB

1、支持事务、支持外键、支持行锁
2、不支持全文索引
3、用来处理巨大量数据，CPU效率非常高

MyISAM

1、不支持事务、不支持外键、不支持行锁（支持表锁）
2、支持全文索引
3、MyISAM相对简单，所以在效率上要由于InnoDB
4、在大并发下的读写可能出现表损坏

InnoDB的原理

所有的数据库都被逻辑的存放在表空间里面，每个表空间含有多个段空间（Segment），每个段空间又含有多个区空间（Extent），然后在每个区空间里面含有多个页（Page），在页空间里面存着每一行的数据。

页是Innodb管理的最小单位，最小为4KB，最大为64KB，默认为16KKB。

如何存储数据

Innodb会将表的定义和数据索引等信息分开来进行存储，其中表的结构存储在.frm文件中，数据索引文件存储在.ibd文件（即表数据和相关的索引数据）中。

在每一页中的数据都是用行来进行存储的，所以存储的时候会有固定的行格式。

行格式：Barracuda在向下兼容Antelope(COMPACT和REDUNDANT)的同时增加了COMPRESSED和DYNAMIC两种行格式

在处理行溢出数据的时候，比如存储VARCHAR或者BLOB的大对象的数据类型的时候：
对于Compact 和 Redundant的格式来说：先将行数据中的前768个字节存储在数据页中，然后后面会有一个偏移量指针指向溢出页的地址。

对于Comparessed 和 Dynamic的格式来说：都只会在行记录中保存20字节的指针，然后这个指针指向溢出页面，实际的数据都会存储在溢出页面中。

在实际存储数据的时候，为了插入和删除的效率，整个页面并不会对数据按照主键进行排序，它会自动从左侧到右寻找空白节点进行插入操作，所以行记录在物理存储上并不是连续的，而是通过next_record指针来进行指示下一条记录所在的位置。

数据库索引

是什么

索引是对数据表中的一列或者多列数据进行排序的的数据结构，使用索引客以快速访问到数据库的相关数据。

什么数据结构实现

Innodb在绝大多数的情况下使用的是B+树来建立索引。在B+树中查找到的并不是给定key所对应的value，而是数据行所对应的页，然后Innodb会将整个页加载到内存中，然后通过存储在页中的PageDirectory中的稀疏索引和next_record等属性取出记录。

B+树特点：
1、n个关键字就有n个分支
2、每个非叶子结点都不保存数据，只是用来做索引，所有数据都保存在叶子结点上
3、叶子结点包含所有信息，而且由小到大排序
4、叶子结点都通过指针相连，便于当做链表直接顺序访问
5、所有的中间元素都同时存在于子节点中，是子节点中的最大值（或者最小值）

B树的特点：
1、n个关键字有n+1和分支
2、每个节点都存有数据元素，每个关键字的左子树的元素小于该关键字的值，右边元素大于该关键字的值
3、节点内的各个关键字之间是按照从小到大的顺序排序
4、不必每一次查询都查到叶子结点（在一定程度上会快一点，但是就降低查询的稳定性）
5、B树做范围查找相比B+树会慢很多

B+树优势：
1、单一节点存储更多的元素，使得查询的IO次数更少
2、由于所有的查找都会查找到叶子结点，所以查找性能稳定
3、所有叶子结点形成有序链表，便于做范围查找

聚簇索引和非聚簇索引

一张表中包含了一个聚簇索引构成的B+树和若干个辅助索引构成的B+树

聚簇索引

索引项的顺序与表记录的物理存储顺序是一致的，在一张表上只能建一个聚簇索引（一般情况下是主键），因为真实数据的物理顺序只有一种。因为跟物理地址是一一对应的，所以没有地址映射的问题，直接对应数据，即在B+树的叶子结点直接存放的就是记录信息。

非聚簇索引

索引项的顺序与表记录的顺序不一致，在一张表中可以有多个非聚簇索引。通过非聚簇索引（又叫辅助索引）查找到对应的主键，然后通过这个主键在聚簇索引中查找到对应的行记录。

什么时候建立索引

1、经常出现在关键字order by、group by、 distinct后面的字段建立索引
2、在使用union来连接