数据库中的晦涩知识点

转自http://blog.youkuaiyun.com/hguisu/article/details/7786014

一、数据库索引

索引就是加快检索表中数据的方法。数据库中索引类似于书记的索引。在书籍中索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也会允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。索引的实现通常是B树或者B+树。

好处：数据之外还维护满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法。所以在大量数据的情况下索引可以提高查找速度

坏处：增加了数据库的存储空间；插入和修改数据时要花费较多的时间（因为索引也要随之变动））

查找树中每个节点包含索引键值和一个指向对应数据记录物理地址的指针。

B树：

定义：一棵m 阶的B-树，或者为空树，或为满足下列特性的m 叉树：
                              ⑴树中每个结点至多有m 棵子树；
                              ⑵若根结点不是叶子结点，则至少有两棵子树；
                              ⑶除根结点之外的所有非终端结点至少有[m/2] 棵子树；

     ⑷所有的非终端结点中包含以下信息数据：（n，A0，K1，A1，K2，…，Kn，An）其中：Ki（i=1,2,…,n）为关 键码，且Ki<Ki+1，Ai 为指向子树根结点的指针(i=0,1,…,n)，且指针Ai-1 所指子树中所有结点的关键码均小于Ki (i=1,2,…,n)，An 所指子树中所有结点的关键码均大于Kn.n   为关键码的个数。

    ⑸所有的叶子结点都出现在同一层次上，并且不带信息（可以看作是外部结点或查找失败的结点，实际上这些结点 不存在，指向这些结点的指针为空）。即所有叶节点具有相同的深度，等于树高度。

B树tips:

B树的查找类似于二叉排序树的查找。所不同的是B树上每个节点上是多关键码有序表，在到达某个节点时，先在有序表中查找，若找到，则查找成功。否则，到按照对应的指针信息指向的子树中查找。当到达叶子节点时，则说明查找失败。

B树通常存储在磁盘上，包含两种操作：

a)在B树中查找节点

b)在节点中查找关键字

其中在B树种查找节点是在磁盘上做的，在节点中查找关键字是在内存中做的。即在磁盘中找到指针P所指节点后，先将节点中的信息读入内存，然后再利用顺序查找或折半查找查询等于K的关键字。显然在磁盘上进行一次查找比在内存中进行一次查找的时间消耗多得多。因此B树上的层次（决定磁盘中进行查找的次数）是决定B树查找效率的首要因素

B树的插入从最后一层（不包括代表插入失败的点），若不满足B树性质则分裂向上添加

B树的删除若节点为最下层的非终端结点且关键字删除后满足要求则删除否则合并。不是最下层非终端下层可由最下层非终端的一个代替然后转化为最下层非终端结点删除。能删删，删不了兄弟借，借不了父节点靠

1.有n课子树的结点中含有n-1个关键字

2.

B+树tips:

1.有n棵子树的结点中含有n个关键字

2.所有的叶子结点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且
叶子结点本身依关键码的大小自小而大的顺序链接。

3.所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键码。

通常在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点。因此可以对B+树进行两种查找运算：一种是从最小关键字起顺序查找，另一种是从根节点开始，进行随机查找。 

在B+树上进行随机查找、插入和删除的过程基本上与B-树类似。只是在查找时，若非终端结点上的关键码等于给定值，并不终止，而是继续向下直到叶子结点。因此，在B+
树，不管查找成功与否，每次查找都是走了一条从根到叶子结点的路径。

为什么选B树：

二叉查找树进化品种的红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

　一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

数据库系统巧妙利用了磁盘预读（局部性原理）原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。而红黑树由于是二叉树。B树一个节点可以有很多子女，红黑树只有两个，明显层数要比B树大得多的多。

二搜索引擎

myisam引擎使用B+树作为索引结构，页节点的data域存放的是数据记录的地址。主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而附注索引可以重复。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。这种索引方式也叫非聚集索引

innoDB索引也是B+作为索引，但myisam中索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innoDB中表数据文件本身就是按B+组织的一个索引结构。这棵树的叶节点域保存了完整的数据记录。这个索引的key是数据表主键，因此innoDB表数据文件本身就是主索引。这种索引叫做聚簇索引。因为innoDB的数据文件本身要按主键聚集，所以innoDB要求表必须有主键，如果不存在这种列，则mysql自动为innoDB生成一个隐含字段的主键，这个字段长度为6个字节，类型为长整型。innoDB的所有辅助索引都引用主键作为data域

聚簇索引这种实现方式使得主键的搜索十分高效，但是辅助索引需要索引两遍：首先检索辅助索引获得主键，然后利用主键到主键索引中检索获得记录。

InnoDB索引和MyISAM索引的区别：
一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。
二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

三存储过程和函数：

摘自：

http://www.cnblogs.com/azai/archive/2010/06/20/1761174.html

区别：

   1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

   2.对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

   3.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

   4.当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。

 

注：sql语句执行的时候要先编译，然后执行。存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程