MYSQL索引不生效的原因

最新推荐文章于 2024-04-26 13:29:49 发布

兄弟你的假发

最新推荐文章于 2024-04-26 13:29:49 发布

阅读量5k

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 database

原文链接：https://learnku.com/articles/68200

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

有时候我们明明加了索引了，但是索引却不生效。在哪些情况下，索引会不生效呢？

在这里插入图片描述

1. 隐式的类型转换，索引失效

例子：
表word_vocabulary字段 book_id 类型为 int(11)，book_id设置为索引

//不走索引
SELECT * FROM `word_vocabulary` WHERE `book_id` = '1'
//走索引
SELECT * FROM `word_vocabulary` WHERE `book_id` = 1

查询的时候是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

2. 查询条件包含 or，可能导致索引失效

CREATE TABLE user ( 
	id int(11) NOT NULL AUTO_INCREMENT, 
	userId varchar(32) NOT NULL, 
	age varchar(16) NOT NULL, name varchar(255) NOT NULL, 
	PRIMARY KEY (id),
	KEY idx_userid (userId) USING BTREE 
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中 userId 加了索引，但是 age 没有加索引的。我们使用了 or，以下 SQL 是不走索引的，如下：

SELECT * FROM `user` WHERE `userId` = '123' or age='18'

对于 or + 没有索引的 age 这种情况，假设它走了 userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描 + 索引扫描 + 合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql 优化器出于效率与成本考虑，遇到 or 条件，让索引失效，看起来也合情合理。

注意：如果 or 条件的列都加了索引，索引可能会走也可能不走，平时大家使用的时候，还是要注意一下这个 or，学会用 explain 分析。遇到不走索引的时候，考虑拆开两条 SQL。

3. like 通配符可能导致索引失效

并不是用了 like 通配符，索引一定会失效，而是 like 查询是以 % 开头，才会导致索引失效。

like 查询以 % 开头，索引失效:

//不走索引
select * from user where userId like '%123';
//走索引
select * from user where userId like '123%';

4. 查询条件不满足联合索引的最左匹配原则

MySQl 建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。
假设有以下表结构：

CREATE TABLE user ( 
	id int(11) NOT NULL AUTO_INCREMENT,
	user_id varchar(32) NOT NULL, 
	age varchar(16) NOT NULL, 
	name varchar(255) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_userid_name (user_id,name) USING BTREE 
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

有一个联合索引 idx_userid_name，我们执行这个 SQL，查询条件是 name，索引是无效：

//不走索引
select * from user where name = '小池';

//在联合索引中，查询条件满足最左匹配原则时，索引才正常生效
select * from user where user_id = '123';

5. 在索引列上使用 mysql 的内置函数

表结构：

CREATE TABLE user(
	id int(11) NOT NULL AUTO_INCREMENT,
	userId varchar(32) NOT NULL,
	login_time datetime NOT NULL,
	PRIMARY KEY (id), 
	KEY idx_userId(userId) USING BTREE,
	KEY idx_login_time(login_Time) USING BTREE 
) 
  ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然 login_time 加了索引，但是因为使用了 mysql 的内置函数 Date_ADD()，索引直接失效，如图：
在这里插入图片描述

一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：
在这里插入图片描述

6. 对索引进行列运算（如，+、-、*、/）, 索引不生效

假设 age 是一个索引，执行以下查询语句索引将无法运行：

select * from user where age-1 = 39;

不可以对索引列进行运算，可以在代码处理好，再传参进去。

7. 索引字段上使用（！= 或者 < >, not in），索引可能失效

age 加了索引，但是使用了！= 或者 < >，not in 这些时，索引如同虚设。如下查询：

select * from user where age != 18;

select * from user where age <> 18;

其实这个也是跟 mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者 < >，not in 的时候，要留点心眼。

8. 索引字段上使用 is null， is not null，索引可能失效（查询结果行数）

很多时候，是因为数据量问题，导致了 MySQL 优化器放弃走索引。同时，平时我们用 explain 分析 SQL 的时候，如果 type=range, 要注意一下哈，因为这个可能因为数据量问题，导致索引无效。

9. 左右连接，关联的字段编码格式不一样

新建两个表，一个 user，一个 user_job


CREATE TABLE user(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
age int(11) NOT NULL, 
PRIMARY KEY (id), KEYidx_name(name`) 
USING BTREE
) 
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE user_job (
	id int(11) NOT NULL,
	userId int(11) NOT NULL,
	job varchar(255) DEFAULT NULL,
	name varchar(255) DEFAULT NULL,
	PRIMARY KEY (id),
	KEY idx_name (name) USING BTREE
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;