文章目錄

• 一. 命名規(guī)范
• 二. 庫表基礎(chǔ)規(guī)范
• 三. 字段規(guī)范
• 四. 索引規(guī)范
• 五. SQL設(shè)計(jì)
• • ①.正規(guī)化表設(shè)計(jì)原則
  • ②.SQL設(shè)計(jì)
  • ③.“三少原則”
• 六. 行為規(guī)范

一. 命名規(guī)范

1.庫名、表名、字段名必須使用小寫字母，并采用下劃線分割
(1)MySQL有配置參數(shù)lower_case_table_names=1，即庫表名以小寫存儲(chǔ)，大小寫不敏感。如果是0，則庫表名以實(shí)際情況存儲(chǔ)，大小寫敏感；如果是2，以實(shí)際情況存儲(chǔ)，但以小寫比較。
(2)如果大小寫混合使用，可能存在abc，Abc，ABC等多個(gè)表共存，容易導(dǎo)致混亂。
(3)字段名顯示區(qū)分大小寫，但實(shí)際使?時(shí)不區(qū)分，即不可以建立兩個(gè)名字一樣但大小寫不一樣的字段。
(4)為了統(tǒng)一規(guī)范， 庫名、表名、字段名使用小寫字母。
2.庫名以 d 開頭，表名以 t 開頭，字段名以 f_ 開頭
(1)比如表 t_crm_relation，中間的 crm 代表業(yè)務(wù)模塊名
(2)視圖以view_開頭，事件以event_開頭，觸發(fā)器以trig_開頭，存儲(chǔ)過程以proc_開頭，函數(shù)以func_開頭
(3)普通索引以idx_col1_col2命名，唯一索引以u(píng)k_col1_col2命名（可去掉f_公共部分）。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)
3.庫名、表名、字段名禁止超過32個(gè)字符，需見名知意
庫名、表名、字段名支持最多64個(gè)字符，但為了統(tǒng)一規(guī)范、易于辨識(shí)以及減少傳輸量，禁止超過32個(gè)字符
4.臨時(shí)庫、表名須以tmp加日期為后綴
如 t_crm_relation_tmp0425。備份表也類似，形如 _bak20160425 。
5.按日期時(shí)間分表須符合_YYYY[MM][DD]格式
這也是為將來有可能分表做準(zhǔn)備的，比如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了這種規(guī)范。
不具有時(shí)間特性的，直接以 t_tbname_001 這樣的方式命名。

二. 庫表基礎(chǔ)規(guī)范

