自从我从 MySQL 5.6 更新到 5.7 后，我的 Aurora RDS 实例中的查询开始变得非常缓慢。在我本地的 XAMPP 中，相同的查询需要几秒钟，但在 RDS 中，其中一些查询大约需要 6 分钟。为了为我的客户提供一个快速的解决方案，我将数据库从 Aurora RDS 迁移到了一个普通的 MariaDB RDS 实例（可能有点矫枉过正，但我需要快速做一些事情）。迁移后查询时间和本地环境差不多，可以接受，但是迁移到现在已经三周了，查询又很慢，大概6分钟左右。CPU 上升到几乎 50%，并且 DB 中只有一个连接，一个执行慢速查询的连接。我检查并修改了 RDS 中的参数，特别是优化器开关，但它们对查询速度没有明显影响。我还优化了没有改进的表。我正在考虑一个日志表或类似的表，它可能会在三周后增长太多，因此会减慢我的查询速度，但这只是一个想法。在这一点上，我有点失落。这些慢查询中的表在 63 到 200K+ 行之间。
最慢的查询是这样的：
SELECT *
FROM bicycles AS Bicycle
LEFT JOIN loans AS Loan ON Loan.uuid = (
SELECT
loans.uuid
FROM loans
WHERE loans.bicycle_uuid = Bicycle.uuid
ORDER BY loans.date_created DESC
LIMIT 1
)
WHERE Bicycle.status = 'SCRAPPED'
AND Bicycle.number <= 9900
ORDER BY Loan.date_created DESC, Bicycle.number DESC
961 rows in set (6 min 23.28 sec)
这是查询的解释：
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
| 1 | PRIMARY | Bicycle | NULL | ALL | number | NULL | NULL | NULL | 2082 | 9.99 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | Loan | NULL | eq_ref | PRIMARY | PRIMARY | 16 | func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | loans | NULL | index | bicycle_uuid | date_created | 5 | NULL | 428 | 10.00 | Using where |
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)
桌上自行车的索引：
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bicycles | 0 | PRIMARY | 1 | uuid | A | 2082 | NULL | NULL | | BTREE | | |
| bicycles | 0 | number | 1 | number | A | 2082 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
表贷款指数：
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| loans | 0 | PRIMARY | 1 | uuid | A | 219864 | NULL | NULL | | BTREE | | |
| loans | 1 | anchor_uuid | 1 | anchor_uuid | A | 1047 | NULL | NULL | | BTREE | | |
| loans | 1 | use_uuid | 1 | use_uuid | A | 4076 | NULL | NULL | YES | BTREE | | |
| loans | 1 | date_created | 1 | date_created | A | 213634 | NULL | NULL | | BTREE | | |
| loans | 1 | bicycle_uuid | 1 | bicycle_uuid | A | 428 | NULL | NULL | | BTREE | | |
| loans | 1 | date_created_bicycle_uuid | 1 | date_created | A | 212288 | NULL | NULL | | BTREE | | |
| loans | 1 | date_created_bicycle_uuid | 2 | bicycle_uuid | A | 218719 | NULL | NULL | | BTREE | | |
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)
我正在使用 db.r3.large 实例、2vCPU、15.25 GiB RAM，未经过 EBS 优化。MySQL 版本是 5.7.34，带有 MariaDB。存储是 IOPS 预置 SSD (io1)，100GiB 分配存储，5000 预置 IOPS。
根据用户请求，我添加下一个表格信息：
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bicycles | CREATE TABLE `bicycles` (
`uuid` binary(16) NOT NULL,
`number` int(14) NOT NULL,
`date_created` datetime NOT NULL,
`status` varchar(20) COLLATE utf8_spanish_ci NOT NULL DEFAULT 'CIRCULATION',
`is_electric` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`uuid`),
UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| bicycles | InnoDB | 10 | Dynamic | 2082 | 78 | 163840 | 0 | 81920 | 0 | NULL | 2022-01-28 12:13:25 | 2022-01-28 12:13:25 | NULL | utf8_spanish_ci | NULL | | | 0 | N |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| loans | CREATE TABLE `loans` (
`uuid` binary(16) NOT NULL,
`use_uuid` binary(16) DEFAULT NULL,
`bicycle_uuid` binary(16) NOT NULL,
`anchor_uuid` binary(16) NOT NULL,
`date_created` datetime NOT NULL,
`type_access` enum('UNKNOWN','CARD','APP','SYSTEM','WORKER') COLLATE utf8_spanish_ci NOT NULL,
PRIMARY KEY (`uuid`),
KEY `anchor_uuid` (`anchor_uuid`),
KEY `use_uuid` (`use_uuid`),
KEY `date_created` (`date_created`),
KEY `bicycle_uuid` (`bicycle_uuid`),
KEY `date_created_bicycle_uuid` (`date_created`,`bicycle_uuid`),
CONSTRAINT `fk_loans_anchors` FOREIGN KEY (`anchor_uuid`) REFERENCES `anchors` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_loans_bicycles` FOREIGN KEY (`bicycle_uuid`) REFERENCES `bicycles` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_loans_users` FOREIGN KEY (`use_uuid`) REFERENCES `users` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| loans | InnoDB | 10 | Dynamic | 216920 | 99 | 21561344 | 0 | 75284480 | 7340032 | NULL | 2022-01-28 12:37:06 | 2022-01-28 12:37:22 | NULL | utf8_spanish_ci | NULL | | | 0 | N |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)
这是添加建议索引后句子的解释：
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
| 1 | PRIMARY | Bicycle | NULL | range | number,bicycles_ndx_status_number | bicycles_ndx_status_number | 66 | NULL | 961 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | Loan | NULL | eq_ref | PRIMARY | PRIMARY | 16 | func | 1 | 100.00 | Using where |
| 1 | PRIMARY | LoanAnchor | NULL | eq_ref | PRIMARY | PRIMARY | 16 | bicicas.Loan.anchor_uuid | 1 | 100.00 | NULL |
| 1 | PRIMARY | LoanBench | NULL | eq_ref | PRIMARY | PRIMARY | 16 | bicicas.LoanAnchor.bench_uuid | 1 | 100.00 | NULL |
| 1 | PRIMARY | User | NULL | eq_ref | PRIMARY | PRIMARY | 16 | func | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | users | NULL | ref | uuid | uuid | 16 | bicicas.Loan.use_uuid | 2 | 100.00 | Using index condition; Using filesort |
| 2 | DEPENDENT SUBQUERY | loans | NULL | ref | bicycle_uuid | bicycle_uuid | 16 | bicicas.Bicycle.uuid | 452 | 100.00 | Using index condition; Using filesort |
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
7 rows in set, 3 warnings (0.00 sec)