程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql 單機數據庫優化的一些實踐,mysql單機

mysql 單機數據庫優化的一些實踐,mysql單機

編輯:MySQL綜合教程

mysql 單機數據庫優化的一些實踐,mysql單機


數據庫優化有很多可以講,按照支撐的數據量來分可以分為兩個階段:單機數據庫和分庫分表,前者一般可以支撐500W或者10G以內的數據,超過這個值則需要考慮分庫分表。另外,一般大企業面試往往會從單機數據庫問起,一步一步問到分庫分表,中間會穿插很多數據庫優化的問題。本文試圖描述單機數據庫優化的一些實踐,數據庫基於mysql,如有不合理的地方,歡迎指正。

1、表結構優化

在開始做一個應用的時候,數據庫的表結構設計往往會影響應用後期的性能,特別是用戶量上來了以後的性能。因此,表結構優化是一個很重要的步驟。

1.1、字符集

一般來說盡量選擇UTF-8,雖然在存中午的時候GBK比UTF-8使用的存儲空間少,但是UTF-8兼容各國語言,其實我們不必為了這點存儲空間而犧牲了擴展性。事實上,後期如果要從GBK轉為UTF-8所要付出的代價是很高的,需要進行數據遷移,而存儲空間完全可以用花錢擴充硬盤來解決。

1.2、主鍵

在使用mysql的innodb的時候,innodb的底層存儲模型是B+樹,它使用主鍵作為聚簇索引,使用插入的數據作為葉子節點,通過主鍵可以很快找到葉子節點,從而快速獲取記錄。因此在設計表的時候需要增加一個主鍵,而且最好要自增。因為自增主鍵可以讓插入的數據按主鍵順序插入到底層的B+樹的葉子節點中,由於是按序的,這種插入幾乎不需要去移動已有的其它數據,所以插入效率很高。如果主鍵不是自增的,那麼每次主鍵的值近似隨機,這時候就有可能需要移動大量數據來保證B+樹的特性,增加了不必要的開銷。

1.3、字段

1.3.1、建了索引的字段必須加上not null約束,並且設置default值

1.3.2、不建議使用float、double來存小數,防止精度損失,建議使用decimal

1.3.3、不建議使用Text/blob來保存大量數據,因為對大文本的讀寫會造成比較大的I/O開銷,同時占用mysql的緩存,高並發下會極大的降低數據庫的吞吐量,建議將大文本數據保存在專門的文件存儲系統中,mysql中只保存這個文件的訪問地址,比如博客文章可以保存在文件中,mysql中只保存文件的相對地址。

1.3.4、varchar類型長度建議不要超過8K。

1.3.5、時間類型建議使用Datetime,不要使用timestamp,雖然Datetime占用8個字節,而timestamp只占用4個字節,但是後者要保證非空,而且後者是對時區敏感的。

1.3.6、建議表中增加gmt_create和gmt_modified兩個字段,用來記錄數據創建的修改時間。這兩個字段建立的原因是方便查問題。

1.4、索引創建

1.4.1、這個階段由於對業務並不了解,所以盡量不要盲目加索引,只為一些一定會用到索引的字段加普通索引。

1.4.2、創建innodb單列索引的長度不要超過767bytes,如果超過會用前255bytes作為前綴索引

1.4.3、創建innodb組合索引的各列索引長度不要超過767bytes,一共加起來不要超過3072bytes

2、SQL優化

一般來說sql就那麼幾種:基本的增刪改查,分頁查詢,范圍查詢,模糊搜索,多表連接

2.1、基本查詢

一般查詢需要走索引,如果沒有索引建議修改查詢,把有索引的那個字段加上,如果由於業務場景沒法使用這個字段,那麼需要看這個查詢調用量大不大,如果大,比如每天調用10W+,這就需要新增索引,如果不大,比如每天調用100+,則可以考慮保持原樣。另外,select * 盡量少用,用到什麼字段就在sql語句中加什麼,不必要的字段就別查了,浪費I/O和內存空間。

2.2、高效分頁

limit m,n其實質就是先執行limit m+n,然後從第m行取n行,這樣當limit翻頁越往後翻m越大,性能越低。比如

select * from A limit 100000,10,這種sql語句的性能是很差的,建議改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3、范圍查詢

范圍查詢包括between、大於、小於以及in。Mysql中的in查詢的條件有數量的限制,若數量較小可以走索引查詢,若數量較大,就成了全表掃描了。而between、大於、小於等,這些查詢不會走索引,所以盡量放在走索引的查詢條件之後。

2.4、模糊查詢like

使用 like %name%這樣的語句是不會走索引的,相當於全表掃描,數據量小的時候不會有太大的問題,數據量大了以後性能會下降的很厲害,建議數據量大了以後使用搜索引擎來代替這種模糊搜索,實在不行也要在模糊查詢前加個能走索引的條件。

2.5、多表連接