1.使用Innodb存儲(chǔ)引擎
5.5版本開始mysql默認(rèn)存儲(chǔ)引擎就是InnoDB，5.7版本開始，系統(tǒng)表都放棄MyISAM了。
2.表字符集統(tǒng)一使用UTF8
(1)UTF8字符集存儲(chǔ)漢字占用3個(gè)字節(jié)，存儲(chǔ)英文字符占用一個(gè)字節(jié)
(2)校對(duì)字符集使用默認(rèn)的 utf8_general_ci
(3)連接的客戶端也使用utf8，建立連接時(shí)指定charset或SET NAMES UTF8;。（對(duì)于已經(jīng)在項(xiàng)目中長期使用latin1的，救不了了）
(4)如果遇到EMOJ等表情符號(hào)的存儲(chǔ)需求，可申請(qǐng)使用UTF8MB4字符集
3.所有表都要添加注釋
(1)盡量給字段也添加注釋
(2)類status型需指明主要值的含義，如”0-離線，1-在線”
4.控制單表字段數(shù)量
(1)單表字段數(shù)上限30左右，再多的話考慮垂直分表，一是冷熱數(shù)據(jù)分離，二是大字段分離，三是常在一起做條件和返回列的不分離。
(2)表字段控制少而精，可以提高IO效率，內(nèi)存緩存更多有效數(shù)據(jù)，從而提高響應(yīng)速度和并發(fā)能力，后續(xù) alter table 也更快。
5.所有表都必須要顯式指定主鍵
(1)主鍵盡量采用自增方式，InnoDB表實(shí)際是一棵索引組織表，順序存儲(chǔ)可以提高存取效率，充分利用磁盤空間。還有對(duì)一些復(fù)雜查詢可能需要自連接來優(yōu)化時(shí)需要用到。
(2)需要全局唯一主鍵時(shí)，使用外部發(fā)號(hào)器ticket server（建設(shè)中）
(3)如果沒有主鍵或唯一索引，update/delete是通過所有字段來定位操作的行，相當(dāng)于每行就是一次全表掃描
(4)少數(shù)情況可以使用聯(lián)合唯一主鍵，需與DBA協(xié)商
6.不強(qiáng)制使用外鍵參考
即使2個(gè)表的字段有明確的外鍵參考關(guān)系，也不使用 FOREIGN KEY ，因?yàn)樾录o(jì)錄會(huì)去主鍵表做校驗(yàn)，影響性能。
7.適度使用存儲(chǔ)過程、視圖，禁止使用觸發(fā)器、事件
(1)存儲(chǔ)過程（procedure）雖然可以簡(jiǎn)化業(yè)務(wù)端代碼，在傳統(tǒng)企業(yè)寫復(fù)雜邏輯時(shí)可能會(huì)用到，而在互聯(lián)網(wǎng)企業(yè)變更是很頻繁的，在分庫分表的情況下要升級(jí)一個(gè)存儲(chǔ)過程相當(dāng)麻煩。又因?yàn)樗遣挥涗沴og的，所以也不方便debug性能問題。如果使用過程，一定考慮如果執(zhí)行失敗的情況。
(2)使用視圖一定程度上也是為了降低代碼里SQL的復(fù)雜度，但有時(shí)候?yàn)榱艘晥D的通用性會(huì)損失性能（比如返回不必要的字段）。
(3)觸發(fā)器（trigger）也是同樣，但也不應(yīng)該通過它去約束數(shù)據(jù)的強(qiáng)一致性，mysql只支持“基于行的觸發(fā)”，也就是說，觸發(fā)器始終是針對(duì)一條記錄的，而不是針對(duì)整個(gè)sql語句的，如果變更的數(shù)據(jù)集非常大的話，效率會(huì)很低。掩蓋一條sql背后的工作，一旦出現(xiàn)問題將是災(zāi)難性的，但又很難快速分析和定位。再者需要ddl時(shí)無法使用pt-osc工具。放在transaction執(zhí)行。
(4)事件（event）也是一種偷懶的表現(xiàn)，目前已經(jīng)遇到數(shù)次由于定時(shí)任務(wù)執(zhí)行失敗影響業(yè)務(wù)的情況，而且mysql無法對(duì)它做失敗預(yù)警。建立專門的 job scheduler 平臺(tái)。
a.單表數(shù)據(jù)量控制在5000w以內(nèi)
b.數(shù)據(jù)庫中不允許存儲(chǔ)明文密碼

三. 字段規(guī)范

