1。SQL的優化主要涉及到索引。SQL的查詢，可以用explain來進行SQL陳述句的決議。從里面可以看到SQL陳述句執行的順序，比如資料庫表查詢是怎么連接的。在它連接的時候，先查小便再查大表。以及可能使用到索引，具體使用哪個索引，等一些欄位。都可以通過explain的結果反映出來。其實我們可以根據結果進行優化如果沒有使用到索引，那么，我們就可以把沒有使用到索引的原因找出來。從而改寫SQL陳述句。
2。索引的底層是一個B+tree，針對每個欄位添加了索引以后會生成一顆B+tree，主鍵索引又叫聚簇索引，索引里面的葉子結點直接保存表中的值，而其他索引，保存的是索引欄位以及主鍵的值，這樣在進行其他的普通索引查詢以后，還需要對聚簇索引進行回表查詢。為了防止進行回表查詢，也可以針對普通索引做覆寫索引，這樣普通索引里面存盤的也是表中的欄位，就可以不用再回表去聚簇索引那里做二次查詢。不過一般不建議這么做。
這里的話一些普通的應用，比如，like陳述句什么時候不用索引；不能where后面進行運算式操作；select后面必須指定欄位。等等就不詳細介紹了，相信大家都知道。
同樣的or條件。也可能造成不使用索引，可以使用union all，來代替or來進行SQL陳述句的改寫。
以前看過一些文章。說SQL用in陳述句運算式，會造成不用索引，其實是不對的。in運算式，其實也是會使用索引的。但是in不能連接太多條件。如果條件是連續的，可以使用between來代替in。如果是包含了子查詢的SQL，也可以使用exists，not exist來代替in，exist和in的區別主要在于，是由子查詢來驅動外層查詢，還是由外層查詢來驅動查詢，exist主要是外層表驅動子查詢，當外層驅動的表大于子查詢的表，使用exist；否則則用in，in運算式先進行子查詢，然后再由子查詢驅動外層查詢。具體是先執行外層查詢還是查詢，也可以用explain來校驗結果。
現在經常會說。where陳述句后面不能有null值的判斷以及不等于判斷，會造成不使用索引。其實這個是不對的。有null的SQL也可能會使用到索引。可以用explain去決議一條包含null的陳述句。會發現其實是使用到索引的。null的資料，會存放在b+tree的最左邊節點，所以也是可以走索引的。
其實走不走索引，主要原因是mysql的內部優化器決定的。針對于每一條SQL陳述句，mysql會進行分析，例如一個null的判斷，如果索引中null的值數量非常大，在查詢到null所有的值以后，還需要針對聚簇索引，進行回表查詢。如果資料量太大，查詢的效果比全表掃描還慢。mysql優化器會自動選擇全表掃描，而不走索引。mySQL有一個演算法，會模糊的計算出查詢出來的值大概有多少條。優化器不只會根據情況來決定使不使用索引，也會涉及到索引的使用優先級，例如兩個表join，優化器會選擇更小的表的索引欄位來進行查詢。如果需要強制使用哪個索引，可以使用force index加索引名，來強制選擇使用的索引。也可以使用straight_join來強制左邊的表成為驅動表（一般使用在inner join，且左邊的表小右邊的表大，由小表驅動大表，因為join條件是遍歷驅動表，再由驅動表的條件去找被驅動表，如果驅動表比較小，則可以減少回圈的次數）。
3。對于SQL的分頁。使用limit如果資料量太大，前面需要跳過的條數太多，會造成SQL查詢非常緩慢。這是需要針對SQL進行處理，例如：如果是自增長的id，并且沒有洗掉資料，可以對ID進行跳頁操作，例如翻到第5頁，可以在翻頁前面加上 ID>5*pageSize 的條件。如果是可以洗掉的操作，那么可以限制跳頁，只允許翻頁，這樣就可以獲取前一頁最大的ID進行條件判斷（這里的分頁條件不局限于ID，也可以是時間，前提是條件必須添加索引）。
當資料量過大時，需要進行分表分庫。對某個欄位進行水平拆分之后，跨庫的分頁查詢，會造成難題。一般的做法就是當翻到第幾頁時，兩個庫就取相同的頁碼進行比較，取兩邊資料，合并后最小的那一頁。
但這樣會有一個問題。就是當頁碼過大以后，獲取的資料就會越多，分頁性能會急速下降。這時需要用到上文所說的，根據分表欄位進行整改，例如如果根據自增長的ID進行分表，并且禁止跳頁，可以根據前一頁的最大ID進行SQL的修改。跳過最大的ID取得靠近的一頁，兩臺機器的這一頁資料回傳在進行資料的比對，獲取資料最小的一頁回傳給前端，這樣可以避免當頁碼過大以后獲取每一張分表的資料太大。
如果能夠確保分表時候的欄位非常均勻。也可以直接跳過對應的資料。例如有兩個分表，每頁10條記錄需要查第5頁。此時可以針對兩個分表，每一個分表，跳20條記錄進行查詢，每張分表查5條記錄，這個方法可能會丟失資料精度，但業務允許的話還是非常方便的。
除此之外，還有一種能夠確保精度，而且可以避免查詢資料量太多的分頁方法。上面說到的跳躍資料量的方法中，因為不知道具體應該跳的最大ID是多少，所以只能在多個分表中平攤。假設知道應該跳躍的最大ID是多少，就可以明確需要跳躍的ID在各個分表中的位置。精確的位置之后，再取出對應分頁條數進行拼接就可以得到需要查詢頁碼的精確資料。所以這個分頁我們可以進行兩次查詢。第1次查詢，是為了定位需要跳躍的最大ID，在每一個分表中的位置，為了找出需要跳躍的最大ID是多少；第2次查詢在進行前文提到的查詢合并資料。那么要如何來找到這個需要跳躍的最大id呢？我們可以進行前文說到的分攤查找，比如有三張分表，如果要跳第30條記錄，那每一張分表，就跳10條記錄，然后從三張分表中獲取最小的資料進行比對，然后，其他兩張分表再拿最小的那條資料分表的資料記錄再進行第2次查詢，第2次查詢其他兩張分表的資料結果集，不止包含第1次查詢出來的資料，還包含了第1張分表最小資料，到他們各自最小資料的記錄。這時就能定位到，需要跳的第30條記錄，在各個分表的位置。定位到需要跳的最大ID。的位置以后就可以取出你想要的分頁資料（注：以上所說ID只是案例欄位，其他有規律欄位同理）。
uj5u.com熱心網友回復：
感謝分享建議寫成博客轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/54254.html
標籤：MySQL