一、SQL性能下降原因

1.等待時間長？執行時間長？

可能原因：

查詢語句寫的不行

索引失效(單值索引、復合索引)

CREATE INDEX index_user_name ON user(name);(底層做了一個排序)

CREATE INDEX index_user_nameEmail ON user(name,email);

查詢關聯join太多太亂

服務器調優參數的設置

...

二、常見join連接查詢

由上文知道，join查詢也是SQL性能下降的原因

1.SQL執行順序

手寫SQL

//逐步向下的順序

機讀SQL

//從FROM開始讀

執行順序小結：

//注意前后關系與同級分支的選擇關系

2.JOIN理論

七種JOIN圖：

內連接：兩張表同時存在的滿足連接條件的記錄

左外連接：兩表同時存在滿足連接條件的加上A獨有的(B以null補足對齊)

右外連接：與左對稱

只要A獨有：與左外連接對比，獨有的就是左外連接部分中B為null的列

只要B獨有：與上對稱

全外連接：全部的外連接，也就是左外右外的合體

完整外部連接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結果集行包含基表的數據值。

要A、B各自的獨有：由外連接的概念，可以知道，獨有就是有一方沒有匹配，只能以null補足的那一部分。

SQL演示：

#內連接SELECT * FROM tb_empt a INNER JOIN tb_dept b ON a.deptId =b.id;

#左外連接(右連接對稱，左表補null)SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId =b.id;

#左外連接中只要A獨有的,只需在ON連接條件后加上WHERE條件(B獨有為對稱)SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id WHERE b.id = NULL#全外連接(按我們思路寫的SQL在MySQL中是不支持的，我們需要手動進行結果集合并：UNION合并并去重)--SELECT * FROM tb_empt a FULL OUTER JOIN tb_dept b ON a.deptId = b.id;

SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId =b.idUNION

SELECT * FROM tb_empt a RIGHT JOIN tb_dept b ON a.deptId =b.id

#只要A、B獨有的部分(同樣，根據合并結果集的啟發，合并兩個只要獨有的部分)SELECT * FROM tb_empt a LEFT JOIN tb_dept b ON a.deptId = b.id WHERE b.id = NULL

UNION

SELECT * FROM tb_empt a RIGHT JOIN tb_dept b ON a.deptId = b.id WHERE a.deptId = NULL

三、索引簡介

不僅僅是說像字典前面的目錄，方便查找那么簡單。

1.是什么？

【官方】：索引(INDEX)是幫助MySQL高效獲取數據的數據結構。

也就是說，是一種排好序的數據結構，目的在于提高查找效率。

它會影響到SQL中ORDER BY 和 WHERE

在數據本身之外，數據庫還維護著一個滿足特定查找算法的數據結構，這些結構以某種方式指向數據(類比指針)；

這樣，就可以在這些數據結構基礎之上，實現高效查找算法，這種數據結構就是索引。

可能的數據結構：

//之前接觸的del_flag，使用邏輯刪除，一來是保留數據進行數據分析，二來是如上圖所示，為了保證索引的完整不失效。

所以說，頻繁刪改的字段不適合做索引。

2.優勢與劣勢

優勢——排序和查找

提高檢索效率，降低IO成本

降低排序成本，減少CPU消耗

劣勢

實際上，索引也是一張表，保存主鍵與索引字段，指向實體記錄。過多的索引占用系統空間。

大大提高查詢效率，但同時降低了更新的操作(更新數據還需要更改索引)

索引需要根據實際情況變更調整(刪了建，建了刪)

3.MySQL索引分類

單值索引

包含單列的普通索引

唯一索引

與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值(注意和主鍵不同)。

復合索引

多個單列索引與單個多列索引的查詢效果不同，因為：?執行查詢時，MySQL只能使用一個索引，

會從多個索引中選擇一個限制最為嚴格的索引。遵循最左前綴 原則。

基本語法：

(1)創建索引，例如CREATE INDEX 索引的名字 ONtablename (列名1，列名2,...);

(2)修改表，例如ALTER TABLE tablename ADD INDEX索引的名字 (列名1，列名2,...);

(3)創建表的時候指定索引，例如CREATE TABLE tablename ( [...], INDEX索引的名字 (列名1，列名2,...) );

DROP INDEX index_name ONtalbe_nameALTER TABLE table_name DROP INDEX index_name

show index fromtblname;show keys from tblname;

4.索引結構

BTree索引——(多路搜索樹，不是二叉樹！)

大致原理圖如下：

(以下3類目前作了解)

Hash索引

full text全文索引

R-Tree索引

5.建索引與否的情況分析

適合創建：

不合適創建：

四、性能分析

1.Mysql Query optimizer

mysql查詢優化器，它所在的位置是我們前文提到過的第三層

查詢優化器的任務是發現執行SQL查詢的最佳方案

2.常見瓶頸

