More  

小編的世界 優質文選 資料

MySQL數據庫性能優化


2021年12月02日 - 資料小編  
   

創作者6kfLODVAzY06:11

1、表設計

1.1、數據庫表最大程度遵守三範式

第一範式:數據庫表中的字段都是單一屬性的,不可再分;

第二範式:在第一範式基礎上,除了主鍵以外的其它列都依賴於該主鍵;

第三範式:如果滿足第二範式,並且除了主鍵以外的其它列都不傳遞依賴於主鍵列。

1.2、數據表和字段設計的原則

字段的命名要有意義;

字段選擇的一般原則是保小不保大,能占用字節少的字段就不用大字段;

盡可能的使用 varchar/nvarchar 代替 char/nchar

盡量不使用 blob,還有將圖片以二進制存到數據庫

不用使用無法加索引的類型作為關鍵字段,比如text;

表中組合主鍵的字段個數越少越好,適當情況使用代理主鍵。

沒有冗餘的數據庫未必是最好的數據庫,適當的時候需降低範式標准;

1.3、根據應用場合選擇表的存儲引擎

數據表選擇合適的引擎

MyISAM 特點

數據存儲方式簡單,使用 B+ Tree 進行索引使用三個文件定義一個表:.MYI、.MYD、.frm;

少碎片、支持大文件、能夠進行索引壓縮;

訪問速度飛快,是所有MySQL文件引擎中速度最快的;

不支持一些數據庫特性,比如 事務、外鍵約束等;

表級鎖,性能稍差,更適合讀取多的操作(查詢和更新操作並行時,查詢操作需等待更新操作結束);

表數據容量有限,一般建議單表數據量介於50w–200w;

增刪查改以後要使用 myisamchk 檢查優化表

InnoDB 特點

使用 Table Space 的方式來進行數據存儲 (ibdata1, ib_logfile0);

支持事務、外鍵約束等數據庫特性;

行級鎖, 讀寫性能都非常優秀。(在默認“可重複讀”事務隔離下,查詢和更新操作並行時,查詢操作不需等待) ;

能夠承載大數據量的存儲和訪問;

擁有自己獨立的緩沖池,能夠緩存數據和索引;

在關閉自動提交的情況下,與MyISAM引擎速度差異不大

1.4、數據表的反範式化設計

降低範式標准至第二範式

ticketorderdetail 中字段inputTime、inputBy等完全可以通過orderId外鍵關聯 ticketorder表來獲取到相應信息。這麼設計:存在一定的冗餘,主要目的是部分場景時,可以提前篩選或直接取值,而不用join鏈接。

2、常用優化方法

1、查詢語句中不要使用 *

2、盡量減少子查詢,使用關聯查詢(left join,right join,inner join)替代

3、減少使用IN或者NOT IN ,使用exists,not exists或者關聯查詢語句替代

4、or 的查詢盡量用 union或者union all 代替 (在確認沒有重複數據或者不用剔除重複數據時,union all會更好)

5、表關聯時,關聯前盡量過濾數據量,可以減少關聯後的集合量級,提高查詢速度

6、空間換取時間。增加中間表進行優化(這個主要是在統計報表的場景,後台開定時任務將數據先統計好,盡量不要在查詢的時候去統計)

7、那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的最前面,(oracle則是末尾)

8、大量數據的更新操作,要盡量使用批量處理,而不能foreach一條一條處理。

9、分頁查詢優化。不需要查詢全部數據時,用分頁查詢返回。

10、分頁查詢優化的優化。在數據量非常大的情況下,分頁查詢時,limit的效率將會非常低。此時應該考慮優化查詢條件。(在Mongodb特別明顯)

11、結合執行計劃explain。

3、索引的創建和使用

3.1、什麼是索引?

3.2、創建索引的好處

幫助用戶提高查詢速度;

利用索引的唯一性來控制記錄的唯一性;

可以加速表與表之間的連接 ;

降低查詢中分組和排序的時間

3.3、創建索引的壞處

存儲索引占用磁盤空間;

執行數據修改操作(INSERT、UPDATE、DELETE)產生索引維護

索引創建的總體原則

在了解表的具體應用場景基礎上建立索引;

為所有主鍵和外鍵列建立索引;

對出現在WHERE子句、JOIN子句、ORDER BY或GROUP BY子句中的列考慮建立索引;

對需要確保唯一性的列考慮建立索引;

對於WHERE子句中用AND連接並頻繁使用的列使用組合索引,最頻繁的列放在最左邊;

數據更新頻繁的列不宜建立索引;

數據量較小的表也不宜建立索引

3.4、Explain中type類型

3.5.1、索引的正確使用(一)

去除查詢條件左端的任何標量函數或計算

3.5.2、索引的正確使用(二)

確保宿主變量定義與列數據類型匹配

3.5.3、索引的正確使用(三)

查詢條件中使用like時避免宿主變量以‘%’開頭

3.5.4、索引的正確使用(四)

避免使用“or” ,采用其它方式重寫

3.5.5、索引的正確使用(五)

使用組合索引時,應注意“最左前綴”基本原則

最左前綴:就是最左優先,我們創建了lname、fname和age的多列索引,相當於創建了lname單列索引,(lname,fname)的組合索引以及(lname,fname,age)組合索引;

SELECT `uid` FROM people WHERE `fname`=‘Zhiqun’ AND `age`=26上述查詢語句因違法“最左前綴”原則,系統通常會掃描整表以匹配數據!

4、EF框架查詢優化

4.1、避免使用Select *

一些必填的字符串,應該在實體類上加上

4.2、表關聯優化

  大家在看