免费在线a视频-免费在线观看a视频-免费在线观看大片影视大全-免费在线观看的视频-色播丁香-色播基地

MySQL使用規(guī)范手冊(cè),程序員必知必會(huì)

:2019年11月19日 IT牧場(chǎng)
分享到:

一個(gè)平臺(tái)或系統(tǒng)隨著時(shí)間的推移和用戶量的增多,數(shù)據(jù)庫(kù)操作往往會(huì)變慢;而在Java應(yīng)用開發(fā)中數(shù)據(jù)庫(kù)更是尤為重要,絕大多數(shù)情況下數(shù)據(jù)庫(kù)的性能決定了程序的性能,如若前期埋下的坑越多到后期數(shù)據(jù)庫(kù)就會(huì)成為...

來源:濤姐濤哥
cnblogs.com/taojietaoge/p/11533693.html

背景

一個(gè)平臺(tái)或系統(tǒng)隨著時(shí)間的推移和用戶量的增多,數(shù)據(jù)庫(kù)操作往往會(huì)變慢;而在Java應(yīng)用開發(fā)中數(shù)據(jù)庫(kù)更是尤為重要,絕大多數(shù)情況下數(shù)據(jù)庫(kù)的性能決定了程序的性能,如若前期埋下的坑越多到后期數(shù)據(jù)庫(kù)就會(huì)成為整個(gè)系統(tǒng)的瓶頸;因此,更規(guī)范化的使用MySQL在開發(fā)中是不可或缺的。

一、MySQL數(shù)據(jù)庫(kù)命名規(guī)范

1、數(shù)據(jù)庫(kù)所有表前綴均使用項(xiàng)目名稱首字母縮寫;

2、數(shù)據(jù)庫(kù)所有對(duì)象名稱均使用小寫字母,并且單詞之間通過下劃線分開;

3、數(shù)據(jù)庫(kù)所有對(duì)象名稱禁止使用MySQL保留字及關(guān)鍵字,涉及到關(guān)鍵字的SQL查詢需要將關(guān)鍵字用單引號(hào)括起來;

4、數(shù)據(jù)庫(kù)所有對(duì)象名稱不超過32個(gè)字符,并且命名要遵循見名知意原則;

5、數(shù)據(jù)庫(kù)臨時(shí)表必須以 pro_tmp_ 為前綴并且以日期 20190917 為后綴,備份表必須以 pro_bac 為前綴并以時(shí)間戳為后綴;(pro為項(xiàng)目名稱首字母縮寫)

6、數(shù)據(jù)庫(kù)所有存儲(chǔ)相同數(shù)據(jù)的列名和列類型必須保持一致。

二、MySQL數(shù)據(jù)庫(kù)基本設(shè)計(jì)規(guī)范

1、若無特殊說明,建表時(shí)一律采用Innodb存儲(chǔ)引擎。

選擇合適的引擎可以提高數(shù)據(jù)庫(kù)性能,如InnoDB和MyISAM,InnoDB和MyISAM是許多人在使用MySQL時(shí)最常用的兩個(gè)表類型,這兩個(gè)表類型各有優(yōu)劣,視具體應(yīng)用而定;

基本的差別為:MyISAM類型不支持事務(wù)處理等高級(jí)處理,而InnoDB類型支持;MyISAM類型的表強(qiáng)調(diào)的是性能,其執(zhí)行數(shù)度比InnoDB類型更快,但是不提供事務(wù)支持,而InnoDB提供事務(wù)支持以及外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能;

因此,其支持事務(wù)處理、支持外鍵、支持崩潰修復(fù)能力和并發(fā)控制是我們建表時(shí)首選的存儲(chǔ)引擎。

2、數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用UTF8

數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用utf8,若是有字段需要存儲(chǔ)emoji表情之類的,則將表或字段設(shè)置成utf8mb4;因?yàn)椋瑄tf8號(hào)稱萬國(guó)碼,其無需轉(zhuǎn)碼、無亂碼風(fēng)險(xiǎn)且節(jié)省空間,而utf8mb4又向下兼容utf8。

