【数据库】数据库索引

柔情只为你懂 2022-06-03 05:47 335阅读 0赞

## 1.范式设计 ##

首先需要了解，数据的范式设计是为了解决数据库对数据操作的一致性和一些异常问题，比如：

*  更新异常
 *  插入异常
 *  删除异常

为了实现数据的独立性，满足更高的范式，表设计会被逐步分解，导致的是性能的下降。因此实际中数据库设计对于范式设计不会那么严格，以及会出现反范式。

### 1NF ###

第一范式即属性不可分割，这是数据库关系设计的最基本的要求。

### 2NF ###

首先需要了解几个关键词的含义：

*  码：即candidate keys，可以用码来唯一确定一个instance
 *  完全函数依赖：在一张表中，若 X → Y，且对于 X 的任何一个真子集（假如属性组 X 包含超过一个属性的话），X’ → Y 不成立，那么我们称 Y 对于 X 完全函数依赖。
 *  部分函数依赖：假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称 Y 部分函数依赖于 X
 *  主属性：包含在任何一个码中的属性成为主属性

二范式的定义是：在1NF的基础之上，消除**非主属性对于码的部分函数依赖**。  
言下之意，如果一个码中只有一个属性，那么必然满足2NF

为了满足2NF，需要继续进行关系分解

### 3NF ###

在2NF的基础上，**消除对于非主属性对于码的传递依赖。**  
比如一张表设计：学生（学号，姓名，系名，系主任）  
那么这其中，学号->系名，系名->系主任，存在传递依赖，因此不符合第三范式。

### BCNF ###

与前面三种范式不同，BCNF关注的是主属性的依赖问题。  
在3NF的基础上，**消除主属性对于码的部分与传递依赖**。

## 2. 反范式设计 ##

### 原则 ###

*  范式的满足便于数据一致性的控制，数据冗余会导致数据一致性的控制变得复杂。规范化的数据都是低效的。引入可以控制的冗余可以提高数据库性能
 *  **所有的冗余都是为了减少表连接的数量**，使用触发器可以解决冗余导致的数据不一致（但是触发器可能会导致循环更改）

### 反范式设计的七种情形 ###

1.  合并一对一关系
    
    如果双方都是完全参与，那么某个表直接可以作为另一张表的属性直接合并。若有一方是部分参与，把完全参与的并入部分参与的会出现空值，将部分参与并入完全参与的可以。若双方都是部分参与，一定会出现空值，这样就很难确定主键。
2.  拷贝一对多关系中的非主键值
    
    > 一部电影可能有多个录像带  
    > 出租某个录像带的日租金时，需要查询video表获得该电影的租金。解决方案是在录像带表添加一个租金字段，并设置触发器
3.  拷贝一对多关系中的外键
4.  拷贝多对多关系中的属性
    
    > 演员演某一步电影，role表只记录了catelogNo和actorNo，如果要查电影名字就比较困难。role表添加一个电影名称属性。
5.  引入重复组
    
    常见做法：在customer表中引入一条addr字段，放最常用地址，address表中存储所有地址
6.  创建提取表
    
    **适用查询实时性不高的情况。**。extract table的表中内容和原表可能都相同，只是组织结构不同，有可能一个是为了车查询而建的表（将经常被查询的数据提前计算出来存入该表，会有大量冗余，但是提高效率），另一个是为了update的，一定程度上实现了读写分离。
7.  分区表
    
    将表分成小部分的分区。水平分区：将记录分在不同的表中；竖直分区：将属性分在不同的表中，主键重复。分区对于存储和分析大量数据的应用有好处。

## 3.索引 ##

### 索引概念 ###

1.  索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。是一种以原子粒度访问数据的手段，而不是为了大量数据的访问。是一种数据访问方式；索引是顺序存取。
2.  索引分类
    
     *  **聚簇索引**：按照数据存放的物理位置为顺序的，索引的叶节点就是物理上的叶节点，聚簇索引能提高多行检索的速度
     *  **非聚簇索引**；索引顺序与数据物理排列顺序无关，叶节点仍然是索引节点，保留一个指针指向数据块，非聚簇索引对于单行的检索很快。
     *  一个表最多只能有一个聚簇索引
3.  索引使用时的考虑
    
     *  检索比率，一般适用于满足条件的数据量少的情况
     *  磁盘访问，内存访问，记录存储
4.  索引与外键
    
     *  如果没有外键和引用的话，一次修改会导致多次修改
     *  大系统普遍取消外键的关联，取消参照完整性（降低在更新主表时候的过多引用）是提高数据库性能的一个措施。如果有大量的外键关联，则做一次主表查询可能会导致连接多个代码表
     *  索引建立必须要有理由，无论是外键还是其他字段，并不是外键都要添加索引。\*\*\*如果该外键不经常使用就不用添加索引。\*\*\*
     *  如果系统为外键自动添加索引，常常会导致同一字段属于多个索引，为每个外键建立索引，会导致多余索引