1.char、varchar、text等字符串類型定義
(1)對(duì)于長度基本固定的列，如果該列恰好更新又特別頻繁，適合char
(2)varchar雖然存儲(chǔ)變長字符串，但不可太小也不可太大。UTF8最多能存21844個(gè)漢字，或65532個(gè)英文
(3)varbinary(M)保存的是二進(jìn)制字符串，它保存的是字節(jié)而不是字符，所以沒有字符集的概念，M長度0-255（字節(jié)）。只用于排序或比較時(shí)大小寫敏感的類型，不包括密碼存儲(chǔ)
(4)TEXT類型與VARCHAR都類似，存儲(chǔ)可變長度，最大限制也是2^16，但是它20bytes以后的內(nèi)容是在數(shù)據(jù)頁以外的空間存儲(chǔ)（row_format=dynamic），對(duì)它的使用需要多一次尋址，沒有默認(rèn)值。
一般用于存放容量平均都很大、操作沒有其它字段那樣頻繁的值。
網(wǎng)上部分文章說要避免使用text和blob，要知道如果純用varchar可能會(huì)導(dǎo)致行溢出，效果差不多，但因?yàn)槊啃姓加米止?jié)數(shù)過多，會(huì)導(dǎo)致buffer_pool能緩存的數(shù)據(jù)行、頁下降。另外text和blob上面一般不會(huì)去建索引，而是利用sphinx之類的第三方全文搜索引擎，如果確實(shí)要?jiǎng)?chuàng)建（前綴）索引，那就會(huì)影響性能。凡事看具體場(chǎng)景。
另外盡可能把text/blob拆到另一個(gè)表中
(5)BLOB可以看出varbinary的擴(kuò)展版本，內(nèi)容以二進(jìn)制字符串存儲(chǔ)，無字符集，區(qū)分大小寫，有一種經(jīng)常提但不用的場(chǎng)景：不要在數(shù)據(jù)庫里存儲(chǔ)圖片。
2.int、tinyint、decimal等數(shù)字類型定義
(1)使用tinyint來代替 enum和boolean
ENUM類型在需要修改或增加枚舉值時(shí)，需要在線DDL，成本較高；ENUM列值如果含有數(shù)字類型，可能會(huì)引起默認(rèn)值混淆
tinyint使用1個(gè)字節(jié)，一般用于status,type,flag的列
(2)建議使用 UNSIGNED 存儲(chǔ)非負(fù)數(shù)值
相比不使用 unsigned，可以擴(kuò)大一倍使用數(shù)值范圍
(3)int使用固定4個(gè)字節(jié)存儲(chǔ)，int(11)與int(4)只是顯示寬度的區(qū)別
(4)使用Decimal 代替float/double存儲(chǔ)精確浮點(diǎn)數(shù)
對(duì)于貨幣、金額這樣的類型，使用decimal，如 decimal(9,2)。float默認(rèn)只能能精確到6位有效數(shù)字
3.timestamp與datetime選擇
(1)datetime 和 timestamp類型所占的存儲(chǔ)空間不同，前者8個(gè)字節(jié)，后者4個(gè)字節(jié)，這樣造成的后果是兩者能表示的時(shí)間范圍不同。前者范圍為1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范圍為 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范圍比 DATATIME 要小。
(2)timestamp可以在insert/update行時(shí)，自動(dòng)更新時(shí)間字段（如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但一個(gè)表只能有一個(gè)這樣的定義。
(3)timestamp顯示與時(shí)區(qū)有關(guān)，內(nèi)部總是以 UTC 毫秒 來存的。還受到嚴(yán)格模式的限制
(4)優(yōu)先使用timestamp，datetime也沒問題
(5)where條件里不要對(duì)時(shí)間列上使用時(shí)間函數(shù)
4.建議字段都定義為NOT NULL
(1)如果是索引字段，一定要定義為not null 。因?yàn)閚ull值會(huì)影響cordinate統(tǒng)計(jì)，影響優(yōu)化器對(duì)索引的選擇
(2)如果不能保證insert時(shí)一定有值過來，定義時(shí)使用default ‘’ ，或 0
5.同一意義的字段定義必須相同
比如不同表中都有 f_user_id 字段，那么它的類型、字段長度要設(shè)計(jì)成一樣

四. 索引規(guī)范