3.Explain

是什么？

簡稱查詢計劃

能干什么？

表的讀取順序　　數據讀取操作的操作類型　　哪些索引可以使用　　哪些索引被實際使用

表之間的引用　　每張表與多少行被優化器查詢

怎么干？

EXPLAIN +SQL語句

//EXPLAIN包含的計劃信息

EXPLAIN各字段解釋

id：查詢中執行SELECT子句或操作表的順序(永遠是id越大，優先級越高)

1. id相同時，執行順序由上至下

2. 如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

3.id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行

//NULL是最后執行(如合并結果集)

select_type：顯示查詢的類型，主要用于區別普通查詢，聯合查詢，子查詢等復雜查詢

(1)?SIMPLE(簡單SELECT,不使用UNION或子查詢等)

(2)?PRIMARY(查詢中若包含任何復雜的子部分,最外層的select被標記為PRIMARY)

(3)?UNION(UNION中的第二個或后面的SELECT語句)

(4)?DEPENDENT UNION(UNION中的第二個或后面的SELECT語句，取決于外面的查詢)

(5)?UNION RESULT(UNION的結果)

(6)?SUBQUERY(子查詢中的第一個SELECT)

(7)?DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢)

(8)?DERIVED(派生表的SELECT, FROM子句的子查詢)

(9)?UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外鏈接的第一行)

table：顯示這一行的數據是關于哪張表的，有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果，也就是前面id的序號)

type：訪問類型，也就是找到需要行的方式

性能從上到下，依次增加(即ALL性能最差，NULL性能最好)

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區別為index類型只遍歷索引樹

range:只檢索給定范圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

eq_ref: 類似ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關聯條件

const、system: 當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量,system是const類型的特例，當查詢的表只有一行的情況下，使用system

NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

常見如下：(針對的是所有的查詢類型)

possible_keys：顯示可能應用在這張表上的索引，存在一個或者多個。(但不一定被實際使用)

Key：顯示MySQL實際決定使用的鍵(索引)(如果為NULL可能是沒有索引或建了沒用，即索引失效)

注意：

意思就是SELECT 后的列與索引列吻合(個數與順序)。理論上mysql認為用不到索引，實際上是全索引掃描。

可能存在的情況：(以你結婚宴請賓客為例)

理論上可能用到的索引，實際上也用到了。(理論宴請的應當到的人，實際也到了)

理論上可能用到的索引，實際上沒用到。(宴請了，本該到場，實際沒到場)

理論上沒用到的索引，實際上也沒用到。(沒宴請，也沒來)

理論上沒用到的索引，實際上用到了。(沒宴請的人，不請自來)

key_len：索引中使用的字節數，可以由此計算出索引的長度。(顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的)

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數(表示上述表的連接匹配條件，即哪些列(例如test.t1.ID，表示test庫的t1表的ID)或常量被用于查找索引列上的值)

rows：表示MySQL根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數。(越小越好)

Extra：該列包含MySQL解決查詢的詳細信息,有以下幾種情況：

Using where:列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示mysql服務器將在存儲引擎檢索行后再進行過濾

Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢

Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”

Using join buffer：改值強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

Impossible where：這個值強調了where語句會導致沒有符合條件的行。

Select tables optimized away：這個值意味著僅通過使用索引，優化器可能僅從聚合函數結果中返回一行

覆蓋索引的理解：

五、索引優化

(左連接加右表，這是由左連接的特性決定的；因為左連接左邊的一定都有，關鍵在于右表記錄的搜索，所以右表必須建立索引)

1.索引失效的情況

其中，索引的最左前綴原則，通俗來說：帶頭大哥不能死(否則無法使用索引)，中間兄弟不能斷(例如1，3只能使用1的索引，部分使用)

Note:

LIKE后使用%abc%形式的模糊查詢，索引失效，解決方案是覆蓋索引！

其中：WHERE a = 3 AND b LIKE 'kk%' AND c = 2　　——使用索引abc

WHERE a = 3 AND b LIKE '%kk%' AND c = 2　　——使用索引a(b就已經失效)

WHERE a = 3 AND b LIKE 'k%kk%' AND c = 2　　——使用索引abc(定值開頭，使用索引，與>的范圍是不同的)

字符串類型不加單引號導致索引失效的原因是MySQL底層完成了一次類型轉換，由第3條即可知道原因。

含有ORDER BY的例如：WHERE a = 3 AND b=2 ORDER BY c3，會用到索引，不會有filesort(不過不會被統計，因為索引的功能有兩個：排序和查找),一般而言，和索引順序不一致的排序：ORDER BY c,b都會出現 filesort，只有在排序字段已經為定值(WHERE b = 2 ORDER BY c,b)情況下不會。

含有GROUP BY的，表面上是分組，實則分組前必排序。順序錯亂后會產生臨時表(use temporary use filesort)必須優化！

小結的口訣如下：