索引02之正确建立和使用索引

正确建立和使用索引

一：MySQL各类型索引的优劣分析

1：索引机制的优劣

优势：【查的快 -> 性能高】

• 整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。
• 通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。
• 在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。
• 连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。
• 索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。
• 从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

弊端：各种额外开销

• 建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。【空间额外开销】
• 写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。【操作额外开销】
• 写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。【维护索引的时间额外开销】

2：主键索引的陷阱

数据库的表中，主键一般都是使用自增ID，但这是为什么呢？

有人可能会回答自增ID不会重复，确保了主键唯一性，这样也确实没错

但不会重复的又不仅仅只有自增ID，比如我使用随机的UUID也不会重复，为何不使用UUID呢？

一张表中大多数情况下，会将主键索引以聚簇的形式存在磁盘中，聚簇索引在存储数据时，表数据和索引数据是一起存放的。

同时，MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。

所以，主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的

那再反推前面给出的疑惑，为何不使用UUID呢？因为UUID是无序的

如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构

上面的绿色节点，是一条新插入的数据，此时经过计算后，应该排第二个位置，那就代表着后面的三个节点需要移动，然后给灰色节点挪出一个位置存储，从而确保索引的有序性。

由于主键索引是聚簇索引，如果主键字段值无序，那代表着几乎每次插入都有可能导致树结构要调整。

所以数据表的主键，最好选用带顺序性的值，否则有可能掉入主键索引的“陷阱”中

3：联合索引的矛盾

假设person表中有如下字段，并创建联合索引如下：

alter table person add index my_test_union_index(name, age);

想要使用联合索引，那么查询条件中必须包含索引的第一个字段

🎉 不走索引不是绝对的，下面索引覆盖会说

4：前缀索引的弊端

前缀索引的特点是短小精悍，我们可以利用一个字段的前N个字符创建索引，以这种形式创建的索引也被称之为前缀索引

相较于使用一个完整字段创建索引，前缀索引能够更加节省存储空间，当数据越多时，带来的优势越明显。

不过前缀索引虽然带来了节省空间的好处，但也正由于其索引节点中，未存储一个字段的完整值

所以MySQL也无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也无法完成覆盖扫描等操作。

5：唯一索引的快慢

唯一索引有个很大的好处，就是查询数据时会比普通索引效率更高，因为对于一棵索引树，唯一索引碰到对应的就结束了，但是普通索引要走完

但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。

但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。

6：哈希索引的问题

哈希索引，也就是数据结构为Hash类型的索引，但接触的比较少，毕竟创建索引时都默认用的B+树结构

但要比起查询速度，哈希索引绝对是MySQL中当之无愧的魁首！

因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据

但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。

因此如果你确定一个表中，不会做排序这类的工作，那可以适当选用哈希结构作为索引的数据结构，它会给你带来意想不到的性能收益

二：正确建立索引

当SQL查询性能较慢时，我们常常会有一个疑惑：表中哪个字段建立一个索引能带来最大的性能收益呢？

一般来说，判断字段是否要添加的索引的依据，是看这个字段是否被经常当做查询条件使用

但也不能光依靠这一个依据来判断，比如用户表中的性别字段，就会经常被用做查询条件

但如果对性别字段建立一个索引，那对查询的性能提升并不大，因为性别就两个值：男/女

那对其建立索引，索引文件中就只会有两个索引节点，大致情况如下：

这种情况下，为性别建立一个索引，带来的性能收益显然不是太大。

同时，上图不是索引真正的样子

如果表中存在主键索引或聚簇索引，对其他字段建立的索引，都是次级索引，也被称为辅助索引，其节点上的值，存储的并非一条完整的行数据，而是指向聚簇索引的索引字段值。

1：回表问题

什么叫做回表呢？意思就是指一条SQL语句在MySQL内部，要经过两次查询过程才能获取到数据。

这是跟索引机制有关的，下面以姓名字段建立的索引在MySQL内部真正的样子：

在上图用户表中，基于ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引

此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。

而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。(非聚簇索引)

那回表是怎么出现的呢？

假设针对上图，现在有如下sql

select * from user where name = "竹子";

首先会走name字段的索引，然后找到对应的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回。

需要遍历两棵索引树，这个过程就叫回表【非聚簇索引中没有拿到select要的全部的信息，还要上聚簇索引中去取出行数据】

回表动作会导致额外的查询开销，因此尽量可以基于主键做查询，如果实在需要使用非主键字段查询，那么尽量要写明查询的结果字段，而并非使用*。

当然，实际情况中建立联合索引，利用索引覆盖特性，从而避免使用辅助索引，这样也能够消除回表动作【下面的索引覆盖会补充此处的内容】

2：建立索引时要遵守的原则

通用原则

• 经常频繁用作查询条件的字段应酌情考虑为其创建索引。
• 表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
• 建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
• 建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
• 建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
• 经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
• 对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
• 尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

注意事项

• 值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
• 一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
• 索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
• 一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
• 建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
• 当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
• 索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

