1.避免查询不必要的列
查询语句避免出现select * 的查询语句,指定查询具体字段。
2.优化分页查询
(1)延迟关联(需有主键)
select * from table_a where type = 1 and number = 10 order by id limit 90000,10;
优化成:
select a.* from table_a a,(select id from table_a where type = 1 and number = 10 order by id limit 90000,10) b where a.id=b.id;
执行时间比全表扫描更快。通过explain解析,可以发现子查询是覆盖索引,type类型是 eq_ref。
3.优化查询及索引
(1)覆盖索引
select name from table where id = 3;
建立联合索引,可以查询的时候直接从索引获取。
(2)避免使用NULL和NOT NULL
使用NULL或者NOT NULL 可能会扫全表的。效率比较低。
(3)避免使用!或者<>
SQL中,不等于操作符会导致查询引擎放弃查询索引,引起全表扫描,即使比较的字段上有索引
解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描
(4)避免列上函数
select * from tablewhere id + 1 = 50;
要避免在列字段上进行算术运算或其他表达式运算,否则可能会导致存储引擎无法正确使用索引,从而影响了查询的效率。
(5)like查询
select id,name from table where name like '%test%';
业务允许情况下,遵循最左前缀原则,优化成:
select id,name from table where name like 'test%';
(6)LIMIT 1
当查询只有一条数据时候,使用limit 1,可以防止引擎往后扫表。
(7)union all和union
union会把查询的列做去重处理,导致效率比union all低。
4.巧用关联
(1)避免列里嵌套子查询
尽量使用 Join 语句来替代子查询,因为子查询是嵌套查询,而嵌套查询会新创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,同时对于返回结果集比较大的子查询,其对查询性能的影响更大。
(2)小表驱动大表
关联查询的时候要拿小表去驱动大表,因为关联的时候,MySQL内部会遍历驱动表,再去连接被驱动表。
比如left join,左表就是驱动表,A表小于B表,建立连接的次数就少,查询速度就被加快了。
select name from A left join B ;
(3)适当增加冗余字段
增加冗余字段可以减少大量的连表查询,因为多张表的连表查询性能很低,所有可以适当的增加冗余字段,以减少多张表的关联查询,这是以空间换时间的优化策略
(4)避免使用JOIN关联太多的表
《阿里巴巴Java开发手册》规定不要join超过三张表,第一join太多降低查询的速度,第二join的buffer会占用更多的内存。