【mysql】索引相关的个人总结

  • Z时代
  • 2024-01-10
  • 分类：综合

database

重点参考：

  • MySQL索引原理及慢查询优化 （美团技术分享网站）：原理、示例优化都写的很好。

  • 索引很难么？带你从头到尾捋一遍MySQL索引结构，不信你学不会！：原理写的很好。

  • 【从入门到入土】令人脱发的数据库底层设计：很详细的底层原理

一定要仔细看其中讲的索引原理！！！本文中都是简单的总结。

参考：

  • 为什么用了索引，查询还是慢？

  • MySQL 索引必须了解的几个重要问题

  • 数据库中查询记录时是否每次只能使用一个索引？

  • 聚簇索引、非聚簇索引、回表：聚簇索引和非聚簇索引讲的很详细。

  • 聚集索引、辅助索引、覆盖索引、联合索引

  • MySQL索引选择及规则整理：仔细看里面提到的“前缀索引”

1. 重点知识概括

1.1索引类型

  • Clustered Index(聚簇索引 或 聚集索引)

  • Secondary Index(非聚簇索引 或 辅助索引 或 二级索引，一般指的都是 单列)

  • 联合索引，多列二级索引

  • 前缀索引，二级索引只截取前N个字符作为索引

  • Covering Index(覆盖索引)

1.2 相关原理

  • B+树

  • 最左前缀匹配原则

  • 联合索引的最左前缀匹配原则

  • Index Condition Pushdown (ICP), 索引下推

1.3 使用索引的疑问或总结

2. 索引类型

总体来说，索引类型只存在：聚簇索引 和 非聚簇索引（二级索引）。

联合索引、前缀索引都是非聚簇索引中的更明确分类。

覆盖索引（个人觉得）并不算一种索引类型，而是基于非聚簇索引的原理对查询的一种优化方式。

“回表查询”：

回到聚簇索引取行数据。1次回表查询需要2次B+树的遍历查找，所以应该尽量避免回表（不要刻意避免，以免得不偿失）。

2.1 Clustered Index(聚簇索引 或 聚集索引)

  • 键值的逻辑顺序决定了表中相应行的物理顺序

  • 叶子节点中存放了该索引对应的行记录的完整数据（重点）

  • InnoDB有且只有一个聚簇索引（一般都是PK，MyISAM中都是非聚簇索引）

  • 聚簇索引可以包含多个列（联合索引），但使用的列越少越好

  1. 为什么InnoDB只有一个聚簇索引，而不将所有索引都使用聚簇索引？

    因为“叶子节点中存放了该索引对应的行记录的完整数据”，如果所有索引都是聚簇索引，意味着每个叶子节点都保存一份数据，会造成数据的冗余和资源的浪费。

  2. 哪些列索引可以是聚簇索引？

    InnoDB中一般都是PK；

    如果不存在PK，则会选择唯一非空索引代替。

    如果不存在唯一非空索引，则会隐式定义一个PK来作为聚集索引。

  3. 建议向聚簇索引中插入有序的值

    例如，聚簇索引列是pk，建议选择int, auto_increment，而避免使用无序的UUID。

    a)无序的pk使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢

    b)无序的pk新插入数据时，可能需要插入到某些列的中间，这可能导致数据页分裂，从而移动行数据。

    c)有序的pk值很好的避免了上述无序的pk带来的问题。

2.2 Secondary Index(非聚簇索引 或 辅助索引 或 二级索引)

（一般都指的是 单列索引，相对 联合索引 而言）

  • 叶子节点不包含完整的行数据

  • 叶子节点除包含键值以外，还包含一个pointer（或者bookmark）用于告诉InnoDB哪里可以找到与索引相对应的行数据（即需要回表查询，也增加了IO次数）

  • 非聚簇索引 要远小于 聚簇索引 （mysql基于此特性，会优化一些sql，例如count(*)）

  1. 为什么叫二级索引的一种解释

    二级索引需要两次B+树的遍历查找才能取到数据。

    第一次通过二级索引找到索引的叶子节点，从而找到数据的主键（或者其聚簇索引的索引值），然后用该主键去聚簇索引中再次通过B+树查找到完整的行数据。所以，“回表”会有2次B+树的查找过程。

  2. 为什么辅助索引使用“聚簇索引的索引值”作为pointer，而不是使用"地址值"作为pointer？

    使用"地址值"带来的好处：

    1）"地址值"比"聚簇索引的索引值"占用更少的空间

    2）减少了1次B+树查找的过程。

    但是，相应的需要维护辅助索引，这是一个相当困难的维护工作。

    使用“聚簇索引的索引值”作为pointer时，当出现行移动或者数据页分裂时，辅助索引不受影响（即不需要维护 辅助索引）

  3. 辅助索引中的最左前缀匹配原则

    单列辅助索引遇到<, <=, =, >, >=, between, like（右边模糊）可以用到索引。

    假设存在索引(col_1)，例如liek "xxx%"是可以用到辅助索引的。

