搜尋

首頁 >資料庫 >mysql教程 >史上最全MySQL使用規範分享

史上最全MySQL使用規範分享

coldplay.xixi
coldplay.xixi轉載
2020-08-21 17:15:502288瀏覽

史上最全MySQL使用規範分享

【相關學習推薦：mysql教學】

#最近涉及資料庫相關作業較多，公司現有規範也不是太全面，就根據網路上各路大神的相關規範，整理了一些自用的規範用法，萬望指正。

資料庫環境

dev: 開發環境

開發可讀寫，可修改表結構。開發人員可以修改表結構，可以隨意修改其中的資料但是需要保證不影響其他開發同事。

test: 測試環境

開發可讀寫，開發人員可以透過工具修改表格結構。

online: 線上環境

開發人員不允許直接在線上環境進行資料庫操作，如果需要操作必須找DBA進行操作並進行對應記錄，禁止進行壓力測試。

重點的問題，各個環境的mysql伺服器對應的使用者權限，一定要做到權限劃分明確，有辨識度，能具體區分業務場景等。

命名規格

基本命名規則

  • 使用有意義的英文詞彙，詞彙中間以下劃線分隔。 （不要用拼音）
  • 只能使用英文字母，數字，底線，並以英文字母開頭。
  • 庫、表格、欄位全部採用小寫，不要使用駝峰式命名。
  • 避免用ORACLE、MySQL的保留字，如desc，關鍵字如index。
  • 命名禁止超過32個字符，須見名之意，建議使用名詞不是動詞
  • 資料庫，資料表一律使用前綴
  • 臨時庫、表名必須以tmp為前綴，並以日期為後綴
  • 備份庫、表必須以bak為前綴，並以日期為後綴

為什麼庫、表、字段全部採用小寫？

在 MySQL 中，資料庫和表格對就於那些目錄下的目錄和檔案。因而，作業系統的敏感度決定資料庫和表命名的大小寫敏感。

  • Windows下方是不區分大小寫的。
  • Linux下大小寫規則
  • 資料庫名稱與表名是嚴格區分大小寫的；
  • 表的別名是嚴格區分大小寫的；
  • 列名與列的別名在所有的情況下均是忽略大小寫的；
  • 變數名也是嚴格區分大小寫的；
  • 如果已經設定了駝峰式的命名如何解決？需要在MySQL的設定檔my.ini中增加 lower_case_table_names = 1即可。

表命名

同一個模組的表盡可能使用相同的前綴，表名稱盡可能表達意義。所有日誌表均以 log_ 開頭

欄位命名

  • #表達其實際意義的英文單字或簡寫。布林意義的字段以is_作為前綴，後接動詞過去分詞。
  • 各表之間相同意義的欄位應同名。各表之間相同意義的字段，以去掉模組前綴的表名_字段名命名。
  • 外鍵欄位以表名_欄位名表示其關聯關係。
  • 表的主鍵一般都約定成為id，自增類型，是別的表的外鍵均使用xxx_id的方式來表示。

索引命名

  • 非唯一索引必須依照「idx_欄位名稱_欄位名稱[_欄位名稱]」來命名
  • 唯一索引必須依照「uniq_欄位名稱_欄位名稱[_欄位名稱]」進行命名

約束命名

  • #主鍵約束：pk_表名稱。
  • 唯一約束：uk_表格名稱_欄位名稱。 （應用程式中需要同時有唯一性檢查邏輯。）

表格設計規格

##表引擎取決於實際應用場景；日誌及報表類表建議用myisam，與交易，審核，金額相關的表建議用innodb引擎。如無說明，建表時一律採用innodb引擎

默認使用utf8mb4字元集，資料庫排序規則使用utf8mb4_general_ci，（由於資料庫定義使用了默認，資料表可以不再定義，但為保險起見，建議都寫上

為什麼字元集不選擇utf8，排序規則不使用utf8_general_ci

採用utf8編碼的MySQL無法儲存佔位是4個位元組的Emoji表情。為了讓後端的項目，全面支援客戶端輸入的Emoji表情，升級編碼為utf8mb4是最佳解決方案。對於JDBC連接串設定了characterEncoding為utf8或做了上述配置仍舊無法正常插入emoji資料的情況，需要在程式碼中指定連接的字元集為utf8mb4。

