這篇文章給大家介紹MySQL準(zhǔn)入規(guī)范有哪些，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

創(chuàng)新互聯(lián)是一家專注于做網(wǎng)站、網(wǎng)站建設(shè)與策劃設(shè)計(jì),綏濱網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:綏濱等地區(qū)。綏濱做網(wǎng)站價(jià)格咨詢:18982081108

一、數(shù)據(jù)庫(kù)設(shè)計(jì)

1、表結(jié)構(gòu)設(shè)計(jì)

 -表中的自增列（auto_increment屬性）推薦使用bigint類型
  -首選使用非空的唯一鍵， 其次選擇自增列或發(fā)號(hào)器
    不使用更新頻繁的列，盡量不選擇字符串列，不使用UUID MD5 HASH
  -業(yè)務(wù)中選擇性很少的狀態(tài)status、類型type等字段推薦使用tinytint或者smallint類型
  -業(yè)務(wù)中IP地址字段推薦使用int類型
  -業(yè)務(wù)活躍的大表中必須有行數(shù)據(jù)的創(chuàng)建時(shí)間字段create_time和最后更新時(shí)間字段update_time
  -表中所有字段必須都是NOT NULL屬性，業(yè)務(wù)可以根據(jù)需要定義DEFAULT值
  -用decimal存儲(chǔ)精確浮點(diǎn)數(shù)(不要用浮點(diǎn)類型)
  -不推薦使用enum，set，blob，text等類型，對(duì)于大表必須將text、blob等類型字段拆分或者獨(dú)立建表

2、索引設(shè)計(jì)

 -避免冗余索引 ：避免將同一個(gè)字段都建立索引，索引的建立需要根據(jù)訪問(wèn)的SQL語(yǔ)句來(lái)評(píng)估
  -一次查詢，一個(gè)表只能用到一個(gè)索引，不要對(duì)每個(gè)查詢條件的字段都單獨(dú)建立索引
  -單張表索引數(shù)量不超過(guò)7，單個(gè)索引字段數(shù)不超過(guò)5  
  -不在null列上加索引
  -不在低基數(shù)列上建立索引，例如“性別” 
  -復(fù)合索引字段排序，區(qū)分度最大的字段放在前面
  -核心SQL優(yōu)先考慮覆蓋索引
  -對(duì)字符串使用前綴索引
  -前綴長(zhǎng)度不超過(guò)8個(gè)字符 ，必須是最左前綴

3、字符集及校驗(yàn)集

 -數(shù)據(jù)庫(kù)和表的字符集必須一致，且所有表的字符集必須一致，只能是utf8；數(shù)據(jù)庫(kù)中所有表采用統(tǒng)一的校驗(yàn)集
  -主、從數(shù)據(jù)庫(kù)的字符集必須一致
  -前端程序字符集或者環(huán)境變量中的字符集，與數(shù)據(jù)庫(kù)、表的字符集必須一致

4、其他要求

 -不推薦使用外鍵，臨時(shí)表，視圖，自定義函數(shù)，存儲(chǔ)過(guò)程以及觸發(fā)器
  -SSD硬盤上，單表數(shù)據(jù)行數(shù)不能超過(guò)5000萬(wàn)或者存儲(chǔ)空間不得大于30GB
  -SAS硬盤上，單表數(shù)據(jù)行數(shù)不能超過(guò)2000萬(wàn)或者存儲(chǔ)空間不得大于15GB
  -上線前DBA必須根據(jù)1年內(nèi)的業(yè)務(wù)訪問(wèn)量和數(shù)據(jù)增長(zhǎng)量，給出庫(kù)、表的擴(kuò)展方案

二、SQL編寫

1、select

 -SELECT語(yǔ)句必須指定具體字段名稱，禁止寫成“select *”
  -SELECT語(yǔ)句禁止使用UNION，推薦使用UNION ALL，并且UNION子句個(gè)數(shù)限制在5個(gè)以內(nèi)

2、DML

 -INSERT語(yǔ)句必須指定具體的字段名稱，不要寫成INSERT VALUES(……)形式 
  -SQL語(yǔ)句在程序中傳入的參數(shù)值類型必須與字段在數(shù)據(jù)庫(kù)中的類型相同

3、多表聯(lián)合查詢

 -多表連接查詢推薦使用別名，且SELECT列表中要用別名引用字段，數(shù)據(jù)庫(kù).表格式，如“select a.cid  from iknow_qb. tblreply a where …”
  -生產(chǎn)系統(tǒng)中，單個(gè)查詢中不推薦將3張表以上（包括3張表）做連接
  -生產(chǎn)系統(tǒng)中，強(qiáng)烈不推薦使用外關(guān)聯(lián)，包括左外關(guān)聯(lián)，右外關(guān)聯(lián)和全外關(guān)聯(lián)
  -在多表連接的查詢中，驅(qū)動(dòng)表須要選擇結(jié)果集較小的表
  -禁止寫成多層子查詢嵌套的SQL語(yǔ)句，推薦改寫成表順序連接的格式
  -盡量不要在INSERT|UPDATE|DELETE|REPLACE語(yǔ)句中進(jìn)行多表連接操作