1.任何新的select,update,delete上線，都要先explain，看索引使用情況
盡量避免extra列出現(xiàn)：Using File Sort，Using Temporary，rows超過1000的要謹(jǐn)慎上線。
explain解讀
(1)type：ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）
(2)possible_keys：指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用
(3)key：表示MySQL實(shí)際決定使用的鍵（索引）
如果沒有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
(4)ref：表示選擇 key 列上的索引，哪些列或常量被用于查找索引列上的值
(5)rows：根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)
(6)Extra
a.Using temporary：表示MySQL需要使用臨時(shí)表來存儲(chǔ)結(jié)果集，常見于排序和分組查詢
b.Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”
1.索引個(gè)數(shù)限制
(1)索引是雙刃劍，會(huì)增加維護(hù)負(fù)擔(dān)，增大IO壓力，索引占用空間是成倍增加的
(2)單張表的索引數(shù)量控制在5個(gè)以內(nèi)，或不超過表字段個(gè)數(shù)的20%。若單張表多個(gè)字段在查詢需求上都要單獨(dú)用到索引，需要經(jīng)過DBA評(píng)估。
2.避免冗余索引
(1.)InnoDB表是一棵索引組織表，主鍵是和數(shù)據(jù)放在一起的聚集索引，普通索引最終指向的是主鍵地址，所以把主鍵做最后一列是多余的。如f_crm_id作為主鍵，聯(lián)合索引(f_user_id,f_crm_id)上的f_crm_id就完全多余
(2)(a,b,c)、(a,b)，后者為冗余索引。可以利用前綴索引來達(dá)到加速目的，減輕維護(hù)負(fù)擔(dān)
3.沒有特殊要求，使用自增id作為主鍵
(1.)主鍵是一種聚集索引，順序?qū)懭搿＝M合唯一索引作為主鍵的話，是隨機(jī)寫入，適合寫少讀多的表
(2)主鍵不允許更新
4.索引盡量建在選擇性高的列上
(1)不在低基數(shù)列上建立索引，例如性別、類型。但有一種情況，idx_feedbackid_type (f_feedback_id,f_type)，如果經(jīng)常用 f_type=1 比較，而且能過濾掉90%行，那這個(gè)組合索引就值得創(chuàng)建。有時(shí)候同樣的查詢語句，由于條件取值不同導(dǎo)致使用不同的索引，也是這個(gè)道理。
(2)索引選擇性計(jì)算方法（基數(shù) ÷ 數(shù)據(jù)行數(shù)）
Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1說明col1上使用索引的過濾效果越好
(3)走索引掃描行數(shù)超過30%時(shí)，改全表掃描
5.最左前綴原則
(1)mysql使用聯(lián)合索引時(shí)，從左向右匹配，遇到斷開或者范圍查詢時(shí)，無法用到后續(xù)的索引列
比如索引idx_c1_c2_c3 (c1,c2,c3)，相當(dāng)于創(chuàng)建了(c1)、(c1,c2)、(c1,c2,c3)三個(gè)索引，where條件包含上面三種情況的字段比較則可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情況就完全用不到這個(gè)索引
(2)遇到范圍查詢(>、<、between、like)也會(huì)停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1,c2列上的比較能用到索引，(c1,c2,c3)排列的索引才可能會(huì)都用上
(3)where條件里面字段的順序與索引順序無關(guān)，mysql優(yōu)化器會(huì)自動(dòng)調(diào)整順序
6.前綴索引
(1)對(duì)超過30個(gè)字符長度的列創(chuàng)建索引時(shí)，考慮使用前綴索引，如 idx_cs_guid2 (f_cs_guid(26))表示截取前26個(gè)字符做索引，既可以提高查找效率，也可以節(jié)省空間
(2)前綴索引也有它的缺點(diǎn)是，如果在該列上 ORDER BY 或 GROUP BY 時(shí)無法使用索引，也不能把它們用作覆蓋索引(Covering Index)
(3)如果在varbinary或blob這種以二進(jìn)制存儲(chǔ)的列上建立前綴索引，要考慮字符集，括號(hào)里表示的是字節(jié)數(shù)
7.合理使用覆蓋索引減少IO
INNODB存儲(chǔ)引擎中，secondary index(非主鍵索引，又稱為輔助索引、二級(jí)索引)沒有直接存儲(chǔ)行地址，而是存儲(chǔ)主鍵值。
如果用戶需要查詢secondary index中所不包含的數(shù)據(jù)列，則需要先通過secondary index查找到主鍵值，然后再通過主鍵查詢到其他數(shù)據(jù)列，因此需要查詢兩次。覆蓋索引則可以在一個(gè)索引中獲取所有需要的數(shù)據(jù)列，從而避免回表進(jìn)行二次查找，節(jié)省IO因此效率較高。
例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主鍵，適當(dāng)時(shí)候可以將索引添加為index(uid，email)，以獲得性能提升。
8.盡量不要在頻繁更新的列上創(chuàng)建索引
如不在定義了 ON UPDATE CURRENT_STAMP 的列上創(chuàng)建索引，維護(hù)成本太高（好在mysql有insert buffer，會(huì)合并索引的插入）

五. SQL設(shè)計(jì)

①.正規(guī)化表設(shè)計(jì)原則

