MySQL——索引 | 青训营

wee
52 阅读8分钟

索引失效

  • explain 查看SQL的执行计划，通过这个查看是否命中索引
    • 只需要重点关注 type 、key 、rows 、filtered 、Extra
    • type连接类型（扫描方式）:速度（性能）从慢到快
      • image.png
    • key:实际使用的索引（显示索引名）
    • rows:扫描的行数
      • 估值，并不一定准确
    • filtered返回的行数
      • 表示符合条件的记录的百分比，百分比为 经过过滤后满足条件的记录数 / 存储引擎返回的记录数 
    • Extra额外信息
      • image.png
  • 常见
    1. 如果在查询语句的 WHERE 子句中出现 OR 并且 OR 的左右两边的字段中有一个字段不是索引列时，会出现索引失效，进行全表扫描（ALL）。
    2. 如果在查询语句中 对索引列使用函数（LENGTH(name) = 5） 或者 对索引列进行表达式计算（id+2=5），都会导致索引失效。
      • 原因：索引的B+树结构中索引键是原始的索引值，是没有经过计算或函数的，经过后无法在B+树找到对应的索引键
      • 可以转换，如id = 5 - 2，from_unixtime(create_time) = '2023-3-22'转换为 create_time = unix_timestamp('2023-3-22')避免索引失效
      • 需要计算的字段自然也不必建立索引了
    3. 如果在查询语句中使用 like 模糊匹配时，对于 like %XXX 和 like %XXX% 这样的形式模糊匹配时是无法命中索引（索引失效）的。但对于 like XXX% 这样的形式是可以命中索引进行索引检索的，一般走range。
      • 原因：B+树索引键有序，模糊查询根据指定的值按前缀的方式比较索引键来找到所需的记录
      • 对于像 Apple% 这样的值因为前面有明确的 A，所以完全可以使用索引快速缩小范围，进行索引范围查询。
      • 对于像 %Pro 或者 %Pro% 这类的值因为前面不具有明确的值，可以是任意值，这样索引就不知道从哪里开始检索，无法快速缩小范围，只能选择全表扫描，也就会导致索引失效。
    4. 如果在查询语句时，对字符串类型的索引列的输入参数类型为整型时，会导致索引失效。
      • 如 select * from products where stockNo = 1001; 中将为 varchar 类型的 stockNo比较时输入参数为整型 1001 时，虽然可以找出记录，但是使用的却是全表扫描，说明索引失效。（此时会发送数据库表的字符串转数字的情况）
      • 原因：MySQL的隐式转换：当遇到字符串和数字进行比较时，会自动将字符串转为数字进行比较，即回到2条件，隐式调用了函数，自然索引失效
      • 当然跟2条件一样，等式右边调用转换函数当然可以用，如果例子为 select * from products where id = '2'; 时，因为 id 是主键索引并且是整型，此时输入参数是字符串类型，但是通过隐式转换后会变为 id = 2，所以并不影响主键索引的使用，也就不会导致索引失效。
    5. 联合索引不匹配问题，最左原则
      • where a=3 and c=5，是否索引失效
      • 答案：否，不但没有，而且索引下推还帮忙
        • 在没有出现 索引下推 前，这条语句的执行过程是先去匹配 a 字段的索引键找到所有符合条件的主键后，通过回表操作将这些主键值经过主键索引找到对应的数据行，并且把这些数据都返回给Server层，然后再通过Server层的 where 比较 c 的值，过滤掉不符合的条件。
        • 在出现 索引下推 后，这条语句的执行过程是 先去匹配 a 字段的索引键找到所有符合条件的主键时，因为 c字段也在索引中，所以可以在存储引擎层就对 c 字段进行比较，过滤掉不符合 c字段的值，然后再将剩余符合条件的主键进行回表操作，通过主键索引找到对应的数据返回给Server层。

仅仅只是索引优化到 使用索引a字段，无法快速检索c字段

