经验 阿里云RDS数据库mysql版性能简单测试

创建数据表：
CREATE TABLE `user_scene_include_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`uid` bigint(20) NOT NULL COMMENT '场景所有者的uid',
`scene_id` bigint(20) NOT NULL COMMENT '场景id',
`country` varchar(50) NOT NULL DEFAULT '',
`city` varchar(50) NOT NULL DEFAULT '',
`address` varchar(100) NOT NULL DEFAULT '',
`landmark` varchar(50) NOT NULL DEFAULT '' COMMENT '地标名称',
`included_uid` bigint(20) NOT NULL COMMENT '被包含者的uid',
`longitude` bigint(20) NOT NULL DEFAULT '0' COMMENT '被包含者的场景经度乘10个零取整',
`latitude` bigint(20) NOT NULL DEFAULT '0' COMMENT '被包含者的场景纬度乘10个零取整',
`last_time` bigint(13) NOT NULL DEFAULT '0' COMMENT '在该场景内最后一次遇见的时间',
`distance` int(10) NOT NULL COMMENT '记录最近的距离，单位分米',
`ctime` int(10) NOT NULL,
`mtime` timestamp NOT NULL DEFAULT '1970-10-10 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `scene_user` (`scene_id`,`included_uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COMMENT='测试数据表';

第一阶段后有再加一个索引，见后面的修改代码。

前面的500万数据是自己写的存储过程生成的，后台的数据是使用阿里云数据库自带的功能生成的（一次最多只能生成100万）
CREATE PROCEDURE `includeUserSeed`()
    COMMENT '给user_scene_include_user随机生成数据'
BEGIN
    DECLARE i INT;
    SET i=0;
    WHILE i<5000000 DO
        INSERT INTO user_scene_include_user (`uid`,`scene_id`,`country`,`city`,`address`,`landmark`,`included_uid`,`longitude`,`latitude`,`last_time`,`distance`,`ctime`,`mtime`)
        VALUES(ROUND(rand()*100000000), ROUND(rand()*10000000000), substring(MD5(RAND()),1,15), substring(MD5(RAND()),1,20), substring(MD5(RAND()),1,32),
        substring(MD5(RAND()),1,25), ROUND(rand()*100000000), RPAD(ROUND(rand()*10000000),8,0), RPAD(ROUND(rand()*10000000),7,0),
        RPAD(ROUND(rand()*10000000),10,0), RPAD(ROUND(rand()*10000),4,0), RPAD(ROUND(rand()*10000000),9,0), '2018-05-17 11:51:30');
    SET i=i+1;
    END WHILE;
END

以下都是在主实例中操作的，通过内网的一台服务器远程连接数据库来操作。服务器和数据库都是购买的阿里云-美国西部 (硅谷)地区的。


第一阶段：

配置：
双机高可用版（主实例）：1核1G SSD硬盘5G大小
只读实例：1核1G SSD硬盘5G大小
最大连接数:300  最大IOPS:600

单表5,000,000（500万）条数据
停止写数据后查询总记录数花费时间：好像是2秒多，忘记做记录了
SELECT COUNT(1) FROM table_name;

添加了索引
ALTER TABLE `table_name`
ADD INDEX `met_user` (`uid`, `included_uid`, `last_time`) USING HASH;

以下查询SQL，如果有索引则是0.00秒完成，没有就会很慢
SELECT uid,included_uid,last_time FROM user_scene_include_user WHERE scene_id=9615253871;

SELECT * FROM user_scene_include_user WHERE uid=52467843  AND included_uid=50426508  AND last_time<93000000022  ORDER BY last_time DESC LIMIT 0, 10;

没有做其它不使用索引的查询测试


第二阶段
配置：
双机高可用版（主实例）：1核1G SSD硬盘5G大小
只读实例：1核1G SSD硬盘5G大小
最大连接数:300  最大IOPS:600

单表7,200,000（720万）条数据，在停止写数据时查询总记录数花费时间：28.90 sec
SELECT COUNT(1) FROM table_name;

执行第一阶段的查询SQL，因为使用到了索引都是0.00秒完成

没有做不使用索引的查询测试



第三阶段
配置：
双机高可用版（主实例）：1核1G SSD硬盘5G大小
只读实例：1核1G SSD硬盘5G大小
最大连接数:300  最大IOPS:600

单表10,114,167（1011万）条数据，在生成随机数据时读取总记录数花费时间：3 hours 48 min 5.89 sec
SELECT COUNT(1) FROM table_name;

停止写数据后读取总记录数花费时间：48.81 sec
SELECT COUNT(1) FROM table_name;

这条语句有出现0.13秒的查询，再查询时0.00秒
SELECT * FROM user_scene_include_user WHERE uid=52467843  AND included_uid=50426508  AND last_time<93000000022  ORDER BY last_time DESC LIMIT 0, 10;

这条语句有出现0.09秒的查询
SELECT uid,included_uid,last_time FROM user_scene_include_user WHERE scene_id=9615253871;

总用磁盘大小：（单位MByte）
已占用4G左右的磁盘空间




第四阶段
配置（升级了配置）：
双机高可用版（主实例）：1核2G SSD硬盘20G大小
只读实例：1核1G SSD硬盘20G大小
最大连接数:600  最大IOPS:1000