零狀態(tài)形式：所需要的所有字段組成的表，未經(jīng)過任何處理。
第一級(jí)正規(guī)化形式處理原則：1.消除每個(gè)表格中重復(fù)的組2.為每套相關(guān)的數(shù)據(jù)建立一個(gè)獨(dú)立的表格3.使用一個(gè)主鍵來標(biāo)識(shí)每套相關(guān)的數(shù)據(jù)。
第二級(jí)正規(guī)化形式處理原則：1.為應(yīng)用在多條記錄的字段建立獨(dú)立的表格；2.通過一個(gè)foreign key來關(guān)聯(lián)這些表格的值。
第三級(jí)正規(guī)化形式處理原則：1.消除不依賴于該鍵的字段。
第四個(gè)正規(guī)化形式處理原則：1.在一個(gè)多對(duì)多的關(guān)系中，獨(dú)立的實(shí)體不能存放在同一個(gè)表格中。由于它僅應(yīng)用于多對(duì)多的關(guān)系，因此大多數(shù)的開發(fā)者可以忽略這條規(guī)定。不過在某些情況下，它是非常實(shí)用的。
第五級(jí)正規(guī)化形式處理原則：1.原來的表格必須可以通過由它分離出去的表格重新構(gòu)建，使用這個(gè)規(guī)定的好處是，你可以確保不會(huì)在分離的表格中引入多余的列，所有你創(chuàng)建的表格結(jié)構(gòu)都與它們的實(shí)際需要一樣大。應(yīng)用這條規(guī)定是一個(gè)好習(xí)慣，不過除非你要處理一個(gè)非常大型的數(shù)據(jù)，否則你將不需要用到它。

②.SQL設(shè)計(jì)

