当前位置:Gxlcms > 数据库问题 > 一个长耗时SQL在TiDB和Mysql上的耗时测试

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

时间:2021-07-01 10:21:17 帮助过:40人阅读

之前看到的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部署

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

命令行客户端连接方式

  1. <code>mysql -h 127.0.0.1 -P 4000 -u root</code>

测试

表结构

  1. <code>create table testTable (
  2. `id` int(10) unsigned auto_increment primary key,
  3. `uid` int(10) not null default 0,
  4. `day` int(6) not null default 0,
  5. `field1` varchar(16) not null default '',
  6. `field2` tinyint(3) not null default 0,
  7. `field3` tinyint(3) not null default 0,
  8. `field4` tinyint(3) not null default 0,
  9. `field5` varchar(32) not null default '',
  10. `field6` varchar(32) not null default '',
  11. `field7` varchar(32) not null default '',
  12. key `uid_day_idx` (`uid`,`day`,`field1`, `field3`)
  13. ) engine=InnoDB default charset=utf8;</code>

插入数据

  • 5000个随机的uid

  • day 平均分布在20190801~20190830

  • filed1 随机取‘a0‘ ~ ‘a9‘
  • field3 随机取0~10

插入数据脚本

  1. <code><?php
  2. $maxRecordNum = 10000000;
  3. $tableName = 'testTable';
  4. $host = '127.0.0.1';
  5. $port = '4000';
  6. $dbname = 'test';
  7. $username = 'root';
  8. $password = '';
  9. $dsn = "mysql:host={$host};dbname={$dbname};port={$port}";
  10. function getDay($maxRecordNum, $i) {
  11. $item = (int)($maxRecordNum / 30);
  12. $day = 20190801;
  13. $add = (int)($i/ $item);
  14. return $day + $add;
  15. }
  16. function getRandomFieldOne() {
  17. static $fieldsOne = [
  18. "'a0'", "'a1'", "'a2'", "'a3'", "'a4'", "'a5'", "'a6'", "'a7'", "'a8'", "'a9'",
  19. ];
  20. return $fieldsOne[rand(0, count($fieldsOne) -1)];
  21. }
  22. function getRandomFieldTwo() {
  23. return rand(0, 1);
  24. }
  25. function getRandomFieldThree() {
  26. return rand(0, 10);
  27. }
  28. function getRandomFieldFour() {
  29. return rand(0, 8);
  30. }
  31. function generateRecordsValue($day) {
  32. $minUid = 200000000;
  33. $maxUid = $minUid + 5000;
  34. $arrRecord = [
  35. 'uid' => rand($minUid, $maxUid),
  36. 'day' => $day,
  37. 'field1' => getRandomFieldOne(),
  38. 'field2' => getRandomFieldTwo(),
  39. 'field3' => getRandomFieldThree(),
  40. 'field4' => getRandomFieldFour(),
  41. 'field5' => "'static'",
  42. 'field6' => "'static'",
  43. 'field7' => "'static'",
  44. ];
  45. return $arrRecord;
  46. }
  47. try {
  48. $db = new PDO($dsn, $username, $password);
  49. $db->query("truncate {$tableName};");
  50. $db->query("alter table {$tableName} AUTO_INCREMENT=1;");
  51. $arr = [];
  52. for ($i = 1; $i <= $maxRecordNum; $i++) {
  53. $day = getDay($maxRecordNum, $i);
  54. $arr[] = '(' . implode(',', generateRecordsValue($day)) . ')';
  55. if ($i % 10000 === 0) {
  56. $sql = "INSERT INTO {$tableName} (" . implode(',', array_keys(generateRecordsValue(0))) . ") values" . implode(',', $arr) .';';
  57. $res = $db->query($sql);
  58. $arr = [];
  59. sleep(1);
  60. echo "{$i}\n";
  61. }
  62. }
  63. } catch (Exception $e) {
  64. echo $e->getMessage();
  65. }</code>

测试sql脚本

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

  1. <code><?php
  2. $tableName = 'testTable';
  3. $host = '127.0.0.1';
  4. $dbname = 'test';
  5. $username = 'root';
  6. $port = '4000';
  7. $password = '';
  8. //$port = '3306';
  9. //$password = 'guapi123';
  10. $dsn = "mysql:host={$host};dbname={$dbname};port={$port}";
  11. try {
  12. $db = new PDO($dsn, $username, $password);
  13. $arrUid = [];
  14. $minUid = 200000000;
  15. for ($i = 0; $i < 2000; $i++) {
  16. $arrUid[] = $minUid + $i;
  17. }
  18. $total = 0;
  19. for ($i = 0; $i < 15; $i++) {
  20. $startDay = 20190801 + $i;
  21. $endDay = $startDay + 14;
  22. $sql = "select field4 from {$tableName} where uid in (" . implode(',', $arrUid) . ") and day >= {$startDay} and day<={$endDay} and field1 = 'a0' and field3 in (3, 5);";
  23. $startTime = microtime(true);
  24. $res = $db->query($sql);
  25. $endTime = microtime(true);
  26. $cost = (int)(($endTime - $startTime) * 1000);
  27. echo "cost:{$cost}ms\n";
  28. $total += $cost;
  29. }
  30. echo "avg cost:" . (int)($total / 15) . "ms\n";
  31. } catch (Exception $e) {
  32. echo $e->getMessage();
  33. }</code>

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

TiDB

  1. <code>cost:1744ms
  2. cost:646ms
  3. cost:720ms
  4. cost:614ms
  5. cost:644ms
  6. cost:659ms
  7. cost:662ms
  8. cost:731ms
  9. cost:728ms
  10. cost:669ms
  11. cost:816ms
  12. cost:682ms
  13. cost:778ms
  14. cost:857ms
  15. cost:718ms
  16. avg cost:777ms</code>

Mysql

  1. <code>cost:5256ms
  2. cost:5165ms
  3. cost:5300ms
  4. cost:5461ms
  5. cost:5376ms
  6. cost:5334ms
  7. cost:5435ms
  8. cost:5339ms
  9. cost:5314ms
  10. cost:5278ms
  11. cost:5346ms
  12. cost:5244ms
  13. cost:5387ms
  14. cost:5497ms
  15. cost:5633ms
  16. avg cost:5357ms</code>

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

标签:测试   arc   value   git clone   下单   imp   ldo   art   creat   

人气教程排行