所有表、欄位均套用comment 列屬性來描述此表、欄位所代表的真正意義，如枚舉值則建議將該欄位中所使用的內容都定義出來。

如無說明，表中的第一個id欄位一定是主鍵且為自動成長，禁止在非交易內作為上下文作為條件進行資料傳遞。禁止使用varchar類型作為主鍵語句設計。

如無說明，表必須包含create_time和modify_time字段，即表必須包含記錄創建時間和修改時間的字段

如無說明，表必須包含is_del，用來標示資料是否被刪除，原則上資料庫資料不允許物理刪除。

  • 用盡量少的儲存空間來存數一個欄位的資料
  • 能用int的就不用char或varchar
  • 能用tinyint的就不用int
  • 使用UNSIGNED儲存非負數值。
  • 不建議使用ENUM、SET類型，使用TINYINT取代
  • 使用短資料類型，例如取值範圍為0-80時，使用TINYINT UNSIGNED
  • 。儲存精確浮點數必須使用DECIMAL取代FLOAT和DOUBLE
  • 時間字段，除特殊情況一律採用int來記錄unix_timestamp
  • 儲存年使用YEAR類型。
  • 儲存日期使用DATE類型。
  • 儲存時間（精確到秒）建議使用TIMESTAMP類型，因為TIMESTAMP使用4位元組，DATETIME使用8個位元組。
  • 建議使用INT UNSIGNED儲存IPV4。
  • 盡可能不使用TEXT、BLOB類型
  • 禁止在資料庫中使用VARBINARY、BLOB儲存圖片、檔案等。建議使用其他方式儲存（TFS/SFS），MySQL只保存指標資訊。
  • 單一記錄大小禁止超過8k（列長度(中文)_3(UTF8) 欄位長度(英文)_1）

datetime與timestamp有何不同？

相同點：

TIMESTAMP欄位的顯示格式與DATETIME欄位相同。顯示寬度固定在19字符，且格式為YYYY-MM-DD HH:MM:SS。

不同點：

TIMESTAMP

  • 4個位元組儲存，時間範圍：1970-01-01 08:00:01 ~ 2038-01-19 11:14:07值以UTC格式儲存，涉及時區轉化，儲存時對目前的時區進行轉換，檢索時再轉換回目前的時區。
  • datetime8個位元組儲存，時間範圍：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 實際格式儲存，與時區無關

如何使用TIMESTAMP的自動賦值屬性？

將目前時間當作ts的預設值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。當行更新時，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

可以將1和2結合：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

如何使用INT UNSIGNED儲存ip？

使用INT UNSIGNED而不是char(15)來儲存ipv4位址，透過MySQL函數inet_ntoa和inet_aton來進行轉換。 Ipv6位址目前沒有轉換函數，需要使用DECIMAL或兩個bigINT來儲存。

  • 如無備註，所有欄位都設定NOT NULL，並設定預設值；
  • 禁止在資料庫中儲存明文密碼
  • 如無備註，所有的布林值字段，如is_hot、is_deleted，都必須設定一個預設值，並設為0；
  • 如無備註，排序字段order_id在程式中預設使用降序排列；
  • #整形定義中不加入長度，例如使用INT，而不是INT[4]

INT[M]，M值代表什麼意義？

注意數值類型括號後面的數字只是表示寬度而跟儲存範圍沒有關係。很多人他們認為INT(4)和INT(10)其值範圍分別是 (-9999到9999)和(-9999999999到9999999999)，這種理解是錯誤的。其實對整數中的 M值與 ZEROFILL 屬性結合使用時可以實現列值等寬。不管INT[M]中M值是多少，其取值範圍還是 (-2147483648到2147483647 有符號時)，(0到4294967295無符號時)。

