程式設計師必會MySQL使用規範手冊

mysql教學欄位介紹MySQL使用規範手冊

#更多相關免費學習推薦：mysql教程(視訊)

一個平台或系統隨著時間的推移和使用者數量的增多，資料庫操作往往會變慢；而在Java應用開發中資料庫更是尤為重要，絕大多數情況下資料庫的效能決定了程式的效能，如若前期埋下的坑越多到後期資料庫就會成為整個系統的瓶頸；因此，更規範化的使用MySQL在開發中是不可或缺的。

一、MySQL資料庫命名規範

1、資料庫所有表前綴均使用項目名稱首字母縮寫；

2、資料庫所有物件名稱均使用小寫字母，且單字之間透過底線分開；

3、資料庫所有物件名稱禁止使用MySQL保留字及關鍵字，涉及到關鍵字的SQL查詢需要將關鍵字用單引號括起來；

4、資料庫所有物件名稱不超過32個字符，且命名要遵循見名知意原則；

5、資料庫臨時表必須以pro_tmp_ 為前綴並且以日期 20190917 為後綴，備份表必須以pro_bac 為前綴並以時間戳為後綴；(pro為項目名稱首字母縮寫)

6、資料庫所有儲存相同資料的列名和列類型必須保持一致。

二、MySQL資料庫基本設計規格

1、若無特殊說明，建表時一律採用Innodb儲存引擎。

選擇合適的引擎可以提高資料庫效能，如InnoDB和MyISAM，InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，視具體應用而定；

基本的差異為：MyISAM類型不支援事務處理等高階處理，而InnoDB類型支援；MyISAM類型的表強調的是效能，其執行數度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持以及外部鍵等高級資料庫功能；

因此，其支援事務處理、支持外鍵、支持崩潰修復能力和並發控制是我們建表時首選的存儲引擎。

2、資料庫和表格的字元集統一使用UTF8

資料庫和表格的字元集統一使用utf8，若是有字段需要儲存emoji表情之類的，則將表格或欄位設置成utf8mb4；因為，utf8號稱萬國碼，其無需轉碼、無亂碼風險且節省空間，而utf8mb4又向下兼容utf8。

3、設計資料庫時所有表格和欄位必須新增註釋

使用Comment子句新增表格和欄位的備註，或直接在資料庫連接工具的註解列中新增註釋，從專案開始就進行資料字典的維護。

使用Comment子句新增註解如：

-- 1、创建表：
CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);
-- 2、添加表注释：
Comment on table t1 is '个人信息';
-- 3、添加字段注释：
comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

使用資料庫連接工具新增註解：

4、單一表格的資料量大小控制在500萬以內

盡量控制單表資料量的大小，建議控制在500萬以內；500萬並不是MySQL資料庫的極限，但資料量太多不利於對錶結構進行修改、備份和恢復數據，適當採用分庫分錶等手段來控制單表數據量的大小。

5、使用MySQL分區表需謹慎

分區是將一個表格的資料依照某種方式，例如依照時間上的月份，分成多個較小的，更容易管理的部分，但是邏輯上仍是一個表；分區表在物理上表現為多個文件，在邏輯上仍表現為同一個表，需要謹慎選擇分區鍵；跨分區查詢效率可能會更低，建議使用物理分區表等方式管理大數據。

6、盡量滿足冷熱資料分離，減少表等寬度

MySQL限制每個表最多儲存4096列，且每一行資料的大小不超過65535字節，為了減少磁碟IO線程的開銷，就要適當控製表的寬度，因為表越寬，把表裝載進內存緩衝池時所佔用的內存也就越大，就會消耗更多的IO線程；除此之外，為了確保熱數據的記憶體快取命中率，更有效的利用緩存，避免讀入無用的冷數據，盡量把經常使用到的列放到同一個表中，避免不必要的關聯操作。

7、建立預留字段需謹慎

部分友人在設計資料庫表時，不僅設計了目前所需的字段，而且還在其中留出幾個字段作為備用。比方說，我設計了一個人員表（Person），其中已經添加了各種必要的字段，包括姓名（Name）、性別（Sex）、出生年月日（birthday）等等；

為了以防萬一，例如之後可能Person 表會涉及到畢業院校、工作單位、是否婚配和相片等信息，於是就加入5個varchar2 型的字段，分別叫做Text1、Text2……Text5；這一手操作看似防範未然，其實也不見得，因為大量預留字段會浪費空間、預留字段不能做到見名知意、預留字段無法確認存儲的資料類型且修改其字段類型還可能會造成鎖定表等問題。

針對此等情況可以參考以下兩點解決方案：

1. 如果數量很少，而且資訊的性質與原表密切相關，那麼就可以直接在原表上增加字段，並將相關的資料更新進去；

2. 如果數量較大，或並非原表物件至關重要的屬性，那麼就可以新增一個表，然後透過鍵值連接起來；

8、資料庫中禁止儲存圖片、檔案等大的二進位資料

若往資料庫表中儲存文件，而檔案通常很大，當資料庫進行讀取操作時，會進行大量的隨機IO操作，大檔案使得IO操作很耗時耗性能，造成短時間內資料量快速增長；所以，通常將圖片、檔案儲存在檔案伺服器中，資料庫只用於儲存檔案位址資訊。

三、MySQL資料庫欄位設計規格

1、優先選擇符合儲存所需的最小的資料類型。

主要是考慮索引的效能，因為列的欄位越大，建立索引時所需要的空間也越大，這樣一頁中能儲存的索引節點的數量就越少，在遍歷時需要的IO次數也就越多，索引的效能也越差。

2、避免使用TEXT、BLOB資料類型

