數(shù)據(jù)庫(kù)性能優(yōu)化講座

關(guān)于數(shù)據(jù)庫(kù)性能優(yōu)化的講座研發(fā)部講師介紹：

姓名：邵宗文

部門：研發(fā)中心

崗位：數(shù)據(jù)庫(kù)平臺(tái)主管

主要負(fù)責(zé)：公司數(shù)據(jù)庫(kù)平臺(tái)

2009年月6日第1頁(yè)，共39頁(yè)。產(chǎn)品概要：給各個(gè)應(yīng)用部門提供一個(gè)高可用的數(shù)據(jù)庫(kù)平臺(tái)服務(wù)。產(chǎn)品目標(biāo)：通過數(shù)據(jù)庫(kù)平臺(tái)，實(shí)現(xiàn)應(yīng)用項(xiàng)目數(shù)據(jù)庫(kù)的資源合理調(diào)配，讓應(yīng)用部門能夠更加專注于產(chǎn)品代碼開發(fā)，無(wú)需過多考慮后臺(tái)數(shù)據(jù)庫(kù)的部署和運(yùn)維。產(chǎn)品特色：實(shí)現(xiàn)數(shù)據(jù)庫(kù)的高可用，對(duì)有問題的數(shù)據(jù)庫(kù)機(jī)器實(shí)現(xiàn)自動(dòng)故障下線和自動(dòng)修復(fù)上線。針對(duì)數(shù)據(jù)庫(kù)的各種狀況的自動(dòng)化監(jiān)控，報(bào)警。分布式多IDC的數(shù)據(jù)中心，既能提高南北用戶訪問體驗(yàn)，并能做到IDC級(jí)容災(zāi)和切換。每天定時(shí)備份，保證了誤操作之后幾分鐘之內(nèi)相應(yīng)數(shù)據(jù)恢復(fù)。自動(dòng)將相關(guān)慢日志sql發(fā)送給對(duì)應(yīng)應(yīng)用開發(fā)人員依據(jù)各個(gè)應(yīng)用項(xiàng)目的生命周期，進(jìn)行機(jī)器資源的合理調(diào)配，給公司大大降低服務(wù)器成本。

數(shù)據(jù)庫(kù)平臺(tái)－－產(chǎn)品介紹第2頁(yè)，共39頁(yè)。目前部署規(guī)模：

4個(gè)IDC數(shù)據(jù)中心（北京，天津，上海，廣州），約50T的數(shù)據(jù)量，約有300個(gè)產(chǎn)品項(xiàng)目使用，重點(diǎn)產(chǎn)品包括財(cái)經(jīng)，體育，發(fā)布，音樂，讀書，UC,統(tǒng)一會(huì)員,空間app，朋友，圈子，汽車，科技，房產(chǎn)，博客廣告分享平臺(tái)等。成本節(jié)省方面：

通過數(shù)據(jù)庫(kù)平臺(tái)，大大節(jié)約了公司的成本，解決了以前各個(gè)部門單獨(dú)申請(qǐng)機(jī)器，導(dǎo)致項(xiàng)目出現(xiàn)冷熱周期而出現(xiàn)機(jī)器低使用率問題。重點(diǎn)產(chǎn)品優(yōu)化案例：在2007年財(cái)經(jīng)特別火爆時(shí)候，數(shù)據(jù)庫(kù)訪問量急劇增大，同時(shí)實(shí)時(shí)性必須得兼顧情況下，后來(lái)財(cái)經(jīng)自己把大部分?jǐn)?shù)據(jù)庫(kù)遷移到數(shù)據(jù)庫(kù)平臺(tái)之后，上述問題都被成功解決，并且多個(gè)財(cái)經(jīng)產(chǎn)品如自選股，模擬炒股。2008奧運(yùn)會(huì)期間，數(shù)據(jù)庫(kù)平臺(tái)為體育部門成功解決了奧運(yùn)期間數(shù)據(jù)量更新多，且實(shí)時(shí)性要求特別高的問題。數(shù)據(jù)庫(kù)平臺(tái)－－成功案例第3頁(yè)，共39頁(yè)。其他優(yōu)化案例：新浪北美，香港的數(shù)據(jù)庫(kù)架構(gòu)改造。圈子數(shù)據(jù)庫(kù)的重新設(shè)計(jì)和架構(gòu)改造。發(fā)布數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)架構(gòu)改造。UC數(shù)據(jù)庫(kù)的遷移和架構(gòu)重新改造統(tǒng)一會(huì)員信息庫(kù)的重新架構(gòu)和改造?目前數(shù)據(jù)庫(kù)平臺(tái)運(yùn)維人員2人第4頁(yè)，共39頁(yè)。數(shù)據(jù)庫(kù)性能優(yōu)化數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)設(shè)計(jì)的性能考慮數(shù)據(jù)庫(kù)應(yīng)用實(shí)現(xiàn)的性能優(yōu)化數(shù)據(jù)庫(kù)參數(shù)的優(yōu)化缺省以MySQL4.0/4.1/5.0，MyISAM表為主第5頁(yè)，共39頁(yè)。何時(shí)需要優(yōu)化低層次–發(fā)現(xiàn)負(fù)載過高、性能下降時(shí)一般–了解數(shù)據(jù)庫(kù)處理機(jī)制，實(shí)現(xiàn)時(shí)優(yōu)化索引高層次–設(shè)計(jì)應(yīng)用時(shí)，從表結(jié)構(gòu)設(shè)計(jì)上保證第6頁(yè)，共39頁(yè)。結(jié)構(gòu)設(shè)計(jì)優(yōu)化原則1.了解自己的應(yīng)用應(yīng)用類型

