MySQL 数据库性能优化之索引优化

文章主要内容分为三个部分。

第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。

第二部分结合MySQL数据库中MyISAM和InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。

第三部分根据上面的理论基础，讨论MySQL中高性能使用索引的策略。

 

数据结构及算法基础

索引的本质：索引是帮助mysql高效获取数据的数据结构。索引的本质是数据结构。

数据库查询是数据库的最基本功能。希望查询的速度尽可能快，因此需要从查询算法的角度进行优化。最基本的查询算法当然是顺序查找，但是着这种查询算法以O（n）的时间复杂度进行，在数据量很大时，显然是很糟糕的。所以就需要更加优秀的算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引结构：B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

 

B-Tree

 

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

 

1 d为大于1的一个正整数，称为B-Tree的度。

 

2 h为一个正整数，称为B-Tree的高度。

 

3 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

 

4 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

 

5 所有叶节点具有相同的深度，等于树高h。

 

6 key和指针互相间隔，节点两端是指针。

 

7 一个节点中的key从左到右非递减排列。

 

8 所有节点组成树结构。

 

9 每个指针要么为null，要么指向另外一个节点。

 

10 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。

 

11 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。

 

12 如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。

B-Tree中按key检索数据的算法原理及其实现：

首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

 

BTree_Search(node, key)

 

{

 

    if(node == null)returnnull;

 

 

    foreach(node.key)

 

    {

 

        if(node.key[i] == key)returnnode.data[i];

 

        if(node.key[i] > key)returnBTree_Search(point[i]->node);

 

    }

 

 

    returnBTree_Search(point[i+1]->node);

 

}

 

 

data = BTree_Search(root, my_key);

 

一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构。

 

B+Tree：是B-Tree树的变种，MySql通常使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

1 每个节点的指针上限为2d而不是2d+1。

2 内节点不存储data，只存储key；叶子节点不存储指针。