MySQL索引总结

/ 数据库 / 441浏览

索引

索引,是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。在数据量较少且负载较低时,不恰当的索引对性能的影响可能还不明显,但当数据量逐渐增加时,性能则会急剧下降。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级。索引的实现通常使用B树及其变种B+树。

索引存储结构

B-Tree索引

B-Tree 即Balance Tree,是一个平衡树(多路搜索树),又称BTree。

在B树中,每个结点最多含有4个子结点。除了根结点和叶子结点其它每个结点至少有2个结点。若根结点不是叶子结点,则至少有两个子结点。最重要的一点就是所有叶子结点都出现在同一层,叶子结点不包含任何关键字的信息

你可以将键和值存放在内部节点;B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

B+Tree索引

B+Tree是BTree的变体。在B+树中,内部节点都是键,没有值,叶子节点同时存放键和值。

由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。

B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间。

区别

数据库为什么使用B+树而不是B树?

聚簇索引

聚簇索引(也叫聚集索引),不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B+Tree索引和数据行。

InnoDB通过主键聚集数据,如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有找打这样的索引,会隐式定义一个逐渐来作为聚簇索引。一个表只能有一个聚簇索引。

聚集的数据优点:

缺点:

聚簇索引与非聚簇索引主要的区别

聚簇索引:

InnoDB存储引擎的主键是基于聚簇索引。聚簇索引就是整个表,每个叶子节点存储都包含了主键值、事务ID、回滚指针以及所有的剩余列。

还有一点不同就是,InnoDB的二级索引和聚簇索引很不同,InnoDB二级索引的叶子节点中存储不是行(主键)指针,而是主键值,并以此作为指向的指针。(这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作,但是会有2次查询的缺陷,上面有提到)

非聚簇索引:

非聚簇索引(普通索引)在叶子结点并不包含所有行的数据记录,只是会在叶子结点存有 自己本身的键值和主键的值。在检索数据时,通过普通索引叶子结点上的主键来获取想要查询的行数据记录。

另外,MyISAM存储引擎就是非聚簇索引。非聚簇索引相对聚簇索引数据存储分布上比较简单,索引中每个叶子节点都存储了行号与列值。它的主键列存储与二级索引存储格式上完全相同,都是在叶子节点存储行号与列值。实际上主键索引就是一个名称为Primary的唯一非空索引

聚簇索引字段使用建议

尽量不要使用UUID做主键索引,一是因为UUID的长度比较大,会导致索引占用的空间也大;二是由于UUID完全是随机的字符串,所以在新行插入时,会增加很多额外工作:

尽量使用顺序值来作为主键存储,例如在单库单表的情况下,可以采用自增列作为主键。在分库分表情况下,可以采用一些中间件(如Zookeeper)来处理,而最好不要图意省事,使用UUID。

索引类型

索引的缺点

时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度;

空间方面:索引需要占物理空间。

创建索引注意事项

  1. 单表索引:单表索引建议控制在5个以内,单索引字段数不允许超过5个。字段超过5个时,实际已经起不到有效过滤数据的作用了。

  2. 更新频繁:不要在更新频繁的字段建立索引,因为更新字段的同时,索引对应的存储位置也需要维护,频繁的更新会导致许多额外的系统开销。

  3. 非空字段:应该指定列为NOT NULL,除非你想存储NULL。在mysql中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;

  4. 取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高;索引字段越小越好:数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

  5. 最左前缀匹配原则:非常重要的原则,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的顺序可以任意调整。

    =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。