一、學習框架
- 一條查詢語句的執(zhí)行過程：一條語句在客戶端執(zhí)行，通過連接器跟服務(wù)器建立起連接跪者，通過連接器控制連接的數(shù)量蛀序，獲知客戶端的狀態(tài)川抡，為每一個客戶端建立起TCP連接拴事。然后到詞法分析器践剂，進行詞法分析和語法分析溉知，詞法分析將語句解析成一個個token詞陨瘩，語法分析將token詞生成語法樹。優(yōu)化器負責生成執(zhí)行計劃级乍，選擇索引舌劳。根據(jù)優(yōu)化器選擇的索引，執(zhí)行器調(diào)用存儲引擎的接口取數(shù)據(jù)玫荣，執(zhí)行語句甚淡。
重點：優(yōu)化器如何選擇索引？
- 掃描行數(shù)：掃描行數(shù)怎么判斷捅厂？一個索引上不同的值的個數(shù)贯卦，我們稱之為“基數(shù)”（cardinality）。也就是說焙贷，這個基數(shù)越大撵割，索引的區(qū)分度越好 。MySQL是通過采樣統(tǒng)計的方式來判斷的辙芍。但是掃描行數(shù)只是個預(yù)估值啡彬，存在對mysql誤導的情況
- 是否使用臨時表 --見下方關(guān)于排序的講解
- 是否需要排序
索引：索引需要知道索引的結(jié)構(gòu)，聚集索引和非聚集索引的區(qū)別故硅，普通索引和唯一索引的區(qū)別庶灿，什么時候適合用普通索引，普通索引能夠用上change buffer的優(yōu)化
覆蓋索引&回表：如果需要查詢的字段吃衅，都包含在索引里面（通常是聯(lián)合索引）往踢，那么就不需要去主鍵索引回表，因為只有主鍵索引的葉子結(jié)點包含row數(shù)據(jù)行徘层，這也是一種常見的優(yōu)化手段峻呕。
需要注意的是回表掃描的行數(shù)不會體現(xiàn)在rows_examed里，因為rows_examed是表示執(zhí)行引擎從存儲器取了多少次數(shù)據(jù)惑灵，而是否回表是取決于不同的存儲引擎山上，例如innoDb需要回表，myslam就不需要回表
如果有聯(lián)合主鍵索引(a,b)英支，和索引c佩憾，a,c,ac,abc都可以走索引索引下推：索引下推的前提是有覆蓋索引，通過索引下推，在覆蓋索引遍歷的時候?qū)⒁恍┎环系男羞^濾掉妄帘，減少回表的次數(shù)楞黄。
無索引下推：
有索引下推：
- 排序---總結(jié)，如果排序字段有索引抡驼，能夠自然有序鬼廓，
- rowid排序
- 全字段排序
- 內(nèi)排序
- 外排序
- 慢查詢
- 開啟：slow_query_log 慢查詢開啟狀態(tài)，long_query_time 查詢超過多少秒才記錄
- 參數(shù)
在有些場景下致盟，執(zhí)行器調(diào)用一次碎税，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的
- explain語句
通沉笪看
possible_keys以及key兩個字段possible_keys的內(nèi)容取決于where語句 +EXPLAIN TABLE的結(jié)果雷蹂，即where會用到哪些EXPLAIN TABLE中能看到的索引key表示 MySQL 存儲引擎實際選擇的索引。這個索引可能不是possible_keys中列出來的key_len表示實際使用的索引的長度杯道，通常能根據(jù)這個字段 +key來判斷用了key的哪幾個字段
當前表的關(guān)聯(lián)方式匪煌，也對查詢性能影響較大。對應(yīng)的在 EXPLAIN 結(jié)果中党巾，有個 type 字段來參考關(guān)聯(lián)方式萎庭。通常問題 SQL 中會出現(xiàn) type=ALL 或者 type=index 的情況，即全表掃描齿拂。下面列舉了下 type 的可能值驳规，按照性能從好到差來排序。
system & const: 表示你條件可以被轉(zhuǎn)換為常數(shù)列（即能通過條件確定唯一值）署海，比如
只有一行記錄的系統(tǒng)表：system
where 條件是 pk or unique key 的唯一值达舒，例如
a=1 & a is pk or ukeq_ref & ref & ref_or_null
eq_ref：previous table 的內(nèi)容能在當前 table 唯一確認一列
ref：用了索引，但是做不到 eq_ref 那么好叹侄，就是 ref
ref_or_null：等價 ref，比 ref 多比較了一個 null 的情況
-
fulltext：使用了
FULLTEXT索引- index_merge
采用了多個索引昨登，此時
key列會展示用到的具體索引unique_subquery & index_subquery
針對 IN 類型的 subquery 優(yōu)化
range
用了索引趾代，判斷采用范圍讀取的方式比較好
通常會出現(xiàn)在范圍類型的 operator & 當前索引中用到的列，其區(qū)分度比較小的情況
range對 column 相關(guān) operator 是有要求的丰辣，支持的有：=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN()index & all
都是全表掃描撒强，區(qū)別是用非主鍵還是主鍵
要注意的是，不同索引的掃描笙什，數(shù)據(jù)的返回順序是不一樣的
rows：預(yù)估掃描行
二飘哨、排查思路
- 索引使用不符合預(yù)期
沒有用索引---選擇合適的索引
優(yōu)化器選擇了錯誤索引---優(yōu)化器為什么會選錯索引呢？force_index或者使用order by 引導優(yōu)化器使用合適的索引
索引沒有實現(xiàn)覆蓋索引---根據(jù)業(yè)務(wù)決定如何用上覆蓋索引的優(yōu)化
- 掃描行數(shù)過多---limit m,n 跳過m行取n行琐凭，會出現(xiàn)掃描m+n的結(jié)果
- IO存在問題芽隆，獲取不到連接，或者被無效的連接占有
- 返回不需要的列，慎用select * 尤其是一些字段是json胚吁，如果使用select *會造成IO壓力
三牙躺、分析慢查詢的思路
- explain查看情況
- 分析語句的執(zhí)行計劃和執(zhí)行過程
- 結(jié)合業(yè)務(wù)尋找優(yōu)化點
SQL優(yōu)化---while true 并且limit m,n導致的SQL掃描行數(shù)過多。limit m,n會取出m+n行數(shù)據(jù)腕扶，然后跳過m行孽拷，因此rows_examed會是m+n。
SELECT id, uid2, accepted, frm, crtime, uptime FROM contact_request WHERE (uid=110977129) AND (type=1) AND (frm!='addrbook_multi') AND (crtime>'2000-01-01 00:00:00') ORDER BY crtime desc LIMIT 0,1001
def get_exclude_talent_ids_add_fr(manager, uid, start):
to_uids = []
ht = {}
page = 0
size = 1000
while True:
cr_rows = network_service.get_cttreq_all_addfr_requests(manager=manager, uid=uid, crtime=start,
page=page * size, count=size + 1)
remain = 0
if cr_rows:
if len(cr_rows) > size:
remain = 1
for cr_row in cr_rows:
to_uid = cr_row['uid2']
if to_uid not in ht:
to_uids.append(to_uid)
ht[to_uid] = to_uid
if remain:
page += 1
else:
break
return to_uids
Limit m,n-->id>max_fid limit n
explain SELECT count(*) FROM contact_request WHERE (uid=110977129) AND (type=1) AND (frm!='addrbook_multi') AND (crtime>'2000-01-01 00:00:00') and id>max_id ORDER BY crtime desc LIMIT 1001
def get_exclude_talent_ids_add_fr_new(manager, uid, start):
to_uids = []
ht = {}
size = 1000
max_id = 0
while True:
cr_rows = network_service.get_cttreq_addfr_requests_by_crtime(manager=manager, uid=uid, crtime=start, limit=size+1, id=max_id)
remain = 1 if len(cr_rows) > size else 0
if cr_rows:
start = cr_rows[len(cr_rows) - 1]['crtime']-datetime.timedelta(seconds=1)
max_id = cr_rows[len(cr_rows) - 1]['id']
for cr_row in cr_rows:
to_uid = cr_row['uid2']
if to_uid not in ht:
to_uids.append(to_uid)
ht[to_uid] = to_uid
if not remain:
break
return to_uids
