原文地址：https://blogs.oracle.com/mysql/post/mysql-query-optimization-top-3-tips
原文作者：David Stokes 

MySQL 查詢優(yōu)化一般是一個簡單工程，但是去查找關(guān)于怎么優(yōu)化查詢的信息時，在很多網(wǎng)站都是像哈利·波特咒語一樣被對待。不過這里就有一些你需要知道的簡單技巧以使你的查詢最優(yōu)化。

技巧一 ：MySQL查詢優(yōu)化器在每次查詢出現(xiàn)時都想對它進行優(yōu)化

每次你的查詢被MySQL服務接收時，查詢優(yōu)化器都是像第一次那樣從未見過，當做全新的問題一樣的對待，想要去優(yōu)化這個查詢，即使已經(jīng)有10打完全一樣的查詢同時都正在運行了。像其他的數(shù)據(jù)庫（比如Oracle）就允許查詢執(zhí)行計劃被鎖定，但是MySQL則每次都一樣的對待。

這個問題沒有很好的辦法除非你使用優(yōu)化器提示（hints)去強制降低影響。例如你從經(jīng)驗上知道連接時b表驅(qū)動a表性能比其他方式更好，那你可以放個優(yōu)化器提示指令去跳過其他優(yōu)化進程部分步驟。優(yōu)化器提示只作用于一個查詢或一個語句，因此是不會對其他查詢性能產(chǎn)生影響的。

技巧二 ：計算順序

運算的順序是每個初學者都要學習的東西，因為這對于理解計算機運算操作是非常重要的。在處理優(yōu)化查詢時注意 MySQL 也有很多順序依賴的問題。
讓我們來假定在計算商品的成本和運算成本上你有一個函數(shù)索引。你的客戶對銷售的產(chǎn)品成本和產(chǎn)品的運輸成本都非常敏感，為了提升性能，你創(chuàng)建了一個函數(shù)索引在計算 產(chǎn)品成本和運輸成本上。
如下函數(shù)索引：

CREATE index cost_and_shipping ON products((cost + shipping));

在如下樣例查詢上使用EXPLAIN 可以看到查詢計劃顯示新索引確實用到了去搜索成本小于5的產(chǎn)品。

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((cost + shipping) < 5)  (cost=1.16 rows=2)
    -> Index range scan on PRODUCTS using cost_and_shipping  (cost=1.16 rows=2)
1 row in set (0.0008 sec)

從上面例子來看函數(shù)索引是正常工作的，但是你同事嘗試使用這個神奇的索引時，他們說性能鮮有提升。最后他們共享了他們的查詢和EXPLAIN 報告（顯示這個神奇的索引沒有被使用到），他們查詢不得不執(zhí)行一個更慢的全表掃描，這發(fā)生了什么？？

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE shipping + cost < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((products.shipping + products.cost) < 5)  (cost=0.65 rows=4)
    -> Table scan on PRODUCTS  (cost=0.65 rows=4)
1 row in set (0.0016 sec)

你注意到問題了嗎?
如果沒有，這就有點微妙了。這個索引是以 (cost + shipping)這個格式創(chuàng)建的，你的查詢使用的也是(cost + shipping) 這個格式，但他們的查詢使用的是(shipping + cost) 這個格式。在這個情況下，優(yōu)化器是意識不到 (cost + shipping) 和 (shipping + cost)是運算等價的。這個是非常容易顛倒這兩列順序的，從而不知不覺地陷入到了性能困境。為了從函數(shù)索引中獲得期望的性能，就必須使用正確的組件（比如列）順序。

技巧三： 新的EXPLAIN 格式

這個EXPLAIN新的變體提供了驚人的新細節(jié)。EXPLAIN 命令被習慣用于看查詢計劃，系統(tǒng)將運行以獲取數(shù)據(jù)的實際查詢，以及關(guān)于查詢?nèi)绾芜\行的詳細信息。傳統(tǒng)的輸出提供了一些非常好的細節(jié)。

EXPLAIN FORMAT=TRADITIONAL SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: PRODUCTS
   partitions: NULL
         type: range
possible_keys: cost_and_shipping
          key: cost_and_shipping
      key_len: 9
          ref: NULL
         rows: 2
     filtered: 100
        Extra: Using where
1 row in set, 1 warning (0.0008 sec)
Note (code 1003): /* select#1 */ select `demo`.`products`.`id` AS `id`,`demo`.`products`.`name` AS `name`,`demo`.`products`.`cost` AS `cost`,`demo`.`products`.`shipping` AS `shipping`,(`demo`.`products`.`cost` + `demo`.`products`.`shipping`) AS `(cost + shipping)` from `demo`.`products` where ((`cost` + `shipping`) < 5)

