That year that month that period of time, do query accumulation of some small skills, record to share;

  • Limit paging optimization

If there is a large amount of data and a large number of pages, the larger the limit offset, the slower the query will be.

The reason is that the larger the offset is, the more rows will be scanned. For example, limit 100000, 10 means that only the last 10 records will be scanned. The larger the offset is, the more rows will be scanned, and the slower it will be.

It is faster to change the associated subquery to a single table instead of a multi-table filter limit. It will be faster than before.

The following SQL.

The original SQL:

SELECT auc.CITY_ID,auc.CITY_NAME,fa.CREATE_TIME FROM BOSS_FINANCE_APPLY fa INNER JOIN BOSS_FINANCE_AUCTION auc ON Fa.auction_id =auc.AUCTION_ID WHERE fa.APPLY_BACK_STATUS=0 AND fa.CITY_ID =2 ORDER BY APPLY_STATUS,ID DESC LIMIT 800,20;Copy the code

The optimized SQL:

SELECT auc.CITY_ID,auc.CITY_NAME,fa.CREATE_TIME FROM BOSS_FINANCE_APPLY fa INNER JOIN BOSS_FINANCE_AUCTION auc ON fa.AUCTION_ID=auc.AUCTION_ID INNER JOIN (SELECT ID FROM BOSS_FINANCE_APPLY WHERE APPLY_BACK_STATUS=0 and CITY_ID =2 ORDER BY APPLY_STATUS,ID DESC LIMIT 0, 800,20) TFA = tfa. ID;Copy the code

The query time after optimization will shorten the original dozens of times to hundreds of times.

  • If limit 1 is added to the index, the SQL statement does not use the optimized index.

Mysql > limit index (); mysql > limit index (); mysql > limit index ();

Mandatory indexes are a simple solution to these problems.

The following SQL:

SELECT a.ID, A. UCTION_ID FROM BOSS_FINANCE_APPLY a FORCE INDEX(IDX_STATUS_TYPE) // INNER JOIN BOSS_FINANCE_AUCTION b ON a.AUCTION_ID = b.AUCTION_ID AND a.APPLY_BACK_STATUS = 0 AND a.ALLOCATION_TYPE = 0 AND a.APPLY_STATUS < 2 WHERE NOT EXISTS ( SELECT ( 1 ) FROM BOSS_FINANCE_CHANNEL_NO_TASK c WHERE c.CHANNEL_ID = b.SOURCE_ID OR c.CHANNEL_ID = b.CHANNEL_ID ) ORDER BY a.ID limit 1;Copy the code

The IDX_STATUS_TYPE index is a combination of APPLY_STATUS and ALLOCATION_TYPE.