1.杜絕直接 SELECT * 讀取全部字段
即使需要所有字段，減少網(wǎng)絡(luò)帶寬消耗，能有效利用覆蓋索引，表結(jié)構(gòu)變更對(duì)程序基本無影響
2.能確定返回結(jié)果只有一條時(shí)，使用 limit 1
在保證數(shù)據(jù)不會(huì)有誤的前提下，能確定結(jié)果集數(shù)量時(shí)，多使用limit，盡快的返回結(jié)果。
3.小心隱式類型轉(zhuǎn)換
(1)轉(zhuǎn)換規(guī)則
a. 兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=> 對(duì)兩個(gè) NULL 做比較時(shí)會(huì)返回 1，這兩種情況都不需要做類型轉(zhuǎn)換
b. 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來比較，不做類型轉(zhuǎn)換
c. 兩個(gè)參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換
d. 十六進(jìn)制的值和非數(shù)字做比較時(shí)，會(huì)被當(dāng)做二進(jìn)制串
e. 有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為 timestamp
f. 有一個(gè)參數(shù)是 decimal 類型，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)，會(huì)將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)，則會(huì)把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
g. 所有其他情況下，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較。
(2)如果一個(gè)索引建立在string類型上，如果這個(gè)字段和一個(gè)int類型的值比較，符合第 g 條。如f_phone定義的類型是varchar，但where使用f_phone in (098890)，兩個(gè)參數(shù)都會(huì)被當(dāng)成成浮點(diǎn)型。發(fā)生這個(gè)隱式轉(zhuǎn)換并不是最糟的，最糟的是string轉(zhuǎn)換后的float，mysql無法使用索引，這才導(dǎo)致了性能問題。如果是 f_user_id = ‘1234567’ 的情況，符合第 b 條,直接把數(shù)字當(dāng)字符串比較。
4.禁止在where條件列上使用函數(shù)
(1)會(huì)導(dǎo)致索引失效，如lower(email)，f_qq % 4。可放到右邊的常量上計(jì)算
(2)返回小結(jié)果集不是很大的情況下，可以對(duì)返回列使用函數(shù)，簡(jiǎn)化程序開發(fā)
5.使用like模糊匹配，%不要放首位
會(huì)導(dǎo)致索引失效，有這種搜索需求是，考慮其它方案，如sphinx全文搜索
6.涉及到復(fù)雜sql時(shí)，務(wù)必先參考已有索引設(shè)計(jì)，先explain
(1)簡(jiǎn)單SQL拆分，不以代碼處理復(fù)雜為由。
(2)比如 OR 條件： f_phone=‘10000’ or f_mobile=‘10000’，兩個(gè)字段各自有索引，但只能用到其中一個(gè)。可以拆分成2個(gè)sql，或者union all。
(3)先explain的好處是可以為了利用索引，增加更多查詢限制條件
7.使用join時(shí)，where條件盡量使用充分利用同一表上的索引
(1)如 select t1.a,t2.b * from t1,t2 and t1.a=t2.a and t1.b=123 and t2.c= 4 ，如果t1.c與t2.c字段相同，那么t1上的索引(b,c)就只用到b了。此時(shí)如果把where條件中的t2.c=4改成t1.c=4，那么可以用到完整的索引
(2)這種情況可能會(huì)在字段冗余設(shè)計(jì)（反范式）時(shí)出現(xiàn)
(3)正確選取inner join和left join
8.少用子查詢，改用join
小于5.6版本時(shí)，子查詢效率很低，不像Oracle那樣先計(jì)算子查詢后外層查詢。5.6版本開始得到優(yōu)化
9.考慮使用union all，少使用union，注意考慮去重
(1)union all不去重，而少了排序操作，速度相對(duì)比union要快，如果沒有去重的需求，優(yōu)先使用union all
(2)如果UNION結(jié)果中有使用limit，在2個(gè)子SQL可能有許多返回值的情況下，各自加上limit。如果還有order by，請(qǐng)找DBA。
10.IN的內(nèi)容盡量不超過200個(gè)
超過500個(gè)值使用批量的方式，否則一次執(zhí)行會(huì)影響數(shù)據(jù)庫的并發(fā)能力，因?yàn)閱蜸QL只能且一直占用單CPU，而且可能導(dǎo)致主從復(fù)制延遲
11.拒絕大事務(wù)
比如在一個(gè)事務(wù)里進(jìn)行多個(gè)select，多個(gè)update，如果是高頻事務(wù)，會(huì)嚴(yán)重影響MySQL并發(fā)能力，因?yàn)槭聞?wù)持有的鎖等資源只在事務(wù)rollback/commit時(shí)才能釋放。但同時(shí)也要權(quán)衡數(shù)據(jù)寫入的一致性。
12.避免使用is null, is not null這樣的比較
13.order by … limit
這種查詢更多的是通過索引去優(yōu)化，但order by的字段有講究，比如主鍵id與f_time都是順序遞增，那就可以考慮order by id而非 f_time 。
14.c1 < a order by c2
與上面不同的是，order by之前有個(gè)范圍查詢，由前面的內(nèi)容可知，用不到類似(c1,c2)的索引，但是可以利用(c2,c1)索引。另外還可以改寫成join的方式實(shí)現(xiàn)。
15.分頁優(yōu)化
建議使用合理的分頁方式以提高分頁效率，大頁情況下不使用跳躍式分頁
假如有類似下面分頁語句:
SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;
這種分頁方式會(huì)導(dǎo)致大量的io，因?yàn)镸ySQL使用的是提前讀取策略。
推薦分頁方式：
SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10
即傳入上一次分頁的界值
SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON t1.id=t2.id
16.count計(jì)數(shù)
(1)首先count()、count(1)、count(col1)是有區(qū)別的，count()表示整個(gè)結(jié)果集有多少條記錄，count(1)表示結(jié)果集里以primary key統(tǒng)計(jì)數(shù)量，絕大多數(shù)情況下count()與count(1)效果一樣的，但count(col1)表示的是結(jié)果集里 col1 列 NOT null 的記錄數(shù)。優(yōu)先采用count()
(2)大數(shù)據(jù)量count是消耗資源的操作，甚至?xí)下麄€(gè)庫，查詢性能問題無法解決的，應(yīng)從產(chǎn)品設(shè)計(jì)上進(jìn)行重構(gòu)。例如當(dāng)頻繁需要count的查詢，考慮使用匯總表
(3)遇到distinct的情況，group by方式可能效率更高。
17.delete,update語句改成select再explain
select最多導(dǎo)致數(shù)據(jù)庫慢，寫操作才是鎖表的罪魁禍?zhǔn)?br /> 18.減少與數(shù)據(jù)庫交互的次數(shù)，盡量采用批量SQL語句
(1)INSERT … ON DUPLICATE KEY UPDATE …，插入行后會(huì)導(dǎo)致在一個(gè)UNIQUE索引或PRIMARY KEY中出現(xiàn)重復(fù)值，則執(zhí)行舊行UPDATE，如果不重復(fù)則直接插入，影響1行。
(2)REPLACE INTO類似，但它是沖突時(shí)刪除舊行。INSERT IGNORE相反，保留舊行，丟棄要插入的新行。
(3)INSERT INTO VALUES(),(),()，合并插入。
19.杜絕危險(xiǎn)SQL
(1)去掉where 1=1 這樣無意義或恒真的條件，如果遇到update/delete或遭到sql注入就恐怖了
(2)SQL中不允許出現(xiàn)DDL語句。一般也不給予create/alter這類權(quán)限，但阿里云RDS只區(qū)分讀寫用戶

