章
目
录
系统业务量越大,数据库数据量越多,原先效率很高的SQL,查询速度会越来越慢,尤其是复杂的多表关联查询,因此我们很有必要针对SQL进行优化,下面潘老师来说下SQL优化查询速度常用的几种方法。
一、为什么要进行SQL优化
主要原因其实也很简单,就是用户操作页面功能发现要等好久才能刷出数据来,而其背后的原因可能如下:
- 1、硬件配置差
- 2、网络传输不稳定
- 3、后台业务处理复杂
- 4、数据库数据量大
- 5、数据库架构和表设计不合理
- 6、SQL写的太差
- ….等等
但一般80%的原因都是因为数据库性能差,查询慢,而80%的数据库性能问题都是因为SQL导致的,数据库架构和表设计那是DBA的事,我们作为后端工程师,应该着重考虑如何优化SQL以提高查询效率。
二、优化SQL的意义
- 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
- 优化效果:硬件<系统配置<数据库表结构<SQL及索引。
从上面看出,SQL及索引优化是成本最低且效果最好的一种方式。
三、查询SQL的执行过程
在执行SQL优化之前,我们必须清楚SQL的执行过程;
以MySQL为例,MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的流程,最终转变成二进制的机器码,才能被执行的,我们需要对执行的SQL进行优化,那么就有必须先来了解下,一个SQL语句的执行有哪些主要环节,以查询的SQL来举例
1)通过网络的通讯协议接收客户端传入的SQL
2)查看该SQL对应的结果在查询缓存中是否存在
a.存在则直接返回结果
b.不存在则继续往下走
3)由解析器来解析当前SQL,最终形成初步的解析树
4)再由预处理器对解析树进行调整,完成占位符赋值等操作
5)查询优化器对最终的解析树进行优化,包括调整SQL顺序等
6)根据优化后的结果得出查询语句的执行计划,就是查询数据的具体实施方案,交给查询的执行引擎
7)查询执行引擎调用存储引擎提供的API,最后由存储引擎来完成数据的查询,然后返回结果
四、SQL的执行顺序
我们写SQL时的顺序如下:
select … from … join … on … where … group by … having … order by … limit …
数据库解析SQL时的顺序如下:
from … on … join … where … group by … having … select … order by … limit …
五、SQL优化的一些方法
1、EXPLAIN
做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据:
type
列,连接类型。一个好的SQL语句至少要达到range
级别。杜绝出现all
级别。key
列,使用到的索引名。如果没有选择索引,值是NULL。key_len
列,索引长度。不损失精确性的情况下,长度越短越好rows
列,扫描行数。该值是个预估值。extra
列,详细说明。注意,常见的不太友好的值,如下:Using filesort
,Using temporary
。
针对explain
命令生成的执行计划,这里有一个查看心法。我们可以先从查询类型type
列开始查看,如果出现all
关键字,后面的内容就都可以不用看了,代表全表扫描。再看key
列,看是否使用了索引,null
代表没有使用索引。然后看rows
列,该列用来表示在SQL执行过程中被扫描的行数,该数值越大,意味着需要扫描的行数越多,相应的耗时越长,最后看Extra
列,在这列中要观察是否有Using filesort
或者Using temporary
这样的关键字出现,这些是很影响数据库性能的。
2、避免使用select *
任何地方都不要使用 select * from t
,用具体的字段列表代替*
,用哪些字段就写哪些字段,不要返回用不到的任何字段。
原因如下:
SELECT *
会增加很多不必要的消耗(CPU、IO、内存、网络带宽),- 增加了使用覆盖索引的可能性;
- 当表结构发生改变时,前端也需要更新。
所以要求直接在select后面接上字段名。
3、区分in和exists
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)。如果是exists,那么以外层表为驱动表,先被访问;如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。 比如:
# in select num from a where num in(select num from b) # 可以使用exists代替 select num from a where exists(select 1 from b where num=a.num)
4、in 和 not in 也要慎用,否则会导致全表扫描
对于连续的数值,能用 between
就不要用 in
:
select id from t where num in(1,2,3) # 使用如下代替: select id from t where num between 1 and 3
如果一定要使用in
,那么in包含的值不应过多。
5、避免在where子句中对字段进行null值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。针对为null的数据可以给个默认值代替。
6、应尽量避免在 where 子句中使用!=或操作符
where
子句中使用!=
或<>
操作符,将导致引擎放弃使用索引而进行全表扫描,可以使用 or
代替。
select id from t where num != 10 # 使用or代替 select id from t where num <10 or num >10
7、如果限制条件中其他字段没有索引,尽量少用or
or
两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。很多时候使用union all
或者是union
(必要的时候)的方式来代替or
会得到更好的效果。
select id from t where num=50 or num=100 # 代替写法 select id from t where num=50 union all select id from t where num=100
8、尽量用union all代替union
union
和union all
的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all
的前提条件是两个结果集没有重复数据。
9、避免在where子句中对字段进行表达式操作
比如下面的SQL中对字段就行了算术运算,这会造成引擎放弃使用索引,
# 表达式运算导致放弃索引 select username,salary from user_base where salary*2=20000 # 可以这样替换 select username,salary from user_base where salary=20000/2
10、尽量避免在where子句中对字段进行函数操作
在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
# 比如 select id from t where substring(name,1,3)='abc'; --name以abc开头的id # 可以使用如下代替 select id from t where name like 'abc%'
11、不建议使用%前缀模糊查询
比如like '%abc'
或者like '%abc%'
,这种查询会导致索引失效而进行全表扫描。但是可以使用like 'abc%'
。
12、避免隐式类型转换
隐式转换导致索引失效,这一点应当引起重视,也是开发中经常会犯的错误,比如is_success
是varchar(1)
类型,如果你传数值型的1或0,数据库会隐士转换为字符串形势的'1'
或'0'
则会导致索引失效。
# 比如 select id from t where is_success = 1 # 替换优化 select id from t where is_success = '1'
13、如果排序字段没有用到索引,就尽量少排序
14、对于联合索引(组合索引)来说,要遵守最左前缀法则
多列索引是先按照第一列进行排序,然后在第一列排好序的基础上再对第二列排序,如果没有第一列的话,直接访问第二列,那第二列肯定是无序的,直接访问后面的列就用不到索引了。例如组合索引(a,b,c)
,组合索引的生效原则是:
从前往后依次使用生效,如果中间某个索引没有使用,那么断点前面的索引部分起作用,断点后面的索引没有起作用;
比如:
#这种三个索引顺序使用中间没有断点,全部发挥作用; where a=3 and b=4 and c=5 #这种情况下b就是断点,a发挥了效果,c没有效果 where a=3 and c=5 #这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果; where b=3 and c=4 # 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关 where b=4 and a=3 and c=5
15、索引数量不要过多
索引并不是越多越好,索引固然可以提高相应的 select
的效率,但同时也降低了 insert
及 update
的效率,因为 insert
或 update
时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16、使用合理的分页方式以提高分页的效率
# 比如 select id,name from product limit 50000, 10
使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是50000。SQL可以采用如下的写法:
select id,name from product where id> 50000 limit 20
17、不使用ORDER BY RAND()
#比如: select id from t order by rand() limit 1000; #上面的SQL语句,可优化为: select id from t t1 join (select rand() * (select max(id) from t) as nid) t2 on t1.id > t2.nid limit 1000;
18、关于JOIN优化
1)交叉连接(笛卡尔积)
如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。在MySQL中可以为CROSS JOIN
或者省略CROSS即JOIN
,或者直接用from多表用逗号分开。如:
SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1 , table2
不用on table1.key1 = table2.key2
得出的结果是table1的记录数*table2的记录数
,如果用on
连接,得出的和inner join
的结果一样(所以在有on的情况下,inner join
、cross join
、 join
(推荐、会自动用小的表作为驱动表)结果一样)。
2)内连接:INNER JOIN
内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法。
3)左【外】连接:LEFT [out] JOIN
左连接LEFT JOIN
的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录。
4)右【外】连接:RIGHT [out] JOIN
同理右连接RIGHT JOIN
就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录。
5)全外连接:Full outer join
产生A和B的并集。对于没有匹配的记录,则以null做为值。
select *,if(a.date is null,b.date,a.date) as date from a left join b on a.date = b.date -- 只有a的全部 union -- union上面的复制下来left改为right,连个表头要一致 select *,if(a.date is null,b.date,a.date) as date from a right join b on a.date = b.date
驱动表是join优化的突破口! 那什么是驱动表呢?
- 指定了联接条件时,满足查询条件的记录行数少的表为驱动表
- 未指定联接条件时,行数少的表为驱动表(Important!)
如果你搞不清楚该让谁做驱动表、谁 join
谁,就别指定谁 left/right join
谁了,直接使用join
即可,MySQL优化器 会在运行时自行选择记录较少的表为驱动表。对于复杂的SQL可以使用EXPLAIN
SQL执行计划, 按经验谈,第一行出现的表就是驱动表。MySQL 表关联的算法是 Nest Loop Join
,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。
//例: user表10000条数据,class表20条数据 select * from user u left join class c u.userid=c.userid
上面sql的后果就是需要用user表循环10000次才能查询出来,而如果用class表驱动user表则只需要循环20次就能查询出来。
因此,优化的目标是尽可能减少JOIN中Nested Loop的循环次数,以此保证:永远用小结果集驱动大结果集。
排序的字段也有影响,有条原则:对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序!
比如:
explain select * from user u left join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id WHERE 1=1 ORDER BY u.create_time DESC limit 0,10
假如,user表有千万级记录,class表要少得多,从执行计划的得知驱动表(数据到千万级)。由于动用了LEFT JOIN
,所以相当于已经指定user
表为驱动表。
//优化第一步:LEFT JOIN改为JOIN,让较少的数据表作为驱动表 explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id WHERE 1=1 ORDER BY u.create_time DESC limit 0,10 //优化第二步:从上面执行计划得知, 有Using temporary(临时表);Using filesort,解决方法是调整排序字段(借助前面讲过排序的原则) explain select * from user u join class c on u.userid=c.userid INNER JOIN subject s on c.subjectId=s.id WHERE 1=1 ORDER BY c.id DESC limit 0,10
六、总结
对于SQL查询速度的优化,是一个比较精细的活,尤其是非常复杂的SQL更是如此,除了要掌握以上的一些SQL写法技巧,还要擅长去使用EXPLAIN SQL执行计划去分析,然后针对性地做出相应的调整优化,往往能起到更好的优化效果。