Mysql索引優化（一）
今天學習了Mysql索引的優化這是第一篇後續還有第二篇，這裡記錄一下學習筆記，有錯誤，還望指出。
文章目錄
前言
提示：這裡提供一個示例表
CREATE TABLE `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=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
-- 插入一些示例資料
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
綜合例子
1、聯合索引第一個欄位用範圍不會走索引
EXPLAIN SELECT * FROM employees WHERE name > ‘LiLei’ AND age = 22 AND position =‘manager’;
結論：聯合索引第一個欄位就用範圍查詢不會走索引，mysql內部可能覺得第一個欄位就用範圍，結果集應該很大，回表效率不高，還不如就全表掃描
2、強制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > ‘LiLei’ AND age = 22 A ND position =‘manager’;
結論：雖然使用了強制走索引讓聯合索引第一個欄位範圍查詢也走索引，掃描的行rows看上去也少了點，但是最終查詢效率不一定比全表 掃描高，因為回表效率不高
可以做一個測試：
‐‐ 關閉查詢快取
set global query_cache_size=0;
set global query_cache_type=0;
‐‐ 執行時間0.333s
SELECT * FROM employees WHERE name > ‘LiLei’;
‐‐ 執行時間0.444s
7 SELECT * FROM employees force index(idx_name_age_position) WHERE name > ‘LiLei’;
3、覆蓋索引優化
EXPLAIN SELECT name,age,position FROM employees WHERE name > ‘LiLei’ AND age = 22 AND position =‘manager’;
4、in和or在表資料量比較大的情況會走索引，在表記錄不多的情況下會選擇全表掃描
EXPLAIN SELECT * FROM employees WHERE name in (‘LiLei’,‘HanMeimei’,‘Lucy’) AND age = 22 AND position =‘manager’;
EXPLAIN SELECT * FROM employees WHERE (name = ‘LiLei’ or name = ‘HanMeimei’) AND age = 22 AND position =‘manager’;
in和or在表資料量如果比較小的話索引則會失效我們做一個小測試，將employees 表複製一張employees_copy的表，裡面只保留兩三條記錄
EXPLAIN SELECT * FROM employees_copy WHERE name in (‘LiLei’,‘HanMeimei’,‘Lucy’) AND age = 22 AND posit ion =‘manager’;
EXPLAIN SELECT * FROM employees_copy WHERE (name = ‘LiLei’ or name = ‘HanMeimei’) AND age = 22 AND pos ition =‘manager’;
5、like KK% 一般情況都會走索引
EXPLAIN SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’;
EXPLAIN SELECT * FROM employees_copy WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’;
可以看出不管是大資料的表還是少量資料的表like都會走索引，這裡給大家補充一個概念，索引下推（Index Condition Pushdown，ICP）, like KK%其實就是用到了索引下推優化
什麼是索引下推了？
對於輔助的聯合索引(name,age,position)，正常情況按照最左字首原則，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 這種情況只會走name欄位索引，因為根據name欄位過濾完，得到的索引行裡的age和 position是無序的，無法很好的利用索引。
在MySQL5.6之前的版本，這個查詢只能在聯合索引裡匹配到名字是 ‘LiLei’ 開頭的索引，然後拿這些索引對應的主鍵逐個回表，到主鍵索 引上找出相應的記錄，再比對age和position這兩個欄位的值是否符合。
MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的所有欄位先做判斷，過濾掉不符合條件的記錄之後再回表，可 以有效的減少回表次數。使用了索引下推優化後，上面那個查詢在聯合索引裡匹配到名字是 ‘LiLei’ 開頭的索引之後，同時還會在索引裡過 濾age和position這兩個欄位，拿著過濾完剩下的索引對應的主鍵id再回表查整行資料。
索引下推會減少回表次數，對於innodb引擎的表索引下推只能用於二級索引，innodb的主鍵索引（聚簇索引）樹葉子節點上儲存的是全 行資料，所以這個時候索引下推並不會起到減少查詢全行資料的效果。
為什麼範圍查詢Mysql沒有用索引下推優化？
估計應該是Mysql認為範圍查詢過濾的結果集過大，like KK% 在絕大多數情況來看，過濾後的結果集比較小，所以這裡Mysql選擇給 like KK% 用了索引下推優化，當然這也不是絕對的，有時like KK% 也不一定就會走索引下推。
Mysql如何選擇合適的索引
mysql> EXPLAIN select * from employees where name > ‘a’;
如果用name索引需要遍歷name欄位聯合索引樹，然後還需要根據遍歷出來的主鍵值去主鍵索引樹裡再去查出最終資料，成本比全表掃描還高，可以用覆蓋索引優化，這樣只需要遍歷name欄位的聯合索引樹就能拿到所有結果，如下：
mysql> EXPLAIN select name,age,position from employees where name > ‘a’ ;
mysql> EXPLAIN select * from employees where name > ‘zzz’ ;
對於上面這兩種 name>‘a’ 和 name>‘zzz’ 的執行結果，mysql最終是否選擇走索引或者一張表涉及多個索引，mysql最 終如何選擇索引，我們可以用trace工具來一查究竟，開啟trace工具會影響mysql效能，所以只能臨時分析sql使用，用 完之後立即關閉
trace工具
trace工具用法：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --開啟trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; --這裡要注意兩天SQL語句要一起執行
檢視trace欄位：
{
"steps": [
{
"join_preparation": { --第一階段：SQL準備階段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二階段：SQL優化階段
"select#": 1,
"steps": [
{
"condition_processing": { --條件處理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ --表依賴詳情
{
"table": "`employees`",
"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": [ --預估表的訪問成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { --全表掃描情況
"rows": 10123, --掃描行數
"cost": 2054.7 --查詢成本
} /* table_scan */,
"potential_range_indexes": [ --查詢可能使用的索引
{
"index": "PRIMARY", --主鍵索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", --輔助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"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_name_age_position",
"ranges": [
"a < name" --索引使用範圍
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, --使用該索引獲取的記錄是否按照主鍵排序
"using_mrr": false,
"index_only": false, --是否使用覆蓋索引
"rows": 5061, --索引掃描行數
"cost": 6074.2, --索引使用成本
"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": "`employees`",
"best_access_path": { --最優訪問路徑
"considered_access_paths": [ --最終選擇的訪問路徑
{
"rows_to_scan": 10123,
"access_type": "scan", --訪問型別：為scan，全表掃描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, --確定選擇
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三階段：SQL執行階段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
結論：全表掃描的成本低於索引掃描，所以mysql最終選擇全表掃描
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
檢視trace欄位可知索引掃描的成本低於全表掃描，所以mysql最終選擇索引掃描
mysql> set session optimizer_trace="enabled=off"; --關閉trace
trace主要有三個階段
第一階段：SQL準備階段，格式化sql，這個階段主要對我們的SQL語句做一個美化
第二階段：SQL優化階段，這個階段幫助我們做一些優化例如過濾一些沒有意義的調教例如where 1=1，或者幫我們對最左字首欄位做一個排序，當然這些建議我們自己做沒有必要浪費Mysql的資源。還可以看到“analyzing_range_alternatives”會分析每個索引計算他們的cost成本來選擇是全表掃描或選擇索引。
第三階段：SQL執行階段，顧名思義。
常見sql深入優化
Order by與Group by優化
Case1：
分析：根據最左字首法則：中間欄位不能斷，因此查詢用到了name索引，從key_len=74也能看出，age索引列用在排序過程中，因為Extra欄位裡沒有using filesort
Case2：
分析：從explain的執行結果來看：key_len=74，查詢使用了name索引，由於用了position進行排序，跳過了 age，出現了Using filesort。
Case3：
分析： 查詢只用到索引name，age和position用於排序，但是它還是遵循了最左字首原則，無Using filesort。
Case4：
分析： 和Case 3中explain的執行結果一樣，但是出現了Using filesort，因為索引的建立順序為 name,age,position，但是排序的時候age和position顛倒位置了。
Case5：
分析： 與Case 4對比，在Extra中並未出現Using filesort，因為age為常量，在排序中被優化，所以索引未顛倒， 不會出現Using filesort。
Case6：
分析： 雖然排序的欄位列與索引順序一樣，且order by預設升序，這裡position desc變成了降序，導致與索引的排序方式不同，從而產生Using filesort。Mysql8以上版本有降序索引可以支援該種查詢方式。
Case7：
分析： 對於排序來說，多個相等條件也是範圍查詢
Case8：
可以用覆蓋索引優化
優化總結：
1、MySQL支援兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序。index 效率高，filesort效率低。
2、order by滿足兩種情況會使用Using index。
- order by語句使用索引最左前列。
- 使用where子句與order by子句條件列組合滿足索引最左前列。
3、儘量在索引列上完成排序，遵循索引建立（索引建立的順序）時的最左字首法則。
4、如果order by的條件不在索引列上，就會產生Using filesort。
5、能用覆蓋索引儘量用覆蓋索引
6、group by與order by很類似，其實質是先排序後分組，遵照索引建立順序的最左字首法則。對於group by的優化如果不需要排序的可以加上order by null禁止排序。注意，where高於having，能寫在where中 的限定條件就不要去having限定了。
Using filesort檔案排序原理詳解
filesort檔案排序方式
- 單路排序：是一次性取出滿足條件行的所有欄位，然後在sort buffer中進行排序；用trace工具可 以看到sort_mode資訊裡顯示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
- 雙路排序（又叫回表排序模式）：是首先根據相應的條件取出相應的排序欄位和可以直接定位行 資料的行 ID，然後在 sort buffer 中進行排序，排序完後需要再次取回其它需要的欄位；用trace工具 可以看到sort_mode資訊裡顯示< sort_key, rowid >
MySQL 通過比較系統變數 max_length_for_sort_data(預設1024位元組) 的大小和需要查詢的欄位總大小來
- 如果 欄位的總長度小於max_length_for_sort_data ，那麼使用 單路排序模式；
- 如果 欄位的總長度大於max_length_for_sort_data ，那麼使用 雙路排序模∙式。
示例驗證下各種排序方式：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --開啟trace
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分結果：
"join_execution": { --Sql執行階段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --檔案排序資訊
"rows": 10000, --預計掃描行數
"examined_rows": 10000, --參與排序的行
"number_of_tmp_files": 3, --使用臨時檔案的個數，這個值如果為0代表全部使用的sort_buffer記憶體排序，否則使用的磁碟檔案排序
"sort_buffer_size": 262056, --排序快取的大小，單位Byte
"sort_mode": "<sort_key, packed_additional_fields>" --排序方式，這裡用的單路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set max_length_for_sort_data = 10; --employees表所有欄位長度總和肯定大於10位元組
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分結果：
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 10000,
"examined_rows": 10000,
"number_of_tmp_files": 2,
"sort_buffer_size": 262136,
"sort_mode": "<sort_key, rowid>" --排序方式，這裡用的雙路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set session optimizer_trace="enabled=off"; --關閉trace
我們先看單路排序的詳細過程：
- 從索引name找到第一個滿足 name = ‘zhuge’ 條件的主鍵 id
- 根據主鍵 id 取出整行，取出所有欄位的值，存入 sort_buffer 中
- 從索引name找到下一個滿足 name = ‘zhuge’ 條件的主鍵 id
- 重複步驟 2、3 直到不滿足 name = ‘zhuge’
- 對 sort_buffer 中的資料按照欄位 position 進行排序
- 返回結果給客戶端
我們再看下雙路排序的詳細過程：
- 從索引 name 找到第一個滿足 name = ‘zhuge’ 的主鍵id
- 根據主鍵 id 取出整行，把排序欄位 position 和主鍵 id 這兩個欄位放到 sort buffer 中
- 從索引 name 取下一個滿足 name = ‘zhuge’ 記錄的主鍵 id
- 重複 3、4 直到不滿足 name = ‘zhuge’
- 對 sort_buffer 中的欄位 position 和主鍵 id 按照欄位 position 進行排序
- 遍歷排序好的 id 和欄位 position，按照 id 的值回到原表中取出 所有欄位的值返回給客戶端
其實對比兩個排序模式，單路排序會把所有需要查詢的欄位都放到 sort buffer 中，而雙路排序只會把主鍵 和需要排序的欄位放到 sort buffer 中進行排序，然後再通過主鍵回到原表查詢需要的欄位。 如果 MySQL 排序記憶體 sort_buffer 配置的比較小並且沒有條件繼續增加了，可以適當把 max_length_for_sort_data 配置小點，讓優化器選擇使用雙路排序演算法，可以在sort_buffer 中一次排序更 多的行，只是需要再根據主鍵回到原表取資料。
如果 MySQL 排序記憶體有條件可以配置比較大，可以適當增大 max_length_for_sort_data 的值，讓優化器 優先選擇全欄位排序(單路排序)，把需要的欄位放到 sort_buffer 中，這樣排序後就會直接從記憶體裡返回查詢結果了。 所以，MySQL通過 max_length_for_sort_data 這個引數來控制排序，在不同場景使用不同的排序模式， 從而提升排序效率。
注意，如果全部使用sort_buffer記憶體排序一般情況下效率會高於磁碟檔案排序，但不能因為這個就隨便增 大sort_buffer(預設1M)，mysql很多引數設定都是做過優化的，不要輕易調整。
索引設計原則
1、程式碼先行，索引後上
不知大家一般是怎麼給資料表建立索引的，是建完表馬上就建立索引嗎？
這其實是不對的，一般應該等到主體業務功能開發完畢，把涉及到該表相關sql都要拿出來分析之後再建立 索引。
2、聯合索引儘量覆蓋條件
比如可以設計一個或者兩三個聯合索引(儘量少建單值索引)，讓每一個聯合索引都儘量去包含sql語句裡的 where、order by、group by的欄位，還要確保這些聯合索引的欄位順序儘量滿足sql查詢的最左字首原 則。
3、不要在小基數字段上建立索引
索引基數是指這個欄位在表裡總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別欄位， 其值不是男就是女，那麼該欄位的基數就是2。
如果對這種小基數字段建立索引的話，還不如全表掃描了，因為你的索引樹裡就包含男和女兩種值，根本沒 法進行快速的二分查詢，那用索引就沒有太大的意義了。
一般建立索引，儘量使用那些基數比較大的欄位，就是值比較多的欄位，那麼才能發揮出B+樹快速二分查 找的優勢來。
4、長字串我們可以採用字首索引
儘量對欄位型別較小的列設計索引，比如說什麼tinyint之類的，因為欄位型別較小的話，佔用磁碟空間也會 比較小，此時你在搜尋的時候效能也會比較好一點。
當然，這個所謂的欄位型別小一點的列，也不是絕對的，很多時候你就是要針對varchar(255)這種欄位建立 索引，哪怕多佔用一些磁碟空間也是有必要的。
對於這種varchar(255)的大欄位可能會比較佔用磁碟空間，可以稍微優化下，比如針對這個欄位的前20個 字元建立索引，就是說，對這個欄位裡的每個值的前20個字元放在索引樹裡，類似於 KEY index(name(20),age,position)。
此時你在where條件裡搜尋的時候，如果是根據name欄位來搜尋，那麼此時就會先到索引樹里根據name 欄位的前20個字元去搜尋，定位到之後前20個字元的字首匹配的部分資料之後，再回到聚簇索引提取出來 完整的name欄位值進行比對。
但是假如你要是order by name，那麼此時你的name因為在索引樹裡僅僅包含了前20個字元，所以這個排 序是沒法用上索引的， group by也是同理。所以這裡大家要對字首索引有一個瞭解。
5、where與order by衝突時優先where
在where和order by出現索引設計衝突時，到底是針對where去設計索引，還是針對order by設計索引？到 底是讓where去用上索引，還是讓order by用上索引？
一般這種時候往往都是讓where條件去使用索引來快速篩選出來一部分指定的資料，接著再進行排序。 因為大多數情況基於索引進行where篩選往往可以最快速度篩選出你要的少部分資料，然後做排序的成本可 能會小很多。
6、基於慢sql查詢做優化
可以根據監控後臺的一些慢sql，針對這些慢sql查詢做特定的索引優化。 關於慢sql查詢不清楚的可以參考這篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740
索引設計實戰
以社交場景APP來舉例，我們一般會去搜尋一些好友，這裡面就涉及到對使用者資訊的篩選，這裡肯定就是對 使用者user表搜尋了，這個表一般來說資料量會比較大，我們先不考慮分庫分表的情況，比如，我們一般會篩選地區(省市)，性別，年齡，身高，愛好之類的，有的APP可能使用者還有評分，比如使用者的受歡迎程度評 分，我們可能還會根據評分來排序等等。
對於後臺程式來說除了過濾使用者的各種條件，還需要分頁之類的處理，可能會生成類似sql語句執行：
select xx from user where xx=xx and xx=xx order by xx limit xx,xx
對於這種情況如何合理設計索引了，比如使用者可能經常會根據省市優先篩選同城的使用者，還有根據性別去篩 選，那我們是否應該設計一個聯合索引 (province,city,sex) 了？這些欄位好像基數都不大，其實是應該的， 因為這些欄位查詢太頻繁了。
假設又有使用者根據年齡範圍去篩選了，比如 where province=xx and city=xx and age>=xx and age<=xx，我們嘗試著把age欄位加入聯合索引 (province,city,sex,age)，注意，一般這種範圍查詢的條件 都要放在最後，之前講過聯合索引範圍之後條件的是不能用索引的，但是對於當前這種情況依然用不到age 這個索引欄位，因為使用者沒有篩選sex欄位，那怎麼優化了？其實我們可以這麼來優化下sql的寫法：where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx 對於愛好之類的欄位也可以類似sex欄位處理，所以可以把愛好欄位也加入索引 (province,city,sex,hobby,age)
假設可能還有一個篩選條件，比如要篩選最近一週登入過的使用者，一般大家肯定希望跟活躍使用者交友了，這 樣能儘快收到反饋，對應後臺sql可能是這樣： where province=xx and city=xx and sex in (‘female’,‘male’) and age>=xx and age<=xx and latest_login_time>= xx
那我們是否能把 latest_login_time 欄位也加入索引了？比如 (province,city,sex,hobby,age,latest_login_time) ，顯然是不行的，那怎麼來優化這種情況了？其實我們 可以試著再設計一個欄位is_login_in_latest_7_days，使用者如果一週內有登入值就為1，否則為0，那麼我們 就可以把索引設計成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 來滿足上面那種場景了！ 一般來說，通過這麼一個多欄位的索引是能夠過濾掉絕大部分資料的，就保留小部分資料下來基於磁碟檔案 進行order by語句的排序，最後基於limit進行分頁，那麼一般效能還是比較高的。
不過有時可能使用者會這麼來查詢，就查下受歡迎度較高的女性，比如sql：where sex = ‘female’ order by score limit xx,xx，那麼上面那個索引是很難用上的，不能把太多的欄位以及太多的值都用 in 語句拼接 到sql裡的，那怎麼辦了？其實我們可以再設計一個輔助的聯合索引，比如 (sex,score)，這樣就能滿足查詢 要求了。
以上就是給大家講的一些索引設計的思路了，核心思想就是，儘量利用一兩個複雜的多欄位聯合索引，抗下 你80%以上的查詢，然後用一兩個輔助索引儘量抗下剩餘的一些非典型查詢，保證這種大資料量表的查詢盡 可能多的都能充分利用索引，這樣就能保證你的查詢速度和效能了！
一切偉大的行動和思想，都有一個微不足道的開始。
相關文章
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- MySQL 效能優化之索引優化MySql優化索引
- MySQL 5.7 索引優化MySql索引優化
- MySQL調優之索引優化MySql索引優化
- MySQL 字串索引優化方案MySql字串索引優化
- MySQL 索引原理以及優化MySql索引優化
- mysql索引原理及優化MySql索引優化
- MySQL——索引優化實戰MySql索引優化
- MySQL 筆記 - 索引優化MySql筆記索引優化
- MySQL優化之索引解析MySql優化索引
- 一篇文章搞定 MySQL 索引優化MySql索引優化
- Mysql索引優化之索引的分類MySql索引優化
- MySQL函式索引及優化MySql函式索引優化
- MySQL 索引使用策略及優化MySql索引優化
- MySQL索引與查詢優化MySql索引優化
- mysql索引的使用和優化MySql索引優化
- mysql優化篇（基於索引）MySql優化索引
- 理解 MySQL（2）：索引與優化MySql索引優化
- MySQL 索引優化全攻略MySql索引優化
- MySQL-效能優化-索引和查詢優化MySql優化索引
- 淺談MySQL的B樹索引與索引優化MySql索引優化
- 「MySQL」高效能索引優化策略MySql索引優化
- MySQL 效能優化——B+Tree 索引MySql優化索引
- MySQL-索引優化全攻略MySql索引優化
- mysql索引優化和TCP協議MySql索引優化TCP協議
- MySQL效能優化之索引設計MySql優化索引
- mysql 語句的索引和優化MySql索引優化
- mysql索引覆蓋掃描優化MySql索引優化
- MySQL SQL 優化之覆蓋索引MySql優化索引
- MYSQL索引優化思維導圖MySql索引優化
- 【MySQL】效能優化之 覆蓋索引MySql優化索引
- 瞭解MySQL-索引與優化MySql索引優化
- MySQL的索引優化分析(一)MySql索引優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- 效能調優-Mysql索引資料結構詳解與索引優化MySql索引資料結構優化
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- Mysql效能優化：如何給字串加索引？MySql優化字串索引