MySQL 索引失效场景总结

查询条件有 or

假设在 customer_name 字段设置了普通索引，实行以下 sql：

1. # type: ref, possible_keys: idx_customer_name, key: idx_customer_name
2. # idx_customer_name 索引生效
3. explain select id, customer_name, company_name from t_user_info where customer_name = 'test_name'
5. # type: ref, possible_keys: idx_customer_name, key: idx_customer_name
6. # idx_customer_name 索引生效
7. explain select id, customer_name, company_name from t_user_info where customer_name = 'test_name' and company_name = 'test_name'
9. # type: all, possible_keys: idx_customer_name, key: null
10. # idx_customer_name 索引不生效，使用全表扫描
11. 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：

1. # type: all, possible_keys: null, key: null
2. # idx_customer_name 索引不生效
3. explain select id, customer_name, company_name from t_user_info where customer_name like '%name'
5. # type: range, possible_keys: idx_customer_name, key: idx_customer_name
6. # idx_customer_name 索引生效
7. explain select id, customer_name, company_name from t_user_info where customer_name like 'test%'

复制代码

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

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

复制代码

索引列参与运算

假设 id 字段为主键，实行以下 sql：

1. # type: const, possible: primary, key: primary
2. # idx_id 索引生效
3. explain select id, customer_name, company_name from t_user_info where id = 2
5. # type: all, possible: null, key: null
6. # idx_id 索引不生效
7. explain select id, customer_name, company_name from t_user_info where id + 1 = 2

复制代码

索引列利用函数

假设在 customer_name 字段设置了普通索引，实行以下 sql：

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

复制代码

类型转换

假设在 customer_name 字段设置了普通索引，实行以下 sql：

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

复制代码

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

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

复制代码

两列做比力

假如两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效
假设在 customer_name、company_name 字段设置了普通索引，实行以下 sql，仅作示例：

1. # type: range, possible_keys: idx_customer_name, key: idx_customer_name
2. # idx_customer_name 索引生效
3. explain select id, customer_name, company_name from t_user_info where customer_name > '查理一世'
5. # type: all, possible_keys: null, key: null
6. # idx_customer_name 索引生效
7. explain select id, customer_name, company_name from t_user_info where customer_name > company_name

复制代码

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


联合索引遵从最左匹配原则，所谓最左匹配原则，就是假如 SQL 语句用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。值得注意的是，当遇到范围查询（>、 1 and b = 2 and c = 3[/code]我们知道索引是用 B+Tree 实现的，假如只对 a 字段建立普通索引，那么 B+Tree 根据 a 字段排序。假如对 a、b、c 建立联合索引，那么首先根据 a 字段排序，假如 a 字段值雷同，再根据 b 字段排序，假如 b 字段值也雷同，再根据 c 字段排序。因此，利用联合索引必须按照从左到右，也就是字段排序的序次，只有先用了 a，才能接着利用 b，利用了 b 才能接着利用 c

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。