MySQL 資料庫最佳化,mysql資料庫最佳化

來源:互聯網
上載者:User

MySQL 資料庫最佳化,mysql資料庫最佳化

       對於一個以資料為中心的應用,資料庫的好壞直接影響到程式的效能,因此資料庫效能至關重要。一般來說,要保證資料庫的效率,要做好以下四個方面的工作:sql語句最佳化及索引、資料庫(表)設計、資料庫參數配置、恰當的硬體資源和作業系統,這個順序也表現了這四個工作對效能影響的大小。


下面我們逐個闡明:


    一、SQL語句最佳化以及索引

    1. Sql語句最佳化工具:慢日誌: 

        使用MySQL慢查日誌對有效率問題的SQL進行監控。

        慢日誌分析工具:mysqldumpslow輸出

  如果發現系統慢了,又說不清楚是哪裡慢,那麼就該用這個工具了。只需要為mysql配置參數,mysql會自己記錄下來慢的sql語句。配置很簡單,參數檔案裡配置:

  slow_query_log=d:/slow.txt

  long_query_time = 2

  就可以在d:/slow.txt裡找到執行時間超過2秒的語句了,根據這個檔案定位問題吧。

  ·mysqldumpslow.pl

慢記錄檔可能會很大,讓人去看是很難受的事。這時候我們可以通過mysql內建的工具來分析。這個工具可以格式化慢記錄檔,對於只是參數不同的語句 會歸類類並,比如有兩個語句select * from a where id=1 和select * from a where id=2,經過這個工具整理後就只剩下select * from a where id=N,這樣讀起來就舒服多了。而且這個工具可以實現簡單的排序,讓我們有的放矢。

       2.Explain

  現在我們已經知道是哪個語句慢了,那麼它為什麼慢呢?看看mysql是怎麼執行的吧,用explain可以看到mysql執行計畫,下面的用法來源於手冊

  EXPLAIN文法(擷取SELECT相關資訊)

  EXPLAIN [EXTENDED] SELECT select_options

  EXPLAIN語句可以用作DESCRIBE的一個同義字,或獲得關於MySQL如何執行SELECT語句的資訊:

  · EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義字。

  · 如果在SELECT語句前放上關鍵詞EXPLAIN,MySQL將解釋它如何處理SELECT,提供有關表如何聯結和聯結的次序。

  該節解釋EXPLAIN的第2個用法。

  藉助於EXPLAIN,可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。

  如果由於使用不正確的索引出現了問題,應運行ANALYZE TABLE更新表的統計(例如關鍵字集的勢),這樣會影響最佳化器進行的選擇。

  還可以知道最佳化器是否以一個最佳次序聯結表。為了強制最佳化器讓一個SELECT語句按照表命名順序的聯結次序,語句應以STRAIGHT_JOIN而不只是SELECT開頭。

   EXPLAIN為用於SELECT語句中的每個表返回一行資訊。表以它們在處理查詢過程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯 接(single-sweep multi-join)的方式解決所有聯結。這意味著MySQL從第一個表中讀一行,然後找到在第二個表中的一個匹配行,然後在第3個表中等等。當所有的 表處理完後,它輸出選中的列並且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行並繼續處理下一個表。

  當使用EXTENDED關鍵字時,EXPLAIN產生附加資訊,可以用SHOW WARNINGS瀏覽。該資訊顯示最佳化器限定SELECT語句中的表和列名,重寫並且執行最佳化規則後SELECT語句是什麼樣子,並且還可能包括最佳化過程的其它註解。

   

       3.如果什麼都做不了,試試全索引掃描

  如果一個語句實在不能最佳化了,那麼還有一個方法可以試試:索引覆蓋。

  如果一個語句可以從索引上擷取全部資料,就不需要通過索引再去讀表,省了很多I/O。比如這樣一個表        

  如果我要統計每個學生每道題的得分情況,我們除了要給每個表的主鍵外鍵建立索引,還要對【得分情況】的實際得分欄位索引,這樣,整個查詢就可以從索引得到資料了。

      
       二、資料庫(表)設計

  適度的反範式,注意是適度的

  我們都知道三範式,基於三範式建立的模型是最有效儲存數 據的方式,也是最容易擴充的模式。我們在開發應用程式時,設計的資料庫要最大程度的遵守三範式,特別是對於OLTP型的系統,三範式是必須遵守的規則。當 然,三範式最大的問題在於查詢時通常需要join很多表,導致查詢效率很低。所以有時候基於效能考慮,我們需要有意的違反三範式,適度的做冗餘,以達到提 高查詢效率的目的。注意這裡的反範式是適度的,必須為這種做法提供充分的理由。下面就是一個糟糕的執行個體:  

   在這裡,為了提高學生活動記錄的檢索效率,把單位名稱冗餘到學生活動記錄表裡。單位資訊有500條記錄,而學生活動記錄在一年內大概有200萬資料量。 如果學生活動記錄表不冗餘這個單位名稱欄位,只包含三個int欄位和一個timestamp欄位,只佔用了16位元組,是一個很小的表。而冗餘了一個 varchar(32)的欄位後則是原來的3倍,檢索起來相應也多了這麼多的I/O。而且記錄數相差懸殊,500 VS 2000000 ,導致更新一個單位名稱還要更新4000條冗餘記錄。由此可見,這個冗餘根本就是適得其反。

  下面這個冗餘就很好  

   可以看到,[學生考試總分]是冗餘的,這個分數完全可以通過[得分情況]匯總得到。在【學生考試總分】裡,一次考試一個學生只有一條記錄,而在【得分情 況】裡,一個學生針對試卷裡一個小題的一個小問一條記錄,粗略的算一下比例大概是1:100。而且判卷子得分是不會輕易變的,更新的頻率不高,所以說這個 冗餘是比較好的。

       1.適當建立索引

  說起提高資料庫效能,索引是最物美價廉的東西了。不用加記憶體,不用改程式,不用調sql,只要執行個正確的’create index’,查詢速度就可能提高百倍千倍,這可真有誘惑力。可是天下沒有免費的午餐,查詢速度的提高是以插入、更新、刪除的速度為代價的,這些寫操作,增加了大量的I/O。由於索引的儲存結構不同於表的儲存,一個表的索引所佔空間比資料所佔空間還大的情況經常發生。這意味著我們在寫資料庫的時候做了很多額外的工作,而這個工作只是為了提高讀的效率。因此,我們建立一個索引,必須保證這個索引不會“虧本”。一般需要遵守這樣的規則:

  索引的欄位必須是經常作為查詢條件的欄位;

  如果索引多個欄位,第一個欄位要是經常作為查詢條件的。如果只有第二個欄位作為查詢條件,這個索引不會起到作用;

  索引的欄位必須有足夠的區分度;

  Mysql 對於長欄位支援首碼索引;


  2.對錶進行水平劃分

   表的水平劃分是為瞭解決單表的資料量過大的問題,水平劃分的表每一個表的結構都是完成一致的。

       如果一個表的記錄數太多了,比如上千萬條,而且需要經常檢索,那麼我們就有必要化整為零了。如果我拆成100個表,那麼每個表只有10萬條記錄。當然這 需要資料在邏輯上可以劃分。一個好的劃分依據,有利於程式的簡單實現,也可以充分利用水平分表的優勢。比如系統介面上只提供按月查詢的功能,那麼把表按月 拆分成12個,每個查詢只查詢一個表就夠了。如果非要按照地區來分,即使把表拆的再小,查詢還是要聯合所有表來查,還不如不拆了。所以一個好的拆分依據是 最重要的。

  這裡有個比較好的執行個體        

   每個學生做過的題都記錄在這個表裡,包括對題和錯題。每個題會對應一個或多個知識點,我們需要根據錯題來分析學生在哪個知識點上掌握的不足。這個表很容 易達到千萬級,迫切需要拆分,那麼根據什麼來拆呢?從需求上看,無論是老師還是學生,最終會把焦點落在一個學生的身上。學生會關心自己,老師會關心自己班 的學生。而且每個學科的知識點是不同的。所以我們很容易想到,聯合學科和知識點兩個欄位來拆分這個表。這樣拆下來,每個表大概2萬條資料,檢索效率非常 高。


     3.對錶進行垂直劃分

       所謂的垂直分割,就是把原來一個有很多列的表拆分成多個表,這解決了表的寬度問題,通常垂直分割可以按一下原則進行:

        (1)把不常用的欄位單獨存放到一個表中;

        (2)把大欄位獨立存放到一個表中;

        (3)把經常一起使用的欄位放到一起。    


        有些表記錄數並不多,可能也就2、3萬條,但是欄位卻很長,表佔用空間很大,檢索表時需要執行大量I/O,嚴重降低了效能。這個時候需要把大的欄位拆分到另一個表,並且該表與原表是一對一的關係。        

   【試題內容】、【答案資訊】兩個表,最初是作為幾個欄位添加到【試題資訊】裡的,可以看到試題內容和答案這兩個欄位很長,在表裡有3萬記錄時,表已經占 了1G的空間,在列試題列表時非常慢。經過分析,發現系統很多時候是根據【冊】、【單元】、類型、類別、難易程度等查詢條件,分頁顯示試題詳細內容。而每 次檢索都是這幾個表做join,每次要掃描一遍1G的表,很鬱悶啊。我們完全可以把內容和答案拆分成另一個表,只有顯示詳細內容的時候才讀這個大表,由此 就產生了【試題內容】、【答案資訊】兩個表。


      4. 選擇適當的欄位類型,特別是主鍵

  選擇欄位的一般原則是保小不保大,能用佔用位元組小的欄位就不用大欄位。比如主鍵, 我們強烈建議用自增類型,不用guid,為什麼?省空間啊?空間是什麼?空間就是效率!按4個位元組和按32個位元組定位一條記錄,誰快誰慢太明顯了。涉及到 幾個表做join時,效果就更明顯了。值得一提的是,datetime和timestamp,datetime佔用8個位元組,而timestamp佔用4 個位元組,只用了一半,而timestamp表示的範圍是1970—2037,對於大多數應用,尤其是記錄什麼考試時間,登入時間這類資訊,綽綽有餘啊。


  5.檔案、圖片等大檔案用檔案系統儲存,不用資料庫

  不用多說,鐵律!!!資料庫只儲存路徑。


  6.外鍵表示清楚,方便建立索引

  我們都知道,在powerdesigner裡為兩個實體建立關係,產生物理模型時會自動給外鍵建立索引。所以我們不要怕建立關係把線拉亂,建立個ShortCut就好了。


  7.掌握表的寫入時機

  在庫模式相同的情況下,如何使用資料庫也對效能有著重要作用。同樣是寫入一個表,先寫和後寫對後續的操作會產生很大影響。例如在上面提到的適度冗餘裡的例子,        

   我們最初的目的是記錄考生的總分,以達到提高檢索效率的目的,也就是在錄入成績時寫入這個表。在需求裡有這樣的要求:列出本次考試的所有學產生績,沒有 錄入成績的也顯示該學生名稱,只是總分顯示為空白。這個查詢就需要用【學生資訊】left outer join 【學生考試總分資訊】,大家都知道outer join 的效率比join是要低的,為了避免這個問題,我們就在布置考試的時候寫入這個表,把所有學生都插入進去,分數都是null,這樣一來我們就可以用 join達到這個效果了。而且還有這樣的好處:在某次考試中,安排了一個班所有學生考試,所有學生都錄入了成績。現在班裡轉來一個新生,那麼在此時如果查 詢學產生績,就會列出這個新生,結果是未錄入成績,這顯然是不對的。如果在安排的時候就寫入,就可以記錄下該次考試中實際的考生了,這個表的作用,也就不 知是冗餘了。


    8.寧可集中大量操作,避免頻繁讀寫

  系統裡包含了積分部分,學生和老師通過系統做了操作都可以獲得積分,而且積分規 則很複雜,限制每類操作獲得積分不同,每人每天每類積分都有上限。比如登入,一次登入就可以獲得1分,但是不管你登入多少次,一天只能累積一個登入積分。 這個還是簡單的,有的積分很變態,比如老師積分中有一類是看老師判作業的情況,規則是:老師判了作業,發現學生有錯的,學生改過了,老師再判,如果這時候 學生都對了,就給老師加分,如果學生還是錯的,那就接著改,知道學生都改對了,老師都判完了,才能給老師加分。如果用程式來處理,很可能每個功能都會額外 的寫一堆代碼來處理這個雞肋似的積分。不僅編程的同事幹活找不到重點,還平白給資料庫帶來了很大的壓力。經過和需求人員的討論,確定積分沒有必要即時累 積,於是我們採取後台指令碼批量處理的方式。夜深人靜的時候,讓機器自己玩去吧。

  這個變態的積分規則用批處理讀出來是這樣的:  

      
