一艰毒、慢查詢優(yōu)化基本步驟
- 先運行看看是否真的很慢摩梧，注意設(shè)置SQL_NO_CACHE
- where條件單表查瓮孙，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起柑船，單表每個字段分別查詢，看哪個字段的區(qū)分度最高
- explain查看執(zhí)行計劃泼各，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
- order by limit 形式的sql語句讓排序的表優(yōu)先查
- 了解業(yè)務(wù)方使用場景
- 加索引時參照建索引的幾大原則
- 觀察結(jié)果鞍时，不符合預(yù)期繼續(xù)從1分析
二、常用調(diào)優(yōu)手段
1.執(zhí)行計劃explain
在日常工作中扣蜻，我們有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的SQL語句逆巍，找出這些SQL語句并不意味著完事了，我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃莽使，查看該SQL語句有沒有使用上了索引锐极，有沒有做全表掃描，這都可以通過explain命令來查看芳肌。所以我們深入了解MySQL的基于開銷的優(yōu)化器灵再，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)，以及當(dāng)運行SQL語句時哪種策略預(yù)計會被優(yōu)化器采用亿笤。
使用explain 只需要在原有select 基礎(chǔ)上加上explain關(guān)鍵字就可以了翎迁，如下：
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)
2. 簡要解釋下explain各個字段的含義
- id : 表示SQL執(zhí)行的順序的標(biāo)識,SQL從大到小的執(zhí)行
- select_type：表示查詢中每個select子句的類型
- table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的，有時不是真實的表名字
- type：表示MySQL在表中找到所需行的方式净薛，又稱“訪問類型”汪榔。常用的類型有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）
- possible_keys：指出MySQL能使用哪個索引在表中找到記錄肃拜，查詢涉及到的字段上若存在索引痴腌，則該索引將被列出，但不一定被查詢使用
- Key：key列顯示MySQL實際決定使用的鍵（索引）爆班，如果沒有選擇索引衷掷，鍵是NULL辱姨。
- key_len：表示索引中使用的字節(jié)數(shù)柿菩，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度雨涛，即key_len是根據(jù)表定義計算而得枢舶，不是通過表內(nèi)檢索出的）
- ref：表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
- rows： 表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況替久，估算的找到所需的記錄所需要讀取的行數(shù)凉泄，理論上行數(shù)越少，查詢性能越好
- Extra：該列包含MySQL解決查詢的詳細(xì)信息
3.EXPLAIN的特性
- EXPLAIN不會告訴你關(guān)于觸發(fā)器蚯根、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
- EXPLAIN不考慮各種Cache
- EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
- 部分統(tǒng)計信息是估算的后众，并非精確值
- EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃。
三蒂誉、建立索引的原則
- 1．選擇唯一性索引
唯一性索引的值是唯一的教藻，可以更快速的通過該索引來確定某條記錄。例如右锨，學(xué)生表中學(xué)號是具有唯一性的字段括堤。為該字段建立唯一性索引可以很快的確定某個學(xué)生的信息。如果使用姓名的話绍移，可能存在同名現(xiàn)象悄窃，從而降低查詢速度。
- 2．為經(jīng)常需要排序蹂窖、分組和聯(lián)合操作的字段建立索引
經(jīng)常需要ORDER BY乾蛤、GROUP BY、DISTINCT和UNION等操作的字段亚享，排序操作會浪費很多時間企孩。如果為其建立索引，可以有效地避免排序操作涣楷。
- 3．為常作為查詢條件的字段建立索引
如果某個字段經(jīng)常用來做查詢條件分唾，那么該字段的查詢速度會影響整個表的查詢速度。因此狮斗，為這樣的字段建立索引绽乔，可以提高整個表的查詢速度。
- 4．限制索引的數(shù)目
索引的數(shù)目不是越多越好碳褒。每個索引都需要占用磁盤空間折砸，索引越多，需要的磁盤空間就越大沙峻。修改表時睦授，對索引的重構(gòu)和更新很麻煩。越多的索引摔寨，會使更新表變得很浪費時間去枷。
- 5．盡量使用數(shù)據(jù)量少的索引
如果索引的值很長，那么查詢的速度會受到影響是复。例如删顶，對一個CHAR(100)類型的字段進(jìn)行全文檢索需要的時間肯定要比對CHAR(10)類型的字段需要的時間要多。
- 6．盡量使用前綴來索引
如果索引字段的值很長淑廊，最好使用值的前綴來索引逗余。例如，TEXT和BLOG類型的字段季惩，進(jìn)行全文檢索會很浪費時間录粱。如果只檢索字段的前面的若干個字符腻格，這樣可以提高檢索速度。
- 7．刪除不再使用或者很少使用的索引
表中的數(shù)據(jù)被大量更新啥繁，或者數(shù)據(jù)的使用方式被改變后荒叶，原有的一些索引可能不再需要。數(shù)據(jù)庫管理員應(yīng)當(dāng)定期找出這些索引输虱，將它們刪除些楣，從而減少索引對更新操作的影響。DROP INDEX index_name ON table_name;
- 8 . 最左前綴匹配原則宪睹，非常重要的原則愁茁。
mysql會一直向右匹配直到遇到范圍查詢(>、<亭病、between鹅很、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)順序的索引罪帖，d是用不到索引的促煮，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整整袁。
- 9 .=和in可以亂序菠齿。
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式
- 10 . 盡量選擇區(qū)分度高的列作為索引坐昙。
區(qū)分度的公式是count(distinct col)/count(*)绳匀，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少炸客，唯一鍵的區(qū)分度是1疾棵，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就 是0痹仙，那可能有人會問是尔，這個比例有什么經(jīng)驗值嗎？使用場景不同开仰，這個值也很難確定拟枚，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條 記錄
- 11 .索引列不能參與計算抖所，保持列“干凈”梨州。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引痕囱，原因很簡單田轧，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時鞍恢，需要把所有元素都應(yīng)用函數(shù)才能比較傻粘，顯然成本 太大每窖。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
- 12 .盡量的擴(kuò)展索引，不要新建索引弦悉。
比如表中已經(jīng)有a的索引窒典，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可
注意：選擇索引的最終目的是為了使查詢的速度變快稽莉。上面給出的原則是最基本的準(zhǔn)則瀑志，但不能拘泥于上面的準(zhǔn)則。讀者要在以后的學(xué)習(xí)和工作中進(jìn)行不斷的實踐污秆。根據(jù)應(yīng)用的實際情況進(jìn)行分析和判斷劈猪，選擇最合適的索引方式。
13.最左原則：
索引匹配的最左原則具體是說良拼，假如索引列分別為A战得，B，C庸推，順序也是A常侦，B，C：
- 那么查詢的時候贬媒，如果查詢【A】【A聋亡，B】 【A，B际乘，C】杀捻，那么可以通過索引查詢
- 如果查詢的時候，采用【A蚓庭，C】致讥，那么C這個雖然是索引，但是由于中間缺失了B器赞，因此C這個索引是用不到的垢袱，只能用到A索引
- 如果查詢的時候，采用【B】 【B港柜，C】 【C】请契，由于沒有用到第一列索引，不是最左前綴夏醉，那么后面的索引也是用不到了
- 如果查詢的時候爽锥，采用范圍查詢，并且是最左前綴畔柔，也就是第一列索引氯夷，那么可以用到索引，但是范圍后面的列無法用到索引
14.order by 與索引
擴(kuò)展閱讀：
索引與調(diào)優(yōu)
mysql索引最左匹配原則的理解
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理 最左前綴原理與相關(guān)優(yōu)化 很多例子
mysql：索引原理與慢查詢優(yōu)化
order by 與索引
