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 语句就可以利用这个联合索引去进行匹配。值得注意的是，当遇到范围查询（>、 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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]