接口調(diào)試：在線sql語句查看與性能優(yōu)化

后臺接口絕大數(shù)情況下，都需要與數(shù)據(jù)庫進行交互，以獲取業(yè)務(wù)數(shù)據(jù)或者接收保存客戶端上報的數(shù)據(jù)。為方便后臺開發(fā)同學(xué)進行調(diào)試，以及實時查看全部執(zhí)行的SQL語句，這里簡單地對全部執(zhí)行的SQL語句進行調(diào)試模式下輸出。

2.13.1 開啟SQL調(diào)試

開啟調(diào)試模式很簡單，但這里和通常的框架不一樣，我們不是全部統(tǒng)一地開啟調(diào)試模式，因為在接口正常調(diào)用情況下返回非法的JSON會導(dǎo)致接口結(jié)果解析失敗。故我們通過添加調(diào)試參數(shù)來控制是否開啟SQL調(diào)試。如下：

//$vim ./Public/init.php

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

特別注意： 通常，我們的調(diào)試參數(shù)不應(yīng)都簡單地使用&debug=1，而是各自定義，如 復(fù)雜一點：&__phalapi_debug__=1 ，或者再添加一個簡單的驗簽，額外帶個參數(shù)校驗，如：&phalapisign__=202cb962ac59075b964b07152d234b70。減少暴露SQL的風(fēng)險。

2.13.2 調(diào)試示例

回到前面獲取用戶基本信息接口 /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調(diào)試下

請求：

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 一個錯誤的接口開發(fā)

有時，在進行接口開發(fā)時，會需要進行批量獲取的功能，如列表。但很多開發(fā)的同學(xué)可能會因為時間趕或者沒有意識去對SQL查詢進行優(yōu)化，或者甚至不知道自己的接口背后隱藏著多少問題。下面是一個錯誤的開發(fā)示例。

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

假設(shè)我們在開發(fā)一個國際的項目，并且運行良好，BOSS說因業(yè)務(wù)需要，要加多一個接口以支持批量獲取用戶的基本信息，提供給國外某知名的社交平臺調(diào)用。

于是乎，我們很快就根據(jù)原來的單個獲取接口實現(xiàn)了新的接口：

//$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)運行調(diào)用一下

顯然，我們可以很清楚地調(diào)用新增的接口：

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": ""
}

假設(shè)我們已經(jīng)有了這樣的數(shù)據(jù)庫表數(shù)據(jù)：

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)這樣的問題？

這樣的問題，在對外黑盒調(diào)用的客戶端同學(xué)是發(fā)現(xiàn)不了的，對于測試人員來說也是無法感知的。但所犯的錯誤也是顯然易見的，就是沒有進行SQL的批量查詢優(yōu)化，造成了很多不必要的重復(fù)查詢。
這里，根據(jù)后臺接口開發(fā)人員提供的調(diào)試參數(shù)（假設(shè)為：&debug=1），則我們可以快速發(fā)現(xiàn)存在的問題：

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

如下返回，我們看到了很多重復(fù)類似的查詢語句。

[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":""}

上面輸出的調(diào)試信息，簡單補充一個格式：

[序號 - 所耗時間]SQl語句 -- [參數(shù)1, 參數(shù)2]

(4)如何改進？

這是一個很基本的問題，當(dāng)然在實際項目中不會普通存在，這里只是作為一個示例加以說明。但讓人失望的是，實際項目確實存在為數(shù)不少的這樣的情況?？赡苁切氯说募夹g(shù)和意識問題，也有可能是老同學(xué)的態(tài)度問題。所以，優(yōu)化這么一個接口的批量SQL查詢不難，難的是如何才能讓新、老同學(xué)都注重這塊的SQL查詢優(yōu)化呢？而不是等到線上服務(wù)器異常崩潰后再來推托責(zé)任。
具體的代碼改進，留給讀者自己實踐了。畢竟，看了，實踐了，才會真正深刻地掌握。

2.13.4 由此引申

• 這里不專門講述SQL的優(yōu)化，但也順便提供一些SQL查詢優(yōu)化的建議：
• 使用批量查詢，而不是N次循環(huán)查詢！
• 重復(fù)的數(shù)據(jù)，不要重復(fù)獲??；
• 根據(jù)需要，按需要獲取表字段，而不是SELECT *；
• 針對頻繁的搜索字段，建立必要的索引，以加快查詢速度；
• 使用關(guān)聯(lián)查詢，而不是粗暴地類似：where uid IN (... 這里是成千上W個用戶ID ...)；
• 針對單條SQL語句執(zhí)行時間超過1秒的，重點優(yōu)化；

2.13.5 最后最后

奉上我們堅持TDD開發(fā)下的單元測試代碼：

    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);
        }
    }

執(zhí)行單元測試的效果：

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)

搞定，收工，開飯！