还不知道MySQL性能优化的，10种索引失效你知道几个？

沙蒿同学
173 阅读9分钟

摘要：本文将深入探讨MySQL索引失效的原因和底层原理，帮助读者理解索引的作用以及何时使用索引，以及在某些情况下为什么不建议使用索引。

引言

简介索引的概念和作用

索引是数据库中用于加快数据检索速度的一种数据结构。它类似于书籍的目录，可以帮助数据库快速定位到存储数据的位置，从而提高查询效率。通过创建索引，数据库可以在查询时直接定位到存储数据的位置，而不需要遍历整个数据表。这样可以大大减少磁盘IO操作，提高数据检索速度。

索引对查询性能的重要性

查询是数据库最常见、最重要的操作之一。在实际应用中，数据库通常需要处理大量的查询请求。如果没有合适的索引，数据库需要进行全表扫描来找到目标数据，这将导致查询变得非常缓慢，影响系统的整体性能。而通过创建适当的索引，数据库可以快速定位到目标数据，大大提高查询效率，使得系统能够更快地响应用户请求。

什么是索引？

索引的定义和特点

  • 索引是一个独立的数据结构，由一个或多个列组成。

  • 索引可以是唯一的，也可以是非唯一的。

  • 索引可以在数据表创建时定义，也可以在后续操作中添加或删除。

  • 索引可以加速数据检索，减少磁盘IO操作。

  • 索引会占用一定的存储空间，增加数据的插入、更新和删除的开销。

索引的类型

  • B-Tree索引：B-Tree（平衡树）索引是最常见的索引类型。它适用于范围查询和精确匹配查询，并且可以支持多列索引。B-Tree索引按照一定的排序规则对索引列进行排序，从而实现快速查找。

  • 哈希索引：哈希索引使用哈希函数将索引列的值映射为一个哈希码，然后将哈希码与数据的物理地址关联起来。哈希索引适用于等值查询，但不适用于范围查询。

  • 全文索引：全文索引用于对文本数据进行全文搜索。它可以在文本数据中查找关键字，并返回匹配的结果。全文索引适用于大量的文本数据查询，例如文章、博客等。

索引的数据结构和存储方式

  • B-Tree索引使用B-Tree数据结构，它是一种自平衡的二叉搜索树，可以快速定位到目标数据。

  • 哈希索引使用哈希表数据结构，可以通过哈希码快速查找到目标数据。

  • 全文索引使用倒排索引（Inverted Index）数据结构，它将文本数据中的关键字映射为文档的物理地址。

为什么要使用索引？

  • 提高查询性能：索引可以加速数据检索过程

通过创建索引，数据库可以直接定位到存储数据的位置，而不需要遍历整个数据表。这样可以大大减少磁盘IO操作，提高数据检索速度。例如，如果我们在某个列上创建了索引，数据库可以快速定位到满足查询条件的数据行，而不需要扫描整个表。

  • 减少IO操作：索引可以减少磁盘IO，提高查询效率

索引可以减少磁盘IO操作。当数据库需要读取数据时，它可以通过索引直接访问存储数据的位置，而不需要读取整个数据表（如上）。这样可以减少磁盘读取的次数，提高查询效率。索引可以创建在单个列上，也可以创建在多个列上，以满足不同的查询需求。

  • 优化排序和分组：索引可以加速排序和分组操作

索引可以加速排序和分组操作。当数据库需要对查询结果进行排序或分组时，它可以使用索引来快速定位和排序数据。例如，如果我们在排序列上创建了索引，数据库可以直接使用索引的排序顺序，而不需要进行额外的排序操作。

比如：有一个表中存在id、name、num三个字段，id自增，想查找num=12的数据

select * from where num = 12

就需要从1到12依次顺序查找,通过使用 EXPLAIN,type=ALL（全表扫描） image.png 其实12行记录查找也挺快的，但是如果存在100W行记录，查询第99999行，这几乎是全表查询，效率很慢。 使用索引后效果：大大减少查询次数，从而提高数据检索速度

image.png

什么场景下不建议使用索引？

  • 数据量较小的表：索引可能会增加查询成本

