一屑咳、安裝及配置
二吏垮、基礎(chǔ)操作
三障涯、MySQL開發(fā)規(guī)范總結(jié)
(一)、設(shè)計(jì)規(guī)范
- 【推薦】字段允許適當(dāng)冗余膳汪,以提高查詢性能,但必須考慮數(shù)據(jù)一致九秀。冗余字段應(yīng)遵循:
- 不是頻繁修改的字段遗嗽。
- 不是 varchar 超長字段,更不能是 text 字段鼓蜒。
正例: 商品類目名稱使用頻率高痹换,字段長度短,名稱基本一成不變都弹,可在相關(guān)聯(lián)的表中冗余存 儲(chǔ)類目名稱娇豫,避免關(guān)聯(lián)查詢。
- 【推薦】單表行數(shù)超過 500 萬行或者單表容量超過 2GB畅厢,才推薦進(jìn)行分庫分表冯痢。
說明:如果預(yù)計(jì)3年后的數(shù)據(jù)量根本達(dá)不到這個(gè)級別,不要在創(chuàng)建表時(shí)就分庫分表框杜。
- 【推薦】一個(gè)表的字段個(gè)數(shù)控制在30-50個(gè)字段以內(nèi)浦楣;如果字段超過50個(gè),可考慮將字段按冷熱程度分表咪辱。
這樣做雖然會(huì)給應(yīng)用帶來更多的代碼開發(fā)量振劳,但對于熱表來說,這樣做可以提升buffer利用率油狂,減少IO历恐,提升查詢的效率。
- 【強(qiáng)制】字段字符集與表保持一致专筷,不單獨(dú)設(shè)置字符集弱贼。
相同含義的字段在不同表中應(yīng)使用相同的名稱,數(shù)據(jù)類型及長度必須保持一致仁堪。
【推薦】id必須是主鍵哮洽,每個(gè)表必須有主鍵,且保持增長趨勢的弦聂, 小型系統(tǒng)可以依賴于 MySQL 的自增主鍵鸟辅,大型系統(tǒng)或者需要分庫分表時(shí)才使用 ID 生成器氛什,如雪花生成器等。
【推薦】id類型沒有特殊要求匪凉,必須使用bigint unsigned枪眉,禁止使用int,即使現(xiàn)在的數(shù)據(jù)量很小再层。id如果是數(shù)字類型的話贸铜,必須是8個(gè)字節(jié)。
- 方便對接外部系統(tǒng)聂受,還有可能產(chǎn)生很多廢數(shù)據(jù)
- 避免廢棄數(shù)據(jù)對系統(tǒng)id的影響
- 未來分庫分表蒿秦,自動(dòng)生成id,一般也是8個(gè)字節(jié)
- 【推薦】字段盡量設(shè)置為 NOT NULL蛋济, 為字段提供默認(rèn)值棍鳖。
- 如字符型的默認(rèn)值為一個(gè)空字符值串’’;
- 數(shù)值型默認(rèn)值為數(shù)值 0;
- 邏輯型的默認(rèn)值為數(shù)值 0;
【強(qiáng)制】每個(gè)字段和表必須提供清晰的注釋碗旅。同時(shí)渡处,如果修改字段含義或?qū)ψ侄伪硎镜臓顟B(tài)追加時(shí)祟辟,需要及時(shí)更新字段注釋。
【推薦】關(guān)于時(shí)間的格式旧困,推薦統(tǒng)一使用Unix時(shí)間戳格式
- unix時(shí)間戳(Unix Timestamp)是從1970年1月1日(UTC/GMT的午夜)開始所經(jīng)過的秒數(shù),不考慮閏秒叮喳。
- 因?yàn)镸ySQL 關(guān)于日期的類型有Date/ Datetime/ Timestamp三種類型。對于時(shí)間的隨便選擇會(huì)導(dǎo)致數(shù)據(jù)庫開發(fā)和業(yè)務(wù)邏輯中遇到不同類型的轉(zhuǎn)換時(shí)經(jīng)常出現(xiàn)混亂或者難以察覺的
- unix時(shí)間戳在MySQL上可以直接使用bigint unsigned存儲(chǔ)馍悟,避免各種時(shí)間類型的不一致畔濒。也方便后臺(tái)和數(shù)據(jù)庫關(guān)于時(shí)間的比較锣咒。
- 前端業(yè)務(wù)可以根據(jù)unix時(shí)間戳轉(zhuǎn)換成自己需要的時(shí)間格式。
- 【強(qiáng)制】更新數(shù)據(jù)表記錄時(shí)毅整,必須同時(shí)更新記錄對應(yīng)的GMT_MODIFIED字段值為當(dāng)前時(shí)間。
- Gmt: 格林威治時(shí)間
- 結(jié)合7艇潭,GMT_MODIFIED建議使用unix時(shí)間戳格式保存
- 【強(qiáng)制】表達(dá)是與否概念的字段,必須使用 IS_XXX 的方式命名蹋凝,數(shù)據(jù)類型是 unsigned tinyint( 1 表示是, 0 表示否)改含。
- 任何字段如果為非負(fù)數(shù)迄汛,必須是 unsigned。
- 注意: POJO 類中的任何布爾類型的變量鞍爱,都不要加 is 前綴,所以帜慢,需要在<resultMap>設(shè)置從 IS_XXX到 Xxx 的映射關(guān)系唯卖。數(shù)據(jù)庫表示是與否的值躬柬,使用 tinyint 類型,堅(jiān)持 IS_xxx 的命名方式是為了明確其取值含義與取值范圍橄碾。
正例: 表達(dá)邏輯刪除的字段名 IS_DELETED颠锉, 1 表示刪除, 0 表示未刪除琼掠。
- 【推薦】表名不使用復(fù)數(shù)名詞。
表名應(yīng)該僅僅表示表里面的實(shí)體內(nèi)容悼瓮,不應(yīng)該表示實(shí)體數(shù)量艰猬,對應(yīng)于 DO 類名也是單數(shù) 形式,符合表達(dá)習(xí)慣冠桃。
正例: USER,RDC_CONFIG
反例: USERS,RDC_CONFIGS
【強(qiáng)制】禁用保留字,如 desc胸蛛、 range、 match省咨、 delayed 等, 請參考 MySQL 官方保留字零蓉。
【推薦】 主鍵索引名為 pk字段名穷缤; 唯一索引名為 uk字段名; 普通索引名則為 idx字段名章喉。
說明: pk 即 primary key身坐; uk_ 即 unique key; idx_ 即 index 的簡稱部蛇。
- 【強(qiáng)制】小數(shù)類型為 decimal,禁止使用 float 和 double巷查。
說明: float 和 double 在存儲(chǔ)的時(shí)候抹腿,存在精度損失的問題,很可能在值的比較時(shí)崇败,得到不 正確的結(jié)果房蝉。如果存儲(chǔ)的數(shù)據(jù)范圍超過 decimal 的范圍,建議將數(shù)據(jù)拆成整數(shù)和小數(shù)分開存儲(chǔ)搭幻。
【推薦】如果存儲(chǔ)的字符串長度幾乎相等,使用 char 定長字符串類型松申。
【推薦】 varchar 是可變長字符串,不預(yù)先分配存儲(chǔ)空間舅逸,長度不要超過 5000皇筛,如果存儲(chǔ)長度大于此值,定義字段類型為 text水醋,獨(dú)立出來一張表,用主鍵來對應(yīng)蝇恶,避免影響其它字段索引效率惶桐。
【強(qiáng)制】表必備三字段: ID, GMT_CREATE, GMT_MODIFIED。
其中ID必為主鍵贿衍,類型為 bigint unsigned救恨。 GMT_CREATE, GMT_MODIFIED的類型均unix時(shí)間戳,前者現(xiàn)在時(shí)表示主動(dòng)創(chuàng)建忿薇,后者過去分詞表示被動(dòng)更新署浩。
- 【推薦】表的命名最好是加上“業(yè)務(wù)名稱_表的作用”扫尺。
正例: FORCE_PROJECt/TRADE_CONFIG
【推薦】庫名與應(yīng)用名稱盡量一致。
【推薦】所有命名必須使用全名正驻,有默認(rèn)約定的除外,如果超過 30 個(gè)字符襟交,使用縮寫伤靠,請盡量名字易懂簡短。如
- DESCRIPTION--> DESC;
- INFORMATION --> INFO;
- ADDRESS --> ADDR 等
- 【強(qiáng)制】業(yè)務(wù)應(yīng)用禁止有 super 賬號的存在
super權(quán)限很大焕梅,一般是DBA才會(huì)用到迹鹅,會(huì)導(dǎo)致read only失效,原則上不提供給應(yīng)用賬號使用贞言。
【推薦】IP地址的存儲(chǔ)该窗,ipv4盡量使用int unsigned來存儲(chǔ),而不要使用varchar(15)來存儲(chǔ),可以節(jié)省11字節(jié)挪捕,如果包括索引,可以節(jié)省22字節(jié)断医。
【強(qiáng)制】不允許不同業(yè)務(wù)模塊的表連接查詢奏纪。
【強(qiáng)制】禁止在數(shù)據(jù)庫中存儲(chǔ)明文密碼。
【推薦】比較重要的數(shù)據(jù)刪除操作使用邏輯刪除(UPDATE table SET is_deleteflag=1)代替物理刪除(DELETE FROM table WHERE ..)
【強(qiáng)制】InnoDB表數(shù)據(jù)量特別大的避免使用COUNT(*)操作醉锅,推薦計(jì)數(shù)統(tǒng)計(jì)實(shí)時(shí)要求較強(qiáng)可以使用redis发绢,非實(shí)時(shí)統(tǒng)計(jì)可以使用單獨(dú)統(tǒng)計(jì)表,定時(shí)更新经柴。
【強(qiáng)制】事務(wù)語句執(zhí)行完成之后必須及時(shí)提交墩朦。
事務(wù)長時(shí)間不提交,如果這時(shí)對事務(wù)相關(guān)表執(zhí)行DDL操作氓涣,會(huì)出現(xiàn)等待元數(shù)據(jù)鎖的提示。DDL語句被阻塞后引润,其他所有表上的正常操作(DML痒玩、SELECT)都會(huì)被阻塞晶渠。
- 【推薦】盡量避免或者拆分執(zhí)行大事務(wù)燃观。
- 無論在業(yè)務(wù)程序中,還是手動(dòng)數(shù)據(jù)調(diào)整中都需要盡量避免執(zhí)行大事務(wù)番川。對于影響行數(shù)過萬的記錄建議DBA審核后通過工具或腳本分批執(zhí)行脊框。
- 大事務(wù)的執(zhí)行會(huì)給數(shù)據(jù)庫穩(wěn)定性帶來很多問題。例如引起從庫復(fù)制延遲沉御、導(dǎo)致鎖等待昭灵、系統(tǒng)臟數(shù)據(jù) checkpoint 寫入的性能抖動(dòng)。
- 【推薦】對于歷史數(shù)據(jù)试疙、日志數(shù)據(jù)等數(shù)據(jù)量特別大的表(通常數(shù)據(jù)量超過千萬抠蚣,占用空間超過10G),需要應(yīng)用根據(jù)業(yè)務(wù)特點(diǎn)嘶窄,預(yù)估業(yè)務(wù)數(shù)據(jù)增漲速度,提前在應(yīng)用代碼中進(jìn)行分表操作(同一MySQL實(shí)例下的分表操作實(shí)現(xiàn)不難)敌完。
- 通逞虺酰可以按照時(shí)間(按年什湘、按月)進(jìn)行數(shù)據(jù)分表,存在明顯的數(shù)據(jù)熱點(diǎn)得哆,比如最近一年或者最近三個(gè)月的數(shù)據(jù)是熱點(diǎn)數(shù)據(jù)哟旗。其他數(shù)據(jù)是歷史數(shù)據(jù)按价。歷史數(shù)據(jù)很少進(jìn)行修改,一般只要提供給業(yè)務(wù)進(jìn)行分析查詢即可辰如。這樣可以把歷史數(shù)據(jù)歸檔到其他實(shí)例上剔宪,提供可供實(shí)時(shí)查詢的接口便可壹无。
- DBA有成熟的實(shí)現(xiàn)方案,主要是減少業(yè)務(wù)庫中大量歷史數(shù)據(jù)造成的磁盤空間緊張斗锭、數(shù)據(jù)備份岖是、恢復(fù)慢的運(yùn)維問題。
- 對于大表按規(guī)則進(jìn)行分表也可提升業(yè)務(wù)并發(fā)讀寫性能璧微。
- 【強(qiáng)制】嚴(yán)格禁止單條記錄超過8K
目前我們的DB一個(gè)page大小都設(shè)置為16K前硫,當(dāng)一條記錄超過page的一半(8K)時(shí),記錄中的blob/varchar會(huì)在行外存儲(chǔ)屹电。存取時(shí)會(huì)有額外IO消耗。插入操作會(huì)鎖住整個(gè)聚簇索引(X Index Lock)牧愁,直到插入完成才釋放外莲。
【推薦】字段約束,對于字典類型的表磨确,因數(shù)據(jù)量小声邦,修改少,影響面大邓了,應(yīng)依賴數(shù)據(jù)庫約束來確保數(shù)據(jù)質(zhì)量。對于日志或流水型表骗炉,為了提升效率,可以釋放放寬限制。
【強(qiáng)制】庫名、表名、字段名禁止超過32個(gè)字符叼屠。
庫名荚坞、表名各淀、字段名支持最多64個(gè)字符,但為了統(tǒng)一規(guī)范璃俗、易于辨識以及減少傳輸量,禁止超過32個(gè)字符钮蛛。
- 【推薦】Mysql數(shù)據(jù)庫統(tǒng)一使用innodb存儲(chǔ)引擎
原則上甸饱,業(yè)務(wù)范疇內(nèi)的表都統(tǒng)一使用innodb存儲(chǔ)引擎驼壶;如需使用其它存儲(chǔ)引擎般妙,需說明原因鲜锚,并征得DBA同意怯疤。集團(tuán)標(biāo)準(zhǔn)化部署設(shè)置默認(rèn)使用innodb引擎塔淤。
- 【推薦】數(shù)據(jù)庫設(shè)計(jì)不建議使用這樣擴(kuò)展字段:attr1,attr2,attr3…备恤,業(yè)務(wù)需要什么字段使用時(shí)再添加。
- 使用擴(kuò)展字段生真,擴(kuò)展字段名字和注釋不好維護(hù)产弹。
- 新版本的數(shù)據(jù)庫新增和刪除字段成本相對比較低。
(二)類型規(guī)范
【強(qiáng)制】表示狀態(tài)字段(0-255)的使用 TINYINT UNSINGED撬讽,禁止使用枚舉類型悬垃,注釋必須清晰地說明每個(gè)枚舉的含義,以及是否支持多選等看彼。
【強(qiáng)制】表示boolean類型的都使用TINYINT(1)
- 因?yàn)镸ySQL本身是沒有boolean類型的标锄,在自動(dòng)生成代碼的時(shí)候茁计,DO對象的字段就是boolean類型,例如 is_delete;其余所有時(shí)候都使用TINYINT(4)。
- TINYINT(4),這個(gè)括號里面的數(shù)值并不是表示使用多大空間存儲(chǔ)毒返,而是最大顯示寬度舷手,并且只有字段指定zerofill時(shí)有用劲绪,沒有zerofill,(m)就是無用的,例如id BIGINT ZEROFILL NOT NULL,所以建表時(shí)就使用默認(rèn)就好了歉眷,不需要加括號了颤枪,除非有特殊需求,例如TINYINT(1)代表boolean類型扇住。
- TINYINT(1),TINYINT(4)都是存儲(chǔ)一個(gè)字節(jié)艘蹋,并不會(huì)因?yàn)槔ㄌ柪锏臄?shù)字改變票灰。例如TINYINT(4)存儲(chǔ)22則會(huì)顯示0022,因?yàn)樽畲髮挾葹?浸策,達(dá)不到的情況下用0來補(bǔ)充惹盼。
【參考】合適的字符存儲(chǔ)長度,不但節(jié)約數(shù)據(jù)庫表空間節(jié)約索引存儲(chǔ)夫晌,更重要的是提升檢索速度昧诱。
|類型|字節(jié)|表示范圍|
|tinyint|1|無符號值: 0~255;有符號值: -128~127 |
|smallint|2|無符號值: 0~65536;有符號值: -32768~32767 |
|mediumint|3|無符號值: 0~16777215;有符號值: -8388608~8388607 |
|int|4|無符號值: 0~4294967295;有符號值: -2147483648~2147483647 |
|bigint|8|無符號值: 0~((232×2)-1);有符號值: -(232×2)/2 ~ (232×2)/2-1|【參考】非負(fù)的數(shù)字類型字段,都添加上
如可以使用INTUNSINGED字段存IPV4
【參考】時(shí)間字段存儲(chǔ)首先推薦7凶掰,如果確實(shí)要使用其他時(shí)間日期類型,不要使用字符串類型存儲(chǔ)前翎,日期使用DATE類型畅涂,年使用YEAR類型,日期時(shí)間使用DATETIME.
【參考】字符串VARCHAR(N), 其中 N表示字符個(gè)數(shù)立宜,請盡量減少 N 的大小臊岸,參考:code VARCHAR(32);name VARCHAR(32)灯帮;memo VARCHAR(512)逻住;
7.【參考】Blob和Text類型所存儲(chǔ)的數(shù)據(jù)量大,刪除和修改操作容易在數(shù)據(jù)表里產(chǎn)生大量的碎片瞪醋,如果可以装诡,應(yīng)該盡量避免使用Blob或Text類型
(三)索引規(guī)范
1.【推薦】業(yè)務(wù)上具有唯一特性的字段,即使是多個(gè)字段的組合宾巍,也必須建成唯一索引渔伯。
說明:不要以為唯一索引影響了insert速度,這個(gè)速度損耗可以忽略选浑,但提高查找速度是明顯的;另外玄叠,即使在應(yīng)用層做了非常完善的校驗(yàn)控制,只要沒有唯一索引隧膘,根據(jù)墨菲定律,必然有臟數(shù)據(jù)產(chǎn)生蹦疑。
- 【推薦】主鍵最好由一個(gè)字段構(gòu)成萨驶,最多不要超過3個(gè),禁止超過3個(gè)字段的組合主鍵判莉。
- 多列主鍵會(huì)導(dǎo)致其他索引體積膨脹育谬,占用更多的空間帮哈,并降低性能。
- 使用多列業(yè)務(wù)主鍵的話咖刃,對主鍵來說憾筏,一般可認(rèn)為是隨機(jī)插入。
- 【強(qiáng)制】多表關(guān)聯(lián)查詢中枫浙,關(guān)聯(lián)字段必須創(chuàng)建索引古拴,關(guān)聯(lián)字段的字段類型黄痪、字符集、校驗(yàn)集 保持一致桅打。where 子句后面必須帶上關(guān)聯(lián)條件挺尾。
多表關(guān)聯(lián)查詢,關(guān)聯(lián)字段如果沒有索引遭铺,或者關(guān)聯(lián)字段類型不一致,則會(huì)對多表進(jìn)行全表掃描航厚。這樣對CPU和磁盤IO消耗是比較大的幔睬。如果是百萬級以上的大表關(guān)聯(lián),則會(huì)嚴(yán)重影響數(shù)據(jù)庫性能赦抖。where 子句后不帶關(guān)聯(lián)條件辅肾,則會(huì)導(dǎo)致 cross-join, MySQL 將使用大量的磁盤空間進(jìn)行數(shù)據(jù)中間結(jié)果處理。嚴(yán)重情況下要尔,/tmp 目錄下的臨時(shí)表空間文件會(huì)直接把所有數(shù)據(jù)盤占滿新娜,導(dǎo)致數(shù)據(jù)庫無法寫入、進(jìn)而崩潰还惠。
- 【強(qiáng)制】在varchar字段上建立索引時(shí)私杜,必須指定索引長度,沒必要對全字段建立索引嚎幸,根據(jù)實(shí)際文本區(qū)分度決定索引長度即可寄猩。
索引的長度與區(qū)分度是一對矛盾體,一般對字符串類型數(shù)據(jù)替废,長度為20的索引泊柬,區(qū)分度會(huì)高達(dá)90%以上,可以使用count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定状答。
- 【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊,如果需要請走搜索引擎來解決拍摇。
索引文件具有 B-Tree 的最左前綴匹配特性馆截,如果左邊的值未確定蜡娶,那么無法使用此索引。
- 【強(qiáng)制】禁止冗余索引窖张。
索引是雙刃劍,會(huì)增加維護(hù)負(fù)擔(dān),增大IO壓力宿接。(a,b,c)、(a,b),后者為冗余索引√ぐ荩可以利用前綴索引來達(dá)到加速目的,減輕維護(hù)負(fù)擔(dān)速梗。
- 【推薦】如果有order by 的場景,請注意利用索引的有序性姻锁。order by 最后的字段是組合索引的一部分位隶,并且放在索引組合順序的最后,避免出現(xiàn) file_sort 的情況篮昧,影響查詢性能笋妥。
正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范圍查找,那么索引有序性無法利用酵颁,如:WHERE a>10 ORDER BY b; 索引 a_b 無法排序。
- 【推薦】利用覆蓋索引來進(jìn)行查詢操作幽污,避免回表
如果一本書需要知道第11章是什么標(biāo)題其掂,會(huì)翻開第11章對應(yīng)的那一頁嗎?目錄瀏覽一下就好,這個(gè)目錄就是起到覆蓋索引的作用深寥。
正例:能夠建立索引的種類:主鍵索引贤牛、唯一索引、普通索引闰集,而覆蓋索引是一種查詢的效果般卑,用explain的結(jié)果蝠检,extra列會(huì)出現(xiàn):using index。
- 【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景叹谁。
MySQL并不是跳過 offset 行焰檩,而是取 offset+N 行,然后返回放棄前 offset 行兜叨,返回 N 行衩侥,那當(dāng) offset 特別大的時(shí)候,效率就非常的低下议街,要么控制返回的總頁數(shù)璧榄,要么對超過特定閾值的頁數(shù)進(jìn)行 SQL 改寫。
正例:先快速定位需要獲取的 id 段涂身,然后再關(guān)聯(lián):
SELECT a.* FROM 表 1 a, (select id from 表 1 where 條件 LIMIT 100000,20 ) b where a.id=b.id
- 【推薦】SQL 性能優(yōu)化的目標(biāo):至少要達(dá)到 range 級別蛤售,要求是 ref 級別,如果可以是 consts 最好揣钦。
- range 對索引進(jìn)行范圍檢索漠酿。
- ref 指的是使用普通的索引(normal index)。
- consts 單表中最多只有一個(gè)匹配行(主鍵或者唯一索引)宇姚,在優(yōu)化階段即可讀取到數(shù)據(jù)夫凸。
反例: explain 表的結(jié)果,type=index魔熏,索引物理文件全掃描啼止,速度非常慢献烦,這個(gè) index 級 別比較 range 還低卖词,與全表掃描是小巫見大巫。
- 【推薦】建組合索引的時(shí)候即横,區(qū)分度最高的在最左邊裆赵。
存在非等號和等號混合判斷條件時(shí)战授,在建索引時(shí)桨嫁,請把等號條件的列前置份帐。如:where a>? and b=? 那么即使 a 的區(qū)分度更高废境,也必須把 b 放在索引的最前列。
正例:如果 where a=? and b=? 巴元,a 列的幾乎接近于唯一值栓始,那么只需要單建 idx_a 索引即可
- 【推薦】防止因字段類型不同造成的隱式轉(zhuǎn)換,導(dǎo)致索引失效禀忆。
反例:為了說明這個(gè)問題我們制造一個(gè)反例:創(chuàng)建一張用戶表test_user落恼,其中USER_ID我們設(shè)置為varchar類型并加上唯一索引佳谦。
CREATE TABLE test_user
( id
int(11) NOT NULL AUTO_INCREMENT, user_id
varchar(11) DEFAULT NULL COMMENT '用戶賬號', user_name
varchar(255) DEFAULT NULL COMMENT '用戶名',
age
int(5) DEFAULT NULL COMMENT '年齡', comment
varchar(255) DEFAULT NULL COMMENT '簡介', PRIMARY KEY (id
), UNIQUE KEY UNIQUE_USER_ID
(user_id
) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入幾條測試數(shù)據(jù):
INSERT INTO test_user (user_id, user_name, age, comment) VALUES (‘111’, ‘張三’,18啥刻,‘法外狂徒’ )咪笑, (‘222’, ‘李四’窗怒,19,‘燕南天’ ) 努隙,(‘333’, ‘王五’辜昵,145,‘隔壁老王’ ) 躬存;
然后我們執(zhí)行EXPLAIN SQL 看一下索引命中情況:
EXPLAIN SELECT * FROM test_user WHERE user_id = 1111;
得到如下結(jié)果:
我們會(huì)發(fā)現(xiàn),雖然user_id字段加了索引诵叁,但是當(dāng)我們使用數(shù)值類型進(jìn)行查詢時(shí)沒走索引钦椭,而是進(jìn)行了全表掃描彪腔。
- 【參考】創(chuàng)建索引時(shí)避免有如下極端誤解
- 寧濫勿缺。認(rèn)為一個(gè)查詢就需要建一個(gè)索引恭垦。
- 寧缺勿濫格嗅。認(rèn)為索引會(huì)消耗空間、嚴(yán)重拖慢更新和新增速度玄柏。
- 抵制惟一索引贴铜。認(rèn)為業(yè)務(wù)的惟一性一律需要在應(yīng)用層通過“先查后插”方式解決。
- 【參考】其他注意事項(xiàng)
- 索引占磁盤空間徘意,不要重復(fù)的索引椎咧,盡量短 灾挨。
- 只給常用的查詢條件加索引竹宋。
- 過濾性高的列建索引蜈七,區(qū)分度不高的列不建索引。
- 唯一的記錄添加唯一索引砂缩。
- 頻繁更新的列不要建索引。
- 不要對索引列運(yùn)算妹懒。
- 同樣過濾效果下双吆,保持索引長度最小。
- 合理利用組合索引匾竿,注意索引字段先后順序蔚万。
- 多列組合索引昵慌,過濾性高的字段最前淮蜈。
- order by 字段建立索引礁芦,避免 filesort肖方。
- 組合索引未状,不同的排序順序不能使用索引。
- <>!=無法使用索引艰垂。
(四)SQL語句規(guī)范
- 【****強(qiáng)制】不要使用 count(列名)或 count(常量)來替代 count()猜憎,count()是 SQL92 定義的 標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法胰柑,跟數(shù)據(jù)庫無關(guān)柬讨,跟 NULL 和非 NULL 無關(guān)。
count(*)會(huì)統(tǒng)計(jì)值為 NULL 的行却桶,而 count(列名)不會(huì)統(tǒng)計(jì)此列為 NULL 值的行肾扰。
- 【強(qiáng)制】count(distinct col) 計(jì)算該列除 NULL 之外的不重復(fù)行數(shù)蛋逾,
count(distinct col1, col2) 如果其中一列全為NULL,那么即使另一列有不同的值,也返回為0莲绰。
- 【強(qiáng)制】當(dāng)某一列col的值全是 NULL 時(shí)蛤签,count(col)的返回結(jié)果為 0震肮,但 sum(col)的返回結(jié)果為 NULL,因此使用 sum()時(shí)需注意 NPE 問題痴柔。
正例:可以使用如下方式避免sum的NPE問題:SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table;
- 【強(qiáng)制】使用 ISNULL()來判斷是否為 NULL 值豪嚎。 說明:NULL 與任何值的直接比較都為 NULL侈询。
- NULL<>NULL的返回結(jié)果是NULL妄荔,而不是false啦租。
- NULL=NULL的返回結(jié)果是NULL篷角,而不是true。
- NULL<>1的返回結(jié)果是NULL俩滥,而不是true错忱。
【強(qiáng)制】 在代碼中寫分頁查詢邏輯時(shí)以清,若 count 為 0 應(yīng)直接返回,避免執(zhí)行后面的分頁語句个绍。
【強(qiáng)制】不得使用外鍵與級聯(lián)错森,一切外鍵概念必須在應(yīng)用層解決涩维。
說明:以學(xué)生和成績的關(guān)系為例瓦阐,學(xué)生表中的 studentid 是主鍵睡蟋,那么成績表中的 studentid 則為外鍵戳杀。如果更新學(xué)生表中的 studentid隔缀,同時(shí)觸發(fā)成績表中的 studentid 更新猾瘸,即為 級聯(lián)更新。外鍵與級聯(lián)更新適用于單機(jī)低并發(fā)咐低,不適合分布式绰更、高并發(fā)集群;級聯(lián)更新是強(qiáng)阻 塞锡宋,存在數(shù)據(jù)庫更新風(fēng)暴的風(fēng)險(xiǎn);外鍵影響數(shù)據(jù)庫的插入速度。
【強(qiáng)制】在MySQL中禁止使用存儲(chǔ)過程徐钠,存儲(chǔ)過程難以調(diào)試和擴(kuò)展尝丐,更沒有移植性。
【強(qiáng)制】數(shù)據(jù)訂正時(shí)矮固,刪除和修改記錄時(shí)盹兢,要先 select绎秒,避免出現(xiàn)誤刪除剂娄,確認(rèn)無誤才能執(zhí)行更新語句阅懦。
【推薦】邏輯運(yùn)算如果能在業(yè)務(wù)層解決儿咱,盡量不在數(shù)據(jù)庫做運(yùn)算庭砍,盡可能簡單使用MySQL混埠。
md5() 或Order by Rand()或計(jì)算字段等操作不在數(shù)據(jù)庫表上進(jìn)行
【推薦】不建議將數(shù)據(jù)字典表進(jìn)行連接查詢
數(shù)據(jù)字典表可以查詢到cache或者在內(nèi)存中對相關(guān)列進(jìn)行替換。
【推薦】用UNION ALL代替UNION
UNION ALL不需要對結(jié)果集再進(jìn)行排序揭北。
【推薦】in操作能避免則避免吏颖,若實(shí)在避免不了,需要仔細(xì)評估 in 后邊的集合元素?cái)?shù)量半醉,控制在 1000 個(gè)之內(nèi)。
【推薦】減少與數(shù)據(jù)庫交互次數(shù),盡量采用批量SQL語句
使用下面的語句來減少和db的交互次數(shù):
1.INSERT ... ON DUPLICATE KEY UPDATE
2.REPLACE INTO
3.INSERT IGNORE
4.INSERT INTO VALUES()
- 【推薦】拆分復(fù)雜SQL為多個(gè)小SQL,避免大事務(wù)
簡單的SQL容易使用到MySQL的QUERY CACHE;減少鎖表時(shí)間特別是MyISAM;可以使用多核CPU。
- 【強(qiáng)制】禁止使用HINT
HINT給DBA維護(hù)帶來很多的不便衬吆,MySQL使用CBO優(yōu)化器,會(huì)綜合考慮如果執(zhí)行SQL逊抡。
【強(qiáng)制】禁止使用分區(qū)表
分區(qū)表對分區(qū)鍵有嚴(yán)格要求;分區(qū)表在表變大后,執(zhí)行DDL、SHARDING麦射、單表恢復(fù)等都變得更加困難灯谣。因此禁止使用分區(qū)表,并建議業(yè)務(wù)端手動(dòng)SHARDING。
【參考】如果有全球化需要胎许,所有的字符存儲(chǔ)與表示罗售,均以 utf8mb4 編碼钩述,注意字符統(tǒng)計(jì)函數(shù) 的區(qū)別。
- SELECT LENGTH(“輕松工作”); 返回為12
- SELECT CHARACTER_LENGTH(“輕松工作”); 返回為4
- utf8mb4字符集可以存儲(chǔ)表情等字符牙勘。
- 【參考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系統(tǒng)和事務(wù)日志資源少放钦,但 TRUNCATE 無事務(wù)且不觸發(fā)trigger恭金,有可能造成事故,故不建議在開發(fā)代碼中使用此語句横腿。
說明:TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同。
- 【推薦】不要寫一個(gè)大而全的數(shù)據(jù)更新接口耿焊。
傳入為 POJO 類,不管是不是自己的目標(biāo)更新字 段器腋,都進(jìn)行 update table set c1=value1,c2=value2,c3=value3; 這是不對的歇父。執(zhí)行 SQL 時(shí),不要更新無改動(dòng)的字段榜苫,一是易出錯(cuò);二是效率低;三是增加 binlog 存儲(chǔ)。
- 【推薦】如果可以放到業(yè)務(wù)邏輯里面媳荒,避免使用 GROUP BY、DISTINCT 钳枕、ORDER BY等語句的使用赏壹,避免聯(lián)表查詢和子查詢,以GROUP BY為列:
order by的實(shí)現(xiàn)有兩種方式,主要就是按用沒用到索引來區(qū)分蝌借,
根據(jù)索引字段排序指蚁,利用索引取出的數(shù)據(jù)已經(jīng)是排好序的自晰,直接返回給客戶端;
沒有用到索引酬荞,將取出的數(shù)據(jù)進(jìn)行一次排序操作后返回給客戶端。這時(shí)會(huì)大量耗費(fèi)數(shù)據(jù)庫服務(wù)器的計(jì)算性能枪向。
【推薦】針對索引字段使用 >, >=, =, <, <=, IF NULL 和 BETWEEN 將會(huì) 使用索引牲剃,如果對某個(gè)索引字段進(jìn)行 LIKE 查詢雄可,使用 LIKE ‘%abc%’ 不能使用索引,使用 LIKE ‘a(chǎn)bc%’ 將能夠使用索引
【推薦】如果在 SQL 里使用了 MySQL部分自帶函數(shù)数苫,而且自帶函數(shù)使用到索引字段,索引將失效
【強(qiáng)制】避免直接使用 select *,只取需要的字段虐急,增加使用覆蓋索引使用的可能。
【推薦】連表查詢的情況下被辑,要確保關(guān)聯(lián)條件的數(shù)據(jù)類型一致敬惦,避免嵌套子查詢。
【強(qiáng)制】where 語句中盡量不要使用 CASE [WHEN]條件,
反例:SELECT * FROM logs WHERE CASE WHEN in_username = "" THEN true ELSE username LIKE CONCAT("%",in_username,"%") END AND CASE WHEN time = "" THEN true ELSE time = in_time END ORDER BY id DESC LIMIT in_page,in_limit;
其中in_username,in_time是傳入的參數(shù)俄删,這樣寫的后果是當(dāng)數(shù)據(jù)量很大的情況下查詢會(huì)很慢,原因是WHERE條件語句中case when后面的username和time字段的索引將失效!
【推薦】當(dāng)只要一行數(shù)據(jù)時(shí)使用 LIMIT 1臊诊,因?yàn)長IMIT 1能有效縮短查詢時(shí)間斜脂。下面是查詢的結(jié)果對比。
【強(qiáng)制】不能使用觸發(fā)器
MySQL中觸發(fā)器是行觸發(fā)的帚戳,每次增加威兜、修改或者刪除記錄都會(huì)觸發(fā)進(jìn)行處理庐椒,編寫過于復(fù)雜的觸發(fā)器或者增加過多的觸發(fā)器對記錄的插入、更新约谈、刪除操作會(huì)有比較嚴(yán)重的影響,因此不要將應(yīng)用的處理邏輯過多地依賴于觸發(fā)器來處理泼橘。觸發(fā)器的功能通陈跹可以用其他方式實(shí)現(xiàn),確實(shí)需要采用觸發(fā)器靡菇,請聯(lián)系DBA進(jìn)行確認(rèn)。
- 【推薦】避免使用視圖
視圖可能導(dǎo)致執(zhí)行計(jì)劃錯(cuò)亂厦凤,影響SQL運(yùn)行效率。對視圖的修改椎木,數(shù)據(jù)庫必須把它轉(zhuǎn)化為對基本表的信息修改博烂,不便于維護(hù)。
- 【強(qiáng)制】對同一個(gè)表的多次alter操作必須合并為一次操作
MySQL對表的修改絕大部分操作都需要鎖表并重建表,而鎖表則會(huì)對線上業(yè)務(wù)造成影響禽篱。為減少這種影響,必須把對表的多次alter操作合并為一次操作。例如,要給表t增加一個(gè)字段b,同時(shí)給已有的字段aa建立索引,
通常的做法分為兩步: alter table t add column b varchar(10);
然后增加索引: alter table t add index idx_aa(aa);
正確的做法是: alter table t add column b varchar(10),add index idx_aa(aa);
- 【推薦】避免多余的排序烤礁。使用GROUP BY 時(shí)肥照,默認(rèn)會(huì)進(jìn)行排序,當(dāng)你不需要排序時(shí)舆绎,可以使用order by null。
例如Select a.OwnerUserID,count(*) cnt from DP_MessageList a group by a.OwnerUserID order by null;
二、Sharding-jdbc開發(fā)規(guī)范
【強(qiáng)制】設(shè)計(jì)窥突、類型硫嘶、索引和SQL上首先應(yīng)該遵循MySQL規(guī)范所提內(nèi)容
【推薦】如果數(shù)據(jù)有時(shí)效性,則建議按時(shí)間分表或者分庫 沦疾。如果所有數(shù)據(jù)熱度相同,則建議根據(jù)hash或者其他手段分庫分表刨秆。
【強(qiáng)制】如果多個(gè)獨(dú)立業(yè)務(wù)需要共用一臺(tái)DB的話忆畅,不同業(yè)務(wù)的表必須放在不同的database(schema)里。
主要考慮管理便捷和安全家凯。
【強(qiáng)制】對于分庫分表,每一個(gè)表的索引結(jié)構(gòu)及名稱都必須一致肆饶。
【推薦】分庫分表數(shù)量必須按2的N次方岖常,便于未來實(shí)現(xiàn)秒級擴(kuò)容方案。
【強(qiáng)制】設(shè)計(jì)階段板惑,必須使用能路由致多節(jié)點(diǎn)(即支持分片)的方式進(jìn)行數(shù)據(jù)庫設(shè)計(jì)偎快。
sharding-jdbc支持路由只單數(shù)據(jù)節(jié)點(diǎn)和路由致多數(shù)據(jù)節(jié)點(diǎn)的方式進(jìn)行路由連接,路由致單數(shù)據(jù)節(jié)點(diǎn)的方式能100%兼容MySQL, 而路由致多數(shù)據(jù)節(jié)點(diǎn)則無法做到100%兼容晒夹。但是,在使用sharding-jdbc時(shí)喷好,在設(shè)計(jì)階段读跷,必須使用路由致多節(jié)點(diǎn)的方式進(jìn)行數(shù)據(jù)庫設(shè)計(jì)。
正例1:對于只需要1個(gè)真實(shí)表的邏輯表
DB00
├-- T_ORDER_00
配置時(shí),數(shù)據(jù)節(jié)點(diǎn)如下
DB00. T_ORDER_00
正例2:自定義的多個(gè)真實(shí)表荡短,例如:
DB00
├── T_ORDER_00
└── T_ORDER_01
DB01
├── T_ORDER_02
├── T_ORDER_03
└── T_ORDER_04數(shù)據(jù)節(jié)點(diǎn)配置如下:DB00.T_ORDER_00, DB00.T_ORDER_01, DB01.T_ORDER_02, DB01. T_ORDER_03, DB01. T_ORDER_04
- 【強(qiáng)制】分庫分表的數(shù)據(jù)庫或者表的命名規(guī)范哆键,建議后綴采用統(tǒng)一的以數(shù)字結(jié)尾的格式,如位數(shù)不足籍嘹,通過補(bǔ)充0保證位數(shù)一致。如:t_user 表分512 個(gè)表锭沟。
正例:T_USER_001, T_USER_002 …. T_USER_512.反例:T_USER_1, T_USER_2 …. T_USER_512.
- 【推薦】在考慮sharding-jdbc進(jìn)行數(shù)據(jù)庫設(shè)計(jì)時(shí)识补,請仔細(xì)考慮如何分片族淮,并最好使用單一鍵作為分片鍵
單一鍵作為分片鍵凭涂,其對應(yīng)的分片算法是標(biāo)準(zhǔn)分片算法( StandardShardingAlgorithm)。單一分片算法有個(gè)好處是它可以使用SQL的 =蝙斜、IN澎胡、BETWEEN AND、>攻谁、<、>=个曙、<=等關(guān)鍵進(jìn)行快速匹配和運(yùn)算受楼。
可以考慮通過在分片鍵之外其他列包含分片信息垦搬,通過自定義ComplexShardingAlgorithm進(jìn)行特殊處理艳汽,比如分片鍵是user_id,可以將Sharding信息作為order_num 的前綴來實(shí)現(xiàn)分片邏輯。
- 【推薦】查詢分頁充分利用ID的連續(xù)性或者區(qū)間性進(jìn)行優(yōu)化糟趾。
- 在sharding-jdbc中偏移量過大的分頁會(huì),查詢偏移量過大的分頁會(huì)導(dǎo)致數(shù)據(jù)庫獲取數(shù)據(jù)性能低下义郑。
- 由于LIMIT并不能通過索引查詢數(shù)據(jù),因此如果可以保證ID的連續(xù)性交汤,通過ID進(jìn)行分頁是比較好的解決方案
- 或通過記錄上次查詢結(jié)果的最后一條記錄的ID進(jìn)行下一頁的查詢:
反例: SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10正例: SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id正例: SELECT * FROM t_order WHERE id > 100000 LIMIT 10
- 【強(qiáng)制】除了使用直接指定單表的直接路由劫笙,不要使用 隨便使用hint會(huì)使代碼變得復(fù)雜和難以理解。
原因是這樣填大,把處理的邏輯一半寫在自己的業(yè)務(wù)邏輯里,一半依靠sharding-jdbc的hint, 只會(huì)是系統(tǒng)更難理解和復(fù)雜圈浇,建議分多個(gè)SQL在業(yè)務(wù)邏輯靴寂,也可以跨微服務(wù)處理。
【強(qiáng)制】訪問分片表的SQL必須包含分片鍵百炬。
【推薦】分片規(guī)則一致的主表和子表建議配置為綁定表,這樣聯(lián)表查詢不會(huì)笛卡爾積現(xiàn)象庶弃,關(guān)聯(lián)查詢效率將大大提升蜜宪。