Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The lack of indexing of the associated fields in the Mysql table leads to slow query, and the query efficiency is significantly improved after optimization.

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

I received feedback from users today that the front-end page opened very slowly. The database server load is also alarmed.

Login server query Mysql takes up too much CPU. Open show full process directly and find that many of the following sql are above 10s.

# User@Host: gyw [gwy] @ [x.x.x.x] Id: 19513

# Query_time: 11.326904 Lock_time: 0.000327 Rows_sent: 69 Rows_examined: 1417696

SET timestamp=1504507662

SELECT odet.seller AS sellerId

Odet.agreementprice_id AS agreementpriceId

Odet.customer_id AS customerId

(SELECT realname

FROM sys_user suser

WHERE suser.id = odet.seller)

AS sellerName

Odet.pkgticket_id AS pkgId

Odet.pkgticket_price AS pkgPrice

DATE_FORMAT (odet.sell_time,'% Ymuri% mmury% d') AS sellTime

Sum (oct.tourist_number-IFNULL (ort.tourist_remain,0)) as totalPeople

Sum ((oct.tourist_number-IFNULL (ort.tourist_remain,0)) * odet.pkgticket_price) as totalMoney

(SELECT name

FROM scenic_pkgticket spkg

WHERE spkg.id = odet.pkgticket_id)

AS pkgticketName

FROM order_detail odet,order_checkticket oct

LEFT JOIN order_refundticket ort

On oct.id = ort.id

WHERE odet.id=oct.order_detail_id

And odet.scenic_id = 215

And odet.sell_time > = '2017-09-04 00 00'

And odet.sell_time odet.agreementprice_id AS agreementpriceId

-> odet.customer_id AS customerId

-> (SELECT realname FROM sys_user suser WHERE suser.id = odet.seller) AS sellerName

-> odet.pkgticket_id AS pkgId

-> odet.pkgticket_price AS pkgPrice

-> DATE_FORMAT (odet.sell_time,'% Ymuri% mmi% d') AS sellTime

-> sum (oct.tourist_number-IFNULL (ort.tourist_remain, 0)) as totalPeople

-> sum ((oct.tourist_number-IFNULL (ort.tourist_remain, 0)) *

-> odet.pkgticket_price) as totalMoney

-> (SELECT name

-> FROM scenic_pkgticket spkg

-> WHERE spkg.id = odet.pkgticket_id) AS pkgticketName

-> FROM order_detail odet, order_checkticket oct

-> LEFT JOIN order_refundticket ort

-> on oct.id = ort.id

-> WHERE odet.id = oct.order_detail_id

-> and odet.scenic_id = 215

-> and odet.sell_time > = '2017-09-04 00 purl 0000'

-> and odet.sell_time GROUP BY sellerId, sellTime, pkgId, pkgPrice WITH ROLLUP

+ -- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ -- +

| | 1 | PRIMARY | oct | ALL | NULL | NULL | NULL | NULL | 414589 | Using temporary; Using filesort |

| | 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |

| | 1 | PRIMARY | odet | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.order_detail_id | 1 | Using where |

| | 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |

| | 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |

+ -- +

Try to add a composite index (scenic_id,sell_time) on order_detail, but still do not leave the index, check carefully and find that order_checkticket order_detail_id does not have an index. After adding the index, the execution plan is as follows

+-+- -+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+- -+

| | 1 | PRIMARY | odet | range | PRIMARY,idx_od_si_stime | idx_od_si_stime | 14 | NULL | 183 | Using index condition; Using temporary; Using filesort |

| | 1 | PRIMARY | oct | ref | idx_oct_odi | idx_oct_odi | 8 | sd_ets.odet.id | 1 | NULL |

| | 1 | PRIMARY | ort | eq_ref | PRIMARY | PRIMARY | 8 | sd_ets.oct.id | 1 | NULL |

| | 3 | DEPENDENT SUBQUERY | spkg | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |

| | 2 | DEPENDENT SUBQUERY | suser | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | NULL |

+-+- -+

5 rows in set (0.00 sec)

We see that key has gone. Indexing uses idx_od_si_stime.

The query speed is only 0.01 milliseconds. Increase the speed by a thousand times

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.

Views: 231

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report