mysql hint優(yōu)化的示例分析

這篇文章將為大家詳細(xì)講解有關(guān)mysql hint優(yōu)化的示例分析，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

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

執(zhí)行計(jì)劃如下所示：

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+---------+---------------------------------+

| id | select_type | table       | type    | possible_keys        | key                  | key_len | ref                           | rows     | Extra                            |

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+---------+---------------------------------+

|  1 | PRIMARY      | <derived2> | ALL     | NULL                 | NULL                 | NULL     | NULL                          |       57 | Using temporary; Using filesort |

|  1 | PRIMARY      | confrec     | ALL     | contractid           | NULL                 | NULL     | NULL                          | 1007935 | Using where                      |

|  1 | PRIMARY      | c2          | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.confrec.contractid     |        1 | Using where                      |

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

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

|  2 | DERIVED      | ca1         | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.c1.contractinapproveid |        1 | Using where                      |

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+---------+---------------------------------+

從執(zhí)行計(jì)劃分析，最大的問(wèn)題是confrec的全表掃描，但是confrec和contract的連接使用了索引?？磮?zhí)行計(jì)劃是對(duì)confrec進(jìn)行了全表掃描，而contract使用了索引，現(xiàn)在修改一下，confrec（百萬(wàn)級(jí)別）用索引,contract（3w）用全表掃描。由于contract的數(shù)據(jù)量小很多，效率高了，從執(zhí)行計(jì)劃的預(yù)估行數(shù)可以看的很明顯。

         使用了sql hint控制了表的連接順序解決該問(wèn)題。

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

修改后的執(zhí)行計(jì)劃：

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+-------+---------------------------------+

| id | select_type | table       | type    | possible_keys        | key                  | key_len | ref                           | rows  | Extra                            |

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+-------+---------------------------------+

|  1 | PRIMARY      | <derived2> | ALL     | NULL                 | NULL                 | NULL     | NULL                          |     57 | Using temporary; Using filesort |

|  1 | PRIMARY      | c2          | ALL     | PRIMARY              | NULL                 | NULL     | NULL                          | 13135 | Using where                      |

|  1 | PRIMARY      | confrec     | ref     | contractid           | contractid           | 4        | ap_db.c2.contractid           |     45 | Using where                      |

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

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

|  2 | DERIVED      | ca1         | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.c1.contractinapproveid |      1 | Using where                      |

+----+-------------+------------+--------+---------------------+---------------------+---------+------------------------------+-------+---------------------------------+

看明顯看到了連接行數(shù)的減少。

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

[@more@]

普通MySQL運(yùn)行，數(shù)據(jù)量和訪問(wèn)量不大的話，是足夠快的，但是當(dāng)數(shù)據(jù)量和訪問(wèn)量劇增的時(shí)候，那么就會(huì)明顯發(fā)現(xiàn)MySQL很慢，甚至down掉，那么就要考慮優(yōu)化我們的MySQL了。

優(yōu)化無(wú)非是從三個(gè)角度入手：
第一個(gè)是從硬件，增加硬件，增加服務(wù)器
第二個(gè)就是對(duì)我們的MySQL服務(wù)器進(jìn)行優(yōu)化，增加緩存大小，開(kāi)多端口，讀寫(xiě)分開(kāi)
第三個(gè)就是我們的應(yīng)用優(yōu)化，建立索引，優(yōu)化SQL查詢語(yǔ)句，建立緩存等等

我就簡(jiǎn)單的說(shuō)說(shuō)SQL查詢語(yǔ)句的優(yōu)化。因?yàn)槿绻覀僕eb服務(wù)器比數(shù)據(jù)庫(kù)服 務(wù)器多或者性能優(yōu)良的話，我們完全可以把數(shù)據(jù)庫(kù)的壓力轉(zhuǎn)嫁到Web服務(wù)器上，因?yàn)槿绻麊闻_(tái)MySQL，或者 Master/Slave 架構(gòu)的數(shù)據(jù)庫(kù)服務(wù)器都負(fù)擔(dān)比較重，那么就可以考慮把MySQL的運(yùn)算放到Web服務(wù)器上去進(jìn)行。當(dāng)然了，如果你Web服務(wù)器比數(shù)據(jù)庫(kù)服務(wù)器差，那就把壓力 放在數(shù)據(jù)庫(kù)服務(wù)器上吧，呵呵。

