MySQL索引失效的原因及应对策略

冷不防 2024-03-17 13:11 36阅读 0赞

#### 文章目录 ####

*   *  引言
     *  1、索引是什么？
     *  2、创建示例表与索引
     *  3、索引失效的常见原因及优化策略
     *   *  3.1、使用了不等于（!= 或 <>）的查询条件
         *  3.2、对索引列进行了计算或函数操作
         *  3.3、使用了 OR 条件
         *  3.4、索引列上有NULL值
         *  3.5、类型转换导致索引失效
         *  3.6、不符合最佳左前缀原则导致索引失效
         *  3.7、索引列中的范围查询导致索引失效
     *  4、总结

### 引言 ###

在大数据时代，数据库管理成为了每个开发者必备的技能之一。MySQL作为全球最受欢迎的开源关系数据库管理系统，深受开发者们的喜爱。为了提高查询效率，我们常常会在MySQL中使用索引，但有时候你会发现，索引并没有发挥出预期的作用，原来，它已经“失效”了。本文将带你探索MySQL中索引失效的原因以及如何避免，让我们一同跨入数据库的奇妙世界。

### 1、索引是什么？ ###

在数据库中，索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。具体来说，MySQL中的索引能够帮助我们快速地查询、更新数据库表中的数据。就如同一本书的目录，使你能够不必翻遍全书就能迅速找到所需内容。

### 2、创建示例表与索引 ###

为了更好的理解索引失效的原因，我们首先创建一些示例数据。我们创建两个表：`dept`和`emp`，并在这两个表上设置索引。

CREATE TABLE `dept` (
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`deptName` VARCHAR(30) DEFAULT NULL,
    	`address` VARCHAR(40) DEFAULT NULL,
    	ceo INT NULL ,
    	PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1;
    
    CREATE TABLE `emp` (
    	`id` INT(11) NOT NULL AUTO_INCREMENT,
    	`empno` INT NOT NULL ,
    	`name` VARCHAR(20) DEFAULT NULL,
    	`age` INT(3) DEFAULT NULL,
    	`deptId` INT(11) DEFAULT NULL,
    	PRIMARY KEY (`id`),
    	INDEX idx_emp_name (`name`),
    	INDEX idx_emp_age_deptid (`age`,`deptId`)
    ) ENGINE=INNODB AUTO_INCREMENT=1;

在这个示例中，我们在`emp`表的`name`字段和`age`，`deptId`字段上创建了两个索引。

### 3、索引失效的常见原因及优化策略 ###

虽然索引可以优化数据库查询，但在某些情况下，索引可能会失效。以下是一些常见的原因及对应的优化策略：

#### 3.1、使用了不等于（!= 或 <>）的查询条件 ####

在进行查询时，如果使用了不等于的条件，MySQL可能会认为全表扫描更为高效，因此不会使用索引。

SELECT * FROM emp WHERE age != 18;

在这个例子中，即使`age`字段有索引，但MySQL并不会使用。

**优化策略**：尽量避免在索引列上使用不等于操作。

#### 3.2、对索引列进行了计算或函数操作 ####

当你在索引列上进行计算或者使用函数，MySQL无法有效地使用索引进行查询，索引在这种情况下也会失效。

SELECT * FROM emp WHERE LEFT(name,3) = 'abc';

在这个例子中，我们在`name`上使用了LEFT函数，这就使得索引失效。