mysql中如果不特殊说明,那么索引指的就是B-tree索引。B-tree有一张图示:数据存储空间中,每个数据之间,都有一个指向叶子页的指针(把第一个指针理解成根节点),这个叶子页里面是根结点key值大于根结点前一个key,小于根结点后一个key。(理解成两个数据的key之间有一个指向下一个叶子页的指针,这个叶子页里存储的是这两个数据之间的key,而叶子页节点的key指向的就是具体的数据了。如果索引比较复杂,应该会有多层结构。
B-tree索引的优点就是索引都是顺序排列的,因此很适合顺序查找以及数据的排序(order by)。
我们之前说过,索引列可以是单独的一列,也可以是多个列组成一个索引列,这里面是有区别的,同样的列组成的索引,会因为多个列的顺序不同形成不同的索引列。在适应索引时,有一下几种类型的可以使用B-tree索引:
全值匹配:就是精确查找。比如有name和age两列组成一个索引,全值匹配就是分别这两个条件完全匹配。
匹配最左前缀:这个我们前面说过,比如name,age,country三列组成一个索引,查询条件只有name=“xxx”时,可以使用这个索引。但是,如果查询条件为age=“10”时,该条查询就不会用到这条索引。
匹配列前缀:索引列只匹配该列前面部分值,是会用到索引的。
范围匹配:多个列组成的索引,在最左列前缀条件下的范围匹配,或是单个列的索引的范围匹配。
精确匹配与范围匹配结合:
还是以上面三个列组成的索引为例:当查询条件变成,name=“a” and age>10时,可以使用索引。
这里也有B-tree的一个限制很类似,做一下类比:也是范围查询,name,age,country分别生成三个索引。当查询条件为:name=“a” and age>10 and country =“b”时,三个索引只会用到前两个,范围匹配后的country索引用不到。
B-tree的另一个限制,name,age,country组成一个索引,查询不能跳过中间列,比如name=“a” and thencountry=“b”时,就不会用到这个索引。
哈希索引:
哈希索引就是在数据表中维护一个哈希值,然后再将这些哈希值和存储对应位置的指针生成一个哈希表。
在查询时,会先比较哈希值,然后查到索引的指针,最后取出值。
有一个点:相同数据的哈希值一样,那么,会有一个链表来存储想到的哈希值对应的多个位置指针,当查询的哈希值相同时,会遍历这个链表的数据,返回值。哈希索引的优点就是快。
InnoDB有一个自适应哈希索引的功能,这个功能是innoDB会自己将经常查询的数据的生成哈希值,当下次查询时,就用这个哈希值做查询条件,还是为了提速,遗憾的是这个并不是用户可定义的。全是存储引擎自己决定的。但是用户可以决定是否开启这项功能。
一个跟哈希相似的例子就是:当匹配比较复杂字符串时(比如url),使用b-tree查询效果并不一定好,一个小技巧是,将url用函数转换成哈希值,然后多加一个列。后面匹配哈希值比之间匹配字符串效率高很多。哈希索引了解的那很到位。后面我们继续~
如有错误,请指正。