3、設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)所有表和字段必須添加注釋

使用Comment從句添加表和列的備注,或直接在數(shù)據(jù)庫(kù)連接工具的注釋欄添加注釋,從項(xiàng)目開始就進(jìn)行數(shù)據(jù)字典的維護(hù)。

使用Comment從句添加注釋如:

-- 1、創(chuàng)建表:

CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);

-- 2、添加表注釋:

Comment on table t1 is '個(gè)人信息';

-- 3、添加字段注釋:

comment on column t1.id is 'id';

comment on column t1.nameis '姓名';

comment on column t1.age is '年齡';

使用數(shù)據(jù)庫(kù)連接工具添加注釋:

4、單個(gè)表的數(shù)據(jù)量大小控制在500萬以內(nèi)

盡量控制單表數(shù)據(jù)量的大小,建議控制在500萬以內(nèi);500萬并不是MySQL數(shù)據(jù)庫(kù)的極限,但數(shù)據(jù)量太多不利于對(duì)表結(jié)構(gòu)進(jìn)行修改、備份和恢復(fù)數(shù)據(jù),適當(dāng)采用分庫(kù)分表等手段來控制單表數(shù)據(jù)量的大小。

5、使用MySQL分區(qū)表需謹(jǐn)慎

分區(qū)是將一個(gè)表的數(shù)據(jù)按照某種方式,比如按照時(shí)間上的月份,分成多個(gè)較小的,更容易管理的部分,但是邏輯上仍是一個(gè)表;分區(qū)表在物理上表現(xiàn)為多個(gè)文件,在邏輯上仍表現(xiàn)為同一個(gè)表,需要謹(jǐn)慎選擇分區(qū)鍵;跨分區(qū)查詢效率可能會(huì)更低,建議使用物理分區(qū)表等方式管理大數(shù)據(jù)。

6、盡量滿足冷熱數(shù)據(jù)分離,減小表等寬度

MySQL限制每個(gè)表最多存儲(chǔ)4096列,并且每一行數(shù)據(jù)的大小不超過65535字節(jié),為了減少磁盤IO線程的開銷,就要適當(dāng)控制表的寬度,因?yàn)楸碓綄挘驯硌b載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大,就會(huì)消耗更多的IO線程;除此之外,為了保證熱數(shù)據(jù)的內(nèi)存緩存命中率,更有效的利用緩存,避免讀入無用的冷數(shù)據(jù),盡量把經(jīng)常使用到的列放到同一個(gè)表中,避免不必要的關(guān)聯(lián)操作。

7、建立預(yù)留字段需謹(jǐn)慎

部分友人在設(shè)計(jì)數(shù)據(jù)庫(kù)表時(shí),不僅設(shè)計(jì)了當(dāng)前所需要的字段,而且還在其中留出幾個(gè)字段作為備用。比方說,我設(shè)計(jì)了一個(gè)人員表(Person),其中已經(jīng)添加了各種必要的字段,包括姓名(Name)、性別(Sex)、出生年月日(birthday)等等;

為了以防萬一,比如之后可能Person 表會(huì)涉及到畢業(yè)院校、工作單位、是否婚配和相片等信息,于是就加入5個(gè)varchar2 型的字段,分別叫做Text1、Text2……Text5;這一手操作看似防范于未然,其實(shí)也并不見得,因?yàn)榇罅款A(yù)留字段會(huì)浪費(fèi)空間、預(yù)留字段不能做到見名知意、預(yù)留字段無法確認(rèn)存儲(chǔ)的數(shù)據(jù)類型且修改其字段類型還可能會(huì)造成鎖表等問題。

針對(duì)此等情況可以參考以下兩點(diǎn)解決方案:

  1. 如果數(shù)量很少,而且信息的性質(zhì)與原表密切相關(guān),那么就可以直接在原表上增加字段,并將相關(guān)的數(shù)據(jù)更新進(jìn)去;

  2. 如果數(shù)量較大,或者并非是原表對(duì)象至關(guān)重要的屬性,那么就可以新增一個(gè)表,然后通過鍵值連接起來;