2.3 联合索引

属于辅助索引，只是：将多列作为索引，默认多列往右匹配。

  1. 联合索引中的最左前缀匹配原则

    联合索引遇到范围查询时就停止匹配。（待商榷）

    假设存在索引(a, b, c, d)，那么where a =1 and b = 2 and c > 3 and d = 4中，a, b可以用到联合索引。此时，创建(a, b, d, c)索引更合适，并且由于查询优化器的优化 where中 a,b,d可以任意顺序。

    （扩展疑问：以上联合索引中，c能否用到索引？参考后面提到的索引下推）

  2. 优化器对单列辅助索引与联合索引的选择

    例如存在单列辅助索引(col_1) 和 联合索引(col_1, col_2)，在执行查询时，优化器是选择 单列辅助索引 还是 联合索引，主要还是需要结合实际SQL。

    where col_1=xxx，可能会选择 单列辅助索引。（不确定，具体还是看 explain）

    where col_1=xxx order by col_2，选择 联合索引，因为col_2是在col_1的基础上排序，避免了进行1次filesort。

2.4 前缀索引

前缀索引能有效减小索引文件的大小，提高索引的速度。

但是前缀索引也有它的坏处：

1）不能在 ORDER BY 或 GROUP BY 中使用前缀索引

2）也不能把它们用作覆盖索引(Covering Index)。

针对2）的个人理解，前缀索引的叶子节点记录的也只是"主键"和"前缀值"，需要回表才能拿到完整的值。

例如，假设需要创建 article_title列的索引，但是 article_title 可能很长（索引占用空间多），那么可以只取article_title的前N个字符作为 前缀索引。

语法：CREATE INDEX index_name ON table_name(column_name(length));

2.5 Covering Index(覆盖索引)

InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。因此：

1) 使用覆盖索引可以避免回表查询（减少了大量的IO操作）

例如，假设存在索引(col_1, col_2, col_3)，现有查询SQL select * from table where col_1 = xx。如果在需求满足的情况下，可以有效利用覆盖索引来优化查询SQL select col_1, col_2, col_3 from table where col_1 = xx。

2) 有助于统计

例如，假设存在非聚簇索引(name)和聚簇索引(id)，在执行统计查询select count(*)时，查询优化器可能会选择使用 非聚簇索引。因为，非聚簇索引 要远小于 聚簇索引。

暂时还无法理解2)，特别是 聚集索引、辅助索引、覆盖索引、联合索引 中基于 联合索引 & count 的示例更不理解~~~

