PolarDB-X「DRDS」 全局二级索引 (Global Secondary Index, GSI) 结合业务压测记录「服务端PHP」

执行环境

• CPU：2.6 GHz 六核Intel Core i7
• 内存：16G
• 操作系统：macOS Catalina(10.15.2)
• 同一个docker容器中

压测工具

ab -c 100 -n 10000 -k url

执行流程

api网关->userRpc用户服务->db数据库

（福利推荐：阿里云、腾讯云、华为云服务器最新限时优惠活动，云服务器1核2G仅88元/年、2核4G仅698元/3年，点击这里立即抢购>>>）

测试流程

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=’;!!

[email protected] ~ % 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=;!!

[email protected] ~ % 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=”;!!

[email protected] ~ % 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=”;!!

[email protected] ~ % 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=”;!!

[email protected] ~ % 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)主表+冗余表

[email protected] ~ % 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)全局二级索引 主表

[email protected] ~ % 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下有一定的限制与约定，所以在替换自写冗余索引表的时候得评估下限制与约定是否会造成影响。

你还在原价购买阿里云、腾讯云、华为云、天翼云产品？那就亏大啦！现在申请成为四大品牌云厂商VIP用户，可以3折优惠价购买云服务器等云产品，并且可享四大云服务商产品终身VIP优惠价，还等什么？赶紧点击下面对应链接免费申请VIP客户吧：

1、点击这里立即申请成为腾讯云VIP客户

2、点击这里立即注册成为天翼云VIP客户

3、点击这里立即申请成为华为云VIP客户

4、点击这里立享阿里云产品终身VIP优惠价

喜欢 (0)赏

[[email protected]]

分享 (0)