摘要：本文将深入探讨MySQL索引失效的原因和底层原理，帮助读者理解索引的作用以及何时使用索引，以及在某些情况下为什么不建议使用索引。
引言简介索引的概念和作用索引是数据库中用于加快数据检索速度的一种数据结构。它类似于书籍的目录，可以帮助数据库快速定位到存储数据的位置，从而提高查询效率。通过创建索引，数据库可以在查询时直接定位到存储数据的位置，而不需要遍历整个数据表。这样可以大大减少磁盘IO操作，提高数据检索速度。
索引对查询性能的重要性查询是数据库最常见、最重要的操作之一。在实际应用中，数据库通常需要处理大量的查询请求。如果没有合适的索引，数据库需要进行全表扫描来找到目标数据，这将导致查询变得非常缓慢，影响系统的整体性能。而通过创建适当的索引，数据库可以快速定位到目标数据，大大提高查询效率，使得系统能够更快地响应用户请求。
什么是索引？索引的定义和特点?索引是一个独立的数据结构，由一个或多个列组成。
?索引可以是唯一的，也可以是非唯一的。
?索引可以在数据表创建时定义，也可以在后续操作中添加或删除。
?索引可以加速数据检索，减少磁盘IO操作。
?索引会占用一定的存储空间，增加数据的插入、更新和删除的开销。
索引的类型?B-Tree索引：B-Tree（平衡树）索引是最常见的索引类型。它适用于范围查询和精确匹配查询，并且可以支持多列索引。B-Tree索引按照一定的排序规则对索引列进行排序，从而实现快速查找。
?哈希索引：哈希索引使用哈希函数将索引列的值映射为一个哈希码，然后将哈希码与数据的物理地址关联起来。哈希索引适用于等值查询，但不适用于范围查询。
?全文索引：全文索引用于对文本数据进行全文搜索。它可以在文本数据中查找关键字，并返回匹配的结果。全文索引适用于大量的文本数据查询，例如文章、博客等。
索引的数据结构和存储方式?B-Tree索引使用B-Tree数据结构，它是一种自平衡的二叉搜索树，可以快速定位到目标数据。
?哈希索引使用哈希表数据结构，可以通过哈希码快速查找到目标数据。
?全文索引使用倒排索引（InvertedIndex）数据结构，它将文本数据中的关键字映射为文档的物理地址。
为什么要使用索引？?提高查询性能：索引可以加速数据检索过程
通过创建索引，数据库可以直接定位到存储数据的位置，而不需要遍历整个数据表。这样可以大大减少磁盘IO操作，提高数据检索速度。例如，如果我们在某个列上创建了索引，数据库可以快速定位到满足查询条件的数据行，而不需要扫描整个表。
?减少IO操作：索引可以减少磁盘IO，提高查询效率
索引可以减少磁盘IO操作。当数据库需要读取数据时，它可以通过索引直接访问存储数据的位置，而不需要读取整个数据表（如上）。这样可以减少磁盘读取的次数，提高查询效率。索引可以创建在单个列上，也可以创建在多个列上，以满足不同的查询需求。
?优化排序和分组：索引可以加速排序和分组操作
索引可以加速排序和分组操作。当数据库需要对查询结果进行排序或分组时，它可以使用索引来快速定位和排序数据。例如，如果我们在排序列上创建了索引，数据库可以直接使用索引的排序顺序，而不需要进行额外的排序操作。
比如：有一个表中存在id、name、num三个字段，id自增，想查找num=12的数据
select*fromwherenum=12
就需要从1到12依次顺序查找,通过使用EXPLAIN,type=ALL（全表扫描）
image.png其实12行记录查找也挺快的，但是如果存在W行记录，查询第行，这几乎是全表查询，效率很慢。使用索引后效果：大大减少查询次数，从而提高数据检索速度
image.png什么场景下不建议使用索引？?数据量较小的表：索引可能会增加查询成本
如果数据表非常小，可能没有必要创建索引。因为索引本身需要占用一定的存储空间，并且在插入、更新和删除数据时需要维护索引，这可能会增加开销。在这种情况下，直接扫描整个数据表可能比使用索引更快。
?经常进行大量写操作的表：索引维护的开销可能超过性能提升
在进行大批量的数据操作时，如果同时存在索引，会增加额外的索引维护开销。因此，在这种情况下，可以考虑在执行大批量操作前，暂时禁用索引，待操作完成后再重新启用索引。
?某些特定的查询场景：例如模糊查询、排序字段较多等
如果查询结果涉及大部分或全部数据，使用索引可能并不会提高查询性能。因为数据库需要遍历大部分或全部数据，而不是根据索引直接定位到目标数据。在这种情况下，使用索引可能会增加额外的IO操作，反而降低查询效率。
?数据列的基数非常低
基数是指数据列中不同值的个数。如果数据列的基数非常低，即数据列中的值重复较多，比如我们常说的状态，1=是，0=否，那么使用索引可能并不会提供太多的性能优势。因为索引需要定位到具体的数据行，如果数据列中的值重复较多，那么索引的选择性就会降低，索引的效果可能会不明显。
索引失效的情况?索引列类型不匹配：例如索引列和查询条件类型不一致
?使用函数操作：函数操作可能导致索引失效
?数据分布不均匀：索引列的数据分布不均匀可能导致索引失效
以下是一些示例，展示了在MySQL中可能导致索引失效的情况：
1.查询条件包含OR：当查询条件中包含OR运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREcustomer_id=1ORorder_id=;
2.索引列类型不匹配，字段类型是字符串，但是查询用数字类型，MySQL无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREorder_id=1;--错误的写法SELECT*FROMordersWHEREorder_id=1;--正确的写法
3LIKE通配符导致索引失效：当使用LIKE运算符并且通配符在开头，例如LIKE%abc，MySQL只能使用索引的前缀部分进行优化，后面的字符无法使用索引，导致索引失效。
SELECT*FROMordersWHEREorder_numberLIKE%ABC;
4.联合索引，查询时的条件列不是联合索引中的第一个列：当使用联合索引，并且查询时的条件列不是联合索引中的第一个列时，MySQL无法使用索引进行优化，导致索引失效。（TODO最左匹配原则）。
SELECT*FROMordersWHEREorder_status=PendingANDcustomer_id=1;--customer_id不是联合索引的第一个列
5.在索引列上使用MySQL的内置函数：当在索引列上使用MySQL的内置函数时，MySQL无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREYEAR(order_date)=;--order_date上有索引，但使用了YEAR函数
6.对索引列进行运算：当对索引列进行运算，例如加减乘除，MySQL无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREtotal_amount+=;--total_amount上有索引，但进行了加法运算
7.索引字段上使用!=或，NOTIN：当在索引字段上使用!=或，或者使用NOTIN运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREorder_status!=Completed;SELECT*FROMordersWHEREcustomer_idNOTIN(1,2,3);
8.索引字段上使用ISNULL或ISNOTNULL：当在索引字段上使用ISNULL或ISNOTNULL运算符时，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT*FROMordersWHEREshipping_addressISNULL;SELECT*FROMordersWHEREpayment_dateISNOTNULL;
9.左连接或右连接查询关联的字段编码格式不一致：如果进行左连接或右连接查询，并且关联的字段的编码格式不一致，MySQL可能无法使用索引进行优化，导致索引失效。
SELECT*FROMordersoLEFTJOINcustomerscONo.customer_id=c.customer_id;--customer_id编码格式不一致
10.MySQL估计全表扫描比使用索引快：如果MySQL估计使用全表扫描要比使用索引快，那么它可能选择不使用索引，导致索引失效。
SELECT*FROMordersWHEREstatus=0;--当订单数据量很小时，MySQL可能选择全表扫描