查询优化_单表使用索引及常见索引失效

1.索引失效场景

1.1.全值匹配我最爱

1.系统中经常出现的sql语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';
1

2.索引应该如何建立 ？

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);
1
2
3

3.建立索引前

4.建立索引后

1.2.最佳左前缀法则

1.如果系统经常出现的sql如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'; 
1
2
3

那原来的idx_age_deptid_name 还能否正常使用？不能
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.age=30 AND emp.name = 'abcd'; #
1
2

2.如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ; #
1

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'; 
1

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
如果where 条件后面索引都存在的情况下，mysql 的优化器会自动使用索引

1.3.计算、函数导致索引失效

1.这两条sql哪种写法更好

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

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc'; 
1
2
3

2.创建索引

CREATE INDEX idx_name ON emp(NAME);
1

3.第一种：索引优化生效


4.第二种：索引失效

1.4.范围条件右边的列索引失效

1.如果系统经常出现的sql如下：

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

2.那么索引 idx_age_deptid_name这个索引还能正常使用么？
a.不能，范围右边的列不能使用。
b.如果这种sql 出现较多，应该建立：

create index idx_age_name_deptid on emp(age,name,deptid);
1

将范围查询条件放置语句最后

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

应用开发中范围查询，例如： 金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。

3.效果

1.5.不等于(!= 或者<>)索引失效

CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <>  'abc' ;
1
2
3

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

1.6.is not null无法使用索引，is null可使用索引

下列哪个sql语句可以用到索引
CREATE INDEX idx_age ON emp(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;  

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;
1
2
3
4
5
6

1.7.like以通配符%开头索引失效

1.下列哪个sql语句可以用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name LIKE 'ab%';  

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name LIKE '%ab%'; 
1
2
3
4

1.8.类型转换导致索引失效

1.下列哪个sql语句可以用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name=123; 
 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE name='123';
1
2
3
4

name=123发生类型转换，索引失效。
设计实体类属性时，一定要与数据库字段类型相对应，否则会出现类型转换的情况。

小总结案例

假设index(a,b,c)

一般性建议

1. 对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）
2. 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 选择组合索引时，尽量包含where中更多字段的索引
4. 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面
5. 尽量避免造成索引失效的情况