这样写sql避免索引失效

淡淡的烟草味﹌ 2023-06-18 03:53 55阅读 0赞

合理地对数据表加索引可以大大加快数据的访问效率，但索引也不是对任何查询都有效，如果sql的结构不当，也会发生索引失效。所以为避免采坑，写下此文，作为记录。下面的例子是在mysql中创建的一张表，建表语句如下：

CREATE TABLE `user_info` (
      `id` bigint(32) NOT NULL,
      `name` varchar(32) DEFAULT NULL,
      `age` int(3) DEFAULT NULL,
      `address` varchar(32) DEFAULT NULL,
      `nick_name` varchar(32) DEFAULT NULL,
      `role_id` bigint(32) DEFAULT NULL,
      `create_time` date DEFAULT NULL,
      `update_time` date DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 一、尽量选择复合索引 ###

在对表创建索引时，如果可以使用复合索引，尽量使用复合索引而不是单列索引，理由如下。现创建的一个复合索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);

这里创建了一个名为idx\_name\_age\_status的复合索引，索引涉及到三个字段。在mysql中，这一个索引实际上相当于三个索引，分别是name、 name + age、 name + age + stauts，那么在使用name或name + age或name + age + stauts进行查询时，都会使索引生效。而如果分别给三个字段三个**单列索引**，在查询这张表时条件中有这三个字段，**数据库会选择一个最优的单列索引**，而不会使用全部三个索引。

### 二、最左前缀法 ###

使用到复合索引时，注意最左前缀法，也就是查询的条件需要**从索引的最左列开始，并且不跳过中间列**，才能使索引发挥最好的效果。现在创建这样的索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);

使用name或name + age或name + age + status进行查询能使索引生效；使用单个查询条件age或status或者age + status 进行查询不会使索引生效；使用name + stauts进行查询只有name字段的索引生效。

### 三、使用**复合索引时范围查询右边的列索引不会生效** ###

三个字段在同一个复合索引中，如果查询条件中用到了范围查询，范围查询后面的索引不再生效**。**比如创建如下索引：

CREATE INDEX idx_name_age_status ON user(name, age, status);

使用这三个字段作为查询条件，处于中间的age如果用到了范围查询，则status上的索引不会生效。

-- name + age + nick_name索引生效
    SELECT * FROM user_info WHERE name = '张三' AND age = 22 AND nick_name = '三儿';
    -- age用到了范围查询，仅name + age索引生效
    SELECT * FROM user_info WHERE name = '张三' AND age > 22 AND nick_name = '三儿';

## ##

### 四、**在索引列上进行运算操作后，索引将失效** ###

在索引列上进行运算操作后，索引将失效。比如有这样一个索引：

CREATE INDEX idx_name ON user_info(name);

直接以name作为条件查询索引是生效的，如果在name上使用函数会导致索引失效，比如这样：

SELECT * FROM user_info WHERE substring(name, 1, 2) = '夏侯';

### 五、模糊匹配不生效的情况 ###

查询条件中的模糊条件，**如果是以%开头，不走索引**，比如

SELECT * FROM user_info WHERE name like '%三儿%';    -- 不走索引

或者

SELECT * FROM user_info WHERE name like '%三儿';    -- 不走索引

以上两种写法都不会走索引，**下面这种写法索引是有效的**。

SELECT * FROM user_info WHERE name like '三儿%';    -- 走索引

### 六、**字符串没加引号，索引失效** ###

查询时字符串如果没加引号，索引失效。

在mysql中，假设有一个字段名是name，类型为VARCHAR，使用name作为条件查询。此时忘记给name的值加上引号，就像这样：

SELECT * FROM user_info WHERE name = 12;    -- name为VARCHAR类型

此时name条件查询的值是12，mysql是可以执行的，如果这里写一个name = 张三，mysql不能执行。虽然这条语句可以执行，并且如果数据表中有name值为12的数据也是可以查出来的。但是，**它不会走索引**。

### 七、作为条件的索引字段位于or两侧，导致该索引失效 ###

name字段上有索引，所以这样查肯定是走索引的。

SELECT * FROM user_info WHERE name = '张三';

在这张表中另外有一列nick\_name，不管这一字段有没有加索引，**下面这一个语句都不会走索引**。

SELECT * FROM user_info WHERE name = '张三' OR nick_name = '顽皮';

### 八、尽量不使用SELECT \*，最好只查询索引列 ###

尽量使用覆盖索引，也就是尽量只查询索引列，比如复合索引如下：

CREATE INDEX idx_name_age_status ON user(name, age, status);

使用SELECT \* FROM user\_info WHERE name = '张三' AND age = 12 AND status = 1进行查询，索引自然会生效，但是效率肯定不如SELECT name, age, status FROM user\_info WHERE name = '张三' AND age = 12 AND status = 1，这在数据量大的时候可以看出来。

另外，**尽量不要写SELECT \* FROM user\_info**这样的语句，SELECT后面只跟需要用到的列是好的选择。