mysql hint優化的示例分析

154次閱讀

沒有評論

共計 7926 個字符，預計需要花費 20 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 mysql hint 優化的示例分析，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

SELECT t.oldcontractno, t.startdate, t.enddate, sum(confrec.confamt)

FROM (

SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

FROM contract c1, contractinapprove ca1, framepolicy f1

WHERE c1.contractinapproveid = ca1.contractid

AND ca1.contractid = f1.contractid

AND c1.contracttype =4

)t, contract c2, confrec

WHERE

(

(substring( c2.oldcontractno, 1, locate( – , c2.oldcontractno) -1 ) = t.oldcontractno)

OR

(c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN (JP , BD)

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

執行計劃如下所示：

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

| 2 | DERIVED | f1 | ALL | FK28E4C8DF253521AD | NULL | NULL | NULL | 262 | |

| 2 | DERIVED | c1 | ref | contractinapproveid | contractinapproveid | 5 | ap_db.f1.contractid | 1 | Using where |

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

從執行計劃分析，最大的問題是 confrec 的全表掃描，但是 confrec 和 contract 的連接使用了索引。看執行計劃是對 confrec 進行了全表掃描，而 contract 使用了索引，現在修改一下，confrec（百萬級別）用索引,contract（3w）用全表掃描。由于 contract 的數據量小很多，效率高了，從執行計劃的預估行數可以看的很明顯。

使用了 sql hint 控制了表的連接順序解決該問題。

SELECT t.oldcontractno, t.startdate, t.enddate, sum(confrec.confamt)

FROM (

SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

FROM contract c1, contractinapprove ca1, framepolicy f1

WHERE c1.contractinapproveid = ca1.contractid

AND ca1.contractid = f1.contractid

AND c1.contracttype =4

)t, contract c2 straight_join confrec

WHERE

(

(substring( c2.oldcontractno, 1, locate( – , c2.oldcontractno) -1 ) = t.oldcontractno)

OR

(c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN (JP , BD)

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

修改后的執行計劃：

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

| 2 | DERIVED | f1 | ALL | FK28E4C8DF253521AD | NULL | NULL | NULL | 262 | |

| 2 | DERIVED | c1 | ref | contractinapproveid | contractinapproveid | 5 | ap_db.f1.contractid | 1 | Using where |

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

看明顯看到了連接行數的減少。

ref: http://hi.baidu.com/veyroner/blog/item/c72827fd9403d3f7fd037f77.html

[@more@]

普通 MySQL 運行，數據量和訪問量不大的話，是足夠快的，但是當數據量和訪問量劇增的時候，那么就會明顯發現 MySQL 很慢，甚至 down 掉，那么就要考慮優化我們的 MySQL 了。

優化無非是從三個角度入手：
第一個是從硬件，增加硬件，增加服務器
第二個就是對我們的 MySQL 服務器進行優化，增加緩存大小，開多端口，讀寫分開
第三個就是我們的應用優化，建立索引，優化 SQL 查詢語句，建立緩存等等

我就簡單的說說 SQL 查詢語句的優化。因為如果我們 Web 服務器比數據庫服
務器多或者性能優良的話，我們完全可以把數據庫的壓力轉嫁到 Web 服務器上，因為如果單臺 MySQL，或者 Master/Slave
架構的數據庫服務器都負擔比較重，那么就可以考慮把 MySQL 的運算放到 Web 服務器上去進行。當然了，如果你 Web 服務器比數據庫服務器差，那就把壓力
放在數據庫服務器上吧，呵呵。

如果是把 MySQL 服務器的壓力放在 Web 服務器上，那么很多運算就需要我們的程序去執行，比如 Web 程序中全部交給 PHP 腳
本去處理數據。單臺 MySQL 服務器，查詢、更新、插入、刪除都在一臺服務器上的話，訪問量一大，你會明顯發現鎖表現象，當對一個表進行更新刪除操作的時
候，就會拒絕其他操作，這樣就會導致鎖表，解決這個問題最簡單直接的辦法就是拿兩臺 MySQL 服務器，一臺負責查詢（select）操作，另外一臺負責更
改（update/delete/insert），然后進行同步，這樣能夠避免鎖表，如果服務器更多，那么就更好處理了，可以采用分布式數據庫架構和數據
的散列存儲，下面我們會簡單說一下。

一、SQL 的優化和注意事項

現在我們假設我們只有一臺 MySQL 服務器，所有的 select/update/insert/delete 操作都是在這上面進行的，我們同時有三臺 Web 服務器，通過 DNS 輪巡來訪問，那么我們如何進行我們應用程序和 SQL 的優化。

1. Where 條件
在查詢中，WHERE 條件也是一個比較重要的因素，盡量少并且是合理的 where 條件是很重要的，在寫每一個 where 條件的時候都要仔細考慮，盡量在多個條件的時候，把會提取盡量少數據量的條件放在前面，這樣就會減少后一個 where 條件的查詢時間。
有時候一些 where 條件會導致索引無效，當使用了 Mysql 函數的時候，索引將無效，比如：select * from tbl1 where
left(name, 4) = hylr，那么這時候索引無效，還有就是使用 LIKE 進行搜索匹配的時候，這樣的語句索引是無效的：select
* from tbl1 where name like %xxx%，但是這樣索引是有效的：select * from tbl1 where
name like xxx%，所以謹慎的寫你的 SQL 是很重要的。

2. 關聯查詢和子查詢
數據庫一個很重要的特點是關聯查詢，LEFT JOIN 和全關聯，特別是多個表進行關聯，因為每個關聯表查詢的時候，進行掃描的時候都是一個笛卡爾乘積的數量級，掃描數量很大，如果確實是需要進行關聯操作，請給 where 或者 on 的條件進行索引。
關聯操作也是可能交給應用去操作的，看數據量的大小，如果數據量不是非常大，比如 10 萬條以下，那么就可以交給程序去處理（totododo 提出筆誤，特此修正），程序分別提取左右兩個表的數據，然后進行循環的掃描處理，返回結果，這個過程同樣非常耗費 Web 服務器的資源，那么就需要取決于你愿意把壓力放在 Web 服務器上或者數據庫服務器上了。
子查詢是在 mysql5 中支持的功能，比如：select * from tbl1 where id in(select id from tbl1)，那樣效率是非常非常低，要盡量避免使用子查詢，要是我，絕對不用，呵呵。

3. 一些耗費時間和資源的操作
SQL 語句中一些浪費的操作，比如 DISTINCT、COUNT、GROUP
BY、各種 MySQL 函數。這些操作都是比較耗資源的，我想應用最多的是 count 字句吧，如果使用 count，盡量不要 count（*），最好
count 一個字段，比如 count（id），或者 count(1)，（據 totododo 測
試效率其實是一樣的），同樣能夠起到統計的作用。如果不是十分必要，盡量不要使用 distinct 操作，就是提取唯一值，你完全可以把這個操作交給腳本程
序去執行提取唯一值，減少 MySQL 的負擔。group by
操作也是，確實需要分組的話，請謹慎的操作，如果是小批量的數據，可以考慮交給腳本程序去做。
至于 MySQL 的函數，估計很多常用，比如有人喜歡把截取字符串也交給 MySQL 去操作，或者時間轉換操作，使用比較多的函數像 SUBSTR(),
CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5()
等等，這些操作完全可以交給腳本程序去做，減輕 MySQL 的負擔。

4. 合理的建立索引
索引的提升速度的一個非常重要的手段，索引在對一些經常進行 select 操作，并且值比較唯一的字段是相當有效的，比如主鍵的 id 字段，唯一的名字 name 字段等等。
但是索引對于唯一值比較少的字段，比如性別 gender 字段，寥寥無幾的類別字段等，意義不大，因為性別是 50% 的幾率，索引幾乎沒有意義。對于 update/delete/insert 非常頻繁的表，建立索引要慎重考慮，因為這些頻繁的操作同樣對于索引的維護工作量也是很大的，最后反而得不償失，這個需要自己仔細考慮。索引同樣不是越多越好，適當的索引會起到很關鍵的作用，不適當的索引，反而減低效率維護，增加維護索引的負擔。

5. 監控 sql 執行效率
在 select 語句前面使用 EXPLAIN 字句能夠查看當前這個 select 字句的執行情況，包括使用了什么操作、返回多少幾率、對索引的使用情況如何等等，能夠有效分析 SQL 語句的執行效率和合理程度。
另外使用 MySQL 中本身的慢查詢日志：slow-log，同樣能夠記錄查詢中花費時間比較多的 SQL 語句，好對相應的語句進行優化和改寫。
另外在 MySQL 終端下，使用 show processlist 命令能夠有效的查看當前 MySQL 在進行的線程，包括線程的狀態，是否鎖表等等，可以實時的查看 SQL 執行情況，同時對一些鎖表操作進行優化。

二、數據庫服務器的架構和分布想法

對于服務器的架構設計，這個其實是比較重要的，一個合理的設計，能夠讓應用更好的運行。當然，架構的設計，取決于你的應用和你硬件的實際情況。我就簡單的說說幾種不同的數據庫架構設計方式，權當是一個個人的想法，希望能夠有幫助。

1. 單臺服務器開多進程和端口
單臺 MySQL 服務器，如果使用長鏈接等等都無法解決負載太大，連接太多的問題，不凡考慮采用一臺 MySQL 上使用多個端口開啟多個 MySQL 守護進程的方法來緩解壓力。當然，前提是你的應用必須支持多端口，并且你的 cpu 和內存足夠運行多個守護進程。
優點是能夠很好的緩解暫時服務器的壓力，把不同的操作放在不同的端口，或者把不同的項目模塊放在不同的端口去操作，良好的分擔單個守護進程的壓力。
缺點是數據可能會產生紊亂，同時可能會導致很多未知的莫名錯誤。呵呵

2. 使用 Master/Slave 的服務器結構
Mysql 本身具有同步功能，完全可以利用這個功能。構建 Master/Slave 的主從服務器結構，最少只需要兩臺 MySQL 服務器，我們可以把
Master 服務器用戶更新操作，包括 update/delete/insert，把 Slave 服務器用于查詢操作，包括 select
操作，然后兩機進行同步。
優點是合理的把更新和查詢的壓力分擔，并且能夠避免鎖表的問題。
缺點是更新部實時，如果網絡繁忙，可能會存在延遲的問題，并且任何一臺服務器 down 掉了都很麻煩。

3. 使用分布式的散列存儲
這種結構適合大數據量，并且負載比較大，然后服務器比較充足的情況。分布式存儲結構，簡單的可以是多臺服務器，每臺服務器功能是類似的，但是存儲的數據不
一樣，比如做一個用戶系統，那么把用戶 ID 在 1 -10 萬以內的存儲在 A 服務器，用戶 ID 在 10-20 萬存儲在 B 服務器，20-3- 萬存儲在 C 服務器，以此
類推。如果每個用戶訪問的服務器不足，可以構建組服務器，就是每組用戶擁有多臺服務器，比如可以在某用戶組建立兩臺 MySQL 服務器，一臺 Master，
一臺 Slave，同樣分離他們的更新和查詢操作，或者可以設計成雙向同步。同時，你的應用程序必須支持跨數據庫和跨服務器的操作能力。
優點是服務器的負載合理的被平攤，每臺服務器都是負責一部分用戶，如果一臺服務器 down 掉了，不會影響其他用戶 ID 的用戶正常訪問。同時添加節點比較容易，如果又增加了 10 萬用戶，那么又可以增加一個節點服務器，升級很方便。
缺點是任何一臺數據庫服務器 down 掉或者數據丟失，那么這部分服務器的用戶將很郁悶，數據都沒了，當然，這個需要良好的備份機制。

關于 mysql hint 優化的示例分析就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。

正文完