Desde que atualizei do MySQL 5.6 para 5.7, comecei a ter consultas muito lentas na minha instância do Aurora RDS. No meu XAMPP local, as mesmas consultas demoravam alguns segundos, mas no RDS algumas delas demoravam cerca de 6 minutos. Tentando oferecer uma solução rápida para meu cliente, migrei o banco de dados do Aurora RDS para uma instância normal do MariaDB RDS (provavelmente um exagero, mas precisava fazer algo rápido). Após a migração os tempos de consulta foram semelhantes ao ambiente local, tempos aceitáveis, mas já se passaram três semanas desde que a migração e as consultas estão muito lentas novamente, cerca de 6 minutos. CPU sobe para quase 50% e há apenas uma conexão no banco de dados, aquela que faz a consulta lenta. Verifiquei e modifiquei parâmetros no RDS, especialmente o otimizador_switch, mas eles não tiveram efeito visível na velocidade da consulta. Eu também otimizei tabelas sem melhorias. Eu estava pensando em uma tabela de log ou similar que poderia estar crescendo muito depois de três semanas e, portanto, retardando minhas consultas, mas é apenas uma ideia. Neste momento estou um pouco perdido. As tabelas nessas consultas lentas têm entre 63 e mais de 200 mil linhas.
A consulta mais lenta é esta:
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)
Aqui está a explicação para a consulta:
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
| 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)
Índices para bicicletas de mesa:
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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)
Índices para empréstimos de mesa:
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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)
Estou usando uma instância db.r3.large, 2vCPUs, 15,25 GiB de RAM, não otimizada para EBS. A versão do MySQL é 5.7.34 com MariaDB. O armazenamento é um SSD provisionado por IOPS (io1), armazenamento atribuído de 100 GiB, IOPS de provisionamento de 5.000.
Conforme solicitação do usuário, adiciono as próximas informações das tabelas:
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
Este é o EXPLAIN para a frase depois de adicionar o ÍNDICE sugerido:
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
| 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)