Py学习  »  DATABASE

MySQL索引入门指北

愚辛 • 4 年前 • 201 次点击  
阅读 165

MySQL索引入门指北

自从两年前了解到的索引以来的,就一直想写一篇有关索引的文章。然而我是个拖延癌症患者,一拖就是两年,不愧是我。该篇文章算是自己的笔记,欢迎批评。

概述

索引是什么?很多书和文章都会使用图书的目录来类比。目录的目的就是用方便我们查找具体内容的位置,具体的章节的范围。与此类似,MySQL中索引的用途是帮助我们加速查询以及排序。

在InnoDB中的索引类型有哈希索引、B+树索引、全文索引。哈希索引在InnoDB中设计为自适应的,不展开讨论。在InnoDB1.12之后有了全文索引,也是应用倒排,还没踩过坑(据说不支持中文),有时间可以研究一下。

本篇主要讨论B+树索引。

B+树

学习MySQL的索引,必须得先了解其原理,否则很多问题将一头雾水。下文将讲述索引数据结构的原理,而不涉及其复杂的具体实现。

在谈B+树之前,不妨先思考,为什么索引能加快查询?为什么要用B+树作为索引而不用B树?哈希索引查询复杂度为O(1)为什么又不用哈希索引?

BST和AVL

在了解B树和B+树之前,先了解一下二叉搜索树(BST)和平衡二叉树(AVL)。

在顺序存储结构中(如数组),最快的情况是第一个就是目标值,最坏的情况是最后一个是目标值,假设有n个元素,用大O标记法平均的时间复杂度为O(n)。

使用二叉搜索树可以有效的优化搜索时间。利用二叉搜索树的特性左子节点比父节点小、右子节点比父节点大,可以很方便的进行二分查找,有效优化的搜索时间。

img1

正常情况下,我们使用二叉搜索树可以了,但如果出现以下的情况,二叉搜索树反而起不到效果,搜索的平均时间复杂度依旧为O(n)。

img2

引入平衡二叉树,深度差不超过1,从而保证不倾斜,或者说更矮,保证其搜索效率。

B树和B+树

既然平衡二叉树已经可以加快查询了,但实际上InnoDB并不会使用。在思考B树和B+树的相关问题的时候,离不开一个问题——磁盘IO。索引文件存储在磁盘,假设有平衡二叉树树高30,那么你可能要扫描30次磁盘才能完成搜索。

对于需要磁盘IO的情况,使用平衡二叉树依旧比较糟糕,所以需要引入多路树,即B树和B+树,使得树更“矮”。

img3

如果上述B树改成二叉树,那么树的高度就大了很多,换而言之就需要更多次的磁盘IO。

B+树是B树的变种。B+树的非叶子结点不存储数据,并且所有的叶子节点以双向链表的形式相连。

img4

现在的索引模型基本都是B+树。

相对于B树来说,B+树的搜索更加稳定,因为B树有的数据是分布在非叶子节点上的。

B树的叶子节点以链表的形式相连且按照规则排了序,通过B+索引,可以更加方便的获取范围数据。

这也是不使用哈希索引的原因。虽然哈希索引搜索的时间复杂度为O(1),但大部分时候我们并不会只查询一条记录,这种时候使用哈希索引就比较乏力了。

聚集索引

聚集索引,亦可称为主键索引。一张表只存在一个聚集索引。

聚集索引是根据其主键作为排序规则的B+树,搜索时根据其主键进行搜索。

其中叶子节点上存储着整条记录的数据。

InnoDB的B+树在磁盘中的存储是以数据页的形式,在树中间进行插入和删除操作涉及列“页分裂”和“页合并”的复杂过程(关于这点我个人也讲不明白,但可以类比AVL树的旋转去理解),十分耗性能,而直接插入尾部是比较快捷的方式,所以在很多的规范中写道,当使用InnoDB引擎的时候,强烈建议用一个与业务无关的自增id作为主键。

此外,删除也是一样的,很多时候会要求做伪删除,不仅仅只是为了数据分析,更是为了索引的性能。

非聚集索引

