Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL query Sending data takes up a lot of time how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

The original SQL execution plan:

EXPLAIN

SELECT tm.id

Tm.to_no

Tm.source_website_id

Tm.warehouse_name

Tm.target_website_id

Tm.channel_name

Tm.sale_channel_name

Ti.product_basic_id

Ti.product_basic_no

Ti.product_basic_name

Ti.tax_rate

Ti.sale_tax_rate

Ti.quantity/ti.main_aux_ratio quantity

Ti.unit_cost * ti.main_aux_ratio unit_cost

Ti.unit_cost * IFNULL (ti.quantity,0) amount

Ti.received_qty

Tm.po_no

Tm.source_website_name

Tm.target_website_name

CreateUser.user_name create_user_name

AuditUser.user_name audit_user_name

OutUser.user_name out_user_name

DATE_FORMAT (tm.create_time,'% Y/%m/%d% HRV% iRO% s') create_time

DATE_FORMAT (tp.audit_time,'% Y/%m/%d% HRV% iRO% s') audit_time

DATE_FORMAT (tp.out_time,'% Y/%m/%d% HRV% iRO% s') out_time

Tm.`status`

DATE_FORMAT (tp.in_time,'% Y/%m/%d% HRV% iRO% s') receive_time

IFNULL (ti.return_qty/ti.main_aux_ratio, 0) return_qty

Ti.unit_cost * IFNULL (ti.received_qty,0) return_amount

DATE_FORMAT (td.production_date,'% Yamp% m _ production_date% d')

Td.location_name off_location_name FROM transfer_master AS tm

LEFT JOIN transfer_item AS ti ON tm.id = ti.to_id

LEFT JOIN transfer_detail td ON tm.id = td.transfer_id AND ti.product_basic_id = td.product_basic_id

LEFT JOIN transfer_operation tp ON tp.transfer_id = tm.id

LEFT JOIN sys_user createUser ON createUser.sysno = tm.create_user_id

LEFT JOIN sys_user auditUser ON auditUser.sysno = tp.audit_user_id

LEFT JOIN sys_user outUser ON outUser.sysno = tp.out_user_id WHERE 1 = 1 AND tm.source_website_id IN (3) AND tm.status = 110 AND tm.create_time > = '2019-04-01' AND tm.create_time

< '2019-10-01' ORDER BY tm.create_time DESC 以上SQL很多列没有用到索引。 1 queries executed, 1 success, 0 errors, 0 warnings 查询：SELECT tm.id, tm.to_no , tm.source_website_id , tm.warehouse_name , tm.target_website_id , tm.channel_name , tm.sale_channel_nam... 共 1000 行受到影响 执行耗时 : 1 min 10 sec 传送时间 : 0.016 sec 总耗时 : 1 min 10 sec Sending data花费时间最长。 "Sending data"状态的含义，原来这个状态的名称很具有误导性，所谓的"Sending data"并不是单纯的发送数据，而是包括"收集 + 发送 数据"。 这里的关键是为什么要收集数据，原因在于：mysql使用"索引"完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到"数据行"上将需要返回的数据读取出来返回个客户端。 对字段添加索引。 第一条索引：ALTER TABLE `transfer_detail` ADD INDEX idx_transfer_id (`transfer_id`); 第二条索引：ALTER TABLE `transfer_item` ADD INDEX idx_to_id (`to_id`); 第三条索引：ALTER TABLE `transfer_operation` ADD INDEX idx_transfer_id (`transfer_id`); 加第一条索引：ALTER TABLE `transfer_detail` ADD INDEX idx_transfer_id (`transfer_id`); 执行计划： 消耗时间：

Add a second index: ALTER TABLE `transfer_ item` ADD INDEX idx_to_id (`to_ id`)

Execute the plan:

Elapsed time:

Add the third index: ALTER TABLE `transfer_ operation` ADD INDEX idx_transfer_id (`transfer_ id`)

Execute the plan:

Elapsed time:

The optimization is complete.

The conditional field data of the tm table is unevenly distributed and indexing is not recommended.

After you add an index to a conditional field, the Sending data elapsed time decreases significantly.

Thank you for reading this article carefully. I hope the article "what to do if Sending data takes up a lot of time in MySQL query" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.