上一篇 <<<MySQL性能優(yōu)化之in、exists優(yōu)化
下一篇 >>>MySQL如何性能優(yōu)化面試題完美解答
1.Mysql排序支持兩種filesort和index
1.1 using index
mysql掃描索引本身完成排序（底層存放在b+樹(shù)中外厂，默認(rèn)順序排列）侧巨，查詢(xún)不需要額外的排序,操作效率較高
1.2 using filesort
將數(shù)據(jù)查詢(xún)到內(nèi)存中宰啦，在內(nèi)存中在做一次排序允懂，效率非常低汽馋；
2.Order by排序代碼示例
索引信息：idx_name_age_position
2.1 查詢(xún)條件和order by字段均被索引覆蓋
explain select * from employees where name= 'jarye' and position ='ceo' order by age;
2.2 跳過(guò)聯(lián)合索引中間值偷遗，出現(xiàn)了Using filesort
explain select * from employees where name= 'jarye' order by position;
2.3 索引順序打亂，出現(xiàn)了Using filesort
explain select * from employees where name= 'jarye' order by position,age;
2.4 索引默認(rèn)順序排列伟姐，如果不一致收苏，則會(huì)出現(xiàn)Using filesort
explain select * from employees where name= 'jarye' order by age asc ,position desc ;
explain select * from employees where name= 'jarye' order by age asc ,position asc ;
2.5 多個(gè)相等條件也是范圍查詢(xún)，也會(huì)出現(xiàn)Using filesort
explain select * from employees where name in('raby','jarye') order by age, position;
3.filesort實(shí)現(xiàn)原理
filesort通過(guò)相應(yīng)的排序算法愤兵，將取得的數(shù)據(jù)在sort_buffer_size系統(tǒng)變量設(shè)置的內(nèi)存排序區(qū)中進(jìn)行排序鹿霸，如果內(nèi)存裝載不下，會(huì)將磁盤(pán)上的數(shù)據(jù)進(jìn)行分塊秆乳，再對(duì)各個(gè)數(shù)據(jù)塊進(jìn)行排序懦鼠，再將各個(gè)塊合并成有序的結(jié)果集。
參數(shù)查看：show variables like '%max_length_for_sort_data%';
3.1單路排序步驟
1.從索引name字段中屹堰，查詢(xún)到滿(mǎn)足條件的數(shù)據(jù)name='jarye'條件主鍵的id
2.根據(jù)主鍵id取出整行的數(shù)據(jù)肛冶，緩存到sort buffer中
3.重復(fù)執(zhí)行1,2步驟流程
4.對(duì)sort buffer中的數(shù)據(jù)實(shí)現(xiàn)排序給客戶(hù)端
3.2雙路排序步驟
1.從索引name字段中，查詢(xún)到滿(mǎn)足條件的數(shù)據(jù)name=‘jarye’條件主鍵的id
2.根據(jù)主鍵id扯键，獲取排序的字段和主鍵id緩存到sort buffer中
3.重復(fù)執(zhí)行1,2步驟流程
4.對(duì)sort buffer中的數(shù)據(jù)實(shí)現(xiàn)排序
5.根據(jù)排序好的主鍵id和position睦袖，在從原來(lái)表中根據(jù)id查詢(xún)數(shù)據(jù)給客戶(hù)端。
3.3單路和雙路排序方式的選擇及優(yōu)缺點(diǎn)
Mysql可以通過(guò) max_length_for_sort_data 默認(rèn)是1024字節(jié)大小
A.如果查詢(xún)字段總的長(zhǎng)度大小比設(shè)定的max_length_for_sort_data 要小荣刑，則使用單路排序方式扣泊；
B.如果查詢(xún)字段總的長(zhǎng)度大小比設(shè)定的max_length_for_sort_data 要大，則使用多路排序方式
單路排序算法：sort buffer 緩沖區(qū)緩存整個(gè)行的數(shù)據(jù)嘶摊，在根據(jù)排序字段進(jìn)行
排序，返回?cái)?shù)據(jù)給客戶(hù)端评矩，缺點(diǎn)：占用內(nèi)存 優(yōu)點(diǎn)：避免回表查詢(xún)
雙路排序算法：sort buffer 緩沖區(qū)只緩存主鍵id和排序字段在內(nèi)存中叶堆，在根據(jù)排序字段進(jìn)行排序，在做一次回表查詢(xún)斥杜，根據(jù)主鍵id查詢(xún)?cè)撔袛?shù)據(jù)返回給客戶(hù)端虱颗。
4.單雙路排序驗(yàn)證方式
optimizer_trace可分析sql語(yǔ)句
-- 設(shè)置開(kāi)啟
set optimizer_trace='enabled=on',end_markers_in_json=on;
-- 以下兩條語(yǔ)句同時(shí)執(zhí)行沥匈，可查看sql語(yǔ)句的執(zhí)行分析情況
select * from employees where name > 'jarye' ;
select * from information_schema.optimizer_trace;
--關(guān)閉trace
set session optimizer_trace="enabled=off";
"join_preparation": --第一階段：SQl準(zhǔn)備階段
"join_optimization": --第二階段：SQL優(yōu)化階段
"rows_estimation": [ --預(yù)估標(biāo)的訪問(wèn)成本
{
"table": "`employees`",
"range_analysis": { --全表掃描情況
"table_scan": {
"rows": 68511, --掃描行數(shù)
"cost": 13929 --查詢(xún)成本
} /* table_scan */,
"potential_range_indexes": [ --查詢(xún)可能使用的索引
{
"index": "PRIMARY", --主鍵索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", --輔助索引
……
"analyzing_range_alternatives": { ‐‐分析各個(gè)索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"mayikt < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 34255, --‐‐索引掃描行數(shù)
"cost": 41107, --索引使用成本
"chosen": false, ‐‐是否選擇該索引
"cause": "cost"
}
"join_execution": --第三階段：SQL執(zhí)行階段
單路排序：
用trace工具可以看到sort_mode信息里顯示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >；
雙路排序（回表查詢(xún)）：
用trace工具可以看到sort_mode信息里顯示< sort_key, rowid >忘渔；
推薦閱讀：
<<<MySQL執(zhí)行計(jì)劃示例解讀
<<<MySQL性能優(yōu)化之慢查詢(xún)定位
<<<MySQL性能優(yōu)化之表設(shè)計(jì)優(yōu)化
<<<MySQL性能優(yōu)化之常用SQL語(yǔ)句優(yōu)化
<<<MySQL性能優(yōu)化之索引調(diào)優(yōu)實(shí)戰(zhàn)
<<<MySQL性能優(yōu)化之分頁(yè)查詢(xún)優(yōu)化
<<<MySQL性能優(yōu)化之關(guān)聯(lián)查詢(xún)優(yōu)化
<<<MySQL性能優(yōu)化之in高帖、exists優(yōu)化
<<<MySQL如何性能優(yōu)化面試題完美解答
