主頁(yè) > 知識(shí)庫(kù) > 超詳細(xì)MySQL使用規(guī)范分享

超詳細(xì)MySQL使用規(guī)范分享

熱門(mén)標(biāo)簽:咸陽(yáng)防封電銷(xiāo)卡 臨沂做地圖標(biāo)注 廣東400企業(yè)電話(huà)申請(qǐng)流程 地圖標(biāo)注客戶(hù)付款 石家莊400電話(huà)辦理公司 宜賓全自動(dòng)外呼系統(tǒng)廠(chǎng)家 新鄉(xiāng)智能外呼系統(tǒng)好處 申請(qǐng)400電話(huà)電話(huà)價(jià)格 許昌外呼增值業(yè)務(wù)線(xiàn)路

最近涉及數(shù)據(jù)庫(kù)相關(guān)操作較多,公司現(xiàn)有規(guī)范也不是太全面,就根據(jù)網(wǎng)上各路大神的相關(guān)規(guī)范,整理了一些自用的規(guī)范用法,萬(wàn)望指正。

數(shù)據(jù)庫(kù)環(huán)境

dev: 開(kāi)發(fā)環(huán)境

開(kāi)發(fā)可讀寫(xiě),可修改表結(jié)構(gòu)。開(kāi)發(fā)人員可以修改表結(jié)構(gòu),可以隨意修改其中的數(shù)據(jù)但是需要保證不影響其他開(kāi)發(fā)同事。

test: 測(cè)試環(huán)境

開(kāi)發(fā)可讀寫(xiě),開(kāi)發(fā)人員可以通過(guò)工具修改表結(jié)構(gòu)。

online: 線(xiàn)上環(huán)境

開(kāi)發(fā)人員不允許直接在線(xiàn)上環(huán)境進(jìn)行數(shù)據(jù)庫(kù)操作,如果需要操作必須找DBA進(jìn)行操作并進(jìn)行相應(yīng)記錄,禁止進(jìn)行壓力測(cè)試。

重點(diǎn)的問(wèn)題,各個(gè)環(huán)境的mysql服務(wù)器對(duì)應(yīng)的用戶(hù)權(quán)限,一定要做到權(quán)限劃分明確,有辨識(shí)度,能具體區(qū)分業(yè)務(wù)場(chǎng)景等。

命名規(guī)范

基本命名規(guī)則

  • 使用有意義的英文詞匯,詞匯中間以下劃線(xiàn)分隔。(不要用拼音)
  • 只能使用英文字母,數(shù)字,下劃線(xiàn),并以英文字母開(kāi)頭。
  • 庫(kù)、表、字段全部采用小寫(xiě),不要使用駝峰式命名。
  • 避免用ORACLE、MySQL的保留字,如desc,關(guān)鍵字如index。
  • 命名禁止超過(guò)32個(gè)字符,須見(jiàn)名之意,建議使用名詞不是動(dòng)詞
  • 數(shù)據(jù)庫(kù),數(shù)據(jù)表一律使用前綴
  • 臨時(shí)庫(kù)、表名必須以tmp為前綴,并以日期為后綴
  • 備份庫(kù)、表必須以bak為前綴,并以日期為后綴

為什么庫(kù)、表、字段全部采用小寫(xiě)?

在 MySQL 中,數(shù)據(jù)庫(kù)和表對(duì)就于那些目錄下的目錄和文件。因而,操作系統(tǒng)的敏感性決定數(shù)據(jù)庫(kù)和表命名的大小寫(xiě)敏感。

  • Windows下是不區(qū)分大小寫(xiě)的。
  • Linux下大小寫(xiě)規(guī)則
  • 數(shù)據(jù)庫(kù)名與表名是嚴(yán)格區(qū)分大小寫(xiě)的;
  • 表的別名是嚴(yán)格區(qū)分大小寫(xiě)的;
  • 列名與列的別名在所有的情況下均是忽略大小寫(xiě)的;
  • 變量名也是嚴(yán)格區(qū)分大小寫(xiě)的;
  • 如果已經(jīng)設(shè)置了駝峰式的命名如何解決?需要在MySQL的配置文件my.ini中增加 lower_case_table_names = 1即可。

