Sql性能優化看這一篇就夠了

點擊上方藍色「終端研發部」，選擇「設為星標」

前言：
一個優秀開發的必備技能：性能優化，包括：JVM調優、緩存、Sql性能優化等。本文主要講基於Mysql的索引優化。
首先我們需要了解執行一條查詢SQL時Mysql的處理過程：
其次我們需要知道，我們寫的SQL在Mysql的執行順序是怎麼樣的？sql的執行順序對sql的性能優化很有幫助，很重要。在建立複合索引的時候需要考慮到這點。
例：
在tb_dept中建立一個複合索引 idx_parent_id_code：
然後看下兩個sql 解釋的結果：
1）在當前索引下，哪一個sql索引利用率高？
藉助於上文中查詢SQL的執行順序，是先執行 WHERE再執行 GROUP BY 的，即：
第一個sql執行的順序是先執行了 where後的 parent_id然後執行了 group by 後的 dept_code，順序是和索引的順序是一致的，type等級為ref，掃描行數rows為 4；
而第二個sql是先執行了 where後的 dept_code然後執行了 group by 後的 parent_id，順序是和索引的順序是不一致的，type等級為index，掃描行數rows為 19；
從解釋結果看，第一條的sql索引利用率高於第二條的。（後文會講到：索引type從優到差：System→const→eq_ref→ref-->ref_or_null→index_merge→unique_subquery→index_subquery→range→index-->all.）
或者從掃描的行數rows對比數據源也可直觀的看出，兩個語句的性能：
2）怎麼優化？
如果業務中用到第二個sql，那麼就需要調整索引的順序和sql執行順序一致。
或者兩個sql都用到了，那麼就再建一個複合索引 （idx_code_parent_id）
然後再看下第二條的執行計劃：
執行計劃分析（下面就是本文的重點內容了）：
通過explain可以知道mysql是如何處理語句的，並分析出查詢或是表結構的性能瓶頸，其實就是在幹查詢優化器的事，通過expalin可以得到：
1. 表的讀取順序
2.表的讀取操作的操作類型
3.哪些索引可以使用
4. 哪些索引被實際使用
5.表之間的引用
6.每張表有多少行被優化器查詢
從上文的例子中我們可以看到執行explain時，結果會有一個表格，這個表格就是分析結果，下面我們來一個一個說明下這個表的表頭：
Id: MySQL QueryOptimizer 選定的執行計劃中查詢的序列號。表示查詢中執行select 子句或操作表的順序,id 值越大優先級越高,越先被執行。id 相同,執行順序由上至下。
Select_type: 一共有9中類型,只介紹常用的4種:
SIMPLE: 簡單的 select 查詢,不使用 union 及子查詢
PRIMARY: 最外層的 select 查詢
UNION: UNION 中的第二個或隨後的 select 查詢,不 依賴於外部查詢的結果集
DERIVED: 用於 from 子句裡有子查詢的情況。MySQL 會 遞歸執行這些子查詢, 把結果放在臨時表裡。
Table: 輸出行所引用的表
Type: 從優到差的順序如下:（紅色標識的是常見的級別。）
system→const→eq_ref→ref-->ref_or_null→index_merge→unique_subquery→index_subquery→range→index→all.
各自的含義如下:
system: 表僅有一行。這是 const 連接類型的一個特例。
const: const 用於用常數值比較 PRIMARY KEY 時。
eq_ref: 查詢使用了索引為主鍵或唯一鍵的全部時使用。即：通過索引關鍵字可能查找到一個符合條件的行。
ref: 通過索引關鍵字可能查找到多個符合條件的行。
ref_or_null: 如同 ref, 但是 MySQL 必須在初次查找的結果裡找出 null 條目,然後進行二次查找。
index_merge: 說明索引合併優化被使用了。
unique_subquery: 在某些 IN 查詢中使用此種類型,而不是常規的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery: 在 某 些 IN 查 詢 中 使 用 此 種 類 型 , 與unique_subquery 類似,但是查詢的是非唯一 性索引
range: 檢索給定範圍的行。當使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符時,會使用到range。
index: 全表掃描,只是掃描表的時候按照索引次序進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。
all: 最壞的情況,從頭到尾全表掃描。
possible_keys : 哪些索引可能有助於查詢。如果為空,說明沒有可用的索引。
key: 實際從 possible_key 選擇使用的索引，如果為 NULL,則沒有使用索引。很少的情況 下,MYSQL 會選擇優化不足的索引。這種情 況下,可以在 SELECT語句中使用 USE INDEX (indexname)來強制使用一個索引或者用IGNORE INDEX(indexname)來強制 MYSQL 忽略索引
key_len: 使用的索引的長度。在不損失精確性的情況 下,長度越短越好。
ref: 顯示索引的哪一列被使用了
rows: 請求數據返回的大概行數
extra: 其他信息，出現Using filesort、Using temporary 意味著不能使用索引,效率會受到重大影響。應儘可能對此進行優化。
Using filesort: 沒有辦法利用現有索引進行排序，需要額外排序，建議：根據排序需要，創建相應合適的索引
Using temporary: 需要用臨時表存儲結果集，通常是因為group by的列列上沒有索引。也有可能是因為同
時有group by和order by，但group by和order by的列又不一樣
Using index ：利用覆蓋索引，無需回表即可取得結果數據（即數據直接從索引文件中讀取），這種結果是好的。
其中重要的幾個就是 key、type 、rows、extra，其中key為null、all 、index時，需要調整、優化索引。一般需要達到 ref、eq_ref 級別，範圍查找需要達到 range，extra有Using filesort、Using temporary 的一定需要優化，根據rows可以直觀看出優化結果。
優化手段：
① SQL優化
避免 SELECT *，只查詢需要的欄位。
小表驅動大表，即小的數據集驅動大的數據集：
當B表的數據集比A表小時，用in優化 exist兩表執行順序是先查B表再查A表查詢語句：SELECT * FROM tb_dept WHERE id in (SELECT id FROM tb_dept) ;
當A表的數據集比B表小時，用exist優化in ，兩表執行順序是先查A表，再查B表，查詢語句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;
儘量使用連接代替子查詢，因為使用 join 時，MySQL 不會在內存中創建臨時表。
② 優化索引的使用
儘量使用主鍵查詢，而非其他索引，因為主鍵查詢不會觸發回表查詢。
不做列運算，把計算都放入各個業務系統實現
查詢語句儘可能簡單，大語句拆小語句，減少鎖時間
or 查詢改寫成 union 查詢
不用函數和觸發器
避免 %xx 查詢，可以使用：select * from t where reverse(f) like reverse(『%abc』);
少用 join 查詢
使用同類型比較，比如 『123』 和 『123』、123 和 123
儘量避免在 where 子句中使用 != 或者 <> 操作符，查詢引用會放棄索引而進行全表掃描
列表數據使用分頁查詢，每頁數據量不要太大
避免在索引列上使用 is null 和 is not null
③ 表結構設計優化
使用可以存下數據最小的數據類型。
儘量使用 tinyint、smallint、mediumint 作為整數類型而非 int。
儘可能使用 not null 定義欄位，因為 null 佔用 4 字節空間。數字可以默認 0 ，字符串默認 「」
儘量少用 text 類型，非用不可時最好獨立出一張表。
儘量使用 timestamp，而非 datetime。
單表不要有太多欄位，建議在 20 個欄位以內。
Mysql常用數據類型存儲大小及範圍：https://blog.csdn.net/HXNLYW/article/details/100104768
3.如果以上優化還是有問題，可以使用show profiles 分析sql 性能
show profiles
show profile for query [queryId]
具體請查看：https://blog.csdn.net/aeolus_pu/article/details/7818498
結尾：
本文是最近學習Mysql索引優化的一些總結和記錄，如有不對的地方，歡迎評論吐槽。
附：
索引相關知識：
———— 查看表索引：
show index from 【table】
———— 直接創建索引
CREATE INDEX indexName ON table(column(length))
———— 修改表結構的方式添加索引
ALTER tableADD INDEX indexName ON (column(length))
-—主鍵索引
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
-—唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column` )
-—普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column`(length) )
-—複合索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
length的確定：
如果索引列長度過長,這種列索引時將會產生很大的索引文件,不便於操作,可以使用前綴索引方式進行索引，前綴索引應該控制在一個合適的點,控制在0.31黃金值即可(大於這個值就可以創建)。
SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 這個值大於0.31就可以創建前綴索引,Distinct去重複
———— 刪除索引：
1）ALTER TABLE table_name DROP INDEX index_name
2）DROP INDEX index_name ON table_name;
MyISAM 和 InnoBD區別：
mysql相關配置參數優化：
• sort-buffer-size/join-buffer-size / read-rnd-buffer-size，4～8MB為宜
• optimizer_switch=「index_condition_pushdown=on,mrr=on,mrr_cost
_based=off,batched_key_access=on」
• tmp-table-size = max-heap-table-size，100MB左右為宜
• log-queries-not-using-indexes & log_throttle_queries_not_using_indexes
作者：葫蘆胡
連結：https://blog.csdn.net/HXNLYW/article/details/82979088
 
BAT等大廠Java面試經驗總結
 想獲取 Java大廠面試題學習資料
掃下方二維碼回復「BAT」就好了
回復 【加群】獲取github掘金交流群
回復 【電子書】獲取2020電子書教程
回復 【C】獲取全套C語言學習知識手冊
回復 【Java】獲取java相關的視頻教程和資料
回復 【爬蟲】獲取SpringCloud相關多的學習資料
回復 【Python】即可獲得Python基礎到進階的學習教程
回復 【idea破解】即可獲得intellij idea相關的破解教程關注我gitHub掘金，每天發掘一篇好項目，學習技術不迷路！
回復 【idea激活】即可獲得idea的激活方式
回復 【Java】獲取java相關的視頻教程和資料
回復 【SpringCloud】獲取SpringCloud相關多的學習資料
回復 【python】獲取全套0基礎Python知識手冊
回復 【2020】獲取2020java相關面試題教程
回復 【加群】即可加入終端研發部相關的技術交流群
閱讀更多
相信自己，沒有做不到的，只有想不到的
在這裡獲得的不僅僅是技術！

喜歡就給個「在看」