单表索引失效场景

单表索引失效场景

---

前言

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

我们创建索引后，用不用索引，最终是优化器说了算。优化器会基于开销选择索引，怎么开销小就怎么来。不是基于规则，也不是基于语义。

另外SQL语句是否使用索引，和数据库的版本、数据量、数据选择度（查询中选择的列数）运行环境都有关系。

---

提示：以下是本篇文章正文内容，下面案例可供参考

失效场景

在emp测试表中为name字段创建索引:

CREATE INDEX idx_name ON emp(`name`);

1.索引列增加了函数和计算：
第二条sql语句增加了left（emp.name,3）函数,
left（emp.name,3）：此函数表示从emp表中取得name字段前三个字符串进行截取。
当索引列增加了函数之后，虽然条件是等值查询，但并不会走索引。

为什么增加函数之后，会导致不走索引？
因为不确定所查值不确定所以获取所有的name进行匹配。在回表查询,相当于查询所有，那不如进行全局扫描。因此索引失效。

EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';

EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

2.模糊查询：LIKE以%开头索引失效
名字中包含a和b的结果是全局扫描：案例中查询49w条数据符合的只有百分之11.11。
因为模糊查询，B+tree是按照关键字大小进行排序，字符串索引列的值必须以ab或者某个字母开头。
–拓展：根据Alibaba《java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎(如：ES)来解决。

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

3.不等于(!= 或者<>)索引失效
sql语句1：name上有索引必然会找到

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;

sql语句2：有可能会用到idx_name索引，但实际上优化器会发现走索引效率不如全局扫描

EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

4.IS NOT NULL 和 IS NULL
优化器基于开销进行选择：与其用name回表挨个扫描哪个不为空，其效率不如直接全局扫描。

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

–注意：**当数据库中的数据的索引列的NULL值达到比较高的比例的时候，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）
5.类型转换导致索引失效
Sql优化器在执行之前会先在name这索引列上添加函数（隐式函数），前面提到添加函数会导致索引失效。

EXPLAIN SELECT * FROM emp WHERE name='123'; 

EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

6.全值匹配我最爱
问题：给以下查询语句创建哪种索引效率最高

-- 查询分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 执行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看执行时间
SHOW PROFILES;

-- 创建索引：分别创建以下三种索引的一种，并分别进行以上查询分析
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_age_deptid ON emp(age,deptid);
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

结论：**可以发现最高效的查询应用了联合索引 idx_age_deptid_name

7.最佳左前缀法则
带头大哥不能死，中间小弟不能断
准备：

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");
-- 创建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

测试：

• 如果索引了多列，要遵守最左前缀法则。即查询从索引的最左前列开始并且不跳过索引中的列。
• 过滤条件要使用索引，必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
  **问题：**以下这些SQL语句能否命中 idx_age_deptid_name 索引，可以匹配多少个索引字段

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
-- EXPLAIN结果：
-- key_len：5 只使用了age索引
-- 索引查找的顺序为 age、deptid、name，查询条件中不包含deptid，无法使用deptid和name索引。
-- 诠释了中间小弟不能断。

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果：
-- type： ALL， 执行了全表扫描
-- key_len： NULL， 索引失效
-- 索引查找的顺序为 age、deptid、name，查询条件中不包含age，无法使用整个索引
-- 诠释了带头大哥不能死。

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN结果：
-- 索引查找的顺序为 age、deptid、name，匹配所有索引字段

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
-- EXPLAIN结果：
-- 索引查找的顺序为 age、deptid、name，匹配所有索引字段
-- 会对顺序自动调配

8.索引中范围条件右边的列失效
准备：

-- 首先删除之前创建的索引
CALL proc_drop_index("atguigudb","emp");

问题：为以下查询语句创建哪种索引效率最高

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc'; 

测试一：

-- 创建索引并执行以上SQL语句的EXPLAIN
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
-- key_len：10， 只是用了 age 和 deptid索引，name失效

注意：**当我们修改deptId的范围条件的时候，例如deptId>100，那么整个索引失效，MySQL的优化器基于成本计算后认为没必要使用索引了，所以就进行了全表扫描。（注意：因为表中的数据是随机生成的，因此实际测试中根据具体数据的不同测试的结果也会不一样，最终是否使用索引由优化器决定）

测试二：
将范围查询的字段调整到最后执行，不然会将右侧的索引全部失效。

-- 创建索引并执行以上SQL语句的EXPLAIN（将deptid索引的放在最后）
CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
-- 使用了完整的索引

**补充：**以上两个索引都存在的时候，MySQL优化器会自动选择最好的方案

总结

【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

建议

• 对于单例索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引顺序汇中，位置越靠前越好
• 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

总之，大家在书写SQL语句时，尽量避免造成索引失效的情况。