表命名

同一個(gè)模塊的表盡可能使用相同的前綴,表名稱(chēng)盡可能表達(dá)含義。所有日志表均以 log_ 開(kāi)頭

字段命名

  • 表達(dá)其實(shí)際含義的英文單詞或簡(jiǎn)寫(xiě)。布爾意義的字段以is_作為前綴,后接動(dòng)詞過(guò)去分詞。
  • 各表之間相同意義的字段應(yīng)同名。各表之間相同意義的字段,以去掉模塊前綴的表名_字段名命名。
  • 外鍵字段用表名_字段名表示其關(guān)聯(lián)關(guān)系。
  • 表的主鍵一般都約定成為id,自增類(lèi)型,是別的表的外鍵均使用xxx_id的方式來(lái)表明。

索引命名

  • 非唯一索引必須按照“idx_字段名稱(chēng)_字段名稱(chēng)[_字段名]”進(jìn)行命名
  • 唯一索引必須按照“uniq_字段名稱(chēng)_字段名稱(chēng)[_字段名]”進(jìn)行命名

約束命名

  • 主鍵約束:pk_表名稱(chēng)。
  • 唯一約束:uk_表名稱(chēng)_字段名。(應(yīng)用中需要同時(shí)有唯一性檢查邏輯。)

表設(shè)計(jì)規(guī)范

表引擎取決于實(shí)際應(yīng)用場(chǎng)景;日志及報(bào)表類(lèi)表建議用myisam,與交易,審核,金額相關(guān)的表建議用innodb引擎。如無(wú)說(shuō)明,建表時(shí)一律采用innodb引擎

默認(rèn)使用utf8mb4字符集,數(shù)據(jù)庫(kù)排序規(guī)則使用utf8mb4_general_ci,(由于數(shù)據(jù)庫(kù)定義使用了默認(rèn),數(shù)據(jù)表可以不再定義,但為保險(xiǎn)起見(jiàn),建議都寫(xiě)上

為什么字符集不選擇utf8,排序規(guī)則不使用utf8_general_ci

采用utf8編碼的MySQL無(wú)法保存占位是4個(gè)字節(jié)的Emoji表情。為了使后端的項(xiàng)目,全面支持客戶(hù)端輸入的Emoji表情,升級(jí)編碼為utf8mb4是最佳解決方案。對(duì)于JDBC連接串設(shè)置了characterEncoding為utf8或者做了上述配置仍舊無(wú)法正常插入emoji數(shù)據(jù)的情況,需要在代碼中指定連接的字符集為utf8mb4。

所有表、字段均應(yīng)用 comment 列屬性來(lái)描述此表、字段所代表的真正含義,如枚舉值則建議將該字段中使用的內(nèi)容都定義出來(lái)。

如無(wú)說(shuō)明,表中的第一個(gè)id字段一定是主鍵且為自動(dòng)增長(zhǎng),禁止在非事務(wù)內(nèi)作為上下文作為條件進(jìn)行數(shù)據(jù)傳遞。禁止使用varchar類(lèi)型作為主鍵語(yǔ)句設(shè)計(jì)。

如無(wú)說(shuō)明,表必須包含create_time和modify_time字段,即表必須包含記錄創(chuàng)建時(shí)間和修改時(shí)間的字段

如無(wú)說(shuō)明,表必須包含is_del,用來(lái)標(biāo)示數(shù)據(jù)是否被刪除,原則上數(shù)據(jù)庫(kù)數(shù)據(jù)不允許物理刪除。

  • 用盡量少的存儲(chǔ)空間來(lái)存數(shù)一個(gè)字段的數(shù)據(jù)
  • 能用int的就不用char或者varchar
  • 能用tinyint的就不用int
  • 使用UNSIGNED存儲(chǔ)非負(fù)數(shù)值。
  • 不建議使用ENUM、SET類(lèi)型,使用TINYINT來(lái)代替
  • 使用短數(shù)據(jù)類(lèi)型,比如取值范圍為0-80時(shí),使用TINYINT UNSIGNED
  • 存儲(chǔ)精確浮點(diǎn)數(shù)必須使用DECIMAL替代FLOAT和DOUBLE
  • 時(shí)間字段,除特殊情況一律采用int來(lái)記錄unix_timestamp
  • 存儲(chǔ)年使用YEAR類(lèi)型。
  • 存儲(chǔ)日期使用DATE類(lèi)型。
  • 存儲(chǔ)時(shí)間(精確到秒)建議使用TIMESTAMP類(lèi)型,因?yàn)門(mén)IMESTAMP使用4字節(jié),DATETIME使用8個(gè)字節(jié)。
  • 建議使用INT UNSIGNED存儲(chǔ)IPV4。
  • 盡可能不使用TEXT、BLOB類(lèi)型
  • 禁止在數(shù)據(jù)庫(kù)中使用VARBINARY、BLOB存儲(chǔ)圖片、文件等。建議使用其他方式存儲(chǔ)(TFS/SFS),MySQL只保存指針信息。
  • 單條記錄大小禁止超過(guò)8k(列長(zhǎng)度(中文)_3(UTF8)+列長(zhǎng)度(英文)_1)

