1.explain
有時在使用explain時，感覺有些條件一定能使用到索引鸽凶，但是并沒有使用到，可能是數(shù)據(jù)少移国，mysql優(yōu)化器做了查詢優(yōu)化吱瘩，全表掃描速度大于索引速度。explain不同mysql版本會有不同的結(jié)果迹缀，不要完全死記使碾。
表結(jié)構(gòu)
CREATE TABLE
test(
idint(11) NOT NULL,
aint(11) DEFAULT NULL,
bint(11) DEFAULT NULL,
cvarchar(45) DEFAULT NULL,
dvarchar(45) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEYindex_d(d),
KEYabc(a,b,c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
在mysql命令行中，執(zhí)行explain命令可以看到sql執(zhí)行計劃祝懂，如下圖所示：
id：select 的序列號票摇，有幾個 select 就有幾個id，并且id的順序是按 select 出現(xiàn)的順序增長的砚蓬，id列越大執(zhí)行優(yōu)先級越高矢门，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行
select_type：表示查詢中每個select子句的類型，簡單 祟剔、復(fù)雜隔躲，復(fù)雜包括簡單子查詢、派生表（from語句中的子查詢）物延、union 查詢
simple：查詢中不包含子查詢或者union
primary：復(fù)雜查詢中最外層的 select
subquery：在select或where列表中包含的子查詢
derived：在from列表中包含的子查詢被標記為derived宣旱，mysql會將結(jié)果存放在一個臨時表中，也稱為派生表（derived的英文含義）
union：在 union 中的第二個和隨后的 select
union result：從union表獲取結(jié)果的select被標記為union result
table： explain 的一行所引用的表叛薯。
1.當(dāng) from 子句中有子查詢時浑吟，table列是 <derivenN> 格式，表示當(dāng)前查詢依賴 id=N 的查詢耗溜，于是先執(zhí)行 id=N 的查詢组力。
2.當(dāng)有 union 時，union result 的 table 列的值為<union1,2>抖拴，1和2表示參與 union 的 select 行id燎字。
type*：表示mysql在表中找到所需行的方式，即mysql決定如何查找表中的行阿宅，查找數(shù)據(jù)行記錄的大概范圍轩触。
依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
NULL：MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引
system：system是const類型的特例家夺，當(dāng)查詢的表只有一行的情況下為system
const：當(dāng)mysql對查詢某部分進行優(yōu)化杨名，并轉(zhuǎn)換為一個常量時布隔，使用這些類型訪問。如主鍵或唯一索引表最多只有一行匹配，只需讀取一次炫掐，所以很快品擎。
eq_ref：主鍵或唯一索引被連接使用時义辕，最多返回一條符合條件的記錄双妨。簡單的select查詢不會出現(xiàn)這種type
ref：相比 eq_ref，不使用唯一索引蔓腐，而是使用普通索引或者唯一性索引的部分前綴矩乐，索引要和某個值相比較，可能會找到多個符合條件的行回论。
range：索引范圍掃描散罕，對索引的掃描開始于某一點，返回匹配值域的行傀蓉，常見于between欧漱、<、>葬燎、in()等的查詢
index：掃描全表索引误甚，index與ALL區(qū)別為index類型只遍歷索引樹缚甩，從索引讀取
ALL：全表掃描，mysql將遍歷全表以找到匹配的行窑邦，從硬盤讀取
possible_keys
指出MySQL能使用哪個索引在表中找到行擅威，查詢涉及到的字段上若存在索引，則該索引將被列出冈钦，但不一定被查詢使用裕寨。
key*
顯示MySQL在查詢中實際使用的索引，若沒有使用索引派继，顯示為NULL。查詢中若使用了覆蓋索引捻艳，則該索引僅出現(xiàn)在key列表中驾窟。
【MySQL可以利用索引返回select列表中的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件（即不用遍歷兩棵索引樹）认轨，包含所有滿足查詢需要的數(shù)據(jù)的索引稱為“覆蓋索引”】
如果想強制mysql使用或忽視possible_keys列中的索引绅络，在查詢中使用 force index、ignore index嘁字。
key_len*
表示索引中使用的字節(jié)數(shù)恩急，可通過該列計算查詢中使用的索引的長度。顯示的值為索引字段的最大可能長度纪蜒，并非實際使用長度衷恭，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的纯续。
key_len計算規(guī)則如下：
- 字符串
n char(n)：n字節(jié)長度
n varchar(n)：2字節(jié)存儲字符串長度随珠，如果是utf-8，則長度 3n + 2- 數(shù)值類型
n tinyint：1字節(jié)
n smallint：2字節(jié)
n int：4字節(jié)
n bigint：8字節(jié)- 時間類型
n date：3字節(jié)
n timestamp：4字節(jié)
n datetime：8字節(jié)- 如果字段允許為 NULL猬错，需要1字節(jié)記錄是否為 NULL
row：表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況窗看，估算的找到所需的記錄所需要讀取的行數(shù)。
Extra*
十分重要的額外信息
Using index：查詢的列被索引覆蓋倦炒，并且where篩選條件是索引的前導(dǎo)列显沈，是性能高的表現(xiàn)。一般是使用了覆蓋索引(索引包含了所有查詢的字段)逢唤。對于innodb來說拉讯，如果是輔助索引性能會有不少提高
Using where：表查詢的列未被索引覆蓋，where篩選條件非索引的前導(dǎo)列
Using where Using index：查詢的列被索引覆蓋鳖藕，并且where篩選條件是索引列之一但不是索引的前導(dǎo)列遂唧，意味著無法直接通過索引查找來查詢到符合條件的數(shù)據(jù)，不是索引的最佳使用姿勢
NULL：查詢的列未被索引覆蓋吊奢，并且where篩選條件是索引的前導(dǎo)列盖彭，意味著用到了索引纹烹，但是部分字段未被索引覆蓋，必須通過“回表”來實現(xiàn)召边，不是純粹地用到了索引铺呵，也不是完全沒用到索引
Using index condition：與Using where類似，查詢的列不完全被索引覆蓋隧熙，where條件中是一個前導(dǎo)列的范圍
Using temporary：表示mysql需要使用臨時表來存儲結(jié)果集片挂，常見于group by
Using filesort： MySQL中無法利用索引完成的排序操作稱為“文件排序”，常見于order by
2.查詢索引失效
1.索引列上做任何操作（計算贞盯、函數(shù)音念、（自動or手動）類型轉(zhuǎn)換），會導(dǎo)致索引失效
2.索引中范圍條件右邊的索引列會失效
3.mysql在使用不等于（躏敢！=或者<>）的時候無法使用索引會導(dǎo)致全表掃描
4.is null,is not null 也無法使用索引
5.字符串不加單引號索引失效
6.like以通配符開頭（'%abc...'）mysql索引會失效
關(guān)于部分索引命名符合使用條件闷愤，有時卻會導(dǎo)致索引失效
如：or或in，或范圍查詢
mysql內(nèi)部優(yōu)化器會根據(jù)檢索比例件余、表大小等都會影響整體評估是否使用索引（如何分析見第4節(jié)）
mysql特殊處理：like KK%相當(dāng)于=常量讥脐，%KK和%KK% 相當(dāng)于范圍
3.排序索引失效
explain-Using filesort
order by會導(dǎo)致Using filesort，我們需要做的是使用索引來避免文件排序啼器，因為索引是有序的
Using filesort底層原理見第5節(jié)
explain-Using temporary
group by會導(dǎo)致Using temporary旬渠，group by實質(zhì)是先排序后分組，所以實際上避免Using filesort則會避免Using temporary
index(a,b,c)
case:1查詢使用索引(a,b) 排序c使用索引(c)
case:2查詢使用索引(a) 排序b使用索引(b)
case:3查詢使用索引(a,b) 排序d由于d沒有索引端壳，產(chǎn)生Using filesort
case:4查詢使用索引(a) 排序c沒有使用索引產(chǎn)生Using filesort告丢，與case1,case2對比可知查詢與order by索引必須連續(xù)才可在排序也使用到索引
case:5 同case4
聯(lián)合索引是將各個索引字段做字符串連接后作為key，使用時將整體做前綴匹配损谦。
結(jié)論：如果where字段與order by字段是連續(xù)的組合索引芋齿，由于b+樹底層是有序的，所以不會出現(xiàn)Using filesort成翩。where條件后通過索引(a,b)前綴匹配到符合條件的數(shù)據(jù)觅捆，由于索引是有序的，所以對這些數(shù)據(jù)進行order by排序處理時只要order by的字段符合索引的順序則沒有問題麻敌，如果order by排序的字段與前邊的條件(a,b)不連續(xù)則不會使用索引本身的順序栅炒。
case:1 (where a order by b,a)會優(yōu)化為(order by b)，(a,b)符合索引正常順序术羔，使用索引順序
case:2 (where a order by b,a)會優(yōu)化為(order by b)赢赊，(a,b)符合索引正常順序，使用索引順序
case:3 (where a order by c,a)會優(yōu)化為(order by c)级历，(a,c)不符合索引正常順序释移，產(chǎn)生Using filesort
case:4 (where a order by b,c) (a,b,c)符合索引正常順序，使用索引順序
case:5 (where a order by a,c)會優(yōu)化為(order by c)寥殖，(a,c)不符合索引正常順序玩讳，產(chǎn)生Using filesort
case:6 (where a order by c,b) (a,c,b)不符合索引正常順序涩蜘，產(chǎn)生Using filesort
case:7 (where a order by b,c) (a,c,b)符合索引順序，但是b正序熏纯，c逆序同诫，與聯(lián)合索引底層數(shù)據(jù)結(jié)構(gòu)順序不匹配，所以需要重新排序樟澜，產(chǎn)生Using filesort
case:8 (where a= and c> order by b) ac查詢索引斷掉误窖，所以查詢(a)，排序(b) (a,b)符合索引順序
case:9 (where a= and b> order by c) (a,b)查詢符合索引順序秩贰，(b)范圍查詢導(dǎo)致后續(xù)索引斷掉霹俺，即order by c不會使用到索引，產(chǎn)生Using filesort
重點：索引是排好序的數(shù)據(jù)結(jié)構(gòu)毒费，聯(lián)合索引是將各個索引字段做字符串連接后作為key丙唧，，使用時將整體做前綴匹配蝗罗。所以一定要順序匹配索引才會使用索引。
4.trace工具
-- 開啟trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on;
-- 鑒定sql運行
select * from sy_demo.user where name > 'hh';
-- 查看trace工具sql結(jié)果
SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 關(guān)閉trace
set session optimizer_trace="enabled=off";
{
"steps": [{
"join_preparation": { ‐‐第一階段:SQL準備階段
"select#": 1,
"steps": [{
"expanded_query": "/* select#1 */ select `sy_demo`.`user`.`id` AS `id`,`sy_demo`.`user`.`name` AS `name`,`sy_demo`.`user`.`id_card` AS `id_card`,`sy_demo`.`user`.`status` AS `status`,`sy_demo`.`user`.`phone` AS `phone`,`sy_demo`.`user`.`modify_time` AS `modify_time`,`sy_demo`.`user`.`create_time` AS `create_time`,`sy_demo`.`user`.`version` AS `version` from `sy_demo`.`user` where (`sy_demo`.`user`.`name` > 'hh') limit 0,500"
}] /* steps */
} /* join_preparation */
},
{
"join_optimization": { ‐‐第二階段:SQL優(yōu)化階段（如聯(lián)合索引字段順序調(diào)整）
"select#": 1,
"steps": [{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`sy_demo`.`user`.`name` > 'hh')",
"steps": [{
"transformation": "equality_propagation",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`sy_demo`.`user`.`name` > 'hh')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {} /* substitute_generated_columns */
},
{
"table_dependencies": [{ ‐‐表依賴詳情
"table": "`sy_demo`.`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [] /* depends_on_map_bits */
}] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [{ ‐‐預(yù)估表的訪問成本（重點）
"table": "`sy_demo`.`user`",
"range_analysis": {
"table_scan": { ‐全表掃描情況 scan
"rows": 17, ‐‐掃描行數(shù)
"cost": 6.5 ‐‐查詢成本 相對值
} /* table_scan */ ,
"potential_range_indexes": [{ ‐‐查詢可能使用的索引
"index": "PRIMARY", ‐‐主鍵索引
"usable": false, --是否使用該索引
"cause": "not_applicable"
},
{
"index": "idx_user", -‐輔助索引
"usable": true, --是否使用該索引
"key_parts": [ --索引構(gòu)成
"name",
"id_card",
"phone",
"id"
] /* key_parts */
}
] /* potential_range_indexes */ ,
"setup_range_conditions": [] /* setup_range_conditions */ ,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */ ,
"analyzing_range_alternatives": { ‐‐分析各個索引使用成本（重點）
"range_scan_alternatives": [{
"index": "idx_user",
"ranges": [
"hh < name" ‐‐索引使用范圍
] /* ranges */ ,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, ‐‐使用該索引獲取的記錄是否按照主鍵排序
"using_mrr": false,
"index_only": false, ‐‐是否使用覆蓋索引
"rows": 10, ‐‐索引掃描行數(shù)
"cost": 13.01, ‐‐索引使用成本
"chosen": false, ‐‐是否選擇該索引
"cause": "cost"
}] /* range_scan_alternatives */ ,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}] /* rows_estimation */
},
{
"considered_execution_plans": [{
"plan_prefix": [] /* plan_prefix */ ,
"table": "`sy_demo`.`user`",
"best_access_path": { ‐‐最優(yōu)訪問路徑
"considered_access_paths": [{ ‐‐最終選擇的訪問路徑
"rows_to_scan": 17,
"access_type": "scan", ‐‐訪問類型 "access_type": "scan" 全表掃描 "used_index": "PRIMARY" 主鍵索引
"resulting_rows": 17,
"cost": 4.4,
"chosen": true ‐‐確定選擇
}] /* considered_access_paths */
} /* best_access_path */ ,
"condition_filtering_pct": 100,
"rows_for_plan": 17,
"cost_for_plan": 4.4,
"chosen": true
}] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`sy_demo`.`user`.`name` > 'hh')",
"attached_conditions_computation": [] /* attached_conditions_computation */ ,
"attached_conditions_summary": [{
"table": "`sy_demo`.`user`",
"attached": "(`sy_demo`.`user`.`name` > 'hh')"
}] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [{
"table": "`sy_demo`.`user`"
}] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { ‐‐Sql執(zhí)行階段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`sy_demo`.`user`",
"field": "phone"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"limit": 500,
"rows_estimate": 642,
"row_size": 581,
"memory_available": 262144,
"strip_additional_fields": {
"row_size": 117,
"sort_merge_cost": 451.8,
"priority_queue_cost": 1758.8,
"chosen": false
} /* strip_additional_fields */
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { ‐‐文件排序信息
"rows": 16, ‐‐預(yù)計掃描行數(shù)
"examined_rows": 16, ‐‐參數(shù)排序的行
"number_of_tmp_files": 0, ‐‐使用臨時文件的個數(shù)蝌戒，這個值如果為0代表全部使用的sort_buffer內(nèi)存排序串塑，否則使用磁盤文件排序
"sort_buffer_size": 261816, ‐排序緩存的大小
"sort_mode": "<sort_key, packed_additional_fields>" ‐排序方式，這里用的單路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
5.Using filesort原理
單路排序:是一次性取出滿足條件行的所有字段存入sort buffer北苟，然后在sort buffer中進行排序;用trace工具可以看到sort_mode信息里顯示<sort_key, additional_fields>或者< sort_key, packed_additional_fields>
雙路排序(又叫回表排序模式):是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和主鍵id存入sort buffer桩匪，然后在 sort buffer 中進行排序，排序完后需要再次根據(jù)id回到原表取回其它需要的字段;用trace工具 可以看到sort_mode信息里顯示<sort_key, rowid>
MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data(默認1024字節(jié)) 的大小和需要查詢的字段總大小來 判斷使用哪種排序模式友鼻。
如果 max_length_for_sort_data 比查詢字段的總長度大傻昙，那么使用 單路排序模式;
如果 max_length_for_sort_data 比查詢字段的總長度小，那么使用 雙路排序模式彩扔。
6.表關(guān)聯(lián)算法（了解）
mysql的表關(guān)聯(lián)常見有兩種算法
Nested-Loop Join 算法
Block Nested-Loop Join 算法
1妆档、 嵌套循環(huán)連接 Nested-Loop Join(NLJ) 算法
一次一行循環(huán)地從第一張表(稱為驅(qū)動表)中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段虫碉，根據(jù)關(guān)聯(lián)字段在另一張表(被驅(qū)動 表)里取出滿足條件的行贾惦，然后取出兩張表的結(jié)果合集敦捧。
2、 基于塊的嵌套循環(huán)連接 Block Nested-Loop Join(BNL)算法
把驅(qū)動表的數(shù)據(jù)讀入到 join_buffer 中习瑰，然后掃描被驅(qū)動表甜奄，把被驅(qū)動表每一行取出來跟 join_buffer 中的數(shù)據(jù)做對比贺嫂。
MySQL對于被驅(qū)動表的關(guān)聯(lián)字段沒索引的關(guān)聯(lián)查詢第喳，一般都會使用 BNL 算法曲饱。如果有索引一般選擇 NLJ 算法扩淀，有 索引的情況下 NLJ 算法比 BNL算法性能更高
關(guān)聯(lián)字段加索引驻谆，讓mysql做join操作時盡量選擇NLJ算法 小表驅(qū)動大表
7.count(*)查詢優(yōu)化
explain select count(1) from sy_demo.user;
explain select count(id) from sy_demo.user;
explain select count(*) from sy_demo.user;
explain select count(name) from sy_demo.user;
以上四個sql的執(zhí)行計劃都一樣的胜臊，都是使用的輔助索引，其執(zhí)行效率也差不多黑忱。區(qū)別在于根據(jù)某個字段count不會統(tǒng)計字段為null值的數(shù)據(jù)行
為什么mysql最終選擇輔助索引而不是主鍵聚集索引?因為二級索引相對主鍵索引存儲數(shù)據(jù)更少甫煞，檢索性能應(yīng)該更高冠绢，所以即使我們使用count(id)弟胀、count(*)邮利、count(1)都是走的輔助索引延届。而為了防止null影響方庭，參考阿里巴巴編程規(guī)范，建議使用count(*)
8.索引口訣
全值匹配我最愛运悲，最左前綴要遵守
帶頭大哥不能死，中間兄弟不能斷
索引列上少計算烁巫，范圍之后全失效
LIKE符號寫最右亚隙，覆蓋索引不寫星
不等空值還有or磁餐，索引失效要少用
var引號不能丟，SQL高級也不難
分組之前必排序阿弃，一定要上索引啊
參考：
https://www.iteye.com/blog/hudeyong926-785181
https://blog.csdn.net/weiwangchao_/article/details/50256673
https://blog.csdn.net/lbr2008/article/details/79245289#%E5%8F%A3%E8%AF%80
