文章目錄
- 資料庫基本設計規範
- 字段優化
- 索引優化
- 查詢優化
- 阿裡規約
-
- 1、建表規約
- 索引規約
- SQL語句
- ORM映射
資料庫基本設計規範
所有表必須使用 Innodb 存儲引擎
資料庫和表的字元集統一使用 UTF8
所有表和字段都需要添加注釋
盡量控制單表資料量的大小,建議控制在 500 萬以内
謹慎使用 MySQL 分區表
禁止在表中建立預留字段
禁止在資料庫中存儲圖檔,檔案等大的二進制資料
禁止線上上做資料庫壓力測試
禁止從開發環境,測試環境直接連接配接生産環境資料庫
原文
字段優化
優先選擇符合存儲需要的
最小
的資料類型
因為列的字段越大，建立索引時所需要的空間也就越大，這樣一頁中所能存儲的索引節點的數量也就越少，在周遊時所需要的 IO 次數也就越多，索引的性能也就越差。
例如：盡量使用
TINYINT
作為整數類型而非INT，如果非負則加上
UNSIGNED
。
簡單的資料類型優先
簡單資料類型的操作通常需要更少的CPU周期。
例如：
1、整型比字元操作代價更低， 因為
字元集和排序規則
使字元比較比整型比較更複雜。
2、應該用整型而不是字元串存儲IP位址。
用
VARCHAR
不用
char
，
VARCHAR
的長度隻配置設定真正需要的空間。
使用
TIMESTAMP
和
DATETIME
而不是
字元串
存時間類型，TIMESTAMP又優于DATETIME，TIMESTAMP存不下才用DATETIME
避免使用NULL字段，因為很難查詢優化且占用額外索引空間
避免使用
TEXT,BLOB
資料類型
避免使用
ENUM
類型
同财務相關的金額類資料
必須使用 decimal 類型
單表不要有太多字段，建議在20以内
索引優化
添加連結描述
查詢優化
避免查詢中篩選大量資料，可以通過limit限制；
避免
select * from table
這種全表掃描的語句
如果業務沒有特殊規定資料，那麼就盡量避免使用
UNION
，可以考慮使用
UNION-ALL
替換UNION，因為後者不會過濾重複資料，效率高于UNION
可通過
開啟慢查詢日志
來找出較慢的SQL
不做列運算
例如：SELECT id WHERE
age + 1
= 10
任何對列的操作都将導緻
表掃描
，它包括資料庫教程函數、計算表達式等等，查詢時要盡可能将操作移至
等号右邊
拆分大事物
一個事物隻能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫
避免
%xxx
式查詢
使用同類型進行比較，比如用’123’和’456’比，123和456比，而不要用‘123’和456比。
盡量避免在WHERE子句中使用
!=或<>
操作符，否則會放棄使用索引而全表掃描
用in不用or
OR的效率是n級别，IN的效率是log(n)級别
如果有索引或者主鍵，性能沒啥差别
但是如果沒有，in的性能高于or
對于連續數值，
使用BETWEEN不用IN
SELECT id FROM t WHERE num
BETWEEN 1 AND 5
清單資料不要拿全表，要使用
LIMIT
來分頁，每頁數量也不要太大
阿裡規約
1、建表規約
資料庫字段名的修改代價很大，設計字段需要慎重考慮。
- 【強制】表達
是與否
is_xxx
unsigned tinyint
（ 1 表示是，0 表示否）。
說明：任何字段如果為非負數，必須是 unsigned。
-
【強制】表名、字段名必須使用小寫字母或數字，禁止出現數字開頭，禁止兩個下劃線中間隻出現數字。
正例：getter_admin，task_config，level3_name
反例：GetterAdmin，taskConfig，level_3_name
-
【強制】表名不使用複數名詞。
說明：表名應該僅僅表示表裡面的實體内容，不應該表示實體數量，對應于 DO 類名也是單數形式，符合表達習慣。
- 【強制】禁用保留字，如 desc、range、match、delayed 等，請參考 MySQL 官方保留字。
- 【強制】主鍵索引名為
pk_字段名
uk_字段名
idx_字段名
- 【強制】小數類型為
decimal
，禁止使用 float 和double。
說明：float 和 double 在存儲的時候，存在
精度損失
整數和小數
- 【強制】如果存儲的字元串長度幾乎相等，使用 char 定長字元串類型。
- 【強制】varchar 是可變長字元串，不預先配置設定存儲空間，長度不要超過 5000，如果存儲長度超過varchar，定義字段類型為 text，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。
-
【強制】表必備三字段：id, gmt_create(create_time), gmt_modified(update_time)。
gmt_modified 的類型均為 date_time 類型。
- 【推薦】表的命名最好是加上
“業務名稱_表的作用”
。
正例：tiger_task / tiger_reader / mpp_config
- 【推薦】庫名與應用名稱盡量一緻。
- 【推薦】如果修改字段含義或對字段表示的狀态追加時，需要及時
更新字段注釋
-
【推薦】字段允許适當備援，以提高性能，但是必須考慮資料同步的情況。備援字段應遵循：
1） 不是頻繁修改的字段。
2） 不是 varchar 超長字段，更不能是 text 字段。
正例：商品類目名稱使用頻率高，字段長度短，名稱基本一成不變，可在相關聯的表中備援存儲類目名稱，避免聯合查詢。
-
【推薦】單表行數超過 500 萬行或者單表容量超過 2GB，才推薦進行分庫分表。
說明：如果預計三年後的資料量根本達不到這個級别，請不要在建立表時就分庫分表。
-
【參考】合适的字元存儲長度，不但節約資料庫表空間、節約索引存儲，更重要的是提升檢索速度。
正例：如下表，其中無符号值可以避免誤存負數，且擴大了表示範圍。
mysql-資料庫、字段、索引、查詢的優化資料庫基本設計規範字段優化索引優化查詢優化阿裡規約
索引規約
1.【強制】業務上具有
唯一特性
的字段，即使是多個字段的組合，也必須建成
唯一索引
。
說明：不要以為唯一索引影響了insert速度，這個速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應用層做了非常完善的校驗控制，隻要沒有唯一索引，根據墨菲定律，必然有
髒資料
産生。
2.【強制】超過三個表禁止join。需要join的字段，資料類型必須絕對一緻；多表關聯查詢時，保證被關聯的字段需要有索引。
說明：即使雙表join也要注意表索引、SQL性能。
3.【強制】在varchar字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。
說明：索引的長度與區分度是一對沖突體，一般對字元串類型資料，長度為20的索引，區分度會高達90%以上。
4.【強制】頁面搜尋嚴禁左模糊或者全模糊，如果需要請走
搜尋引擎
來解決。
說明：索引檔案具有B-Tree的
最左字首比對特性
，如果左邊的值未确定，那麼無法使用此索引。
5.【推薦】如果有order by的場景，請注意利用索引的有序性。orderby最後的字段是組合索引的一部分，并且放在索引組合順序的最後，避免出現file_sort的情況，影響查詢性能。
正例：where a=? and b=? order by c;索引：
a_b_c
反例：索引中有範圍查找，那麼索引有序性無法利用，如：WHERE a>10 ORDERBY b;索引a_b無法排序。
6.【推薦】利用
覆寫索引
來進行查詢操作，
避免回表
。
7.【推薦】利用
延遲關聯或者子查詢
優化超多分頁場景。
說明：MySQL并不是跳過offset行，而是取offset+N行，然後傳回放棄前offset行，傳回N行，那當offset特别大的時候，效率就非常的低下，要麼控制傳回的總頁數，要麼對超過特定門檻值的頁數進行SQL改寫。
正例：先快速定位需要擷取的id段，然後再關聯：
SELECT a.* FROM 表1 a, (select id from 表1 where 條件LIMIT 100000,20 ) b where a.id=b.id
8.【推薦】SQL性能優化的目标：至少要達到
range
級别，要求是
ref
級别，如果可以是
consts
最好。
9.【推薦】建組合索引的時候，區分度最高的在最左邊。
正例：如果where a=? and b=? ，a列的幾乎接近于唯一值，那麼隻需要單建idx_a索引即可。
說明：存在
非等号和等号
混合判斷條件時，在建索引時，請把
等号條件的列前置
。
如：
where a>? and b=?
那麼即使a的區分度更高，也
必須把b放在索引的最前列
。
10.【推薦】防止因字段類型不同造成的
隐式轉換
，導緻索引失效。
SQL語句
1.【強制】不要使用
count(列名)或count(常量)
來替代
count(*)
，
count(*)
是SQL92定義的标準統計行數的文法，跟資料庫無關，跟NULL和非NULL無關。
說明：count(*)會統計值為NULL的行，而count(列名)不會統計此列為NULL值的行。
2.【強制】
count(distinct col)
計算該列除NULL之外的不重複行數，注意
count(distinct col1, col2)
如果其中一列全為NULL，那麼即使另一列有不同的值，也傳回為0。
3.【強制】當某一列的值全是NULL時，count(col)的傳回結果為0，但sum(col)的傳回結果為NULL，是以使用sum()時需注意NPE(NullPointerException)問題。正例：可以使用如下方式來避免sum的NPE問題：
4.【強制】使用ISNULL()來判斷是否為NULL值。
說明：
NULL與任何值的直接比較都為NULL
。
1）NULL<>NULL的傳回結果是NULL，而不是false。
2）NULL=NULL的傳回結果是NULL，而不是true。
3）NULL<>1的傳回結果是NULL，而不是true。
5.【強制】在代碼中寫分頁查詢邏輯時，若count為0應直接傳回，避免執行後面的分頁語句。
6.【強制】不得使用
外鍵與級聯
，一切外鍵概念必須在應用層解決。
說明：以學生和成績的關系為例，學生表中的student_id是主鍵，那麼成績表中的student_id則為外鍵。如果更新學生表中的student_id，同時觸發成績表中的student_id更新，即為
級聯更新
。外鍵與級聯更新适用于單機低并發，不适合分布式、高并發叢集；級聯更新是
強阻塞
，存在資料庫更新風暴的風險；外鍵影響資料庫的插入速度。
7.【強制】禁止使用存儲過程，存儲過程難以調試和擴充，更沒有移植性。
8.【強制】資料訂正時，删除和修改記錄時，要先select，避免出現誤删除，确認無誤才能執行更新語句。
9.【推薦】in操作能避免則避免，若實在避免不了，需要仔細評估in後邊的集合元素數量，控制在1000個之内。
10.【參考】如果有全球化需要，所有的字元存儲與表示，均以
utf-8
編碼，注意字元統計函數的差別。說明：
SELECT LENGTH("輕松工作")；傳回為12
SELECTCHARACTER_LENGTH("輕松工作")；傳回為4
如果需要存儲表情，那麼選擇
utfmb4
來進行存儲，注意它與utf-8編碼的差別。
11.【參考】不建議使用TRUNCATE TABLE
TRUNCATE TABLE 比DELETE速度快，且使用的系統和事務日志資源少，但TRUNCATE無事務且不觸發
trigger
，有可能造成事故。
ORM映射
1.【強制】在表查詢中，一律不要使用
*
作為查詢的字段清單，需要哪些字段必須明确寫明。
說明：1）增加查詢分析器解析成本。2）增減字段容易與resultMap配置不一緻。
2.【強制】POJO類的布爾屬性
不能加is
，而資料庫字段必須加
is_
，要求在resultMap中進行字段與屬性之間的映射。
說明：參見定義POJO類以及資料庫字段定義規定，在resultMap中增加映射，是必須的。在
MyBatis Generator
生成的代碼中，需要進行對應的修改。
3.【強制】不要用resultClass當傳回參數，即使所有類屬性名與資料庫字段一一對應，也需要定義；反過來，每一個表也必然有一個與之對應。說明：配置映射關系，使字段與DO類解耦，友善維護。
4.【強制】sql.xml配置參數使用
#{}
，不要使用
${}
此種方式容易出現
SQL注入
。
5.【強制】
iBATIS
自帶的
queryForList(StringstatementName,intstart,intsize)
不推薦使用。說明：其實作方式是在資料庫取到statementName對應的SQL語句的所有記錄，再通過subList取start,size的子集合。正例：Map<String, Object> map = new HashMap<String, Object>(); map.put(“start”, start); map.put(“size”, size);
6.【強制】不允許直接拿HashMap作為查詢結果集的輸出。說明：resultClass=”Hashtable”，會置入字段名和屬性值，但是值的類型不可控。
7.【強制】更新資料表記錄時，必須同時更新記錄對應的
gmt_modified
字段值為目前時間。
8.【推薦】執行SQL時，不要更新無改動的字段，
一是易出錯；二是效率低；三是增加binlog存儲
。
9.【參考】
@Transactional
事務不要濫用。
事務會影響資料庫的QPS，另外使用事務的地方需要考慮各方面的
復原方案
，包括緩存復原、搜尋引擎復原、消息補償、統計修正等。
10.【參考】
<isEqual>
中的compareValue是與屬性值對比的常量，一般是數字，表示相等時帶上此條件；表示不為空且不為null時執行；
<isNotNull>
表示不為null值時執行。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)