為大型網(wǎng)站提速

--mysql優(yōu)化講師：韓順平email:hanshunping@qq:2403931377數(shù)據(jù)庫優(yōu)化工作對于一個以數(shù)據(jù)為中心的應(yīng)用，數(shù)據(jù)庫的好壞直接影響到程序的性能，因此數(shù)據(jù)庫性能至關(guān)重要。一般來說，要保證數(shù)據(jù)庫的效率，要做好以下四個方面的工作：①數(shù)據(jù)庫設(shè)計②sql語句優(yōu)化③數(shù)據(jù)庫參數(shù)配置④恰當(dāng)?shù)挠布Y源和操作系統(tǒng)這個順序也表現(xiàn)了這四個工作對性能影響的大小

數(shù)據(jù)庫表設(shè)計 通俗地理解三個范式，對于數(shù)據(jù)庫設(shè)計大有好處。在數(shù)據(jù)庫設(shè)計中，為了更好地應(yīng)用三個范式，就必須通俗地理解三個范式(通俗地理解是夠用的理解，并不是最科學(xué)最準(zhǔn)確的理解)：第一范式：1NF是對屬性的原子性約束，要求屬性具有原子性，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫都滿足1NF)第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標(biāo)識，即實體的惟一性；第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余。沒有冗余的數(shù)據(jù)庫設(shè)計可以做到。但是，沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，有時為了提高運(yùn)行效率，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。具體做法是：在概念數(shù)據(jù)模型設(shè)計時遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計時考慮。降低范式就是增加字段，允許冗余。

數(shù)據(jù)庫設(shè)計 舉例說明什么事適度冗余,或者說有理由的冗余!數(shù)據(jù)庫設(shè)計 給學(xué)生說一個相冊瀏覽次數(shù)的設(shè)計案例SQL語句優(yōu)化 SQL優(yōu)化的一般步驟通過showstatus命令了解各種SQL的執(zhí)行頻率。定位執(zhí)行效率較低的SQL語句-（重點select）通過explain分析低效率的SQL語句的執(zhí)行情況確定問題并采取相應(yīng)的優(yōu)化措施SQL語句優(yōu)化-show參數(shù) MySQL客戶端連接成功后，通過使用show[session|global]status命令可以提供服務(wù)器狀態(tài)信息。其中的session來表示當(dāng)前的連接的統(tǒng)計結(jié)果，global來表示自數(shù)據(jù)庫上次啟動至今的統(tǒng)計結(jié)果。默認(rèn)是session級別的。

下面的例子：

showstatuslike‘Com_%’;

其中Com_XXX表示XXX語句所執(zhí)行的次數(shù)。

重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數(shù)，可以容易地了解到當(dāng)前數(shù)據(jù)庫的應(yīng)用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執(zhí)行比例是多少。還有幾個常用的參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況。

Connections：試圖連接MySQL服務(wù)器的次數(shù)

Uptime：服務(wù)器工作的時間（單位秒）

Slow_queries：慢查詢的次數(shù)(默認(rèn)是10)SQL語句優(yōu)化-定位慢查詢 通過慢查詢?nèi)罩径ㄎ粓?zhí)行效率較低的SQL語句。慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過long_query_time所設(shè)置的SQL語句。

showvariableslike‘long_query_time’;

setlong_query_time=2;測試語句

select*fromempe,deptdwheree.empno=123451ande.deptno=d.deptno; 查看慢查詢?nèi)罩荆耗J(rèn)為數(shù)據(jù)目錄data中的host-name-slow.log。低版本的mysql需要通過在開啟mysql時使用--log-slow-queries[=file_name]來配置在默認(rèn)情況下mysql不記錄慢查詢?nèi)罩荆枰趩拥臅r候指定

bin\mysqld.exe--slow-query-logSQL語句優(yōu)化-explain分析問題

Explainselect*fromempwhereename=“zrlcHd”

會產(chǎn)生如下信息：

select_type:表示查詢的類型。

table:輸出結(jié)果集的表

type:表示表的連接類型

possible_keys:表示查詢時，可能使用的索引

key:表示實際使用的索引

key_len:索引字段的長度

rows:掃描的行數(shù)