还是上一阶段的数据量，连接执行3次总记录数的查询，分别是：29.30 sec、2.09 sec、2.05 sec，过了几十分钟没有增加新数据，再执行：2.14 sec
SELECT COUNT(1) FROM user_scene_include_user;

mysql> SELECT uid,included_uid,last_time FROM user_scene_include_user WHERE scene_id=9615253000;
Empty set (0.00 sec)

mysql> SELECT uid,included_uid,last_time FROM user_scene_include_user WHERE scene_id=9615253871;
---为了阅读方便，查询结果数据不输出---
3 rows in set (0.00 sec)

mysql> SELECT * FROM user_scene_include_user WHERE uid=52467843  AND included_uid=50426508  AND last_time<93000000022  ORDER BY last_time DESC LIMIT 0, 10;
---为了阅读方便，查询结果数据不输出---
1 row in set (0.00 sec)

mysql> SELECT * FROM user_scene_include_user WHERE uid=52467843  AND included_uid=50426508  AND last_time<93000000033  ORDER BY last_time DESC LIMIT 0, 10;
---为了阅读方便，查询结果数据不输出---
1 row in set (0.00 sec)


第五阶段
配置（升级了配置）：
双机高可用版（主实例）：1核2G SSD硬盘20G大小
只读实例：1核1G SSD硬盘20G大小
最大连接数:600  最大IOPS:1000

这一次我在查询总记录数时，执行储存过程添加新数据，看查询时间是多少？
1、每添加1条数据停0.2秒，查询速度挺快的，算正常。
CREATE PROCEDURE `includeUserSeed`()
    COMMENT '给user_scene_include_user随机生成数据'
BEGIN
    DECLARE i INT;
    SET i=0;
    WHILE i<180 DO
        INSERT INTO user_scene_include_user (`uid`,`scene_id`,`country`,`city`,`address`,`landmark`,`included_uid`,`longitude`,`latitude`,`last_time`,`distance`,`ctime`,`mtime`)
        VALUES(ROUND(rand()*100000000), ROUND(rand()*10000000000), substring(MD5(RAND()),1,15), substring(MD5(RAND()),1,20), substring(MD5(RAND()),1,32),
        substring(MD5(RAND()),1,25), ROUND(rand()*100000000), RPAD(ROUND(rand()*10000000),8,0), RPAD(ROUND(rand()*10000000),7,0),
        RPAD(ROUND(rand()*10000000),10,0), RPAD(ROUND(rand()*10000),4,0), RPAD(ROUND(rand()*10000000),9,0), '2018-05-17 11:51:30');
        SET i=i+1;
　　 SELECT sleep(0.2);
    END WHILE;
END

mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 10114182 |
+----------+
1 row in set (2.11 sec)

mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 10114182 |
+----------+
1 row in set (3.35 sec)

mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 10114197 |
+----------+
1 row in set (2.06 sec)

2、在执行查询总记录SQL之后，未完成之前，执行储存过程添加新数据180条（连续执行，1.354ms就执行完了），查询时间非常长：23.10 sec
mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 10114197 |
+----------+
1 row in set (23.10 sec)


测试未使用索引的查询
mysql> SELECT count(1) FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC ;
+----------+
| count(1) |
+----------+
|   511933 |
+----------+
1 row in set (25.43 sec)

mysql> SELECT * FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC limit 451256, 10;
---为了阅读方便，查询结果数据不输出---
10 rows in set (36.96 sec)

再查询一次
mysql> SELECT * FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC limit 451256, 10;
---为了阅读方便，查询结果数据不输出---
10 rows in set (18.38 sec)

缩小页码距离查询快了很多
mysql> SELECT * FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC limit 45, 10;
---为了阅读方便，查询结果数据不输出---
10 rows in set (5.96 sec)

再放大页码距离查询还是很快
mysql> SELECT * FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC limit 45, 10;
10 rows in set (4.99 sec)


第六阶段
配置（升级了配置）：
双机高可用版（主实例）：1核2G SSD硬盘20G大小
只读实例：1核1G SSD硬盘20G大小
最大连接数:600  最大IOPS:1000

总记录数
mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 21314377 |
+----------+
1 row in set (4.51 sec)

mysql> SELECT COUNT(1) FROM user_scene_include_user;
+----------+
| COUNT(1) |
+----------+
| 21314377 |
+----------+
1 row in set (4.54 sec)


使用索引查询
mysql> SELECT * FROM user_scene_include_user WHERE uid=52467843  AND included_uid=50426508  AND last_time<93000000022  ORDER BY last_time DESC LIMIT 0, 10;
---为了阅读方便，查询结果数据不输出---
1 row in set (0.00 sec)

使用索引带条件查记录数
mysql> SELECT COUNT(1) FROM user_scene_include_user where scene_id=12322;
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

不使用索引查询，很慢，2分钟多
mysql> SELECT * FROM user_scene_include_user WHERE `distance` < 500 ORDER BY `distance` ASC limit 45345, 10;
---为了阅读方便，查询结果数据不输出---
10 rows in set (2 min 17.61 sec)