一个长耗时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/)

直接使用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上的耗时测试

TiDB单机环境部署

测试

表结构

插入数据

插入数据脚本

测试sql脚本

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

TiDB

Mysql

最新文章

热门文章