Extra:執(zhí)行情況的描述和說明建立適當(dāng)?shù)乃饕? 說起提高數(shù)據(jù)庫性能，索引是最物美價廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行個正確的’createindex’，查詢速度就可能提高百倍千倍，這可真有誘惑力?？墒翘煜聸]有免費(fèi)的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。這里我們舉例說明索引的好處(快速體驗案例)是不是建立一個索引就能解決所有的問題?ename上沒有建立索引會怎樣?select*fromempwhereename=‘a(chǎn)xJxC’;索引的原理說明 沒有索引為什么會慢?使用索引為什么會快?索引的代價磁盤占用對dml(updatedeleteinsert)語句的效率影響哪些列上適合添加索引 較頻繁的作為查詢條件字段應(yīng)該創(chuàng)建索引 select*fromempwhereempno=1唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件 select*fromempwheresex='男'更新非常頻繁的字段不適合創(chuàng)建索引 select*fromempwherelogincount=1不會出現(xiàn)在WHERE子句中字段不該創(chuàng)建索引：）索引的類型 主索引，主鍵自動的為主索引(類型Primary)唯一索引(UNIQUE)普通索引(INDEX)全文索引(FULLTEXT)（只有MyISAM存儲引擎支持）

sphinx+中文分詞coreseek 綜合使用=>復(fù)合索引索引的使用 建立索引

create[UNIQUE|FULLTEXT]indexindex_nameontbl_name(col_name[(length)][ASC|DESC],…..);

altertabletable_nameADDINDEX[index_name](index_col_name,...)

添加主鍵(索引)ALTERTABLE表名ADDPRIMARYKEY(列名,..);聯(lián)合主鍵刪除索引

DROPINDEXindex_nameONtbl_name;

altertabletable_namedropindexindex_name;

刪除主鍵(索引)比較特別:altertablet_bdropprimarykey;查詢索引(均可)

showindexfromtable_name;

showkeysfromtable_name;

desc

table_Name;索引的使用 查詢要使用索引最重要的條件是查詢條件中需要使用索引。下列幾種情況下有可能使用到索引：

1，對于創(chuàng)建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。

2，對于使用like的查詢，查詢?nèi)绻恰?aaa’不會使用到索引

‘a(chǎn)aa%’會使用到索引。下列的表將不使用索引：

1，如果條件中有or，即使其中有條件帶索引也不會使用。

2，對于多列索引，不是使用的第一部分，則不會使用索引。

3，like查詢是以%開頭

4，如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引。

5，如果mysql估計使用全表掃描要比使用索引快，則不使用索引。索引的使用 查看索引的使用情況

showstatuslike‘Handler_read%’;

大家可以注意：

handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數(shù)。 handler_read_rnd_next:這個值越高，說明查詢低效。常用SQL優(yōu)化 大批量插入數(shù)據(jù)

對于MyISAM：

altertabletable_namedisablekeys;

loadingdata;

altertabletable_nameenablekeys;

對于Innodb：

1，將要導(dǎo)入的數(shù)據(jù)按照主鍵排序

2，setunique_checks=0,關(guān)閉唯一性校驗。

3，setautocommit=0,關(guān)閉自動提交。常用SQL優(yōu)化 優(yōu)化groupby語句

默認(rèn)情況，MySQL對所有的groupbycol1,col2進(jìn)行排序。這與在查詢中指定orderbycol1,col2類似。如果查詢中包括groupby但用戶想要避免排序結(jié)果的消耗，則可以使用orderbynull禁止排序有些情況下，可以使用連接來替代子查詢。