补充：索引下推限制

  • 大致如下

    1. ICP 仅适用于 二级索引，聚簇索引并不会减少IO操作
    2. ICP 目标是 减少回表查询。
    3. ICP 对联合索引的部分列模糊查询非常有效。

  • 官方

    • 当需要访问整个表行时，ICP 用于 range、 ref、 eq_ref 和 ref_or_null
    • ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。
      • 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。
      • 对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。
    • 引用子查询的条件不能下推。
    • 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。
    • 触发条件不能下推。
    • 不能将条件下推到包含对系统变量的引用的派生表。（MySQL 8.0.30 及更高版本)。

索引优化

  1. 前缀索引代替普通索引

    • 对字符串来说，代替后可以减少索引字段的大小，从而增大数据也存储索引的数量，可以提高索引的查询效率。

  2. 使用覆盖索引优化回表次数

  3. 主键索引中主键设置为自增（尽量用唯一自增做主键）

    • 插入新数据时，自增的主键值，让索引的B+树可以顺序追加，无序移动节点和调整树结构，插入效率变得更高
    • 非自增可能会导致页分裂，即当前数据页由于新数据的插入需要将数据页的数据隔开，所以需要新建一张数据页并且把当前数据页的一部分数据复制进新数据页，页分裂可能造成大量的内存碎片从而导致索引结构不稳定，影响查询效率。
  • 对于!=,IS NULL,IS NOT NULL,SQL语句优化器会去自己判断，若是走索引和表扫描区别不大，当然可以不走索引，但是如果它判断像主键 IS NULL这种条件，它甚至不会执行

最佳实践

  • 适合场景
    • 不为NULL的字段：对于数据为NULL的字段，数据库较难优化，字段被频繁查询时，避免不了为NULL。
    • 频繁用于where子句查询的字段：经常用于条件查询的字段可以建立索引，这样能提高查询的速度。
    • 经常排序的字段：经常被oreder by 或者 group by 使用的字段可以建立索引，这样在查询时就无需再做一次排序。
    • 被经常用于连接的字段：经常用于连接的字段涉及到表与表的关系，对于频繁被连接查询的字段，可以建立索引，提高多表连接查询的效率。
    • 尽可能地创建联合索引：相对于创建的单一索引来说，如果适合创建联合索引应当选择建立联合索引，因为联合索引多个字段在一个索引上，可以很大程度地减少磁盘占用的空间，提高维护索引效率。
    • 尽可能地创建前缀索引：对于字符串类型的字段，创建前缀索引相对于创建普通索引来说，能节约磁盘占用空间。
  • 不适合
    • 频繁需要更新的字段：对于需要经常更新的字段不适合建立索引，因为字段频繁被修改，而B+树需要维护索引键的有序性，所以频繁更新的字段会增加维护索引的成本，很影响数据库的性能。
    • 表中数据量过少：表数据太少时无需创建索引，因为全表扫描也不会很慢，无需以空间换时间。
    • 冗余索引：比如已经有联合索引(a, b, c)，则自然无需为字段 a 单独建立索引，此时单独建立字段 a 索引就是冗余索引。
    • 较低区分度（重复数据过多）的字段：较低区分度的字段很容易导致表中出现过多的重复数据，此时建立索引已经无法起到优化作用了，因为查询优化器在对比成本后会直接跳过索引选择全表扫描（索引失效）。
    • 需要参与计算的字段：索引列不能参与计算，因为B+树中存储的都是原始的字段值，如果需要参与计算的话，可能会导致索引失效（具体后面介绍）。虽然可以把 a + 1 = 5 这样的表达式变为 a = 5 - 1使得索引不失效，但针对一些较为复杂的表达式，无疑会增加查询成本，所以不建议在需要计算的字段上建立索引。（与索引失效相关）

总结

  • 要好好掌握，索引一直是很重要的部分
avatar
文章
阅读
粉丝