讀多寫少（如體育項(xiàng)目），讀寫比例差不多（如郵件），和寫多讀少（如投票，統(tǒng)計(jì)）預(yù)計(jì)數(shù)據(jù)量

半年？一年？后續(xù)擴(kuò)展？決定單表還是多表，擴(kuò)展的方法預(yù)計(jì)訪問量 多少讀？多少寫？峰值？幾臺(tái)服務(wù)器，主從方式實(shí)時(shí)數(shù)據(jù)和非實(shí)時(shí)數(shù)據(jù)

哪些必須實(shí)時(shí)查詢？哪些可以預(yù)先準(zhǔn)備或近似？哪些用于統(tǒng)計(jì)匯總？時(shí)間的要求

實(shí)時(shí)性高的項(xiàng)目，如財(cái)經(jīng)，體育，實(shí)時(shí)性低的項(xiàng)目如博客圈。第7頁(yè)，共39頁(yè)。結(jié)構(gòu)設(shè)計(jì)優(yōu)化原則2.數(shù)據(jù)表盡量小

-行數(shù)少，字段類型高效

-為什么？

√IO高效√全表遍歷√表級(jí)鎖提高并發(fā)度√便于應(yīng)用分布式結(jié)構(gòu) √可擴(kuò)展性好√altertable快√損壞修復(fù)快√備份和數(shù)據(jù)庫(kù)重建時(shí)間短

-手段：

分庫(kù)、分表 使用最合適的類型長(zhǎng)度，比如男女代碼用tinyint就可以了，IP用varchar(15)就 一些如當(dāng)天統(tǒng)計(jì)活躍用戶的自己內(nèi)部需要的數(shù)據(jù)可以用內(nèi)存表。 應(yīng)該盡量把字段設(shè)置為NOTNULL，這樣在將來(lái)執(zhí)行查詢的時(shí)候，數(shù)據(jù)庫(kù)不用去比較NULL值。

-負(fù)面影響：

日志、統(tǒng)計(jì)等用途要慎選分表依據(jù)第8頁(yè)，共39頁(yè)。分表原則的選擇 按時(shí)間 按地區(qū) 按ID，手機(jī)號(hào)…

按hash值要點(diǎn)：平均分擔(dān)數(shù)據(jù)和負(fù)載

第9頁(yè)，共39頁(yè)。結(jié)構(gòu)設(shè)計(jì)優(yōu)化原則3.表數(shù)量的限制

--為什么？

-受文件系統(tǒng)操作限制，文件數(shù)過大需要更多文件句柄，且大目錄 操作造成復(fù)制、壓縮、備份效率低。

-打開表占用數(shù)據(jù)庫(kù)資源（table_cache）

--

建議一個(gè)庫(kù)不應(yīng)超過300-400個(gè)表

--不當(dāng)?shù)脑O(shè)計(jì)： 長(zhǎng)期運(yùn)營(yíng)的項(xiàng)目，每次活動(dòng)一個(gè)（一組）表

--與“表盡量小”矛盾，一般來(lái)說表數(shù)量限制較嚴(yán)格第10頁(yè)，共39頁(yè)。結(jié)構(gòu)設(shè)計(jì)優(yōu)化原則4.字段定義最好能適當(dāng)

