接口调试：在线sql语句查看与性能优化

后台接口绝大数情况下，都需要与数据库进行交互，以获取业务数据或者接收保存客户端上报的数据。为方便后台开发同学进行调试，以及实时查看全部执行的SQL语句，这里简单地对全部执行的SQL语句进行调试模式下输出。

2.13.1 开启SQL调试

开启调试模式很简单，但这里和通常的框架不一样，我们不是全部统一地开启调试模式，因为在接口正常调用情况下返回非法的JSON会导致接口结果解析失败。故我们通过添加调试参数来控制是否开启SQL调试。如下：

//$vim ./Public/init.php

//数据操作 - 基于NotORM
DI()->notorm = function() {
    $debug = isset($_GET['debug']) ? true : false;
    return new PhalApi_DB_NotORM(DI()->config->get('dbs'), $debug);
};

特别注意： 通常，我们的调试参数不应都简单地使用&debug=1，而是各自定义，如 复杂一点：&__phalapi_debug__=1 ，或者再添加一个简单的验签，额外带个参数校验，如：&phalapisign__=202cb962ac59075b964b07152d234b70。减少暴露SQL的风险。

2.13.2 调试示例

回到前面获取用户基本信息接口 /demo/?service=User.GetBaseInfo 的示例。

(1)正常情况下

请求：

http://dev.phalapi.com/demo/?service=User.GetBaseInfo&user_id=1

返回：

{"ret":200,"data":{"code":0,"msg":"","info":{"id":"1","name":"dogstar","note":"oschina"}},"msg":""}

(2)带&debug=1调试下

请求：

http://dev.phalapi.com/demo/?service=User.GetBaseInfo&user_id=1&debug=1

返回：

[1 - 0.00057s]SELECT * FROM tbl_user WHERE (id = ?); -- 1
{"ret":200,"data":{"code":0,"msg":"","info":{"id":"1","name":"dogstar","note":"oschina"}},"msg":""}

2.13.3 一个错误的接口开发

有时，在进行接口开发时，会需要进行批量获取的功能，如列表。但很多开发的同学可能会因为时间赶或者没有意识去对SQL查询进行优化，或者甚至不知道自己的接口背后隐藏着多少问题。下面是一个错误的开发示例。

(1)新增的批量获取接口

假设我们在开发一个国际的项目，并且运行良好，BOSS说因业务需要，要加多一个接口以支持批量获取用户的基本信息，提供给国外某知名的社交平台调用。

于是乎，我们很快就根据原来的单个获取接口实现了新的接口：

//$vim ./Demo/Api/User.php
<?php

class Api_User extends PhalApi_Api {

    public function getRules() {
        return array(
            //...
            'getMultiBaseInfo' => array(
                'user_ids' => array('name' => 'user_ids', 'type' => 'array', 'format' => 'explode', 'require' => true),
            ),
        );
    }

    //...

    public function getMultiBaseInfo() {
        $rs = array('code' => 0, 'msg' => '', 'list' => array());

        $domain = new Domain_User();
        foreach ($this->user_ids as $userId) {
            $rs['list'][] = $domain->getBaseInfo($userId);
        }

        return $rs;
    }
}

(2)运行调用一下

显然，我们可以很清楚地调用新增的接口：

http://dev.phalapi.com/demo/?service=User.GetMultiBaseInfo&user_ids=1,2,3

可返回：

{
    "ret": 200,
    "data": {
        "code": 0,
        "msg": "",
        "list": [
            {
                "id": "1",
                "name": "dogstar",
                "note": "oschina"
            },
            {
                "id": "2",
                "name": "Tom",
                "note": "USA"
            },
            {
                "id": "3",
                "name": "King",
                "note": "game"
            }
        ]
    },
    "msg": ""
}

假设我们已经有了这样的数据库表数据：

INSERT INTO `tbl_user` VALUES ('1', 'dogstar', 'oschina');
INSERT INTO `tbl_user` VALUES ('2', 'Tom', 'USA');
INSERT INTO `tbl_user` VALUES ('3', 'King', 'game');

(3)这样的问题？

这样的问题，在对外黑盒调用的客户端同学是发现不了的，对于测试人员来说也是无法感知的。但所犯的错误也是显然易见的，就是没有进行SQL的批量查询优化，造成了很多不必要的重复查询。
这里，根据后台接口开发人员提供的调试参数（假设为：&debug=1），则我们可以快速发现存在的问题：

http://dev.phalapi.com/demo/?service=User.GetMultiBaseInfo&user_ids=1,2,3&debug=1

如下返回，我们看到了很多重复类似的查询语句。

[1 - 0.0005s]SELECT * FROM tbl_user WHERE (id = ?); -- 1
[2 - 0.00042s]SELECT * FROM tbl_user WHERE (id = ?); -- 2
[3 - 0.00038s]SELECT * FROM tbl_user WHERE (id = ?); -- 3
{"ret":200,"data":{"code":0,"msg":"","list":[{"id":"1","name":"dogstar","note":"oschina"},{"id":"2","name":"Tom","note":"USA"},{"id":"3","name":"King","note":"game"}]},"msg":""}

上面输出的调试信息，简单补充一个格式：

[序号 - 所耗时间]SQl语句 -- [参数1, 参数2]

(4)如何改进？

这是一个很基本的问题，当然在实际项目中不会普通存在，这里只是作为一个示例加以说明。但让人失望的是，实际项目确实存在为数不少的这样的情况。可能是新人的技术和意识问题，也有可能是老同学的态度问题。所以，优化这么一个接口的批量SQL查询不难，难的是如何才能让新、老同学都注重这块的SQL查询优化呢？而不是等到线上服务器异常崩溃后再来推托责任。
具体的代码改进，留给读者自己实践了。毕竟，看了，实践了，才会真正深刻地掌握。

2.13.4 由此引申

• 这里不专门讲述SQL的优化，但也顺便提供一些SQL查询优化的建议：
• 使用批量查询，而不是N次循环查询！
• 重复的数据，不要重复获取；
• 根据需要，按需要获取表字段，而不是SELECT *；
• 针对频繁的搜索字段，建立必要的索引，以加快查询速度；
• 使用关联查询，而不是粗暴地类似：where uid IN (... 这里是成千上W个用户ID ...)；
• 针对单条SQL语句执行时间超过1秒的，重点优化；

2.13.5 最后最后

奉上我们坚持TDD开发下的单元测试代码：

    public function testGetMultiBaseInfo()
    {
        $str = 'service=User.GetMultiBaseInfo&user_ids=1,2,3';
        parse_str($str, $params);

        DI()->request = new PhalApi_Request($params);

        $api = new Api_User();
        //自己进行初始化
        $api->init();
        $rs = $api->getMultiBaseInfo();

        $this->assertNotEmpty($rs);
        $this->assertArrayHasKey('code', $rs);
        $this->assertArrayHasKey('msg', $rs);
        $this->assertArrayHasKey('list', $rs);

        foreach ($rs['list'] as $item) {
            $this->assertArrayHasKey('id', $item);
            $this->assertArrayHasKey('name', $item);
            $this->assertArrayHasKey('note', $item);
        }
    }

执行单元测试的效果：

dogstar@ubuntu:Tests$ phpunit --filter testGetMultiBaseInfo ./Api/Api_User_Test.php 
PHPUnit 4.3.4 by Sebastian Bergmann.

.

Time: 23 ms, Memory: 6.25Mb

OK (1 test, 13 assertions)

搞定，收工，开饭！