PHP中的SQL优化

近期工作中遇到不少代码执行速度偏慢，通常这部分慢速代码都是由于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

邪道亦有其妙用。