索引概述:
索引(也就是mysql中常说的键值key)是存储引擎用户快速找到记录的一种数据结构,这是索引的基本功能,索引对于良好的性能非常关键,尤其是当表中数据量
越来越大的时候,索引对性能的影响就越重要,在数据量较小且负载较低时,不恰当的索引对性能的影响可能不明显,但当数据量逐渐增大的时候,性能就会急剧下降.
理解索引最简单的方法:就是去看看一本书的"索引"部分,如果想在一本书中找到某个特定主题,一般会先看书的“索引”,根据“索引”找到对应的页码.在mysql中,存储
引擎用类似的方法使用索引,先在索引中找到对应值,然后根据匹配的索引记录找到对应的值,然后根据匹配的索引记录找到对应的数据行.
索引的类型:
一.B-Tree索引
在mysql中,索引是在存储引擎层而不是服务器层实现的,所以并没有统一的索引标准;不同存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所
有类型的索引,即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同.
如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据,大多数mysql引擎都支持这种索引,存储引擎以不同的方式使用B-Tree索引,性能
也各不相同,各有优劣,例如,myisam使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储.再如MyISAM索引通过数据的物理位置引用被索引的行,而
InnoDB则根据主键引用被索引的行.
B-Tree通常意味着所有的值都是按照顺序存储的,并且每一个叶子页到根的距离相同,B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取
需要的数据,取而代之的是从索引的根节点开始进行搜索,根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找.通过比较节点页的值和要查找的值可以
找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限与下限,最终存储引擎要么是找到对应的值,要么该记录不存在.
叶子节点比较特别,它们的指针指向的是被索引的数据,而不是其他的节点页(不同引擎的“指针”类型不同),根节点与叶子节点之间可能存在很多层节点页,树的深度与表的大小
直接相关.B-Tree对索引列是顺序组织存储的,所以很合适查找范围数据.例如,在一个基于文本域的索引树上,按字母顺序传递连续的值进行查询是非常合适的,所以像“找出所有以
I到K开头的名字”这样的查找效率会非常高.
可以使用B-Tree索引的查询类型,B-Tree索引适用于全键值、键值范围或者键前缀查找,其中键前缀查找只适用于根据最左前缀的查找.
B-Tree对以下类型的查询有效:
1.全值匹配
2.匹配最左前缀(第一列)
3.匹配列前缀(第一列)
4.匹配范围值(第一列)
5.精确匹配某一列并范围匹配另外一列
6.只访问索引的查询
关于B-Tree索引的限制:
1.如果不是按照索引的最左列开始查找,则无法使用索引.
2.不能跳过索引列
3.如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找,例如有查询条件where last_name='smith' and first_name like 'j%' and dob='1976-12-23',
这个查询只能使用索引的前两列,因为这里的like是一个范围条件(但是服务器可以把其余列用于其他目的).如果范围查询列值的数量有限,那么可以通过使用多个等于
条件来代替范围条件.
二.哈希索引
哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同的键值
的行计算出来的哈希码也不一样,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针,在MySQL中,只有Memory引擎显式支持哈希索引.这
也是Memory引擎表默认的索引类型,Memory引擎同时也支持B-Tree索引. 值得一提的是,Memory引擎是支持非唯一哈希索引的,这在数据库世界里面是比较与众不同的,如果
多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一哈希条目中.
假如有下列表:
create table testhash(
fname varchar(50) not null,
lname varchar(50) not null,
key using hash(fname)
) engine=memory;
表中包含如下数据:
select * from testhash;
fname lname
Arjen Lentz
Baron Schwartz
Peter Zaitsev
Vadim Tkachenko
假设索引使用假想的哈希函数f(),它返回下面的值(都是演示数据)
f('Arjen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
则哈希索引的数据结构如下:
槽 值
2323 指向第一行的指针
2458 指向第四行的指针
7437 指向第二行的指针
8784 指向第三行的指针
注意每个槽的编号是顺序的,但是数据行不是.现在我们讲解一下下面查询语句的查询过程
select lname from testhash where fname='Peter';
mysql先计算“Peter”的哈希值,并使用该值寻找对应的记录指针,因为f('Peter')=8784,所以mysql在索引中查找8784,可以找到指向第3行的指针,
最后一步是比较第三行的值是否为‘Peter’,以确保就是要查找的行.
因为索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快.然而,哈希索引也有他的限制,如下:
1.哈希索引只包含哈希值与行指针,而不存储字段值,所以不能使用索引中的值来避免读取行,不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的
影响并不明显.
2.哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序.
3.哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容计算哈希值的
4.哈希索引只支持等值比较查询,包括=,IN(),<=>,也不支持任何范围查询,
5.访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值),当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,
直到找到所有符合条件的行
6.如果哈希冲突很多的话,一些索引维护操作的代价也会很高,例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行的时候,存储引擎需要遍历
对应哈希值的链表的每一行,找到并删除对应行的应用,冲突越多,代价越大
因为以上的这些限制,所以哈希索引只适用于某些特定的场合,而一旦适合哈希索引,则它带来的性能提升将非常显著.
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”,当InnoDB注意到某些索引值被使用得非常频繁的时候,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样就让B-Tree索引也
具有哈希索引的一些优点,比如快速的哈希查找.这是一个完全自动的、内部的行为,用户无法控制或者配置,不过如果有必要,完全可以关闭该功能.
三.创建自定义哈希索引
如果存储引擎不支持哈希索引,则可以模拟像InnoDB一样创建哈希索引,这样可以享受一些哈希索引的便利,例如只需要很小的索引就可以为超长的键创建索引.
思路很简单:在B-Tree基础上创建一个伪哈希索引,这和真正的哈希索引不是一回事,因为还是使用B-Tree进行查找,但是它使用哈希值而不是键本身进行索引查找,你需要做的就是
在查询的where子句中手动指定使用哈希函数
下面通过一个例子进行讲解,例如需要存储大量的URL,并且需根据URL进行搜索查找,如果使用B-Tree来存储URL,存储的内容就会很大,因为URL本身都很长,正常情况下会有以下查询:
select id from url where url=''