datetime與timestamp有什么不同?

相同點(diǎn):

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

不同點(diǎn):

TIMESTAMP

  • 4個(gè)字節(jié)儲(chǔ)存,時(shí)間范圍:1970-01-01 08:00:01 ~ 2038-01-19 11:14:07值以UTC格式保存,涉及時(shí)區(qū)轉(zhuǎn)化 ,存儲(chǔ)時(shí)對(duì)當(dāng)前的時(shí)區(qū)進(jìn)行轉(zhuǎn)換,檢索時(shí)再轉(zhuǎn)換回當(dāng)前的時(shí)區(qū)。
  • datetime8個(gè)字節(jié)儲(chǔ)存,時(shí)間范圍:1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 實(shí)際格式儲(chǔ)存,與時(shí)區(qū)無(wú)關(guān)

如何使用TIMESTAMP的自動(dòng)賦值屬性?

將當(dāng)前時(shí)間作為ts的默認(rèn)值:ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。當(dāng)行更新時(shí),更新ts的值:ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

可以將1和2結(jié)合起來(lái):ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

如何使用INT UNSIGNED存儲(chǔ)ip?

使用INT UNSIGNED而不是char(15)來(lái)存儲(chǔ)ipv4地址,通過(guò)MySQL函數(shù)inet_ntoa和inet_aton來(lái)進(jìn)行轉(zhuǎn)化。Ipv6地址目前沒(méi)有轉(zhuǎn)化函數(shù),需要使用DECIMAL或者兩個(gè)bigINT來(lái)存儲(chǔ)。

  • 如無(wú)備注,所有字段都設(shè)置NOT NULL,并設(shè)置默認(rèn)值;
  • 禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)明文密碼
  • 如無(wú)備注,所有的布爾值字段,如is_hot、is_deleted,都必須設(shè)置一個(gè)默認(rèn)值,并設(shè)為0;
  • 如無(wú)備注,排序字段order_id在程序中默認(rèn)使用降序排列;
  • 整形定義中不添加長(zhǎng)度,比如使用INT,而不是INT[4]

INT[M],M值代表什么含義?

注意數(shù)值類(lèi)型括號(hào)后面的數(shù)字只是表示寬度而跟存儲(chǔ)范圍沒(méi)有關(guān)系。很多人他們認(rèn)為INT(4)和INT(10)其取值范圍分別是 (-9999到9999)和(-9999999999到9999999999),這種理解是錯(cuò)誤的。其實(shí)對(duì)整型中的 M值與 ZEROFILL 屬性結(jié)合使用時(shí)可以實(shí)現(xiàn)列值等寬。不管INT[M]中M值是多少,其取值范圍還是 (-2147483648到2147483647 有符號(hào)時(shí)),(0到4294967295無(wú)符號(hào)時(shí))。