8、數(shù)據(jù)庫(kù)中禁止存儲(chǔ)圖片、文件等大的二進(jìn)制數(shù)據(jù)

若往數(shù)據(jù)庫(kù)表中存儲(chǔ)文件,而文件通常很大,當(dāng)數(shù)據(jù)庫(kù)進(jìn)行讀取操作時(shí),會(huì)進(jìn)行大量的隨機(jī)IO操作,大文件使得IO操作很耗時(shí)耗性能,造成短時(shí)間內(nèi)數(shù)據(jù)量快速增長(zhǎng);所以,通常將圖片、文件存儲(chǔ)在文件服務(wù)器中,數(shù)據(jù)庫(kù)只用于存儲(chǔ)文件地址信息。

三、MySQL數(shù)據(jù)庫(kù)字段設(shè)計(jì)規(guī)范

1、優(yōu)先選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型。

主要是考慮索引的性能,因?yàn)榱械淖侄卧酱螅⑺饕龝r(shí)所需要的空間也越大,這樣一頁(yè)中能存儲(chǔ)的索引節(jié)點(diǎn)的數(shù)量也就越少,在遍歷時(shí)需要的IO次數(shù)也就越多,索引的性能也就越差。

2、避免使用TEXT、BLOB數(shù)據(jù)類型

避免使用TEXT和BLOB數(shù)據(jù)類型,其中最常見的TEXT類型可以存儲(chǔ)64K數(shù)據(jù),MySQL內(nèi)存臨時(shí)表不支持TEXT、BLOB這樣的大數(shù)據(jù)類型,若查詢中包含這樣的數(shù)據(jù),在執(zhí)行排序等操作時(shí)就不能使用內(nèi)存臨時(shí)表,必須使用磁盤臨時(shí)表執(zhí)行操作;

TEXT和BLOB類型只能使用前綴索引(當(dāng)索引是很長(zhǎng)的字符序列時(shí),這個(gè)索引將會(huì)很占內(nèi)存,而且會(huì)很慢,這時(shí)候就會(huì)用到前綴索引了;所謂的前綴索引就是去索引的前面幾個(gè)字母作為索引,但是要降低索引的重復(fù)率,所以我們還必須要判斷前綴索引的重復(fù)率;),因?yàn)镸ySQL對(duì)索引字段長(zhǎng)度是有限的,所以TEXT類型只能使用前綴索引,并且TEXT列上是不能有默認(rèn)值的;

若需要使用,建議把BLOB或TEXT列分離到單獨(dú)的的擴(kuò)展表中,且查詢時(shí)一定不要使用select *,只需取出必要的列即可。

3、避免使用ENUM枚舉類型

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

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

禁止使用數(shù)值作為ENUM 的枚舉值。

4、所有列的默認(rèn)值定義為NOT NULL

數(shù)據(jù)庫(kù)所有為NULL 的列需要額外的空間來存儲(chǔ),因此會(huì)占用更多的空間;

數(shù)據(jù)庫(kù)在進(jìn)行比較和計(jì)算時(shí)需要對(duì)NULL 值做特別處理。

5、使用TIMESTAMP(4字節(jié))或DATETIME(8字節(jié))類型存儲(chǔ)時(shí)間

TIMESTAMP 存儲(chǔ)的時(shí)間范圍為:1970-01-01 00:00:01 ~ 2038-01-19-03:14:07;

TIMESTAMP 占用4字節(jié)和INT相同,但可讀性比INT 類型的高,若是超出TIMESTAMP 取值范圍的則使用DATETIME 類型存儲(chǔ);

用字符串類型存儲(chǔ)時(shí)間的缺點(diǎn):無法使用日期函數(shù)進(jìn)行比較計(jì)算、字符串存儲(chǔ)占有更多的空間。

6、財(cái)務(wù)相關(guān)的金額類數(shù)據(jù)必須使用decimal 類型

精準(zhǔn)浮點(diǎn):decimal

