原文地址: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ù)自己需要來選擇使用哪個。