③.“三少原則”

防止數(shù)據(jù)庫設(shè)計(jì)打補(bǔ)丁的方法是“三少原則”
　　(1)一個(gè)數(shù)據(jù)庫中表的個(gè)數(shù)越少越好。只有表的個(gè)數(shù)少了，才能說明系統(tǒng)的E–R圖少而精，去掉了重復(fù)的多余的實(shí)體，形成了對(duì)客觀世界的高度抽象，進(jìn)行了系統(tǒng)的數(shù)據(jù)集成，防止了打補(bǔ)丁式的設(shè)計(jì)；

(2)一個(gè)表中組合主鍵的字段個(gè)數(shù)越少越好。因?yàn)橹麈I的作用，一是建主鍵索引，二是做為子表的外鍵，所以組合主鍵的字段個(gè)數(shù)少了，不僅節(jié)省了運(yùn)行時(shí)間，而且節(jié)省了索引存儲(chǔ)空間；

(3)一個(gè)表中的字段個(gè)數(shù)越少越好。只有字段的個(gè)數(shù)少了，才能說明在系統(tǒng)中不存在數(shù)據(jù)重復(fù)，且很少有數(shù)據(jù)冗余，更重要的是督促讀者學(xué)會(huì)“列變行”，這樣就防止了將子表中的字段拉入到主表中去，在主表中留下許多空余的字段。所謂“列變行”，就是將主表中的一部分內(nèi)容拉出去，另外單獨(dú)建一個(gè)子表。這個(gè)方法很簡(jiǎn)單，有的人就是不習(xí)慣、不采納、不執(zhí)行。

數(shù)據(jù)庫設(shè)計(jì)的實(shí)用原則是：在數(shù)據(jù)冗余和處理速度之間找到合適的平衡點(diǎn)。“三少”是一個(gè)整體概念，綜合觀點(diǎn)，不能孤立某一個(gè)原則。該原則是相對(duì)的，不是絕對(duì)的。“三多”原則肯定是錯(cuò)誤的。試想：若覆蓋系統(tǒng)同樣的功能，一百個(gè)實(shí)體(共一千個(gè)屬性)的E–R圖，肯定比二百個(gè)實(shí)體(共二千個(gè)屬性)的E–R圖，要好得多。

六. 行為規(guī)范

(1)不允許在DBA不知情的情況下導(dǎo)現(xiàn)網(wǎng)數(shù)據(jù)
(2)大批量更新，如修復(fù)數(shù)據(jù)，避開高峰期，并通知DBA。直接執(zhí)行sql的由運(yùn)維或DBA同事操作
(3)及時(shí)處理已下線業(yè)務(wù)的SQL
(4)復(fù)雜sql上線審核，因?yàn)槟壳斑€沒有SQL審查機(jī)制，復(fù)雜sql如多表join,count,group by，主動(dòng)上報(bào)DBA評(píng)估。
(5)重要項(xiàng)目的數(shù)據(jù)庫方案選型和設(shè)計(jì)必須提前通知DBA參與

總結(jié)

以上是生活随笔為你收集整理的数据库开发设计规范及表结构设计原则的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。