3：最左前缀匹配原则

只有联合索引才有最左前缀匹配原则，不是啥索引类型都有

在上面通用原则中提到，尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高

举个栗子理解，比如此时基于X、Y、Z字段建立了一个联合索引，实际上也相当于建立了三个索引：(X)、(X、Y)、(X、Y、Z)

因此只要查询中使用了这三组字段，都可以让联合索引生效。

但如若查询中这三个字段不以AND形式出现，而是单独作为查询条件出现，那单值索引性能会好一些，但三个不同的索引，维护的代价也会高一些。

其实联合索引的最左前缀原则，道理很简单的，就是组成联合索引的多个列，越靠左边优先级越高

同时也只有SQL查询条件中，包含了最左的字段，才能使用联合索引

所以在上面注意事项中才会提到，建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

因为将查询频率越高的字段放首位，就代表着查询时命中索引的几率越大。

同时，MySQL的最左前缀原则，在匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引

select * from tb where X="..." and Y > "..." and Z="...";

由于Y出现了范围查询，所以上面的语句只能使用(X)索引，而不能使用(X、Y)和(X、Y、Z)

4：索引失效问题

4.1：执行分析工具 - explain

🎉 这里只是先说明下这个命令结果中的参数的各个意义是啥，后面会详细的说这个工具

当在一条SQL前加上explain命令，执行这条SQL后会列出所有的执行方案：

• id：这是执行计划的ID值，这个值越大，表示执行的优先级越高。
• select_type：当前查询语句的类型，有如下几个值： <----------- 核心1
  • simple：简单查询。
  • primary：复杂查询的外层查询。
  • subquery：包含在查询语句中的子查询。
  • derived：包含在FROM中的子查询。
• table：表示当前这个执行计划是基于那张表执行的。
• type：当前执行计划查询的类型，有几种情况： <----------- 核心2
  • all：表示走了全表查询，未命中索引或索引失效。
  • system：表示要查询的表中仅有一条数据。
  • const：表示当前SQL语句的查询条件中，可以命中索引查询。
  • range：表示当前查询操作是查某个区间。
  • eq_ref：表示目前在做多表关联查询。
  • ref：表示目前使用了普通索引查询。
  • index：表示目前SQL使用了辅助索引查询。
• possible_keys：执行SQL时，优化器可能会选择的索引（最后执行不一定用）。
• key：查询语句执行时，用到的索引名字。 <----------- 核心3
• key_len：这里表示索引字段使用的字节数。
• ref：这里显示使用了那种查询的类型。
• rows：当前查询语句可能会扫描多少行数据才能检索出结果。
• Extra：这里是记录着额外的一些索引使用信息，有几种状态：
  • using index：表示目前使用了覆盖索引查询
  • using where：表示使用了where子句查询，通常表示没使用索引。
  • using index condition：表示查询条件使用到了联合索引的前面几个字段。
  • using temporary：表示使用了临时表处理查询结果。
  • using filesort：表示以索引字段之外的方式进行排序，效率较低。
  • select tables optimized away：表示在索引字段上使用了聚合函数。

4.2：索引失效的情况

• 查询中带有OR会导致索引失效
• 模糊查询中like以%开头导致索引失效【注意是开头有%，没有说like结尾有%会失效】
• 字符类型查询时不带引号导致索引失效
• 索引字段参与计算导致索引失效
• 字段被用于函数计算导致索引失效
• 违背最左前缀原则导致联合索引失效
• 不同字段值对比导致索引失效 (where name = sex)
• 反向范围操作导致索引失效(not in, not like, is not null, !=, <>…)
• 当走索引扫描的行数超过表行数的30%时，会默认放弃索引查询

关于索引是否会失效，实际上也跟索引的数据结构、MySQL的版本、存储引擎的不同有关

例如一条SQL语句在B+Tree索引中会导致索引失效，但在哈希索引中却不会（好比IS NULL/IS NOT NULL）

这种情况在不同版本、不同引擎中都有可能会体现出来。

三：正确使用索引

现在有index_test表，其中有五个字段，id为主键：

构建如下的联合索引：

alter table index_test add index my_union_index(a, b, c);

插入四条数据准备进行测试：

1：索引覆盖

前面说由于表中只能存在一个聚簇索引，一般都为主键索引，而建立的其他索引都为辅助索引，包括联合索引也不例外

最终索引节点上存储的都是指向主键索引的值

虽然这条SQL会走联合索引查询，但是基于联合索引查询出来的值仅是一个指向主键索引的ID

然后会拿着这个ID再去主键索引中查一遍，这个过程之前聊过，被称为回表过程。

那么回表问题无法解决吗？必须得经过两次查询才能得到数据吗？答案并非如此。

比如假设此时只需要a、b、c这三个字段的信息，此时SQL语句可以更改为如下情况：

explain select a, b, c from index_test where a = '1' and b = '2';

此时将SQL更改为查询所需的列后，就不会发生回表现象

