Mysql——索引

发表于 更新于 分类于 阅读次数： Waline： 本文字数： 3k 阅读时长 ≈ 5 分钟

数据结构

哈希表

可以O(1)的检索数据；

hash索引由于数据是随机的，不支持顺序和范围查询；

二叉查找树

性能依赖于平衡程度，最坏情况下会退化至O(n)；

AVL树（自平衡二叉查找树）

进行 O(logn) 次数的旋转操作，需要频繁地进行旋转操作来保持平衡，会有较大的计算开销；

每个树节点仅存储一个数据，因此一次磁盘IO只能获取一个数据；

红黑树

插入和删除节点时只需进行 O(1) 次数的旋转和变色操作；

平衡性相对较弱，可能会导致树的高度较高，导致一些数据需要进行多次磁盘 IO 操作才能查询到；

每个树节点仅存储一个数据，因此一次磁盘IO只能获取一个数据；

更适合数据在内存的情况；

B树（多路平衡二叉树）

所有节点既存放key也存放data；

B+树

只有叶子节点存放 key 和 data，其他内节点只存放 key，因此检索的效率都是稳定的；

每个叶子节点有一条引用链指向与它相邻的叶子节点；进行范围查询时，只需要找到下界后，对链表进行遍历即可；

每个节点存放多个key或data，减少了树高，而且一次磁盘IO可以取多个值，减少了IO次数。

聚簇索引与非聚簇索引

在innodb中，主键索引是聚簇索引，即索引结构和数据一起存放的索引。

其他所有索引都是二级索引，也是非聚簇索引，这些索引存的是主键。当使用二级索引查询到主键后，还要再到主键索引去查询，称为回表。

不过非聚簇索引不一定回表查询，如果SQL查的字段正好被索引覆盖了，那就没必要回表了，这种称为覆盖索引。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。

原理：联合索引的底层如下图所示，可以看到，a 是全局有序的（1, 2, 2, 3, 4, 5, 6, 7 ,8），而 b 是全局是无序的（12，7，8，2，3，8，10，5，2）。只有在 a 相同的情况才，b 才是有序的，比如 a 等于 2 的时候，b 的值为（7，8），即局部有序。

因此如果查询 where a>2 and b=7，可以使用到索引a，但在a>2的情况下，b是无序的，因此无法使用到索引b。

而如果查询 where a>=2 and b=7，虽然a>2的时候用不了索引b，但在a=2的时候，b是有序的，因此可以使用到索引b，然后后面再用链表遍历。

image-20250306161018744

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

对于查询SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;，其中的 birthdate 字段使用函数导致索引失效。

没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3。

有了索引下推之后，存储引擎会在使用zipcode 字段索引查找zipcode = '431200' 的用户时，同时判断MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。

索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。

索引失效

以下情况会导致索引失效：

  1. 当select * 且 where 范围查找过大时，有可能直接走全表扫描而不是使用索引；
  2. 联合索引，但未遵循最左匹配原则；
  3. 在索引列上进行计算、函数、类型转换等操作（因为索引保存的是索引字段的原始值，而不是经过函数计算后的值）；
  4. 以 % 开头的 LIKE 查询比如 LIKE '%abc';；
  5. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到；
  6. IN 或 NOT IN的取值范围较大时会导致索引失效，走全表扫描
  7. 发生隐式转换，当 where 查询操作符左边为字符类型时发生了隐式转换，会导致索引失效；

隐式转换

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。

  1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换；

  2. 两个参数都是字符串，会按照字符串来比较，不做类型转换；

  3. 两个参数都是整数，按照整数来比较，不做类型转换；

  4. 十六进制的值和非数字做比较时，会被当做二进制串；

  5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp；

  6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较；

  7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较；

关于第7条，具体的：

  • 不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；

  • 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如'123abc'会转换为123，'012abc'会转换为012也就是12，'5.3a66b78c'会转换为5.3，其他同理。

因此，隐式转化可能导致索引失效，如下：

  • SELECT * FROM test1 WHERE num1 = '10000';左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型'10000'，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

  • SELECT * FROM test1 WHERE num2 = 10000;左边是字符串类型'10000'，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，'10000'转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如'10000a'，'010000'，'10000'等等都能转为浮点数10000，这样的情况下，是不能用到索引的。