-最費(fèi)時(shí)的操作是行尋址，后續(xù)的整塊讀寫延遲有限

-分割字段可能意味著聯(lián)合查詢（join） 優(yōu)點(diǎn)：數(shù)據(jù)邏輯清晰，冗余小，更新方便 缺點(diǎn)：臨時(shí)表，優(yōu)化復(fù)雜

-可以接受適當(dāng)?shù)娜哂嗪蛥R總數(shù)據(jù)

-盡量避免使用text,varchar(255)

第11頁(yè)，共39頁(yè)。結(jié)構(gòu)設(shè)計(jì)優(yōu)化原則5.訪問量大的應(yīng)用考慮讀寫分開 使用replication適于讀多寫少的應(yīng)用寫庫(kù)master讀庫(kù)1slave1讀庫(kù)nslaven·····第12頁(yè)，共39頁(yè)。HEAP

內(nèi)存表，缺省為hash索引（適合=，不適合range)

速度快 有長(zhǎng)度限制 適于做一些統(tǒng)計(jì)。InnoDB

支持事務(wù) 但是不容易維護(hù)，同時(shí)目前web應(yīng)用主要還是讀多寫少。 性能總體比MyISAM低，但有特例6.其他類型的表格式第13頁(yè)，共39頁(yè)。7.補(bǔ)充/替代解決方案結(jié)果cache

提高響應(yīng)速度，減輕DB負(fù)載，如squid,phpcacheliteHash存儲(chǔ)結(jié)構(gòu)（文件庫(kù)） 速度快，消耗資源少，并發(fā)度高。 無(wú)SQL能力，備份困難Memcached

對(duì)頻繁投票統(tǒng)計(jì)操作和長(zhǎng)期變化不大的數(shù)據(jù)效果很好第14頁(yè)，共39頁(yè)。Mysql的調(diào)用流程主要環(huán)節(jié)語(yǔ)法分析索引檢索全表檢索優(yōu)化整合連接數(shù)據(jù)庫(kù)發(fā)送查詢請(qǐng)求用戶認(rèn)證

編譯執(zhí)行中斷連接其他條件過濾生成結(jié)果自帶優(yōu)化功能從索引取得限制大小Query_cache中存在通過lex/yaccCache池第15頁(yè)，共39頁(yè)。2.庫(kù)表的優(yōu)化正確使用索引，避免全表搜索使用定長(zhǎng)表,且定期做OPTIMIZETABLE命令（注意這個(gè)命令會(huì)鎖表，請(qǐng)?jiān)跀?shù)據(jù)庫(kù)訪問小的時(shí)候做）在對(duì)大表進(jìn)行添加索引，一定要選擇訪問小的時(shí)間段做，否則會(huì)導(dǎo)致嚴(yán)重問題。注:一般臨晨1-2點(diǎn)時(shí)候是訪問的低谷。第16頁(yè)，共39頁(yè)。索引使用的關(guān)鍵“一次查詢中一個(gè)表上只有一個(gè)索引會(huì)起作用！”

5.0以后的版本有發(fā)展，增加了多個(gè)索引檢索結(jié)果歸并（index-merge）的機(jī)制?！八饕龝?huì)減慢寫庫(kù)操作，延長(zhǎng)寫入時(shí)間”

所以只建立必要的有效的索引，并且可以使用insertdelayed等方法。減少磁盤的頻繁IO開銷。第17頁(yè)，共39頁(yè)。索引優(yōu)化第一步、發(fā)現(xiàn)問題記錄slowquerylog

啟動(dòng)參數(shù)--log-slow-queries[=log_file_path]#Time:09060517:07:15#User@Host:biz_r[biz_r]@[10.XX.XX.XX]#Query_time:2Lock_time:0Rows_sent:66Rows_examined:175883selectdistinctF84_1039fromTB_OBJECT_1039,TB_OBJECT_1090where(F4_1090='A'orF4_1090='B')andOB_REVISIONS_1090=F1_1039orderbyF84_1039desc;log_parser協(xié)助分析###99Queries###Totaltime:476,Averagetime:4.80808080808081###Taking3to14secondstocomplete###Rowsanalyzed1104-98810selectcount(*)fromkoubeiwheresubid=NNNandstatus=NNNandflg=NNN;selectcount(*)fromkoubeiwheresubid=111andstatus=1andflg=0;

