mysql B-Tree、哈希索引、R-Tree、全文索引、前缀索引

B-tree索引:使用B-tree数据结构来存储数据(实际上一般使用的是B+tree,即每一个叶子节点都包含指向下一个叶子节点的指针,为了方便叶子节点的范围遍历)

B-tree意味着所有的值都是按顺序存储的,且每一个叶子页到根的距离相同。


说明:存储引擎从索引的根节点开始进行搜索,通过节点槽中的指针向下层查找,比较节点页的值和要查找的值找到合适的指针进入下层子节点。存储引擎最终要么找到对应的值,要么该记录不存在。

叶子节点的指针指向的是被索引的数据,而不是其他的节点页

树的深度和表的大小直接相关


B-tree索引适用于全键值,键值范围,键前缀查找(只适用于根据最左前缀的查找)

B-tree索引对以下类型的查询有效:

全值匹配:和索引列中的所有列进行匹配

匹配最左前缀

匹配列前缀:只匹配某一列的值的开头部分

匹配范围值

精确匹配某一列并范围匹配另外一列

只访问索引的查询:只访问索引,无须访问数据行(覆盖索引)


索引可以按值查找之外,还可以用于查询中的order by操作(原因:索引树中的节点是有序的)


B-tree索引的限制:

1.如果不是按照索引的最左列开始查找,则无法使用索引

2.不能跳过索引中的列

3.如果查询中有某个列的范围查找,则其右边所有列都无法使用索引优化查找


哈希索引

哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效

原理:对每一行数据,存储引擎会对所有的索引列计算一个哈希码(很小且不同键值的行计算出的哈希码也不一样),哈希索引将所有哈希码存储在索引中,同时也保存着每个数据行的指针。

在mysql中,只有memory引擎显示支持哈希索引(注:为非唯一哈希索引),NDB支持唯一哈希索引,innodb支持自适应哈希索引

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

哈希索引的数据结构:哈希码+指向数据行的指针(注:哈希码是顺序的,数据行不是)

哈希索引的限制:

1.哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引的值来避免读取行

2.哈希索引数据并不是按照索引值顺序存储的,所以无法用于排序

3.哈希索引不支持部分索引匹配查找(因为哈希索引始终是使用索引列的全部内容来计算哈希值的)

4.哈希索引只支持等值查询,包括= ,in

5.当出现哈希冲突(不同的索引列值却有相同的哈希值)时,存储引擎必须遍历链表中所有的行指针,逐个比较直至找到符合条件的行

6.如果哈希冲突很多,则索引维护操作的代价会很高

要避免哈希冲突,必须在where条件中带入哈希值和对应列值。


空间数据索引(R-tree)

myisam支持空间索引,可以用作地理数据存储,R-tree无须前缀索引。空间索引会从所有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。


全文索引

通过查找文本中的关键词,类似于搜索引擎,而不是简单的where条件匹配


索引的优点:

1.索引大大减少了服务器需要扫描的数据量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变成顺序IO


索引的策略:

如果索引列是表达式的一部分或者是函数的参数,则不会使用到索引 如:where id+1=5;

始终将索引列单独放在比较符号的一侧


前缀索引:通过索引开始的部分字符,提高索引效率且可以节约索引空间,但会降低索引的选择性(选择性:不重复的索引值和数据表的记录总数),索引选择性越高则查询效率越高

对于BLOB、text或者很长的varchar类型的列必须使用前缀索引。

前缀应该足够长,使得前缀索引的选择性接近于索引整个列。

前缀索引无法使用order by和group by也无法使用覆盖索引


译者介绍:家华,从事mysqlDBA的工作,记录自己对mysql的一些总结

  • 0
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值