MySQL 索引失效场景总结

查询条件有 or

假设在 customer_name 字段设置了普通索引,执行以下 sql:

# type: ref, possible_keys: idx_customer_name, key: idx_customer_name
# idx_customer_name 索引生效
explain select id, customer_name, company_name from t_user_info where customer_name = 'test_name'

# type: ref, possible_keys: idx_customer_name, key: idx_customer_name
# idx_customer_name 索引生效
explain select id, customer_name, company_name from t_user_info where customer_name = 'test_name' and company_name = 'test_name'

# type: all, possible_keys: idx_customer_name, key: null
# idx_customer_name 索引不生效,使用全表扫描
explain select id, customer_name, company_name from t_user_info where customer_name = 'test_name' or company_name = 'test_company'

like 查询以 % 开头

假设在 customer_name 字段设置了普通索引,执行以下 sql:

# type: all, possible_keys: null, key: null
# idx_customer_name 索引不生效
explain select id, customer_name, company_name from t_user_info where customer_name like '%name'

# type: range, possible_keys: idx_customer_name, key: idx_customer_name
# idx_customer_name 索引生效
explain select id, customer_name, company_name from t_user_info where customer_name like 'test%'

如果希望以 % 开头仍使用索引,则需要使用覆盖索引,即只查询带索引字段的列

# type: index, possible_keys: null, key: idx_customer_name
# idx_customer_name 索引生效
# id 是主键,idx_customer_name 构成的 b+tree 除了有 customer_name,也包含用于指向对应行的 id
explain select id, customer_name from t_user_info where customer_name like '%name'

索引列参与运算

假设 id 字段为主键,执行以下 sql:

# type: const, possible: primary, key: primary
# idx_id 索引生效
explain select id, customer_name, company_name from t_user_info where id = 2

# type: all, possible: null, key: null
# idx_id 索引不生效
explain select id, customer_name, company_name from t_user_info where id + 1 = 2

索引列使用函数

假设在 customer_name 字段设置了普通索引,执行以下 sql:

# type: ref, possible_keys: idx_customer_name, key: idx_customer_name
# idx_customer_name 索生效
explain select id, customer_name, company_name from t_user_info where customer_name = '查理一世'

# type: all, possible_keys: null, key: null
# idx_customer_name 索引不生效
explain select id, customer_name, company_name from t_user_info where substr(customer_name, 1, 3) = '查理一'

类型转换

假设在 customer_name 字段设置了普通索引,执行以下 sql:

# type: all, possible_keys: idx_customer_name, key: null
# idx_customer_name 索引不生效
explain select id, customer_name, company_name from t_user_info where customer_name = 10

这是因为 mysql 会自动对字段执行类型转换函数,如上 sql 相当于

select id, customer_name, company_name from t_user_info where cast(customer_name as signed) = 10

两列做比较

如果两个列数据都有索引,但在查询条件中对两列数据进行了对比操作,则会导致索引失效

假设在 customer_name、company_name 字段设置了普通索引,执行以下 sql,仅作示例:

# type: range, possible_keys: idx_customer_name, key: idx_customer_name
# idx_customer_name 索引生效
explain select id, customer_name, company_name from t_user_info where customer_name > '查理一世'

# type: all, possible_keys: null, key: null
# idx_customer_name 索引生效
explain select id, customer_name, company_name from t_user_info where customer_name > company_name

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

联合索引遵从最左匹配原则,所谓最左匹配原则,就是如果 SQL 语句用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配。值得注意的是,当遇到范围查询(>、<、between、like)时就会停止匹配

假设对 a、b、c 字段建立联合索引 idx_a_b_c,执行 sql 如下:

# type: ref, possible_keys: idx_a_b_c, key: idx_a_b_c, ref: const
# idx_a_b_c 索引生效,a 字段能用到索引
explain select * from test_table where a = 1

# type: ref, possible_keys: idx_a_b_c, key: idx_a_b_c, ref: const, const
# idx_a_b_c 索引生效,a、b 字段能用到索引
explain select * from test_table where a = 1 and b = 2

# type: ref, possible_keys: idx_a_b_c, key: idx_a_b_c, ref: const, const, const
# idx_a_b_c 索引生效,a、b、c 字段能用到索引
explain select * from test_table where a = 1 and b = 2 and c = 3

# type: ref, possible_keys: idx_a_b_c, key: idx_a_b_c, ref: const, const, const
# idx_a_b_c 索引生效,a、b、c 字段能用到索引,优化器会调整 a、b、c 的顺序,从而用上索引
explain select * from test_table where b = 2 and c = 3 and a = 1

# type: range, possible_keys: idx_a_b_c, key: idx_a_b_c, ref: null, key_len: 75
# a 字段类型为 varchar(18),字符集为 utf8mb4,1 个字符占 4 个字节,占用 4*18=72 字节
# varchar 为变长数据类型,额外占用 2 个字节
# 字段默认为 null,额外占用 1 个字节
# 因此 key_len = 72 + 2 + 1 = 75,可判断 idx_a_b_c 索引生效,但只有 a 字段用到索引
explain select * from test_table where a > 1 and b = 2 and c = 3

我们知道索引是用 B+Tree 实现的,如果只对 a 字段建立普通索引,那么 B+Tree 根据 a 字段排序。如果对 a、b、c 建立联合索引,那么首先根据 a 字段排序,如果 a 字段值相同,再根据 b 字段排序,如果 b 字段值也相同,再根据 c 字段排序。因此,使用联合索引必须按照从左到右,也就是字段排序的顺序,只有先用了 a,才能接着使用 b,使用了 b 才能接着使用 c

热门相关:龙皇武神   重生不嫁豪门   隐婚365天:江少,高调宠!   驭房有术   全能王妃:偷个王爷生宝宝