優化思路
詳細的mysql優化步驟如下:
盡可能不要使用NULL值
因為建表的時候,如果不對創建的值設置默認值,mysql都會設置默認為NULL。那么為啥用NULL不好呢?
- NULL使得索引維護更加復雜,強烈建議對索引列設置NOT NULL
- NOT IN、!=等負向條件查詢在有NULL值的情況下返回永遠為空結果,查詢容易出錯
- NULL列需要一個額外字節作為判斷是否為NULL的標志位
- 使用NULL時和該列其他的值可能不是同種類型,導致問題。(在不同的語言中表現不一樣)
- MySQL難以優化對可為NULL的列的查詢
所以對于那些以前偷懶的字段,手動設置一個默認值吧,空字符串呀,0呀補上。
雖然這種方法對于MySQL的性能來說沒有提升多少,但是這是一個好習慣,而且以小見大,不要忽略這些細節。
添加索引
對于經常查詢的字段,請加上索引,有索引和沒有索引的查詢速度相差十倍甚至更多。
- 一般來說,每張表都需要有一個主鍵id字段
- 常用于查詢的字段應該設置索引
- varchar類型的字段,在建立索引的時候,最好指定長度
- 查詢有多個條件時,優先使用具有索引的條件
- 像LIKE條件這樣的模糊搜索對于字段索引是無效的,需要另外建立關鍵詞索引來解決
- 請盡量不要在數據庫層面約束表和表之間的關系,這些表之間的依賴應該在代碼層面去解決
當表和表之間有約束時,雖然增刪查的SQL語句變簡單了,但是帶來的負面效果是插入等操作數據庫都會去檢查約束(雖然可以手動設置忽略約束),這樣相當于把一些業務邏輯寫到了數據庫層,不便于維護。
優化表字段結構
數據庫中那些可以用整形表示的數據就不要使用字符串類型,到底是用varchar還是char要看字段的可能值。
這種優化往往在數據庫中有大量數據以后是不可行的,最好在數據庫設計之前就設計好。
- 對于那些可能值很有限的列,使用tinyint代替VARCHAR,
- 對于那些定長字符串,可以使用char,比如郵編,總是5位
- 對于那些長度未知的字符串,使用varchar
- 不要濫用bigint,比如記錄文章數目的表id字段,用int就行了,21億篇文章上限夠了
- 適當打破數據庫范式添加冗余字段,避免查詢時的表連接
查詢的時候,肯定int類型比varchar快,因為整數的比較直接調用底層運算器就可以實現,而字符串比較要逐個字符比較。
定長數據比變長數據查詢快,因為比較定長數據與數據之間的偏移是固定的,很容易計算下一個數據的偏移。而變長數據則還需要多一步去查詢下一個數據的偏移量。不過。定長數據可能會浪費更多的存儲空間。
大表拆分
對于那些數據量可能近期會超過500W或者增長很快的表,一定要提前做好垂直分表或者水平分表,當數據量超過百萬以后,查詢速度會明顯下降。
分庫分表盡量在數據庫設計初期敲定方案,否則后期會極大增加代碼復雜性而且不易更改。
垂直分表是按照日期等外部變量進行分表,水平分表是按照表中的某些字段關系,使用hash映射等分表。
分庫分表的前提條件是在執行查詢語句之前,已經知道需要查詢的數據可能會落在哪一個分庫和哪一個分表中。
優化查詢語句
這個才是很多系統數據庫瓶頸的始作俑者。
- 請盡量使用簡單的查詢,避免使用表鏈接
- 請盡量避免全表掃描,會造成全表掃描的語句包括但不限于:
- where子句條件恒真或為空
- 使用LIKE
- 使用不等操作符(、!=)
- 查詢含有is null的列
- 在非索引列上使用or
- 多條件查詢時,請把簡單查詢條件或者索引列查詢置于前面
- 請盡量指定需要查詢的列,不要偷懶使用select *
- 如果不指定,一方面會返回多余的數據,占用帶寬等
- 另一方面MySQL執行查詢的時候,沒有字段時會先去查詢表結構有哪些字段
- 大寫的查詢關鍵字比小寫快一點點
- 使用子查詢會創建臨時表,會比鏈接(JOIN)和聯合(union)稍慢
- 在索引字段上查詢盡量不要使用數據庫函數,不便于緩存查詢結果
- 當只要一行數據時,請使用LIMIT 1,如果數據過多,請適當設定LIMIT,分頁查詢
- 千萬不要 ORDER BY RAND(),性能極低
添加緩存
使用redis等緩存,還有本地文件緩存等,可以極大地減少數據庫查詢次數。緩存這個東西,一定要分析自己系統的數據特點,適當選擇。
- 對于一些常用的數據,比如配置信息等,可以放在緩存中
- 可以在本地緩存數據庫的表結構
- 緩存的數據一定要注意及時更新,還有設置有效期
- 增加緩存務必會增加系統復雜性,一定要注意權衡
檢查數據表結構
推薦學習:《mysql視頻教程》