运行缓慢的sql查询

hsgswve4 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(375)

我有一个问题，这个sql查询mysql上运行了5秒，只取得25条记录相当糟糕；

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

所有这三个表都估计每个表有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    |       |
+--------------------------------+--------------+------+-----+---------+-------+

我已经索引表1（id，自定义类型，确认号）表2（交易id）表3（客户收据），没有任何重大改进。
如何将此查询的执行时间降低到100ms以下？

mysql query-optimization database-performance

来源：https://stackoverflow.com/questions/48250883/slow-running-sql-query

2条答案

按热度按时间

ffdz8vbo1#

以下是我将尝试的更改，按尝试顺序排列。

添加索引

首先，正如gordon linoff所建议的，添加以下索引：

ALTER TABLE table1
ADD INDEX (`custom_type`,`id`)

使列不为空

如果这不能充分提高性能，那么我会改变 table2.transaction_id 成为 NOT NULL ，如果您的业务规则允许的话。
原因是文档描述了如何执行您正在使用的反连接（在页面上搜索“not exists”）：
mysql能够对查询执行左连接优化，并且在找到一行与左连接条件匹配后，不会检查此表中的更多行以查找上一行组合。下面是一个可以通过这种方式优化的查询类型的示例：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;

假设t2.id定义为not null。在本例中，mysql扫描t1并使用t1.id的值查找t2中的行。如果mysql在t2中找到匹配的行，它知道t2.id永远不能为null，并且不会扫描t2中具有相同id值的其余行。换句话说，对于t1中的每一行，mysql只需要在t2中进行一次查找，而不管t2中实际匹配了多少行。
在您的查询中 t2.id 列是您的 table2.transaction_id 列，但这可能是 NULL . 如果可能，请尝试将其表定义更改为 NOT NULL 看看性能是否提高(如果由于其他原因必须使该列为null，那么显然该解决方案将不起作用。

添加缓存表

剩下的解决方案对我的工作很有效。我有一个查询，基本上可以找到可用的“项目”供用户挑选。有问题的用户倾向于积极地快速刷新调用此查询的页面以查找可用项。
我的查询最初的工作方式和你的相同。它需要一张主桌，像你的table table1 ，和 LEFT OUTER JOIN table2 ... WHERE table2. IS NULL 排除那些已经被人抢走的物品。
然而，由于两个表中的记录都没有被删除，所以当有50000个左右的“抓取”项时，这一速度开始减慢。基本上，mysql检查所有的条目，找到10-100个左右还没有抓取的条目花费了太长的时间。
解决方案是创建一个缓存表，其中只包含未检索的项。服务器端代码更新为每当有新项可用时插入两条记录，而不是一条记录。就你的情况来说，我们称之为 available_table1 .

CREATE TABLE available_table1 (
`id` INT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `Table1_AvailableTable1_fk`
FOREIGN KEY (`id`)
REFERENCES `table1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

使用原始查询填充此表一次，不受以下限制：

INSERT INTO available_table1
(`id`)
SELECT
t.id
FROM table1 t
left join table2 t2 on t.id=t2.transaction_id
where t2.transaction_id is null
and t.custom_type =0

现在您的查询变成：

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

您需要定期清理此表（我们每晚都清理），方法是删除 table2.transaction_id 现在对于给定的id存在。

DELETE at FROM available_table1 at
INNER JOIN table2 t2
ON t2.transaction_id = at.transaction_id

如果您的代码可以很容易地修改，您甚至可以删除 available_table 记录此时此刻 table2 记录已插入。但是，只要在 available_table1 table，你不必过于激进地清理它。
有了这个改变，我们的查询从一个让整个应用程序真正变慢的主要问题变成了一个在我们的慢日志中甚至不再显示的问题，这个慢日志被设置为只显示超过0.03秒的查询。

赞(0）回复(0）举报 2021-06-24

dsekswqp2#

这是您的问题：

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) .

赞(0）回复(0）举报 2021-06-24

我来回答

运行缓慢的sql查询

2条答案

添加索引

使列不为空

添加缓存表

相关问题

热门标签

最新问答