顯示寬度并不限制可以在列內(nèi)保存的值的范圍,也不限制超過(guò)列的指定寬度的值的顯示。當(dāng)結(jié)合可選擴(kuò)展屬性ZEROFILL使用時(shí)默認(rèn)補(bǔ)充的空格用零代替。例如:對(duì)于聲明為INT(5) ZEROFILL的列,值4檢索為00004。請(qǐng)注意如果在整數(shù)列保存超過(guò)顯示寬度的一個(gè)值,當(dāng)MySQL為復(fù)雜聯(lián)接生成臨時(shí)表時(shí)會(huì)遇到問(wèn)題,因?yàn)樵谶@些情況下MySQL相信數(shù)據(jù)適合原列寬度,如果為一個(gè)數(shù)值列指定ZEROFILL, MySQL自動(dòng)為該列添加UNSIGNED屬性。

使用VARBINARY存儲(chǔ)大小寫(xiě)敏感的變長(zhǎng)字符串

什么時(shí)候用CHAR,什么時(shí)候用VARCHAR?

CHAR和VARCHAR類(lèi)型類(lèi)似,但它們保存和檢索的方式不同。它們的最大長(zhǎng)度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR類(lèi)型聲明的長(zhǎng)度表示你想要保存的最大字符數(shù)。例如,CHAR(30)可以占用30個(gè)字符。

CHAR列的長(zhǎng)度固定為創(chuàng)建表時(shí)聲明的長(zhǎng)度。長(zhǎng)度可以為從0到255的任何值。當(dāng)保存CHAR值時(shí),在它們的右邊填充空格以達(dá)到指定的長(zhǎng)度。當(dāng)檢索到CHAR值時(shí),尾部的空格被刪除掉。在存儲(chǔ)或檢索過(guò)程中不進(jìn)行大小寫(xiě)轉(zhuǎn)換。

VARCHAR列中的值為可變長(zhǎng)字符串。長(zhǎng)度可以指定為0到65,535之間的值。(VARCHAR的最大有效長(zhǎng)度由最大行大小和使用的字符集確定。整體最大長(zhǎng)度是65,532字節(jié))。同CHAR對(duì)比,VARCHAR值保存時(shí)只保存需要的字符數(shù),另加一個(gè)字節(jié)來(lái)記錄長(zhǎng)度(如果列聲明的長(zhǎng)度超過(guò)255,則使用兩個(gè)字節(jié))。VARCHAR值保存時(shí)不進(jìn)行填充。當(dāng)值保存和檢索時(shí)尾部的空格仍保留,符合標(biāo)準(zhǔn)SQL。

char適合存儲(chǔ)用戶(hù)密碼的MD5哈希值,它的長(zhǎng)度總是一樣的。對(duì)于經(jīng)常改變的值,char也好于varchar,因?yàn)楣潭ㄩL(zhǎng)度的行不容易產(chǎn)生碎片,對(duì)于很短的列,char的效率也高于varchar。char(1)字符串對(duì)于單字節(jié)字符集只會(huì)占用一個(gè)字節(jié),但是varchar(1)則會(huì)占用2個(gè)字節(jié),因?yàn)?個(gè)字節(jié)用來(lái)存儲(chǔ)長(zhǎng)度信息。

索引設(shè)計(jì)規(guī)范

MySQL的查詢(xún)速度依賴(lài)良好的索引設(shè)計(jì),因此索引對(duì)于高性能至關(guān)重要。合理的索引會(huì)加快查詢(xún)速度(包括UPDATE和DELETE的速度,MySQL會(huì)將包含該行的page加載到內(nèi)存中,然后進(jìn)行UPDATE或者DELETE操作),不合理的索引會(huì)降低速度。MySQL索引查找類(lèi)似于新華字典的拼音和部首查找,當(dāng)拼音和部首索引不存在時(shí),只能通過(guò)一頁(yè)一頁(yè)的翻頁(yè)來(lái)查找。當(dāng)MySQL查詢(xún)不能使用索引時(shí),MySQL會(huì)進(jìn)行全表掃描,會(huì)消耗大量的IO。索引的用途:去重、加速定位、避免排序、覆蓋索引。

什么是覆蓋索引

