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后的 school_id 然后執行了 group by 后的 grade_id，順序是和索引的順序是一致的，type等級為ref，掃描行數rows為 4；

而第二個sql是先執行了 where后的 grade_id 然后執行了 group by 后的 school_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