因為使用join，MySQL不需要在內(nèi)存中創(chuàng)建臨時表。(講解)如果想要在含有or的查詢語句中利用索引，則or之間的每個條件列都必須用到索引，如果沒有索引，則應(yīng)該考慮增加索引(與環(huán)境相關(guān)講解) select*from表名where條件1=‘’or條件2=‘tt’選擇合適的存儲引擎 MyISAM:默認(rèn)的MySQL存儲引擎。如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不是很高。其優(yōu)勢是訪問的速度快。InnoDB：提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對比MyISAM，寫的處理效率差一些并且會占用更多的磁盤空間。選擇合適的數(shù)據(jù)類型 在精度要求高的應(yīng)用中，建議使用定點數(shù)來存儲數(shù)值，以保證結(jié)果的準(zhǔn)確性。對于存儲引擎是MyISAM的數(shù)據(jù)庫,如果經(jīng)常做刪除和修改記錄的操作，要定時執(zhí)行optimizetabletable_name;功能對表進(jìn)行碎片整理。日期類型要根據(jù)實際需要選擇能夠滿足應(yīng)用的最小存儲的早期類型createtablebbs(idint,convarchar(1024),pub_timeint);date(‘Ymd’,時間-3*24*60*60);2038年-1-19對表進(jìn)行水平劃分 主講:韓順平如果一個表的記錄數(shù)太多了，比如上千萬條，而且需要經(jīng)常檢索，那么我們就有必要化整為零了。如果我拆成100個表，那么每個表只有10萬條記錄。當(dāng)然這需要數(shù)據(jù)在邏輯上可以劃分。一個好的劃分依據(jù)，有利于程序的簡單實現(xiàn)，也可以充分利用水平分表的優(yōu)勢。比如系統(tǒng)界面上只提供按月查詢的功能，那么把表按月拆分成12個，每個查詢只查詢一個表就夠了。如果非要按照地域來分，即使把表拆的再小，查詢還是要聯(lián)合所有表來查，還不如不拆了。所以一個好的拆分依據(jù)是最重要的。UNION對表進(jìn)行水平劃分 主講:韓順平每個學(xué)生做過的題都記錄在這個表里，包括對題和錯題。每個題會對應(yīng)一個或多個知識點，我們需要根據(jù)錯題來分析學(xué)生在哪個知識點上掌握的不足。這個表很容易達(dá)到千萬級，迫切需要拆分，那么根據(jù)什么來拆呢?從需求上看，無論是老師還是學(xué)生，最終會把焦點落在一個學(xué)生的身上。學(xué)生會關(guān)心自己，老師會關(guān)心自己班的學(xué)生。而且每個學(xué)科的知識點是不同的。所以我們很容易想到，聯(lián)合學(xué)科和知識點兩個字段來拆分這個表。這樣拆下來，每個表大概2萬條數(shù)據(jù)，檢索效率非常高。(使用hash方法來處理)對表進(jìn)行垂直劃分 主講:韓順平有些表記錄數(shù)并不多，可能也就2、3萬條，但是字段卻很長，表占用空間很大，檢索表時需要執(zhí)行大量I/O，嚴(yán)重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關(guān)系。

(JOIN)對表進(jìn)行垂直劃分 主講:韓順平【試題內(nèi)容】、【答案信息】兩個表，最初是作為幾個字段添加到【試題信息】里的，可以看到試題內(nèi)容和答案這兩個字段很長，在表里有3萬記錄時，表已經(jīng)占了1G的空間，在列試題列表時非常慢。經(jīng)過分析，發(fā)現(xiàn)系統(tǒng)很多時候是根據(jù)【冊】、【單元】、類型、類別、難易程度等查詢條件，分頁顯示試題詳細(xì)內(nèi)容。而每次檢索都是這幾個表做join，每次要掃描一遍1G的表。我們完全可以把內(nèi)容和答案拆分成另一個表，只有顯示詳細(xì)內(nèi)容的時候才讀這個大表，由此就產(chǎn)生了【試題內(nèi)容】、【答案信息】兩個表。

選擇適當(dāng)?shù)淖侄晤愋停貏e是主鍵

主講:韓順平選擇字段的一般原則是保小不保大，能用占用字節(jié)小的字段就不用大字段。比如主鍵，建議使用自增類型，這樣省空間,空間就是效率!按4個字節(jié)和按32個字節(jié)定位一條記錄，誰快誰慢太明顯了。涉及到幾個表做join時，效果就更明顯了。

es1234,建議使用一個不含業(yè)務(wù)邏輯的id做主角 student表

idstuno

stuname

adress

1es1234 順平 北京文件、圖片等大文件用文件系統(tǒng)存儲

數(shù)據(jù)庫只存儲路徑。圖片和文件存放在文件系統(tǒng)，甚至單獨(dú)放在一臺服務(wù)器(圖床).數(shù)據(jù)庫參數(shù)配置

主講:韓順平最重要的參數(shù)就是內(nèi)存，我們主要用的innodb引擎，所以下面兩個參數(shù)調(diào)的很大

innodb_additional_