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

My colleagues in the operation group recently put forward a request to count the users and orders of the system, so we naturally wrote a statistical SQL, user table user and itinerary direct join, and made a group for the itinerary, but the execution speed of SQL was surprisingly slow.

Explain select users.`mobile _ num`, concat (users.`lastName`, users.`firstName`) as userName, users.`company`, (case `users`.`idPhotoCheckStatus`when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `idPhotoCheckStatus`, (case `users`.`driverLicenseCheckStatus` when' 2' then 'authenticated' when'3' then 'rejected' else 'pending certification' end) as `driverLicenseCheckStatus` (case `users`.`companyCheckStatus` when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `companyCheckStatus`, (case `users`.`unionCheckStatus` when' 2' then 'authenticated' else'3' then 'rejected' else'to be authenticated 'end) as `unionCheckStatus` Count (passenger_trip.id) as ptrip_numfrom usersleft join passenger_trip on passenger_trip.userId = users.id and passenger_trip.status! = 'cancel'left join driver_trip on driver_ trip.`userId` = users.`id`and driver_ trip.`status`! =' cancel'where company! = 'company name' and company! = 'company nickname'

The first reaction at that time was that the database hung up, because the data of the user table was about 10W, and the data of the itinerary was about 10W, which could not be so slow. Looking at the analysis plan through explain, and looking at the index of the associated fields, it is found that this is the most common association query, which is, of course, implemented through join.

Think about it, 10W*10W, after the Cartesian set, isn't this a ten-billion-level data filter?! So I tried to write it in a different way.

Explain select users.`mobile _ num`, concat (users.`lastName`, users.`firstName`) as userName, users.`company`, (case `users`.`idPhotoCheckStatus`when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `idPhotoCheckStatus`, (case `users`.`driverLicenseCheckStatus` when' 2' then 'authenticated' when'3' then 'rejected' else 'pending certification' end) as `driverLicenseCheckStatus` (case `users`.`companyCheckStatus` when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `companyCheckStatus`, (case `users`.`unionCheckStatus` when' 2' then 'authenticated' else'3' then 'rejected' else'to be authenticated 'end) as `unionCheckStatus`, (select count (passenger_trip.id) from passenger_trip where passenger_trip.userId = users.id and passenger_trip.status! =' cancel') as ptrip_num (select count (driver_trip.id) from driver_trip where driver_trip.userId = users.id and driver_trip.status! = 'cancel') as dtrip_numfrom userswhere company! =' company name 'and company! =' company nickname'

This effect is N times faster than direct join, and the execution speed is never known to return within 10 seconds. Check the execution plan:

Further adjust the SQL to try:

Explain select users.`mobile _ num`, concat (users.`lastName`, users.`firstName`) as userName, users.`company`, (case `users`.`idPhotoCheckStatus`when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `idPhotoCheckStatus`, (case `users`.`driverLicenseCheckStatus` when' 2' then 'authenticated' when'3' then 'rejected' else 'pending certification' end) as `driverLicenseCheckStatus` (case `users`.`companyCheckStatus` when'2' then 'authenticated' when'3' then 'rejected' else'to be authenticated 'end) as `companyCheckStatus`, (case `users`.`unionCheckStatus` when' 2' then 'authenticated' else'3' then 'rejected' else'to be authenticated 'end) as `unionCheckStatus`, ptrip_num, dtrip_numfrom users left join (select count (CheckStatus`)) Passenger_ trip.`userId` from passenger_trip where passenger_trip.status! = 'cancel' group by passenger_ trip.`userId`) as ptrip on ptrip.userId = users.id left join (select count (driver_trip.id) as dtrip_num, driver_ trip.`userId` from driver_trip where driver_trip.status! =' cancel' group by driver_ trip.`userId`) as dtrip on dtrip.userId = users.idwhere company! = 'company name' and company! = 'company nickname'

Unexpectedly return within 5 seconds, this is the normal expectation, 10W level of data filtering, should be returned in a few seconds!

The reason for this difference is simply that SQL statements are executed in a certain order.

From first selects a table to form a result set. Where filters the result set and filters out the information needed to form a new result set. Group by groups the new result set. Having filters out the desired grouping. Select selects the column. Order by, when all the conditions are done. Finally, sort it.

The first way of writing, the result of direct join, is to sift through 10 billion pieces of data.

The latter two are priority to execute the subquery, complete the 10W-level query, and then carry out the main table 10W-level associated query, so the order of magnitude is obviously less than the first method.

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.