1.案例in查詢條件很慢

其中in中共115個

select 
id,detail_id,request,response,utime,ctime 
from response_detaill 
where detaill_id in
(26371986, 26372242, 26371984, 26371990, 26400150, 26371988, 26371994, 26371992,26371998, 26371996, 26371970, 26371968, 26371974, 26400390, 26371972, 26371978,26371976, 26371982, 26371980, 26400178, 26372018, 26400435, 26372016, 26402486, 
26372022, 26372534, 26372020, 26372026, 26372024, 26372030, 26404286, 26404287, 
26372028, 26404285, 26372002, 26400163, 26400160, 26372000, 26372006, 26372004, 
26400165, 26372010, 26372008, 26372014, 26372012, 26372050, 26372048, 26404290, 
26404291, 26404288, 26404289, 26404294, 26404292, 26404293, 26372042, 26400201, 
26372044, 26372338, 26372082, 26372346, 26468863, 26372064, 26372836, 26372068, 
26400235, 26402028, 26400018, 26400019, 26400022, 26400020, 26400024, 26372892, 
26372124, 26372098, 26372864, 26400011, 26400520, 26400012, 26372402, 26400048, 
26402356, 26400315, 26468922, 26400056, 26372926, 26400063, 26372668, 26372156, 
26372128, 26400042, 26400046, 26372142, 26400594, 26372434, 26402129, 26400593, 
26400071, 26400586, 26372426, 26400335, 26400588, 26371954, 26371952, 26371958, 
26371956, 26371962, 26371960, 26371966, 26371964, 26402401, 26372708, 26371946, 
26371944, 26371950, 26371948)

慢查詢監控平臺：

查看該sql語句的執行計劃，type：range指的是有范圍的索引掃描，相對于index的全索引掃描，它有范圍限制，因此要優于index。關于range比較容易理解，出現了range，則一定是基于索引的。

分析原因：

執行計劃中顯示，是使用了索引，所以不是索引失效的問題

回表次數增加：如果?IN?列表中的值很多，即使使用了索引，也需要進行大量的回表操作，會導致查詢變慢，需要的字段是大文本，無法建立索引，使用覆蓋索引優化。

網絡傳輸：100M的數據量，增加了傳輸耗時

內存緩沖限制：Mysql中一頁數據是16KB，每條數據需要多個分頁數據，增加了磁盤IO

綜合來看，是數據量太大導致，考慮只查詢需要的字段，不用返回所有的字段，減少數據量。

修改：

select 
id,detail_id,response,ctime 
from response_detaill 
where detaill_id in
(26371986, 26372242, 26371984, 26371990, 26400150, 26371988, 26371994, 26371992,26371998, 26371996, 26371970, 26371968, 26371974, 26400390, 26371972, 26371978,26371976, 26371982, 26371980, 26400178, 26372018, 26400435, 26372016, 26402486, 
26372022, 26372534, 26372020, 26372026, 26372024, 26372030, 26404286, 26404287, 
26372028, 26404285, 26372002, 26400163, 26400160, 26372000, 26372006, 26372004, 
26400165, 26372010, 26372008, 26372014, 26372012, 26372050, 26372048, 26404290, 
26404291, 26404288, 26404289, 26404294, 26404292, 26404293, 26372042, 26400201, 
26372044, 26372338, 26372082, 26372346, 26468863, 26372064, 26372836, 26372068, 
26400235, 26402028, 26400018, 26400019, 26400022, 26400020, 26400024, 26372892, 
26372124, 26372098, 26372864, 26400011, 26400520, 26400012, 26372402, 26400048, 
26402356, 26400315, 26468922, 26400056, 26372926, 26400063, 26372668, 26372156, 
26372128, 26400042, 26400046, 26372142, 26400594, 26372434, 26402129, 26400593, 
26400071, 26400586, 26372426, 26400335, 26400588, 26371954, 26371952, 26371958, 
26371956, 26371962, 26371960, 26371966, 26371964, 26402401, 26372708, 26371946, 
26371944, 26371950, 26371948)

修改之后查詢時間變為100ms，由原來的1s變為100ms，由此可見，是因為數據太大導致網絡延時與Mysql的內存限制

還可以修改為連表查詢join

2. 分頁查詢很慢

背景：負責的項目中慢查詢數量報警，通過數據庫分析診斷平臺可以看出有一條sql命令慢查詢數量超過閾值，是一個分頁查詢，需要查詢某個范圍內的數據，使用limit，深度分頁導致慢查詢。

select * from flow where group_id=3290 limit 150000,100;

分析：這條查詢語句一直都沒有問題，最近突然有問題，發現最近幾次使用的數據集合都是很大的，導致每次分頁limit偏移量較大，平均查詢時間>2s。explain分析執行計劃,使用了非唯一索引。即使有索引，索引只能加速定位，但無法直接“跳轉到第 N 條”（因為索引存儲的是鍵值，而不是行號）。

解決：采用游標分頁，將查詢上一頁的最大id返回，下一頁的查詢基于此id

select * from flow where group_id=3290 AND id>150000 limit 100;

深度分頁的本質：

LIMIT offset, size 的工作原理：
數據庫會先讀取 offset + size 條數據，再丟棄前 offset 條。

第一條查詢實際加載了 5000600 行數據，再丟棄前 5000000 行。