概述

本日,數(shù)據(jù)庫的操作越來越成為整個(gè)應(yīng)用的性能瓶頸了,這點(diǎn)對于Web應(yīng)用尤其明顯.關(guān)于數(shù)據(jù)庫的性能,這并不只是DBA才需要擔(dān)心的事,而這更是我 們程序員需要去關(guān)注的事情.當(dāng)我們?nèi)ピO(shè)計(jì)數(shù)據(jù)庫表結(jié)構(gòu),對操作數(shù)據(jù)庫時(shí)(尤其是查表時(shí)的SQL語句),我們都需要注意數(shù)據(jù)操作的性能.這里,我們不會講過 多的SQL語句的優(yōu)化,而只是針對MySQL這一Web應(yīng)用最多的數(shù)據(jù)庫.希望下面的這些優(yōu)化技巧對你有用.

MySQL架構(gòu)圖

一丶 為查詢緩存優(yōu)化你的查詢

大多數(shù)的MySQL服務(wù)器都開啟了查詢緩存.這是提高性最有效的方法之一,而且這是被MySQL的數(shù)據(jù)庫引擎處理的.當(dāng)有很多相同的查詢被執(zhí)行了多次的時(shí)候,這些查詢結(jié)果會被放到一個(gè)緩存中,這樣,后續(xù)的相同的查詢就不用操作表而直接拜訪緩存結(jié)果了.MySQL的查詢緩存對這個(gè)函數(shù)不起作用.所以,像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數(shù)都不會開啟查詢緩存,因?yàn)檫@些函數(shù)的返回是會不定的易變的.所以,你所需要的就是用一個(gè)變量來代替MySQL的函數(shù),從而 開啟緩存.

二丶EXPLAIN 你的 SELECT 查詢

使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語句的.這可以幫你分析你的查詢語句或是表布局的性能瓶頸.

EXPLAIN 的查詢成果還會告訴你你的索引主鍵被如何利用的,你的數(shù)據(jù)表是如何被搜索和排序的……等等,等等.

挑一個(gè)你的SELECT語句(保舉挑選那個(gè)最復(fù)雜的,有多表聯(lián)接的),把關(guān)鍵字EXPLAIN加到前面.你可以使用phpmyadmin來做這個(gè)事.然后,你會看到一張表格.

三丶 永遠(yuǎn)為每張表設(shè)置一個(gè)ID

我們應(yīng)該為數(shù)據(jù)庫里的每張表都設(shè)置一個(gè)ID做為其主鍵,而且最好的是一個(gè)INT型的(保舉使用UNSIGNED),并設(shè)置上自動增加的AUTO_INCREMENT標(biāo)志.

就算是你 users 表有一個(gè)主鍵叫 “email”的字段,你也別讓它成為主鍵.使用 VARCHAR 類型來當(dāng)主鍵會使用得性能下降.另外,在你的程序中,你應(yīng)該使用表的ID來構(gòu)造你的數(shù)據(jù)布局.

而且,在MySQL數(shù)據(jù)引擎下,還有一些操作需要使用主鍵,在這些情況下,主鍵的性能和設(shè)置變得非常重要,好比,集群,分區(qū)……

在這里,只有一個(gè)情況是例外,那便是“關(guān)聯(lián)表”的“外鍵”,也便是說,這個(gè)表的主鍵,通過若干個(gè)別的表的主鍵構(gòu)成.我們把這個(gè)情況叫做“外鍵”.比 如：有一個(gè)“學(xué)生表”有學(xué)生的ID,有一個(gè)“課程表”有課程ID,那么,“成績表”便是“關(guān)聯(lián)表”了,其關(guān)聯(lián)了學(xué)生表和課程表,在成績表中,學(xué)生ID和課 程ID叫“外鍵”其共同組成主鍵.

四丶 盡可能的使用 NOT NULL

除非你有一個(gè)很特別的原因去使用 NULL 值,你應(yīng)該總是讓你的字段堅(jiān)持 NOT NULL.這看起來好像有點(diǎn)爭議,請往下看.