5.  系统生成键
    
     *  系统生成键远好于寻找当前最大值并加1；好于用一个专用表保存下一个值“且加锁更新”
     *  系统生成键是串行插入
     *  如果插入并发性过高，在主键索引的创建操作上会发生严重的资源竞争
     *  解决方案：反向键索引（逆向索引）；哈希索引
     *  系统生成键使用数字比使用字符串效率高
     *  不使用系统生成键，可能会导致插入时主键取值不唯一，有利于主键的唯一性

### 索引的优点，为什么使用索引？ ###

1.  什么时候使用B树索引：
    
     *  仅当要通过索引访问表中很少一部分行
     *  如果要处理表中多行，而且可以使用索引而不用表
2.  索引的5种优点
    
     *  通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
     *  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
     *  可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
     *  在使用分组和排序子句进行数据检索时，同样可以显着减少查询中分组和排序的时间。
     *  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
3.  应该建立索引的条件
    
     *  在经常需要搜索的列上，可以加快搜索的速度；
     *  在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
     *  在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
     *  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；外键建索引由于连接加快还会减少死锁几率。
     *  在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
     *  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

### 索引的局限性（索引的限制） ###

1.  为什么不为每一列建立索引
    
    1.  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
    
     *  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
     *  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2.  索引会带来的问题
    
     *  索引有可能降低查询性能，带来磁盘空间的开销和处理开销等
     *  太多的索引，让设计不稳定
     *  对于大量数据检索，索引效率反而更低
     *  创建索引会带来系统的维护和空间的开销
     *  数据修改需求大于检索需求时，索引会降低性能
3.  这些列不应该建立索引
    
     *  对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
     *  对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
     *  对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
     *  当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
4.  为什么没有使用我的索引？（不使用索引的情况）

**主要是因为：使用索引反而得不到正确结果；或使查询效率变得更慢**

*  情况1：我们在使用B+树索引，而且谓词中没有使用索引的最前列  
    表T，T(X,Y)上有索引，做SELECT \* FROM T WHERE Y=5·  
    跳跃式索引（仅CBO）
 *  情况2：使用SELECT COUNT(\*) FROM T，而且T上有索引，但是优化器仍然全表扫描，不带任何条件的count会引起全表扫描。
    
     *  情况3：对于一个有索引的列作出函数查询  
        Select \* from t where f(indexed\_col) = value
 *  情况4：隐形函数查询（主要是时间和类型变化这种隐形函数查询）
    
     *  不等于符”<>”会限制索引，引起全表扫描，如果改成or就可以使用索引了。
     *  is null查询条件也会屏蔽索引。
 *  情况5：此时如果用了索引，实际反而会更慢。
    
     *  数据量本来不够大，oracle自己计算后认为不用索引更合算，则CBO不会选择用索引
 *  情况6：没有正确的统计信息，造成CBO无法做出正确的选择；  
    如果查询优化器认为所有会使查询变慢，则不会使用索引  
    表分析就是收集表和索引的信息，生成的统计信息会存在user\_tables这个视图。CBO根据这些信息决定SQL最佳的执行路径。
 *  其他：  
    \- 对于两个公有字段的表，如果在做外表的表上对该字段建立索引，则该索引不会被使用因为外表的数据访问方式是全表扫描。
    
     *  查询使用了两个条件用or连接，如果条件1中的字段有索引而条件2中字段没有，则仍会全表扫描。

### IOT 索引组织表 ###

[索引底层实现][Link 1]

[mysql采用B+树而不是B树的原因][mysql_B_B]

[索引组织表][Link 2]

*  OT的用途:全索引表，代码查找表，高频度的一组 关联数据查询
 *  IOT最大的优点:记录是排序的…(效率惊人)

### 其他索引 ###

1.  位图索引
    
    主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.这种方式存储数据,相对于B\*Tree索引,占用的空间非常小,创建和使用非常快。  
    非常紧凑，块变得复杂，更新操作会导致整个块被锁住，不利于更新，所以创建位图索引的目的是为了查询而不是为了更行  
    B树索引不能存空值，位图索引可以存空值
2.  哈希索引
    
    所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，再和Hash表中的Hash值进行比较，并得出相应的信息。  
    HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。
3.  函数索引
    
    基于函数的索引，类似于普通的索引，只是普通的索引是建立在列上，而它是建立在函数上。当然这回对插入数据有一定影响，因为需要通过函数计算一下，然后生成索引。但是插入数据一般都是少量插入，而查询数据一般数据量比较大。
4.  倒排索引
    
    常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。通过倒排索引，可以根据单词快速获取包含这个单词的文档列表。倒排索引主要由两个部分组成：“单词词典”和“倒排文件”。

[Link 1]: https://www.cnblogs.com/xqzt/p/4456746.html
[mysql_B_B]: http://blog.csdn.net/fei33423/article/details/48469899
[Link 2]: https://www.cnblogs.com/nieliu/archive/2012/05/04/2482223.html