MySQL數(shù)據(jù)庫優(yōu)化方法總結(jié)

時間：2023-01-25 06:15:01 | 來源：電子商務(wù)

時間：2023-01-25 06:15:01 來源：電子商務(wù)

數(shù)據(jù)庫優(yōu)化是一項很復(fù)雜的工作，因為這最終需要對系統(tǒng)優(yōu)化的很好理解才行。盡管對系統(tǒng)或應(yīng)用系統(tǒng)的了解不多的情況下優(yōu)化效果還不錯，但是如果想優(yōu)化的效果更好，那么就需要對它了解更多才行。

1、優(yōu)化概述

讓系統(tǒng)運(yùn)行得快得最重要因素是數(shù)據(jù)庫基本的設(shè)計。并且還必須清楚您的系統(tǒng)要用來做什么，以及存在的瓶頸。

最常見的系統(tǒng)瓶頸有以下幾種：

磁盤搜索。它慢慢地在磁盤中搜索數(shù)據(jù)塊。對現(xiàn)代磁盤來說，平時的搜索時間基本上小于10毫秒，因此理論上每秒鐘可以做100次磁盤搜索。這個時間對于全新的新磁盤來說提高的不多，并且對于只有一個表的情況也是如此。加快搜索時間的方法是將數(shù)據(jù)分開存放到多個磁盤中。

磁盤讀/寫。當(dāng)磁盤在正確的位置上時，就需要讀取數(shù)據(jù)。對現(xiàn)代磁盤來說，磁盤吞吐量至少是10-20MB/秒。這比磁盤搜索的優(yōu)化更容易，因為可以從多個媒介中并行地讀取數(shù)據(jù)。

CPU周期。數(shù)據(jù)存儲在主內(nèi)存中(或者它已經(jīng)在主內(nèi)存中了)，這就需要處理這些數(shù)據(jù)以得到想要的結(jié)果。

內(nèi)存帶寬。當(dāng)CPU要將更多的數(shù)據(jù)存放在CPU緩存中時，主內(nèi)存的帶寬就是瓶頸了。在大多數(shù)系統(tǒng)中，這不是常見的瓶頸，不過也是要注意的一個因素。

1.1 MySQL 設(shè)計的局限性

當(dāng)使用MyISAM存儲引擎時，MySQL會使用一個快速數(shù)據(jù)表鎖以允許同時多個讀取和一個寫入。這種存儲引擎的最大問題是發(fā)生在一個單一的表上同時做穩(wěn)定的更新操作及慢速查詢。如果這種情況在某個表中存在，可以使用另一種表類型。

MySQL可以同時在事務(wù)及非事務(wù)表下工作。為了能夠平滑的使用非事務(wù)表(發(fā)生錯誤時不能回滾)，有以下幾條規(guī)則：

所有的字段都有默認(rèn)值

如果字段中插入了一個"錯誤"的值，比如在數(shù)字類型字段中插入過大數(shù)值，那么MySQL會將該字段值置為"最可能的值"而不是給出一個錯誤。數(shù)字類型的值是0，最小或者最大的可能值。字符串類型，不是空字符串就是字段所能存儲的最大長度。

所有的計算表達(dá)式都會返回一個值而報告條件錯誤，例如 1/0 返回 NULL。

這些規(guī)則隱含的意思是，不能使用MySQL來檢查字段內(nèi)容。相反地，必須在存儲到數(shù)據(jù)庫前在應(yīng)用程序中來檢查。

1.2 應(yīng)用設(shè)計的可移植性

由于各種不同的數(shù)據(jù)庫實現(xiàn)了各自的SQL標(biāo)準(zhǔn)，這就需要我們盡量使用可移植的SQL應(yīng)用。查詢和插入操作很容易就能做到可移植，不過由于更多的約束條件的要求就越發(fā)困難。想要讓一個應(yīng)用在各種數(shù)據(jù)庫系統(tǒng)上快速運(yùn)行，就變得更困難了。

為了能讓一個復(fù)雜的應(yīng)用做到可移植，就要先看這個應(yīng)用運(yùn)行于哪種數(shù)據(jù)庫系統(tǒng)之上，然后看這些數(shù)據(jù)庫系統(tǒng)都支持哪些特性。每個數(shù)據(jù)庫系統(tǒng)都有某些不足。也就是說，由于設(shè)計上的一些妥協(xié)，導(dǎo)致了性能上的差異。

可以用MySQL的 crash-me 程序來看選定的數(shù)據(jù)庫服務(wù)器上可以使用的函數(shù)，類型，限制等。crash-me 不會檢查各種可能存在的特性，不過這仍然是合乎情理的理解，大約做了450次測試。一個crash-me 的信息類型的例子就是，它會告訴您如果想使用Informix 或 DB2的話，就不能使字段名長度超過18個字符。

crash-me 程序和MySQL基準(zhǔn)使每個準(zhǔn)數(shù)據(jù)庫都實現(xiàn)了的?？梢酝ㄟ^閱讀這些基準(zhǔn)程序是怎么寫的，自己就大概有怎樣做才能讓程序獨(dú)立于各種數(shù)據(jù)庫這方面的想法了。這些程序可以在MySQL源代碼的 `sql-bench' 目錄下找到。他們大部分都是用Perl寫的，并且使用DBI接口。由于它提供了獨(dú)立于數(shù)據(jù)庫的各種訪問方式，因此用DBI來解決各種移植性的問題。

如果您想努力做到獨(dú)立于數(shù)據(jù)庫，這就需要對各種SQL服務(wù)器的瓶頸都有一些很好的想法。例如，MySQL對于 MyISAM 類型的表在檢索以及更新記錄時非?？?，但是在有并發(fā)的慢速讀取及寫入記錄時卻有一定的問題。作為Oracle來說，它在訪問剛剛被更新的記錄時有很大的問題(直到結(jié)果被刷新到磁盤中)。事務(wù)數(shù)據(jù)庫一般地在從日志表中生成摘要表這方面的表現(xiàn)不怎么好，因為在這種情況下，行記錄鎖幾乎沒用。

為了能讓應(yīng)用程序真正的做到獨(dú)立于數(shù)據(jù)庫，就必須把操作數(shù)據(jù)的接口定義的簡單且可擴(kuò)展。由于C++在很多系統(tǒng)上都可以使用，因此使用C++作為數(shù)據(jù)庫的基類結(jié)果很合適。

如果使用了某些數(shù)據(jù)庫獨(dú)有的特定功能(比如 REPLACE 語句就只在MySQL中獨(dú)有)，這就需要通過編寫替代方法來在其他數(shù)據(jù)庫中實現(xiàn)這個功能。盡管這些替代方法可能會比較慢，但是它能讓其他數(shù)據(jù)庫實現(xiàn)同樣的功能。

在MySQL中，可以在查詢語句中使用 /*! */ 語法來增加MySQL特有的關(guān)鍵字。然而在很多其他數(shù)據(jù)庫中，/**/ 卻被當(dāng)成了注釋(并且被忽略)。

如果有時候更高的性能比數(shù)據(jù)結(jié)果的精確更重要，就像在一些Web應(yīng)用中那樣，這可以使用一個應(yīng)用層來緩存結(jié)果，這可能會有更高的性能。通過讓舊數(shù)據(jù)在一定時間后過期，來合理的更新緩存。這是處理負(fù)載高峰期時的一種方法，這種情況下，可以通過加大緩存容量和過期時間直到負(fù)載趨于正常。

這種情況下，建表信息中就要包含了初始化緩存的容量以及正常刷新數(shù)據(jù)表的頻率。一個實現(xiàn)應(yīng)用層緩存的可選方案是使用MySQL的查詢緩存(query cache)。啟用查詢緩存后，數(shù)據(jù)庫就會根據(jù)一些詳情來決定哪些結(jié)果可以被重用。它大大簡化了應(yīng)用程序。

1.3 我們都用MySQL來做什么

在MySQL最開始的開發(fā)過程中，MySQL本來是要準(zhǔn)備給大客戶用的，他們是瑞典的2個最大的零售商，他們用于貨物存儲數(shù)據(jù)管理。

我們每周從所有的商店中得到交易利潤累計結(jié)果，以此給商店的老板提供有用的信息，幫助他們分析如果更好的打廣告以影響他們的客戶。

數(shù)據(jù)量相當(dāng)?shù)拇?每個月的交易累計結(jié)果大概有7百萬)，而且還需要顯示4-10年間的數(shù)據(jù)。我們每周都得到客戶的需求，他們要求能‘瞬間’地得到數(shù)據(jù)的最新報表。

我們把每個月的全部信息存儲在一個壓縮的‘交易’表中以解決這個問題。我們有一些簡單的宏指令集，它們能根據(jù)不同的標(biāo)準(zhǔn)從存儲的‘交易’表中根據(jù)字段分組(產(chǎn)品組、客戶id、商店等等)取得結(jié)果。我們用一個小Perl腳本動態(tài)的生成Web頁面形式的報表。這個腳本解析Web頁面，執(zhí)行SQL語句，并且插入結(jié)果。我們還可以用PHP或者mod_perl來做這個工作，不過當(dāng)時還沒有這2個工具。

為了得到圖形數(shù)據(jù)，我們還寫了一個簡單的C語言工具，用于執(zhí)行SQL查詢并且將結(jié)果做成GIF圖片。這個工具同樣是Perl腳本解析Web頁面后動態(tài)執(zhí)行的。

很多情況下，只要拷貝現(xiàn)有的腳本簡單的修改里面的SQL查詢語句就能產(chǎn)生新的報表了。有時候，就需要在現(xiàn)存的累計表中增加更多的字段或者新建一個。這個操作十分簡單，因為我們在磁盤上存儲有所有的交易表(總共大概有50G的交易表以及20G的其他客戶資料)。

我們還允許客戶通過ODBC直接訪問累計表，這樣的話，那些高級用戶就可以自己利用這些數(shù)據(jù)做試驗了。這個系統(tǒng)工作的很好，并且在適度的Sun Ultra SPARC工作站(2x200MHz)上處理數(shù)據(jù)沒有任何問題。最終這個系統(tǒng)移植到了Linux上。

1.4 MySQL 基準(zhǔn)套件

基準(zhǔn)套件就是想告訴用戶執(zhí)行什么樣的SQL查詢表現(xiàn)的更好或者更差。請注意，這個基準(zhǔn)是單線程的，因此它度量了操作執(zhí)行的最少時間。我們未來打算增加多線程測試的基準(zhǔn)套件。

想要使用基準(zhǔn)套件，必備以下幾個條件：

基準(zhǔn)腳本是用Perl寫的，它用Perl的DBI模塊來連接數(shù)據(jù)庫，因此必須安裝DBI模塊。并且還需要每個要做測試的服務(wù)器上都有特定的BDB驅(qū)動程序。例如，為了測試MySQL、PostgreSQL和DB2，就必須安裝 DBD::mysql, DBD::Pg 及 DBD::DB2 模塊。詳情請看"2.7 Perl Installation Note"。

取得MySQL的分發(fā)源代碼后，就能在 `sql-bench' 目錄下看到基準(zhǔn)套件。想要運(yùn)行這些基準(zhǔn)測試，請先搭建好服務(wù)，然后進(jìn)入 `sql-bench' 目錄，執(zhí)行 run-all-tests 腳本：

