阅读 15 SEO

4)MySQL索引优化

索引优化

1. 尽量全值匹配

当建立索引后,能再where条件中使用索引列,就尽量使用。
例如 alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
尽量加上三个列在where里,EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev'

2. 最佳左前缀法则

如果是复合索引,就要遵守最左前缀法则,意思是:查询从最左前列开始,并且不跳过索引中的列。
同样索引列是name,age,pos。
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; (会用索引NAME )
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND AGE = 25;(会用索引NAME 和AGE)
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND POS= 'dev'(只会用到NAME,因为跳过了AGE)
EXPLAIN SELECT * FROM staffs WHERE AGE = 25 AND POS='dev';(不会用索引,因为最左前列NAME没有使用)

3. 不在索引列上做任何操作

不在索引列上(计算,函数,自动或者手动的进行类型转换),会导致索引失效。
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';(不会用到索引)

4. 范围条件放最后(是指索引定义顺序的最后)

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age =22 and pos='manager'
中间有范围查询会导致后面的索引列全部失效(按照name,age,pos,索引创建的顺序,age后面的POS会失效)
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age >22 and pos='manager' (索引只会用到NAME 和AGE列)
对于in条件查询,如果索引没有生效,使用in不会有影响;如果索引有效,使用in则会进行全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 22 and pos in ('a','b' ) (name,age,pos索引都存在,没有跳过的,pos使用in则会导致全表扫描)
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and pos in ('a','b' ) (索引age跳过了,所以pos不会生效,加in不会对索引有影响)

5. 尽量使用覆盖索引

覆盖索引(只访问索引的查询(索引列和查询列一致)),而尽量避免 select *

6. 不等于要慎用

在使用不等于(!= 或者<>),会导致索引失效
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';
如果定要需要使用不等于,请用覆盖索引
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME != 'July';

7. NULL和Not NULL要慎用

|-- 在字段为NOT NULL的情况下,如果使用 is null 或者 is not null,会导致索引失效。解决方案(覆盖索引)
EXPLAIN select * from staffs where name is not null (索引失效)
EXPLAIN select name,age,pos from staffs where name is not null (可以使用索引)
|-- 在字段为可以为NULL的情况下,使用IS NULL,索引正常;使用 IS NOT NULL,则索引失效。(解决方案同上,覆盖索引)

8. LIKE查询要当心

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。
EXPLAIN select * from staffs where name like '%july%' (索引失效,一定要用的话,用覆盖索引)
EXPLAIN select * from staffs where name like 'july%' (可以使用索引,类似遵循最左前缀原则)

9. 字符类型加引号

EXPLAIN select * from staffs where name = 917 (索引失效)
EXPLAIN select * from staffs where name = ‘917’ (可以使用索引)

10. OR改UNION效率高

EXPLAIN select * from staffs where name='July' or name = 'z3' (索引失效,解决方案UNION或者覆盖索引)
|-- 解决方式:UNION
EXPLAIN
select * from staffs where name='July'
UNION
select * from staffs where name = 'z3'
|-- 解决方式:覆盖索引
EXPLAIN
select name,age from staffs where name='July' or name = 'z3'

除了索引优化之外,还有一些查询优化的技巧:

1. 延迟关联

select c1 from table where c2='M' order by c3 limit 100000, 10; 类似这种分页查询
可以通过延迟关联(deferred join)技术,通过覆盖索引(c2+c3)来得到所需要的主键,然后再根据这些主键关联原表获得需要的行。

select c1 
from table,  (select id from table where c2='M' order by c3 limit 100000, 10) as table2
where table.id = table2.id 
2. LIMIT 1

当查询结果只可能为1条数据的时候,加上LIMIT 1可以增加性能,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

作者:涣涣虚心0215

原文链接:https://www.jianshu.com/p/d24d3681d370

文章分类
后端
文章标签
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 gxwowoo@163.com 举报,一经查实,本站将立刻删除。
相关推荐