首先我们要认识到索引的各种类型; 并在认识的基础上进行对比;
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 密集型应用的性能; 但是访问顺序没有那么重要了, 没有什么优势;
插入速度严重依赖于插入顺序;
更新聚簇索引列 的代价很高;
会面临页分裂的的问题;
聚簇索引会导致全表扫描变慢;
二级索引 (非聚簇索引) 可能比想象的要更大;
覆盖索引:
是指索引的叶子节点中已经包含要查询的数据, 如果一个所以包含所有需要查询的字段的值, 就称为覆盖索引; 直接扫描索引就可以查询到你想要的值;
因为我们知道这个因为覆盖索引要求, 你在索引中就将要查询的东西查到了, 所以索引必须要存储索引列的值, 因此, 哈希索引, 空间索引, 全文索引都不能用于覆盖索引;
来源: https://www.cnblogs.com/zxx123/p/9737525.html