一个长耗时SQL在TiDB和Mysql上的耗时测试

你的名字 2023-02-28 11:29 30阅读 0赞

参考文章：[一个长耗时SQL在TiDB和Mysql上的耗时测试][SQL_TiDB_Mysql]

之前看到的TiDB和MySql的性能对比都是大量短耗时请求下的压测，单机情况下TiDB和MySql的确有些差距，不过笔者最近碰到的场景更多是sql要扫描的行数不小的情况下单sql比较耗时的问题，所以自己做了个简单测试这类型sql的耗时。

## TiDB单机环境部署 ##

按照官方文档([https://pingcap.com/docs-cn/dev/how-to/get-started/deploy-tidb-from-docker-compose/][https_pingcap.com_docs-cn_dev_how-to_get-started_deploy-tidb-from-docker-compose])  
直接使用docker-composer部署

git clone https://github.com/pingcap/tidb-docker-compose.git  # 下载
    
    cd tidb-docker-compose && docker-compose pull # 拉取镜像
    docker-compose up -d # 启动

命令行客户端连接方式

mysql -h 127.0.0.1 -P 4000 -u root

## 测试 ##

### 表结构 ###

create table testTable (
        `id` int(10) unsigned auto_increment primary key,
        `uid` int(10) not null default 0,
        `day` int(6) not null default 0,
        `field1` varchar(16) not null default '',
        `field2` tinyint(3) not null default 0,
        `field3` tinyint(3) not null default 0,
        `field4` tinyint(3) not null default 0,
        `field5` varchar(32) not null default '',
        `field6` varchar(32) not null default '',
        `field7` varchar(32) not null default '',
        key `uid_day_idx` (`uid`,`day`,`field1`, `field3`)
    ) engine=InnoDB default charset=utf8;

### 插入数据 ###

>  *  5000个随机的uid
>  *  day 平均分布在20190801~20190830
>  *  filed1 随机取'a0' ~ 'a9'
>  *  field3 随机取0~10

### 插入数据脚本 ###

<?php
    $maxRecordNum = 10000000;
    
    $tableName = 'testTable';
    $host = '127.0.0.1';
    $port = '4000';
    $dbname = 'test';
    $username = 'root';
    $password = '';
    $dsn = "mysql:host={$host};dbname={$dbname};port={$port}";
    
    function getDay($maxRecordNum, $i) {
        $item = (int)($maxRecordNum / 30);
        $day = 20190801;
        $add = (int)($i/ $item);
        return $day + $add;
    }
    
    function getRandomFieldOne() {
        static $fieldsOne = [
            "'a0'", "'a1'", "'a2'", "'a3'", "'a4'", "'a5'", "'a6'", "'a7'", "'a8'", "'a9'",
        ];
        return $fieldsOne[rand(0, count($fieldsOne) -1)];
    }
    
    function getRandomFieldTwo() {
        return rand(0, 1);
    }
    
    function getRandomFieldThree() {
        return rand(0, 10);
    }
    
    function getRandomFieldFour() {
        return rand(0, 8);
    }
    
    function generateRecordsValue($day) {
        $minUid = 200000000;
        $maxUid = $minUid + 5000;
    
        $arrRecord = [
            'uid' => rand($minUid, $maxUid),
            'day' => $day,
            'field1' => getRandomFieldOne(),
            'field2' => getRandomFieldTwo(),
            'field3' => getRandomFieldThree(),
            'field4' => getRandomFieldFour(),
            'field5' => "'static'",
            'field6' => "'static'",
            'field7' => "'static'",
        ];
        return $arrRecord;
    }
    
    try {
        $db = new PDO($dsn, $username, $password);
    
        $db->query("truncate {$tableName};");
        $db->query("alter table {$tableName} AUTO_INCREMENT=1;");
        $arr = [];
        for ($i = 1; $i <= $maxRecordNum; $i++) {
                $day = getDay($maxRecordNum, $i);
                $arr[] = '(' . implode(',', generateRecordsValue($day)) . ')';
            if ($i % 10000 === 0) {
                $sql = "INSERT INTO {$tableName} (" . implode(',', array_keys(generateRecordsValue(0))) . ") values" . implode(',', $arr) .';';
                $res = $db->query($sql);
                $arr = [];
                sleep(1);
                echo "{$i}\n";
            }
        }
    } catch (Exception $e) {
        echo $e->getMessage();
    }

### 测试sql脚本 ###

查询15天范围内, 2000个uid, 附带两个字段条件

<?php
    $tableName = 'testTable';
    $host = '127.0.0.1';
    $dbname = 'test';
    $username = 'root';
    $port = '4000';
    $password = '';
    //$port = '3306';
    //$password = 'guapi123';
    $dsn = "mysql:host={$host};dbname={$dbname};port={$port}";
    
    try {
        $db = new PDO($dsn, $username, $password);
        $arrUid = [];
        $minUid = 200000000;
        for ($i = 0; $i < 2000; $i++) {
            $arrUid[] = $minUid + $i;
        }
        $total = 0;
        for ($i = 0; $i < 15; $i++) {
            $startDay = 20190801 + $i;
            $endDay = $startDay + 14;
            $sql = "select field4 from {$tableName} where uid in (" . implode(',', $arrUid) . ") and day >= {$startDay} and day<={$endDay} and field1 = 'a0' and field3 in (3, 5);";
            $startTime = microtime(true);
            $res = $db->query($sql);
            $endTime = microtime(true);
            $cost = (int)(($endTime - $startTime)  * 1000);
            echo "cost:{$cost}ms\n";
            $total += $cost;
        }
        echo "avg cost:" . (int)($total / 15) . "ms\n";
    
    } catch (Exception $e) {
        echo $e->getMessage();
    }

### 测试结果(Mysql和TiDB缓存策略都是默认配置) ###

TiDB

cost:1744ms
    cost:646ms
    cost:720ms
    cost:614ms
    cost:644ms
    cost:659ms
    cost:662ms
    cost:731ms
    cost:728ms
    cost:669ms
    cost:816ms
    cost:682ms
    cost:778ms
    cost:857ms
    cost:718ms
    avg cost:777ms

### Mysql ###

cost:5256ms
    cost:5165ms
    cost:5300ms
    cost:5461ms
    cost:5376ms
    cost:5334ms
    cost:5435ms
    cost:5339ms
    cost:5314ms
    cost:5278ms
    cost:5346ms
    cost:5244ms
    cost:5387ms
    cost:5497ms
    cost:5633ms
    avg cost:5357ms

[SQL_TiDB_Mysql]: https://www.cnblogs.com/Me1onRind/p/11372026.html
[https_pingcap.com_docs-cn_dev_how-to_get-started_deploy-tidb-from-docker-compose]: https://pingcap.com/docs-cn/dev/how-to/get-started/deploy-tidb-from-docker-compose/