如果数据表非常小，可能没有必要创建索引。因为索引本身需要占用一定的存储空间，并且在插入、更新和删除数据时需要维护索引，这可能会增加开销。在这种情况下，直接扫描整个数据表可能比使用索引更快。

  • 经常进行大量写操作的表：索引维护的开销可能超过性能提升

在进行大批量的数据操作时，如果同时存在索引，会增加额外的索引维护开销。因此，在这种情况下，可以考虑在执行大批量操作前，暂时禁用索引，待操作完成后再重新启用索引。

  • 某些特定的查询场景：例如模糊查询、排序字段较多等

如果查询结果涉及大部分或全部数据，使用索引可能并不会提高查询性能。因为数据库需要遍历大部分或全部数据，而不是根据索引直接定位到目标数据。在这种情况下，使用索引可能会增加额外的IO操作，反而降低查询效率。

  • 数据列的基数非常低

基数是指数据列中不同值的个数。如果数据列的基数非常低，即数据列中的值重复较多， 比如我们常说的状态，1=是，0=否，那么使用索引可能并不会提供太多的性能优势。因为索引需要定位到具体的数据行，如果数据列中的值重复较多，那么索引的选择性就会降低，索引的效果可能会不明显。

索引失效的情况

  • 索引列类型不匹配：例如索引列和查询条件类型不一致
  • 使用函数操作：函数操作可能导致索引失效
  • 数据分布不均匀：索引列的数据分布不均匀可能导致索引失效

以下是一些示例，展示了在MySQL中可能导致索引失效的情况：

  1. 查询条件包含 OR：当查询条件中包含 OR 运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT * FROM orders WHERE customer_id = 1 OR order_id = 100;
  1. 索引列类型不匹配，字段类型是字符串，但是查询用数字类型，MySQL无法使用索引进行优化，导致索引失效。
SELECT * FROM orders WHERE order_id = 1; -- 错误的写法
SELECT * FROM orders WHERE order_id = '1'; -- 正确的写法
  1. LIKE 通配符导致索引失效：当使用 LIKE 运算符并且通配符在开头，例如 LIKE '%abc'，MySQL只能使用索引的前缀部分进行优化，后面的字符无法使用索引，导致索引失效。
SELECT * FROM orders WHERE order_number LIKE '%ABC';
  1. 联合索引，查询时的条件列不是联合索引中的第一个列：当使用联合索引，并且查询时的条件列不是联合索引中的第一个列时，MySQL无法使用索引进行优化，导致索引失效。（TODO最左匹配原则）。
    SELECT * FROM orders WHERE order_status = 'Pending' AND customer_id = 1; -- customer_id不是联合索引的第一个列
  1. 在索引列上使用 MySQL 的内置函数：当在索引列上使用 MySQL 的内置函数时，MySQL无法使用索引进行优化，导致索引失效。
SELECT * FROM orders WHERE YEAR(order_date) = 2022; -- order_date上有索引，但使用了YEAR函数
  1. 对索引列进行运算：当对索引列进行运算，例如加减乘除，MySQL无法使用索引进行优化，导致索引失效。
SELECT * FROM orders WHERE total_amount + 100 = 500; -- total_amount上有索引，但进行了加法运算
  1. 索引字段上使用 != 或 < >，NOT IN：当在索引字段上使用 != 或 < >，或者使用 NOT IN 运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
    SELECT * FROM orders WHERE order_status != 'Completed';
SELECT * FROM orders WHERE customer_id NOT IN (1, 2, 3);
  1. 索引字段上使用 IS NULL 或 IS NOT NULL：当在索引字段上使用 IS NULL 或 IS NOT NULL 运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT * FROM orders WHERE shipping_address IS NULL;
SELECT * FROM orders WHERE payment_date IS NOT NULL;
  1. 左连接或右连接查询关联的字段编码格式不一致：如果进行左连接或右连接查询，并且关联的字段的编码格式不一致，MySQL可能无法使用索引进行优化，导致索引失效。
    SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id; -- customer_id编码格式不一致
  1. MySQL估计全表扫描比使用索引快：如果MySQL估计使用全表扫描要比使用索引快，那么它可能选择不使用索引，导致索引失效。
SELECT * FROM orders WHERE status = 0; -- 当订单数据量很小时，MySQL可能选择全表扫描
avatar
文章
阅读
粉丝