首先,問問你本身“Empty”和“NULL”有多大的區(qū)別(如果是INT,那就是0和NULL)?如果你覺得它們之間沒有什么區(qū)別,那么你就不要使用NULL.(你知道嗎?在 Oracle 里,NULL 和 Empty 的字符串是一樣的!)

不要以為 NULL 不需要空間,其需要額外的空間,并且,在你進(jìn)行比擬的時(shí)候,你的程序會更復(fù)雜. 當(dāng)然,這里并不是說你就不能使用NULL了,現(xiàn)實(shí)情況是很復(fù)雜的,依然會有些情況下,你需要使用NULL值.

五丶使用 ENUM 而不是 VARCHAR

ENUM 類型是非?？旌途o湊的.在實(shí)際上,其保留的是 TINYINT,但其外表上顯示為字符串.這樣一來,用這個(gè)字段來做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝?

如果你有一個(gè)字段,好比“性別”,“國家”,“民族”,“狀態(tài)”或“部門”,你知道這些字段的取值是有限而且固定的,那么,你應(yīng)該使用 ENUM 而不是 VARCHAR.

MySQL也有一個(gè)“建議”(見第十條)告訴你怎么去重新組織你的表布局.當(dāng)你有一個(gè) VARCHAR 字段時(shí),這個(gè)建議會告訴你把其改成 ENUM 類型.使用 PROCEDURE ANALYSE() 你可以得到相關(guān)的建議.

六丶 固定長度的表會更快

如果表中的所有字段都是“固定長度”的,整個(gè)表會被認(rèn)為是 “static” 或 “fixed-length”. 例如,表中沒有如下類型的字段： VARCHAR,TEXT,BLOB.只要你包括了其中一個(gè)這些字段,那么這個(gè)表就不是“固定長度靜態(tài)表”了,這樣,MySQL 引擎會用另一種辦法來處理.

固定長度的表會提高性能,因?yàn)镸ySQL搜尋得會更快一些,因?yàn)檫@些固定的長度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的,所以讀取的自然也會很快.而如果字段不是定長的,那么,每一次要找下一條的話,必要程序找到主鍵.

而且,固定長度的表也更容易被緩存和重建.不過,唯一的副作用是,固定長度的字段會浪費(fèi)一些空間,因?yàn)槎ㄩL的字段無論你用不用,他都是要分配那么多的空間.

使用“垂直分割”技術(shù),你可以分割你的表成為兩個(gè)一個(gè)是定長的,一個(gè)則是不定長的.

七丶垂直分割

“垂直分割”是一種把數(shù)據(jù)庫中的表按列變成幾張表的辦法,這樣可以降低表的復(fù)雜度和字段的數(shù)目,從而達(dá)到優(yōu)化的目的.(以前,在銀行做過項(xiàng)目,見過一張表有100多個(gè)字段,很恐怖)

示例一：在Users表中有一個(gè)字段是家庭地址,這個(gè)字段是可選字段,相比起,而且你在數(shù)據(jù)庫操作的時(shí)候除了個(gè) 人信息外,你并不必要經(jīng)常讀取或是改寫這個(gè)字段.那么,為什么不把他放到另外一張表中呢? 這樣會讓你的表有更好的性能,大家想想是不是,大量的時(shí)候,我對于用戶表來說,只有用戶ID,用戶名,口令,用戶角色等會被經(jīng)常使用.小一點(diǎn)的表總是會有 好的性能.

示例二： 你有一個(gè)叫 “l(fā)ast_login” 的字段,它會在每次用戶登錄時(shí)被更新.但是,每次更新時(shí)會導(dǎo)致該表的查詢緩存被清空.所以,你可以把這個(gè)字段放到另一個(gè)表中,這樣就不會影響你對用戶 ID,用戶名,用戶角色的不絕地讀取了,因?yàn)椴樵兙彺鏁湍阍黾雍芏嘈阅?

另外,你必要注意的是,這些被分出去的字段所形成的表,你不會經(jīng)常性地去Join他們,不然的話,這樣的性能會比不分割時(shí)還要差,而且,會是極數(shù)級的下降.