非精準(zhǔn)浮點(diǎn):float、double

Decimal類型為精準(zhǔn)浮點(diǎn)數(shù),在計(jì)算時(shí)不會(huì)丟失精度;占有空間大小由定義的寬度決定,每4個(gè)字節(jié)可以存儲(chǔ)9位數(shù)字,且小數(shù)點(diǎn)也要占有一個(gè)字節(jié);另外,Decimal類型可用于存儲(chǔ)比bigint更大的數(shù)據(jù)類型。

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

1、每張表的索引數(shù)量不超過5個(gè)

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

2、每個(gè)Innodb 表必須有一個(gè)主鍵

Innodb 是一種索引組織表,其數(shù)據(jù)存儲(chǔ)的邏輯順序和索引的順序是相同的;

每張表可以有多個(gè)索引,但表的存儲(chǔ)順序只能有一種,Innodb 是按照主鍵索引的順序來組織表的,因此不要使用更新頻繁的列、UUID、MD5、HASH和字符串列作為主鍵,這些列無法保證數(shù)據(jù)的順序增長(zhǎng),主鍵建議使用自增ID 值。

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

不建議使用外鍵約束(foreign key),但一定要在表與表之間的關(guān)聯(lián)鍵上建立索引;

外鍵雖然可以保證數(shù)據(jù)的參照完整性,但外鍵也會(huì)影響父表和子表的寫操作從而降低性能,還會(huì)使得表更耦合,建議在業(yè)務(wù)端實(shí)現(xiàn)。

五、MySQL數(shù)據(jù)庫(kù)SQL開發(fā)規(guī)范

1、建議使用預(yù)編譯語句進(jìn)行數(shù)據(jù)庫(kù)操作

預(yù)編譯語句可以重復(fù)使用,相同的SQL語句可以一次解析,多次使用,減少SQL編譯所需要的時(shí)間,提高處理效率;此外,還可以有效解決動(dòng)態(tài)SQL帶來的SQL注入問題。

2、避免數(shù)據(jù)類型的隱式轉(zhuǎn)換

隱式轉(zhuǎn)換如:SELECT 1 + "1";數(shù)值型 + 字符型 的隱式轉(zhuǎn)換有可能會(huì)導(dǎo)致索引失效,以及一些意想不到的結(jié)果等。

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

1)避免使用雙%號(hào)的查詢條件

如 WHERE first_name like '%James%',若無前置%,只有后置%,則執(zhí)行SQL語句時(shí)會(huì)用到列上的索引,雙%號(hào)則不會(huì)使用列上的索引。

2)一條SQL語句只能使用復(fù)合索引中的一列進(jìn)行范圍查詢

例如有weight、age、sex三列的聯(lián)合索引,在查詢條件中有weight列的范圍查詢,則在age和sex列上的索引將不會(huì)被使用;因此,在定義聯(lián)合索引時(shí),若某列需要用到范圍查詢,則將該列放到聯(lián)合索引的右側(cè)。

3)使用not exists 代替not in

因?yàn)閚ot in 在SQL語句中執(zhí)行時(shí)會(huì)導(dǎo)致索引失效。

4、杜絕使用SELECT * ,必須使用SELECT <字段列表> 查詢

因?yàn)槭褂肧ELECT * 查詢會(huì)消耗更多的CPU、IO和網(wǎng)絡(luò)寬帶資源,并且查詢時(shí)無法使用覆蓋索引。

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

如:INSERT into table_name values ('1','2','3'); 改為帶字段列表的INSERT 語句:INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查詢,可以把子查詢優(yōu)化為join 關(guān)聯(lián)操作

但是,通常子查詢?cè)趇n 子句中,且子查詢中為簡(jiǎn)單SQL(即不包含union、group by、order by、limit從句)時(shí),才可以把子查詢轉(zhuǎn)化為join關(guān)聯(lián)查詢進(jìn)行優(yōu)化;