InnoDB存儲(chǔ)引擎中,secondary index(非主鍵索引)中沒(méi)有直接存儲(chǔ)行地址,存儲(chǔ)主鍵值。如果用戶(hù)需要查詢(xún)secondary index中所不包含的數(shù)據(jù)列時(shí),需要先通過(guò)secondary index查找到主鍵值,然后再通過(guò)主鍵查詢(xún)到其他數(shù)據(jù)列,因此需要查詢(xún)兩次。覆蓋索引的概念就是查詢(xún)可以通過(guò)在一個(gè)索引中完成,覆蓋索引效率會(huì)比較高,主鍵查詢(xún)是天然的覆蓋索引。合理的創(chuàng)建索引以及合理的使用查詢(xún)語(yǔ)句,當(dāng)使用到覆蓋索引時(shí)可以獲得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主鍵,適當(dāng)時(shí)候可以將索引添加為index(uid,email),以獲得性能提升。

索引的基本規(guī)范

  • 索引數(shù)量控制,單張表中索引數(shù)量不超過(guò)5個(gè),單個(gè)索引中的字段數(shù)不超過(guò)5個(gè)。
  • 綜合評(píng)估數(shù)據(jù)密度和分布
  • 考慮查詢(xún)和更新比例

為什么一張表中不能存在過(guò)多的索引?

InnoDB的secondary index使用b+tree來(lái)存儲(chǔ),因此在UPDATE、DELETE、INSERT的時(shí)候需要對(duì)b+tree進(jìn)行調(diào)整,過(guò)多的索引會(huì)減慢更新的速度。

對(duì)字符串使用前綴索引,前綴索引長(zhǎng)度不超過(guò)8個(gè)字符,建議優(yōu)先考慮前綴索引,必要時(shí)可添加偽列并建立索引。

不要索引blob/text等字段,不要索引大型字段,這樣做會(huì)讓索引占用太多的存儲(chǔ)空間

什么是前綴索引?

前綴索引說(shuō)白了就是對(duì)文本的前幾個(gè)字符(具體是幾個(gè)字符在建立索引時(shí)指定)建立索引,這樣建立起來(lái)的索引更小,所以查詢(xún)更快。前綴索引能有效減小索引文件的大小,提高索引的速度。但是前綴索引也有它的壞處:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前綴索引,也不能把它們用作覆蓋索引(Covering Index)。

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

主鍵準(zhǔn)則

  • 表必須有主鍵
  • 不使用更新頻繁的列
  • 盡量不選擇字符串列
  • 不使用UUID MD5 HASH
  • 默認(rèn)使用非空的唯一鍵
  • 建議選擇自增或發(fā)號(hào)器

重要的SQL必須被索引,核心SQL優(yōu)先考慮覆蓋索索引

  • UPDATE、DELETE語(yǔ)句的WHERE條件列
  • ORDER BY、GROUP BY、DISTINCT的字段
  • 多表JOIN的字段

區(qū)分度最大的字段放在前面

  • 選擇篩選性更優(yōu)的字段放在最前面,比如單號(hào)、userid等,type,status等篩選性一般不建議放在最前面
  • 索引根據(jù)左前綴原則,當(dāng)建立一個(gè)聯(lián)合索引(a,b,c),則查詢(xún)條件里面只有包含(a)或(a,b)或(a,b,c)的時(shí)候才能走索引,(a,c)作為條件的時(shí)候只能使用到a列索引,所以這個(gè)時(shí)候要確定a的返回列一定不能太多,不然語(yǔ)句設(shè)計(jì)就不合理,(b,c)則不能走索引
  • 合理創(chuàng)建聯(lián)合索引(避免冗余),(a,b,c) 相當(dāng)于 (a) 、(a,b) 、(a,b,c)

索引禁忌

  • 不在低基數(shù)列上建立索引,例如“性別”
  • 不在索引列進(jìn)行數(shù)學(xué)運(yùn)算和函數(shù)運(yùn)算
  • 不要索引常用的小型表
  • 盡量不使用外鍵
  • 外鍵用來(lái)保護(hù)參照完整性,可在業(yè)務(wù)端實(shí)現(xiàn)
  • 對(duì)父表和子表的操作會(huì)相互影響,降低可用性
  • INNODB本身對(duì)online DDL的限制

