Mysql索引分析

一、索引的基本概念

1、索引的含义

MSQL官方定义：索引（Index）是帮助MySQL高效获取数据的数据结构。（索引是数据结构）

（数据结构是指相互之间存在一种或多种特定关系的数据元素的集合）

个人理解：排好序的快速查找的数据结构

其实索引也就是数据集合，是一个排好序的数据集合，帮助快速获取数据库表中的数据，和排好序。简而言之将数据库表中的选中的字段按照B树的存储结构进行排序存储

一般来说索引本身很大，不可能全部存储在内存中，因此往往以索引文件的形式存储在磁盘上

2、索引存储的数据结构

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，InnoDB引擎，默认的是B+树

3、Hash索引、B+ Tree、B-Tree索引的区别

3.1 Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。

如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中去。

举例：

CREATE TABLE `testhash` ( `fname` varchar(50) DEFAULT NULL, `lname` varchar(50) DEFAULT NULL, KEY `fname` (`fname`) USING HASH) ENGINE=MEMORY;

为什么用MEMORY存储引擎，因为mysql只有MEMORY存储引擎显示支持哈希索引。

select lname from testhash where fname ='Peter'
Mysql首先计算Peter的哈希值是8784，然后到哈希索引中找到对应的行指针，根据指针找到对应的数据行。

优势：由于索引存储是用hash地址和行指针，查询非常的快；

缺点：

1）因为hash值始终是使用全部的索引列计算得到的，只能全部索引列进行查询，不支持部分索引列查询；

2）哈希索引的数据并不是按照索引值的顺序进行存储的，所以无法用于排序；

3）哈希索引只支持等值比较查询，包括=，IN()等，不支持范围查询，如 >；

4）哈希冲突（不同索引列会用相同的哈希码）会影响查询速度，此时需遍历索引中的行指针，逐行进行比较。

如果哈希冲突很多，一些索引维护操作的代价会很高。例如：从表中删除一行，需要遍历链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

**总结：**哈希索引限制多，只适用于一定的场合。而一旦适合哈希索引，它带来的性能提升将非常显著。

3.2 B-Tree

B 树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树。（相对于二叉，B树每个内结点有多个分支，即多叉）

B树又可以写成B-树B-Tree，并不是B“减”树，横杠为连接符，容易被误导

首先我们介绍一下一棵 m 阶B-tree的特性

m 阶的定义：一个节点能拥有的最大子节点数来表示这颗树的阶数
举个例子：
如果一个节点最多有 n 个key，那么这个节点最多就会有 n+1 个子节点，这棵树就叫做 n+1（m=n+1）阶树

包括以下5条特性