網上關于sql優化的答案數不勝數,可是篇幅太長,看一下滾動條就沒有要看下去的欲望,這裡自己總結精簡了一下,全文1200字,閱讀需要5分鐘,還有很多不足之處望大佬多多指點~
字段設計
價格使用定點數decimal
小機關大數額避免出現小數
單表字段不宜過多(最多30)
盡可能使用 not null
關聯表的設計
一對多(使用外鍵)
多對多(單獨建立一張表将多對多拆分成兩個一對多)
一對一(如商品的基本資訊(item)和商品的詳細資訊(item_intro),通常使用相同的主鍵或者增加一個外鍵字段(item_id))
範式
第一範式1NF:字段原子性(字段原子性,字段不可再分割。)
第二範式:消除對主鍵的部分依賴(即在表中加上一個與業務邏輯無關的字段作為主鍵)
第三範式:消除對主鍵的傳遞依賴(傳遞依賴:B字段依賴于A,C字段又依賴于B。比如上例中,任課老師是誰取決于是什麼課,是什麼課又取決于主鍵id。)
存儲引擎選擇
MyISAM:以讀寫插入為主的應用程式,比如部落格系統、新聞門戶網站。
Innodb:更新(删除)操作頻率也高,或者要保證資料的完整性;并發量高,支援事務和外鍵保證資料完整性。比如OA自動化辦公系統。
索引
通過explain檢視适合加索引的字段
文法細節
字段要獨立出現
like查詢,不能以通配符開頭
複合索引隻對第一個字段有效
or,兩邊條件都有索引可用
查詢緩存
在配置檔案中開啟緩存
windows上是my.ini,linux上是my.cnf
在[mysqld]段中配置query_*_type:
0:不開啟
1:開啟,預設緩存所有,需要在SQL語句中增加select sql-no-*提示來放棄緩存
2:開啟,預設都不緩存,需要在SQL語句中增加select sql-*來主動緩存
更改配置後需要重新開機以使配置生效,重新開機後可通過show variables like ‘query_*_type’;來檢視
在用戶端設定緩存大小(通過配置項query_*_size來設定)
将查詢結果緩存
分區
一般情況下我們建立的表對應一組存儲檔案,
QQ賬号轉讓使用MyISAM存儲引擎時是一個.MYI和.MYD檔案,使用Innodb存儲引擎時是一個.ibd和.frm(表結構)檔案。
當資料量較大時(一般千萬條記錄級别以上),MySQL的性能就會開始下降,這時我們就需要将資料分散到多組存儲檔案,==保證其單個檔案的執行效率==。
最常見的分區方案是按id分區,如下将id的哈希值對10取模将資料均勻分散到10個.ibd存儲檔案中:
create table article(
id int auto_increment PRIMARY KEY,
title varchar(64),
content text
)PARTITION by HASH(id) PARTITIONS 10
水準分割和垂直分割
水準分割:通過建立結構相同的幾張表分别存儲資料
垂直分割:将經常一起使用的字段放在一個單獨的表中,分割後的表記錄之間是一一對應關系。
叢集
讀寫分離
負載均衡
典型SQL
select * 要少用
order by rand()不要用
使用exist 替代in
字段類型轉換導緻不用索引,如字元串類型的不用引号,數字類型的用引号等,這有可能會用不到索引導緻全表掃描;
mysql 不支援函數轉換,是以字段前面不能加函數,否則這将用不到索引
不要在字段前面加減運算
字元串比較長的可以考慮索引一部份減少索引檔案大小,提高寫入效率
like % 在前面用不到索引
根據聯合索引的第二個及以後的字段單獨查詢用不到索引
排序請盡量使用升序
or 的查詢盡量用 union 代替(Innodb)
複合索引高選擇性的字段排在前面
order by / groupby 字段包括在索引當中減少排序,效率會更高