student表：PRIMARY KEY (id), KEY idx_name (name), KEY idx_school_age (school,age)`

执行sql：select count(*) from student

优化器会选用 idx_name 这个辅助索引。（具体看 explain）

执行SQL：select count(*) from student where age > 10 and age < 15

优化器会选用 idx_school_age 这个辅助索引。（具体看 explain）

3. 索引中的原理

3.1 B+树

  • MySQL索引原理及慢查询优化 （美团技术分享网站）：原理、示例优化都写的很好。

  • 索引很难么？带你从头到尾捋一遍MySQL索引结构，不信你学不会！：原理写的很好。

  • 【从入门到入土】令人脱发的数据库底层设计：很详细的底层原理

    再次，再次，再次通过这3篇大神的文章简单理解就好。如果想深入理解，再baidu/google。

3.2 （单列辅助索引的）最左前缀匹配原则

忽略。

3.3 联合索引的最左前缀匹配原则

相比单例辅助索引的最左前缀匹配原则，联合索引 是从左往右依次比较列。

例如col_1, col_2, col_3, col_4，先比较col_1，再比较col_2，以此类推。

3.4 Index Condition Pushdown (ICP), 索引下推

参考：

  • 索引下推（5.6版本+）

  • MySQL--索引条件下推优化

  • sql索引" title="mysql索引">mysql索引篇之覆盖索引、联合索引、索引下推

在前面提到了一个疑问：

where a = 1 and b = 2 and c > 3 and d = 4在已有联合索引(a, b, c, d)时，c/d能否用到联合索引？

在主要阅读的的2篇文章（美团大佬、java知音）都说的是：

最左前缀匹配原则，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的顺序可以任意调整。

其中并未提到c，而且个人觉得 c&d 都可以用到索引（只是不知道其性能如何）。

针对这疑问，我看到了索引下推。

例如以上SQL可能有2种执行可能：

1）假设 c&d 都没用到索引，根据联合索引查询到满足 a&b 的条件，然后就回表找到所有行数据，再进行遍历筛选出c > 3 and d = 4的数据行。

2）假设 c&d 都用到了索引，那么最后回表的数据行 一定小于等于 1)中回表的数据行，这就是mysql的索引下推

mysql默认启用索引下推，可以通过变量来修改：

SET optimizer_switch = "index_condition_pushdown=off";

注意：

a) 索引下推只能用于二级索引。（聚簇索引包含了行数据，这时候索引下推并不会起到减少回表操作的效果）

b) 索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。（没理解~~）

备注：

个人并不确定是 c&d 都用到索引，还是只有 c 用到索引，d未用到索引。

4. 索引使用中的疑问总结

(ps: cnblog的markdown对于 1.和-的解析貌似有错误，导致下面的序号是乱的)

  1. 索引不一定能提高查询速度，甚至可能比不存在索引时更慢！

  2. 一次查询只能用到1个索引

    如果多列查询存在多个索引，查询优化器一般选择区分度高的索引列。

  3. 区分度，简单公式：count(distinct col) / count(*)。

    意味着通过索引列可以返回更少的rows（回表查询的行数更少）

    具体需要看实际数据，比如假设is_download只存在true/false，当下载完成后将false改为true。

    此时实际业务数据是很少存在false，当存在大量查询false的时候，可以创建索引。

  4. 覆盖索引拥有更高效率和性能

  5. 无法使用索引的情况

  • <>, !=, not in

  • 对字段进行函数运算

  • 索引字段存在null

  • 字符串不加单引号，例如phone是varchar类型但sql是where phone=13800010002

  1. 创建索引的原则

  • 最左前缀匹配原则

  • 区分度高的列（美团文章提到）一般需要join的字段都要求是0.1以上。

  • 尽量的扩展索引，不要新建索引。

    联合索引 & 最左前缀匹配原则的优化，当存在(col_a, col_b)的联合索引后，大多情况下不需要再创建a索引

  • 例如书 “SQL Tuning“，如果选择性超过 20% 那么全表扫描比使用索引性能更优。

  1. 基于 新增/修改索引 来优化查询时，不能只看到当前需要优化的SQL，还需要结合该表的其余查询SQL来综合分析。

    例如，当前待优化sql创建了联合索引(col_1, col_2, col_3, col_4)，但是可能另外一条sql可能需要联合索引(col_1, col_2, col_4)。所以，最终联合索引(col_1, col_2, col_4, col_3)更适合。

  2. 联合索引，如何决定其col的顺序？

    最左前匹配原则&列的区分度 的理解运用，当然还要结合实际SQL。

  3. 范围查询是否会使用索引（例如 like、between-and、in）？

    可以使用到索引（但具体还是要看写法）。

  4. 性别字段是否需要创建索引（十万级以上的表，只有男/女）？

    为什么重复值高的字段不能建索引

    mysql千万级大表，关于性别及年龄字段是否需要加索引？

    没有绝对，要根据实际的数据。

    例如1亿的数据，其中只有10万的"男"数据，并且总是查询少的那部分数据，那么存在索引的效果更好。

（ps：整理完一看，并没有写或整理出多少东西...但磨磨唧唧也花费了蛮多时间）

以上是 【mysql】索引相关的个人总结 的全部内容， 来源链接： utcz.com/z/532205.html

回到顶部