因为此时所需的a, b, c三个字段数据，在联合索引中完全包含，因此可以直接通过联合索引获取到数据。

但如果查询时用*，因为联合索引中不具备完整的一行数据，只能再次转向聚簇索引中获取完整的行数据

因此到这里大家应该也明白了为什么查询数据时，不能用*的原因，这是因为会导致索引覆盖失效，造成回表问题。

2：索引覆盖优化最左前缀

看下面这个sql

explain select a, b, c from index_test where b = '2';

上述这条SQL，显然是不符合联合索引的最左前缀匹配原则的，但是依然走了索引，这个优化的原因也是基于索引覆盖

🖊 要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

3：索引下推

索引下推是MySQL5.6版本以后引入的一种优化机制，将筛选下推到存储引擎来做

可以通过命令set optimizer_switch=‘index_condition_pushdown=off|on’;命令来手动管理。

set optimizer_switch='index_condition_pushdown=on';
set optimizer_switch='index_condition_pushdown=off';

4：MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施

一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据

回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO

select * from student where score between 0 and 59; # 找到所有的不及格的学生 

假设成绩字段上存在一个普通索引，那思考一下，这条SQL的执行流程是什么样的呢？

1. 先在成绩字段的索引上找到0分的节点，然后拿着ID去回表得到成绩零分的学生信息。
2. 再次回到成绩索引，继续找到所有1分的节点，继续回表得到1分的学生信息。
3. 再次回到成绩索引，继续找到所有2分的节点…
4. 周而复始，不断重复这个过程，直到将0~59分的所有学生信息全部拿到为止。

假设此时成绩0~5分的表数据，位于磁盘空间的page_01页上，而成绩为5~10分的数据，位于磁盘空间的page_02页上，成绩为10~15分的数据，又位于磁盘空间的page_01页上。

此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但0_5、1015分的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

MRR实现原理

MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中

然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序

从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

如何开启和关闭呢？

set @@optimizer_switch='mrr=on|off, mrr_cost_based=on|off';

可以通过上述这条命令开启或关闭MRR机制，MySQL5.6及以后的版本是默认开启的。

5：索引跳跃式扫描(8+, 了解)

在讲联合索引时，咱们提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询

但实际上这条规则也并不是100%遵循的。

在MySQL8.13+版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

原理

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

select * from tb_xx where B = xxx and C = xxx;

按理来说，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的

但是，这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊

因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

select * from tb_xx where B = xxx and C = xxx
union
select * from tb_xx where B = xxx and C = xxx and A = "yyy"
......
select * from tb_xx where B = xxx and C = xxx and A = "zzz";

其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍

一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。

开启和关闭

set @@optimizer_switch = 'skip_scan=on|off';

跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发…

具体的可以参考《MySQL官网8.0-跳跃扫描》

6：索引命令

最后再给出两条关于索引的查询命令：

• show status like '%Handler_read%'; -> 查看当前会话的索引使用情况。
• show global status like 'Handler_read%'; -> 查询全局索引使用情况。

这两个命令在sql调优中经常用到。

• Handler_read_first：表示读取表中第一条记录的次数。这通常在全表扫描或者根据索引进行有序查找的开头阶段会被用到。如果这个值较高，可能意味着有很多操作需要从表的开头开始读取数据，可能需要检查查询是否可以通过添加合适的索引来优化。
• Handler_read_key：表示通过索引键读取记录的次数。这是一种较为高效的读取方式，通过使用索引来定位到具体的记录。较高的Handler_read_key值通常表示查询能够有效地利用索引来获取数据，说明索引的使用情况良好，查询性能可能较好。
• Handler_read_next：表示按照索引顺序读取下一条记录的次数。通常在使用索引进行范围查询或者遍历索引记录时会出现。例如，在执行SELECT * FROM table WHERE column > value这样的查询时，如果column上有索引，就可能会使用Handler_read_next来按顺序读取满足条件的记录。
• Handler_read_prev：与Handler_read_next相反，它表示按照索引顺序读取上一条记录的次数。这种情况相对较少见，但在某些特定的查询场景中，如按照反向索引顺序进行遍历或者在某些特定的游标操作中可能会用到。
• Handler_read_rnd：表示按照随机顺序读取记录的次数。通常是在没有使用索引或者无法有效使用索引的情况下，MySQL 需要随机地从磁盘读取数据块来获取记录，这是一种相对较慢的读取方式。如果Handler_read_rnd的值过高，可能意味着查询没有很好地利用索引，需要考虑优化查询或者添加合适的索引。
• Handler_read_rnd_next：表示按数据文件中的顺序读取下一条记录的次数。这通常发生在全表扫描或者按照非索引列进行顺序读取的情况下。与Handler_read_next不同，Handler_read_rnd_next不是按照索引顺序读取，而是按照数据在磁盘上的存储顺序读取。如果这个值很大，可能说明存在大量的全表扫描操作，可能需要优化查询以减少全表扫描的使用。