一、索引基础
1.1索引的类型
在MYSQL中,索引是在存储引擎层而不是服务器层实现的。所以,并没有统一的索引标准:
- 不同索引引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引
即使多个存储引擎支持同一种类型的索引,其底层的实现也不同
BTree索引(B-TREE、B+TREE)
回忆了B-TREE的特点:节点内各值互不相等按从小到大排列。
- 下层节点内的取值总是落在上层节点值所划分的区间内。
各个底层节点是叶节点,他们同处一层;页节点下面是失败节点(可以用空指针表示),是查找失败到达的位置。
B-TREE和B+TREE的一个区别:
在B+TREE上有一个指针指向节点值最小的叶子节点,所有叶子节点链接成一个线性链表,而B-TRR中没有。
在B+TREE上叶子节点包含信息,并且包含了全部节点的值,叶子节点的指针指向索引。
下图就是一个建立在B+TRR上的索引:
B TREE索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行索引,不断的比较要查找的值和节点值,直到找到对应的值。叶子节点比较特别,他们的指针指向的是被索引的数据。
看书上的一个例子:
有如下数据表:
在这个表中建立了一个组合索引(last_name,first_name,dob)。
根据建立组合索引形成的BTREE索引结构图如下:
BTREE索引适用于:
- 全值匹配:查询条件中同时有三个索引。如查找 last_name=’Allen’ and first_name=’Cuba’ and dob=’1960-01-01’。
- 匹配最左前缀:如下
last_name=’Allen’;
last_name=’Allen’ and first_name=’Cuba’ - 匹配列前缀:也可以只匹配某一列的开头部分。例如查找所有J开头的姓的人,前提满足【匹配最左原则】
- 匹配范围值:查找姓在Allen 和 Barrymore之间的人。前提满足【匹配最左原则】
- 精确匹配某一行并范围匹配另一列:查找所有姓为Allen,并且字母K开头的人。前提满足【匹配最左原则】
因为索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的order by 操作。一般来说,如果BTREE可以按照某种方式查找到值,那么也可以按照这种方式排序。所以,如果order by子句满足前面列出的几种查询类型,那么这个索引页可以满足对应的排序需求。
下面是关于BTREE索引的限制:
- 如果不是按照索引最左列开始查找,则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人,也无法查找某个特定生日的人,因为这两列都不是最左数据列。
- 不能跳过索引中的列,也就是说,前面所述的索引无法用于查找姓名为Simth并且再某个特定日期出生的人。
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询。例如有查询 where last_name=’Simth’ and first_name like ‘%j’ and dob = ‘1976-01-01’,这个查询只能使用索引的前两列,因为这里的LIKE是一个范围条件。如果范围查询列值的数量有限,那么可以通过使用多个等于条件来代替范围条件。
1.2哈希索引
看下面的例子:
表中数据如下:
假如hash函数f(),它返回下面的值:
来看此查询:
select lname from testhash where fname='Peter';
mysql先计算Peter的哈希值,并使用该值找到对应的记录指针,便能够找到要查找的行。
因为索引自身只需存储对应的哈希值,所以索引结构十分紧凑,速度非常快。然而,哈希索引也有它的限制:
- 哈希索引只包含哈希值,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行速度很快,所以大部分情况下这一点对性能的影响并不明显。
- 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
- 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
- 哈希索引只支持等值比较查询,包括=、in()。
- 访问哈希索引的数据非常快,除非有很多哈希冲突(不同行有相同的哈希值)
- 如果hash冲突很多的话,一些索引的维护代价也很高。例如,如果再某个哈希冲突很多的列上建立哈希索引,那么从表中删除一行时,存储引擎需要遍历对应哈希表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。
因为这些限制,哈希索引只适用与某些特定的场合,而一旦适合哈希索引,则它带来的性能能提升非常显著。在mysql中,只有Memory引擎显示的支持哈希索引。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当引擎注意到某些值使用的非常频繁时,他会再内存中基于BTREE索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为,用户无法控制或配置,不过如果有必要,完全可以关闭该功能。
1.3 空间数据索引R-TREE
MyISAM引擎支持空间索引,可以用作地理数据存储。
1.4 全文索引
全文索引是一种特殊类型的索引,他查找的是文本中的关键词,而不是直接比较索引中的值。全文索引和其他几个索引的匹配方式完全不同。它有许多需要注意的细节,如停用词、词干等。全文检索更类似于索引引擎做的事情,而不是简单的where条件匹配。