HybirdDB_VS_RDS查询测试

2017-11-03

获取测试数据

mysqldump -h[ip] -uxxx -pxxx --default-character-set=utf8 --databases vingoo_manage_sys --tables vg_terminal_logs_021_2016 vg_terminal_logs_021_2017 vg_card_info > testdb.sql

[root@ToBeRoot juang]# file testdb.sql 
testdb.sql: UTF-8 Unicode text, with very long lines

RDS测试环境的准备

RDS For MySQL 5.6 8核32G

mysql -uzyadmin -pUploo00king -h rm-bp1nix28m50dinb27.mysql.rds.aliyuncs.com juang < testdb.sql
mysql>show table status ;

Name	Engine	Rows	Avg_row_length	Data_length	Index_length
vg_card_info	InnoDB	61810	367	22740992	51445760
vg_terminal_logs_021_2016	InnoDB	1965345	820	1612709888	341671936
vg_terminal_logs_021_2017	InnoDB	2854918	820	2341470208	558202880

HybirdDB测试环境准备

8核32G 2个节点

mysql -uzyadmin -pUploo00king -h pub-m-bp18e997b01056b4.petadata.rds.aliyuncs.com juang

CREATE TABLE `vg_card_info` (
  `id_outside` char(50) DEFAULT '' PRIMARY KEY,
  `id_inside` char(50) NOT NULL,
  `brand_id` int(11) DEFAULT '0',
  `id_code` char(50) DEFAULT '' ,
  `id_remark` varchar(50) DEFAULT '',
  `id_weixin` char(50) DEFAULT '',
  `id_alipay` char(50) DEFAULT '',
  `card_value` decimal(11,2) DEFAULT '0.00',
  `card_value_face` decimal(11,2) DEFAULT '0.00',
  `card_type` int(11) DEFAULT '1',
  `card_checkout` int(11) DEFAULT '0',
  `card_recharge` int(11) DEFAULT '0',
  `create_id` int(11) DEFAULT '0',
  `tips` int(11) DEFAULT '0' ,
  `status` int(10) DEFAULT '1',
  `add_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `add_name` varchar(20) DEFAULT NULL,
  `remark` text CHARACTER SET utf8 COLLATE utf8_icelandic_ci,
  `edit_op` varchar(20) DEFAULT NULL,
  `edit_date` varchar(20) DEFAULT NULL,
  KEY `IDX_brand_id` (`brand_id`),
  KEY `IDX_card_type` (`card_type`),
  KEY `IDX_id_inside` (`id_inside`),
  KEY `IDX_id_weixin` (`id_weixin`) USING BTREE
) distribute_key (id_outside) DEFAULT CHARSET=utf8;

CREATE TABLE `vg_terminal_logs_021_2016` (
  `id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT '0' ,
  `terminal_num` varchar(100) DEFAULT NULL ,
  `address` varchar(100) DEFAULT NULL ,
  `kiosk_time` int DEFAULT NULL,
  `kiosk_info` char(255) DEFAULT NULL,
  `kiosk_initstatus` int(20) DEFAULT '0' ,
  `purchase_way` int(20) DEFAULT '0' ,
  `price` decimal(10,2) DEFAULT '10.00',
  `trade_no` varchar(100) DEFAULT NULL,
  `cash_starting` int(20) DEFAULT '0',
  `cash_prerecieved` int(20) DEFAULT '0',
  `cash_recieved` int(20) DEFAULT '0',
  `card_starting` int(20) DEFAULT '0' ,
  `card_reading` char(255) DEFAULT NULL,
  `card_account` decimal(10,2) DEFAULT '0.00' ,
  `card_preorder` int(11) DEFAULT '0' ,
  `kiosk_temperature` int(11) DEFAULT '0' ,
  `kiosk_humidity` int(11) DEFAULT '0',
  `kiosk_ozone` int(11) DEFAULT '0' ,
  `orange_stock` int(20) DEFAULT '0' ,
  `coins_stock` char(50) DEFAULT '-1' ,
  `upload_date` datetime DEFAULT NULL ,
  UNIQUE KEY `IDX_terminal_num_time` (`terminal_num`,`kiosk_time`) USING BTREE,
  KEY `IDX_brand_id` (`brand_id`),
  KEY `IDX_card_account` (`card_account`),
  KEY `IDX_kiosk_time` (`kiosk_time`),
  KEY `IDX_trade_no` (`trade_no`),
  KEY `IDX_terminal_num_update` (`terminal_num`,`upload_date`),
  KEY `IDX_update` (`upload_date`)
)distribute_key (kiosk_time) DEFAULT CHARSET=utf8 ;



