我用sysbench造了1亿条数据,mysql用了30分钟,tidb总共花了3个小时,感觉tidb在批量插入时比较慢,如果后面程序做分页查询会很慢的.
count总数、降序排序也比mysql慢。
tidb 执行时间,count 一亿条数据用了1分22秒多, order by id 升序几毫秒,但desc降序花了9秒多,limit 1000反而比limit 1要快很多:
mysql> select id from sbtest1 order by id desc limit 10;
+-----------+
| id |
+-----------+
| 100000000 |
| 99999999 |
| 99999998 |
| 99999997 |
| 99999996 |
| 99999995 |
| 99999994 |
| 99999993 |
| 99999992 |
| 99999991 |
+-----------+
10 rows in set (9.90 sec)
mysql> select count(id) from sbtest1;
+-----------+
| count(id) |
+-----------+
| 100000000 |
+-----------+
1 row in set (1 min 22.55 sec)
mysql> status;
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
Connection id: 25
Current database: sysbench
Current user: root@192.168.4.76
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.1-TiDB-v1.1.0-alpha-195-g8a5b8d5 MySQL Community Server (Apache License 2.0)
Protocol version: 10
Connection: 192.168.4.76 via TCP/IP
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 4000
mysql 执行时间,count 一亿条数据用了34秒多, order by id 降序只花了几十毫秒:
mysql> select id from sbtest1 order by id desc limit 10;
+-----------+
| id |
+-----------+
| 100000000 |
| 99999999 |
| 99999998 |
| 99999997 |
| 99999996 |
| 99999995 |
| 99999994 |
| 99999993 |
| 99999992 |
| 99999991 |
+-----------+
10 rows in set (0.01 sec)
mysql> select count(id) from sbtest1;
+-----------+
| count(id) |
+-----------+
| 100000000 |
+-----------+
1 row in set (34.55 sec)
mysql> status;
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
Connection id: 284
Current database: sysbench
Current user: root@192.168.4.76
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.20 MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.100.19 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 4 days 6 hours 9 min 57 sec
Threads: 3 Questions: 274077 Slow queries: 0 Opens: 3504 Flush tables: 1 Open tables: 299 Queries per second avg: 0.745