1. 全字段排序

? ? 疑問1:? explain 后, 各個字段代表啥意思?

? ? ? ? ? ? 答:? ? ?https://blog.csdn.net/bingguang1993/article/details/98313390

? ? 1.sort_buffer : mysql給每個線程分配一塊內存用于排序

1. using filesort 需要排序

2. 索引city 示意圖

? ? 2.執(zhí)行計劃:?

? ? ? ? ? ? 1.初始化sort_buffer , 放入 name, city , age 三個字段;

? ? ? ? ? ? 2.從索引city 找到第一個滿足city = '杭州'條件的主鍵id ==> 圖中ID_X

? ? ? ? ? ? 3.到主鍵id索引取出整行, 區(qū) name, city , age 三個字段的值, 存入sort_buffer

? ? ? ? ? ? 4.從索引city 取下一個記錄的主鍵id

? ? ? ? ? ? 5.重復 3 , 4 知道city 的值不滿足查詢條件位置.? ==> 圖中ID_Y

? ? ? ? ? ? 6.對sort_buffer中的數(shù)據(jù)按照字段name 做快速排序

? ? ? ? ? ? 7.取快排結果前1000行返回客戶端.?

3.全字段排序

? ?3.參數(shù) sort_buffer_size? ==> 為程序開辟的內存大小

? ? ? ? ? ? 數(shù)據(jù)量 <??sort_buffer_size? ==> 在內存中完成

? ? ? ? ? ? 數(shù)據(jù)量 >?sort_buffer_size? ==> 磁盤臨時文件排序

? ? 4.有方法可以確定排序是否使用了臨時文件:?

4. 全排序的 OPTIMIZER_TRACE

? ? ? ? ? ?1. number_of_tmp_files ==> 使用了幾個臨時文件? ==> 歸并排序

? ? 疑問2 : 為什么 sort_buffer_size 越小,??number_of_tmp_files? 就越大???

????????????????難道 是總數(shù)據(jù)量 /?sort_buffer_size? =?number_of_tmp_files? ??

? ? ? ? ? ?2.examined_rows ==> 檢查行數(shù) ==> 參與排序的行數(shù)

? ? ? ? ? ?3. internal_tmp_disk_storage_engine 設置成 MyISAM (為了避免干擾)

? ? ? ? ? ?4.因為查OPTIMIZER_TRACE 要用到臨時表,如果使用innodb , 把數(shù)據(jù)從臨時表取出來, 會讓 innodb_rows_read 加1

? ? ? ?疑問3 : 什么場景會用到臨時表 ?

? ? ? ?疑問4 : 內存臨時表和磁盤臨時表有什么不同 ?? ?

????????????靈光: 其實臨時表就是臨時文件, 因為表就是以文件形式儲存的.?

2.row id排序?

? ? 1.全字段排序缺點:?

? ? ? ? 1如果查詢要返回的字段很多, sort_buffer中要放的字段太多, 需要很多臨時文件, 性能差.

? ? ? ? 2單行長度太大 , 性能差

? ? 2.max_length_for_sort_data? 如果單行長度大于該值 則使用row id 排序(單位為char)

5. row id 排序

? ? ? ? 3.執(zhí)行計劃:?

? ? ? ? ? ? 1與全字段排序不同點:?

? ? ? ? ? ? ? ? 1.只把 id? ,? name 放入sort_buffer中?

? ? ? ? ? ? ? ? 2. 最后多了一次回表, 取目標name, city , age?

? ? ? ? ? ? 因為需要查詢的字段長度太大,所以使用這個方式. 機智啊!?

6. row id 的OPTIMIZER_TRACE

? ? ? ? ? ? 2. number_of_tmp_files 變成10 , 變小了. 因為每一行 從name ,city ,age 變成了 id , name, 長度小了

3. 全字段排序? VS row id 排序 :?

? ? 1.優(yōu)先選擇全字段排序, 因為少一次回表.?

設計思想: 如果內存夠, 盡量使用內存, 不得已再使用磁盤.?

? ? 2. 使用臨時表的原因是 , 原來的數(shù)據(jù)是無序的.?

? ? ? ? ==> 如果保證從city所以中取出的行, 是按照name排序的. 就不用再排序.?

? ? ? ? ==> 所以可以使用聯(lián)合索引

4更快的方法

????1.聯(lián)合索引

7. 聯(lián)合索引

? ? 執(zhí)行計劃:?

? ? ? ? 1.從索引(city, name)找到第一個滿足 city = ' 杭州'條件的主鍵id

? ? ? ? 2.到主鍵id索引取出整行, 取name, city, age 三個字段的值, 返回

? ? ? ? 3. 從索引 (city, name) 去下一個記錄主鍵id

? ? ? ? 4.重復步驟2, 3 , 直到查到1000條記錄, 或者是不滿足city = '杭州'條件.?

8. 聯(lián)合索引執(zhí)行計劃

9.聯(lián)合索引 explain

? ? 4.覆蓋索引 ==> 性能最高, 但是要權衡索引維護代價

? ? ? ? ==>建立索引(city, name, age)

? ? ? ? 執(zhí)行計劃:?

????????1.從索引(city, name, age)找到第一個滿足 city = ' 杭州'條件的主鍵id,??取name, city, age 三個字段的值, 返回

????????2. 從索引 (city, name, age) 取下一個記錄, 從索引中取出三個值.

? ? ? ? 3.重復步驟2 , 直到查到1000條記錄, 或者是不滿足city = '杭州'條件.?

10. 覆蓋索引執(zhí)行計劃

11. 覆蓋索引expalin