ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 索引失效场景总结 [打印本页]
作者: 渣渣兔    时间: 2024-5-15 23:10
标题: 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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4