八丶 選擇正確的存儲引擎

大部分情況下,InnoDB都是正確的選擇,可以簡單地歸納為一句話“除非需要用到某些InnoDB不具備的特性,并且沒有其他方法可以替代,否則都應(yīng)該優(yōu)先選擇InnoDB引擎”.

除非萬不得已,否則建議不要混合使用多種存儲引擎,否則可能帶來一系列負(fù)責(zé)的問題,以及一些潛在的bug和界限問題.

如果應(yīng)用必要不同的存儲引擎,請先考慮以下幾個(gè)因素：

事務(wù)：

如果應(yīng)用必要事務(wù)支持,那么InnoDB(或者XtraDB)是目前最穩(wěn)定并且經(jīng)過驗(yàn)證的選擇.

備份：

如果可以定期地關(guān)閉服務(wù)器來執(zhí)行備份,那么備份的因素可以忽略.反之,如果需要在線熱備份,那么選擇InnoDB就是基本的要求.瓦解恢復(fù)MyISAM瓦解后發(fā)生損壞的概率比InnoDB要高很多,而且恢復(fù)速度也要慢.

如果一個(gè)存儲引擎擁有一些關(guān)鍵的特性,同時(shí)卻又缺乏一些需要的特性,那么有時(shí)候不得不做折中的考慮,或者在架構(gòu)設(shè)計(jì)上做一些取舍.

九丶使用一個(gè)對象關(guān)系映射器(Object Relational Mapper)

使用 ORM (Object Relational Mapper),你能夠獲得可靠的性能增漲.一個(gè)ORM可以做的所有事情,也能被手動的編寫出來.但是,這必要一個(gè)高級專家.

ORM 的最重要的是“Lazy Loading”,也就是說,只有在必要的去取值的時(shí)候才會去真正的去做.但你也必要小心這種機(jī)制的副作用,因?yàn)檫@很有可能會因?yàn)橐?chuàng)建很多很多小的查詢反而會降低性能.

ORM 還可以把你的SQL語句打包成一個(gè)事務(wù),這會比單獨(dú)執(zhí)行他們快得多得多.

目前,個(gè)人最喜歡的PHP的ORM是：Doctrine.

十丶 小心“永久鏈接”

一直對mysql_pconnect 和mysql_connect的理解差別是p鏈接是持久鏈接,不會關(guān)閉了鏈接,即使你使用了mysql_close();建立連接后,將保持sleep狀態(tài),可以使用show processlist 查看有哪些正在sleep的連接,這樣p鏈接的好處是當(dāng)有新的哀求時(shí)就直接返回這個(gè)連接,感覺類似于http1.1協(xié)議的keep-alive的用法,到底是開個(gè)還是關(guān)呢還是不關(guān)呢,一般那些專家都告訴你,當(dāng)用戶量不多時(shí),可以打開keep -live 這樣可以,在http協(xié)議進(jìn)行打交道時(shí),給用戶保持連接.

在理論上來說,這聽起來非常的不錯(cuò).但是從個(gè)人經(jīng)驗(yàn)(也是大多數(shù)人的)上來說,這個(gè)功能制造出來的麻煩事更多.因?yàn)?你只有有限的鏈接數(shù),內(nèi)存問題,文件句柄數(shù),等等.

而且,Apache 運(yùn)行在極端并行的環(huán)境中,會創(chuàng)建很多很多的了進(jìn)程.這就是為什么這種“永久鏈接”的機(jī)制工作地不好的原因.在你決定要使用“永久鏈接”之前,你必要好好地考慮一下你的整個(gè)系統(tǒng)的架構(gòu).

總結(jié)

以上是對MySQL性能優(yōu)化總結(jié),分享給大家,希望大家可以了解什么是MySQL性能優(yōu)化.覺得收獲的話可以點(diǎn)個(gè)存眷收藏轉(zhuǎn)發(fā)一波喔,謝謝大佬們支持.(吹一波,233~~)