## Mysql通過索引提升查詢效率 ## 1\. 索引基本知識概覽  ##### 索引的優點 1、大大減少了服務器需要掃描的數據量 2、幫助服務器避免排序和臨時表 3、將隨機io變成順序io ##### 索引的用處 1、快速查找匹配WHERE子句的行 2、從consideration中消除行,如果可以在多個索引之間進行選擇，mysql通常會使用找到最少行的索引 3、如果表具有多列索引，則優化器可以使用索引的任何最左前綴來查找行 4、當有表連接的時候，從其他表檢索行數據 5、查找特定索引列的min或max值 6、如果排序或分組時在可用索引的最左前綴上完成的，則對表進行排序和分組 7、在某些情況下，可以優化查詢以檢索值而無需查詢數據行 ##### 索引的分類 主鍵索引/唯一索引/普通索引/全文索引/組合索引 ##### 常用優化原則 回表/覆蓋索引/最左匹配/索引下推 ##### 索引采用的數據結構 哈希表/B+樹/索引匹配方式 create table staffs( id int primary key auto\_increment, name varchar(24) not null default '' comment '姓名', age int not null default 0 comment '年齡', pos varchar(20) not null default '' comment '職位', add\_time timestamp not null default current\_timestamp comment '入職時間' ) charset utf8 comment '員工記錄表'; \-----------alter table staffs add index idx\_nap(name, age, pos); ##### 全值匹配 全值匹配指的是和索引中的所有列進行匹配 explain select \* from staffs where name = 'July' and age = '23' and pos = 'dev'; ##### 匹配最左前綴 只匹配前面的幾列 explain select \* from staffs where name = 'July' and age = '23'; explain select \* from staffs where name = 'July'; ##### 匹配列前綴 可以匹配某一列的值的開頭部分 explain select \* from staffs where name like 'J%'; explain select \* from staffs where name like '%y'; ##### 匹配范圍值 可以查找某一個范圍的數據 explain select \* from staffs where name > 'Mary'; 精確匹配某一列并范圍匹配另外一列 可以查詢第一列的全部和第二列的部分 explain select \* from staffs where name = 'July' and age > 25; ##### 只訪問索引的查詢 查詢的時候只需要訪問索引，不需要訪問數據行，本質上就是覆蓋索引 explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'; ## 2\. HASH索引相關  ##### 哈希索引 基于哈希表的實現，只有精確匹配索引所有列的查詢才有效 在mysql中，只有memory的存儲引擎顯式支持哈希索引 哈希索引自身只需存儲對應的hash值，所以索引的結構十分緊湊，這讓哈希索引查找的速度非常快 ##### 哈希索引的限制 1、哈希索引只包含哈希值和行指針，而不存儲字段值，索引不能使用索引中的值來避免讀取行 2、哈希索引數據并不是按照索引值順序存儲的，所以無法進行排序 3、哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部內容來計算哈希值 4、哈希索引支持等值比較查詢，也不支持任何范圍查詢 5、訪問哈希索引的數據非常快，除非有很多哈希沖突，當出現哈希沖突的時候，存儲引擎必須遍歷鏈表中的所有行指針，逐行進行比較，直到找到所有符合條件的行 6、哈希沖突比較多的話，維護的代價也會很高 ##### SQL案例 當需要存儲大量的URL，并且根據URL進行搜索查找，如果使用B+樹，存儲的內容就會很大 select id from url where url="" 也可以利用將url使用CRC32做哈希，可以使用以下查詢方式： select id fom url where url="" and url\_crc=CRC32("") 此查詢性能較高原因是使用體積很小的索引來完成查找 ## 3\. 組合索引 ##### 組合索引 當包含多個列作為索引，需要注意的是正確的順序依賴于該索引的查詢，同時需要考慮如何更好的滿足排序和分組的需要  案例，建立組合索引a,b,c不同SQL語句使用索引情況  ## 4\. 聚簇和非聚簇索引  ##### 聚簇索引與非聚簇索引 ##### 聚簇索引 不是單獨的索引類型，而是一種數據存儲方式，指的是數據行跟相鄰的鍵值緊湊的存儲在一起 優點 1、可以把相關數據保存在一起 2、數據訪問更快，因為索引和數據保存在同一個樹中 3、使用覆蓋索引掃描的查詢可以直接使用頁節點中的主鍵值 缺點 1、聚簇數據最大限度地提高了IO密集型應用的性能，如果數據全部在內存，那么聚簇索引就沒有什么優勢 2、插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式 3、更新聚簇索引列的代價很高，因為會強制將每個被更新的行移動到新的位置 4、基于聚簇索引的表在插入新行，或者主鍵被更新導致需要移動行的時候，可能面臨頁分裂的問題 5、聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致數據存儲不連續的時候 ##### 非聚簇索引 數據文件跟索引文件分開存放 ## 5.覆蓋索引  ##### 覆蓋索引 基本介紹 1、如果一個索引包含所有需要查詢的字段的值，我們稱之為覆蓋索引 2、不是所有類型的索引都可以稱為覆蓋索引，覆蓋索引必須要存儲索引列的值 3、不同的存儲實現覆蓋索引的方式不同，不是所有的存儲引擎都支持覆蓋索引，memory不支持覆蓋索引 優勢 1、索引條目通常遠小于數據行大小，如果只需要讀取索引，那么mysql就會極大的較少數據訪問量 2、因為索引是按照列值順序存儲的，所以對于IO密集型的范圍查詢會比隨機從磁盤讀取每一行數據的IO要少的多 3、一些存儲引擎如MYISAM在內存中只緩存索引，數據則依賴于操作系統來緩存，因此要訪問數據需要一次系統調用，這可能會導致嚴重的性能問題 4、由于INNODB的聚簇索引，覆蓋索引對INNODB表特別有用 ##### 案例演示 1、當發起一個被索引覆蓋的查詢時，在explain的extra列可以看到using index的信息，此時就使用了覆蓋索引  2、在大多數存儲引擎中，覆蓋索引只能覆蓋那些只訪問索引中部分列的查詢。不過，可以進一步的進行優化，可以使用innodb的二級索引來覆蓋查詢。 例如：actor使用innodb存儲引擎，并在last\_name字段又二級索引，雖然該索引的列不包括主鍵actor\_id，但也能夠用于對actor\_id做覆蓋查詢  ## 6\. 常見優化處理落地方案  #### 常見優化方案 #### 1.當使用索引列進行查詢的時候盡量不要使用表達式，把計算放到業務層而不是數據庫層 select actor\_id from actor where actor\_id=4; select actor\_id from actor where actor\_id+1=5; #### 2.盡量使用主鍵查詢，而不是其他索引，因此主鍵查詢不會觸發回表查詢 #### 3.使用前綴索引 有時候需要索引很長的字符串，這會讓索引變的大且慢，通常情況下可以使用某個列開始的部分字符串，這樣大大的節約索引空間，從而提高索引效率，但這會降低索引的選擇性，索引的選擇性是指不重復的索引值和數據表記錄總數的比值，范圍從1/#T到1之間。索引的選擇性越高則查詢效率越高，因為選擇性更高的索引可以讓mysql在查找的時候過濾掉更多的行。 一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢的性能，但是對應BLOB,TEXT,VARCHAR類型的列，必須要使用前綴索引，因為mysql不允許索引這些列的完整長度，使用該方法的訣竅在于要選擇足夠長的前綴以保證較高的選擇性，通過又不能太長。 案例演示： ~~~csharp --創建數據表 create table citydemo(city varchar(50) not null); insert into citydemo(city) select city from city; --重復執行5次下面的sql語句 insert into citydemo(city) select city from citydemo; --更新城市表的名稱 update citydemo set city=(select city from city order by rand() limit 1); --查找最常見的城市列表，發現每個值都出現45-65次， select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10; --查找最頻繁出現的城市前綴，先從3個前綴字母開始，發現比原來出現的次數更多，可以分別截取多個字符查看城市出現的次數 select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10; select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10; --此時前綴的選擇性接近于完整列的選擇性 --還可以通過另外一種方式來計算完整列的選擇性，可以看到當前綴長度到達7之后，再增加前綴長度，選擇性提升的幅度已經很小了 select count(distinct left(city,3))/count(*) as sel3, count(distinct left(city,4))/count(*) as sel4, count(distinct left(city,5))/count(*) as sel5, count(distinct left(city,6))/count(*) as sel6, count(distinct left(city,7))/count(*) as sel7, count(distinct left(city,8))/count(*) as sel8 from citydemo; --計算完成之后可以創建前綴索引 alter table citydemo add key(city(7)); --注意：前綴索引是一種能使索引更小更快的有效方法，但是也包含缺點：mysql無法使用前綴索引做order by 和 group by。 ~~~ #### 4.使用索引掃描來排序 mysql有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描，如果explain出來的type列的值為index,則說明mysql使用了索引掃描來做排序 掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄。但如果索引不能覆蓋查詢所需的全部列，那么就不得不每掃描一條索引記錄就得回表查詢一次對應的行，這基本都是隨機IO，因此按索引順序讀取數據的速度通常要比順序地全表掃描慢 mysql可以使用同一個索引即滿足排序，又用于查找行，如果可能的話，設計索引時應該盡可能地同時滿足這兩種任務。 只有當索引的列順序和order by子句的順序完全一致，并且所有列的排序方式都一樣時，mysql才能夠使用索引來對結果進行排序，如果查詢需要關聯多張表，則只有當orderby子句引用的字段全部為第一張表時，才能使用索引做排序。order by子句和查找型查詢的限制是一樣的，需要滿足索引的最左前綴的要求，否則，mysql都需要執行順序操作，而無法利用索引排序 ~~~csharp --sakila數據庫中rental表在rental_date,inventory_id,customer_id上有rental_date的索引 --使用rental_date索引為下面的查詢做排序 explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ref possible_keys: rental_date key: rental_date key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) --order by子句不滿足索引的最左前綴的要求，也可以用于查詢排序，這是因為所以你的第一列被指定為一個常數 --該查詢為索引的第一列提供了常量條件，而使用第二列進行排序，將兩個列組合在一起，就形成了索引的最左前綴 explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ref possible_keys: rental_date key: rental_date key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) --下面的查詢不會利用索引 explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALL possible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort --該查詢使用了兩中不同的排序方向，但是索引列都是正序排序的 explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALL possible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) --該查詢中引用了一個不再索引中的列 explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental partitions: NULL type: ALL possible_keys: rental_date key: NULL key_len: NULL ref: NULL rows: 16005 filtered: 50.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) ~~~ union all,in,or都能夠使用索引，但是推薦使用in explain select \* from actor where actor\_id = 1 union all select \* from actor where actor\_id = 2; explain select \* from actor where actor\_id in (1,2); explain select \* from actor where actor\_id = 1 or actor\_id =2; ##### 5.范圍列可以用到索引 范圍條件是： 范圍列可以用到索引，但是范圍列后面的列無法用到索引，索引最多用于一個范圍列 強制類型轉換會全表掃描 create table user(id int,name varchar(10),phone varchar(11)); alter table user add index idx\_1(phone); ##### 不會觸發索引 explain select \* from user where phone=13800001234; ##### 觸發索引 explain select \* from user where phone='13800001234'; #### 常見總結使用規則 1.更新十分頻繁，數據區分度不高的字段上不宜建立索引 2.更新會變更B+樹，更新頻繁的字段建議索引會大大降低數據庫性能 3.類似于性別這類區分不大的屬性，建立索引是沒有意義的，不能有效的過濾數據， 4.一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(\*) 來計算 5.創建索引的列，不允許為null，可能會得到不符合預期的結果 6.當需要進行表連接的時候，最好不要超過三張表，因為需要join的字段，數據類型必須一致 7.能使用limit的時候盡量使用limit 8.單表索引建議控制在5個以內 9.單索引字段數不允許超過5個（組合索引） #### 創建索引的時候應該避免以下錯誤概念 1.索引越多越好 2.過早優化，在不了解系統的情況下進行優化 ## 7.索引監控  #### 索引監控 show status like 'Handler\_read%'; #### 參數解釋 Handler\_read\_first：讀取索引第一個條目的次數 Handler\_read\_key：通過index獲取數據的次數 Handler\_read\_last：讀取索引最后一個條目的次數 Handler\_read\_next：通過索引讀取下一條數據的次數 Handler\_read\_prev：通過索引讀取上一條數據的次數 Handler\_read\_rnd：從固定位置讀取數據的次數 Handler\_read\_rnd\_next：從數據節點讀取下一條數據的次數 ## 8.索引優化實踐案例 ~~~php SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `itdragon_order_list`; CREATE TABLE `itdragon_order_list` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id，默認自增長', `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易號', `gross` double DEFAULT NULL COMMENT '毛收入(RMB)', `net` double DEFAULT NULL COMMENT '凈收入(RMB)', `stock_id` int(11) DEFAULT NULL COMMENT '發貨倉庫', `order_status` int(11) DEFAULT NULL COMMENT '訂單狀態', `descript` varchar(255) DEFAULT NULL COMMENT '客服備注', `finance_descript` varchar(255) DEFAULT NULL COMMENT '財務備注', `create_type` varchar(100) DEFAULT NULL COMMENT '創建類型', `order_level` int(11) DEFAULT NULL COMMENT '訂單級別', `input_user` varchar(20) DEFAULT NULL COMMENT '錄入人', `input_date` varchar(20) DEFAULT NULL COMMENT '錄入時間', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8; INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49'); INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50'); INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49'); ~~~ ## 案例一 ~~~csharp select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; --通過查看執行計劃發現type=all,需要進行全表掃描 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; --優化一、為transaction_id創建唯一索引 create unique index idx_order_transaID on itdragon_order_list (transaction_id); --當創建索引之后，唯一索引對應的type是const，通過索引一次就可以找到結果，普通索引對應的type是ref，表示非唯一性索引賽秒，找到值還要進行掃描，直到將索引文件掃描完為止，顯而易見，const的性能要高于ref explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; --優化二、使用覆蓋索引，查詢的結果變成 transaction_id,當extra出現using index,表示使用了覆蓋索引 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E"; ~~~ ## 案例二 ~~~csharp --創建復合索引 create index idx_order_levelDate on itdragon_order_list (order_level,input_date); --創建索引之后發現跟沒有創建索引一樣，都是全表掃描，都是文件排序 explain select * from itdragon_order_list order by order_level,input_date; --可以使用force index強制指定索引 explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date; --其實給訂單排序意義不大，給訂單級別添加索引意義也不大，因此可以先確定order_level的值，然后再給input_date排序 explain select * from itdragon_order_list where order_level=3 order by input_date; ~~~