谈谈MySql中索引失效的情况

下列是一个索引可视化的网站：

索引可视化网站 (opens new window)

• https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

索引失效的情况

查看一个语句是否走索引，可以通过如下sql进行查看

#explain select * from tableName where 条件
explain select * from users where password="222" 

若对应的key有值，则为查询时走了索引。

当然，在mysql8.0之后，还可以加上analyze来查看具体的执行时间。

# 加上 analyze
explain analyze select * from users where password="222" 

结果，会返回类似结果的：

-> Table scan on <temporary>  (actual time=0.117..0.118 rows=2 loops=1)

实际花费了 0.117毫秒的时间。

不满足最左匹配原则

使用联合索引时，比如我们建立了name, age, phone 三个字段的联合索引。

alter table users add index uni_index(name,age,phone);
# 不走索引
explain select * from users where age=12 and phone= 1298672

因为最左优先原则：会先比较name属性，再接着age，最后phone，如没有，则无法匹配

所以如果where age = 12,则不会生效，而where age = 12 and name="test"则会生效。或者说只有(a),(a,b),(a,b,c)三种查询可以生效。

使用不等于（!= 、<>）

explain select * from user where age != 20;

通常情况来说，使用不等于是不走索引的，但也有另外，MySQL中有一个叫做优化器的东西，他会对每一条查询sql做成本分析，然后根据分析结果选择是否使用索引或者全表扫描。

这里举2种情况，如果不等于20的记录跟等于20的记录，数量都比较多，则成本很高，不会走索引。但如果本身记录很少，比如一共只有几条，则会走索引，或者不等于2的仅有10条，也会走索引。

总结：使用不等于的情况下，不一定不走索引，而是看成本，如果走索引成本不大，也会走索引，否则不会。

“OR”前后存在非索引列，导致索引失效

当where语句中使用or操作符并且两边的条件至少涉及两个字段,且字段中存在非索引列时，MySQL无法使用索引，会转向全表扫描。

**index_merge：**OR前后的两个条件中的列都是索引时，查询中才使用索引，索引类型是“index_merge”，将两个索引字段分别扫描，然后合并。

explain select * from diy.users where age =88 or hobby ='看电影';
# 使用了索引
explain select * from diy.users where age =88 or hobby ='看电影' or password='123'
# 以上语句 则没有使用索引

在上述两个语句中，因为age,hobby都使用了索引，所以第一个sql走了索引，而password没有索引，所以第二个sql不会走索引。

like中用通配符开头('%xxx')

# 在一个表中创建一个索引
alter table diy.users add index h_index(hobby);
# 以下语句 使用了索引
explain select * from diy.users where hobby like '看%'
# 未使用索引 花费时间更久
explain select * from diy.users where hobby like '%看'

1. %通配符：表示任意字符，可以匹配任意的字符数量。
2. _通配符：表示任意单个字符。

再来看个例子：

create table t4(
	id int not null primary key,
  col1 int,
  col2 varchar(50)
);
# 创建一个索引
create index idx4 on t4(col2);

#以下sql 有没有性能问题
select * from t4 where col2 like '%sql%';

它是不会走索引的，因为一开始是模糊的。如果要想其生效，可以改为如下：

select * from t4 where col2 like 'sql%'

使用Order By

explain select * from diy.users order by id,age;

以上sql语句，order后跟了2个字段，其中id是主键，但age没建立索引，则在查询时不会走索引。这是因为：ORDER BY子句中的列与索引列不完全匹配（即索引列包含部分数据而非完整列），所以MySQL可能无法有效地利用索引进行排序。

通常来说，order by后跟的列只要是索引列就会走索引，但有时即使order by后跟的列是索引列，也会发生索引失效的情况，这是因为优化器的作用，可能是由于查询优化器的估算成本较高，或者使用文件排序的开销较小等原因。

在索引列上进行计算、函数

现有一个需求，找出name为ty开头的字段信息：

create index idx_name on demos(name);
# 对索引列执行函数计算
explain select * from demos where left(name,2)='ty'

字段使用函数会让MySql中的优化器无法生效，因为函数的结构可能和B+树中的值不匹配，所以不会走索引。字段能不用函数就不用函数。

当然也有使用索引的解决方法：

SELECT * FROM demos WHERE name LIKE 'ty%';

是否生效

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

-- 创建一个覆盖索引
CREATE INDEX idx_order_date_total_amount ON orders (order_date, total_amount);
# 以下sql 会走索引 还是说 索引会失效
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE DATE(order_date) = '2023-10-01' AND total_amount > 50.00;

它可能会生效，可能不生效。为什么会这样呢？

其原因在于，优化器是否重写：

• 有些数据库系统的优化器足够智能，可以将 DATE(order_date) = '2023-10-01' 重写为一个范围查询，例如：

  WHERE order_date >= '2023-10-01 00:00:00' AND order_date < '2023-10-02 00:00:00'
  

这种重写方式使得数据库系统可以利用 order_date 上的索引。

当然，如果优化器没有重写它，那上述sql就发生索引失效。

仍生效的例子

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- 创建一个索引
CREATE INDEX idx_price ON products (price);
-- 进行计算
SELECT * FROM products WHERE price * 1.10 > 15.00;

虽然我们在 WHERE 子句中使用了乘法运算，但数据库优化器可能会重写查询为 price > 15.00 / 1.10，从而仍然可以使用 idx_price 索引

所以，请谨记：MySQL 中使用函数并不一定会导致索引失效，关键取决于函数的类型和对索引列的影响。

Select *

select * from tableName

使用select * 查询，不加任何条件，也不会走索引，直接返回全部结果，没有用到任何索引，查询效率很低

关于使用select *，其实还有一点需要注意：

在查找数据时，应该尽量不写 *，应该按需编写，因为 ，它代表全部，如果某些列特别长，会消耗大量的内存，且消耗时间也特别长。

还有其它的一些索引失效的情况，这里只列举了一些常用的。

优缺点与建议

优点：它类似于书的目录，降低了数据库IO成本，大大提高了数据查询效率

缺点：索引也是数据，因此会占据一些磁盘空间，同时由于索引跟数据存在一定关联关系，在改动数据时，也需要同时对它进行更新，一定程度上减低了更新表的效率（每次对表进行增删改查，MySQL不仅要保存数据，还有保存或者更新对应的索引文件）。

总结：索引确实能提高查询效率，但并非每次查询都会奏效，因为它独特的数据结构，有时也会存在失效的情况。索引其实就是用空间换取时间的典型例子。

数据库表中不是索引越多越好，而是根据情况为那些常用的搜索字段建立索引，效果才是最佳!

一些扩展资料

菜鸟教程-MySql索引 (opens new window)

数据结构可视化网站 (opens new window)