mysql > A.id , A.title , B.title from A left join B on A.catid = B.id left join C on A. = C.id where A.id < 100 ; +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ | id | | table | type | | key| | ref| rows | Extra| +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ | 1 | | A| range | | | 4| NULL| 90 | Using where | | 1 | | B| | | | 4| . A . catid| 1 || | 1 | | C| | | | 4| . A . | 1 | Using index | +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+ 3 rows in set ( 0.05 sec )
再次進行排序操作的時候，Using 也沒有再出現
mysql > A.id , A.title, B.title from A left join B on A.catid = B.id left join C on A. = C.id where A.id < 100 order by A.id ; +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ | id | | table | type | | key| | ref| rows | Extra| +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ | 1 | | A| range | | | 4| NULL| 105 | Using where | | 1 | | B| | | | 4| . A . catid| 1 || | 1 | | C| | | | 4| . A . | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+ 3 rows in set ( 0.00 sec )
結論：
對where條件里涉及到的字段，Mysql會使用索引進行搜索，而這個索引的使用也對排序的效率有很好的提升
測試
分別讓以下兩個SQL語句執行200次：
A.id , A.title , B.title from A left join B on A.catid = B.id left join C on A. = C.id A.id, A.title, B.title from A , B, C where A.catid = B.id and A. = C.id A.id , A.title , B.title from A left join B on A.catid = B.id left join C on A. = C.id order by rand () limit 10 A.id from A left join B on B.id = A.catid left join C on A. = C.id order by A.id
結果是第(1)條平均用時27s ，第(2)條平均用時54s ，第(3)條平均用時80s ，第(4)條平均用時3s 。
用觀察第(3)條語句的執行情況，會發現它創建了臨時表來進行排序。
知識點：
對需要查詢和排序的字段要加索引。盡量少地連接表。left join 比普通連接查詢效率要高，注意觀察索引是否起了作用。排序盡量對第一個表的索引字段進行，可以避免mysql創建臨時表，這是非常耗資源的。對where條件里涉及到的字段，應適當地添加索引，這樣會對排序操作有優化的作用。如果說在分頁時我們能先得到主鍵，再根據主鍵查詢相關內容，也能得到查詢的優化效果。避免使用order by rand()。在執行過程中用show 查看，會發現第(3)條有 to tmp table on disk。Slow 檢查一下是哪些語句降低的Mysql 的執行效率，并進行定期優化。
優化GROUP BY語句
如果查詢包括GROUP BY 但想要避免排序結果的消耗，則可以指定ORDER By NULL禁止排序
例如：
id, sum() from group by id \ id, sum() from group by id order by null \G
比較發現第一條語句會比第二句在Extra:里面多了Using .而恰恰是最耗時的。
優化ORDER BY語句
在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。WHERE 條件和 ORDER BY使用相同的索引，并且ORDER BY的順序和索引順序相同，并且ORDER BY的字段都是升序或者都是降序。
例如：
* FROM t1 ORDER BY ,,....: * FROM t1 WHERE = 1 ORDER BY DESC, DESC; * FROM t1 ORDER BY DESC, DESC;
但是以下的情況不使用索引：
* FROM t1 ORDER BY DESC, ASC; --ORDER by的字段混合ASC 和 DESC * FROM t1 WHERE key2= ORDER BY key1; --用于查詢行的關鍵字與ORDER BY 中所使用的不相同 * FROM t1 ORDER BY key1, key2; --對不同的關鍵字使用ORDER BY
優化LIMIT分頁
當需要分頁操作時，通常會使用LIMIT加上偏移量的辦法實現，同時加上合適的ORDER BY字句。
如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的文件排序操作。
當偏移量非常大的時候，比如：LIMIT 20000 20這樣的查詢，MySQL需要查詢條記錄然后只返回20條記錄，前面的20000條都將被拋棄，這樣的代價非常高。
優化這種查詢一個最簡單的辦法就是盡可能的使用覆蓋索引掃描，而不是查詢所有的列。然后根據需要做一次關聯查詢再返回所有的列。
測試：
, from order by title limit 50,5;
如果這張表非常大，那么這個查詢最好改成下面的樣子：
film., film. from INNER JOIN( from order by title limit 50,5 ) as tmp USING();
這里的延遲關聯將大大提升查詢效率，讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄后在根據關聯列回原表查詢所需要的列。
有時候如果可以使用書簽記錄上次取數據的位置，那么下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用，比如下面的查詢：
id from t limit 10000, 10; id from t where id > 10000 limit 10;
其他優化的辦法還包括使用預先計算的匯總表，或者關聯到一個冗余表，冗余表中只包含主鍵列和需要做排序的列。
優化UNION
MySQL處理UNION的策略是先創建臨時表，然后再把各個查詢結果插入到臨時表中，最后再來做查詢。因此很多優化策略在UNION查詢中都沒有辦法很好的時候。經常需要手動將WHERE、LIMIT、ORDER BY等字句“下推”到各個子查詢中，以便優化器可以充分利用這些條件先優化。
除非確實需要服務器去重，否則就一定要使用UNION ALL，如果沒有ALL關鍵字，MySQL會給臨時表加上選項，這會導致整個臨時表的數據做唯一性檢查，這樣做的代價非常高。當然即使使用ALL關鍵字，MySQL總是將結果放入臨時表，然后再讀出，再返回給客戶端。雖然很多時候沒有這個必要，比如有時候可以直接把每個子查詢的結果返回給客戶端。
特定類型查詢優化
優化COUNT()查詢
COUNT()有兩種不同的作用，其一是統計某個列值的數量，其二是統計行數。
統計列值時，要求列值是非空的，它不會統計NULL。如果確認括號中的表達式不可能為空時，實際上就是在統計行數。
最簡單的就是當使用COUNT(*)時，并不是我們所想象的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計行數。
我們最常見的誤解也就在這兒，在括號內指定了一列卻希望統計結果是行數，而且還常常誤以為前者的性能會更好。
但實際并非這樣，如果要統計行數，直接使用COUNT(*)，意義清晰，且性能更好。
有時候某些業務場景并不需要完全精確的COUNT值，可以用近似值來代替，出來的行數就是一個不錯的近似值，而且執行并不需要真正地去執行查詢，所以成本非常低。通常來說，執行COUNT()都需要掃描大量的行才能獲取到精確的數據，因此很難優化，MySQL層面還能做得也就只有覆蓋索引了。如果不還能解決問題，只有從架構層面解決了，比如添加匯總表mysql查詢4到10條數據，或者使用redis這樣的外部緩存系統。
優化關聯查詢
在大數據場景下，表與表之間通過一個冗余字段來關聯，要比直接使用JOIN有更好的性能。如果確實需要使用關聯查詢的情況下，需要特別注意的是：
確保ON和USING字句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用列c關聯的時候，如果優化器關聯的順序是A、B，那么就不需要在A表的對應列上創建索引。沒有用到的索引會帶來額外的負擔，一般來說，除非有其他理由，只需要在關聯順序中的第二張表的相應列上創建索引（具體原因下文分析）。
確保任何的GROUP BY和ORDER BY中的表達式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優化。
要理解優化關聯查詢的第一個技巧，就需要理解MySQL是如何執行關聯查詢的。當前MySQL關聯執行的策略非常簡單，它對任何的關聯都執行嵌套循環關聯操作，即先在一個表中循環取出單條數據，然后在嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為為止。然后根據各個表匹配的行，返回查詢中需要的各個列。
太抽象了？以上面的示例來說明，比如有這樣的一個查詢：
A.xName, b.yName from A INNER JOIN B USING(c) WHERE A.xName IN (5,6)
假設MySQL按照查詢中的關聯順序A、B來進行關聯操作，那么可以用下面的偽代碼表示MySQL如何完成這個查詢：
= A.xName, A.c from A where A.xName IN(5,6); = .next; while(){ = B.yName from B WHERE B.c = .c; = .next; while() { [.yName, .xx]; = .next; } = .next; }
可以看到，最外層的查詢是根據A.xName，A.c上如果有索引的話，整個關聯查詢也不會使用。再看內層的查詢，很明顯B.c上如果有索引的話，能夠加速查詢，因此只需要在關聯順序中的第二張表的相應列上創建索引即可。
1、MySQL不會使用索引的情況：非獨立的列
“獨立的列”是指索引列不能是表達式的一部分，也不能是函數的參數。比如：
* from t where id + 1 = 15
其等價于 id = 14，但是MySQL無法自動解析這個表達式，使用函數是同樣的道理。
2、前綴索引
如果列很長，通常可以索引開始的部分字符，這樣可以有效節約索引空間，從而提高索引效率。
3、多列索引和索引順序
在多數情況下，在多個列上建立獨立的索引并不能提高查詢性能。
因為MySQL不知道選擇哪個索引的查詢效率更好，所以在老版本，比如.0之前就會隨便選擇一個列的索引，而新的版本會采用合并索引的策略。
示例：
在一張電影演員表中，在和兩個列上都建立了獨立的索引，然后有如下查詢：
, from where = 1 or = 1
老版本的MySQL會隨機選擇一個索引，但新版本做如下的優化：
, from where = 1 union all , from where and 1
當出現多個索引做相交操作時（多個AND條件），通常來說一個包含所有相關列的索引要優于多個獨立索引。
當出現多個索引做聯合操作時（多個OR條件），對結果集的合并、排序等操作需要耗費大量的CPU和內存資源，特別是當其中的某些索引的選擇性不高，需要返回合并大量數據時，查詢成本更高。所以這種情況下還不如走全表掃描。
結論：
如果發現有索引合并（Extra字段出現Using union），檢查查詢和表結構，檢查索引（或許一個包含所有相關列的多列索引更適合）。
多列索引時索引的順序對于查詢是至關重要的，應該把選擇性更高的字段放到索引的前面，這樣通過第一個字段就可以過濾掉大多數不符合條件的數據。
索引選擇性
索引選擇性是指不重復的索引值和數據表的總記錄數的比值，選擇性越高查詢效率越高，因為選擇性越高的索引可以讓MySQL在查詢時過濾掉更多的行。
唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。
示例：
* from where = 2 and = 785
是應該創建(,)的索引還是應該顛倒一下順序？
selct count( )/count(*) as , count( )/count(*) as ity, count(*) from
哪個字段的選擇性更接近1就把哪個字段索引前面，多數情況下使用這個原則沒有任何問題，但仍然注意你的數據中是否存在一些特殊情況。
示例：
比如要查詢某個用戶組下有過交易的用戶信息：
from where = 1 and > 0
MySQL為這個查詢選擇了索引(,)，看起來沒有任何問題。
但實際情況是這張表的大多數數據都是從老系統中遷移過來的，由于新老系統的數據不兼容，所以就給老系統遷移過來的數據賦予了一個默認的用戶組。
這種情況下，通過索引掃描的行數跟全表掃描基本沒什么區別，索引也就起不到任何作用。
經驗法則可以指導我們開發和設計，但實際業務場景下的某些特殊情況可能會摧毀你的整個設計。
4、避免多個范圍條件
實際開發中，我們會經常使用多個范圍條件，比如想查詢某個時間段內登錄過的用戶：
USER.* from USER where > '2019-01-01' and age 18 and 30
這個查詢有一個問題：它有兩個范圍條件，列和age列mysql查詢4到10條數據，MySQL可以使用列的索引或者age列的索引，但無法同時使用它們。
5、覆蓋索引
如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。
覆蓋索引可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：
6、使用索引掃描來排序
MySQL有兩種方式可以生產有序的結果集
其一是對結果集進行排序的操作，
其二是按照索引順序掃描得出的結果自然是有序的。
如果type列的值為index表示使用了索引掃描來做排序。掃描索引本身很快，因為只需要從一條索引記錄移動到相鄰的下一條記錄。
但如果索引本身不能覆蓋所有需要查詢的列，那么就不得不每掃描一條索引記錄就回表查詢一次對應的行。
這個讀取操作基本上是隨機I/O，因此按照索引順序讀取數據的速度通常要比順序地全表掃描要慢。
知識點：
示例
有一種情況例外，就是最左的列被指定為常數
// 最左列為常數，索引：(date,,) , from where date = '2015-06-01' order by ,
7、冗余和重復索引
立即刪除。冗余索引是指在相同的列上按照相同的順序創建的相同類型的索引。比如有一個索引(A,B)，再創建索引(A)就是冗余索引。
大多數情況下都應該盡量擴展已有的索引而不是創建新索引，冗余索引經常發生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴展已有的索引(A)。
但有極少情況下出現性能方面的考慮需要冗余索引，比如擴展已有索引而導致其變得過大，從而影響到其他使用該索引的查詢。
8、定期檢查，可刪除長期未使用的索引
只有當索引幫助提高查詢速度帶來的好處大于其帶來的額外工作時，索引才是有效的。所以 后再提測是一種美德。
talk is easy, show me the code.