CREATETABLE`employees`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='員工記錄表';
INSERTINTOemployees(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW());
INSERTINTOemployees(name,age,position,hire_time)VALUES('HanMeimei',23,'dev',NOW());
INSERTINTOemployees(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());
Mysql如何選擇合適的索引
mysql> EXPLAIN select * from employees where name > 'a';
如果用name索引需要遍歷name字段聯(lián)合索引樹，然后還需要根據(jù)遍歷出來的主鍵值去主鍵索引樹里再去查出最終數(shù)據(jù)，成本比全表掃描 還高，可以用覆蓋索引優(yōu)化，這樣只需要遍歷name字段的聯(lián)合索引樹就能拿到所有結(jié)果，如下:
mysql> EXPLAIN select name,age,position from employees where name > 'a' ;
mysql> EXPLAIN select * from employees where name > 'zzz' ;
因為sql表中name>'zzz'的數(shù)據(jù)量非常少
對于上面這兩種 name>'a' 和 name>'zzz' 的執(zhí)行結(jié)果，mysql最終是否選擇走索引或者一張表涉及多個索引，mysql最 終如何選擇索引，我們可以用trace工具來一查究竟，開啟trace工具會影響mysql性能，所以只能臨時分析sql使用，用 完之后立即關(guān)閉
trace工具用法:
. mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐開啟trace
. 2 mysql> select * from employees where name > 'a' order by position;
. 3 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
4
5 查看trace字段: 6{7 "steps": [ 8{
. 9 "join_preparation": { ‐‐第一階段:SQL準(zhǔn)備階段
. 10 "select#": 1,
. 11 "steps": [
. 12 {
. 13 "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`emp
oyees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
. 14 }
. 15 ] /* steps */
. 16 } /* join_preparation */
l
. 17 },
. 18 {
. 19 "join_optimization": { ‐‐第二階段:SQL優(yōu)化階段
. 20 "select#": 1,
. 21 "steps": [
. 22 {
. 23 "condition_processing": { ‐‐條件處理
. 24 "condition": "WHERE",
. 25 "original_condition": "(`employees`.`name` > 'a')",
. 26 "steps": [
. 27 {
. 28 "transformation": "equality_propagation",
. 29 "resulting_condition": "(`employees`.`name` > 'a')"
. 30 },
. 31 {
. 32 "transformation": "constant_propagation",
. 33 "resulting_condition": "(`employees`.`name` > 'a')"
. 34 },
. 35 {
. 36 "transformation": "trivial_condition_removal",
. 37 "resulting_condition": "(`employees`.`name` > 'a')"
. 38 }
. 39 ] /* steps */
. 40 } /* condition_processing */
. 41 },
. 42 {
. 43 "substitute_generated_columns": {
. 44 } /* substitute_generated_columns */
. 45 },
. 46 {
. 47 "table_dependencies": [ ‐‐表依賴詳情
. 48 {
. 49 "table": "`employees`",
. 50 "row_may_be_null": false,
. 51 "map_bit": 0,
. 52 "depends_on_map_bits": [
. 53 ] /* depends_on_map_bits */
. 54 }
. 55 ] /* table_dependencies */
. 56 },
. 57 {
. 58 "ref_optimizer_key_uses": [
. 59 ] /* ref_optimizer_key_uses */
. 60 },
. 61 {
. 62 "rows_estimation": [ ‐‐預(yù)估表的訪問成本
. 63 {
. 64 "table": "`employees`",
. 65 "range_analysis": {
. 66 "table_scan": { ‐‐全表掃描情況
. 67 "rows": 10123, ‐‐掃描行數(shù)
. 68 "cost": 2054.7 ‐‐查詢成本
. 69 } /* table_scan */,
. 70 "potential_range_indexes": [ ‐‐查詢可能使用的索引
. 71 {
. 72 "index": "PRIMARY", ‐‐主鍵索引
. 73 "usable": false,
. 74 "cause": "not_applicable"
. 75 },
. 76 {
. 77 "index": "idx_name_age_position", ‐‐輔助索引
. 78 "usable": true,
. 79 "key_parts": [
. 80 "name",
. 81 "age",
. 82 "position",
. 83 "id"
. 84 ] /* key_parts */
. 85 }
. 86 ] /* potential_range_indexes */,
. 87 "setup_range_conditions": [
. 88 ] /* setup_range_conditions */,
. 89 "group_index_range": {
. 90 "chosen": false,
. 91 "cause": "not_group_by_or_distinct"
. 92 } /* group_index_range */,
. 93 "analyzing_range_alternatives": { ‐‐分析各個索引使用成本
. 94 "range_scan_alternatives": [
. 95 {
. 96 "index": "idx_name_age_position",
. 97 "ranges": [
. 98 "a < name" ‐‐索引使用范圍
. 99 ] /* ranges */,
. 100 "index_dives_for_eq_ranges": true,
. 101 "rowid_ordered": false, ‐‐使用該索引獲取的記錄是否按照主鍵排序
. 102 "using_mrr": false,
. 103 "index_only": false, ‐‐是否使用覆蓋索引
. 104 "rows": 5061, ‐‐索引掃描行數(shù)
. 105 "cost": 6074.2, ‐‐索引使用成本
. 106 "chosen": false, ‐‐是否選擇該索引
. 107 "cause": "cost"
. 108 }
. 109 ] /* range_scan_alternatives */,
. 110 "analyzing_roworder_intersect": {
. 111 "usable": false,
. 112 "cause": "too_few_roworder_scans"
. 113 }
. 114 }
. 115 }
. 116 }
. 117 ]
. 118 },
. 119 {
. 120 "considered_execution_plans": [
. 121 {
/* analyzing_roworder_intersect */
/* analyzing_range_alternatives */
/* range_analysis */
/* rows_estimation */
. 122 "plan_prefix": [
. 123 ] /* plan_prefix */,
. 124 "table": "`employees`",
. 125 "best_access_path": { ‐‐最優(yōu)訪問路徑
. 126 "considered_access_paths": [ ‐‐最終選擇的訪問路徑
. 127 {
. 128 "rows_to_scan": 10123,
. 129 "access_type": "scan", ‐‐訪問類型:為scan，全表掃描
. 130 "resulting_rows": 10123,
. 131 "cost": 2052.6,
. 132 "chosen": true, ‐‐確定選擇
. 133 "use_tmp_table": true
. 134 }
. 135 ] /* considered_access_paths */
. 136 } /* best_access_path */,
. 137 "condition_filtering_pct": 100,
. 138 "rows_for_plan": 10123,
. 139 "cost_for_plan": 2052.6,
. 140 "sort_cost": 10123,
. 141 "new_cost_for_plan": 12176,
. 142 "chosen": true
. 143 }
. 144 ] /* considered_execution_plans */
. 145 },
. 146 {
. 147 "attaching_conditions_to_tables": {
. 148 "original_condition": "(`employees`.`name` > 'a')",
. 149 "attached_conditions_computation": [
. 150 ] /* attached_conditions_computation */,
. 151 "attached_conditions_summary": [
. 152 {
. 153 "table": "`employees`",
. 154 "attached": "(`employees`.`name` > 'a')"
. 155 }
. 156 ] /* attached_conditions_summary */
. 157 } /* attaching_conditions_to_tables */
. 158 },
. 159 {
. 160 "clause_processing": {
. 161 "clause": "ORDER BY",
. 162 "original_clause": "`employees`.`position`",
. 163 "items": [
. 164 {
. 165 "item": "`employees`.`position`"
. 166 }
. 167 ] /* items */,
. 168 "resulting_clause_is_simple": true,
. 169 "resulting_clause": "`employees`.`position`"
. 170 } /* clause_processing */
. 171 },
. 172 {
. 173 "reconsidering_access_paths_for_index_ordering": {
. 174 "clause": "ORDER BY",
常見sql深入優(yōu)化
Order by與Group by優(yōu)化 Case1:
分析:
. 175 "steps": [
. 176 ] /* steps */,
. 177 "index_order_summary": {
. 178 "table": "`employees`",
. 179 "index_provides_order": false,
. 180 "order_direction": "undefined",
. 181 "index": "unknown",
. 182 "plan_changed": false
. 183 } /* index_order_summary */
. 184 } /* reconsidering_access_paths_for_index_ordering */
. 185 },
. 186 {
. 187 "refine_plan": [
. 188 {
. 189 "table": "`employees`"
. 190 }
. 191 ] /* refine_plan */
. 192 }
. 193 ] /* steps */
. 194 } /* join_optimization */
. 195 },
. 196 {
. 197 "join_execution": { ‐‐第三階段:SQL執(zhí)行階段
. 198 "select#": 1,
. 199 "steps": [
. 200 ] /* steps */
. 201 } /* join_execution */
. 202 }
. 203 ] /* steps */
. 204 }
205206 結(jié)論:全表掃描的成本低于索引掃描，所以mysql最終選擇全表掃描 207
. 208 mysql> select * from employees where name > 'zzz' order by position;
. 209 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
210211 查看trace字段可知索引掃描的成本低于全表掃描，所以mysql最終選擇索引掃描 212213 mysql> set session optimizer_trace="enabled=off"; ‐‐關(guān)閉trace
常見sql深入優(yōu)化
Order by與Group by優(yōu)化
Case1:
利用最左前綴法則:中間字段不能斷，因此查詢用到了name索引，從key_len=74也能看出，age索引列用 在排序過程中，因為Extra字段里沒有using filesort
Case 2:
分析: 從explain的執(zhí)行結(jié)果來看:key_len=74，查詢使用了name索引，由于用了position進行排序，跳過了 age，出現(xiàn)了Using filesort。
Case 3:
分析:
查找只用到索引name，age和position用于排序，無Using filesort。
Case 4:
分析:
和Case 3中explain的執(zhí)行結(jié)果一樣，但是出現(xiàn)了Using filesort，因為索引的創(chuàng)建順序為 name,age,position，但是排序的時候age和position顛倒位置了。
Case 5:
分析:
與Case 4對比，在Extra中并未出現(xiàn)Using filesort，因為age為常量，在排序中被優(yōu)化，所以索引未顛倒， 不會出現(xiàn)Using filesort。
Case 6:
分析:
雖然排序的字段列與索引順序一樣，且order by默認(rèn)升序，這里position desc變成了降序，導(dǎo)致與索引的 排序方式不同，從而產(chǎn)生Using filesort。Mysql8以上版本有降序索引可以支持該種查詢方式。
Case 7:
分析: 對于排序來說，多個相等條件也是范圍查詢
Case 8:
優(yōu)化總結(jié):
1、MySQL支持兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序。index
效率高，filesort效率低。
2、order by滿足兩種情況會使用Using index。
......order by語句使用索引最左前列。
.......使用where子句與order by子句條件列組合滿足索引最左前列。
3、盡量在索引列上完成排序，遵循索引建立(索引創(chuàng)建的順序)時的最左前綴法則。
4、如果order by的條件不在索引列上，就會產(chǎn)生Using filesort。
5、能用覆蓋索引盡量用覆蓋索引
6、group by與order by很類似，其實質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最左前綴法則。對于group by的優(yōu)化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能寫在where中 的限定條件就不要去having限定了。
Using filesort文件排序原理詳解 filesort文件排序方式
單路排序:是一次性取出滿足條件行的所有字段，然后在sort buffer中進行排序;用trace工具可 以看到sort_mode信息里顯示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
雙路排序(又叫回表排序模式):是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行 數(shù)據(jù)的行 ID，然后在 sort buffer 中進行排序，排序完后需要再次取回其它需要的字段;用trace工具 可以看到sort_mode信息里顯示< sort_key, rowid >
MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data(默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來 判斷使用哪種排序模式。
如果 max_length_for_sort_data 比查詢字段的總長度大，那么使用 單路排序模式; 如果 max_length_for_sort_data 比查詢字段的總長度小，那么使用 雙路排序模式。
我們先看單路排序的詳細(xì)過程:
- 從索引name找到第一個滿足 name = ‘zhuge’ 條件的主鍵 id 2. 根據(jù)主鍵 id 取出整行，取出所有字段的值，存入 sort_buffer 中 3. 從索引name找到下一個滿足 name = ‘zhuge’ 條件的主鍵 id 4. 重復(fù)步驟 2、3 直到不滿足 name = ‘zhuge’
- 對 sort_buffer 中的數(shù)據(jù)按照字段 position 進行排序
- 返回結(jié)果給客戶端
我們再看下雙路排序的詳細(xì)過程: - 從索引 name 找到第一個滿足 name = ‘zhuge’ 的主鍵id
- 根據(jù)主鍵 id 取出整行，把排序字段 position 和主鍵 id 這兩個字段放到 sort buffer 中 3. 從索引 name 取下一個滿足 name = ‘zhuge’ 記錄的主鍵 id
- 重復(fù) 3、4 直到不滿足 name = ‘zhuge’
- 對 sort_buffer 中的字段 position 和主鍵 id 按照字段 position 進行排序
- 遍歷排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回給客戶端
其實對比兩個排序模式，單路排序會把所有需要查詢的字段都放到 sort buffer 中，而雙路排序只會把主鍵 和需要排序的字段放到 sort buffer 中進行排序，然后再通過主鍵回到原表查詢需要的字段。
如果 MySQL 排序內(nèi)存配置的比較小并且沒有條件繼續(xù)增加了，可以適當(dāng)把 max_length_for_sort_data 配 置小點，讓優(yōu)化器選擇使用雙路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根據(jù)主鍵 回到原表取數(shù)據(jù)。
如果 MySQL 排序內(nèi)存有條件可以配置比較大，可以適當(dāng)增大 max_length_for_sort_data 的值，讓優(yōu)化器 優(yōu)先選擇全字段排序(單路排序)，把需要的字段放到 sort_buffer 中，這樣排序后就會直接從內(nèi)存里返回查 詢結(jié)果了。
所以，MySQL通過 max_length_for_sort_data 這個參數(shù)來控制排序，在不同場景使用不同的排序模式， 從而提升排序效率。
注意，如果全部使用sort_buffer內(nèi)存排序一般情況下效率會高于磁盤文件排序，但不能因為這個就隨便增 大sort_buffer(默認(rèn)1M)，mysql很多參數(shù)設(shè)置都是做過優(yōu)化的，不要輕易調(diào)整。
