SQL優化總結
- 通過 show status 了解SQL執行頻率</strong></span>
</span></span></span>
通過show status可以提供服務器狀態信息，可以根據需要顯示當前session級別的統計結果和 global級別的統計結果。
如：顯示當前session，show status like "Com_%"，全局級別：show global status。
</span>以下幾個參數對 Myisam 和 Innodb 存儲引擎都計數：
1. Com_select 執行 select 操作的次數，一次查詢只累加 1 ；
2. Com_insert 執行 insert 操作的次數，對于批量插入的 insert 操作，只累加一次 ；
3. Com_update 執行 update 操作的次數；
4. Com_delete 執行 delete 操作的次數；
以下幾個參數是針對 Innodb 存儲引擎計數的，累加的算法也略有不同：
1. Innodb_rows_read select 查詢返回的行數；
2. Innodb_rows_inserted 執行 Insert 操作插入的行數；
3. Innodb_rows_updated 執行 update 操作更新的行數；
4. Innodb_rows_deleted 執行 delete 操作刪除的行數；
通過以上幾個參數，可以很容易的了解當前數據庫的應用是以插入更新為主還 是以查詢操作為主，以及各種類型的 SQL大致的執行比例是多少。
此外，以下幾個參數便于我們了解數據庫的基本情況：
1. Connections 試圖連接 Mysql 服務器的次數
2. Uptime 服務器工作時間
3. Slow_queries 慢查詢的次數
2. 定位執行效率低的SQL
可以通過以下兩種方式定位執行效率較低的 SQL 語句：
2. 使用 show processlist查看當前MYSQL的線程， 命令慢查詢日志在查詢結束以后才紀錄，所以在應用反映執行效率出現問題的時候查詢慢查詢日志并不能定位問題，可以使用 show processlist 命令查看當前 MySQL 在進行的線程，包括線程的狀態，是否鎖表等等，可以實時的查看 SQL 執行情況， 同時對一些鎖表操作進行優化。
1. 可以通過慢查詢日志定位那些執行效率較低的 sql 語句
在my.ini文件下的mysqld下添加如下代碼：
log-slow-queries=c:/mysql/slowquery.log
long_query_time=2
上面的配置打開了slow query日志,將會捕獲了執行時間超過了2秒的查詢，包括執行速度較慢的管理命令(比如OPTIMEZE TABLE),并且記錄了沒有使用索引的查詢。這些SQL，都會被記錄到log-slow-queries指定的文件c:/mysql/slowquery.log 文件中。
索引用于快速找出在某個列中有一特定值的行。對相關列使用索引是提高SELECT 操作性能的最佳途徑。
3. 通過explain或desc分析低效的SQL
通過以上步驟查詢到效率低的 SQL 后，我們可以通過 explain 或者 desc 獲取MySQL 如何執行 SELECT 語句的信息，包括 select 語句執行過程表如何連接和連接 的次序。
4. MySQL索引
查詢要使用索引最主要的條件是查詢條件中需要使用索引關鍵字，如果是多列 索引，那么只有查詢條件使用了多列關鍵字最左邊的前綴時（ 前綴索引 ），才可以使用索引，否則將不能使用索引。
下列情況下， Mysql 不會使用已有的索引：
1、如果 mysql 估計使用索引比全表掃描更慢，則不使用索引。
2、如果使用 heap表 并且 where 條件中不用＝索引列， 其他 > 、 < 、 >= 、 <= 均不使用索引 （MyISAM和innodb表使用索引）；
3、使用or分割的條件，如果or前的條件中的列有索引，后面的列中沒有索引，那么涉及到的索引都不會使用。
4、如果創建復合索引，如果條件中使用的列不是索引列的第一部分； （不是前綴索引）
5、如果 like 是以％開始；
6、對 where 后邊條件為字符串的一定要加引號，字符串如果為數字 mysql 會自動轉為字符串，但是不使用索引。
5. 索引的使用情況
如果索引正在工作， Handler_read_key 的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的性能改善不高，因為索引并不經常使用。
Handler_read_rnd_next 的值高則意味著查詢運行低效，并且應該建立索引補救。這個值的含義是在數據文件中讀下一行的請求數。如果你正進行大量的表掃描， 該值較高。通常說明表索引不正確或寫入的查詢沒有利用索引。
語法：show status like 'Handler_read%'。
6. 優化查詢語句
1） 對查詢進行優化，應避免全表查詢，首先應該考慮在where及order by設計的列上建立索引
2） 應盡量避免在 where 子句中對字段進行 null 值判斷 ，否則將導致引擎放棄使用索引而進行全表掃描
NULL對于大多數數據庫都需要特殊處理，MySQL也不例外，它需要更多的代碼，更多的檢查和特殊的索引邏輯，有些開發人員完全沒有意識到，創建表時NULL是默認值，但大多數時候應該使用NOT NULL，或者使用一個特殊的值，如0，-1作為默認值。
3） 應盡量避免在 where 子句中使用!=或<>操作符， 否則將引擎放棄使用索引而進行全表掃描
不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。 任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。
MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。
可以在LIKE操作中使用索引的情形是指另一個操作數不是以通配符（%或者_）開頭的情形。例如:
SELECT id FROM t WHERE col LIKE 'Mich%'; -- 這個查詢將使用索引，
SELECT id FROM t WHERE col LIKE '%ike'; -- 這個查詢不會使用索引。
4）應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描
在某些情況下，or條件可以避免全表掃描：
1. where 語句里面如果帶有or條件, myisam表能用到索引， innodb不行。
2. 必須所有的or條件都必須是獨立索引5）并不是所有索引對查詢都有效
6） 索引并不是越多越好
SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有字段sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。
索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
7）應盡可能的避免更新 clustered 索引數據列
因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那么需要考慮是否應將該索引建為 clustered 索引。
8） 盡量使用數字型字段
若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。
9） 盡可能的使用 varchar/nvarchar 代替 char/nchar
因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。
10） 最好不要使用"*"返回所有： select * from t
用具體的字段列表代替"*"，不要返回用不到的任何字段。
7. 優化Order by語句
MySQL的弱點之一是它的排序。雖然MySQL可以在1秒中查詢大約15,000條記錄，但由于MySQL在查詢時最多只能使用一個索引。因此，如果WHERE條件已經占用了索引，那么在排序中就不使用索引了，這將大大降低查詢的速度。我們可以看看如下的SQL語句:
SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL 的WHERE子句中已經使用了NAME字段上的索引，因此，在對SALE_DATE進行排序時將不再使用索引。為了解決這個問題，我們可以對SALES表建立復合索引:
ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
這樣再使用上述的SELECT語句進行查詢時速度就會大副提升。但要注意，在使用這個方法時，要確保WHERE子句中沒有排序字段，在上例中就是不能用SALE_DATE進行查詢，否則雖然排序快了，但是SALE_DATE字段上沒有單獨的索引，因此查詢又會慢下來。
在某些情況中， MySQL可以使用一個索引來滿足 ORDER BY子句，而不需要額外的排序。 where條件和order by使用相同的索引，并且order by 的順序和索引順序相同，并且order by的字段都是升序或者都是降序。例如：下列sql可以使用索引。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情況不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1; -- 用于查詢行的關鍵字與 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2; -- 對不同的關鍵字使用 ORDER BY
8. Explain/Desc 解釋說明
explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。使用方法，在select語句前加上explain就可以了。
結果分析：
table | type | possible_keys | key | key_len | ref | rows | Extra
1）table
顯示這一行的數據是關于哪張表的
2）type
這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為：system、const、eg_reg、ref、ref_or_null、range、indexhe、ALL。
system:表僅有一行(=系統表)。這是const聯接類型的一個特例
const:(PRIMARY KEY或UNIQUE)
表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。 const表很快，因為它們只讀取一次！
eg: SELECT * from tbl_name WHERE primary_key=1；
eq_reg:key
對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。
它用在一個索引的所有部分被聯接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。ref:key
對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基于關鍵字選擇單個行的話），則使用ref。
如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。 ref可以用于使用=或<=>操作符的帶索引的列。
ref_or_null:Or Is null
該聯接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行。在解決子查詢中經常使用該聯接類型的優化。
range:=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN
只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。
當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range：
indexhe:
該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小。
當查詢只使用作為單索引一部分的列時，MySQL可以使用該聯接類型。
ALL：
對于每個來自于先前的表的行組合，進行完整的表掃描。如果表是第一個沒標記const的表，這通常不好，并且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，
使得行能基于前面的表中的常數值或列值被檢索出。
3）possible_keys
顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
4）key
實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。
這種情況下，可以在SELECT語句中使用USEINDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引。
5）ken_len
使用的索引的長度。在不損失精確性的情況下，長度越短越好
6）ref
顯示索引的哪一列被使用了，如果可能的話，是一個常數
7）rows
MYSQL認為必須檢查的用來返回請求數據的行數 (掃描行的數量)
8）Extra
該列包含MySQL解決查詢的詳細信息，這里可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢。
extra列返回的描述的意義：
Distinct:
一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了
Not exists :
MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行， 就不再搜索了。下面是一個可以這樣優化的查詢類型的例子：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL；
假定t2.id定義為NOT NULL。在這種情況下，MySQL使用t1.id的值掃描t1并查找t2中的行。如果MySQL在t2中發現一個匹配的行，它知道t2.id絕不會為NULL，并且不再掃描t2內有相同的id值的行。
換句話說，對于t1的每個行，MySQL只需要在t2中查找一次，無論t2內實際有多少匹配的行。
Range checked for each Record（index map:#）
沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，并用它來從表中返回行。
這是使用索引的最慢的連接之一 ，MySQL沒有發現好的可以使用的索引，但發現如果來自前面的表的列值已知，可能部分索引可以使用。
對前面的表的每個行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。
不同的是前面表的所有列值已知并且認為是常量。這并不很快，但比執行沒有索引的聯接要快得多。
Using filesort
看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。
它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
Using index
列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，
這發生在對表的全部的請求列都是同一個索引的部分的時候
Using temporary
看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上
Using where
使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題