MySQL—SQL最佳化

標籤：

學到了幾個不錯的最佳化方法：
1、技術最佳化
在網上有不少對SQL本身最佳化的方法，如建立索引並確保合理有效使用上索引，分析執行計畫，在基於代價執行情境下，通過執行計畫不是高手很難分析出問題（慢慢學習吧~）。
2、業務最佳化
1）明確查詢主表再left join查詢輔助列，from MainTabel m，m中包括整個sql返回結果的全部行；
A表的ID取前10然後再取B表的name
select T.,B.name from (select from A order by a.id limit 10 ) T left join B on t.id = b.id
2）去掉不必要的列和表；
去掉列好說，去掉表別說不可能，見過不少，特別是用到資料量大的表進行關聯取資料，沒業務環境例子不那麼好舉。
select tn.id,tn.cn, t1.c1,t2.c2,… from t1,t2,…,tn where … and tn.id = ‘100’ and tn.cn = ‘cn’
考慮不使用tn表，查詢列用到tn的兩個列如果非得加在sql裡可以寫成select ‘100’ id, ‘cn’ cn, … from …
去掉不必要的表最佳化效果非常明顯，前提是得較深入瞭解業務。
3）利用子查詢限定資料範圍；
select * from t1,t2,…,tn條件全部在where中
實際上用到t2表僅有一條記錄，過多的條件關聯未能確保對於記錄少的表優先處理，可以考慮
select * from t1,(select * from t2 where t2條件),…,tn
4）子查詢未加條件導致範圍過大；
select t1.c1,t1.c2,t2.cnt
from t1,(select c3,max(c4) cnt from t1 group by c3) t2
where t1.c3 = t2.c3
and t1.id1 = ? and t1.id2 = ?
在t2子查詢中group by的是整個t1表資料，實際只需要對id1和id2為特定值的資料進行分組，在子查詢中加上條件。
5）建立暫存資料表或使用with文法；
with w_tab as
(select from t1,t2,t3)
select from w_tab m, w_tab a
6）最佳化count()、min()、max()
min() ：如果使用min()函數的資料列建立了索引，那麼查詢在O(1)的時間內完成，B-tree可以直接找最左端的資料。同理，max()直接找最右端的資料返回
count()：沒有where條件的count對於myisam儲存引擎來說是很快的，因為在myisam引擎中維護了一個變數來儲存行數
7）最佳化order by，使用索引掃描來做排序
order by 的列要滿足最左索引，也可以跟where條件同時使用來滿足最左索引
條件： 1）最左索引，不能跳躍
2）順序跟索引順序一致
3）排序次序要一致，不能一個升序一個降序
樣本：
CREATE TABLE pro_subgroup (
pro_subgroup_id int(10) NOT NULL AUTO_INCREMENT
pro_subgroup_name varchar(50) NOT NULL DEFAULT ”
pro_group_id int(10) NOT NULL DEFAULT ‘0’
PRIMARY KEY (pro_subgroup_id),
KEY name_id (pro_subgroup_id,pro_subgroup_name,pro_group_id)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
EXPLAIN SELECT * FROM pro_subgroup ps
ORDER BY ps.pro_subgroup_id DESC, ps.pro_group_id DESC
Using index; Using filesort
跳躍未使用索引
8）使用覆蓋索引。覆蓋索引就是查詢的列是全部的索引或部分索引列。使用覆蓋索引的好處就是不需要回表再取其他列資料，直接使用索引查詢就能返回結果，查詢效率會提高很多。
對於innodb儲存引擎而言，主鍵索引是叢集索引，叢集索引在葉子結點上儲存了行的其他資訊。二級索引在葉子結點上儲存了行了主索引值。如果在innodb上不使用覆蓋索引，那麼在需要先遍曆二級索引，找到相應的主索引值，然後再從主鍵尋找其他列的資訊。而使用覆蓋索引就可以直接返回索引查詢到的資訊。
對於myisam來說，myisam的索引檔案時存在記憶體中的，資料檔案時放在磁碟中的。如果使用覆蓋索引相當於是只訪問記憶體資料，不用去磁碟尋找。所以效率也是相當好的。
查詢全部的索引列，可以使用覆蓋索引
Key(name)
EXPLAIN SELECT NAME FROM user_admin

注意使用覆蓋索引時，查詢的列必須在一個索引集裡面。比如單獨給name和password建立了兩個索引。此時select name,password from table是無法使用覆蓋索引的
查詢列不在一個索引集裡，無法使用覆蓋索引
Key name(name)
Key password (‘password’)
EXPLAIN SELECT NAME,PASSWORD FROM user_admin

查詢列為索引的一部分，可以使用覆蓋索引
Key name_password (name,password)
EXPLAIN SELECT NAME FROM user_admin

查詢全部的列
EXPLAIN SELECT * FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”

Using index condition :必要的時候查看是否需要全表掃描，因為select * 還是要讀到表資料的，但是使用了索引。
最佳化
EXPLAIN SELECT * FROM user_admin a JOIN (SELECT admin_id FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”) b
ON a.admin_id = b.admin_id

沒有最佳化
繼續。。
修改key:
alter table modify key name_password_email id_name_password(‘admin_id’,’name’,’password’)
EXPLAIN SELECT * FROM user_admin a JOIN (SELECT admin_id FROM user_admin WHERE NAME=’admin’ AND PASSWORD =”) b
ON a.admin_id = b.admin_id

終於可以了，子查詢使用覆蓋索引，然後對查詢出的結果再與主表進行串連查詢其他列資料
如果表使用的引擎是innoDB，那麼可以有效利用主鍵進行覆蓋索引查詢。因為二級索引的葉子結點儲存了主索引值
EXPLAIN SELECT admin_id,NAME FROM user_admin WHERE NAME=”

最佳化總則：
1）索引最佳化
正確的使用索引
2）查詢需要的資料n
innodb引擎的話where條件中有主鍵時可以直接使用select *,因為innodb的主鍵中儲存了其他列資訊，如果不含主鍵在查詢時盡量只查詢需要的欄位。
3）切分查詢
切分查詢是將一個查詢分多次執行。比如一次較大的delete可以分幾次來執行
4）分解關聯查詢
分解關聯查詢就是將一個大的關聯查詢分解為多個小的查詢，一次查詢出一部分資料，可以有效使用緩衝。但是不一定是所有的查詢都要進行分解，因為分解後的查詢語句可讀性不強，對於以後的維護會有一定的困難。

總結：最佳化查詢語句需要從幾方面入手考慮：
1、 減少資料訪問（減少磁碟訪問）
2、 返回更少資料（減少網路傳輸或磁碟訪問）
3、 減少互動次數（減少網路傳輸）
4、 減少伺服器CPU開銷（減少CPU及記憶體開銷）
5、 利用更多資源（增加資源）

MySQL—SQL最佳化