PHP中的SQL优化

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_24986157/article/details/50110981

近期工作中遇到不少代码执行速度偏慢,通常这部分慢速代码都是由于SQL语句使用不当造成的。如何改善SQL的执行质量,是一个优秀PHP程序猿的必备技能。

普遍遇到的慢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

这些细节也要引起注意。



看到这里,很多有经验的程序猿都会表示:“索引太基础了,是个人都会用啊”。

其实不然,在很多时候我们会存在一些盲点,从而忽略一些“本可以走的索引”,导致了SQL查询的效率低下。

实际项目中的例子:

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倍

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



这里推荐一个查询SQL性能的利器:EXPLAIN

explain select * from kw_user_copy where new_id=1;
explain select * from kw_user_copy where id=1;

可以自行试试explain对这两条SQL的解析差异在哪。


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

复杂查询中,子查询与多表联查非常普遍。

这些查询以SQL复杂、效率低下、维护困难等诸多特点著称,通常是程序猿和产品汪之间撕逼的导火索。

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

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

简单来说,一张只有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语句,依然不能得到较好的性能。

所依,SQL优化绝不是照搬课本就能轻松完成的,更多时候要结合业务与数据特点,因病下药


分析一个实际项目里的案例,根据数据特点突破了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天的缓存
        $cache_id = 'kw:getZeroAnswerQuestionList:'.$game_id;
        if(!$data = $this->mc->get($cache_id)) {
            $query   = $this->db_r->query("select id from kw_question  a where  not exists( select id from kw_answer where question_id=a.id and status != -1)" . $where . " AND status in (0,1,2) order by id desc");
            $data   = $query->result_array();
            $this->mc->set($cache_id, $data, 86400);
        }
 
        //过滤非零回答,并设置序列中的最新id
        if(!empty($data)) {
            foreach($data as $key=>$obj) {
                $id = $obj['id'];
                $query  = $this->db_r->query("select count(1) as num from kw_answer where question_id=".$obj['id']." AND status != -1");
                $num = $query->first_row()->num;
                if($num > 0) {
                    unset($data[$key]);
                }
            }
            $last_id = $data[0]['id'];
        } else {
            //最新id置0
            $last_id = 0;
        }
 
        //增量查询零回答,如果存在增量数据,补入数据集中
        $query   = $this->db_r->query("select id from kw_question  a where  id >= ".$last_id . $where . " AND status in (0,1,2) AND not exists( select id from kw_answer where question_id=a.id and status != -1) order by id desc");
        $data_delta   = $query->result_array();
        if(!empty($data_delta)) {
            $data = array_merge($data, $data_delta);
        }
        return $data;

思路如下:

1.初始查询:我们正常查询一次,将取到的零回答数据做一个长达1天的缓存,并将这部分数据称为“初始集”

2.数据更新:随着时间的推移,“初始集”的数据肯定会出现变化。于是我们对初始集做遍历,对集合中的每条数据执行之前的子查询(判断其回答数是否为0):

select count(1) as num from kw_answer where question_id=xxx AND status != -1

由于初始集的数量(约数百)远小于源集数量(约15W),这部分查询的速度会非常快。

查询完毕后,我们更新初始集的数据状态,将这段时间内已经从“零回答”变成“非零回答”的数据移出查询结果。

3.增量查询:我们取得初始集中最后一条数据的id,并从这条id开始往上搜索,对这段时间内新生成的数据做一次增量查询:

select id from kw_question  a where  id >= {$last_id} AND status in (0,1,2) AND not exists( select id from kw_answer where question_id=a.id and status != -1) order by id desc

查询完毕后,将这部分数据中的“零回答”数据补入查询结果。

4.返回查询结果


剑走偏锋的优化思路由于回避了最慢的那批高达15W次遍历的子查询,使速度得到了大幅度提升。

原程序时间:3.680 s
优化后时间:0.502 s


三.大量数据查询优化

通常来讲,百万甚至千万级别的数据检索,倘若走索引的话,速度还是可以让人接受的。

那走不了索引的查询又要如何处理呢?