子查詢性能差的原因:

  • 子查詢的結(jié)果集無法使用索引,通常子查詢的結(jié)果集會(huì)被存儲(chǔ)到臨時(shí)表中,不論是內(nèi)存臨時(shí)表還是磁盤臨時(shí)表都不會(huì)存在索引,所以查詢性能會(huì)受到一定的影響;

  • 由于子查詢會(huì)產(chǎn)生大量的臨時(shí)表也沒有索引,所以會(huì)消耗過多的CPU和IO資源,產(chǎn)生大量的慢查詢。

7、避免使用JOIN 關(guān)聯(lián)太多表

1)在Mysql中,對(duì)于同一個(gè)SQL關(guān)聯(lián)(join)多個(gè)表,每個(gè)join 就會(huì)多分配一個(gè)關(guān)聯(lián)緩存,如果在一個(gè)SQL中關(guān)聯(lián)的表越多,所占用的內(nèi)存也就越大;

2)如果程序中大量的使用了多表關(guān)聯(lián)的操作,同時(shí)join_buffer_size(MySQL允許關(guān)聯(lián)緩存的個(gè)數(shù))設(shè)置的也不合理的情況下,就容易造成服務(wù)器內(nèi)存溢出的情況,就會(huì)影響服務(wù)器數(shù)據(jù)庫(kù)性能的穩(wěn)定性;

3)此外,對(duì)于關(guān)聯(lián)操作來說,會(huì)產(chǎn)生臨時(shí)表影響查詢效率,而Mysql最多允許關(guān)聯(lián)61個(gè)表,建議不超過5個(gè);

8、對(duì)同一列對(duì)象進(jìn)行or 判斷時(shí),使用in 替代or

in 的值只要涉及不超過500個(gè),則in 操作可以更有效的利用索引,or 大多數(shù)情況下很少能利用到索引。

9、禁止使用order by rand() 進(jìn)行隨機(jī)排序

10、禁止在WHERE 從句中對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換和計(jì)算

因?yàn)樵赪HERE 從句中對(duì)列進(jìn)行函數(shù)轉(zhuǎn)換或計(jì)算時(shí)會(huì)導(dǎo)致索引無法使用。

No推薦:

where date(end_time)='20190101'

推薦:

where end_time >= '20190101' and end_time < '20190102'

11、在明顯不會(huì)有重復(fù)值時(shí)使用UNION ALL 而不是UNION

1)UNION 會(huì)把兩個(gè)結(jié)果集的所有數(shù)據(jù)放到臨時(shí)表中后再進(jìn)行去重操作;

2)UNION ALL 不會(huì)再對(duì)結(jié)果集進(jìn)行去重操作;

12、把復(fù)雜、較長(zhǎng)的SQL 拆分為為多個(gè)小SQL 執(zhí)行

1)大SQL在邏輯上比較復(fù)雜,是需要占用大量CPU 進(jìn)行計(jì)算一條SQL語句;

2)在MySQL中,一條SQL 語句只能使用一個(gè)CPU 進(jìn)行計(jì)算;

3)SQL拆分后可以通過并行執(zhí)行來提高處理效率。

六、MySQL數(shù)據(jù)庫(kù)行為規(guī)范

1、超過100萬行數(shù)據(jù)的批量操作(update delete insert),分多次進(jìn)行

大批量操作可能回造成嚴(yán)重的主從延遲;

binlog日志為row格式時(shí)會(huì)產(chǎn)生大量的日志;

避免產(chǎn)生大事物操作。

2、對(duì)于大表使用pt-online-schema-change 修改表結(jié)構(gòu)

1)避免大表修改產(chǎn)生的主從延遲、避免在對(duì)表字段進(jìn)行修改時(shí)進(jìn)行鎖表;

2)pt-online-schema-change 它首先會(huì)建立一個(gè)與原表結(jié)構(gòu)相同的新表,并且在新表上進(jìn)行表結(jié)構(gòu)的修改,然后再把原表中的數(shù)據(jù)復(fù)制到新表中,并在原表中增加一些觸發(fā)器;然后,把原表中新增的數(shù)據(jù)也復(fù)制到新表中,在行所有數(shù)據(jù)復(fù)制完成之后,把新表命名成原表,并把原來的表刪除掉,其是把原來一個(gè)DDL操作,分解成多個(gè)小的批次執(zhí)行。

