深入了解MySQL的索引(一)

（一）关于存储引擎       

       创建合适的索引是SQL性能调优中最重要的技术之一。在学习创建索引之前，要先了解MySql的架构细节，包括在硬盘上面如何组织的，索引和内存用法和操作方式，以及存储引擎的差异如何影响到索引的选择。

       MySQL有很多种衍生版本，这些衍生版本支持更多不同种类的存储引擎。本文主要讨论三种MySQL引擎。

       MyISAM 一种非事务性的存储引擎，是MySQL 5.5之前版本默认的存储引擎。

       InnoDB  最流行的事务性存储引擎，从5.5版开始成为MySQL默认的引擎。

       Memory 基于内存的，非事务性的以及非持久性的存储引擎。

       注意：

        从5.5版本开始，MySQL表的默认存储引擎从MyISAM换成InnoDB，将会使用户安装那些依赖默认设置或者专门为MyISAM编写的软件包时带来很大的影响。

（二）MySQL索引类型

        MySQL支持在所有关系数据库表中创建主键、唯一键、不唯一的非主码索引等多种类型的索引。此外MySQL还支持纯文本和空间索引类型。

       MySQL内置的存储引擎对各种索引技术有不同的实现方式，包括：B-树，B+树，R-树以及散列类型。

       索引数据结构理论：

       1.B-树

       B-树中有两种节点类型：索引节点和叶子节点。叶子节点是用来存储数据的，而索引节点则用来告诉用户存储在叶子节点中的数据顺序，并帮助用户找到相应的数据。

       B-树的搜索，从根节点开始，对节点内的关键字有序进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点，重复。直到所对应的儿子指针为空，或已经是叶子节点。

       B-树是一种多路搜索树：

       (1). 定义任意非叶子节点最多有M个儿子，且M>2;

       (2). 根节点的儿子数为[2,M];

       (3). 除根节点以外的非叶子节点的儿子数为[M/2,M];

       (4). 每个节点存放至少M/2-1(取上整)和至多M-1个关键字;

       (5). 非叶子节点的关键字个数=指向儿子节点的指针的个数-1;  

       (6). 非叶子节点的关键字：k[i]<k[i+1];

       (7). 非叶子节点的指针：p[1]，p[2]，·····，p[M]；其中p[1]指向的关键字小于k[1]的子树，p[M]指向的关键字大于K[m-1]的子树;

       (8). 所有的叶子节点位于同一层; 

      2.B+树

       B+树数据结构是B-树实现的增强版本。尽管B+树支持B-树索引的所有特性，它们之间最显著的不同点在于B+树中底层数据是根据被提及的索引列进行排序的。B+树还通过叶子节点之间的附加引用来优化扫描性能。

       B+搜索和B-搜索不同，区别是B+树只有达到叶子节点才命中（B-树可以在非叶子节点命中），其性能等价于关键字全集做一次二分搜索。

      B+树的特性：

     （1）所有关键字都出现在叶子节点的链表中，叶子节点相当于存储数据的数据层。

     （2）不可能在非叶子节点上命中。

     （3）非叶子节点相当于是叶子节点的索引，叶子节点相当于数据层。

     3.散列

     散列表数据结构是一种很简单的概念，它将一种算法应用到给定值中以在底层数据存储系统中返回一个唯一的指针或位置。散列表的优点是始终以线性时间复杂度找到需要读取的行的位置，而不像B-树那样需要横跨多层节点来确定位置。

     4.通信R-树

    R-树数据结构支持基于数据类型对几何数据进行管理。目前只有MyISAM使用R-树实现支持空间索引，使用空间索引也有很多限制，比如只支持唯一的NOT NULL列等。

    5.全文本

     全文本结构也是一种MySQL采用的基本数据结构。这种数据结构目前只有当前版本MySQL中的MyISAM存储引擎支持。5.6版本将要在InnoDB存储引擎中加入全文本功能。全文本索引在大型系统中并没有什么实用的价值，因为大规模系统有很多专门的文件检索产品。所以不用在介绍。