第18頁(yè)，共39頁(yè)。索引優(yōu)化第二步、查找原因explainselect…from…where….\Gid:1select_type:SIMPLEtable:msg_1100type:ref(ALL)possible_keys:major_defect,major_defect_2,statuskey:major_defect_2 (NULL)key_len:5ref:constrows:51863 (越少越好)Extra:Usingwhere(Usingindex，Usingfilesort

Usingtemporary) 第19頁(yè)，共39頁(yè)。索引優(yōu)化第三步、選擇和試驗(yàn)穩(wěn)妥地改進(jìn)將需要優(yōu)化的相關(guān)表復(fù)制到測(cè)試環(huán)境在測(cè)試環(huán)境啟動(dòng)一個(gè)測(cè)試daemon，關(guān)閉querycache或是使用selectSQL_NO_CACHE方式。未優(yōu)化時(shí)測(cè)試若干次查詢時(shí)間選擇合適的索引試驗(yàn)建立?？梢酝ㄟ^useindex(xx)來(lái)強(qiáng)制使用。檢查是否有效。測(cè)試查詢時(shí)間變化，反復(fù)試驗(yàn)得到最優(yōu)結(jié)果保持關(guān)注，根據(jù)情況隨時(shí)改變索引設(shè)置第20頁(yè)，共39頁(yè)。選擇合適的索引（1）選擇區(qū)分度最大的字段最有效 如果預(yù)測(cè)相關(guān)記錄數(shù)超過一定比例（30%），數(shù)據(jù)庫(kù)選擇全表掃描。showindexfromtablename

獲取表上索引的情況。

Cardinality–“基數(shù)”

-避免使用cardinality小的值做索引

-避免NULL，通過analyzetabletablename得到更準(zhǔn)確的估算第21頁(yè)，共39頁(yè)。選擇合適的索引（2）聯(lián)合索引規(guī)則

-總是同時(shí)出現(xiàn)在查詢條件的多個(gè)字段可以考慮聯(lián)合索引

-組成索引的字段從左到右地出現(xiàn)于查詢條件時(shí)索引起作用，col1應(yīng)該是最常用，區(qū)分度最好的字段

createindexindex1ontable(col1,col2,col3);

√…wherecol1=xandcol2=yandcol3=z;

√…wherecol1=xandcol2=y;

×…wherecol2=yandcol3=z; -不需要再建一個(gè)index(col1)了

-可能用于orderby（稍后詳述）第22頁(yè)，共39頁(yè)。關(guān)于排序盡量使用帶主鍵的字段做orderby的排序盡量不要多提供頁(yè)面的查找（最好只提供20頁(yè)），避免機(jī)器爬蟲查找，導(dǎo)致數(shù)據(jù)庫(kù)壓力負(fù)載過高。因?yàn)樽鰋rderbyfiledlimitxxxxxx,20是非常消耗數(shù)據(jù)庫(kù)資源。第23頁(yè)，共39頁(yè)。Union一個(gè)含OR條件的語(yǔ)句可以分解成多個(gè)語(yǔ)句的union

-好處：繞開一次查詢只用一個(gè)索引的限制

-例子：SELECT*FROMHeadlineWHEREExpireTime>=1012201600ORId<=5000000ORDERBYExpireTimeASCLIMIT10(SELECT*FROMHeadlineWHEREExpireTime>=1081020749ORDERBYExpireTimeASCLIMIT10)UNION(SELECT*FROMHeadlineWHEREId<=50000ORDERBYExpireTimeASCLIMIT10)ORDERBYExpireTimeASCLIMIT10第24頁(yè)，共39頁(yè)。Orderby的優(yōu)化排序的步驟

1.通過索引或全表搜索得到符合條件數(shù)據(jù)的tuple（索引值+行指針），存放在sortbuffer2.如果sort_buffer滿了，做一次排序，生成一個(gè)有序塊存放在臨時(shí)文件

3.重復(fù)上面兩步處理所有數(shù)據(jù)

4.多路歸并各有序塊，得到最后的排序結(jié)果（行指針）

5.根據(jù)行指針取得要求的字段

PS:通過適當(dāng)提高sort_buffer_size,tmp_table_size來(lái)分配多一些內(nèi)存給查詢語(yǔ)句使用。想辦法“不做”或“少做”一般來(lái)說用整數(shù)排序比字符串排序稍好

第25頁(yè)，共39頁(yè)。Orderby（2）“不做”

