mysql索引不生效原因？

前言

在大数据时代，数据库管理成为了每个开发者必备的技能之一。MySQL 作为全球最受欢迎的开源关系数据库管理系统，深受开发者们的喜爱。为了提高查询效率，我们常常会在 MySQL 中使用索引，但有时候你会发现，索引并没有发挥出预期的作用，原来，它已经“失效”了。本文通过实例来详解一下 mysql 索引不生效的情况。

首先，我们先创建一个表：有 username、age、address 等字段, 并且建立了 (username)、(age,address)等字段。

CREATE TABLE t_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    username VARCHAR(255),
    address VARCHAR(255),
    INDEX idx_username (username),
    INDEX idx_age_address (age, address)
);

再插入几条数据：

INSERT INTO t_user (age, username, address)
VALUES
(25, 'person1', '北京'),
(30, 'person2', '上海'),
(22, 'person3', '广州'),
(28, 'person4', '深圳'),
(35, 'person5', '成都'),
(40, 'person6', '重庆'),
(29, 'person7', '杭州'),
(26, 'person8', '武汉'),
(31, 'person9', '南京'),
(27, 'person10', '西安');

如何了解 select 语句是否走了索引呢？我们可以使用 explain 语句。

explain select * from t_user where id = 1;

当 key 不为 null 时，代表走了索引。

那么，索引不生效的情况有哪些呢？

1.联合索引不满足最左匹配原则

联合索引遵从最左匹配原则，顾名思义，在联合索引中，最左侧的字段优先匹配。因此，在创建联合索引时，where 子句中使用最频繁的字段放在组合索引的最左侧。

而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。

比如我们刚才建立了 (age, address)联合索引, 索引树是按照 age 排序之后，再按照 address 排序

当我们只用 address 查询的时候，就走不了这条联合索引

explain select * from t_user where address = '北京';

而用 age 查询的时候，就可以走这条索引

explain select * from t_user where age = 25;

2.对索引使用函数

比如下面这条语句查询条件中对 name 字段使用了 LENGTH 函数，执行计划中的 type=ALL，代表了全表扫描

EXPLAIN select * from t_user where length(username) = 7;

从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。就可以使用索引了。

alter table t_user add key idx_name_length ((length(username)));

3. WHERE 子句中的 OR

比如我们使用 or 进行分析，id 有索引。address 没有索引

explain select * from t_user where id = 2 or address = '上海';

可以看到，即使 id 有索引也是走全表扫描。

因为 address 字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，前面 id 的条件再走一次索引反而是浪费了。所以，在使用 or 关键字时，切记两个条件都要添加索引，否则会导致索引失效，走全表扫描。

对索引使用 Like 左或者左右模糊匹配

我们一般使用 like 使用方式有这几种，左模糊，右模糊，左右模糊

• like ‘%abc’;
• like ‘abc%’;
• like ‘%abc%’;

而使用左或者左右模糊是无法走索引的，会走全表扫描。

如果索引关键字的类型是字符串类型，索引的排列顺序是根据比较字符串的首字母排序的，如果首字母相同，就根据比较第二个字母进行排序，以此类推。

比如我们要找 “absne”， 我们就需要判断第一个字符“a”需要往左边走， 再判断第二个字符”b”应该往右走。

所以当使用左模糊或者左右模糊时，就无法根据字符逐个走下去。

左模糊：走全表扫描

explain select * from t_user where username like '%son';

右模糊：走索引

explain select * from t_user where username like 'person%';

左右模糊：走全表扫描

explain select * from t_user where username like '%son%';

4.对索引进行表达式计算

在查询条件中对索引进行表达式计算，也是无法走索引的。

explain select * from t_user where id + 1 = 10;

5.order by 索引失效

可以先了解 order by 怎么工作的。

order by 分为 全字段排序 和 rowid 排序

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。 而走哪个排序，正取决与这个内存的大小。

select city,name,age from t where city='杭州' order by name limit 1000  ;

当内存足够时，会优先走全字段索引，把二级索引对应获取到主键索引，再把主见索引都装入 sort_buffer 中，然后进行排序，排序后到结果集就是最终结果。

当内存不够时，会走 rowid 排序, 只把 orderby 的字段以及主键 id 装入 sort_buffer 中， 按照 name 排序，在根据主键 id 进行回表，最后就是结果集。 也就是说：rowid 排序会多进行一次回表

explain select * from t_user order by username ;

从 extra 里出现了 Using filesort，说明这里是没有走索引的，而且 type 为 ALL，说明进行了一次全表扫描。

username 建立了索引，但是 orderby 依旧走全表扫描。

orderBy 是否使用索引比较复杂。

MySQL 的优化器可能会根据查询的复杂性和表的大小选择执行计划。在某些情况下，全表扫描可能比使用索引更快，尤其是当表很小或索引不够稠密时。

优化器的位置如下。

所以 mysql 经过各种因素分析，即使 username 有索引, 也走全表扫描，这并不一定意味着查询性能会受到严重损害，特别是当表的大小较小或查询性能已经足够快时。查询性能优化是一个综合考虑多个因素的复杂任务。

在我们特别清晰该 sql 的目的的情况下，可以使用强制索引

EXPLAIN SELECT * FROM t_user FORCE INDEX (idx_username) ORDER BY username;

同时，覆盖索引也是一种很好的方式，因为 username 已经是索引，索引树已经是排序好的。

EXPLAIN SELECT username FROM t_user ORDER BY username;

结语

索引是提高 MySQL 查询性能的重要工具，但在某些情况下，索引可能会失效。了解索引失效的原因并找到对应的优化策略，能够帮助我们在实际应用中更好地使用索引，从而提高数据库的性能。同时，我们也需要注意，过多的索引会对数据库的写操作带来影响，因此，要在提高查询效率和保持写操作性能之间找到一个平衡。