數據庫優化是一項很復雜的工作因為這最終需要對系統優化的很好理解才行盡管對系統或應用系統的了解不多的情況下優化效果還不錯但是如果想優化的效果更好那麼就需要對它了解更多才行
本章主要講解了幾種優化MySQL的方法並且給出了例子記著總有各種辦法能讓系統運行的更快當然了這需要更多的努力
優化概述
讓系統運行得快得最重要因素是數據庫基本的設計並且還必須清楚您的系統要用來做什麼以及存在的瓶頸
最常見的系統瓶頸有以下幾種
磁盤搜索它慢慢地在磁盤中搜索數據塊對現代磁盤來說平時的搜索時間基本上小於毫秒因此理論上每秒鐘可以做次磁盤搜索這個時間對於全新的新磁盤來說提高的不多並且對於只有一個表的情況也是如此加快搜索時間的方法是將數據分開存放到多個磁盤中
磁盤讀/寫當磁盤在正確的位置上時就需要讀取數據對現代磁盤來說磁盤吞吐量至少是MB/秒這比磁盤搜索的優化更容易因為可以從多個媒介中並行地讀取數據
CPU周期數據存儲在主內存中(或者它已經在主內存中了)這就需要處理這些數據以得到想要的結果存在多個?硐啾饒诖嫒萘坷此蹈竅拗頻囊蛩亍還孕砝此擔俣韌ǔ皇俏侍狻?
內存帶寬當CPU要將更多的數據存放在CPU緩存中時主內存的帶寬就是瓶頸了在大多數系統中這不是常見的瓶頸不過也是要注意的一個因素
MySQL 設計的局限性
當使用MyISAM存儲引擎時MySQL會使用一個快速數據表鎖以允許同時多個讀取和一個寫入這種存儲引擎的最大問題是發生在一個單一的表上同時做穩定的更新操作及慢速查詢如果這種情況在某個表中存在可以使用另一種表類型詳情請看 MySQL Storage Engines and Table Types
MySQL可以同時在事務及非事務表下工作為了能夠平滑的使用非事務表(發生錯誤時不能回滾)有以下幾條規則
所有的字段都有默認值
如果字段中插入了一個
錯誤
的值
比如在數字類型字段中插入過大數值
那麼MySQL會將該字段值置為
最可能的值
而不是給出一個錯誤
數字類型的值是
最小或者最大的可能值
字符串類型
不是空字符串就是字段所能存儲的最大長度
所有的計算表達式都會返回一個值而報告條件錯誤
例如
/
返回 NULL
這些規則隱含的意思是不能使用MySQL來檢查字段內容相反地必須在存儲到數據庫前在應用程序中來檢查詳情請看 How MySQL Deals with Constraints 和 INSERT Syntax
應用設計的可移植性
由於各種不同的數據庫實現了各自的SQL標准這就需要我們盡量使用可移植的SQL應用查詢和插入操作很容易就能做到可移植不過由於更多的約束條件的要求就越發困難想要讓一個應用在各種數據庫系統上快速運行就變得更困難了
為了能讓一個復雜的應用做到可移植就要先看這個應用運行於哪種數據庫系統之上然後看這些數據庫系統都支持哪些特性
每個數據庫系統都有某些不足也就是說由於設計上的一些妥協導致了性能上的差異
可以用MySQL的 crashme 程序來看選定的數據庫服務器上可以使用的函數類型限制等crashme 不會檢查各種可能存在的特性不過這仍然是合乎情理的理解大約做了次測試
一個 crashme 的信息類型的例子就是它會告訴您如果想使用Informix 或 DB的話就不能使字段名長度超過個字符
crashme 程序和MySQL基准使每個准數據庫都實現了的可以通過閱讀這些基准程序是怎麼寫的自己就大概有怎樣做才能讓程序獨立於各種數據庫這方面的想法了這些程序可以在MySQL源代碼的 `sqlbench 目錄下找到他們大部分都是用Perl寫的並且使用DBI接口由於它提供了獨立於數據庫的各種訪問方式因此用DBI來解決各種移植性的問題
想要看到 crashme 的結果可以訪問訪問 可以看到基准的結果
如果您想努力做到獨立於數據庫這就需要對各種SQL服務器的瓶頸都有一些很好的想法例如MySQL對於 MyISAM 類型的表在檢索以及更新記錄時非常快但是在有並發的慢速讀取及寫入記錄時卻有一定的問題作為Oracle來說它在訪問剛剛被更新的記錄時有很大的問題(直到結果被刷新到磁盤中)事務數據庫一般地在從日志表中生成摘要表這方面的表現不怎麼好因為在這種情況下行記錄鎖幾乎沒用
為了能讓應用程序真正的做到獨立於數據庫就必須把操作數據的接口定義的簡單且可擴展由於C++在很多系統上都可以使用因此使用C++作為數據庫的基類結果很合適
如果使用了某些數據庫獨有的特定功能(比如 REPLACE 語句就只在MySQL中獨有)這就需要通過編寫替代方法來在其他數據庫中實現這個功能盡管這些替代方法可能會比較慢但是它能讓其他數據庫實現同樣的功能
在MySQL中可以在查詢語句中使用 /*! */ 語法來增加MySQL特有的關鍵字然而在很多其他數據庫中/**/ 卻被當成了注釋(並且被忽略)
如果有時候更高的性能比數據結果的精確更重要就像在一些Web應用中那樣這可以使用一個應用層來緩存結果這可能會有更高的性能通過讓舊數據在一定時間後過期來合理的更新緩存這是處理負載高峰期時的一種方法這種情況下可以通過加大緩存容量和過期時間直到負載趨於正常
這種情況下建表信息中就要包含了初始化緩存的容量以及正常刷新數據表的頻率
一個實現應用層緩存的可選方案是使用MySQL的查詢緩存(query cache)啟用查詢緩存後數據庫就會根據一些詳情來決定哪些結果可以被重用它大大簡化了應用程序詳情請看 The MySQL Query Cache
我們都用MySQL來做什麼
本章描述了一個MySQL的早期應用
在MySQL最開始的開發過程中MySQL本來是要准備給大客戶用的他們是瑞典的個最大的零售商他們用於貨物存儲數據管理
我們每周從所有的商店中得到交易利潤累計結果以此給商店的老板提供有用的信息幫助他們分析如果更好的打廣告以影響他們的客戶
數據量相當的大(每個月的交易累計結果大概有百萬)而且還需要顯示年間的數據我們每周都得到客戶的需求他們要求能瞬間地得到數據的最新報表
我們把每個月的全部信息存儲在一個壓縮的交易表中以解決這個問題我們有一些簡單的宏指令集它們能根據不同的標准從存儲的交易表中根據字段分組(產品組客戶id商店等等)取得結果我們用一個小Perl腳本動態的生成Web頁面形式的報表這個腳本解析Web頁面執行SQL語句並且插入結果我們還可以用PHP或者mod_perl來做這個工作不過當時還沒有這個工具
為了得到圖形數據我們還寫了一個簡單的C語言工具用於執行SQL查詢並且將結果做成GIF圖片這個工具同樣是Perl腳本解析Web頁面後動態執行的
很多情況下只要拷貝現有的腳本簡單的修改裡面的SQL查詢語句就能產生新的報表了有時候就需要在現存的累計表中增加更多的字段或者新建一個這個操作十分簡單因為我們在磁盤上存儲有所有的交易表(總共大概有G的交易表以及G的其他客戶資料)
我們還允許客戶通過ODBC直接訪問累計表這樣的話那些高級用戶就可以自己利用這些數據做試驗了
這個系統工作的很好並且在適度的Sun Ultra SPARC工作站(xMHz)上處理數據沒有任何問題最終這個系統移植到了Linux上
MySQL 基准套件
本章本來要包括MySQL基准套件(以及 crashme)的技術描述的但是至今還未寫現在您可以通過查看MySQL發布源代碼 `sqlbench 目錄下的代碼以及結果有一個更好的想法
基准套件就是想告訴用戶執行什麼樣的SQL查詢表現的更好或者更差
請注意這個基准是單線程的因此它度量了操作執行的最少時間我們未來打算增加多線程測試的基准套件
想要使用基准套件必備以下幾個條件
基准套件在MySQL的發布源代碼中就有可以去 下載發布版或者使用現有開發代碼樹(詳情請看 Installing from the Development Source Tree)
基准腳本是用Perl寫的它用Perl的DBI模塊來連接數據庫因此必須安裝DBI模塊並且還需要每個要做測試的服務器上都有特定的BDB驅動程序例如為了測試MySQLPostgreSQL和DB就必須安裝 DBD::mysql DBD::Pg 及 DBD::DB 模塊詳情請看 Perl Installation Note
取得MySQL的分發源代碼後就能在 `sqlbench 目錄下看到基准套件想要運行這些基准測試請先搭建好服務然後進入 `sqlbench 目錄執行 runalltests 腳本
shell> cd sqlbench
shell> perl runalltests server=server_name
server_name 可以是任何一個可用的服務想要列出所有的可用選項和支持的服務只要調用以下命令
shell> perl runalltests help
crashme 腳本也是放在 `sqlbench 目錄下crashme 通過執行真正的查詢以試圖判斷數據庫都支持什麼特性性能表現以及限制例如它可以判斷
都支持什麼字段類型
支持多少索引
支持什麼樣的函數
能支持多大的查詢
VARCHAR 字段類型能支持多大
可以從 上找到各種不同數據庫 crashme 的結果更多的信息請訪問
使用您自己的基准
請確定對您的數據庫或者應用程序做基准測試以發現它們的瓶頸所在解決這個瓶頸(或者使用一個假的模塊來代替)之後就能很容易地找到下一個瓶頸了即使應用程序當前總體的表現可以接受不過還是至少要做好找到每個瓶頸的計劃說不定某天您就希望應用程序能有更好的性能
從MySQL的基准套件中就能找到一個便攜可移植的基准測試程序了詳情請看 The MySQL Benchmark Suite您可以從基准套件中的任何一個程序做適當的修改以適合您的需要通過整個方式您就可以有各種不同的辦法來解決問題知道哪個程序才是最快的
另一個基准套件是開放源碼的數據庫基准可以在 上找到
當系統負載十分繁重的時候通常就會發生問題我們就有很多客戶聯系我們說他們有一個(測試過的)生產系統也遭遇了負載問題在很多情況下性能問題歸結於數據庫的基本設計(例如在高負載下掃描數據表的表現不好)操作系統或者程序庫等因素很多時候這些問題在還沒有正式用於生產前相對更容易解決
為了避免發生這樣的問題最好讓您的應用程序在可能的最差的負載下做基准測試!可以使用Super Smack在 可以找到從它名字的意思就能想到只要您願意它就能讓您的系統死掉因此確認只在開發系統上做測試
優化 SELECT 語句及其他查詢
首先影響所有語句的一個因素是您的權限設置越復雜那麼開銷就越大
使用比較簡單的 GRANT 語句能讓MySQL減少在客戶端執行語句時權限檢查的開銷例如如果沒有設定任何表級或者字段級的權限那麼服務器就無需檢查 tables_priv 和 columns_priv 表的記錄了同樣地如果沒有對帳戶設定任何資源限制的話那麼服務器也就無需做資源使用統計了如果有大量查詢的話花點時間來規劃簡單的授權機制以減少服務器權限檢查的開銷是值得的
如果問題處在一些MySQL特定的表達式或者函數上則可以通過 mysql 客戶端程序使用 BENCHMARK() 函數做一個定時測試它的語法是BENCHMARK(loop_countexpression)例如
mysql> SELECT BENCHMARK(+);
++
| BENCHMARK(+) |
++
| |
++
row in set ( sec)
上述結果是在Pentium II MHz的系統上執行得到的它告訴我們MySQL在這個系統上可以在秒內執行 次簡單的加法運算
所有的MySQL函數都應該被最優化不過仍然有些函數例外BENCHMARK() 是一個用於檢查查詢語句中是否存在問題的非常好的工具
From:http://tw.wingwit.com/Article/program/Oracle/201311/16975.html
