PHP的SQL语句优化

时间:2021-08-08 10:55:11

(转)仅供自己学习,特此转发

普遍遇到的慢SQL有以下三种:

 1.未走索引
 2.where条件里包含子查询,多表联查
 3.查询大量数据

解决

一.索引:SQL中的高速公路

但凡优化SQL,首先要看的就是这条查询是否走了索引。走索引的查询和没走索引的差距可谓云泥之别。

可以看下面这个例子:

在一张大约3W数据量的用户表中,两种查询方式在速度上的差距:

不走索引:

select * from kw_user_copy where new_id=1
时间: 0.321 s

走主键索引:

select * from kw_user_copy where id=1
时间: 0.002 s

执行时间上有着数百倍的差距。

这种差距如果放在一些大的嵌套中,譬如循环查询500次,将成为非常致命的问题,甚至可能让程序执行超时。

PS. 很多查询条件也会导致SQL放弃索引而执行全表遍历,譬如:

select id from item where num is null

这些细节也要引起注意。

实际项目中的例子:

select id as user_id, name, nickname, photo, status, sdk_key, sdk_status from kw_user where name = 'wallkop' AND password = '44209a6a592dea91bcf7d4dd53e47a5a'
时间: 0.247 s

这是一条非常常见的用户登录查询。

直观看起来,这条SQL似乎写的非常完善了,根据name和password去查询相关用户的信息,怎么看都没有优化的余地了。

我们也知道:name和password作为两个string字段,通常是不会建立索引的,也就是说,这是一条必然不走索引的查询。

这种查询就没有优化余地了吗?

非也。

下面就是一个简单的优化:

select id from kw_user where name = 'wallkop' AND password = '44209a6a592dea91bcf7d4dd53e47a5a'
时间: 0.060 s
 
select id as user_id, name, nickname, photo, status, sdk_key, sdk_status from kw_user where id=37215
时间: 0.001 s
 
总耗时:0.061 s

将一条查询语句拆成两条,第一条不走索引的查询,我们尽量去简化它,只查一个id字段,你会惊奇的发现:速度居然提升了4倍。

而第二条查询用户详细信息的SQL,我们走了主键索引,仅仅用了0.001s。

如此一来,两条查询加起来总耗时才0.061s,比之前快了4倍。

这就是索引的灵活运用之道。

二.子查询(多表联查)优化

子查询速度慢的原因非常简单:

主查询遍历多少条数据,就要执行多少遍子查询。

简单来说,一张只有50条数据的表,普通查询和复杂子查询是没什么速度差异的,但当数据量级达到几万甚至几十万的时候,这个差距就会非常明显。

分析一条SQL语句来说明:

select id from kw_question where game_id=2 AND status=2 AND id in (select question_id from kw_answer where question_id = kw_question.id AND answer like "%瑞文%");
时间: 0.063 s

在这个语句中,where末端是一个非常坑爹的模糊文字子查询

但乍一看这条SQL速度似乎也不算太慢,其原因就在于:game_id=2和status=2这两个查询条件大幅度缩小了需要查询的数据集范围。

这就给我们了一个优化思路:

如果我能在执行子查询前,尽可能的缩减它的“数据范围”,不就可以提高查询效率了么?

分析一个实际项目里的案例,根据数据特点突破了SQL速度的极限:

select id as question_id, question, game_id, modify_time, has_attachment, status from kw_question a
where game_id=2 AND status in (0,1,2) AND not exists( select id from kw_answer where question_id=a.id and status != -1) order by a.create_time desc limit 0,20
时间: 3.160 s

上述SQL是一个问题列表页的查询语句,业务要求是把符合where条件的零回答数据筛选出来。

其实对于这种查询,最简单高效的方式是在字段里加一个answer_num,手动记录每个问题下的答案数量。

但是由于相关业务比较复杂,涉及审核、关闭、二次编辑等流程,最终项目组放弃了这个字段的使用。

所依在kw_question表缺乏相关answer_num字段的情况下,查询一个“零回答”的问题,就得去查其相关联的表:kw_answer。

这就由一个简单的单表查询变成了多表联查,大幅度增加了时间性能损耗。

平心而论,单纯从SQL的角度来讲,这条SQL已经没啥能优化的了,在无法使用answer_num这个字段的情况下,它已经写的蛮不错了。

然而3.16秒的速度真的让人无法接受。

于是开始思考:

首先EXPLAIN分析,发现主查询中rows多达157269条,难怪这么慢。

结合“零回答”这个特点仔细想了想,发现某个问题一旦有了至少一个答案,就彻底摆脱了0回答,通常来讲这个过程是不可逆的。

这就导致了这15W次查询中,大部分查询是废查询,因为你知道那些数据根本不会变动,但SQL还是把它们全部遍历了一次。

三.大量数据查询优化

1、首先分析有没有用到索引

2、