1select person_id, @semester_id, 301003, 0, @one_marks, assign_date, @one_marks2         from hom_assignmentinfo   ha, hom_assign_class hac3         where ha.assignment_id = hac.assignment_id4              and ha.assign_datebetween @time_beginand @time_end5              and ha.assignment_idnot in6                    (7                        select haa.assignment_idfrom hom_assignment_appraise haa, hom_check_assignment hca8                         where haa.appraise_id = hca.appraise_idand haa.if_submit=19                              and (10                                      (hca.recheck_state = 3004001and hca.check_resultin (3003002, 3003003) )11                                      or12                                      (hca.recheck_state = 3004002and hca.recheck_resultin (3003002, 3003003))13                                    )14                    )15              and ha.assignment_idnot in16                    (17                        select assignment_idfrom hom_assignment_appraisewhere if_submit=0and result_type = 018                    )19              and ha.assignment_idin     20                    (21                        select haa.assignment_idfrom hom_assignment_appraise haa, hom_check_assignment hca22                         where haa.appraise_id = hca.appraise_idand haa.if_submit=123                              and hca.check_resultin (3003002, 3003003)24                    );

 

  這還只是個中間過程,這要是用程式即時處理,即使編程人員不罷工,資料庫也會歇了。


  9.選擇合適的引擎

   Mysql提供了很多種引擎,我們用的最多的是myisam,innodb,memory這三類。官方手冊上說道myisqm比innodb的讀速度要 快,大概是3倍。不過書不能盡信啊,《OreIlly.High.Performance.Mysql》這本書裡提到了myisam和innodb的比 較,在測試中myisam的表現還不及innodb。至於memory,哈哈,還是比較好用的。在批處理種作暫存資料表是個不錯的選擇(如果記憶體夠大)。在我的一個批處理中,速度比近乎1:10。


 

 三、資料庫參數配置

      資料庫是基於OS的,目前大多數MySQL都是安裝在Linux系統之上的,所以對於OS的一些參數配置也會影響MySQL的效能。

     

      

       最重要的參數就是記憶體,我們主要用的innodb引擎,所以下面兩個參數調的很大

  # Additional memory pool that is used by InnoDB to store metadata

  # information. If InnoDB requires more memory for this purpose it will

  # start to allocate it from the OS. As this is fast enough on most

  # recent operating systems, you normally do not need to change this

  # value. SHOW INNODB STATUS will display the current amount used.

  innodb_additional_mem_pool_size = 64M

  # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

  # row data. The bigger you set this the less disk I/O is needed to

  # access data in tables. On a dedicated database server you may set this

  # parameter up to 80% of the machine physical memory size. Do not set it

  # too large, though, because competition of the physical memory may

  # cause paging in the operating system. Note that on 32bit systems you

  # might be limited to 2-3.5G of user level memory per process, so do not

  # set it too high.

  innodb_buffer_pool_size = 5G

  對於myisam,需要調整key_buffer_size

  當然調整參數還是要看狀態,用show status語句可以看到目前狀態,以決定改調整哪些參數

  Cretated_tmp_disk_tables 增加tmp_table_size

  Handler_read_key 高表示索引正確 Handler_read_rnd高表示索引不正確

  Key_reads/Key_read_requests 應小於0.01 計算緩衝損失率,增加Key_buffer_size

  Opentables/Open_tables 增加table_cache

  select_full_join 沒有實用索引的連結的數量。如果不為0,應該檢查索引。

  select_range_check 如果不為0,該檢查表索引。

  sort_merge_passes 排序演算法已經執行的合并的數量。如果該值較大,應增加sort_buffer_size

  table_locks_waited 不能立即獲得的表的鎖的次數,如果該值較高,應最佳化查詢

  Threads_created 建立用來處理串連的線程數。如果Threads_created較大,要增加 thread_cache_size值。

  緩衝訪問率的計算方法Threads_created/Connections。

 

      四、合理的硬體資源和作業系統

  如果你的機器記憶體超過4G,那麼毋庸置疑應當採用64位作業系統和64位mysql,成本高效果不明顯。

  讀寫分離

  如果資料庫壓力很大,一台機器支撐不了,那麼可以用mysql複製實現多台機器同步,將資料庫的壓力分散。  

  Master

  Slave1

  Slave2

  Slave3

   主庫master用來寫入,slave1—slave3都用來做select,每個資料庫分擔的壓力小了很多。

   要實現這種方式,需要程式特別設計,寫都操作master,讀都操作slave,給程式開發帶來了額外負擔。當然目前已經有中介軟體來實現這個代理,對程 序來讀寫哪些資料庫是透明的。官方有個mysql-proxy,但是還是alpha版本的。新浪有個amobe for mysql,也可達到這個目的,結構如下  

  使用方法可以看amobe的手冊。


相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.