大量数据查询是互联网时代的热点问题,无数专家大拿都有各自的心得。

作为一个普通程序猿,我在这里只介绍两种解决方案。


1.正道:分布式查询

分布式查询是解决大量查询的‘王道’方案,一次查询1000W条数据慢,那我就启用10台计算机(或者10个进程),按一定约束条件同时进行10次查询。

并把这10次查询的子结果在各自范围内进行排序,最终拼合成一个完整的结果集。

其思路,非常类似于排序算法中的桶算法

分布式固然是王道,不过代价也是不菲,高昂的硬件开销、大量临时表所占的内存空间,都应该在“是否值得分布式”的考虑范围之内。

这么看来,分布式查询更像是架构师和运维工程师该考虑的事情,那我们这群程序猿就没别的办法了吗?


2.邪道:伪查询

有正亦有邪,作为程序猿——“产品业务的实现者”,很多时候对于产品业务的理解应优于技术理解。

只要不影响用户体验,不影响产品表现,很多时候我们可以舍弃一些“真实的东西”,来获取“性能的提高”。


话不多说,看项目:

SELECT DISTINCT a.question_id from kw_answer as a  inner join kw_question AS q on a.question_id = q.id where a.status in(0,1,2) AND q.status in(0,1,2) AND a.game_id = 2 ORDER BY a.create_time desc LIMIT 0,20
时间: 0.861 s

这条查询的业务背景是:获取一批问题,并将它们按照各自答案的回答时间进行排序


首先,它是一个多表联查,绕不开全表遍历。

其次,原作者已经很聪明的将信息获取的SQL分离,在这次查询中仅仅获取相关的question_id,然后二次执行另一条SQL查询相关的详情。


这已经是一条优化的不错的SQL了,但单条查询依然在0.9秒左右,加上获取详情的SQL和程序间损耗,总时间消耗为1.186秒。

按照单次请求0.8s的及格线,它依然“不合格”。


EXPLAIN分析后,发现这条SQL的rows为199340,每次查询又有一个inner join的联查,速度自然会下降。

这条SQL真的太‘实诚’了:它拿全表做了一次子查询排序,将所有结果完整的展示了出来。

不过用户可不领情,通常用户只会阅览这些数据的前几十条,撑死了前几百条。

于是就有了邪道的优化方案:

        //查询kw_answer表中的question_id,按回答时间排序并取前500条
        $_num = 0;
        $i = 0;
        $_tmp = array();
 
        $sql1 = "SELECT question_id as id from kw_answer where game_id = 2 AND status in(0,1,2) ORDER BY create_time DESC LIMIT 0,500";
        $query = $this->db_r->query($sql1);
        $ids = $query->result_array();
 
 
        //筛选500条qid中符合条件的question数据
        $id_set = array();
        foreach($ids as $obj) {
            $id_set[] = $obj['id'];
        }
        $ids = "(".implode(",", $id_set).")";
 
        $sql2 = "SELECT id from kw_question where id in ".$ids." AND status in (0,1,2)";
        $query = $this->db_r->query($sql2);
        $_tmp = $query->result_array();
 
 
        //按分页拼装question_id,查询数据详情
        $id_set = array();
        foreach($_tmp as $obj) {
            $id_set[] = $obj['id'];
        }
        $id_set  = array_slice($id_set, $start, $this->page_size);
        $qid_arr = "(".implode(",", $id_set).")";

思路如下:

1.取最新答案:查询kw_answer表中的question_id,按回答时间排序并取前500条

2.过滤序列:根据答案的qid生成一个question_id序列,并对这些序列进行where条件的筛选

3.分页查询:按照分页参数对序列进行分页,然后查询相关详情

4.返回结果

伪查询返回的数据并不是“真实的”,只是“按回答时间排序”数据中靠前的那一部分,在这个案例中我们设置的捞取数据阈值为:500

那么实际返回的数据可能只有300-400条。

但已经足够用户看的了。

对比时间消耗:

原请求时间:1.186 s
优化后时间:0.126 s

邪道亦有其妙用。

展开阅读全文

没有更多推荐了,返回首页