一个阿里云 MYSQL 服务 RDS 下的查询优化

因MYSQL 现在业务量不大，使用也不多，为了便捷及安全考虑。就不再自己架构数据库了，直接放到的阿里的RDS 服务中。

但前几天开发的测试下来，总说RDS还不如自己测试库压测快。

查看了RDS下的数据，如下：

数据分布及其不平衡，查询一个用户的数据很多，其它却很少，

MySQL [GTUAN]> select MallID,UserId,count(*) from `order` group by MID,UserId order by count(*) desc;
+--------+---------+----------+
| MID | UserId  | count(*) |
+--------+---------+----------+
|  10008 | 3705695 |    49387 |
|  10008 | 5033586 |       30 |
|  10113 | 7345849 |       14 |
|  10008 | 5301964 |       10 |
|  10008 | 7868945 |       10 |
|  10068 | 7791370 |        9 |
|  10023 | 7103027 |        5 |
|  10113 | 7796603 |        4 |
|  10010 | 4068427 |        4 |

MySQL [tuan]> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                            |
+----------+------------+--------------------------------------------------------------------------------------------------+
|        1 | 0.00021225 | show profiles 6                                                                                  |
|        2 | 0.00020000 | show profiles for query 6                                                                        |
|        3 | 0.10450975 | select *from `order` where MallID=10008 and UserId=3705695 order by CreateTimestamp desc limit 5 |
|        4 | 0.00020725 | show profiles for query 6                                                                        |
|        5 | 0.00089350 | select *from `order` where MallID=10008 and UserId=3705695 order by ID desc limit 5              |
+----------+------------+--------------------------------------------------------------------------------------------------+
5 rows in set, 1 warning (0.01 sec)

MySQL [tuan]> show profile for query 3
    -> ;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000139 |
| checking permissions | 0.000031 |
| Opening tables       | 0.000051 |
| init                 | 0.000102 |
| System lock          | 0.000028 |
| optimizing           | 0.000040 |
| statistics           | 0.000100 |
| preparing            | 0.000037 |
| Sorting result       | 0.000027 |
| executing            | 0.000021 |
| Sending data         | 0.000038 |
| Creating sort index  | 0.103652 |
| end                  | 0.000069 |
| query end            | 0.000027 |
| closing tables       | 0.000033 |
| freeing items        | 0.000094 |
| cleaning up          | 0.000022 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)

MySQL [tuan]> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000137 |
| checking permissions | 0.000039 |
| Opening tables       | 0.000043 |
| init                 | 0.000074 |
| System lock          | 0.000027 |
| optimizing           | 0.000033 |
| statistics           | 0.000105 |
| preparing            | 0.000039 |
| Sorting result       | 0.000020 |
| executing            | 0.000020 |
| Sending data         | 0.000177 |
| end                  | 0.000023 |
| query end            | 0.000026 |
| closing tables       | 0.000028 |
| freeing items        | 0.000083 |
| cleaning up          | 0.000021 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

MySQL [tuan]>

同时看到使用的排序字段也有问题，使用 CreateTimestamp 排序性能与  ID 字段相比，直接差了100倍。所有的消耗都花在ORDER BY

这是因为MYSQL 的索引是BTREE，数据是按ID 大小进行排序的，如果你用ID 来排序，那就是物理顺序，而使用了  CreateTimestamp ，那就得在

内存中重新排序。

修改后，再压测试，达到他们的要求。