索引的类型
1.B-Tree索引
该索引底层的数据结构是B-Tree。B-树可以加快数据的访问速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始搜索。其实在根节点和叶子节点之间可能有更多层节点也,树的深度与表的大小直接相关。
B-Tree对索引列是顺序组织存储的,所以更适合查找范围数据。
B-Tree可以进行全值匹配,最左前缀,匹配前缀列,匹配范围值,精确匹配某一列并范围匹配某一列,只访问索引的查询。(注:不管哪种匹配,一定要满足左匹配思想,如索引构成字段有”A,B,C”,可以精确匹配A再范围匹配B,但不可以范围匹配B再精确匹配A。也不可匹配A后再匹配C,),索引还可以用于查询的Order BY子句中。
2.哈希索引
Hash索引是基于hash表实现的,只有精确的匹配索引所有列的查询才有效。因为索引自身只需要存储对应的hash值,所以索引的结构十分紧凑,这也让hash索引查找的速度非常的快(因为数据紧凑,每个hash值得存储得位数一样,适合查找)
Hash索引得限制
- 因为hash索引只包含hash值和行指针,而不存储字段值,所以不能使用索引中得值来避免读取行。(因为在内存中读取行的速度是非常快,所以大部分情况下这一点对性能的影响并不明显)
- Hash索引的数据并不是按照索引值顺序存储的,所以也就无法用于排序
- Hash索引不支持部分索引查询,因为hash值是通过所有索引列计算得来的
- Hash索引只支持等值查询和=,in(),<=>。不支持范围查询
- Hash索引中如果hash冲突过多,那么可能在某个hash值中有过多的链,并且还要比对链上的值直到找到
- 如果hash冲突过多,维护的代价太高
创建自定义的hash索引
自定义hash索引,最常见的一种方式是CONV(RIGHT(MD5('列’),16),16,10),当要防止hash索引所产生的hash冲突时,必须使用select id from urltable where hash64 = hash索引值 and url = ‘所查询列值’
空间数据索引
MyISAM表支持空间索引,可以用作地理数据存储。和B-Tree不同,这类索引无需前缀查询。空间索引会从所有唯独来索引数据。查询时,可以有效的使用任意唯独来组合查询。
全文索引
全文索引是一种特殊类型的索引,它查找的是文本中的关键字,而不是直接比较索引中的值。全文索引梗死和搜索引擎做的事情,而不是签单的where条件匹配。在同一个列中既可以简历全文索引又可以建立b-Tree索引
索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变成顺序I/O