4、事務(wù)

 -事務(wù)中INSERT|UPDATE|DELETE|REPLACE語(yǔ)句操作的行數(shù)控制在2000，以及WHERE子句中IN列表的傳參個(gè)數(shù)控制在2000
  -批量操作數(shù)據(jù)時(shí)，需要控制事務(wù)處理間隔時(shí)間，進(jìn)行必要的sleep，具體值由DBA給出，并且程序必須有中斷處理能力
  -對(duì)于有auto_increment屬性字段的表的插入操作，并發(fā)需要控制在200/s以內(nèi)
  -SQL級(jí)別/事務(wù)級(jí)別/主從數(shù)據(jù)庫(kù)中的表存儲(chǔ)引擎類型要一致，存儲(chǔ)引擎混合使用會(huì)導(dǎo)致主從數(shù)據(jù)不一致或主從同步中斷
  -對(duì)于同步延遲不敏感的只讀查詢，必須放到從庫(kù)上執(zhí)行；對(duì)于同步延遲敏感的只讀查詢，可以放到主庫(kù)上執(zhí)行
  -前端程序中盡量不要使用set語(yǔ)句，包括set names、set sql_mode和set isolation_level等

5、表掃描方式：

 -SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的條件必需使用索引查找
  -生產(chǎn)數(shù)據(jù)庫(kù)中強(qiáng)烈不推薦大表上發(fā)生全表掃描，但對(duì)于5000行以下的靜態(tài)表可以全表掃描
  -業(yè)務(wù)中大表全表掃描和全表導(dǎo)出（dump）推薦放在備份庫(kù)或者線下讀庫(kù)中進(jìn)行
  -WHERE 子句中禁止只使用全模糊的LIKE條件進(jìn)行查找(如like '%aj%')，必須有其他查詢條件
  -WHERE子句中的索引列或組合索引前導(dǎo)列上不能使用函數(shù)

6、排序和分組

 -有distinct、order by和group by子句的查詢，中間結(jié)果集限制10000行以內(nèi)
  -對(duì)于大結(jié)果集（中間結(jié)果集超過(guò)10000行）的排序、分組放到程序端實(shí)現(xiàn)

7、其他要求

 -單個(gè)SQL語(yǔ)句的大小限制在5MB以內(nèi)
  -生產(chǎn)數(shù)據(jù)庫(kù)中SQL語(yǔ)句的中間結(jié)果集和最終結(jié)果集必須限制在5MB以內(nèi)
  -生產(chǎn)數(shù)據(jù)庫(kù)中SQL語(yǔ)句禁止使用提示，如force index，ignore index，straight_join，sql_no_cache等
  -禁止使用全文檢索功能
  -禁止使用事件（EVENT）功能
  -程序中不要使用或操作mysql庫(kù)和test庫(kù)，禁止創(chuàng)建test或以test開頭的庫(kù)
  -禁止在mysql中使用用戶自定義變量
  -線上數(shù)據(jù)庫(kù)中不要進(jìn)行業(yè)務(wù)的實(shí)時(shí)統(tǒng)計(jì)或者匯總等計(jì)算操作，可導(dǎo)出后利用其它工具或者在線下備份庫(kù)中完成
  -減少與數(shù)據(jù)庫(kù)的交互次數(shù) 
        INSERT ... ON DUPLICATE KEY UPDATE 
        REPLACE  INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
        UPDATE … WHERE ID IN(A,B,C,…)
  -不使用負(fù)向查詢，例如 not in，!= ，not like
  -不在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算 
  -不使用%前導(dǎo)的查詢，例如like “%abc”
  -避免大表數(shù)據(jù)類型間的隱式轉(zhuǎn)換(這個(gè)經(jīng)常出性能問(wèn)題)會(huì)導(dǎo)致索引失效，例如數(shù)字轉(zhuǎn)字符串

三、MySQL相關(guān)特點(diǎn)介紹

1、MySQL對(duì)SQL的處理特點(diǎn)

 -SQL請(qǐng)求處理只能使用一個(gè)核
  -沒(méi)有SQL編譯緩存,SQL存儲(chǔ)過(guò)程都是硬解析
  -索引上不支持運(yùn)算對(duì)比
  -大多情況下一個(gè)Query只能使用一個(gè)索引
  -不支持Hash jion(MariaDB目前支持)
  -基于線程的對(duì)外服務(wù)模型(連接數(shù)太高,性能下降嚴(yán)重)
  -子查詢支持較差，外層查詢一般走不了索引

2、MySQL支持的存儲(chǔ)大小

 -單個(gè)表空間64T, 每個(gè)表只有一個(gè)表空間,也就是每個(gè)單表最大64T
  -Innodb Logfile 加起來(lái)不能超過(guò)512G
  -每行大小限制65535 byte 
  -每個(gè)表最多1027個(gè)字段
  -每個(gè)表最多64個(gè)普通索引

