MySQL 中的索引类型

索引的概念

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

要想把索引彻底搞明白，需要了解一下索引使用的数据结构 B+Tree。这样才能更好的理解索引。

---

BTREE

B+Tree其实是 B-Tree 的变体，了解 B+Tree 之前还要简单了解下 B-Tree

B-树
B-树是一颗平衡二叉树，数据量稍大一点的数据库，索引有几G,甚至更多，这么大的数据所以不可能一次性放入内存，只能放在磁盘中，那么在读取数据的时候，磁盘 io 就是一个很大的问题，想办法让磁盘 io 尽可能的少交互几次。

二叉查找树的效率非常高是O(logN)，B-Tree 的思想是还能不能在检索次数上进行优化。至于比较次数无需优化（因为磁盘 io 才是耗时的）就是在节点上多放一个或多个节点，这样在比较次数不变的情况下，磁盘 io 减少了。从而提升了效率。

为了营造这种平衡结构，那么在添加和删除的时候就需要维护这种平衡，而且还增加了存储空间，这就是为什么说索引会影响数据的写入速度。

B+树
B-树的根节点是存储数据的（卫星数据），这样查找数据的时候会不稳定，如果在根节点获取到数据销量非常高，如果在叶子节点读取到数据那销量相对来说比较低一些。

B+树的效率与B-树有一些共同的特点，但B+树也具备一些新的特征。
B+树索引（父）节点不存储数据，所有的数据都存储在叶子节点（与跳表类似），叶子节点之前用指针链接起来。这样叶子节点就是一条有序链表
B+树因为不存储卫星数据，更加节省空间，由于叶子节点有指针的存在，那么在做范围查找的时候效率会更高更便捷。

总和比较：B+树比 B-树 io 次数更少 ，查询性能更稳定，范围查询更便捷。

---

索引的类型

BTree 索引

索引有效

• 全值匹配
  与索引中所有的列进行匹配
• 匹配最左前缀
  只使用索引的第一列，或者前两列。如果索引是 key(name, age) 可以使用name 或 name, age 不可使用 age
• 匹配列前缀
  只匹配列开头的部分，例如 孙悟空，只匹配 孙
• 匹配范围值
  匹配一个范围 如 >= <=
• 精确匹配某一列并范围匹配另外一列
  第一列全匹配，第二列范围匹配
• 只访问索引的查询
  参见覆盖索引

索引的失效

• 如果不是按照索引最左列开始查找，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询， 则其右边所有列都无法使用索引优化查找

更多参见失效索引

哈希索引 (Hash Index)

哈希索引（hash index）基于哈希表的实现，只有精确匹配索引所有列的查询才有效。
在 MySQL 中，只有 Memory 引擎显式的支持哈希索引，NDB 集群引擎也支持唯一哈希索引。

例如

SELECT * FROM TABLE WHERE name = "jacky"

MySQL先计算哈希值，并使用该值去找对应的记录指针。

哈希索引资深只需要存储对应的哈希值，所以索引结构非常紧凑，这样让哈希索引的查找速度非常快

哈希索引的限制

• 无法用于排序，因为哈希索引数据并不是按照索引值顺序存储的
• 哈希索引也不支持不分索引列匹配查找，例如在数据列（a,b）建立索引，那么只查询 a 是无法使用索引的
• 哈希索引只支持等值比较查询包括（= in, <=>）也不支持范围查询。
• 哈希索引访问数据非常快，在哈希冲突较小的情况下
• 如果哈希冲突很多的话，性能下降很严重。

全文索引 (Full-Index)

空间数据索引 （R-TREE）

少用...

---

索引的策略

独立索引

索引不能是表达式的一部分，也不能是函数的参数，例如 where id+1 = 5

前缀索引和索引的选择性

前缀索引的长度要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）前缀应该足够长，以使得索引的选择性接近整个列。

完整列选择性的计算公式

SELECT COUNT(DISTINCT city) / COUNT(*) FROM table

如何创建一个前缀索引

ALTER TABLE tablename ADD KEY(name(5))

前缀索引是一种更小，更快的有效办法，但另一方面也有缺点，就是无法使用前缀索引做 order by 和 group by

多列索引

选择合适的索引列顺序

将选择性最高的列放到索引最前列（在不考虑排序和分组的情况下）

聚簇索引

聚餐索引并不是单独的索引类型，而是一种数据的存储方式。表记录的排列顺序和索引的排列顺序一致, 一个表只能又一个聚簇索引（覆盖索引可以墨迹多个聚簇索引的情况）。 InnoDB 的聚簇索引，如果没有主键，InnoDB 会隐式定义一个主键来作为聚簇索引。

优点：聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。

缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

聚集索引和非聚集索引的区别
聚集索引在叶子节点存储的是表中的数据（卫星数据）。 
非聚集索引在叶子节点存储的是主键和索引列。

覆盖索引

索引和锁

索引可以让查询锁定更少的行

创建索引的原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*) 表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp('2014-05-29')。
5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

参考地址：

https://tech.meituan.com/2014...
https://mp.weixin.qq.com/s/sT...
https://blog.codinglabs.org/a...
https://segmentfault.com/a/11...
高性能 MySQL MySQL 技术内幕