如果是把MySQL服務(wù)器的壓力放在Web服務(wù)器上，那么很多運(yùn)算就需要我們的程序去執(zhí)行，比如Web程序中全部交給PHP腳 本去處理數(shù)據(jù)。單臺(tái)MySQL服務(wù)器，查詢、更新、插入、刪除都在一臺(tái)服務(wù)器上的話，訪問(wèn)量一大，你會(huì)明顯發(fā)現(xiàn)鎖表現(xiàn)象，當(dāng)對(duì)一個(gè)表進(jìn)行更新刪除操作的時(shí) 候，就會(huì)拒絕其他操作，這樣就會(huì)導(dǎo)致鎖表，解決這個(gè)問(wèn)題最簡(jiǎn)單直接的辦法就是拿兩臺(tái)MySQL服務(wù)器，一臺(tái)負(fù)責(zé)查詢（select）操作，另外一臺(tái)負(fù)責(zé)更 改（update/delete/insert），然后進(jìn)行同步，這樣能夠避免鎖表，如果服務(wù)器更多，那么就更好處理了，可以采用分布式數(shù)據(jù)庫(kù)架構(gòu)和數(shù)據(jù) 的散列存儲(chǔ)，下面我們會(huì)簡(jiǎn)單說(shuō)一下。

一、SQL的優(yōu)化和注意事項(xiàng)

現(xiàn)在我們假設(shè)我們只有一臺(tái)MySQL服務(wù)器，所有的select/update/insert/delete操作都是在這上面進(jìn)行的，我們同時(shí)有三臺(tái)Web服務(wù)器，通過(guò)DNS輪巡來(lái)訪問(wèn)，那么我們?nèi)绾芜M(jìn)行我們應(yīng)用程序和SQL的優(yōu)化。

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

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

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

4. 合理的建立索引
索引的提升速度的一個(gè)非常重要的手段，索引在對(duì)一些經(jīng)常進(jìn)行select操作，并且值比較唯一的字段是相當(dāng)有效的，比如主鍵的id字段，唯一的名字name字段等等。
但是索引對(duì)于唯一值比較少的字段，比如性別gender字段，寥寥無(wú)幾的類(lèi)別字段等，意義不大，因?yàn)樾詣e是50%的幾率，索引幾乎沒(méi)有意義。對(duì)于update/delete/insert非常頻繁的表，建立索引要慎重考慮，因?yàn)檫@些頻繁的操作同樣對(duì)于索引的維護(hù)工作量也是很大的，最后反而得不償失，這個(gè)需要自己仔細(xì)考慮。索引同樣不是越多越好，適當(dāng)?shù)乃饕龝?huì)起到很關(guān)鍵的作用，不適當(dāng)?shù)乃饕炊鴾p低效率維護(hù)，增加維護(hù)索引的負(fù)擔(dān)。

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

二、數(shù)據(jù)庫(kù)服務(wù)器的架構(gòu)和分布想法

對(duì)于服務(wù)器的架構(gòu)設(shè)計(jì)，這個(gè)其實(shí)是比較重要的，一個(gè)合理的設(shè)計(jì)，能夠讓?xiě)?yīng)用更好的運(yùn)行。當(dāng)然，架構(gòu)的設(shè)計(jì)，取決于你的應(yīng)用和你硬件的實(shí)際情況。我就簡(jiǎn)單的說(shuō)說(shuō)幾種不同的數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)方式，權(quán)當(dāng)是一個(gè)個(gè)人的想法，希望能夠有幫助。

1. 單臺(tái)服務(wù)器開(kāi)多進(jìn)程和端口
單臺(tái)MySQL服務(wù)器，如果使用長(zhǎng)鏈接等等都無(wú)法解決負(fù)載太大，連接太多的問(wèn)題，不凡考慮采用一臺(tái)MySQL上使用多個(gè)端口開(kāi)啟多個(gè)MySQL守護(hù)進(jìn)程的方法來(lái)緩解壓力。當(dāng)然，前提是你的應(yīng)用必須支持多端口，并且你的cpu和內(nèi)存足夠運(yùn)行多個(gè)守護(hù)進(jìn)程。
優(yōu)點(diǎn) 是能夠很好的緩解暫時(shí)服務(wù)器的壓力，把不同的操作放在不同的端口，或者把不同的項(xiàng)目模塊放在不同的端口去操作，良好的分擔(dān)單個(gè)守護(hù)進(jìn)程的壓力。
缺點(diǎn) 是數(shù)據(jù)可能會(huì)產(chǎn)生紊亂，同時(shí)可能會(huì)導(dǎo)致很多未知的莫名錯(cuò)誤。呵呵

2. 使用Master/Slave的服務(wù)器結(jié)構(gòu)
Mysql本身具有同步功能，完全可以利用這個(gè)功能。構(gòu)建 Master/Slave 的主從服務(wù)器結(jié)構(gòu)，最少只需要兩臺(tái)MySQL服務(wù)器，我們可以把 Master 服務(wù)器用戶更新操作，包括 update/delete/insert，把Slave服務(wù)器用于查詢操作，包括 select 操作，然后兩機(jī)進(jìn)行同步。
優(yōu)點(diǎn) 是合理的把更新和查詢的壓力分擔(dān)，并且能夠避免鎖表的問(wèn)題。
缺點(diǎn) 是更新部實(shí)時(shí)，如果網(wǎng)絡(luò)繁忙，可能會(huì)存在延遲的問(wèn)題，并且任何一臺(tái)服務(wù)器down掉了都很麻煩。

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

關(guān)于mysql hint優(yōu)化的示例分析就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。