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 技术内幕

阅读 1k
79 声望
1 粉丝
0 条评论
79 声望
1 粉丝
文章目录
宣传栏