CREATE TABLE `vg_terminal_logs_021_2017` (
  `id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
  `brand_id` int(11) DEFAULT '0' ,
  `terminal_num` varchar(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `kiosk_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `kiosk_info` char(255) DEFAULT NULL,
  `kiosk_initstatus` int(20) DEFAULT '0' ,
  `purchase_way` int(20) DEFAULT '0' ,
  `price` decimal(10,2) DEFAULT '10.00' ,
  `trade_no` varchar(100) DEFAULT NULL ,
  `cash_starting` int(20) DEFAULT '0' ,
  `cash_prerecieved` int(20) DEFAULT '0' ,
  `cash_recieved` int(20) DEFAULT '0' ,
  `card_starting` int(20) DEFAULT '0' ,
  `card_reading` char(255) DEFAULT NULL,
  `card_account` decimal(10,2) DEFAULT '0.00' ,
  `card_preorder` int(11) DEFAULT '0',
  `kiosk_temperature` int(11) DEFAULT '0' ,
  `kiosk_humidity` int(11) DEFAULT '0' ,
  `kiosk_ozone` int(11) DEFAULT '0' ,
  `orange_stock` int(20) DEFAULT '0' ,
  `coins_stock` char(50) DEFAULT '-1' ,
  `upload_date` int DEFAULT NULL,
  UNIQUE KEY `IDX_terminal_num_time` (`terminal_num`,`kiosk_time`) USING BTREE,
  KEY `IDX_brand_id` (`brand_id`),
    KEY `IDX_card_account` (`card_account`),
  KEY `IDX_kiosk_time` (`kiosk_time`),
  KEY `IDX_trade_no` (`trade_no`),
  KEY `IDX_terminal_num_update` (`terminal_num`,`upload_date`) USING BTREE,
  KEY `IDX_update` (`upload_date`)
) distribute_key (upload_date) DEFAULT CHARSET=utf8 ;

注意事项：

ddl规则如链接：https://help.aliyun.com/document_detail/48679.html?spm=5176.product26320.6.561.ilcKkj

PRIMARY KEY AUTO_INCREMENT
comment只能是英文
distribute_key在前
ddl语句创建时间消耗在1分钟左右

测试过程

测试语句经过简化

select count(id) from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-09-01 00:00:00' and upload_date<='2017-09-30 23:59:59';

select count(*) from (select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2016 as a left join vg_card_info as b on a.card_reading=b.id_outside where kiosk_time >='2016-12-01 00:00:00' and kiosk_time<='2016-12-31 23:59:59'
union 
select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-01-01 00:00:00' and upload_date<='2017-01-31 23:59:59'
) as t1;

RDS执行时间

mysql> select count(id) from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-09-01 00:00:00' and upload_date<='2017-09-30 23:59:59';
+-----------+
| count(id) |
+-----------+
|    348626 |
+-----------+
1 row in set (2.79 sec)

mysql> select count(id) from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-09-01 00:00:00' and upload_date<='2017-09-30 23:59:59';
+-----------+
| count(id) |
+-----------+
|    348626 |
+-----------+
1 row in set (2.77 sec)


mysql> select count(*) from (select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2016 as a left join vg_card_info as b on a.card_reading=b.id_outside where kiosk_time >='2016-12-01 00:00:00' and kiosk_time<='2016-12-31 23:59:59'
    -> union 
    -> select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-01-01 00:00:00' and upload_date<='2017-01-31 23:59:59'
    -> ) as t1;
+----------+
| count(*) |
+----------+
|   471910 |
+----------+
1 row in set (9.86 sec)
mysql> select count(*) from (select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2016 as a left join vg_card_info as b on a.card_reading=b.id_outside where kiosk_time >='2016-12-01 00:00:00' and kiosk_time<='2016-12-31 23:59:59' union  select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-01-01 00:00:00' and upload_date<='2017-01-31 23:59:59' ) as t1;
+----------+
| count(*) |
+----------+
|   471910 |
+----------+
1 row in set (9.68 sec)

HybirdDB测试过程

mysql> select count(id) from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-09-01 00:00:00' and upload_date<='2017-09-30 23:59:59';
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (1.55 sec)

mysql> select count(id) from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-09-01 00:00:00' and upload_date<='2017-09-30 23:59:59';
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.35 sec)

mysql> select count(*) from (select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2016 as a left join vg_card_info as b on a.card_reading=b.id_outside where kiosk_time >='2016-12-01 00:00:00' and kiosk_time<='2016-12-31 23:59:59'
    -> union 
    -> select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-01-01 00:00:00' and upload_date<='2017-01-31 23:59:59'
    -> ) as t1;
+----------+
| COUNT(*) |
+----------+
|     1786 |
+----------+
1 row in set (2.18 sec)


mysql> select count(*) from (select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2016 as a left join vg_card_info as b on a.card_reading=b.id_outside where kiosk_time >='2016-12-01 00:00:00' and kiosk_time<='2016-12-31 23:59:59' union  select terminal_num,kiosk_initstatus,purchase_way,cash_recieved,card_preorder,kiosk_info,kiosk_time,upload_date,price,b.card_type from vg_terminal_logs_021_2017 as a left join vg_card_info as b on a.card_reading=b.id_outside where upload_date >='2017-01-01 00:00:00' and upload_date<='2017-01-31 23:59:59' ) as t1;
+----------+
| COUNT(*) |
+----------+
|     1786 |
+----------+
1 row in set (0.63 sec)

测试总结

测试前提，相同的配置，相同的数据，相同的索引，相同的测试语句

数据库	SQL1 最大耗时	SQL1 最小耗时	SQL2 最大耗时	SQL2 最小耗时
RDS	2.79 sec	2.77 sec	9.86 sec	9.68 sec
HybirdDB	1.55 sec	0.35sec	2.18 sec	0.63 sec

HybirdDB在本轮测试中占优。