3、禁止給程序使用的賬號(hào)授予super 權(quán)限

當(dāng)達(dá)到最大連接數(shù)限制時(shí),還運(yùn)行1個(gè)有super權(quán)限的用戶連接super權(quán)限只能留給DBA處理問題的賬號(hào)使用。

4、對(duì)于程序連接數(shù)據(jù)庫(kù)賬號(hào),遵循權(quán)限最小原則

程序使用數(shù)據(jù)庫(kù)賬號(hào)只能在一個(gè)數(shù)據(jù)庫(kù)下使用,且程序使用的賬號(hào)原則上不授予drop 權(quán)限。

[我要糾錯(cuò)]
文:王振袢&發(fā)表于江蘇
關(guān)鍵詞: 點(diǎn)擊 添加 圖片 描述 最多

來源:本文內(nèi)容搜集或轉(zhuǎn)自各大網(wǎng)絡(luò)平臺(tái),并已注明來源、出處,如果轉(zhuǎn)載侵犯您的版權(quán)或非授權(quán)發(fā)布,請(qǐng)聯(lián)系小編,我們會(huì)及時(shí)審核處理。
聲明:江蘇教育黃頁(yè)對(duì)文中觀點(diǎn)保持中立,對(duì)所包含內(nèi)容的準(zhǔn)確性、可靠性或者完整性不提供任何明示或暗示的保證,不對(duì)文章觀點(diǎn)負(fù)責(zé),僅作分享之用,文章版權(quán)及插圖屬于原作者。

點(diǎn)個(gè)贊
0
踩一腳
0

您在閱讀:MySQL使用規(guī)范手冊(cè),程序員必知必會(huì)

Copyright?2013-2024 JSedu114 All Rights Reserved. 江蘇教育信息綜合發(fā)布查詢平臺(tái)保留所有權(quán)利

蘇公網(wǎng)安備32010402000125 蘇ICP備14051488號(hào)-3技術(shù)支持:南京博盛藍(lán)睿網(wǎng)絡(luò)科技有限公司

南京思必達(dá)教育科技有限公司版權(quán)所有   百度統(tǒng)計(jì)

主站蜘蛛池模板: 456亚洲人成影视在线观看 | 免费视频成人国产精品网站 | 波多野结衣视频免费在线观看 | 国产性片在线观看 | 最近2019年免费中文字幕高清 | 亚洲综合色网 | 国产成人免费 | 中文字幕久久亚洲一区 | 成人午夜激情 | 一级欧美视频 | 色视频免费网站 | 亚洲日本va中文字幕婷婷 | 日韩欧美国产中文 | free性欧美69| 欧美成视频在线观看 | 欧美日韩中文国产一区二区三区 | 性free中国美女hd | 黄色在线观看www | 天天搞夜夜| 黄色大片免费网站 | 欧美性video高清精品 | a一级毛片视频免费看 | 久久成人网18网站 | 女人牲交视频一级毛片 | 一级黄色片a | 91视频看 | 亚洲 欧美 日韩在线一区 | 亚洲精品视频在线播放 | 1204手机在线观看免费高清 | 精品女同一区二区三区免费播放 | 一级在线毛片 | 亚洲成成熟老妇女 | 国产91精品久久久久999 | 一级毛片完整免费版 | 亚洲国产日产韩国欧美综合 | 黄色网在线免费观看 | 日韩欧美一区二区三区久久 | 日韩伦理网 | 国产成 人 综合 亚洲绿色 | 成人全黄三级视频在线观看 | 免费老色鬼永久视频网站 |
最熱文章
最新文章
  • 阿里云上云鉅惠,云產(chǎn)品享最低成本,有需要聯(lián)系,
  • 卡爾蔡司鏡片優(yōu)惠店,鏡片價(jià)格低
  • 蘋果原裝手機(jī)殼