慢 SQL 的优化,主要从两个方面考虑,SQL 语句本身的优化,以及数据库设计的优化。
避免不必要的列
SQL 查询的时候,应该只查询需要的列,而不是包含额外的列,像select *
这种写法应该尽量避免。
分页优化
在数据量比较大,分页比较深的情况下,需要考虑分页的优化。
select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;
-
延迟关联
先通过
where
条件提取出主键,在将该表与原数据表关联,通过主键 id 提取数据行,而不是通过原来的二级索引提取数据行select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 100000,10) b where a.id = b.id;
-
id 偏移量
偏移量就是找到
limit
第一个参数对应的主键值,根据这个主键值再去过滤并limit
select * from table where id > (select id from table where type = 2 and level = 9 order by id asc limit 190 );
索引优化
合理的设计和使用索引,是优化慢 SQL 的利器。
-
利用覆盖索引
InnoDB 使用二级索引查询数据时会回表,但是如果索引的叶节点中已经包含要查询的字段,那它没有必要再回表查询了,这就叫覆盖索引,还有一个简单的理解查询列都是索引列。
select b from test where a = "wanna";
alter table test add index idx_a_b (a,b);
-
避免使用 or 查询
在 MySQL 5.0之前的版本要尽量避免使用
or
查询,可以使用union
或者子查询来替代,因为早期的 MySQL 版本使用or
查询可能会导致索引失效,高版本引入了索引合并,解决了这个问题,不过建议大家在实际使用中还是规范写法,能不用就少用。 -
避免使用 != 或者 <> 操作符
SQL 中,不等于操作符会导致查询引擎放弃查询索引,引起全表扫描,即时比较的字段上有索引
解决方法:通过把不等于操作符改成
or
,可以使用索引,避免全表扫描id <> 'aaa' ===> id > 'aaa' or id < 'aaa'
-
适当使用前缀索引
适当的使用前缀索引,可以降低索引的控件占用,提高索引的查询效率。
比如,邮箱的后缀都是固定的
@xxx.com
,那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引alter table test add index dix_emaile_prefix (email(6));
需要注意的是,前缀索引也存在缺点,MySQL 无法利用前缀索引做
order by
和group by
操作,也无法作为覆盖索引。 -
避免列上函数运算
要避免在列字段上进行算术运算符或其他表达式运算,否则可能会导致存储引擎无法正确的使用索引,从而影响了查询的效率。
select * from test where id + 1 = 50; select * from test where month(updateTime) = 7;
-
正确的使用联合索引
使用联合索引的时候,注意最左匹配原则。
JOIN 的优化
-
优化子查询
尽量使用
join
语句来替代子查询,因为子查询是嵌套查询,而嵌套查询会新建创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,同时对于返回结果集比较大的子查询,其对查询性能的影响更大。 -
小表驱动大表
关联查询的时候要拿小表去驱动大表,因为关联的时候,MySQL 内部会遍历驱动表,再去连接被驱动表。
select name from 小表 left join 大表;
-
适当增加冗余字段
增加冗余字段可以减少大量的连表查询,因为多张表的连表查询性能很低,所有可以适当的增加冗余字段,以减少多张表的关联查询,这是以空间换时间的优化策略。
-
避免使用 JOIN 关联太多表
《阿里巴巴 Java 开发手册》规定不要
join
超过三张表,第一join
太多降低查询的速度,第二join
的 buffer 会占用更多的内存。
排序优化
-
利用索引扫描做排序
MySQL 有两种方式生成有序结果:一是对结果集进行排序的操作,而是按照索引顺序扫描得出的结果,索引是排好序的数据结果,自然是有序的。
但是如果索引不能覆盖查询所需列(覆盖索引),就会没扫描一条记录回表查询一次(逐个获取),这个读操作是随机 IO,通常会比顺序全表扫描还慢,有时会直接放弃使用索引转为全表扫描。
因此,在设计索引时,尽可能使用同一个索引既满足排序又用于查找行。
-- 索引(a,b,c) select b,c from test where a like 'aa%' order by b,c;
只有当索引的列顺序和
order by
子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果做排序。
UNION 优化
-
条件下推
MySQL 处理 union 的策略是先创建临时表,然后将各个查询结果填充到临时表中最后再来做查询,很多优化策略在
union
查询中都会失效,因为它无法利用索引。所以需要将
where
、limit
等子句下推到union
的各个子查询中,以便优化器可以充分利用这些条件进行优化。此外,除非确实需要服务器去推,一定要试用
union all
,如果不加all
关键字,MySQL 会给临时表加上distinct
选项,这会导致对整个临时表做唯一性检查,代价很高。