mysql優化（sql優化）

SQL優化

分批處理

業務描述：更新使用者所有已過期的優惠券為不可用狀态。SQL語句：update status=0 FROMcoupon WHERE expire_date <= #{currentDate} and status=1;如果大量優惠券需要更新為不可用狀态，執行這條SQL可能會堵死其他SQL，分批處理僞代碼如下：

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
    if (CollectionUtils.isEmpty(batchIdList)) {
        return;
    }
    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
    pageNo ++;
}
           

操作符<>優化

通常<>操作符無法使用索引，舉例如下，查詢金額不為100元的訂單：select id from orders where amount != 100;如果金額為100的訂單極少，這種資料分布嚴重不均的情況下，有可能使用索引。鑒于這種不确定性，采用union聚合搜尋結果，改寫方法如下：

OR優化

在Innodb引擎下or無法使用組合索引，比如：

OR無法命中mobileno + userid的組合索引，可采用union，如下所示：

此時id和product_name字段都有索引，查詢才最高效。

IN優化

IN适合主表大子表小，EXIST适合主表小子表大。由于查詢優化器的不斷更新，很多場景這兩者性能差不多一樣了。

嘗試改為join查詢，舉例如下：

采用JOIN如下所示：

不做列運算

通常在查詢條件列運算會導緻索引失效，如下所示：查詢當日訂單

date_format函數會導緻這個查詢無法使用索引，改寫後：

避免Select all

如果不查詢表中所有的列，避免使用 SELECT *，它會進行全表掃描，不能有效利用索引。

Like優化

like用于模糊查詢，舉個例子（field已建立索引）：

這個查詢未命中索引，換成下面的寫法：

去除了前面的%查詢将會命中索引，但是産品經理一定要前後模糊比對呢？全文索引fulltext可以嘗試一下，但Elasticsearch才是終極武器。

Join優化

join的實作是采用Nested Loop Join算法，就是通過驅動表的結果集作為基礎資料，通過該結資料作為過濾條件到下一個表中循環查詢資料，然後合并結果。如果有多個join，則将前面的結果集作為循環資料，再次到後一個表中查詢資料。

驅動表和被驅動表盡可能增加查詢條件，滿足ON的條件而少用Where，用小結果集驅動大結果集。

被驅動表的join字段上加上索引，無法建立索引的時候，設定足夠的Join Buffer Size。

禁止join連接配接三個以上的表，嘗試增加備援字段。

Limit優化

limit用于分頁查詢時越往後翻性能越差，解決的原則：縮小掃描範圍 ，如下所示：

select * from orders order by id desc limit 100000,10 
           

耗時0.4秒

select * from orders order by id desc limit 1000000,10
           

耗時5.2秒

先篩選出ID縮小查詢範圍，寫法如下：

select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10
           

耗時0.5秒

如果查詢條件僅有主鍵ID，寫法如下：

select id from orders where id between 1000000 and 1000010 order by id desc
           

耗時0.3秒

如果以上方案依然很慢呢？隻好用遊标了，感興趣的朋友閱讀JDBC使用遊标實作分頁查詢的方法