3、MySQL生產(chǎn)參考指標(biāo)

 -單實(shí)例最好不要超過(guò)1T, 周邊LOG除外,最大不建議超過(guò)5T
  -一般的OLTP單表建議最大不要超過(guò)10G 
  -通常在有buffer命中的情況下:
        Select 可以達(dá)到3-6W/S
        Insert 在聚集索引連續(xù)的情況可以到2w-3W/S
        在聚集索引不連續(xù)的情況下有可能也就是200-300/S
        UPDATE數(shù)據(jù)在內(nèi)存的情況下可以達(dá)到3K/S
        DELETE數(shù)據(jù)在內(nèi)存的情況下可以達(dá)到1k/s,有可能更少
  -數(shù)據(jù)庫(kù)的瓶頸： IO能力 ,想辦法用順序IO,減少隨機(jī)IO

四、建表審核

建庫(kù)或者建表，需要提前找DBA評(píng)估建表語(yǔ)句，并填寫表及SQL審核模板：

SQL審核模板.doc

五、容量評(píng)估

1、容量評(píng)估概述
        所有的數(shù)據(jù)庫(kù)上線：新建集群、新建數(shù)據(jù)庫(kù)、新建表，都需要提前進(jìn)行容量評(píng)估，防止后續(xù)因容量問(wèn)題而又對(duì)已上線的業(yè)務(wù)進(jìn)行調(diào)整、擴(kuò)容、遷移等操作，從而對(duì)線上業(yè)務(wù)造成影響。容量包括：訪問(wèn)量(讀寫)、數(shù)據(jù)及增長(zhǎng)量、磁盤空間容量. 

2、表容量
        表容量主要從表的 記錄數(shù)、平均長(zhǎng)度、增長(zhǎng)量、讀寫量、總大小量進(jìn)行評(píng)估。一般對(duì)于OLTP的表，建議單表不要超過(guò)2000W行數(shù)據(jù)量，總大小15G以內(nèi)。訪問(wèn)量：?jiǎn)伪碜x寫量在1600/s以內(nèi)。
        對(duì)于單表數(shù)據(jù)量上百萬(wàn)的表，每行記錄長(zhǎng)度不要過(guò)長(zhǎng)，不要和text、blob等字段類型放在同一個(gè)表中。(MySQL數(shù)據(jù)頁(yè)大小為16K，每行記錄越長(zhǎng)，每個(gè)數(shù)據(jù)頁(yè)存儲(chǔ)的記錄數(shù)就越少，因此在對(duì)數(shù)據(jù)進(jìn)行檢索時(shí)，會(huì)產(chǎn)生更多的IO) 

3、實(shí)例容量
        MySQL是基于線程的服務(wù)模型，因此在一些并發(fā)較高的場(chǎng)景下，單實(shí)例并不能充分利用服務(wù)器的CPU資源，吞吐量反而會(huì)卡在mysql層，特別是對(duì)于mysql5.5版本。在mysql 5.6版本中 做了很大優(yōu)化，而且percona 版本有thread pool ，可以充分應(yīng)對(duì)高并發(fā)場(chǎng)景下CPU上下文切換消耗過(guò)高的問(wèn)題。
        單實(shí)例QPS吞吐量一般控制在20000/s以內(nèi)，寫入量還需考慮從庫(kù)延遲問(wèn)題，對(duì)于mysql5.6版本可以考慮進(jìn)行分庫(kù)后再分表，充分利用5.6版本基于庫(kù)級(jí)別的多線程復(fù)制，從而提高寫入的吞吐量。 

4、磁盤空間
        服務(wù)器一般會(huì)承載多個(gè)數(shù)據(jù)庫(kù)實(shí)例，因此在各個(gè)實(shí)例上線前，需要對(duì)各個(gè)實(shí)例進(jìn)行 數(shù)據(jù)量的評(píng)估，以及1-2年內(nèi) 主要的幾個(gè)大表的增長(zhǎng)量情況，對(duì)數(shù)據(jù)量的評(píng)估，盡量精確到每個(gè)字段。對(duì)于增長(zhǎng)量不是特別快的業(yè)務(wù)(半年就翻倍的情況)，建議1-2年的數(shù)據(jù)量，最終占磁盤使用率的70%以內(nèi)。同時(shí)，對(duì)于一些數(shù)據(jù)增長(zhǎng)較快，可以考慮使用大的慢盤進(jìn)行數(shù)據(jù)歸檔。 

關(guān)于MySQL準(zhǔn)入規(guī)范有哪些就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。

分享名稱：MySQL準(zhǔn)入規(guī)范有哪些
當(dāng)前鏈接：http://sd-ha.com/article20/pepejo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計(jì)、定制開發(fā)、小程序開發(fā)、網(wǎng)站設(shè)計(jì)、Google、企業(yè)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)