qileilove

          blog已經(jīng)轉(zhuǎn)移至github,大家請?jiān)L問 http://qaseven.github.io/

          優(yōu)化MySQL語句的十個(gè)建議

          Jaslabs的Justin Silverton列出了十條有關(guān)優(yōu)化MySQL查詢的語句,我不得不對此發(fā)表言論,因?yàn)檫@個(gè)清單非常非常糟糕。另外一個(gè)Mike也同樣意識(shí)到了。所以在這個(gè)博客中,我要做兩件事情,第一,指出為什么這個(gè)清單很糟糕,第二,列出我的清單,希望我的比較好些。繼續(xù)看吧,無畏的讀者們!

            為什么那個(gè)清單很糟糕

            1、他的力氣沒使對地方

            我們要遵循的一個(gè)準(zhǔn)則就是如果你要優(yōu)化代碼時(shí),應(yīng)該先找出瓶頸在哪。然而Silverton先生的力氣沒有用對地方。我認(rèn)為60%的優(yōu)化是基于清楚理解SQL數(shù)據(jù)庫基 礎(chǔ)的。你需要知道join和子查詢的區(qū)別,列索引,以及如何將數(shù)據(jù)規(guī)范化等等。另外的35%的優(yōu)化是需要清楚數(shù)據(jù)庫選擇時(shí)的性能表現(xiàn),例如 COUNT(*)可能很快也可能很慢,要看你選用什么數(shù)據(jù)庫引擎。還有一些其他要考慮的因素,例如數(shù)據(jù)庫在什么時(shí)候不用緩存,什么時(shí)候存在硬盤上而不存在 內(nèi)存中,什么時(shí)候數(shù)據(jù)庫創(chuàng)建臨時(shí)表等等。剩下的5%就很少會(huì)有人碰到了,但Silverton先生恰好在這上面花了大量的時(shí)間。我從來就沒用過 SQL_SAMLL_RESULT。

            2、很好的問題,但是很糟糕的解決方法

             Silverton先生提出了一些很好的問題。MySQL針對長度可變的列如TEXT或BLOB,將會(huì)使用動(dòng)態(tài)行格式(dynamic row format),這意味著排序?qū)⒃谟脖P上進(jìn)行。我們的方法不是要回避這些數(shù)據(jù)類型,而是將這些數(shù)據(jù)類型從原來的表中分離開,放入另外一個(gè)表中。下面的 schema可以說明這個(gè)想法:

          1. CREATE TABLE posts (  
          2.     id int UNSIGNED NOT NULL AUTO_INCREMENT,  
          3.     author_id int UNSIGNED NOT NULL,  
          4.     created timestamp NOT NULL,  
          5.     PRIMARY KEY(id)  
          6. );  
          7.    
          8. CREATE TABLE posts_data (  
          9.     post_id int UNSIGNED NOT NULL.  
          10.     body text,  
          11.     PRIMARY KEY(post_id)  
          12. );

            3、有點(diǎn)匪夷所思……

             他的許多建議都是讓人非常吃驚的,譬如“移除不必要的括號(hào)”。你這樣寫SELECT * FROM posts WHERE (author_id = 5 AND published = 1),還是這樣寫SELECT * FROM posts WHERE author_id = 5 AND published = 1 ,都不重要。任何比較好的DBMS都會(huì)自動(dòng)進(jìn)行識(shí)別做出處理。這種細(xì)節(jié)就好像C語言中是i++快些還是++i快些。真的,如果你把精力都花在這上面了,那 就不用寫代碼了。

            我的列表

            看看我的列表是不是更好吧。我先從最普遍的開始。

            1、建立基準(zhǔn),建立基準(zhǔn),建立基準(zhǔn)!

            如果需要做決定的話,我們需要數(shù)據(jù)說話。什么樣的查詢是最糟的?瓶頸在哪?我什么情況下會(huì)寫出糟糕的查詢?基準(zhǔn)測試可以讓你模擬高壓情況,然后借助性能測評(píng)工具,可以讓你發(fā)現(xiàn)數(shù)據(jù)庫配置中的錯(cuò)誤。這樣的工具有supersmack, ab, SysBench。這些工具可以直接測試你的數(shù)據(jù)庫(譬如supersmack),或者模擬網(wǎng)絡(luò)流量(譬如ab)。

            2、性能測試,性能測試,性能測試!

            那么,當(dāng)你能夠建立一些高壓情況之后,你需要找出配置中的錯(cuò)誤。這就是性能測評(píng)工具可以幫你做的了。它可以幫你發(fā)現(xiàn)配置中的瓶頸,不論是在內(nèi)存中,CPU中,網(wǎng)絡(luò)中,硬盤I/O,或者是以上皆有。

            你要做的第一件事就是開啟慢查詢日志(slow query log),裝上mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運(yùn)行10秒的查詢語句正在破壞你的應(yīng)用程序嗎?這些家伙會(huì)展示給你看他的查詢語句是怎么寫的。

             在你發(fā)現(xiàn)那些很慢的查詢語句后,你需要用MySQL自帶的工具,如EXPLAIN,SHOW STATUS,SHOW PROCESSLIST。它們會(huì)告訴你資源都消耗在哪了,查詢語句的缺陷在哪,譬如一個(gè)有三次join子查詢的查詢語句是否在內(nèi)存中進(jìn)行排序,還是在硬盤 上進(jìn)行。當(dāng)然你也應(yīng)該使用測評(píng)工具如top,procinfo,vmstat等等獲取更多系統(tǒng)性能信息。

           3、減小你的schema

            在你開始寫查詢語句之前,你需要設(shè)計(jì)schema。記住將一個(gè)表裝入內(nèi)存所需要的空間大概是行數(shù)*一行的大小。除非你覺得世界上的每個(gè)人都會(huì)在 你的網(wǎng)站注冊2兆8000億次的話,否則你不需要采用BITINT作為你的user_id。同樣的,如果一個(gè)文本列是固定大小的話(譬如US郵編,通常 是”XXXXX-XXXX”的形式),采用VARCHAR的話會(huì)給每行增加多余的字節(jié)。

            有些人對數(shù)據(jù)庫規(guī)范化不以為意,他們說這樣會(huì)形成相當(dāng)復(fù)雜的schema。然而適當(dāng)?shù)囊?guī)范化會(huì)減少化冗余數(shù)據(jù)。(適當(dāng)?shù)囊?guī)范化)就意味著犧牲少 許性能,換取整體上更少的footprint,這種性能換取內(nèi)存在計(jì)算機(jī)科學(xué)中是很常見的。最好的方法是IMO,就是開始先規(guī)范化,之后如果性能需要的 話,再反規(guī)范化。你的數(shù)據(jù)庫將會(huì)更邏輯化,你也不用過早的進(jìn)行優(yōu)化。(譯者注,這一段我不是很理解,可能翻譯錯(cuò)了,歡迎糾正。)

            4、拆分你的表

            通常有些表只有一些列你是經(jīng)常需要更新的。例如對于一個(gè)博客,你需要在許多不同地方顯示標(biāo)題(如最近的文章列表),只在某個(gè)特定頁顯示概要或者全文。水平垂直拆分是很有幫助的:

          1. CREATE TABLE posts_tags (  
          2.     relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,  
          3.     post_id int UNSIGNED NOT NULL,  
          4.     tag_id int UNSIGNED NOT NULL,  
          5.     PRIMARY KEY(relation_id),  
          6.     UNIQUE INDEX(post_id, tag_id)  
          7. );

            artificial key完全是多余的,而且post-tag關(guān)系的數(shù)量將會(huì)受到整形數(shù)據(jù)的系統(tǒng)最大值的限制。

          1. CREATE TABLE posts_tags (  
          2.     post_id int UNSIGNED NOT NULL,  
          3.     tag_id int UNSIGNED NOT NULL,  
          4.     PRIMARY KEY(post_id, tag_id)  
          5. );

            6、學(xué)習(xí)索引

            你選擇的索引的好壞很重要,不好的話可能破壞數(shù)據(jù)庫。對那些還沒有在數(shù)據(jù)庫學(xué)習(xí)很深入的人來說,索引可以看作是就是hash排序。例如如果我們 用查詢語句SELECT * FROM users WHERE last_name = ‘Goldstein’,而last_name沒有索引的話,那么DBMS將會(huì)查詢每一行,看看是否等于“Goldstein”。索引通常是B- tree(還有其他的類型),可以加快比較的速度。

            你需要給你要select,group,order,join的列加上索引。顯然每個(gè)索引所需的空間正比于表的行數(shù),所以越多的索引將會(huì)占用更 多的內(nèi)存。而且寫數(shù)據(jù)時(shí),索引也會(huì)有影響,因?yàn)槊看螌憯?shù)據(jù)時(shí)都會(huì)更新對應(yīng)的索引。你需要取一個(gè)平衡點(diǎn),取決每個(gè)系統(tǒng)和實(shí)施代碼的需要。

            7、SQL不是C

            C是經(jīng)典的過程語言,對于一個(gè)程序員來說,C語言也是個(gè)陷阱,使你錯(cuò)誤的以為SQL也是一種過程語言(當(dāng)然SQL也不是功能語言也不是面向?qū)ο蟮模D悴灰胂髮?shù)據(jù)進(jìn)行操作,而是要想象有一組數(shù)據(jù),以及它們之間的關(guān)系。經(jīng)常使用子查詢時(shí)會(huì)出現(xiàn)錯(cuò)誤的用法。

          1. SELECT a.id,  
          2.     (SELECT MAX(created)  
          3.     FROM posts  
          4.     WHERE author_id = a.id)  
          5. AS latest_post  
          6. FROM authors a
           因?yàn)檫@個(gè)子查詢是耦合的,子查詢要使用外部查詢的信息,我們應(yīng)該使用join來代替。

          1. SELECT a.id, MAX(p.created) AS latest_post  
          2. FROM authors a  
          3. INNER JOIN posts p  
          4.     ON (a.id = p.author_id)  
          5. GROUP BY a.id

            8、理解你的引擎

            MySQL有兩種存儲(chǔ)引擎:MyISAM和InnoDB。它們分別有自己的性能特點(diǎn)和考慮因素??傮w來講,MyISAM適合讀數(shù)據(jù)很多的情況,InnoDB適合寫數(shù)據(jù)很多的情況,但也有很多情況下正好相反。最大的區(qū)別是它們?nèi)绾翁幚鞢OUNT函數(shù)。

            MyISAM緩存有表meta-data,如行數(shù)。這就意味著,COUNT(*)對于一個(gè)結(jié)構(gòu)很好的查詢是不需要消耗多少資源的。然后對于 InnoDB來說,就沒有這種緩存。舉個(gè)例子,我們要對一個(gè)查詢來分頁,假設(shè)你有這樣一個(gè)語句SELECT * FROM users LIMIT 5,10,而運(yùn)行SELECT COUNT(*) FROM users LIMIT 5,10 時(shí),對于MyISAM很快完成,而對InnoDB就需要和第一個(gè)語句相同的時(shí)間。MySQL有個(gè)SQL_CALC_FOUND_ROWS選項(xiàng),可以告訴 InnoDB運(yùn)行查詢語句時(shí)就計(jì)算行數(shù),之后再從SELECT FOUND_ROWS()來獲取。這是MySQL特有的。但使用InnoDB有時(shí)候是非常必要的,你可以獲得一些功能(如行鎖定,stord procedure等)。

            9、MySQL特定的快捷鍵

            MySQL提供了許多擴(kuò)展,方便使用。譬如INSERT … SELECT, INSERT … ON DUPLICATE KEY UPDATE, 以及REPLACE。

            我能用到它們時(shí)是毫不猶豫的,因?yàn)樗鼈兒芊奖?,能在許多情況下發(fā)揮不錯(cuò)的效果。但是MySQL也有一些危險(xiǎn)的關(guān)鍵字,應(yīng)該少用。例如 INSERT DELAYED,它告訴MySQL不需要立即插入數(shù)據(jù)(例如在寫日志的時(shí)候)。但問題是如果在很高數(shù)據(jù)量的情況下,插入可能會(huì)被無限期延遲,導(dǎo)致插入隊(duì)列 爆滿。你也可以使用MySQL的索引提示來指出哪些索引是需要使用的。MySQL大部分時(shí)間運(yùn)行是不錯(cuò)的,但如果schema設(shè)計(jì)不好的話或語句寫得不好 的話,MySQL的表現(xiàn)可能很糟糕。

            10、到這里為止吧

            最后,如果你關(guān)心MySQL性能優(yōu)化的話,請閱讀Peter Zaitsev的關(guān)于MySQL性能的博客,他寫了許多關(guān)于數(shù)據(jù)庫管理和優(yōu)化的博客。

          posted on 2012-05-15 09:57 順其自然EVO 閱讀(195) 評(píng)論(0)  編輯  收藏 所屬分類: 測試學(xué)習(xí)專欄數(shù)據(jù)庫

          <2012年5月>
          293012345
          6789101112
          13141516171819
          20212223242526
          272829303112
          3456789

          導(dǎo)航

          統(tǒng)計(jì)

          常用鏈接

          留言簿(55)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          搜索

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 哈密市| 潜江市| 定日县| 宜良县| 中江县| 洪洞县| 积石山| 仙桃市| 乌苏市| 确山县| 石景山区| 化隆| 海伦市| 金堂县| 阿尔山市| 麻阳| 福海县| 汉阴县| 桦南县| 丹江口市| 永川市| 定结县| 民勤县| 扎囊县| 灵山县| 金乡县| 常德市| 本溪市| 苗栗市| 图木舒克市| 克拉玛依市| 英德市| 蚌埠市| 宁武县| 嵩明县| 乌兰县| 屯门区| 出国| 绥宁县| 景洪市| 临西县|