MYSQL 中索引的限制

MYISAM 存儲(chǔ)引擎索引長(zhǎng)度的總和不能超過(guò) 1000 字節(jié)
BLOB 和 TEXT 類(lèi)型的列只能創(chuàng)建前綴索引
MYSQL 目前不支持函數(shù)索引
使用不等于 (!= 或者 >) 的時(shí)候, MYSQL 無(wú)法使用索引。
過(guò)濾字段使用函數(shù)運(yùn)算 (如 abs (column)) 后, MYSQL無(wú)法使用索引。
join語(yǔ)句中join條件字段類(lèi)型不一致的時(shí)候MYSQL無(wú)法使用索引
使用 LIKE 操作的時(shí)候如果條件以通配符開(kāi)始 (如 ‘%abc…')時(shí), MYSQL無(wú)法使用索引。
使用非等值查詢(xún)的時(shí)候, MYSQL 無(wú)法使用 Hash 索引。

語(yǔ)句設(shè)計(jì)規(guī)范

使用預(yù)編譯語(yǔ)句

  • 只傳參數(shù),比傳遞SQL語(yǔ)句更高效
  • 一次解析,多次使用
  • 降低SQL注入概率

避免隱式轉(zhuǎn)換

會(huì)導(dǎo)致索引失效

充分利用前綴索引

  • 必須是最左前綴
  • 不可能同時(shí)用到兩個(gè)范圍條件
  • 不使用%前導(dǎo)的查詢(xún),如like “%ab”

不使用負(fù)向查詢(xún),如not in/like

  • 無(wú)法使用索引,導(dǎo)致全表掃描
  • 全表掃描導(dǎo)致buffer pool利用率降低

避免使用存儲(chǔ)過(guò)程、觸發(fā)器、UDF、events等

  • 讓數(shù)據(jù)庫(kù)做最擅長(zhǎng)的事
  • 降低業(yè)務(wù)耦合度,為sacle out、sharding留有余地
  • 避開(kāi)BUG

避免使用大表的JOIN

MySQL最擅長(zhǎng)的是單表的主鍵/二級(jí)索引查詢(xún)
JOIN消耗較多內(nèi)存,產(chǎn)生臨時(shí)表

避免在數(shù)據(jù)庫(kù)中進(jìn)行數(shù)學(xué)運(yùn)算

  • MySQL不擅長(zhǎng)數(shù)學(xué)運(yùn)算和邏輯判斷
  • 無(wú)法使用索引

減少與數(shù)據(jù)庫(kù)的交互次數(shù)

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

合理的使用分頁(yè)

限制分頁(yè)展示的頁(yè)數(shù)只能點(diǎn)擊上一頁(yè)、下一頁(yè)采用延遲關(guān)聯(lián)

如何正確的使用分頁(yè)?

假如有類(lèi)似下面分頁(yè)語(yǔ)句:SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里對(duì)LIMIT OFFSET的處理方式是取出OFFSET+LIMIT的所有數(shù)據(jù),然后去掉OFFSET,返回底部的LIMIT。所以,在OFFSET數(shù)值較大時(shí),MySQL的查詢(xún)性能會(huì)非常低。可以使用id > n 的方式進(jìn)行解決:

使用id > n 的方式有局限性,對(duì)于id不連續(xù)的問(wèn)題,可以通過(guò)翻頁(yè)的時(shí)候同時(shí)傳入最后一個(gè)id方式來(lái)解決。

http://example.com/page.php?last=100 
select * from table where id100 order by id desc limit 10 
//上一頁(yè) 
 http://example.com/page.php?first=110 
select * from table where id>110 order by id desc limit 10 

這種方式比較大的缺點(diǎn)是,如果在瀏覽中有插入/刪除操作,翻頁(yè)不會(huì)更新,而總頁(yè)數(shù)可能仍然是根據(jù)新的count(*) 來(lái)計(jì)算,最終可能會(huì)產(chǎn)生某些記錄訪(fǎng)問(wèn)不到。為了修補(bǔ)這個(gè)問(wèn)題,可以繼續(xù)引入當(dāng)前頁(yè)碼以及在上次翻頁(yè)以后是否有插入/刪除等影響總記錄數(shù)的操作并進(jìn)行緩存

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

EXPLAIN語(yǔ)句(在MySQL客戶(hù)端中執(zhí)行)可以獲得MySQL如何執(zhí)行SELECT語(yǔ)句的信息。通過(guò)對(duì)SELECT語(yǔ)句執(zhí)行EXPLAIN,可以知曉MySQL執(zhí)行該SELECT語(yǔ)句時(shí)是否使用了索引、全表掃描、臨時(shí)表、排序等信息。盡量避免MySQL進(jìn)行全表掃描、使用臨時(shí)表、排序等。詳見(jiàn)官方文檔。

用union all而不是union

union all與 union有什么區(qū)別?

union和union all關(guān)鍵字都是將兩個(gè)結(jié)果集合并為一個(gè),但這兩者從使用和效率上來(lái)說(shuō)都有所不同。

union在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄,所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算,刪除重復(fù)的記錄再返回結(jié)果。如:

select * from test_union1 
union select * from test_union2 

這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果,再用排序空間進(jìn)行排序刪除重復(fù)的記錄,最后返回結(jié)果集,如果表數(shù)據(jù)量大的話(huà)可能會(huì)導(dǎo)致用磁盤(pán)進(jìn)行排序。

而union all只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。這樣,如果返回的兩個(gè)結(jié)果集中有重復(fù)的數(shù)據(jù),那么返回的結(jié)果集就會(huì)包含重復(fù)的數(shù)據(jù)了。

從效率上說(shuō),union all要比union快很多,所以,如果可以確認(rèn)合并的兩個(gè)結(jié)果集中不包含重復(fù)的數(shù)據(jù)的話(huà),那么就使用union all,如下:

select * from test_union1 union all select * from test_union2 
  •  程序應(yīng)有捕獲SQL異常的處理機(jī)制
  • 禁止單條SQL語(yǔ)句同時(shí)更新多個(gè)表
  • 不使用select * ,SELECT語(yǔ)句只獲取需要的字段
  • 消耗CPU和IO、消耗網(wǎng)絡(luò)帶寬
  • 無(wú)法使用覆蓋索引
  • 減少表結(jié)構(gòu)變更帶來(lái)的影響
  • 因?yàn)榇?,select/join 可能生成臨時(shí)表
  • UPDATE、DELETE語(yǔ)句不使用LIMIT
  • INSERT語(yǔ)句必須顯式的指明字段名稱(chēng),不使用INSERT INTO table()
  • INSERT語(yǔ)句使用batch提交(INSERT INTO table VALUES(),(),()……),values的個(gè)數(shù)不超過(guò)500
  • 統(tǒng)計(jì)表中記錄數(shù)時(shí)使用COUNT(*),而不是COUNT(primary_key)和COUNT(1) 備注:僅針對(duì)Myisam
  • 數(shù)據(jù)更新建議使用二級(jí)索引先查詢(xún)出主鍵,再根據(jù)主鍵進(jìn)行數(shù)據(jù)更新
  • 禁止使用跨庫(kù)查詢(xún)
  • 禁止使用子查詢(xún),建議將子查詢(xún)轉(zhuǎn)換成關(guān)聯(lián)查詢(xún)
  • 針對(duì)varchar類(lèi)型字段的程序處理,請(qǐng)驗(yàn)證用戶(hù)輸入,不要超出其預(yù)設(shè)的長(zhǎng)度;

