C++复习之路16：数据库相关的一些基础知识2：

1.数据库的索引类型：

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

唯一索引：在创建唯一索引时不能具有相同的索引值。
主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。
聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。
普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找。
全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。

1.2唯一索引与主键索引的区别

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

唯一性索引列允许空值，而主键列不允许为空值。

主键列在创建时，已经默认为空值 + 唯一索引了。

主键可以被其他表引用为外键，而唯一索引不能。

一个表最多只能创建一个主键，但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

1.3聚集索引与非聚集索引的区别

聚集索引：数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序相同，一个表只能拥有一个聚集索引！

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理顺序不同一个表可以拥有多个非聚集索引！（非聚集索引可细分成普通索引，唯一索引，全文索引…）

区别：

聚集索引：可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了。

非聚集索引：把一个很大的范围，转换成一个小的地图。你需要在这个小地图中找你要寻找的信息的位置。然后通过这个位置，再去找你所需要的记录。

可以把聚集索引查询过程看成：在字典中通过字母（字母本身有顺序的，并且对应页码顺序）查汉字页码

可以把非聚集索引查询过程看成：在字典中通过偏旁（偏旁先查到具体汉字，然后该汉字再指出具体页码）查汉字页码

可以总结出：
区别一：表数据记录的物理排列顺序和与索引的排列顺序是否一致。

区别二：聚集索引叶子结点为数据记录页！而非聚集索引的叶子结点依然是索引列（表中的某个字段或者说列作为索引项），并且会有一个指针指向数据记录！

1.4索引的优缺点，及什么时候该用，什么时候不该用

1. 优点：

   通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。

   可以加快数据的检索速度

   可以加速表与表之间的连接

   在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

2. 缺点

   创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

   索引需要占用物理空间，数据量越大，占用空间越大

   会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

3. 什么时候需要创建索引

   • 主键自动建立唯一索引

   • 频繁作为查询条件的字段应该创建索引

   • 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找

   • 查询中统计或者分组的字段；

4. 什么时候不需要创建索引

   • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

   • where条件里用不到的字段，不创建索引；

   • 表记录太少，不需要创建索引；

   • 经常增删改的表；

   • 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

1.5索引的底层实现（为什么用B+树而不是红黑树）

B+树和B树区别

B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。所以从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。

那么Mysql如何衡量查询效率呢？– 磁盘IO次数。 B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。

另一个优点是： B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B树和红黑树区别

AVL 数和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

 

1.6索引的最左前缀的原则，即最左优先，如：

如果有一个2列的联合索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列联合索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

关于最左前缀的使用，有下面两条说明：

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式