-selectt.*fromtleftjoinsons.id==;-select*fromtwheretimexx>FROM_DAYS(TO_DAYS(CURDATE())-N)例子：select*fromtablewherestatus=1and…orderbyupdate_timelimit10000,10;第26頁(yè)，共39頁(yè)。使用定長(zhǎng)表優(yōu)點(diǎn) 表長(zhǎng)度上限高（showtablestatusMax_data_length

） 查詢速度快，生成結(jié)果快（由于尋址快） 表?yè)p壞影響有限 修復(fù)快缺點(diǎn) 空間浪費(fèi)權(quán)衡：分離變長(zhǎng)字段到另外的表，提升主表性能

第27頁(yè)，共39頁(yè)。3.數(shù)據(jù)庫(kù)參數(shù)的優(yōu)化showstatus;(5.0之后的用showglobalstatus)Flushstatusshowvariables;(5.0之后的用showglobalstatus)showprocesslist;第28頁(yè)，共39頁(yè)。索引緩沖區(qū)參數(shù)show[global]statuslike‘key%’Key_blocks_used 曾經(jīng)使用過的最大緩沖區(qū)塊數(shù)Key_read_requests 讀取索引請(qǐng)求數(shù)Key_reads 物理讀取索引次數(shù)Key_write_requests 寫索引請(qǐng)求數(shù)Key_writes 物理寫索引次數(shù)若key_reads/key_read_request過大(1%)，需要提高key_buffer_size

可參考：如果key_block_used*1K≈key_buffer_size，也可考慮提高負(fù)面影響 過大可能造成換出；系統(tǒng)崩潰造成的影響更大最多是所有.MYI的大小粗略估算索引大小： （key值長(zhǎng)度+4）*行數(shù)*1.5第29頁(yè)，共39頁(yè)。排序相關(guān)參數(shù)show[global]statuslike‘sort%’Sort_merge_passes 中間結(jié)果merge次數(shù)Sort_range 部分?jǐn)?shù)據(jù)排序Sort_scan 掃描全表排序Sort_rows 排序結(jié)果總行數(shù)

sort_merge_passes如果很大，說明需要提高sort_buffer_size sort_scan非常大，可能需要優(yōu)化索引Sortbuffer是線程buffer，總分配額是buffer_size*threads，不要過大造成換出。第30頁(yè)，共39頁(yè)。tmp_table_sizeshowstatuslike'Created_tmp_%';Created_tmp_disk_tables 在磁盤上建立臨時(shí)表數(shù)Created_tmp_tables 在內(nèi)存和磁盤上建立臨時(shí)表總數(shù) Created_tmp_files 建立臨時(shí)文件數(shù)EXPLAIN里顯示UsingTemporary如果created_tmp_disk_tables所占比例高，可以考慮提高tmp_table_size第31頁(yè)，共39頁(yè)。打開數(shù)據(jù)表Show[global]statuslike‘open%’;Open_files 打開文件數(shù)Open_tables 打開表數(shù)Opened_tables open表次數(shù)總計(jì)Opened_tables很大，說明table_cache參數(shù)需要調(diào)高第32頁(yè)，共39頁(yè)。QueryCache是什么

select[sql_cache|sql_no_cache]colsfromtable…啟動(dòng)參數(shù)

query_cache_size64Mshowstatuslike‘q%’：Qcache_hitsQcache_free_memoryQuestions

命中率=QCache_hits/Questionsquerycache參數(shù)第33頁(yè)，共39頁(yè)。鎖的問題MyISAM是表級(jí)鎖 分表的設(shè)計(jì)減少鎖發(fā)生的可能,但是受磁盤IO影響，在一個(gè)機(jī)器的對(duì)頻繁寫的大表拆分并不能解決問題。showstatuslike‘table%’Table_locks_immediate立即獲得表鎖次數(shù)Table_locks_waited 等待表鎖釋放次數(shù)Table_locks_waited比例高的話要考慮分表用showprocesslist觀察鎖的情況

“Locked”是被鎖的線程，要關(guān)心其他運(yùn)行狀態(tài)的產(chǎn)生鎖的線程，例如正在“Sortingresult”、“Sendingdata”第34頁(yè)，共39頁(yè)。鎖的問題（2）讀-讀不會(huì)有鎖讀-寫，寫-寫原則上不可并發(fā)

特殊情況：insert+表中無(wú)刪除行通常寫操作優(yōu)先級(jí)高

通過啟動(dòng)參數(shù)--low-priority-updates改變結(jié)論：避免復(fù)雜耗時(shí)查詢

特別是寫操作多的表