mysql 5.7單表300萬數(shù)據(jù),性能嚴(yán)重下降,如何破?
問題描述
環(huán)境:DB: mysql 5.7.xxOS: windows server 2012 r2CPU: E3 1220-V5內(nèi)存: 4G。
數(shù)據(jù)庫配置(基本上是默認(rèn)配置):join_buffer_size = 128Msort_buffer_size = 2Mread_rnd_buffer_size = 2M innodb_buffer_pool_size = 128M
表現(xiàn):
有個(gè)表service_log,其中有ID, DIAL_NUMBER, contact_name, contact_result, remark, CREATE_TIME等20多個(gè)常規(guī)字段。ID是PK,在contact_name,create_time等列上建有單獨(dú)索引。
此表每日產(chǎn)生的新數(shù)據(jù)大概在1萬左右,目前有數(shù)據(jù)近300萬。
有一個(gè)查詢,查詢字段較多:select id, dial_number, contact_name ....from service_logwhere create_time between ’2016-10-01’ and ’2016-10-02’
從300萬數(shù)據(jù)中,查詢出近8000條數(shù)據(jù),耗時(shí)大概在40秒左右。查看執(zhí)行計(jì)劃,已經(jīng)用了create_time上的索引。
顯然這個(gè)效率很難接受,但是索引已經(jīng)用上,實(shí)在想不出其他辦法了。
請(qǐng)問除了分區(qū),還有什么好辦法嗎?
問題解答
回答1:innodb_buffer_pool_size 這個(gè)太小了改成2G先試試,磁盤和內(nèi)存的性能差了幾十倍,128M讓你的數(shù)據(jù)操作對(duì)磁盤進(jìn)行了IO, innodb_buffer_pool_size這個(gè)加大應(yīng)該就好好多了這個(gè)和索引已經(jīng)沒有關(guān)系了,是磁盤io造成的問題,你看看任務(wù)管理器的磁盤性能,隨著buffer加大,磁盤負(fù)載會(huì)下降,這個(gè)就和系統(tǒng)內(nèi)存小會(huì)卡一樣,給mysql的內(nèi)存小也會(huì)卡。別的也會(huì)造成卡,應(yīng)該沒有innodb_buffer_pool_size這個(gè)明顯回答2:
一次性取到太大的結(jié)果集不一定就是好。除了加大對(duì)應(yīng)的配置參數(shù)之外,可以考慮把一條sql查分為N條sql,這個(gè)可以具體的自己測(cè)試,比如每次取三個(gè)小時(shí)四個(gè)小時(shí)這樣試試看得到一個(gè)平衡。
回答3:并發(fā)高嗎,重復(fù)查詢的概率高不高,可以嘗試打開或者關(guān)閉查詢緩存。
回答4:你要看時(shí)間花在哪里,是查找上,還是傳輸上。。。查找的話就說明優(yōu)化沒做好
回答5:建立組合索引,最頻繁使用的列放在左邊;查看列的選擇性(即該列的索引值數(shù)量與記錄數(shù)量的比值),比值越高,效果越好。
回答6:感謝各位回答,后來發(fā)現(xiàn)這個(gè)問題的癥結(jié)在哪兒了,時(shí)間耗在回表操作了。select id, dial_number, contact_name ....from service_logwhere create_time between ’2016-10-01’ and ’2016-10-02’這個(gè)查詢,如果改成這樣:select idfrom service_logwhere create_time between ’2016-10-01’ and ’2016-10-02’速度飛快,因?yàn)镾ELECT 后面只有一個(gè)主鍵PK,直接在索引里就能確定每個(gè)滿足條件的記錄的ID了。而如果SELECT后面還有其他字段,則需要根據(jù)PK去表里找出相應(yīng)記錄,找出其他字段的值,這就是回表操作了,這個(gè)過程很慢。這個(gè)應(yīng)該是屬于IO問題吧?
