执行环境
- CPU:2.6 GHz 六核Intel Core i7
- 内存:16G
- 操作系统:macOS Catalina(10.15.2)
- 同一个docker容器中
压测工具
ab -c 100 -n 10000 -k url
执行流程
api网关->userRpc用户服务->db数据库
测试流程
SELECT COUNT( ) FROM users; !!#ff0000 COUNT():1000w+!!
1、 读取一行数据(select *)
1)drds(5.6.29-TDDL-5.3.7-15460044)主表执行
执行的sql:
!!#0000ff SELECT * FROM users WHERE mobile=';!!
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 353 bytes
Concurrency Level: 100
Time taken for tests: 12.148 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 7130000 bytes
HTML transferred: 3530000 bytes
Requests per second: 823.16 [#/sec] (mean)
Time per request: 121.483 [ms] (mean)
Time per request: 1.215 [ms] (mean, across all concurrent requests)
Transfer rate: 573.16 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.3 0 5
Processing: 42 119 42.8 111 766
Waiting: 41 119 42.8 111 766
Total: 42 120 42.8 112 766
Percentage of the requests served within a certain time (ms)
50% 112
66% 123
75% 133
80% 139
90% 160
95% 184
98% 242
99% 321
100% 766 (longest request)
2)drds(5.6.29-TDDL-5.3.7-15460044)冗余表索引+主表缺失列回查执行
执行的sql:
!!#0000ff SELECT * FROM redundance_mobile WHERE mobile='';!!
!!#0000ff SELECT * FROM users WHERE uid=;!!
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 353 bytes
Concurrency Level: 100
Time taken for tests: 19.521 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 7130000 bytes
HTML transferred: 3530000 bytes
Requests per second: 512.28 [#/sec] (mean)
Time per request: 195.207 [ms] (mean)
Time per request: 1.952 [ms] (mean, across all concurrent requests)
Transfer rate: 356.69 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.3 0 7
Processing: 57 192 101.8 155 824
Waiting: 56 192 101.7 155 824
Total: 60 192 101.8 155 824
Percentage of the requests served within a certain time (ms)
50% 155
66% 171
75% 185
80% 199
90% 391
95% 440
98% 478
99% 514
100% 824 (longest request)
3)drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引执行
执行的sql:
!!#0000ff SELECT * FROM users WHERE mobile='';!!
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 353 bytes
Concurrency Level: 100
Time taken for tests: 14.018 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 7130000 bytes
HTML transferred: 3530000 bytes
Requests per second: 713.37 [#/sec] (mean)
Time per request: 140.181 [ms] (mean)
Time per request: 1.402 [ms] (mean, across all concurrent requests)
Transfer rate: 496.71 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.8 0 181
Processing: 27 139 49.4 127 451
Waiting: 22 139 49.3 127 451
Total: 28 139 49.4 127 451
Percentage of the requests served within a certain time (ms)
50% 127
66% 141
75% 152
80% 161
90% 210
95% 257
98% 287
99% 302
100% 451 (longest request)
|__ab并发和请求数__|__数据库环境__|__执行时间(5次平均值)__|__QPS(每秒请求数)(5次平均值)__|
|100并发,10000请求|drds(5.6.29-TDDL-5.3.7-15460044)主表执行|14.62 seconds|713|
||100并发,10000请求||drds(5.6.29-TDDL-5.3.7-15460044)冗余表索引+主表缺失列回查执行|| 19.77 seconds||514||
||100并发,10000请求||drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引|| 17.18 seconds||592||
2、 读取一行数据(select uid mobile)查询二级索引/索引表(不回表)
1)drds(5.6.29-TDDL-5.3.7-15460044)冗余表
执行的sql:
!!#0000ff SELECT uid,mobile FROM redundance_mobile WHERE mobile='';!!
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 353 bytes
Concurrency Level: 100
Time taken for tests: 15.822 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 7130000 bytes
HTML transferred: 3530000 bytes
Requests per second: 632.03 [#/sec] (mean)
Time per request: 158.221 [ms] (mean)
Time per request: 1.582 [ms] (mean, across all concurrent requests)
Transfer rate: 440.07 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.8 0 178
Processing: 67 155 46.4 147 879
Waiting: 67 155 46.4 147 879
Total: 67 156 46.5 147 880
Percentage of the requests served within a certain time (ms)
50% 147
66% 160
75% 168
80% 175
90% 193
95% 216
98% 323
99% 365
100% 880 (longest request)
2)drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引
执行的sql:
!!#0000ff SELECT uid,mobile FROM users WHERE mobile='';!!
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 90 bytes
Concurrency Level: 100
Time taken for tests: 12.937 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 4490000 bytes
HTML transferred: 900000 bytes
Requests per second: 773.00 [#/sec] (mean)
Time per request: 129.366 [ms] (mean)
Time per request: 1.294 [ms] (mean, across all concurrent requests)
Transfer rate: 338.94 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 17.5 0 1753
Processing: 26 128 180.5 100 1989
Waiting: 26 128 180.5 99 1989
Total: 31 129 181.4 100 1989
Percentage of the requests served within a certain time (ms)
50% 100
66% 116
75% 128
80% 136
90% 169
95% 243
98% 313
99% 1789
100% 1989 (longest request)
||~ __ab并发和请求数__||__数据库环境__||__执行时间(5次平均值)__||__QPS(每秒请求数)(5次平均值)__||
||100并发,10000请求||drds(5.6.29-TDDL-5.3.7-15460044)|| 17.44 seconds||587||
||100并发,10000请求||drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引|| 12.18 seconds||830||
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WTtqoiEW-1586768686639)(/tfl/captures/2020-04/tapd_38981207_base64_1586765666_50.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ntlWYqKS-1586768686639)(/tfl/captures/2020-04/tapd_38981207_base64_1586766337_18.png)]
3、 插入数据
1)drds(5.6.29-TDDL-5.3.7-15460044)主表+冗余表
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 162 bytes
Concurrency Level: 100
Time taken for tests: 418.928 seconds
Complete requests: 10000
Failed requests: 8842
(Connect: 0, Receive: 0, Length: 8842, Exceptions: 0)
Total transferred: 5230254 bytes
HTML transferred: 1630254 bytes
Requests per second: 23.87 [#/sec] (mean)
Time per request: 4189.277 [ms] (mean)
Time per request: 41.893 [ms] (mean, across all concurrent requests)
Transfer rate: 12.19 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.3 0 4
Processing: 845 4161 672.7 4231 7236
Waiting: 842 4161 672.7 4231 7236
Total: 846 4162 672.7 4232 7236
Percentage of the requests served within a certain time (ms)
50% 4232
66% 4491
75% 4632
80% 4725
90% 4948
95% 5122
98% 5375
99% 5656
100% 7236 (longest request)
2)drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引 主表
joex@joexdembp ~ % ab -c 100 -n 10000 http://127.0.0.1/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests
Server Software: swoole-http-server
Server Hostname: 127.0.0.1
Server Port: 80
Document Path: /
Document Length: 57 bytes
Concurrency Level: 100
Time taken for tests: 10.948 seconds
Complete requests: 10000
Failed requests: 0
Total transferred: 4160000 bytes
HTML transferred: 570000 bytes
Requests per second: 913.39 [#/sec] (mean)
Time per request: 109.482 [ms] (mean)
Time per request: 1.095 [ms] (mean, across all concurrent requests)
Transfer rate: 371.06 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.4 0 11
Processing: 38 108 43.8 99 605
Waiting: 38 107 43.8 98 600
Total: 38 108 43.8 99 605
Percentage of the requests served within a certain time (ms)
50% 99
66% 111
75% 120
80% 126
90% 148
95% 178
98% 264
99% 313
100% 605 (longest request)
||~ __ab并发和请求数__||__数据库环境__||__执行时间(5次平均值)__||__QPS(每秒请求数)(5次平均值)__||
||100并发,10000请求||drds(5.6.29-TDDL-5.3.7-15460044)|| 24 seconds||408.55||
||100并发,10000请求||drds(5.6.29-TDDL-5.4.4-15864860)全局二级索引|| 11.808 seconds||856||
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AqhVqhsc-1586768686640)(/tfl/captures/2020-04/tapd_38981207_base64_1586765507_87.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iQN9GcAt-1586768686640)(/tfl/captures/2020-04/tapd_38981207_base64_1586765286_61.png)]
结论
目前的结论是基于数据量在1000W+
1、在查询(SELECT * )查询所有列时:直接查询主表-未建全局二级索引的执行时间和QPS是 最优的 ;查询查询主表-建立全局二级索引的执行时间和QPS次之,应该是因为查询了索引表后进行了回表;而自建冗余表索引+主表缺失列回查执行和全局二级索引原理类似,执行效率也是差不多。
2、在查询 (select uid mobile)全局二级索引/自写冗余索引表(不回表)时:全局二级索引比自写冗余索引表的优势明显。
3、插入数据:主表+写冗余索引表和全局二级索引的对比,全局二级索引优势更加明显。
4、GSI综合比较下来比自写冗余索引表方式效率更优,但考虑目前GSI在DML、DDL下有一定的限制与约定,所以在替换自写冗余索引表的时候得评估下限制与约定是否会造成影响。