這里優(yōu)化器再次意識到可以利用cost_and_shipping索引,優(yōu)化器通過掃描這個索引將返回兩行數(shù)據(jù)，這還有一些其他的我們現(xiàn)在可能不感興趣的細節(jié)。
樹格式對信息提供了一個不同的視覺。

EXPLAIN FORMAT=TREE SELECT id, name, cost, shipping, (cost + shipping) 
FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((cost + shipping) < 5)  (cost=1.16 rows=2)
    -> Index range scan on PRODUCTS using cost_and_shipping  (cost=1.16 rows=2)
1 row in set (0.0008 sec)

在樹（TREE）格式中添加的內(nèi)容使我們得到了一個更容易的閱讀展示，我們還可以看到查詢的成本。它還明確提示了我們where條件查詢中使用了 filter。
對于優(yōu)化器如何對待你的查詢，如果你真的渴望細節(jié)，想要一個更細粒度的視角。那么，這里有個JSON格式的，你可以看看。

EXPLAIN FORMAT=JSON SELECT id, name, cost, shipping, (cost + shipping) FROM PRODUCTS 
WHERE cost + shipping  < 5\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.16"
    },
    "table": {
      "table_name": "PRODUCTS",
      "access_type": "range",
      "possible_keys": [
        "cost_and_shipping"
      ],
      "key": "cost_and_shipping",
      "used_key_parts": [
        "(`cost` + `shipping`)"
      ],
      "key_length": "9",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.96",
        "eval_cost": "0.20",
        "prefix_cost": "1.16",
        "data_read_per_join": "208"
      },
      "used_columns": [
        "id",
        "cost",
        "shipping",
        "name",
        "(`cost` + `shipping`)"
      ],
      "attached_condition": "((`cost` + `shipping`) < 5)"
    }
  }
}
1 row in set, 1 warning (0.0023 sec)
Note (code 1003): /* select#1 */ select `demo`.`products`.`id` AS `id`,`demo`.`products`.`name` AS `name`,`demo`.`products`.`cost` AS `cost`,`demo`.`products`.`shipping` AS `shipping`,(`demo`.`products`.`cost` + `demo`.`products`.`shipping`) AS `(cost + shipping)` from `demo`.`products` where ((`cost` + `shipping`) < 5)

現(xiàn)在我們有了閱讀的成本，評估的成本等等更多的信息了。

譯者備注：

第一點 講到了MySQL和其他數(shù)據(jù)庫（比如Oracle）一個非常重要的不同點：MySQL沒有硬解析、軟解析的概念，MySQL每執(zhí)行一條SQL（哪怕完全一樣的SQL多次執(zhí)行）時都要進行語法檢查、語義檢查、查詢轉(zhuǎn)換、生成執(zhí)行樹（執(zhí)行計劃）、進行執(zhí)行等完全的解析執(zhí)行步奏，而不會像Oracle那樣將生成的執(zhí)行計劃保存到SGA里，后面再執(zhí)行時可以拿來直接使用，以避免生成執(zhí)行計劃這些非常耗費資源(CPU、時間）的步驟。從這點也可以理解為什么MySQL的開發(fā)規(guī)范里都說要避免復雜SQL，因為復雜SQL生成執(zhí)行計劃的過程都是很復雜、很耗時的，而MySQL每次執(zhí)行都要經(jīng)過這個耗時的過程，那么SQL的執(zhí)行效率自然不會高，整個數(shù)據(jù)庫的性能都可能受到影響。
第二點 講到了優(yōu)化器處理一些復雜點運算的注意事項，優(yōu)化器整體是非常聰明的（大部分情況可以從眾多執(zhí)行路徑中找到一個最優(yōu)或較優(yōu)的執(zhí)行路徑），但有時候你發(fā)現(xiàn)它又是非常笨的，比如文中舉例的，創(chuàng)建了函數(shù)索引，查詢時列的順序換了下就不認識了，相當于記住了1 + 2 = 3,但你問他 2 + 1 等于多少就要重新計算了，與之類似的問題還有 當索引列上 使用了 函數(shù)、運算、表達式、隱式轉(zhuǎn)換 等操作都可能導致這個查詢列使用不上索引而變成全表掃描。
第三點 提供了多種查看執(zhí)行計劃信息的選項，以提供執(zhí)行計劃信息不同的詳細程度，大家可以根據(jù)自己需要來選擇使用哪個。