时间:2021-07-01 10:21:17 帮助过:40人阅读
之前看到的TiDB和MySql的性能对比都是大量短耗时请求下的压测,单机情况下TiDB和MySql的确有些差距,不过笔者最近碰到的场景更多是sql要扫描的行数不小的情况下单sql比较耗时的问题,所以自己做了个简单测试这类型sql的耗时。
按照官方文档(https://pingcap.com/docs-cn/dev/how-to/get-started/deploy-tidb-from-docker-compose/)直接使用docker-composer部署
- <code>git clone https://github.com/pingcap/tidb-docker-compose.git # 下载
- cd tidb-docker-compose && docker-compose pull # 拉取镜像
- docker-compose up -d # 启动</code>
命令行客户端连接方式
- <code>mysql -h 127.0.0.1 -P 4000 -u root</code>
- <code>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;</code>
5000个随机的uid
day 平均分布在20190801~20190830
field3 随机取0~10
- <code><?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();
- }</code>
查询15天范围内, 2000个uid, 附带两个字段条件
- <code><?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();
- }</code>
- <code>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</code>
- <code>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</code>
一个长耗时SQL在TiDB和Mysql上的耗时测试
标签:测试 arc value git clone 下单 imp ldo art creat