首先我们要认识到索引的各种类型;并在认识的基础上进行对比;
B-Tree索引;
存储引擎的不同,会用到不同的技术;
MyISAM使用前缀压缩技术使得索引更小;
InnoDB则按照数据格式进行存储;
MyISAM索引通过数据的物理位置引用被索引的行;
InnoDB根据主键引用被索引的行;
对于B-Tree索引的自我总结和概括:
简单地说呢,就相当于我们的书本目录一样了,而InnoDB的索引就是根据主键来查找的,因此,当我们建立索引之后,对于查询的方式,就是现在索引中查找你想要的主键,对应好之后,再跳到那一个主键对应的数据行,去查询所有的数据;
伴随着这种索引,我们就得出了以下几种适用索引查找的查询方法;
假定现在的索引有三列,于是我们得出以下的匹配方式;
全值匹配:三列要求全部匹配,全值匹配;
匹配最左前缀;只匹配最左层的值,只使用索引的第一列
匹配列前缀;匹配第一列的一些部分字母;
匹配范围值;范围匹配;
精确匹配某一列并范围匹配另外一列;从左到右,先精确匹配一列之后再范围匹配查找,但是记得,范围查找之后不能再做精确匹配了;
只访问索引的查询;专门就是只访问索引的;
但随之而来的还有B-Tree索引的限制:
如果不是从最左前缀开始匹配的话,将无法使用索引;
不能跳过索引中间的列,例如,你不能只匹配第一列和第三列;而跳过了第二列,这是不允许的;
如果查询中有某个列的范围查询,在这之后的所有列都无法使用索引优化查找,这点在前面有说到了;
哈希索引;
基于哈希表实现,只有精确匹配索引所有列的查询才有效;对于每一行数据,计算出一个哈希码,不同键值的行计算出来的哈希码也不一样,将得出来的哈希码存入索引中,同时在索引中保存指向每行数据的指针;
因为哈希索引只存储对应的哈希值,所以索引的结构十分紧凑;也让哈希的查找的速度非常快;
哈希索引的限制:
哈希索引里面只包含哈希值和指针,而不存储字段值,所以无法通过索引来避免读取行,意思也就是说,由于索引中不包含行的数据,所以没有办法通过只读取索引来进而读取整个行数据,不过,访问内存中的行的速度很快,所以也问题不大;
哈希索引不是按照索引值顺序存储的,所以也就无法用于排序了;
哈希索引也不支持部分索引列匹配查找,因为你哈希索引毕竟是用索引列的全部内容来计算哈希值的,没办法只用部分索引查询;
哈希索引只支持等值查询,不支持范围查找;
访问哈希索引的速度非常快,除非有很多哈希冲突;
如果哈希冲突很多的话,一些索引维护操作的代价也会很高,因为你如果索引冲突了,就需要遍历对应哈希值的链表中的每一行,直到找到为止;
当我们出现哈希冲突的时候,我们要注意,我们不仅要给出哈希值,还要给出对应的索引内容,这样方便我们解决哈希冲突,毕竟哈希值相同时,只能通过这样的方式去区别了;
空间数据索引;
emmmm。。。只剩下MyISAM表支持空间索引了,无需前缀查询,而是从所有维度来索引数据;
全文索引;
一种特殊类型的索引,查找的文本中的关键词;而不是直接比较索引中的值;
索引优点:
拿B-Tree索引来比较,因为数据有序,可以将相关列值都存储在一起,而且因为索引中存储了实际的列值,所以某些查询只需要通过索引就能完成了;
三大优点;
索引大大减少了服务器需要扫描的数据量;
索引可以帮助服务器避免排序和临时表;
索引可以将随机I/O变为顺序I/O;
高性能的索引策略:
独立的列:要求建立索引的列必须是独立的列;
前缀索引和索引选择性;一般我们都是追求,高选择性的索引的。什么是高选择的索引呢?是指不重复的索引和数据表的记录总数的比值,在1/n和1之间,值越大,则选择性越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的;对于BLOB,TEXT或者很长的VARCHAR类型的列,我们要求必须使用前缀索引,因为不允许索引这些列的完整长度;
多列索引:通过在不同的列上建立不同的列索引,来进行匹配查找;但是事实却证明,使用多列索引只能说明的你的索引建的很糟糕;
选择合适的索引列顺序;正确的顺序依赖于使用该索引的查询,同时考虑如何更好地满足排序和分组的需要;将选择性最高的列放在最前面,只能说是一个比较通用方法,但不是最佳的办法,具体看具体情况的分析;
聚簇索引:
聚簇索引:将索引和对应的数据行都存储在聚簇索引中,也就是说,并不是一种单独的索引类型,而是一种数据存储方式,聚簇索引实际上是保存了B-Tree索引和数据行;
在这里我们要强调一下的就是B-Tree和聚簇索引的区别:
首先,这两者是没法比的,聚簇索引是B-Tree索引的一部分,B-Tree本身是一种索引类型了,就是按照B-Tree数据结构来实现的,而对于B-Tree索引来说,是用哪一个索引并不重要了,可能是主键,也可能是全部的数据,但是一定是保存了指向对应数据行的指针的,这是一点,而聚簇索引就是保存了B-Tree索引和对应的数据行,方便了数据的查找;
聚簇索引的优点:
可以将相关数据保存在一起;
数据访问更快;
使用覆盖索引扫描的查询可以直接使用主键值;
聚簇索引的缺点:
最大限度地提高了I/O密集型应用的性能;但是访问顺序没有那么重要了,没有什么优势;
插入速度严重依赖于插入顺序;
更新聚簇索引列 的代价很高;
会面临页分裂的的问题;
聚簇索引会导致全表扫描变慢;
想一起学习探讨java的朋友可以加群
874811168 一起学习一起进步
还能免费领取java资料。
二级索引(非聚簇索引)可能比想象的要更大;
覆盖索引:
是指索引的叶子节点中已经包含要查询的数据,如果一个所以包含所有需要查询的字段的值,就称为覆盖索引;直接扫描索引就可以查询到你想要的值;
因为我们知道这个因为覆盖索引要求,你在索引中就将要查询的东西查到了,所以索引必须要存储索引列的值,因此,哈希索引,空间索引,全文索引都不能用于覆盖索引;