前言
前幾天幫公司解決線上慢SQL告警問題优烧，遇到了幾個case炕舵。
下面我會結合case案例分析自己這段時間在工作上遇到的慢查詢談談數(shù)據(jù)庫如何優(yōu)化慢查詢逻锐。
一般我們遇到的慢sql都是索引沒有正確使用導致的猜谚，所以我先介紹下索引相關知識
索引介紹
索引概念
排好序的快速查找的數(shù)據(jù)結構（我們平時說的索引，如果沒有特別指明呼巷，都是指B樹，其中聚集索引赎瑰、次要索引、覆蓋索引破镰、復合索引餐曼、前綴索引、唯一索引默認使用的都是B+樹索引鲜漩，除B+樹這種類型的索引外還有哈希索引等）
索引優(yōu)缺點
優(yōu)點：
查找 ：提高數(shù)據(jù)檢索效率源譬，降低IO成本。
排序：通過索引對數(shù)據(jù)進行排序孕似，降低排序成本踩娘，降低cpu消耗
缺點：
實際上索引也是一張表，該表保存了主鍵與索引字段喉祭，并指向索引的記錄养渴，所以索引列也需要占空間。
更新表時（insert泛烙、update理卑、delete）不僅要保存數(shù)據(jù)還要更新保存索引文件新添加的索引列。
索引分類
單值索引（單列索引）：一個索引只包含單個列蔽氨，一個表中可以有多個單列索引藐唠。
唯一索引：索引列必須唯一，但可以允許有空值
復合索引：一個索引包含多個列
索引結構
BTree索引
Hash索引
full-text全文檢索
R-Tree索引
哪些情況要建索引
主鍵自動建主鍵索引
頻繁作為查詢條件的字段應該創(chuàng)建索引
查詢中與其他表關聯(lián)的字段鹉究，外鍵關系建立索引
在高并發(fā)下傾向建立組合索引
查詢中的排序字段宇立，排序字段若通過索引去訪問將大大提高排序速度
查詢中統(tǒng)計或者分組的數(shù)據(jù)
哪些情況不適合建索引
頻繁更新的字段
where條件用不到的字段不創(chuàng)建索引
表記錄太少
經(jīng)常增刪改的表
數(shù)據(jù)重復太多的字段，為它建索引意義不大（假如一個表有10萬自赔，有一個字段只有T和F兩種值妈嘹，每個值的分布概率大約只有50%，那么對這個字段的建索引一般不會提高查詢效率匿级，索引的選擇性是指索引列的不同值數(shù)據(jù)與表中索引記錄的比蟋滴，，如果一個表中有2000條記錄痘绎，表中索引列的不同值記錄有1980個津函，這個索引的選擇性為
1980/2000=0.99，如果索引項越接近1孤页，這個索引效率越高）
explain字段分析
explain是排查慢sql的一種最常用的手段
mysql> EXPLAIN SELECT 1;
[圖片上傳失敗...(image-5d524b-1700187059535)]
id：表示select子句或者操作的順序
id相同：執(zhí)行順序自上而下
id不同：id值越大優(yōu)先級越高尔苦，越先被執(zhí)行
id相同不同：id越大越先執(zhí)行，相同的自上而下執(zhí)行
select_type：主要是區(qū)分普通查詢、聯(lián)合查詢允坚、子查詢等魂那。
SIMPLE：簡單的select查詢，不包含子查詢與union
PRIMARY：查詢中包含復雜的子部分稠项，最外層會被標記為primary
SUBQUERY：在select或者where列表中包含了子查詢
DERIVED：在from列表中包含的子查詢衍生表
UNION：若第二個select出現(xiàn)在union之后涯雅，則被標記為union
UNION RESESULT：從union表獲取結果的select
table：這一行數(shù)據(jù)是哪個表的數(shù)據(jù)
type：查詢中使用了何種類型
結果值從最好到最壞：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
一般來說，得保證查詢至少達到range級別展运，最好能到達ref
system：表只有一行記錄（等于系統(tǒng)表）活逆，這是const類型的特例，平時不會出現(xiàn)
const：表示通過索引一次就能夠找到
eq_ref:唯一性索引掃描拗胜，對于每個索引鍵蔗候，表示只有一條記錄與之匹配，常見于主鍵或唯一索引掃描
ref：非唯一性索引掃描埂软，返回匹配某個單獨值的所有行
range：只檢索給定范圍的行锈遥，使用一個索引來選擇行，一般就是在where語句中出現(xiàn)了between勘畔、<所灸、>、in等的查詢
index：index比all快咖杂，因為index是從索引中讀取庆寺，all是從硬盤中讀取
all：遍歷全表才能找到
possible_key：顯示可能應用在這張表中的索引，但實際上不一定用到
key：實際上使用的索引诉字，如果沒有則為null
key_len：表示索引中使用的字節(jié)數(shù)（可能使用的懦尝，不是實際的），可通過該列查詢中使用的索引的長度壤圃，在不損失精確性的情況下陵霉，長度越短越好
ref：顯示索引的哪一列被用到，如果可能的話是一個常數(shù)伍绳，哪些常量被用于查找索引列上的值
rows：大致估算找出所需的記錄要讀取的行數(shù)
Extra：包含不適合在其他列中顯示踊挠，但十分重要的的額外信息
Using filesort 說明mysql會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內的索引順序進行讀取冲杀，mysql中無法利用索引完成的排序成為文件排序
Using temporary 使了用臨時表保存中間結果效床，mysql在對查詢結果排序時使用了臨時表，常見于排序order by 和分組查詢group by
Using index 表示相應的select操作中使用了覆蓋索引权谁，避免訪問了表的數(shù)據(jù)行剩檀，效率高
Using where 表明使用了where進行過濾
Using join buffer 使用了連接緩存
impossible where 如果where子句的值總是false，不能用來獲取任何元組
select table optimized away 在沒有group by子句的情況下旺芽，基于索引優(yōu)化min/max操作或者對于myisam存儲引擎優(yōu)化
count(*)操作沪猴，不必等到執(zhí)行階段再進行計算
更詳細的內容辐啄，請看我之前的文章：
索引失效
應該盡量全值匹配
復合最佳左前綴法則（第一個索引不能掉运嗜，中間不能斷開）
不在索引列上做任何操作（計算壶辜、函數(shù)、類型轉換）會導致索引失效而轉向全表掃描
存儲存引擎不能使用索引中范圍條件右邊的列
盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致））担租，減少
select*mysql在使用不等于(!=或者<>)的時候無法使用索引會導致全表掃描
is null砸民，is not null也會無法使用索引
like以統(tǒng)配符開頭
字符串不加單引號
少用or
order by優(yōu)化
- 避免filesort，盡量在索引上進行排序奋救，遵照最佳左前綴原則
filesort有兩種排序：
雙路排序：兩次磁盤掃描
單路排序：一次性讀取保存在內存中阱洪，沒拉完的數(shù)據(jù)再次拉
單路排序總體好于雙路排序
優(yōu)化策略：1、增大
sort_buffer_size參數(shù)的設置菠镇，2、增大max_length_for_sort_data參數(shù)的設置承璃，盡可能一次拿到內存
Case分析
案例一
in中參數(shù)太多
select * from goods_info where goods_status = ? and id in(11,22,33......)
in中id數(shù)據(jù)量比較多利耍，導致查詢的數(shù)據(jù)量比較大，這是一個比較常見的慢查詢類型盔粹，并且往往在業(yè)務數(shù)據(jù)量比較少的時候這條語句不是慢查隘梨；
因為參數(shù)傳進一個List集合，當參數(shù)比較多的時候舷嗡，可以采用在業(yè)務層把List集合拆分為多個長度較小的集合轴猎，分多次查詢，具體每一次拆長度為多少进萄，可能需要具體根據(jù)業(yè)務及數(shù)據(jù)量進行評估
我的解決辦法：業(yè)務代碼增加拆分集合操作捻脖，LIMIT_SIZE設置為1000
List<List<Integer>> partitionGoodsIdList = Lists.partition(goodsIdList, LIMIT_SIZE);
當SQL的查詢參數(shù)過多，我覺得可以考慮使用上述拆分的方式
案例二
返回的查詢結果過多
select from goods where goods_status = ? and poi_id = ?
解決辦法：將SQL修改為分頁查詢中鼠，并在業(yè)務代碼上修改為分頁查詢可婶，修改后的SQL語句如下：
select from goods where goods_status = 1 and poi_id = 11 and goods_id > 22 order by goods_id limit 2000
通過分頁的方式可以降低數(shù)據(jù)量，避免慢查詢援雇，但是會從而導致一次查詢請求矛渴，增加為多次查詢請求，對于limit的大小需要謹慎評估
案例三
order by慢查詢
SELECT * FROM order FORCE INDEX (orderid) WHERE orderId = 11 AND status IN (0,22) ORDER BY id ASC ;
該SQL由于強制指定了使用orderId索引惫搏，但條件中并沒有orderId具温，導致產(chǎn)生全表掃描（type: ALL）；
如下為問題SQL的執(zhí)行計劃：
直接原因是最終傳給SQL查詢函數(shù)的參數(shù)筐赔，orderId沒有加入where子句铣猩，但forceindex一直生效
案例四
join慢查詢
select * from useract join userinfo order by useracct.id desc limit 11;
對sql進行explain可以發(fā)現(xiàn)，因為忘寫了join的on條件川陆，這是掃全表sql剂习，如下圖：
我們首先看type級別兩個表的級別都是ALL蛮位，說明該條語句沒有用到索引，做了全表掃描是最差的情況
優(yōu)化：
案例五
不同索引嘗試
select id from goods_info where id > ? and activity_id = ? and goods_switch in(?+) limit ?
select id from goods_info where id > 123991510 and activity_id = 0 and goods_switch in (2,3) limit 1000
通過執(zhí)行計劃可知鳞绕，該語句走的是activity_id和主鍵的索引失仁，但是這種命中率比較低，大量的數(shù)據(jù)被goods_switch篩掉
解決辦法：在不確定最優(yōu)的索引的情況下们何，可以在測試環(huán)境下萄焦，分別添加不同的索引，觀察執(zhí)行計劃及語句的執(zhí)行時間冤竹。
嘗試強制走主鍵索引拂封，效果不佳；嘗試添加activity_id_id的聯(lián)合索引鹦蠕，效果不佳冒签；嘗試添加activity_id,goods_switch的聯(lián)合索引，問題解決钟病！
所以在不確定哪種索引是最優(yōu)時萧恕，可以嘗試建立不同的索引，觀察語句在不同索引情況下的執(zhí)行情況進行權衡肠阱。
案例六
MySQL選錯索引
select * from goods_info
where goods_source = ? and goods_switch != ? and id > ? order by id limit ?
select * from goods_info
where goods_source = 2 and goods_switch != 8 and id > 12395070 order by id limit 1000
這條語句從語句本身猜測使用的是主鍵索引票唆，但是查看該語句的執(zhí)行計劃，發(fā)現(xiàn)走的索引是idx_goods_source屹徘，即走了goods_source的單列索引走趋！
解決辦法：修改SQL語句，強制走主鍵索引噪伊，查看執(zhí)行計劃簿煌，走了主鍵索引，查詢時間大大降低鉴吹。
正常情況下MySQL會選擇最優(yōu)的索引啦吧，但是有時候也會選錯，MySQL的優(yōu)化器會依據(jù)掃描行數(shù)拙寡、是否排序授滓，索引區(qū)分度來選擇最優(yōu)的索引，并且掃描行數(shù)不一定完成準確肆糕，只是MySQL的一個預估值
