Mysql查詢優化彙總 order by優化例子，group by優化例子，limit優化例子，優化建議
索引
索引是一種存儲引擎快速查詢記錄的一種資料結構。
注意
MYSQL一次查詢隻能使用一個索引，這個說法是不正确的，MYSQL會在兩個索引列中，使用OR查詢的時候，進行索引合并(index_merge;Using union(col1,col2);)，但這種建立索引會使得索引資料的膨脹，不建議使用。如果對多個字段使用索引，建立使用複合索引。
備援和重複索引
Mysql需要單獨維護重複的索引，并且優化查詢的時候也需要逐個進行考慮，這會影響性能。
重複索引是指在相同的列上按照相同的順序建立的相同類型的索引。應該避免這樣建立重複索引，發現以後也應該立即移除。
例如：
在唯一限制和主鍵ID上建立索引，則是重複索引。Mysql的唯一限制和主鍵限制都是通過索引實作的。
如果建立了索引(A，B)，再建立(A)索引，就是備援索引，這隻是一個索引的字首索引。
如果建立了索引(A，B)，再建立(B，A)則不是備援索引。
大多數情況下都不需要備援索引，應該盡量擴充已有的索引而不是建立新的索引。
解決備援和重複索引的方法很簡單，删除這些索引就可以了。可以使用Percona-Toolikt的pt-duplicate-key-checker檢查重複索引。
索引的優點：
索引大大減少了伺服器需要掃描的資料量。
索引可以幫助伺服器避免排序和臨時表。
索引可以将随機I/O變為順序I/O。
CREATE TABLE `tab` (
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
KEY `col1_2` (`col1`,`col2`,`col3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
insert into tab (col1, col2, col3) values (1,2,3,1),(4,5,6,1),(4,5,6,2),(4,5,6,3),(4,5,6,4),(4,5,6,3),(4,5,6,3);
CREATE TABLE `tab_1` (
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ORDER BY優化
在ORDER BY操作中，MYSQL隻有在排序條件不是一個查詢表達式的情況下才使用索引。
說明：表：tab，字段：col1，col2，col3，索引：INDEX(col1，col2，col3)。
在關聯表查詢(JOIN)中，order by第一張表會使用索引排序，如果是使用JOIN的話，由于優化器在優化時可能将第二個表當成第一張表即(RIGHT JOIN)，那麼實際上無法使用索引，如：desc select t.col1 from tab t left join tab_1 t1 on t.id = t1.id order by t.col1;。
能夠使用到索引的情況
desc select col1, col2, col3 from tab order by col1, col2, col3。
desc select col1, col2 from tab where col1 = 1 order by col2。[有where的時候，前導列為常數的情況下可以用索引]
desc select * from tab where col1 < 4 order by col1 desc。
desc select * from tab where col1 = 10 and col2 > 10 order by col2 desc。
desc select * from tab where col1 < 10 and col2 > 20 order by col1 desc。
不能夠使用索引的情況
desc select col1 from tab where col1 > 18 order by col2, col3。[col1是一個範圍查詢條件，而不是一個常數，無法使用索引]
desc select * from tab where col1 > 1 order by col2, col1。[排序鍵順序與索引中列順序不一緻，無法使用索引排序]
desc select col1, col2 from tab order by col1 desc, col2 asc。[升序降序不一緻，無法使用索引排序]
desc select col1, col2 from tab where col1 > 1 order by col2。[條件1是範圍查詢，無法使用索引排序，具體群組合索引資料分布有關系]
desc select * from tab where col1 = 1234 or col2 = 4321 order by col2。[當你邏輯表達式為OR時，使用索引排序會丢失，無法使用索引排序]
desc select * from tab where col1 = 123 and col2 in (1,2) order by col3。[IN查詢也是一種範圍查詢，無法使用索引排序]
desc select * from tab where col1 = 123 order by col2, col4。[ORDER BY子句中引用了一個不在索引中的列，col4，無法使用索引排序]
LIMIT優化
對limit分頁問題的性能優化方法-延遲關聯
推薦使用“延遲關聯”的方法來優化排序操作，何為“延遲關聯”：通過使用覆寫索引查詢傳回需要的主鍵，然後再根據主鍵關聯原表獲得需要的資料來加速分頁查詢。
我們都知道，利用了索引查詢的語句中如果隻包含了那個索引列(覆寫索引)，那麼這種情況會查詢很快。因為利用索引查找有優化算法，且資料就在查詢索引上面，不用再去找相關的資料位址了，這樣節省了很多時間。
例如
select * from ummor_user_detail ud order by user_id limit 300000,20 ;
優化寫法1
select * from ummor_user_detail ud where user_id >= (select user_id from ummor_user_detail order by user_id limit 300000,1) limit 20 ;
優化寫法2
select * from ummor_user_detail as a join (select user_id from ummor_user_detail order by user_id limit 300000,20 ) as b on a.user_id = b.user_id;
GROUP BY
MYSQL有三種索引掃描方式完成GROUP BY操作，分别是松散索引掃描和緊湊索引掃描以及臨時表實作GROUP BY。
在松散索引掃描下，分組操作和範圍預測(如果有的話)一起執行完成的。
在緊湊索引掃描下，先對索引執行範圍掃描(range scan)，再對結果元祖進行分組。
GROUP BY優化
desc select * from tab where col1 > 1 group by col1, col2, col3, col4。[松散索引掃描]
desc select * from tab where col2 > 1 group by col1, col3。[緊湊索引掃描，必須加where，在查詢中存在常量相等的where條件字段(索引中的字段，且該字段在GROUP BY指定的字段的前面或者中間)]
desc select * from tab where col1 > 1 group by col2, col3。
desc select * from tab where col1 > 1 group by col3, col4。[臨時表實作GROUP BY]
1. 松散索引掃描[Loose Index San]
使用松散索引掃描需要滿足以下條件
查詢在單一表上。
GROUP BY指定的所有列是索引的一個最左字首，并且沒有其他的列。比如：表tab1(col1，col2，col3，col4)上建立了索引(col1，col2，col3)。如果查詢包含“group by col1，col2”，那麼可以使用松散索引掃描。但是“group by col2，col3”(不是最左字首)和“group by col1，col2，col4”(col4字段不在索引中)無法使用。
如果在選擇清單select list中存在聚集函數，隻能使用MIN()和MAX()兩個聚合函數，并且指定的事同一個列(如果min()和max()同時存在)，這一列必須在索引中，且緊跟着GROUP BY指定的列。比如：select col1,col2,min(col3),max(col3) from tab group by col1, col2。
如果查詢中存在除了group by指定的列之外的索引其他部分，那麼必須以常量的形式出現(除了min()和max()兩個聚集函數)。比如：select col1, col3 from tab group by col1, col2不能使用松散索引掃描。而select col1, col3 from tab where col3 = 3 group by col1, col2可以使用松散索引掃描。
2. 緊湊索引掃描(Tight Index Scan)
緊湊索引掃描可能是全索引掃描或者範圍索引掃描，取決于查詢條件。
緊湊索引掃描實作GROUP BY和松散索引掃描的差別主要在于他需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然後再根據讀取的資料來完成GROUP BY 操作得到相應結果。
3. 使用臨時表實作GROUP BY
優化建議
JOIN。
在ON字段上加索引，并且字段類型必須是相同的，否則MYSQL無法使用到它們的索引。
使用 ENUM 而不是 VARCHAR。
避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN ORDER BY RAND 等這樣的操作符。
把IP位址存成 UNSIGNED INT。
固定長度的表會更快。
垂直分割。
“垂直分割”是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和字段的數目，進而達到優化的目的。
例一：在Users表中有一個字段是家庭位址，這個字段是可選字段，相比起，而且你在資料庫操作的時候除了個人資訊外，你并不需要經常讀取或是改寫這個字段。那麼，為什麼不把他放到另外一張表中呢？ 這樣會讓你的表有更好的性能，大家想想是不是，大量的時候，我對于使用者表來說，隻有使用者ID，使用者名，密碼，使用者角色等會被經常使用。小一點的表總是會有好的性能。
示例二： 你有一個叫 “last_login” 的字段，它會在每次使用者登入時被更新。但是，每次更新時會導緻該表的查詢緩存被清空。是以，你可以把這個字段放到另一個表中，這樣就不會影響你對使用者ID，使用者名，使用者角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。
另外，你需要注意的是，這些被分出去的字段所形成的表，你不會經常性地去Join他們，不然的話，這樣的性能會比不分割時還要差，而且，會是極數級的下降。
拆分大的 DELETE 或 INSERT 語句。
如果你需要在一個線上的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，别的操作都進不來了。
越小的列會越快。建議：布爾/枚舉：tinyint，日期與時間戳：timestamp或int，char/text/blob: 盡量用符合實際長度的varchar(n)，小數及貨币：移位轉為int 或 decimal，IP位址：int。
在建立臨時表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。
盡量使用數字型字段，若隻含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接配接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接配接時會 逐個比較字元串中每一個字元，而對于數字型而言隻需要比較一次就夠了。
NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法。
能用UNION ALL就不要用UNION，需知道兩者差別。
盡量避免使用or，會導緻資料庫引擎放棄索引進行全表掃描。
SELECT * FROM t WHERE id = 1 OR id = 3
優化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
能夠用BETWEEN的就不要用IN。
能夠用DISTINCT的就不用GROUP BY。
盡量不要用SELECT INTO語句。SELECT INTO 語句會導緻表鎖定，阻止其他使用者通路該表。
UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是性能差别是很大的。
索引字段上進行運算會使索引失效。
資料表結構優化建議
根據資料分析建議來修改表結構，使之更符合資料存儲規範：PROCEDURE ANALYSE(1)。
如：select * from tab PROCEDURE ANALYSE(1);分析出每個字段。
編寫MYSQL習慣
1. SELECT * from TABLE1 和 SELECT * FROM TABLE1
上面的兩條SQL語句對于查詢緩沖是完全不同的SELECT。而且查詢緩沖并不自動處理空格，是以，在寫SQL語句時，應盡量減少空格的使用，尤其是在SQL首和尾的空格(因為，查詢緩沖并不自動截取首尾空格)。
2. 關鍵字大寫。如：SELECT，LIKE，OR。
例子：
desc select col1, col2 from tab where col1 = 1 or col2 = 1; # index col1, index col2 索引合并，不建議
desc select * from tab where col1 = 1 or col2 = 2; # index col1, index col2，使用col1索引,索引合并，不建議使用
desc select col1, col2 from tab where col1 = 1 or col2 = 1; # index col1 col3, index col2 沒有使用到索引，不是獨立索引列
desc select * from tab where col1 = 1 or col2 = 2; # index col1 col3, index col2 沒有使用到索引，不是獨立索引列
desc select col1,col2 from tab where col1 = 1 or col2 = 1; # index col1, col2 使用到了索引
desc select * from tab where col1 = 1 or col2 = 1; # index col1, col2 沒有使用到索引
對比下面
desc
select * from tab where col1 = 1
union all
select * from tab where col2 = 2; # 使用這個政策是第一條語句能夠使用到索引，而第二條不行，這樣就有一半的資料還是能夠使用到索引，而不是整條語句使用不到索引。如果col2也有做索引列的話，那麼兩條均能使用到索引查詢。