MySQL性能优化之索引调优实战

上一篇 <<<MySQL性能优化之常用SQL语句优化
下一篇 >>>

---

索引失效场景或使用注意事项

a、索引无法存储null值，所以建议都给默认值
b、如果条件中有or，即使使用了索引条件也不起作用，所以尽量少用or
如果想使用or，又让索引生效，只能将or的每个列上加上索引
c、对于多列索引，不是使用其中的第一部分，则不会使用索引。
d、like查询以%开头(like '%XX'或者like '%XX%')
e、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
f、如果mysql估计使用全表扫描要比使用索引快,则不使用索引
g、索引的字段类型与传入参数不匹配，则索引失效

索引优化样例

1.使用索引时，关联表的条件字段中，字段长度和编码必须一致

a.fk_user_id = b.user_id ，fk_user_id 的编码是utf8 而 user_id 的编码方式是utf8mb4的，所以导致索引失效

2.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

 SELECT name,age FROM employees WHERE name= 'jarye' AND age = 23 AND position ='ceo';----直接走索引
 SELECT * FROM employees WHERE name= 'jarye' AND age = 23 AND position ='ceo';-----索引走了还会走二次查询

3.列类型是字符串，则必须使用''引号，否则不使用索引

SELECT * FROM employees WHERE name = 123;--索引会失效
SELECT * FROM employees WHERE name = '123';--索引生效

4.判断是否为空用is null，使用=null则不启用索引

select id from t where num is null;
注意： NULL 与任何值的直接比较都为 NULL。
1 ） NULL<>NULL 的返回结果是 NULL ，而不是 false 。
2 ） NULL=NULL 的返回结果是 NULL ，而不是 true 。
3 ） NULL<>1 的返回结果是 NULL ，而不是 true 。

5.应尽量避免在 where 子句中对”="左边进行函数、算数运算或表达式运算，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where substring(name,1,3) = ’abc’ -–name 以 abc 开头的 id 
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ –生成的 id
应改为:
select id from t where name like ‘abc%’ 
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’ 

6.在where子句中尽量避免使用!=或<>操作符，引擎会放弃使用索引而进行全表扫描。

 SELECT * FROM employees WHERE name != 'jarye'

7.若中间索引列用到了范围（>、<、like等），则后面的所以全失效

 SELECT * FROM employees WHERE name= 'jarye' AND age = 22 AND position ='ceo';----索引生效
 SELECT * FROM employees WHERE name= 'jarye' AND age > 22 AND position ='ceo';----索引position无效

8.like前面有%会失效，如果字段长的话，可以考虑使用全文检索

Select * from dw_user where username like ‘%123%’——索引失效 
Select * from dw_user where username like ‘%123’——索引失效 
Select * from dw_user where username like ‘123%'——索引有效 

9.在 where 子句中使用 or 来连接条件，必须全部索引存在才有效

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如： 
select id from t where num=10 or Name = ‘admin’
可以这样查询：
select id from t where num = 10 
union all 
select id from t where Name = ‘admin’ 

10.有 order by 的场景，请注意利用索引的有序性【阿里巴巴JAVA开发手册】，参考order by中的单路和双路排序算法原理

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file _ sort 的情况，影响查询性能。 
正例： where a =?  and b =?  order by c; 索引： a _ b _ c
反例：索引中有范围查找，那么索引有序性无法利用，如： WHERE a >10  ORDER BY b; 索引a _ b 无法排序。

11.联合索引，第一条件必须使用，且尽可能按索引顺序执行

alter table dept add index my_ind (dname,loc);
select * from dept where dname=‘aaa’ and loc=‘aaa’———二个条件都使用了，索引生效 
select * from dept where dname=‘aaa’——第一条件查询，使用索引 
select * from dept where loc=‘aaa’——没有第一条件，不使用索引
建组合索引的时候，区分度最高的在最左边。
正例：如果 where a =?  and b =? ， a 列的几乎接近于唯一值，那么只需要单建 idx _ a 索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where a >? and b =? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

联合索引为什么需要遵循左前缀原则？
因为索引底层采用B+树叶子节点顺序排列，必须通过左前缀索引才能定位到具体的节点范围。

12. 在where子句中使用参数，也会导致全表扫描,可以使用强制索引

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num = @num
可以改为强制索引
select id from t with(index(索引名)) where num = @num