分表規(guī)范

單表一到兩年內(nèi)數(shù)據(jù)量超過(guò)500w或數(shù)據(jù)容量超過(guò)10G考慮分表,需提前考慮歷史數(shù)據(jù)遷移或應(yīng)用自行刪除歷史數(shù)據(jù),采用等量均衡分表或根據(jù)業(yè)務(wù)規(guī)則分表均可。要分表的數(shù)據(jù)表必須與DBA商量分表策略

  • 用HASH進(jìn)行散表,表名后綴使用十進(jìn)制數(shù),下標(biāo)從0開(kāi)始
  • 按日期時(shí)間分表需符合YYYY[MM][dd][HH]格式
  • 采用合適的分庫(kù)分表策略。例如千庫(kù)十表、十庫(kù)百表等
  • 禁止使用分區(qū)表,分區(qū)表對(duì)分區(qū)鍵有嚴(yán)格要,分區(qū)表在表變大后執(zhí)行DDL、SHARDING、單表恢復(fù)等都變得更加困難。
  • 拆分大字段和訪(fǎng)問(wèn)頻率低的字段,分離冷熱數(shù)據(jù)

行為規(guī)范

  • 批量導(dǎo)入、導(dǎo)出數(shù)據(jù)必須提前通知DBA協(xié)助觀(guān)察
  • 禁止在線(xiàn)上從庫(kù)執(zhí)行后臺(tái)管理和統(tǒng)計(jì)類(lèi)查詢(xún)
  • 禁止有super權(quán)限的應(yīng)用程序賬號(hào)存在
  • 產(chǎn)品出現(xiàn)非數(shù)據(jù)庫(kù)導(dǎo)致的故障時(shí)及時(shí)通知DBA協(xié)助排查
  • 推廣活動(dòng)或上線(xiàn)新功能必須提前通知DBA進(jìn)行流量評(píng)估
  • 數(shù)據(jù)庫(kù)數(shù)據(jù)丟失,及時(shí)聯(lián)系DBA進(jìn)行恢復(fù)
  • 對(duì)單表的多次alter操作必須合并為一次操作
  • 不在MySQL數(shù)據(jù)庫(kù)中存放業(yè)務(wù)邏輯
  • 重大項(xiàng)目的數(shù)據(jù)庫(kù)方案選型和設(shè)計(jì)必須提前通知DBA參與
  • 對(duì)特別重要的庫(kù)表,提前與DBA溝通確定維護(hù)和備份優(yōu)先級(jí)
  • 不在業(yè)務(wù)高峰期批量更新、查詢(xún)數(shù)據(jù)庫(kù)其他規(guī)范
  • 提交線(xiàn)上建表改表需求,必須詳細(xì)注明所有相關(guān)SQL語(yǔ)句

