一、SQL分析
效能下降、SQL慢、執行時間長、等待時間長
- 查詢語句寫的差
- 索引失效關聯查詢太多join(設計缺陷)
- 單值索引：在user表中給name屬性建立索引，create index idx_name on user(name);
- 複合索引：在user表中給name、email屬性建立索引，create index idx_name_email on user(name,email);
- 伺服器調優及各個引數設定(緩衝、執行緒數等)
二，join查詢
1，SQL執行順序
a）手寫SQL
b）機讀
c）總結
2，JOIN查詢
a）建表語句
CREATE TABLE tbl_dept( id INT(11) NOT NULL AUTO_INCREMENT, deptName VARCHAR(30) DEFAULT NULL, locAdd VARCHAR(40) DEFAULT NULL, PRIMARY KEY(id) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE tbl_emp ( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) DEFAULT NULL, deptId INT(11) DEFAULT NULL, PRIMARY KEY (id), KEY fk_dept_Id (deptId) #CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id') )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11); INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13); INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14); INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15); INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1); INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2); INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2); INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3); INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4); INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
b）滿足條件的join語句
#左連線 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId; #右連線 SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId; #內連線(交集) SELECT * FROM tbl_dept t1 INNER JOIN tbl_emp t2 on t1.id = t2.deptId; #左連線(只取A獨有的) SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL; #右連線(只取B獨有的) SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL; #A和B均有 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId UNION SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId; #A和B均是獨有 SELECT * FROM tbl_dept t1 LEFT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t2.deptId is NULL UNION SELECT * FROM tbl_dept t1 RIGHT JOIN tbl_emp t2 on t1.id = t2.deptId WHERE t1.id is NULL;
三、索引
1，索引是什麼
- 索引(Index)是幫助MySQL高效獲取資料的資料結構。
- 排好序的快速查詢資料結構，即索引 = 排序 + 查詢
- 一般來說索引本身佔用記憶體空間也很大，不可能全部儲存在記憶體中，因此索引往往以檔案形式儲存在硬碟上
- 聚集索引，次要索引，覆蓋索引，複合索引，字首索引，唯一索引預設都是使用B+樹索引，統稱索引。當然，除了B+樹這種型別的索引之外，還有雜湊索引(hash index)等。
2，索引原理
3，索引優劣勢
a)索引的優勢
- 索引能提高資料檢索的效率，降低資料庫的IO成本
- 通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性
- 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間
- 加速兩個表之間的連線，一般在外來鍵上建立索引
b)劣勢
- 實際上索引也是一張表，該表儲存了主鍵和索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的
- 雖然索引大大提高了查詢速度，同時卻也會降低更新表的速度，如對錶insert、update和delete。因為更新表時，不僅要儲存資料，還要儲存一下索引檔案。
- 索引只是提高效率的一個因素，如果你的MySQL有大資料量的表，就需要花時間研究建立優秀的索引，或優化查詢語句
4，MySQL索引分類
- 普通索引：是最基本的索引，它沒有任何限制，即一個索引只包含單個列，一個表可以有多個單列索引；建議一張表索引不要超過5個，優先考慮複合索引
- 唯一索引：與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一
- 主鍵索引：是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時建立主鍵索引
- 複合索引：指多個欄位上建立的索引，只有在查詢條件中使用了建立索引時的第一個欄位，索引才會被使用。使用組合索引時遵循最左字首集合
- 全文索引：主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同，它更像是一個搜尋引擎，而不是簡單的where語句的引數匹配
5，MySQL索引語法
- 建立索引
CREATE [UNIQUE] INDEX index_name ON mytable(column_name(length));
- 刪除索引
DROP INDEX index_name ON mytable;
- 檢視索引(
\G表示將查詢到的橫向表格縱向輸出，方便閱讀)
SHOW INDEX FROM table_name\G
- 採用alter方式新增索引
#該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。 ALTER TABLE tbl_name ADD PRIMARY KEY(column_list) #這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次） ALTER TABLE tbl_name ADD UNIQUE index_name(column_list) #新增普通索引，索引值可出現多次。 ALTER TABLE tbl_name ADD INDEX index_name(column_list) #該語句指定了索引為FULLTEXT，用於全文索引。 ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
6，MySQL的索引結構
詳情可檢視：多路查詢樹(B-Tree，B+Tree，B*Tree)
a）B-Tree與B+Tree的區別
B-樹的關鍵字（資料項）和記錄是放在一起的； B+樹的非葉子節點中只有關鍵字和指向下一個節點的索引， 記錄只放在葉子節點中。
b）B+Tree 與 BTree 的查詢過程
- 在 B 樹中， 越靠近根節點的記錄查詢時間越快， 只要找到關鍵字即可確定記錄的存在； 而 B+ 樹中每個記錄的查詢時間基本是一樣的， 都需要從根節點走到葉子節點， 而且在葉子節點中還要再比較關鍵字。
- 從這個角度看 B 樹的效能好像要比 B+ 樹好， 而在實際應用中卻是 B+ 樹的效能要好些。 因為 B+ 樹的非葉子節點不存放實際的資料，這樣每個節點可容納的元素個數比 B 樹多， 樹高比 B 樹小， 這樣帶來的好處是減少磁碟訪問次數。
- 儘管 B+ 樹找到一個記錄所需的比較次數要比 B 樹多， 但是一次磁碟訪問的時間相當於成百上千次記憶體比較的時間， 因此實際中B+ 樹的效能可能還會好些， 而且 B+樹的葉子節點使用指標連線在一起， 方便順序遍歷（範圍搜尋）， 這也是很多資料庫和檔案系統使用 B+樹的緣故。
7，建立索引的場景
a）何時需要建立索引
- 主鍵自動建立唯一索引
- 頻繁作為查詢的條件的欄位應該建立索引
- 查詢中與其他表關聯的欄位，外來鍵關係建立索引
- 頻繁更新的欄位不適合建立索引
- Where 條件裡用不到的欄位不建立索引
- 單間/組合索引的選擇問題（在高併發下傾向建立組合索引）
- 查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序的速度
- 查詢中統計或者分組欄位
b）何時不建立索引
- 表記錄太少
- 經常增刪改的表
- 資料重複且分佈平均的表欄位。如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
四、Explain
1，作用
- 表的讀取順序（id 欄位，越大優先順序越高）
- 資料讀取操作的操作型別（select_type 欄位）
- 哪些索引可以使用（possible_keys 欄位）
- 哪些索引被實際使用（keys 欄位）
- 表之間的引用（ref 欄位）
- 每張表有多少行被優化器查詢（rows 欄位）
2，id
- id相同，執行順序由上至下
- id不同，如果是子查詢，id的序號會遞增，id值越大優先順序越高，越先被執行
- id相同不同，同時存在：id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先順序越高，越先執行；衍生=DERIVED
2，select_type
select_type：查詢的型別，主要用於區別普通查詢、聯合查詢、子查詢等複雜查詢
- SIMPLE：簡單的select查詢，查詢中不包含子查詢或者UNION
- PRIMARY：查詢中若包含任何複雜的子部分，最外層查詢則被標記為PRIMARY
- SUBQUERY：在SELECT或者WHERE列表中包含了子查詢
- DERIVED：在FROM列表中包含的子查詢被標記為DERIVED（衍生）MySQL會遞迴執行這些子查詢，把結果放在臨時表裡
- UNION：若第二個SELECT出現在UNION之後，則被標記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為：DERIVED
- UNION RESULT：從UNION表獲取結果的SELECT
3，table
table：顯示這一行的資料是關於哪張表的
4，type
type：訪問型別排列，顯示查詢使用了何種型別，其中從好到壞依次是：system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。其中，相比比較重要的指標為：system > const > eq_ref > ref > range > index > ALL
- system：表只有一行記錄（等於系統表），這是const型別的特例，平時不會出現，這個也可以忽略不計
- const：表示通過索引一次就能找到了，const用於比較primary key或者unique索引。因為只匹配一行資料，所以很快。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量
- eq_ref：唯一性索引，對於每個索引建，表中只有一條記錄與之匹配，常見於主鍵或唯一索引掃描
- ref：非唯一索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，它返回所有匹配某個單獨值的行。不過，它可能找到多個符合條件的行，所以它應該屬於查詢和掃描的混合體
- range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是你的where語句中出現了between、<、>、in等的查詢，這種範圍掃描索引比全表掃描要好，因為他只需要開始索引的某一個點，而結束於另一點，不用掃描全部索引
- index：Full Index Scan，index與ALL區別為index型別只遍歷索引樹。這通常比ALL快，因為索引檔案通常比資料檔案小。(也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟資料庫檔案中讀的)
- ALL：Full Table Scan，將遍歷全表以找到匹配的行(全表掃描)
5，possible_keys
- 顯示可能應用在這張表上的索引，一個或多個
- 若查詢涉及的欄位上存在索引，則該索引將被列出，但不一定被查詢實際使用
6，key
- 實際使用的索引，如果為null，則沒有使用索引
- 若查詢中使用了覆蓋索引，則該索引僅出現在key列表中
7，key_len
- 表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
- key_len顯示的值為索引最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的
8，ref
- 顯示索引哪一列被使用了，如果可能的話，最好是一個常數。哪些列或常量被用於查詢索引列上的值
- 由key_len可知t1表的的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，t1表的col2匹配一個常量('ac')
9，rows
根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數
10，Extra
a）Using filesort(檔案排序)
- 在使用order by關鍵字的時候，如果待排序的內容不能由所使用的索引直接完成排序的話，那麼mysql有可能就要進行檔案排序
- filesort是通過相應的排序演算法,將取得的資料在記憶體中進行排序
- MySQL需要將資料在記憶體中進行排序，所使用的記憶體區域也就是我們通過sort_buffer_size 系統變數所設定的排序區
b）Using temporary(建立臨時表)
- using temporary一般出現在多張表的資料需要排序的情況下. MySQl會先使用using temporary儲存臨時資料, 然後再在臨時表上使用filesort進行排序
- 如果有ORDER BY子句和一個不同的GROUP BY子句, 或者如果ORDER BY或GROUP BY中的欄位都來自其他的表而非連線順序中的第一個表的話, 就會建立一個臨時表了。
- MySQL首先建立heap引擎的臨時表, 如果臨時的資料過多, 超過max_heap_table_size配置的大小, 會自動把臨時錶轉換成MyISAM引擎的表來使用
c）Using indexing(覆蓋索引)
-
表示相應的select操作中使用了覆蓋索引（Coveing Index），避免訪問了表的資料行。
-
如果同時出現using where，表明索引被用來執行索引鍵值的查詢
-
如果沒有同時出現using where，表明索引用來讀取資料而非執行查詢動作
覆蓋索引:
理解方式一：就是select的資料列只用從索引中就能夠取得，不必讀取資料行，MySQL可以利用索引返回select列表中的欄位，而不必根據索引再次讀取資料檔案，換句話說查詢列要被所建的索引覆蓋。
理解方式二：索引是高效找到行的一個方法，但是一般資料庫也能使用索引找到一個列的資料，因此它不必讀取整個行。畢竟索引葉子節點儲存了它們索引的資料；當能通過讀取索引就可以得到想要的資料，那就不需要讀取行了。一個索引包含了（或覆蓋了）滿足查詢結果的資料就叫做覆蓋索引。
注意：如果要使用覆蓋索引，一定要注意select列表中只取出需要的列，不可select * ，因為如果將所有欄位一起做索引會導致索引檔案過大，查詢效能下降。
d）Using where
表示使用了where過濾
e）Using join buffer
表示使用了連線快取
f）impossible where
where子句的值總是false，不能用來獲取任何元組
g）select tables optimized away
在沒有GROUP BY子句的情況下，基於索引優化MIN/MAX操作或者對於MyISAM儲存引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。
h）distinct
優化distinct，在找到第一匹配的元組後即停止找同樣值的工作