Mysql-5.7
幾週後 RDS 查詢緩慢
自從我從 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)
要考慮減少查詢完成所需時間的建議/觀察。
CREATE INDEX bicycles_ndx_status_number ON bicycles (status, number);測試您的查詢以查看是否提供了相同的結果但速度更快。
EXPLAIN SELECT (your query) and observe ROWS column value. Posted EXPLAIN had ROWS at 2,082. The index should reduce the ROWS number significantly, saving time.觀察，AND Bicycle.number <= 9900 可以是 AND Bicycle.number 介於 0 和 9899 之間，可以減少所需時間。
觀察 2 單數與復數表名限定符。當兩者都使用時讓我感到困惑，但可能最適合你。
觀察 3 任何查詢緩慢時的第一道防線 ANALYZE TABLE table_name;
每個表以確保索引是目前的（它們可能以某種方式損壞）。這應該是糾正任何 SLOW 查詢的第一次嘗試。
對您沒有多大幫助，因為您需要額外的 BICYCLES 多列索引以獲得高性能。