1、索引
官方定義：索引（Index） 是幫助 MySQL 高效獲取數據的數據結構。
索引為什麼是一種數據結構，它又是怎麼提高查詢的速度？我們拿最常用的二叉樹來分析索引的工作原理。如下圖：
創建索引的優勢：
1. 提高數據的檢索速度，降低數據庫 IO 成本：使用索引的意義就是通過縮小表中需要查詢的記錄的數目從而加快搜索的速度。
2. 降低數據排序的成本，降低 CPU 消耗：索引之所以查的快，是因為先將數據排好序，若該字段正好需要排序，則真好降低了排序的成本。
創建索引的劣勢：
1. 佔用存儲空間：索引實際上也是一張表，記錄了主鍵與索引字段，一般以索引文件的形式存儲在磁盤上。
2. 降低更新表的速度：表的數據發生了變化，對應的索引也需要一起變更，從而減低的更新速度。否則索引指向的物理數據可能不對，這也是索引失效的原因之一。
3. 優質索引創建難：索引的創建並非一日之功，也並非一直不變。需要頻繁根據用戶的行為和具體的業務邏輯去創建最佳的索引。
索引分類：
我們常說的索引一般指的是 BTree（多路搜索樹）結構組織的索引。其中還有聚合索引，次要索引，複合索引，前綴索引，唯一索引，統稱索引，當然除了 B + 樹外，還有哈希索引（hash index）等。
單值索引：一個索引只包含單個列，一個表可以有多個單列索引
唯一索引：索引列的值必須唯一，但允許有空值
複合索引：一個索引包含多個列，實際開發中推薦使用
實際開發中推薦使用複合索引，並且單表創建的索引個數建議不要超過五個
基本語法：
創建：
create [unique] index indexName on tableName (columnName...)
alter tableName add [unique] index [indexName] on (columnName...)
刪除：
drop index [indexName] on tableName
查看：
show index from tableName
哪些情況需要建索引：
1. 主鍵，唯一索引
2. 經常用作查詢條件的字段需要創建索引
3. 經常需要排序、分組和統計的字段需要建立索引
4. 查詢中與其他表關聯的字段，外鍵關係建立索引
哪些情況不要建索引：
1. 表的記錄太少，百萬級以下的數據不需要創建索引
2. 經常增刪改的表不需要創建索引
3. 數據重複且分佈平均的字段不需要創建索引，如 true,false 之類。
4. 頻發更新的字段不適合創建索引
5. where 條件裡用不到的字段不需要創建索引
性能分析
MySQL 自身瓶頸
MySQL 自身參見的性能問題有磁盤空間不足，磁盤 I/O 太大，服務器硬件性能低。
1. CPU：CPU 在飽和的時候一般發生在數據裝入內存或從磁盤上讀取數據時候
2. IO：磁盤 I/O 瓶頸發生在裝入數據遠大於內存容量的時候
3. 服務器硬件的性能瓶頸：top,free,iostat 和 vmstat 來查看系統的性能狀態
2、藉助Explain 分析 sql 語句
使用 explain 關鍵字可以模擬優化器執行 sql 查詢語句，從而得知 MySQL 是如何處理 sql 語句。
id
1. select 查詢的序列號，包含一組可以重複的數字，表示查詢中執行 sql 語句的順序。一般有三種情況：
2. 第一種：id 全部相同，sql 的執行順序是由上至下；
3. 第二種：id 全部不同，sql 的執行順序是根據 id 大的優先執行；
4. 第三種：id 既存在相同，又存在不同的。先根據 id 大的優先執行，再根據相同 id 從上至下的執行。
select_type
partitions
1. 表所使用的分區，如果要統計十年公司訂單的金額，可以把數據分為十個區，每一年代表一個區。這樣可以大大的提高查詢效率。
type
這是一個非常重要的參數，連接類型，常見的有：all , index , range , ref , eq_ref , const , system , null 八個級別。
性能從最優到最差的排序：system > const > eq_ref > ref > range > index > all。
對 java 程序員來說，若保證查詢至少達到 range 級別或者最好能達到 ref 則算是一個優秀而又負責的程序員。
1. all：（full table scan）全表掃描無疑是最差，若是百萬千萬級數據量，全表掃描會非常慢。
2. index：（full index scan）全索引文件掃描比 all 好很多，畢竟從索引樹中找數據，比從全表中找數據要快。
3. range：只檢索給定範圍的行，使用索引來匹配行。範圍縮小了，當然比全表掃描和全索引文件掃描要快。sql 語句中一般會有 between，in，>，< 等查詢。
4. ref：非唯一性索引掃描，本質上也是一種索引訪問，返回所有匹配某個單獨值的行。比如查詢公司所有屬於研發團隊的同事，匹配的結果是多個並非唯一值。
5. eq_ref：唯一性索引掃描，對於每個索引鍵，表中有一條記錄與之匹配。比如查詢公司的 CEO，匹配的結果只可能是一條記錄，
6. const：表示通過索引一次就可以找到，const 用於比較 primary key 或者 unique 索引。因為只匹配一行數據，所以很快，若將主鍵至於 where 列表中，MySQL 就能將該查詢轉換為一個常量。
7. system：表只有一條記錄（等於系統表），這是 const 類型的特列，平時不會出現，瞭解即可
possible_keys
1. 顯示查詢語句可能用到的索引 (一個或多個或為 null)，不一定被查詢實際使用。僅供參考使用。
key
1. 顯示查詢語句實際使用的索引。若為 null，則表示沒有使用索引。
key_len
1. 顯示索引中使用的字節數，可通過 key_len 計算查詢中使用的索引長度。在不損失精確性的情況下索引長度越短越好。key_len 顯示的值為索引字段的最可能長度，並非實際使用長度，即 key_len 是根據表定義計算而得，並不是通過表內檢索出的。
ref
1. 顯示索引的哪一列或常量被用於查找索引列上的值。
rows
1. 根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，值越大越不好。
extra
1. Using filesort： 說明 MySQL 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL 中無法利用索引完成的排序操作稱為 “文件排序” 。出現這個就要立刻優化 sql。
2. Using temporary： 使用了臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和 分組查詢 group by。 出現這個更要立刻優化 sql。
3. Using index： 表示相應的 select 操作中使用了覆蓋索引（Covering index），避免訪問了表的數據行，效果不錯！如果同時出現 Using where，表明索引被用來執行索引鍵值的查找。如果沒有同時出現 Using where，表示索引用來讀取數據而非執行查找動作。
4. 覆蓋索引（Covering Index） ：也叫索引覆蓋，就是 select 的數據列只用從索引中就能夠取得，不必讀取數據行，MySQL 可以利用索引返回 select 列表中的字段，而不必根據索引再次讀取數據文件。
5. Using index condition： 在 5.6 版本後加入的新特性，優化器會在索引存在的情況下，通過符合 RANGE 範圍的條數 和 總數的比例來選擇是使用索引還是進行全表遍歷。
6. Using where： 表明使用了 where 過濾。
7. Using join buffer： 表明使用了連接緩存。
8. impossible where： where 語句的值總是 false，不可用，不能用來獲取任何元素。
9. distinct： 優化 distinct 操作，在找到第一匹配的元組後即停止找同樣值的動作。
filtered
1. 一個百分比的值，和 rows 列的值一起使用，可以估計出查詢執行計劃 (QEP) 中的前一個表的結果集，從而確定 join 操作的循環次數。小表驅動大表，減輕連接的次數。
通過 explain 的參數介紹，我們可以得知：
1. 表的讀取順序 (id)
2. 數據讀取操作的操作類型 (type)
3. 哪些索引被實際使用 (key)
4. 表之間的引用 (ref)
5. 每張表有多少行被優化器查詢 (rows)
性能下降的原因
從程序員的角度
1. 查詢語句寫的不好
2. 沒建索引，索引建的不合理或索引失效
3. 關聯查詢有太多的 join
從服務器的角度
1. 服務器磁盤空間不足
2. 服務器調優配置參數設置不合理
3、案例分析:
場景：訂單管理頁面，通過訂單級別和訂單錄入時間排序
業務邏輯：優先處理訂單級別高，錄入時間長的訂單。
既然是排序，首先想到的應該是 order by， 還有一個可怕的 Using filesort 等著你。
最基礎的 sql 語句
possible_keys
1. 顯示查詢語句可能用到的索引 (一個或多個或為 null)，不一定被查詢實際使用。僅供參考使用。
key
1. 顯示查詢語句實際使用的索引。若為 null，則表示沒有使用索引。
key_len
1. 顯示索引中使用的字節數，可通過 key_len 計算查詢中使用的索引長度。在不損失精確性的情況下索引長度越短越好。key_len 顯示的值為索引字段的最可能長度，並非實際使用長度，即 key_len 是根據表定義計算而得，並不是通過表內檢索出的。
ref
1. 顯示索引的哪一列或常量被用於查找索引列上的值。
rows
1. 根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，值越大越不好。
extra
1. Using filesort： 說明 MySQL 會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL 中無法利用索引完成的排序操作稱為 “文件排序” 。出現這個就要立刻優化 sql。
2. Using temporary： 使用了臨時表保存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和 分組查詢 group by。 出現這個更要立刻優化 sql。
3. Using index： 表示相應的 select 操作中使用了覆蓋索引（Covering index），避免訪問了表的數據行，效果不錯！如果同時出現 Using where，表明索引被用來執行索引鍵值的查找。如果沒有同時出現 Using where，表示索引用來讀取數據而非執行查找動作。
4. 覆蓋索引（Covering Index） ：也叫索引覆蓋，就是 select 的數據列只用從索引中就能夠取得，不必讀取數據行，MySQL 可以利用索引返回 select 列表中的字段，而不必根據索引再次讀取數據文件。
5. Using index condition： 在 5.6 版本後加入的新特性，優化器會在索引存在的情況下，通過符合 RANGE 範圍的條數 和 總數的比例來選擇是使用索引還是進行全表遍歷。
6. Using where： 表明使用了 where 過濾。
7. Using join buffer： 表明使用了連接緩存。
8. impossible where： where 語句的值總是 false，不可用，不能用來獲取任何元素。
9. distinct： 優化 distinct 操作，在找到第一匹配的元組後即停止找同樣值的動作。
filtered
1. 一個百分比的值，和 rows 列的值一起使用，可以估計出查詢執行計劃 (QEP) 中的前一個表的結果集，從而確定 join 操作的循環次數。小表驅動大表，減輕連接的次數。
通過 explain 的參數介紹，我們可以得知：
1. 表的讀取順序 (id)
2. 數據讀取操作的操作類型 (type)
3. 哪些索引被實際使用 (key)
4. 表之間的引用 (ref)
5. 每張表有多少行被優化器查詢 (rows)
性能下降的原因
從程序員的角度
1. 查詢語句寫的不好
2. 沒建索引，索引建的不合理或索引失效
3. 關聯查詢有太多的 join
從服務器的角度
1. 服務器磁盤空間不足
2. 服務器調優配置參數設置不合理
3、案例分析:
場景：訂單管理頁面，通過訂單級別和訂單錄入時間排序
業務邏輯：優先處理訂單級別高，錄入時間長的訂單。
既然是排序，首先想到的應該是 order by， 還有一個可怕的 Using filesort 等著你。
最基礎的 sql 語句
首先，採用全表掃描就不合理，還使用了文件排序 Using filesort，更加拖慢了性能。
MySQL 在 4.1 版本之前文件排序是採用雙路排序的算法，由於兩次掃描磁盤，I/O 耗時太長。後優化成單路排序算法。其本質就是用空間換時間，但如果數據量太大，buffer 的空間不足，會導致多次 I/O 的情況。其效果反而更差。與其找運維同事修改 MySQL 配置，還不如自己乖乖地建索引。
初步優化：為 order_level, input_date 創建複合索引
創建複合索引後你會驚奇的發現，和沒創建索引一樣？？？都是全表掃描，都用到了文件排序。是索引失效？還是索引創建失敗？我們試著看看下面打印情況
4、總結
1. 索引是排好序且快速查找的數據結構。其目的是為了提高查詢的效率。
2. 創建索引後，查詢數據變快，但更新數據變慢。
3. 性能下降的原因很可能是索引失效導致。
4. 索引創建的原則，經常查詢的字段適合創建索引，頻繁需要更新的數據不適合創建索引。
5. 索引字段頻繁更新，或者表數據物理刪除容易造成索引失效。
6. 擅用 explain 分析 sql 語句
7. 除了優化 sql 語句外，還可以優化表的設計。如儘量做成單表查詢，減少表之間的關聯。設計歸檔表等。
文章摘錄於：https://mp.weixin.qq.com/s?__biz=MjM5OTkxOTc0Mw==&mid=2650232797&idx=1&sn=639c2b114c66b8043cd0d0f30fdd983c&chksm=bf37decd884057dbf7dbe69c70012f5a15bad9e07058cbeaa0cf895c1ce041ada428757f668c&mpshare=1&scene=23&srcid=07176qeJCND0twpd6Rary3t6#rd
閱讀更多 碼農筆記Day 的文章
