[B站尚硅谷](https://space.bilibili.com/302417610/?spm_id_from=333.999.0.0) ## 1. 什么是索引 官方: 本質是一種數據結構, 是幫助MySQL高效獲取數據的數據結構 是排好序的快速查找數據結構 ## 2. 索引的優劣 ### 1. 優勢 * 提高數據檢索效率，降低數據庫的IO成本 * 降低數據排序成本，降低了CPU的消耗 ### 2. 劣勢 * 實際上索引也是一張表，該表保存了主鍵和索引字段，并指向實體表的記錄,所以索引列也是要占用空間的 * 因為更新表時，MySQL不僅要不存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息 * 索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立優秀的索引，或優化查詢語句 ## 3. 索引分類 * 單值索引 即一個索引只包含單個列，一個表可以有多個單列索引 * 唯一索引 索引列的值必須唯一，但允許有空值 * 復合索引 即一個索引包含多個列 ## 4. 索引創建的基本語法 ### 創建 如果是CHAR,VARCHAR類型，length可以小于字段實際長度； 如果是BLOB和TEXT類型，必須指定length。 ``` CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length)); ``` ``` ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length)); ``` ### 刪除 ``` DROP INDEX [indexName] ON mytable; ``` ### 查看 ``` SHOW INDEX FROM table_name G ``` ## 5. 哪些情況需要創建索引 1. 主鍵自動建立唯一索引 2. 頻繁作為查詢的條件的字段應該創建索引 3. 查詢中與其他表關聯的字段，外鍵關系建立索引 4. 頻繁更新的字段不適合創建索引 因為每次更新不單單是更新了記錄還會更新索引，加重IO負擔 5. Where條件里用不到的字段不創建索引 6. 單間/組合索引的選擇問題，who？（在高并發下傾向創建組合索引） 7. 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序的速度 8. 查詢中統計或者分組字段 ## 6. 哪些情況不要創建索引 1. 表記錄太少 2. 經常增刪改的表 3. 數據重復且分布平均的表字段，因此應該只為經常查詢和經常排序的數據列建立索引。 注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。 ## 7. 性能分析 ### 7.1 Explain 使用: Explain + SQL語句 結果  作用: 1. 表的讀取順序 id 字段 2. 數據讀取操作的操作類型 select_type 3. 哪些索引可以使用 possible_keys 4. 哪些索引被實際使用 key 5. 表之間的引用 ref 6. 每張表有多少行被優化器查詢 rows ### 7.2 Explain 結果介紹 #### 7.2.1 ID 表的讀取順序 * select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序 1. id相同，執行順序由上至下  2. id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行  3. id相同不同，同時存在  #### 7.2.2 select_type 數據讀取操作的操作類型 1. SIMPLE 簡單的select查詢，查詢中不包含子查詢或者UNION 2. PRIMARY 查詢中若包含任何復雜的子部分，最外層查詢則被標記為 PRIMARY 3. SUBQUERY 在SELECT或者WHERE列表中包含了子查詢 4. DERIVED 在FROM列表中包含的子查詢被標記為DERIVED（衍生）MySQL會遞歸執行這些子查詢，把結果放在臨時表里。 5. UNION 若第二個SELECT出現在UNION之后，則被標記為UNION; 若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED 6. UNION RESULT 從UNION表獲取結果的SELECT 7. #### 7.2.3 table #### 7.2.4 type  從最好到最差依次是 system > const > eq_ref > ref > range > index > ALL 一般達到range, 最好能達到ref 1. system 表只有一行記錄（等于系統表），這是const類型的特例，平時不會出現，這個也可以忽略不計 2. const 表示通過索引一次就找到了，const用于比較primary key或者unique索引。因為只匹配一行數據，所以很快。如將主鍵至于where列表中，MySQL就能將該查詢轉換為一個常量 3. eq_ref 唯一性索引，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描 4. ref 非唯一索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體 5. range 只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引一般就是在你的where語句中出現了between、<、>、in等的查詢這種范圍掃描索引掃描比全表掃描要好，因為他只需要開始索引的某一點，而結束語另一點，不用掃描全部索引 6. index Full Index Scan,index與ALL區別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數據文件小。（也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的） 7. all FullTable Scan,將遍歷全表以找到匹配的行 #### 7.2.5 possible_keys 顯示可能應用在這張表中的索引,一個或多個。 查詢涉及的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用 #### 7.2.5 key * 實際使用的索引。如果為null則沒有使用索引 查詢中若使用了覆蓋索引，則索引和查詢的select字段重疊 #### 7.2.6 ref 顯示索引那一列被使用了，如果可能的話，是一個常數。那些列或常量被用于查找索引列上的值  #### 7.2.7 rows * 根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數 #### 7.2.8 Extra 1. Using filesort 說明MySQL 會對數據使用一個外部的索引排序, 而不是按照表內的索引順序進行讀取. MySQL 無法利用索引完成的排序操作稱為 "文件排序" 2. Using temporaty 使用了臨時表保存中間結果, 常見于排序 和分組查詢 3. Using Index 效率高 4. USING JOIN buffer (Block Nested LOOP) 緩存塊嵌套循環鏈接，join連表時使用了BNL算法，如果無法使用Index Nested-Loop Join的時候, 就默認使用這個。可以通過調整join_buffer_size參數來優化BNL算法的性能或者在連表的字段上加索引 5. Using where ： 表示 MySQL 正在使用 WHERE 子句中的條件來過濾行，需要掃描表或索引，并應用 WHERE 條件來決定哪些行應該被返回。優化：為 WHERE 子句中經常使用的、選擇性高的列創建索引；對于多個條件的 AND 操作，考慮創建復合索引（多列索引）；使用覆蓋索引減少回表；減少返回的字段，去掉非必要字段 ## 8. 索引優化 1. 全值匹配我最愛 2. 最佳左前綴法則 如果索引了多例，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。帶頭大哥不能死,中間兄弟不能斷 3. 不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描 4. 存儲引擎不能使用索引中范圍條件右邊的列 5. 盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select* 6. mysql在使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描 7. is null,is not null 也無法使用索引 8. like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作 9. 字符串不加單引號索引失效 10. 少用or,用它連接時會索引失效 ## 9. 查詢優化 ### 9.1 永遠小表驅動大表, 類似嵌套循環Nested Loop   ### 9.2 order by關鍵字優化 聯合索引的各個排序列的排序順序必須要一致 ORDER BY子句，盡量使用Index方式排序，避免使用FileSort方式排序 使用Index排序的兩種情況 order by 語句使用索引最左前列 使用where子句與order by字句條件列組合滿足索引最左前列  ### 9.3 group by關鍵字優化 和order by 相似 ## 10 slow_query_log 慢查詢日志 默認是關閉的 開啟: set global slow_query_log = 1 本次有效, 重啟數據庫就沒用了, 長期開啟要設置配置文件 Mysql 日志工具  ## 11 Mysql鎖機制 ### 11.1 鎖的分類 從數據操作的類型（讀、寫）分 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響, 此時無法修改數據 寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。 從對數據操作的顆粒度 表鎖 行鎖 頁鎖(前倆鎖之前, 了解即可) ### 11.2 表鎖（偏讀） 偏向MyISAM存儲引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，發生鎖沖突的概率最高，并發最低  ### 11.3 行鎖（偏寫） 偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。 InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）;二是采用了行級鎖 #### 間隙鎖危害  ## 如何鎖定一行