mysql - 运行缓慢的 SQL 查询

Question

我在 mysql 上运行 5 秒后仅获取 25 条记录的 SQL 查询出现问题 - 非常糟糕；

select t.* from table1 t
left join table2 t2 on t.id=t2.transaction_id
where t2.transaction_id is null
and t.custom_type =0 limit 25

所有 3 个表都估计有 1000 万条记录。

受影响表的结构；

table1 ;
+---------------------+--------------+------+-----+-------------------+----------------+
| Field               | Type         | Null | Key | Default           | Extra          |
+---------------------+--------------+------+-----+-------------------+----------------+
| id                  | int(11)      | NO   | PRI | NULL              | auto_increment |
| loan_application_id | int(11)      | YES  | MUL | NULL              |                |
| loan_repayment_id   | int(11)      | YES  | MUL | NULL              |                |
| person_id           | int(11)      | YES  | MUL | NULL              |                |
| direction           | tinyint(4)   | NO   |     | NULL              |                |
| amount              | float        | NO   |     | NULL              |                |
| sender_phone        | varchar(32)  | YES  | MUL | NULL              |                |
| recipient_phone     | varchar(32)  | YES  | MUL | NULL              |                |
| sender_name         | varchar(128) | YES  |     | NULL              |                |
| recipient_name      | varchar(128) | YES  |     | NULL              |                |
| date_time           | datetime     | NO   | MUL | NULL              |                |
| local_date_time     | datetime     | YES  |     | NULL              |                |
| payment_method      | varchar(128) | YES  |     | NULL              |                |
| project             | varchar(30)  | YES  | MUL | NULL              |                |
| confirmation_number | varchar(64)  | YES  | MUL | NULL              |                |
| reversal_of         | varchar(32)  | YES  |     | NULL              |                |
| custom_type         | int(11)      | YES  |     | 0                 |                |
| timestamp           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+---------------------+--------------+------+-----+-------------------+----------------+

table2;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| transaction_id      | int(11)     | YES  | MUL | NULL    |                |
| type                | int(11)     | NO   | MUL | NULL    |                |
| phone_number        | varchar(16) | NO   | MUL | NULL    |                |
| amount              | double      | NO   |     | NULL    |                |
| description         | text        | YES  |     | NULL    |                |
| person_id           | int(11)     | YES  | MUL | NULL    |                |
| loan_application_id | int(11)     | YES  | MUL | NULL    |                |
| repayment_id        | int(11)     | YES  |     | NULL    |                |
| date_time           | datetime    | YES  |     | NULL    |                |
| local_date_time     | datetime    | YES  |     | NULL    |                |
| last_modified_by    | varchar(32) | YES  |     | NULL    |                |
| last_modified       | timestamp   | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+

table3;
+--------------------------------+--------------+------+-----+---------+-------+
| Field                          | Type         | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| id                             | int(11)      | NO   | PRI | NULL    |       |
| transaction_type_id            | int(11)      | NO   | MUL | NULL    |       |
| msisdn                         | varchar(32)  | NO   | MUL | NULL    |       |
| amount                         | float        | NO   |     | NULL    |       |
| mobile_money_provider_id       | int(11)      | YES  |     | NULL    |       |
| mobile_money_provider_code     | varchar(32)  | YES  |     | NULL    |       |
| source_external_id             | varchar(128) | YES  |     | NULL    |       |
| source_user_id                 | int(11)      | YES  |     | NULL    |       |
| payment_server_trx_id          | varchar(64)  | YES  | MUL | NULL    |       |
| customer_receipt               | varchar(64)  | YES  | MUL | NULL    |       |
| transaction_account_ref_number | varchar(64)  | YES  |     | NULL    |       |
| status                         | int(11)      | YES  |     | NULL    |       |
| mno_status                     | int(11)      | YES  |     | NULL    |       |
| mno_status_desc                | text         | YES  |     | NULL    |       |
| mno_transaction_id             | varchar(64)  | YES  |     | NULL    |       |
| date_completed                 | timestamp    | YES  |     | NULL    |       |
| date_acknowledged              | timestamp    | YES  |     | NULL    |       |
| created_at                     | timestamp    | YES  |     | NULL    |       |
| updated_at                     | timestamp    | YES  |     | NULL    |       |
| project                        | varchar(32)  | NO   |     | NULL    |       |
| loan_application_id            | int(11)      | YES  | MUL | NULL    |       |
+--------------------------------+--------------+------+-----+---------+-------+

我已经为 table1(id,custom_type,confirmation_number) table2(transaction_id) table3(customer_receipt) 建立了索引，但没有任何重大改进。

如何将此查询的执行时间降低到 100 毫秒以下？

Answer 1

这是您的查询:

select t.*
from table1 t left join
     table2 t2
     on t.id = t2.transaction_id left join
     table3 t3
     on t3.customer_receipt = confirmation_number
where t2.transaction_id is null and t.custom_type = 0
limit 25;

首先，您似乎不需要 table3，所以让我们删除它:

select t.*
from table1 t left join
     table2 t2
     on t.id = t2.transaction_id 
where t2.transaction_id is null and t.custom_type = 0
limit 25;

对于此查询，您需要在 table1(custom_type, id) 和 table2(transaction_id) 上建立索引。