其他規(guī)范

日志類(lèi)數(shù)據(jù)不建議存儲(chǔ)在MySQL上,優(yōu)先考慮Hbase或OceanBase,如需要存儲(chǔ)請(qǐng)找DBA評(píng)估使用壓縮表存儲(chǔ)。

以上就是超詳細(xì)MySQL使用規(guī)范分享的詳細(xì)內(nèi)容,更多關(guān)于MySQL使用規(guī)范的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • MySQL 使用規(guī)范總結(jié)
  • MySQL數(shù)據(jù)庫(kù)使用規(guī)范總結(jié)
  • 老鳥(niǎo)帶你開(kāi)發(fā)專(zhuān)業(yè)規(guī)范的MySQL啟動(dòng)腳本
  • MySQL開(kāi)發(fā)規(guī)范與使用技巧總結(jié)
  • mysql數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范【推薦】
  • MySQL數(shù)據(jù)庫(kù)命名規(guī)范及約定
  • Mysql建表與索引使用規(guī)范詳解
  • MYSQL 數(shù)據(jù)庫(kù)命名與設(shè)計(jì)規(guī)范
  • 專(zhuān)業(yè)級(jí)的MySQL開(kāi)發(fā)設(shè)計(jì)規(guī)范及SQL編寫(xiě)規(guī)范

標(biāo)簽:北京 貴州 阜新 臺(tái)灣 鎮(zhèn)江 合肥 日照 鷹潭

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《超詳細(xì)MySQL使用規(guī)范分享》,本文關(guān)鍵詞  超,詳細(xì),MySQL,使用,規(guī)范,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《超詳細(xì)MySQL使用規(guī)范分享》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于超詳細(xì)MySQL使用規(guī)范分享的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章