了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助, 例如知道了InnoDB的索引实现后, 就很容易明白为什么不建议使用过长的字段作为 主键, 因为所有辅助索引都引用主索引, 过长的主索引会令辅助索引变得过大. 再例如, 用非单调的字段作为主键在InnoDB中不是个好主意, 因为 InnoDB 数据文件本身是一棵 B+Tree, 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整, 十分低效, 而使用 自增字段作为主键则是一个很好的选择
使用场景
- Archive 用来存日志
- memory用来存session
- MyISAM尽量不用
- 其他的都用InnoDB
MySQL索引类型有?
- B+树索引(O(log(n))): 关于B+树索引, 可以参考 MySQL索引背后的数据结构及算法原理
- hash索引:
- 仅仅能满足"=","IN"和"<=>"查询, 不能使用范围查询
- 其检索效率非常高, 索引的检索可以一次定位, 不像B-Tree 索引需要从根节点到枝节点, 最后才能访问到页节点这样多次的IO访问, 所以 Hash 索引的查询效率要远高于 B-Tree 索引
- 只有Memory存储引擎显式支持hash索引
- FULLTEXT索引: 现在MyISAM和InnoDB引擎都支持了
- R-Tree索引: 用于对GIS数据类型创建SPATIAL索引, 相对于BTREE, RTREE的优势在于范围查找
对比一下B+树索引和 Hash索引
B+树
一个平衡的多叉树. B+树从根节点到叶子节点的搜索效率基本相当, 不会出现大幅波动
哈希索引
采用一定的哈希算法, 把键值换成新的哈希值, 检索时不需要类似B+树那样从根节点逐级查找, 只需一次哈希算法即可立刻定位到相应的位置, 查询效率要远高于 B-Tree 索引
区别
等值查询哈希索引具有绝对优势（前提是: 没有大量重复键值, 如果大量重复键值时, 哈希索引的效率很低, 因为存在所谓的哈希碰撞问题. Hash 索引在任何时候都不能避免表扫描, 即使取满足某个 Hash 键值的数据的记录条数, 也无法从 Hash 索引中直接完成查询, 还是要通过访问表中的实际数据进行相应的比较, 并得到相应的结果
哈希索引不适用的场景:
- 不支持范围查询
- 不支持索引完成排序
- 不支持联合索引的最左前缀匹配规则
MySQL中, 只有HEAP/MEMORY引擎才显式支持哈希索引, 而常用的InnoDB引擎中默认使用的是B+树索引, 不能指定使用哈希索引, 只能通过设置自适应哈希索引间接使用.
