MySQL高级-索引是个什么东西？explain到底怎么用-MySQL查询优化大全
目錄
一、引出問題-MySQL的查詢優化：
二、性能下降的原因：
三、索引到底是什么？怎么用？
1.索引操作
查看索引：
刪除索引：
創建索引：
說明：
索引命名規范：
2.索引優勢：
3.索引劣勢：
4.索引分類：
5.哪些情況需要創建索引：
6.哪些情況不要創建索引：
7.mysql都有哪幾種索引：
8.BTree索引是怎么存儲、查找的：
9.索引到底是怎么工作的：
10.哪些情況下會造成索引失效：
四、怎么知道查詢語句是否走了索引-explain到底是什么？（重頭戲來了）
1.Explain（查看執行計劃）：
2.explain到底是做什么的？
五、explain的用法（重中之重頭戲）
explain的十列表頭信息：
初始建表語句
1.id
2.select_type
3.table
4.type（重要）
5.possible_keys
6.key
7.key_len:
8.ref
9.rows
10.Extra
一、引出問題-MySQL的查詢優化：
? ? 相信很多剛入職的新人，經常會聽到老員工說“看看怎么優化這個sql”、“這條select走沒走索引”。。。
? ? 甚至更多地老員工，在面對跳槽面試的時候，面試官問及“mysql查詢優化你是怎么做的”。
? ? 這篇文章，讓你徹底明白，我們作為一個java程序員，是如何做MySQL的查詢優化的！
二、性能下降的原因：
性能下降sql慢的原因：（1.執行時間長；2.等待時間長）
? ? 查詢語句寫的差（自身問題）；
? ? 索引失效（單值索引或復合索引失效）；
? ? 關聯查詢太多join（設計缺陷或不得已的需求）
? ? 服務器調優及各個參數設置（緩沖、線程數等）
三、索引到底是什么？怎么用？
? ? 在關系數據庫中，索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
? ? 簡而言之：索引就是為了增加查詢和排序的速度（就像圖書的目錄一樣）！
? ? 一般來說索引本身也很大不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲在磁盤上。
? ? 我們平常說的索引，如果沒有特別指明，都是B樹（多路搜索樹，并不一定是二叉的）結構組織的索引。其中聚集索引，次要索引，覆蓋索引，復合索引，前綴索引，唯一索引默認都是使用B+樹索引，統稱索引。當然，除了B+樹這種類型的索引之外，還有哈希索引（hash?index）等。
? ? 具體什么是B樹，什么是B+樹，以后再開博文細細來討論~
1.索引操作
查看索引：
SHOW INDEX FROM tb_stu_info2;
SHOW INDEX FROM tb_stu_info2\G
刪除索引：
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
創建索引：
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]??INDEX | KEY??[索引名] (字段名1 [(長度)] [ASC | DESC]) [USING 索引方法]；
CREATE??[UNIQUE | FULLTEXT | SPATIAL]??INDEX??索引名 ON??表名(字段名) [USING 索引方法]；
說明：
UNIQUE:可選。表示索引為唯一性索引。
FULLTEXT:可選。表示索引為全文索引。
SPATIAL:可選。表示索引為空間索引。
INDEX和KEY:用于指定字段為索引，兩者選擇其中之一就可以了，作用是一樣的。
索引名:可選。給創建的索引取一個新名稱。
字段名1:指定索引對應的字段的名稱，該字段必須是前面定義好的字段。
長度:可選。指索引的長度，必須是字符串類型才可以使用。
ASC:可選。表示升序排列。
DESC:可選。表示降序排列
索引命名規范：
idx_user_name（user表的name索引）
idx_user_nameAge（user表的name、age）
2.索引優勢：
增加查詢、排序速率。
3.索引劣勢：
? ? 實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的。
? ? 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息。
? ? 索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢。
4.索引分類：
單值索引：即一個索引只包含單個列，一個表可以有多個單列索引。
唯一索引：索引列的值必須唯一，但允許有空值。
復合索引：即一個索引包含多個列。
5.哪些情況需要創建索引：
?? ?1.主鍵自動建立唯一索引。
?? ?2.頻繁作為查詢條件的字段應該創建索引。
?? ?3.查詢中與其他表關聯的字段，外鍵關系建立索引。
?? ?4.頻繁更新的字段不適合創建索引（因為每次更新不單單是更新了記錄還會更新索引）
?? ?5.where條件里用不到的字段不創建索引。
?? ?6.單鍵/組合索引的選擇問題？（在高并發下傾向創建組合索引）
?? ?7.查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度。
?? ?8.查詢中統計或者分組字段。
6.哪些情況不要創建索引：
?? ?1.表記錄太少。
?? ?2.經常增刪改的表。（提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件）
?? ?3.數據重復且分布平均的表字段，因此應該只為最經常查詢和最經常排序的數據列建立索引。注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。
? ? 假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值得分布概率大約為50%，那么對這種表A字段建索引一般不會提高數據庫的查詢速度。
? ? 索引的選擇性是指索引列中不同值的數目與表中記錄數的比。如果一個表中有2000條記錄，表索引列有1980個不同的值，那么這個索引的選擇性就是1980/2000=0/99。一個索引的選擇性越接近于1，這個索引的效率就越高。
7.mysql都有哪幾種索引：
BTree索引（多）、Hash索引、full-text全文索引、R-Tree索引。
8.BTree索引是怎么存儲、查找的：
（略...等待后續單獨出一篇文章）
9.索引到底是怎么工作的：
（略...等待后續單獨出一篇文章）
10.哪些情況下會造成索引失效：
（略...等待后續單獨出一篇文章）
四、怎么知道查詢語句是否走了索引-explain到底是什么？（重頭戲來了）
1.Explain（查看執行計劃）：
使用Explain關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。
用法：explain +?sql。
注意：explain只能用于select語句！update、delete語句想知道是否走了索引，需要將后面的where條件單獨拿出來放到select語句中，再進行分析！
2.explain到底是做什么的？
使用explain，能夠清楚的知道：
? ? 表的讀取順序；
? ? 數據讀取操作的操作類型；
? ? 哪些索引可以使用；
? ? 哪些索引被實際使用；
? ? 表之間的引用；
? ? 每張表有多少行被優化器查詢。
五、explain的用法（重中之重頭戲）
（因為涉及內容太過龐大，其中有解釋不清楚的，可以自行再單獨百度搜索其中的一種類型，然后深究。）
explain的十列表頭信息：
初始建表語句
CREATE TABLE `t1` (`id` int(11) DEFAULT NULL,`oname` varchar(20) DEFAULT NULL,`address` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `t1` VALUES ('1', '一中', '青島'); INSERT INTO `t1` VALUES ('2', '二中', '濟南');CREATE TABLE `t2` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL,`age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `t2` VALUES ('1', '張三', '12'); INSERT INTO `t2` VALUES ('2', '李四', '13');CREATE TABLE `t3` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL,`phoneno` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `t3` VALUES ('1', '王五', '155'); INSERT INTO `t3` VALUES ('2', '趙六', '134');1.id
id標識select查詢的序列號，包含一個數字，表示查詢中執行select子句或操作表的順序；
有三種情況：
1）id相同，執行順序由上至下
EXPLAIN SELECTt2.* FROMt1 LEFT JOIN t3 ON t3.id = t1.id LEFT JOIN t2 ON t2.id = t1.id WHEREt3. NAME = '';如上，三個表，mysql的執行順序是t1-t3-t2。
2）如果查詢中攜帶子查詢，id序號會自增，id值越大越先被執行
EXPLAIN SELECTt2.* FROMt2 WHEREt2.id = (SELECTidFROMt1WHEREt1.id = (SELECT id FROM t3 WHERE t3.id = 1))如上，三個表的執行順序是t3-t1-t2。
3）id有相同的有不同的，先執行id大的，如果有id相同的，按照從上往下執行
EXPLAIN SELECTt2.* FROM(SELECTt1.idFROMt1WHEREt1.oname = '一中') s1 LEFT JOIN t2 ON s1.id = t2.id如上，執行順序為t1-t2-<derived2>（注：<derived2>是id為2的查詢生成的一張臨時表，后面的2代表id值）。
2.select_type
共有六種情況：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。
SIMPLE：簡單的select查詢，查詢中不包含子查詢或者UNION。
PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY。
SUBQUERY：在select或where列表中包含了子查詢。
DERIVED：在from列表中包含的子查詢被標記為DERIVED（衍生），MySQL會遞歸執行這些子查詢，把結果放在臨時表里。
UNION：若第二個select出現在union之后，則被標記為union；若union包含在from子句的子查詢中，外層select將被標記為：DERIVED。
UNION?RESULT：從UNION表獲取結果的select。
EXPLAIN SELECTt1.id FROMt1 LEFT JOIN t2 ON t1.id = t2.id UNIONSELECTt2.idFROMt2LEFT JOIN t3 ON t2.id = t3.id3.table
顯示這一行的數據是關于哪張表的。
4.type（重要）
system：表只有一行記錄（等于系統表），這是const類型的特例，平時不會出現，這個也可以忽略不計。
const：表示通過索引一次就找到了，const用于比較primary?key或者unique索引。因為只匹配一行數據，所以很快。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量。
?
eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或位移索引掃描。
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的結合體。（聯合索引，使用其中的某幾個索引就會是ref）
fulltext：全文搜索
ref_or_null：與ref類似，但包括NULL
index_merge：表示出現了索引合并優化(包括交集，并集以及交集之間的并集)，但不包括跨表和全文索引。這個比較復雜，目前的理解是合并單表的范圍索引掃描（如果成本估算比普通的range要更優的話）
unique_subquery：在in子查詢中，就是value in (select…)把形如select unique_key_column的子查詢替換。PS：所以不一定in子句中使用子查詢就是低效的！
index_subquery：同上，但把形如”select non_unique_key_column“的子查詢替換
range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。一般就是在你的where語句中出現了between、<、>、in等的查詢。這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結束于另一點，不用掃描全部索引。
index：Full?Index?Scan，index與All區別為index類型只遍歷索引樹。這通常比all快，因為索引文件通常比數據文件小。（也就是說雖然all和index讀全表，但index是從索引中讀取的，而all是從硬盤中讀取的）
all：全表掃描(full table scan)
以下性能越來越慢：system > const > eq_ref > ref > range > index > ALL
備注：一般來說，得保證查詢至少達到range級別，最好能達到ref。
5.possible_keys
顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用。
6.key
實際使用的索引。如果為NULL，則沒有使用索引。
查詢中若使用了復合索引，則該索引僅出現在key列表中。（這種情況，創建的復合索引的個數和順序，與查詢字段的個數和順序一致）
7.key_len:
? ? 表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好。
? ? key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的。
8.ref
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值。
9.rows
根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需要的讀取的行數。（通常數字越小性能越高）
10.Extra
? ? 包含不適合在其他列中顯示但十分重要的額外信息。
1.Using?filesort：說明MySQL會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱之為“文件排序”（索引也跟排序有關系的）
2.Using?temporary（用了一個內部臨時表）：使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表。常見于排序order?by和分組查詢group?by。
3.Using?index（使用索引-性能非常好）：
?? ?表示相應的select操作中使用了覆蓋索引（Covering?Index），避免訪問了表的數據行，效率不錯！
? ? 如果同時出現using?where，表明索引被用來執行索引鍵值的查找；
? ? 如果沒有同時出現using?where，表明索引用來讀取數據而非執行查找動作。
4.Using?where：表明使用了where過濾
5.Using?join?buffer：使用了連接緩存
6.impossible?where：where子句的值總是false，不能用來獲取任何元組。
7.select?tables?optimized?away：在沒有group by子句的情況下，基于索引優化min/max操作或者對于MyISAM存儲引擎優化count(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。
8.distinct：優化distinct，在找到第一匹配的元組后即停止找同樣值得動作。
?
?
總結
以上是生活随笔為你收集整理的MySQL高级-索引是个什么东西？explain到底怎么用-MySQL查询优化大全的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： weblogic从入门到起飞！（webl
- 下一篇： elasticSearch入门到java