shell> cd sql-bench

shell> perl run-all-tests --server=server_name

server_name 可以是任何一個可用的服務(wù)。想要列出所有的可用選項和支持的服務(wù)，只要調(diào)用以下命令：

shell> perl run-all-tests --help

crash-me 腳本也是放在 `sql-bench' 目錄下。crash-me 通過執(zhí)行真正的查詢以試圖判斷數(shù)據(jù)庫都支持什么特性、性能表現(xiàn)以及限制。例如，它可以判斷：

·都支持什么字段類型

·支持多少索引

·支持什么樣的函數(shù)

·能支持多大的查詢

·VARCHAR 字段類型能支持多大

1.5 使用您自己的基準(zhǔn)

請確定對您的數(shù)據(jù)庫或者應(yīng)用程序做基準(zhǔn)測試，以發(fā)現(xiàn)它們的瓶頸所在。解決這個瓶頸(或者使用一個假的模塊來代替)之后，就能很容易地找到下一個瓶頸了。即使應(yīng)用程序當(dāng)前總體的表現(xiàn)可以接受，不過還是至少要做好找到每個瓶頸的計劃，說不定某天您就希望應(yīng)用程序能有更好的性能。

從 MySQL的基準(zhǔn)套件中就能找到一個便攜可移植的基準(zhǔn)測試程序了。詳情請看"7.1.4 The MySQL Benchmark Suite"。您可以從基準(zhǔn)套件中的任何一個程序，做適當(dāng)?shù)男薷囊赃m合您的需要。通過整個方式，您就可以有各種不同的辦法來解決問題，知道哪個程序才是最快的。

當(dāng)系統(tǒng)負(fù)載十分繁重的時候，通常就會發(fā)生問題。我們就有很多客戶聯(lián)系我們說他們有一個(測試過的)生產(chǎn)系統(tǒng)也遭遇了負(fù)載問題。在很多情況下，性能問題歸結(jié)于數(shù)據(jù)庫的基本設(shè)計(例如，在高負(fù)載下掃描數(shù)據(jù)表的表現(xiàn)不好)、操作系統(tǒng)、或者程序庫等因素。很多時候，這些問題在還沒有正式用于生產(chǎn)前相對更容易解決。

2、優(yōu)化 SELECT 語句及其他查詢

首先，影響所有語句的一個因素是：您的權(quán)限設(shè)置越復(fù)雜，那么開銷就越大。使用比較簡單的 GRANT 語句能讓MySQL減少在客戶端執(zhí)行語句時權(quán)限檢查的開銷。例如，如果沒有設(shè)定任何表級或者字段級的權(quán)限，那么服務(wù)器就無需檢查 tables_priv 和 columns_priv 表的記錄了。同樣地，如果沒有對帳戶設(shè)定任何資源限制的話，那么服務(wù)器也就無需做資源使用統(tǒng)計了。如果有大量查詢的話，花點時間來規(guī)劃簡單的授權(quán)機(jī)制以減少服務(wù)器權(quán)限檢查的開銷是值得的。

如果問題處在一些MySQL特定的表達(dá)式或者函數(shù)上，則可以通過 mysql 客戶端程序使用 BENCHMARK() 函數(shù)做一個定時測試。它的語法是：BENCHMARK(loop_count,expression)。例如：

所有的MySQL函數(shù)都應(yīng)該被最優(yōu)化，不過仍然有些函數(shù)例外。BENCHMARK() 是一個用于檢查查詢語句中是否存在問題的非常好的工具。