非聚集索引又称辅助索引,以非主键列来建立。非聚集索引可以有多个。非聚集索引和聚集索引的区别在于,非聚集索引的叶子节点并不存储整条记录的数据,而是存储指向的主键的指针。所以,当利用非主键索引进行搜索时,还需要通过主键索引获取整条数据。

单值索引

单值索引就是在数据表单个列上建立单个值。

CREATE INDEX index_name ON table_name(column);
复制代码

与主键索引类似,单值索引按照所指定列排序建立二叉树。当利用单值搜索到目标后,再通过主键索引去读取整条数据。

唯一索引

唯一索引与单值索引区别不大,只是唯一索引的值不会重复。

唯一索引除了能提高一些效率以外,有时也用来保证列的唯一性,如用户的手机号身份证等。这里不做过多赘述。

联合索引

创建联合索引时指定多列即可。

CREATE INDEX index_name ON table_name(column1, columm2, column3 [,...])
复制代码

联合索引会按照建立索引时的顺序,对每个字段进行排序。即第一个字段排完序,接着排第二个字段,第三...

img5

覆盖索引

在前面提到,非聚集索引搜索记录时还需要通过的主键索引,但如果查找的列刚刚好是联合索引的字段,那就没有必要去再去搜索主键索引,直接取叶子节点值即可,这就是覆盖索引。

为什么不用select *,原因就在此,不仅仅是为了减少读取更多列带来的开销,也是为了能够使用上覆盖索引。使用覆盖索引可以减少磁盘IO,有效提高性能。

下文将讲述有关联合索引的更多细节。

最左前缀原则

上文了解了联合索引,知道了联合索引的节点数据是按照建索引的顺序依次排序,由此我们引出了最左前缀原则,联合索引中,如果要用上索引字段,前面的字段不能跳过。如果上图的例子,假设是找column2=“ccc”的记录,大概的sql如下

SELECT some_column FROM table_name WHERE column2="ccc"
复制代码

这种情况下索引是用不上的,因为索引是先排序的column1,再排序column2,直接通过column2搜索,B+树并不知道怎么搜索。

索引失效

除了上述的最左前缀原则下索引的失效,还有其他索引失效情况。

  • 使用MySQL内置函数运算的列索引会失效。

  • 使用!=,is null,is not null 索引会失效。

    比如你查找id != 500的记录,相当把扫描id<500,以及id >500的记录,本质上全表扫描没啥区别。

  • 范围查询后的列无法使用。

    还是用上图的例子,假设查询 column1 <= 4的情况

    SELECT some_column FROM table_name WHERE column1 <= 4
    复制代码

    因为column1是排了序的,索引联合索引column1还是可以使用上的,但column1是范围数据,在这范围内column2并不有序。

  • 以通配符开头的模糊查询(LIKE "%string")。

    值得一提的是,LIKE "string%"是能用上索引的,类似于范围查询,查询从string开头的最小字符串stirng开头的最大字符串。知道了LIKE "string%"是能用上索引的就能理解为什么LIKE "%string"为什么用不上索引了。

还有其他情况的情况,可用MySQL的查询分析器进行分析。

InooDB使用的锁是行锁,但如果在更新时索引失效了,行锁会变成表锁,在开发中应该避免。

索引使用tip

  • 常用来分组和排序的字段可建立索引。

    索引的作用是查询和排序,order bygroup by是可以用上索引的,如果排序的有多字段,也是按照最左前缀原则。

  • 经常用来查询的字段可建索引。

  • 更新频繁的字段不要建立索引。

    频繁更新的字段如果建立来索引,更新时不仅更新数据,而且索引的B+树也会发生变化,开销比较大,得不偿失。

  • 选择性小的列不要建立索引。

    比如说性别字段,只有男或女或未知,百万数据里只有这三个值,建立索引毫无意义。

  • 索引尽量使用等值匹配。

  • 尽量使用覆盖索引。

小结

通过建立索引,可以有效的加速数据库的查询和排序。当谈及的数据库优化时,索引优化肯定跑不了。索引的使用有各业界大佬总结的技巧,但很多东西不是绝对的,不能以偏概全,在大数据以及复杂业务下,索引的维护算是玄学,需要不断寻找最佳的索引方案。

Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/55380
 
201 次点击