避免使用TEXT和BLOB資料類型，其中最常見的TEXT類型可以儲存64K數據，MySQL記憶體臨時表不支援TEXT、BLOB這樣的大數據類型，若查詢中包含這樣的數據，在執行排序等操作時就不能使用內存臨時表，必須使用磁碟臨時表執行操作；

TEXT和BLOB類型只能使用前綴索引(當索引是很長的字元序列時，這個索引將會很佔內存，而且會很慢，這時候就會用到前綴索引了；所謂的前綴索引就是去索引的前面幾個字母作為索引，但是要降低索引的重複率，所以我們也必須判斷前綴索引的重複率；)，因為MySQL對索引欄位長度是有限的，所以TEXT類型只能使用前綴索引，且TEXT列上是不能有預設值的；

若需要使用，建議把BLOB或TEXT列分離到單獨的擴充表中，且查詢時一定不要使用select *，只需取出必要的列即可。

3、避免使用ENUM枚舉類型

修改ENUM 值需要使用ALTER 語句；

ENUM 類型的ORDER BY 操作效率低；

#禁止使用數值作為ENUM 的枚舉值。

4、所有列的預設值定義為NOT NULL

資料庫所有為NULL 的資料列需要額外的空間來存儲，因此會佔用更多的空間；

資料庫在進行比較和計算時需要對NULL 值做特別處理。

5、使用TIMESTAMP(4位元組)或DATETIME(8位元組)類型儲存時間

TIMESTAMP 儲存的時間範圍為：1970-01-01 00:00:01 ~ 2038 -01-19-03:14:07；

TIMESTAMP 佔用4位元組和INT相同，但可讀性比INT 類型的高，若是超出TIMESTAMP 取值範圍的則使用DATETIME 類型存儲；

用字串類型儲存時間的缺點：無法使用日期函數進行比較計算、字串儲存佔有更多的空間。

6、財務相關的金額類別資料必須使用decimal 類型

精確浮點：decimal

非精確浮點：float、double

Decimal類型為精確浮點數，在計算時不會遺失精度；佔有空間大小由定義的寬度決定，每4個位元組可以儲存9位元數字，且小數點也要佔有一個位元組；另外，Decimal類型可用於存儲比bigint更大的資料型別。

四、MySQL索引設計規格

1、每張表的索引數量不超過5個

索引可以增加查詢效率，但同樣也會降低插入和更新的效率，甚至有些情況下還會降低查詢效率，因此並不是越多越好，要控制其數量。

2、每個Innodb 表必須有一個主鍵

Innodb 是一種索引組織表，其資料儲存的邏輯順序和索引的順序是相同的；

每張表可以有多個索引，但表的儲存順序只能有一種，Innodb 是按照主鍵索引的順序來組織表的，因此不要使用更新頻繁的列、UUID、MD5、HASH和字串列作為主鍵，這些欄位無法保證資料的順序成長，主鍵建議使用自增ID 值。

3、盡量避免使用外鍵約束

不建議使用外鍵約束(foreign key)，但一定要在表與表之間的關聯鍵上建立索引；

外鍵雖然可以保證資料的參考完整性，但外鍵也會影響父表和子表的寫入操作從而降低效能，也會使得表更耦合，建議在業務端實作。

五、MySQL数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。

2、避免数据类型的隐式转换

隐式转换如：SELECT 1 + "1"；数值型 + 字符型 的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。

3、充分利用表中存在的索引

1）避免使用双%号的查询条件

如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。

2）一条SQL语句只能使用复合索引中的一列进行范围查询

例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。

3）使用not exists 代替not in

因为not in 在SQL语句中执行时会导致索引失效。

4、杜绝使用SELECT * ，必须使用SELECT 查询

因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的INSERT 语句

如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查询，可以把子查询优化为join 关联操作

但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；

子查询性能差的原因：

• 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；

• 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

7、避免使用JOIN 关联太多表

1）在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；

2）如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；

3）此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；

8、对同一列对象进行or 判断时，使用in 替代or

in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

9、禁止使用order by rand() 进行随机排序

10、禁止在WHERE 从句中对列进行函数转换和计算

因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No推荐：

where date(end_time)='20190101'

推荐：

where end_time >= &#39;20190101&#39; and end_time < &#39;20190102&#39;

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；

2）UNION ALL 不会再对结果集进行去重操作；

12、把复杂、较长的SQL 拆分为为多个小SQL 执行

1）大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；

2）在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；

3）SQL拆分后可以通过并行执行来提高处理效率。

六、MySQL数据库行为规范

1、超过100万行数据的批量操作(update delete insert)，分多次进行

大批量操作可能回造成严重的主从延迟；

binlog日志为row格式时会产生大量的日志；

避免产生大事物操作。

2、对于大表使用pt-online-schema-change 修改表结构

1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；

2）pt-online-schema-change 它首先會建立一個與原表結構相同的新表，並且在新表上進行表結構的修改，然後再把原表中的資料複製到新表中，並在原表中增加一些觸發器；然後，把原表中新增的資料也複製到新表中，在行所有資料複製完成之後，把新表命名成原表，並把原來的表刪除掉，其是把原來一個DDL操作，分解成多個小的批次執行。

3、禁止給程式使用的帳號授予super 權限

當達到最大連線數限制時，還執行1個有super權限的使用者連線super權限只能留給DBA處理問題的帳號使用。

4、對於程式連接資料庫帳號，遵循權限最小原則

程式使用資料庫帳號只能在一個資料庫下使用，且程式使用的帳號原則上不授予drop 權限。

以上是程式設計師必會MySQL使用規範手冊的詳細內容。更多資訊請關注PHP中文網其他相關文章！