面試的時候經常會文件一些SQL方面的問題，比較常見的面試題例如“什麼時候回造成索引失效？”，又或者“你經常做的SQL最佳化的工作有哪些？”

下面，我就介紹幾個有關SQL最佳化的知識點。

負向條件查詢不能使用索引：包括!=、not in、not exists都儘量不要使用；

%在前面的模糊查詢：where name like "%xxx"；

等號左邊有函式：where upper(str) = "..."，就算str欄位有索引，這個寫法也不會走索引；

資料區分度不大的欄位，不要建索引：例如性別男、女、為止，這種就不適合建立索引；

隱式轉換：where tel = 13800000000，如果tel欄位是varchar型別，這個寫法不會報錯，但是會索引失效；

只返回需要的資料：select name，gender from users 優於 select * from users；

允許為Null的列，有風險：比如 where name != "Tom"，如果name允許為Null，索引不儲存Null值，結果集不包含這些記錄；因為索引不儲存Null值，所以is null也不會走索引；

如果業務大部分是單條記錄查詢，那麼Hash索引效率更高

複合索引最左字首：（name,gender）複合索引，where name=xx and gender =x 可以命中，where name=xx可以命中，where gender =x不能命中。

之前在BAT裡參與過一個公司級應用（非市場級，投入的人力也不會那麼大），上線2年後，總是被使用者投訴，原因是這個應用使用MySql資料庫來做持久層，但是2年了，有一張非常重要的儲存歷史任務的表實在是太大了，導致透過頁面想要查詢歷史資料的速度變得非常非常慢，所以使用者很是不滿意。

分析下來，這不是用Redis能解決的快取問題，而是歷史資料的查詢響應速度問題。

雖然經常被使用者投訴反應慢，也不能破罐破摔，直接超時不響應了吧。

於是我們陷入了兩難的境地。

後來我們分了兩個部分來最佳化持久層。

MySQL的主從配置

第一步就是配置MySQL的主從庫，透過將讀寫請求分離，來提高資料庫的響應速度。

從上圖可知，來自同一臺伺服器的請求，經過MySQL-proxy被分流給了不同的MySQL節點，其中寫請求給了主節點，讀請求給了從節點。因此，我們首先透過分流的方式，減輕了單節點MySQL的響應壓力，實現了最佳化的第一步。

引入ElasticSearch

但是，只配置MySQL的主從是遠遠不夠的。

透過查閱論壇，相關資料，我們最終敲定在持久層引入ElasticSearch。

Elastic Search是一個輕量級的持久層工具，它支援動態多節點部署，自動備份，節點掉線後能夠自動切換主從，動態廣播發現新上線的節點，而這些優點的應用，無須修改任何server端配置。可以這樣理解，如果你部署了4個elastic search節點，其中2個掉了，伺服器還是可以很好的繼續執行。

此外，它還有一個最重要的優勢，那就是支援大資料快速查詢。一張幾千萬的表，如果用MySQL查詢，可能需要幾秒到幾十秒不等，但是如果用elastic search，只需要毫秒級別就能查詢到結果。完美的解決了我們當前的問題，還順帶幫我們鞏固了持久層的穩定性問題。

綜上，最佳化Mysql的目的是為持久層服務，除了引入主從配置，當MySQL自身侷限性導致無法繼續最佳化後，引入其他技術也是十分必要的。

作為一個碼農，每天都會和資料庫打交道，有時候需要最佳化sql語句也是難免的。在這裡就列舉一些工作中常用到的一些sql最佳化手段吧。

1.給欄位加索引。這個是大家都能想到的，但這裡不得不說，加索引也是有技巧的，對於一些區分不是很大的情況來說，例如一個欄位表示刪除與否的狀態只有0,1兩種值的情況下，這個欄位就不要加索引了。意義不大。同時，如果某張表裡有排序欄位的話，而且出現的頻率比較高的，例如透過符合索引來加欄位。例如(field_name,create_time),經常需要這樣查詢where field = field_name order create_time。當然，複合索引也遵循字首的原則，當我們只是需要where field = field_name的時候，查詢也會走索引。

2.使用IN查詢時裡面的值不應該過多。MySQL對於IN做了相應的最佳化，即將IN中的常量全部儲存在一個數組裡面，而且這個陣列是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from t where num in(1,2,3) 對於連續的數值，能用 between 就不要用 in 了。

3.在查詢的過程中如果不需要查詢出全部欄位，儘量不要使用select *查詢。MySQL在查詢資料的時候，需要先把資料先拿出來然後再按照條件來篩選的。如果用*查詢的話，就需要把整條記錄都拿出，增加了很多不必要的消耗（cpu、io、記憶體、網路頻寬）；增加了使用覆蓋索引的可能性。

4.當需要拿出一條資料時，在sql結尾處最好加上limit 1。

5.模糊查詢的時候進行用右模糊的方式。例如select field_name from table_name like field_name like "xxx%"。這樣做的好處就是在查詢的時候會走索引，減少查詢時間。

6.連表查詢的時候儘量使用inner join，避免left join；被驅動表的索引欄位作為on的限制欄位以此合理利用索引。參與聯合查詢的表至少為2張表，一般都存在大小之分。如果連線方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。

7.查詢的判斷條件中儘量的不要使用!=,not in或者是判斷null等情況。這些情況會導致查詢的效率變慢。

8.合理的利用分頁方式來提高分頁效率。例如select id,name from product limit 800000, 20。這條sql語句在隨著表的增大的過程中查詢會變得越來越慢。這種情況我們可以取前一頁的最大行數的id，然後根據這個最大的id來限制下一頁的起點。例如select id,name from product where id > 800000 limit 20。

9.使用Explain來檢視自己的sql語句使用索引的情況。如果是在命令列模式下的話，則是explain select.........的形式來檢視。如果是用的Navicat這樣的sql工具的話，也可以在查詢的時候直接按解釋，也會出現sql索引使用的情況。形如下面：

其主要關鍵的幾個欄位的意思如下：

type列，連線型別。一個好的sql語句至少要達到range級別。杜絕出現all級別key列，使用到的索引名。如果沒有選擇索引，值是NULL。可以採取強制索引方式key_len列，索引長度rows列，掃描行數。該值是個預估值extra列，詳細說明。注意常見的不太友好的值有：Using filesort, Using temporary

當然的，mysql還有其他方面的最佳化。如果你真的想要深入對mysql的理解的話，也可以買一些書籍來看，深入mysql才能真正知道如何最佳化最好。