子查詢和join都可以實現在多張表之間取數據,但是子查詢性能較差,建議將子查詢改成join。對於mysql的join,它用的是Nested Loop Join算法,也就是通過前一個表查詢的結果集去後一個表中查詢,比如前一個表的結果集是100條數據,後一個表有10W數據,那麼就需要在100*10W的數據集合中去過濾得到最終的結果集。因此,盡量用小結果集的表去和大表做join,同時在join的字段上建立索引,如果建不了索引,就需要設置足夠大的join buffer size。如果以上的技巧都無法解決join所帶來的性能下降的問題,那干脆就別用join了,將一次join查詢拆分成兩次簡單查詢。另外,多表連接盡量不要超過三張表,超過三張表一般來說性能會很差,建議拆分sql。

3、數據庫連接池優化

數據庫連接池本質上是一種緩存,它是一種抗高並發的手段。數據庫連接池優化主要是對參數進行優化,一般我們使用DBCP連接池,它的具體參數如下:

3.1  initialSize

初始連接數,這裡的初始指的是第一次getConnection的時候,而不是應用啟動的時候。初始值可以設置為並發量的歷史平均值

3.2、minIdle

最小保留的空閒連接數。DBCP會在後台開啟一個回收空閒連接的線程,當該線程進行空閒連接回收的時候,會保留minIdle個連接數。一般設置為5,並發量實在很小可以設置為1.

3.3、maxIdle

最大保留的空閒連接數,按照業務並發高峰設置。比如並發高峰為20,那麼當高峰過去後,這些連接不會馬上被回收,如果過一小段時間又來一個高峰,那麼連接池就可以復用這些空閒連接而不需要頻繁創建和關閉連接。

3.4、maxActive

最大活躍連接數,按照可以接受的並發極值設置。比如單機並發量可接受的極值是100,那麼這個maxActive設置成100後,就只能同時為100個請求服務,多余的請求會在最大等待時間之後被拋棄。這個值必須設置,可以防止惡意的並發攻擊,保護數據庫。

3.5、maxWait

獲取連接的最大等待時間,建議設置的短一點,比如3s,這樣可以讓請求快速失敗,因為一個請求在等待獲取連接的時候,線程是不可以被釋放的,而單機的線程並發量是有限的,如果這個時間設置的過長,比如網上建議的60s,那麼這個線程在這60s內是無法被釋放的,只要這種請求一多,應用的可用線程就少了,服務就變得不可用了。

3.6、minEvictableIdleTimeMillis

連接保持空閒而不被回收的時間,默認30分鐘。

3.7、validationQuery

用於檢測連接是否有效的sql語句,一般是一條簡單的sql,建議設置

3.8、testOnBorrow

申請連接的時候對連接進行檢測,不建議開啟,嚴重影響性能

3.9、testOnReturn

歸還連接的時候對連接進行檢測,不建議開啟,嚴重影響性能

3.10、testWhileIdle

開啟了以後,後台清理連接的線程會沒隔一段時間對空閒連接進行validateObject,如果連接失效則會進行清除,不影響性能,建議開啟

3.11、numTestsPerEvictionRun

代表每次檢查鏈接的數量,建議設置和maxActive一樣大,這樣每次可以有效檢查所有的鏈接。

3.12、預熱連接池

對於連接池,建議在啟動應用的時候進行預熱,在還未對外提供訪問之前進行簡單的sql查詢,讓連接池充滿必要的連接數。

4、索引優化

當數據量增加到一定程度後,靠sql優化已經無法提升性能了,這時候就需要祭出大招:索引。索引有三級,一般來說掌握這三級就足夠了,另外,對於建立索引的字段,需要考慮其選擇性。

4.1、一級索引

在where後面的條件上建立索引,單列可以建立普通索引,多列則建立組合索引。組合索引需要注意最左前綴原則。

4.2、二級索引

如果有被order by或者group by用到的字段,則可以考慮在這個字段上建索引,這樣一來,由於索引天然有序,可以避免order by以及group by所帶來的排序,從而提高性能。

4.3、三級索引

如果上面兩招還不行,那麼就把所查詢的字段也加上索引,這時候就形成了所謂的索引覆蓋,這樣做可以減少一次I/O操作,因為mysql在查詢數據的時候,是先查主鍵索引,然後根據主鍵索引去查普通索引,然後根據普通索引去查相對應的記錄。如果我們所需要的記錄在普通索引裡都有,那就不需要第三步了。當然,這種建索引的方式比較極端,不適合一般場景。

4.4、索引的選擇性

在建立索引的時候,盡量在選擇性高的字段上建立。什麼是選擇性高呢?所謂選擇性高就是通過這個字段查出來的數據量少,比如按照名字查一個人的信息,查出來的數據量一般會很少,而按照性別查則可能會把數據庫一半的數據都查出來,所以,名字是一個選擇性高的字段,而性別是個選擇性低的字段。

5、歷史數據歸檔

當數據量到了一年增加500W條的時候,索引也無能為力,這時候一般的思路都是考慮分庫分表。如果業務沒有爆發式增長,但是數據的確在緩慢增加,則可以不考慮分庫分表這種復雜的技術手段,而是進行歷史數據歸檔。我們針對生命周期已經完結的歷史數據,比如6個月之前的數據,進行歸檔。我們可以使用quartz的調度任務在凌晨定時將6個月之前的數據查出來,然後存入遠程的hbase服務器。當然,我們也需要提供歷史數據的查詢接口,以備不時之需。

以上就是對mysql 單機數據庫的優化資料整理,後續繼續補充相關資料,謝謝大家對本站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved