测试环境:
PolarDB-X 标准版:16C64G(1台)
RDS MySQL5.7: 4C8G(4台)
ECS 压测机: 8C16G(1台)
RDS测试表 单表1亿,PolarDB-X +RDS x4分库分表,单表1亿
直连RDS:
MySQL [sysbenchtest]> show create table sbtest1;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `xid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [sysbenchtest]> alter table sbtest1 add index idx_k(k);
Query OK, 0 rows affected (7 min 15.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [sysbenchtest]> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `xid` (`id`),
KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
直连RDS, 单表1亿,添加单列索引,耗时7min55s。
查看CPU消耗:
![](https://i-blog.csdnimg.cn/blog_migrate/52035563673771b482e3805fc4a45146.png)
查看IO消耗:
![](https://i-blog.csdnimg.cn/blog_migrate/5fc583d875b2c619528182b8088ef038.png)
PolarDB-X +RDS x4分库分表:
MySQL [polardb2]> show create table sbtest1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT BY GROUP,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `id` (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 99999875 DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
MySQL [polardb2]> alter table sbtest1 add index idx_k(k);
Query OK, 0 rows affected (1 min 34.52 sec)
MySQL [polardb2]> show create table sbtest1;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT BY GROUP,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `id` (`id`),
KEY `idx_k` (`k`)
) ENGINE = InnoDB AUTO_INCREMENT = 99999875 DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 4 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
PolarDB-X +RDS x4分库分表只用了1min34s就执行完毕。
查看CPU消耗:
![](https://i-blog.csdnimg.cn/blog_migrate/bef78a5503871cf415d1d358432d2384.png)
查看IO消耗:
![](https://i-blog.csdnimg.cn/blog_migrate/9c3840d835b1067c74e848b760721c82.png)
总结:
对于添加索引这样操作,相对于RDS,PolarDB-X +RDS执行过程中CPU和IO消耗都略低,而且因为数据分散在不同RDS节点,耗时也较短。