顯示寬度並不會限制可以在列內保存的值的範圍，也不限制超過列的指定寬度的值的顯示。當結合可選擴展屬性ZEROFILL使用時預設補充的空格用零代替。例如：對於宣告為INT(5) ZEROFILL的資料列，值4檢索為00004。請注意如果在整數列中保存超過顯示寬度的一個值，當MySQL為複雜聯接產生臨時表時會遇到問題，因為在這些情況下MySQL相信資料適合原列寬度，如果為一個數值列指定ZEROFILL, MySQL自動為該列新增UNSIGNED屬性。

使用VARBINARY儲存大小寫敏感的變長字串

什麼時候用CHAR，什麼時候用VARCHAR？

CHAR和VARCHAR類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格保留等方面也不同。 CHAR和VARCHAR類型聲明的長度表示你想要儲存的最大字元數。例如，CHAR(30)可以佔用30個字元。

CHAR列的長度固定為建立表格時宣告的長度。長度可以為從0到255的任何值。當儲存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除。在儲存或檢索過程中不進行大小寫轉換。

VARCHAR欄位中的值為可變長字串。長度可以指定為0到65,535之間的值。 (VARCHAR的最大有效長度由最大行大小和使用的字元集決定。整體最大長度是65,532位元組）。同CHAR對比，VARCHAR值保存時只保存需要的字元數，另加一個位元組來記錄長度(如果列宣告的長度超過255，則使用兩個位元組)。 VARCHAR值儲存時不進行填充。當值儲存和檢索時尾部的空格仍保留，符合標準SQL。

char適合儲存使用者密碼的MD5雜湊值，它的長度總是一樣的。對於經常改變的值，char也好於varchar,因為固定長度的行不容易產生碎片，對於很短的列，char的效率也高於varchar。 char(1)字串對於單字節字元集只會佔用一個字節，但是varchar(1)則會佔用2個位元組，因為1個位元組用來儲存長度資訊。

索引設計規格

MySQL的查詢速度依賴良好的索引設計，因此索引對於高效能至關重要。合理的索引會加快查詢速度（包含UPDATE和DELETE的速度，MySQL會將包含該行的page載入到記憶體中，然後進行UPDATE或DELETE操作），不合理的索引會降低速度。 MySQL索引查找類似新華字典的拼音和部首查找，當拼音和部首索引不存在時，只能透過一頁一頁的翻頁來查找。當MySQL查詢無法使用索引時，MySQL會進行全表掃描，會消耗大量的IO。索引的用途：去重、加速定位、避免排序、覆蓋索引。

什麼是覆蓋索引

InnoDB儲存引擎中，secondary index（非主鍵索引）中沒有直接儲存資料列位址，儲存主鍵值。如果使用者需要查詢secondary index中所不包含的資料列時，需要先透過secondary index查找到主鍵值，然後再透過主鍵查詢到其他資料列，因此需要查詢兩次。覆蓋索引的概念就是查詢可以透過在一個索引中完成，覆蓋索引效率會比較高，主鍵查詢是天然的覆蓋索引。合理的創建索引以及合理的使用查詢語句，當使用到覆蓋索引時可以獲得效能提升。例如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當時候可以將索引加入為index(uid,email)，以獲得效能提升。

索引的基本規格

  • 索引數量控制，單張表中索引數量不超過5個，單一索引中的欄位數不超過5個。
  • 綜合評估資料密度和分佈
  • 考慮查詢和更新比例

#為什麼一張表中不能存在過多的索引？

InnoDB的secondary index使用b tree來存儲，因此在UPDATE、DELETE、INSERT的時候需要對b tree進行調整，過多的索引會減慢更新的速度。

對字串使用前綴索引，前綴索引長度不超過8個字符，建議優先考慮前綴索引，必要時可添加偽列並建立索引。

不要索引blob/text等欄位,不要索引大型欄位,這樣做會讓索引佔用太多的儲存空間

什麼是前綴索引？

前綴索引說白了就是對文字的前幾個字元（具體是幾個字元在建立索引時指定）建立索引，這樣建立起來的索引更小，所以查詢更快。前綴索引能有效縮小索引檔案的大小，提高索引的速度。但前綴索引也有它的壞處：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引，也不能把它們用作覆蓋索引(Covering Index)。

建立前綴索引的語法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

主鍵準則

  • #表必須有主鍵
  • 不使用更新頻繁的列
  • 盡量不選擇字串列
  • #不使用UUID MD5 HASH
    • ##預設使用非空的唯一鍵
  • 建議選擇自增或發號器

重要的SQL必須被索引，核心SQL優先考慮覆蓋索索引

    UPDATE、 DELETE語句的WHERE條件列
  • ORDER BY、GROUP BY、DISTINCT的欄位
  • 多表JOIN的欄位

區分度最大的欄位放在前面

    選擇篩選性較優的欄位放在最前面，例如單號、userid等，type，status等篩選性一般不建議放在最前面
  • 索引根據左前綴原則，當建立一個聯合索引(a,b,c)，則查詢條件裡面只有包含(a)或(a,b)或(a,b,c)的時候才能走索引,(a,c)作為條件的時候只能使用到a列索引,所以這個時候要確定a的返回列一定不能太多，不然語句設計就不合理,(b,c)則不能走索引
  • 合理地建立聯合索引（避免冗餘），(a,b,c) 相當於(a) 、(a,b) 、(a,b,c)

索引禁忌#

  • 不在低基底數列上建立索引，例如「性別」
  • 不在索引列進行數學運算和函數運算
  • 不要索引常用的小型表
  • 盡量不使用外鍵
  • 外鍵用來保護參考完整性，可在業務端實現
  • #對父表和子表的操作會相互影響，降低可用性
  • INNODB本身對online DDL的限制

MYSQL 中索引的限制

MYISAM 儲存引擎索引長度的總和不能超過1000 位元組
BLOB 和TEXT 類型的欄位只能建立前綴索引
MYSQL 目前不支援函數索引
使用不等於(!= 或a8093152e673feb7aba1828c43532094) 的時候, MYSQL 無法使用索引。
過濾欄位使用函數運算 (如 abs (column)) 後, MYSQL無法使用索引。
join語句中join條件字段類型不一致的時候MYSQL無法使用索引
使用 LIKE 操作的時候如果條件以通配符開始 (如 ‘�c…')時, MYSQL無法使用索引。
使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。

語句設計規格

使用預編譯語句

  • 只傳參數，比傳遞SQL語句更有效率
  • 一次解析，多次使用
  • 降低SQL注入機率

#避免隱含轉換

會導致索引失效

充分利用前綴索引

  • 必須是最左前綴
  • 不可能同時用到兩個範圍條件
  • 不使用%前導的查詢，如like “�”

不使用負向查詢，如not in/like

  • 無法使用索引，導致全表掃描
  • 全表掃描導致buffer pool利用率降低

避免使用預存程序、觸發器、UDF、events等

  • 讓資料庫做最擅長的事
  • 降低業務耦合度，為sacle out、sharding留有餘地
  • #避開BUG

避免使用大表的JOIN

#MySQL最擅長的是單表的主鍵/二級索引查詢
JOIN消耗較多內存，產生臨時表

避免在資料庫中進行數學運算

  • #MySQL不擅長數學運算和邏輯判斷
  • 無法使用索引

減少與資料庫的互動次數

  • INSERT …ON DUPLICATE KEY UPDATE
  • #REPLACE INTO 、INSERT IGNORE 、INSERT INTO VALUES(),(),()
  • UPDATE … WHERE ID IN(10,20,50,…)

#合理的使用分頁

限制分頁顯示的頁數只能點選上一頁、下一頁採用延遲關聯

如何正確的使用分頁？

假如有類似下面分頁語句：SELECT * FROM table ORDER BY id LIMIT 10000, 10由於MySQL裡對LIMIT OFFSET的處理方式是取出OFFSET LIMIT的所有數據，然後去掉OFFSET，返回底部的LIMIT。所以，在OFFSET數值較大時，MySQL的查詢效能會非常低。可以使用id > n 的方式來解決：

使用id > n 的方式有局限性，對於id不連續的問題，可以透過翻頁的時候同時傳入最後一個id方式來解決。 

http://example.com/page.php?last=100 
select * from table where id<100 order by id desc limit 10 
//上一页 
 http://example.com/page.php?first=110 
select * from table where id>110 order by id desc limit 10

這種方式比較大的缺點是，如果在瀏覽中有插入/刪除操作，翻頁不會更新，而總頁數可能仍然是根據新的count(*) 來計算，最終可能會產生某些記錄存取不到。為了修補這個問題，可以繼續引入當前頁碼以及在上次翻頁以後是否有插入/刪除等影響總記錄數的操作並進行緩存

select * from table where id >= (select id from table order by id limit #offset#, 1)
  •  拒絕大SQL，拆分成小SQL
  • 充分利用QUERY CACHE
  • 充分利用多核心CPU
  • 使用in代替or，in的值不超過1000個
  • 禁止使用order by rand ()
  • 使用EXPLAIN診斷，避免產生臨時表

EXPLAIN語句（在MySQL客戶端中執行）可以獲得MySQL如何執行SELECT語句的資訊。透過對SELECT語句執行EXPLAIN，可以知道MySQL執行此SELECT語句時是否使用了索引、全表掃描、臨時表、排序等資訊。盡量避免MySQL進行全表掃描、使用臨時表、排序等。詳見官方文件。

用union all而不是union

union all與 union有什麼不同？

union和union all關鍵字都是將兩個結果集合併為一個，但這兩者從使用和效率上都有所不同。

union在進行表格連結後會篩選掉重複的記錄，所以在表格連結後會對所產生的結果集進行排序運算，刪除重複的記錄再傳回結果。如：

select * from test_union1 
union select * from test_union2

這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序刪除重複的記錄，最後返回結果集，如果表資料量大的話可能會導致用磁碟進行排序。

而union all只是簡單的將兩個結果合併後就回傳。這樣，如果傳回的兩個結果集中有重複的數據，那麼傳回的結果集就會包含重複的數據了。

从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：

select * from test_union1 union all select * from test_union2
  •  程序应有捕获SQL异常的处理机制
  • 禁止单条SQL语句同时更新多个表
  • 不使用select * ，SELECT语句只获取需要的字段
  • 消耗CPU和IO、消耗网络带宽
  • 无法使用覆盖索引
  • 减少表结构变更带来的影响
  • 因为大，select/join 可能生成临时表
  • UPDATE、DELETE语句不使用LIMIT
  • INSERT语句必须显式的指明字段名称，不使用INSERT INTO table()
  • INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500
  • 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对Myisam
  • 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
  • 禁止使用跨库查询
  • 禁止使用子查询，建议将子查询转换成关联查询
  • 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；

分表规范

单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略

  • 用HASH进行散表，表名后缀使用十进制数，下标从0开始
  • 按日期时间分表需符合YYYY[MM][dd][HH]格式
  • 采用合适的分库分表策略。例如千库十表、十库百表等
  • 禁止使用分区表，分区表对分区键有严格要，分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
  • 拆分大字段和访问频率低的字段，分离冷热数据

行为规范

  • 批量导入、导出数据必须提前通知DBA协助观察
  • 禁止在线上从库执行后台管理和统计类查询
  • 禁止有super权限的应用程序账号存在
  • 产品出现非数据库导致的故障时及时通知DBA协助排查
  • 推广活动或上线新功能必须提前通知DBA进行流量评估
  • 数据库数据丢失，及时联系DBA进行恢复
  • 对单表的多次alter操作必须合并为一次操作
  • 不在MySQL数据库中存放业务逻辑
  • 重大项目的数据库方案选型和设计必须提前通知DBA参与
  • 对特别重要的库表，提前与DBA沟通确定维护和备份优先级
  • 不在业务高峰期批量更新、查询数据库其他规范
  • 提交线上建表改表需求，必须详细注明所有相关SQL语句

其他规范

日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。

相关图文教程：mysql数据库图文教程

以上是史上最全MySQL使用規範分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！

batch sql mysql 数据类型 Float NULL count select date timestamp 整型 enum 字符串 union char int double 指针 值类型 delete default column table windows oracle hbase 数据库 dba oceanbase linux tfs bug
陳述：
本文轉載於：jb51.net。如有侵權，請聯絡admin@php.cn刪除
上一篇：MySQL資料庫如何修改表格結構下一篇：MySQL資料庫如何修改表格結構

相關文章

看更多