MySQL開發規範與使用技巧總結

来源:https://www.cnblogs.com/a609251438/archive/2019/11/15/11865929.html
-Advertisement-
Play Games

命名規範 1.庫名、表名、欄位名必須使用小寫字母,並採用下劃線分割。 a)MySQL有配置參數lower_case_table_names,不可動態更改,Linux系統預設為 0,即庫表名以實際情況存儲,大小寫敏感。如果是1,以小寫存儲,大小寫不敏感。如果是2,以實際情況存儲,但以小寫比較。 b)如 ...


命名規範

1.庫名、表名、欄位名必須使用小寫字母,並採用下劃線分割。

a)MySQL有配置參數lower_case_table_names,不可動態更改,Linux系統預設為 0,即庫表名以實際情況存儲,大小寫敏感。如果是1,以小寫存儲,大小寫不敏感。如果是2,以實際情況存儲,但以小寫比較。

b)如果大小寫混合使用,可能存在abc,Abc,ABC等多個表共存,容易導致混亂。

c)欄位名顯示區分大小寫,但實際使⽤用不區分,即不可以建立兩個名字一樣但大小寫不一樣的欄位。

d)為了統一規範, 庫名、表名、欄位名使用小寫字母。

2.庫名、表名、欄位名禁止超過32個字元。

庫名、表名、欄位名支持最多64個字元,但為了統一規範、易於辨識以及減少傳輸量,禁止超過32個字元。

3.使用INNODB存儲引擎。

INNODB引擎是MySQL5.5版本以後的預設引擘,支持事務、行級鎖,有更好的數據恢復能力、更好的併發性能,同時對多核、大記憶體、SSD等硬體支持更好,支持數據熱備份等,因此INNODB相比MyISAM有明顯優勢。

4.庫名、表名、欄位名禁止使用MySQL保留字。

當庫名、表名、欄位名等屬性含有保留字時,SQL語句必須用反引號引用屬性名稱,這將使得SQL語句書寫、SHELL腳本中變數的轉義等變得⾮非常複雜。

5.禁止使用分區表。

分區表對分區鍵有嚴格要求;分區表在表變大後,執⾏行DDL、SHARDING、單表恢復等都變得更加困難。因此禁止使用分區表,並建議業務端手動SHARDING。

6.建議使用UNSIGNED存儲非負數值。

同樣的位元組數,非負存儲的數值範圍更大。如TINYINT有符號為 -128-127,無符號為0-255。

7.建議使用INT UNSIGNED存儲IPV4。

用UNSINGED INT存儲IP地址占用4位元組,CHAR(15)則占用15位元組。另外,電腦處理整數類型比字元串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址,通過MySQL函數inet_ntoa和inet_aton來進行轉化。IPv6地址目前沒有轉化函數,需要使用DECIMAL或兩個BIGINT來存儲。例如:

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);

209.207.224.40

 

8.強烈建議使用TINYINT來代替ENUM類型。ENUM類型在需要修改或增加枚舉值時,需要線上DDL,成本較高;ENUM列值如果含有數字類型,可能會引起預設值混淆。

9.使用VARBINARY存儲大小寫敏感的變長字元串或二進位內容。

VARBINARY預設區分大小寫,沒有字元集概念,速度快。

類型固定占用4位元組存儲

例如INT(4)僅代表顯示字元寬度為4位,不代表存儲長度。數值類型括弧後面的數字只是表示寬度而跟存儲範圍沒有關係,比如INT(3)預設顯示3位,空格補齊,超出時正常顯示,Python、Java客戶端等不具備這個功能。

11.區分使用DATETIME和TIMESTAMP。

存儲年使用YEAR類型。存儲日期使用DATE類型。存儲時間(精確到秒)建議使用TIMESTAMP類型。

DATETIME和TIMESTAMP都是精確到秒,優先選擇TIMESTAMP,因為TIMESTAMP只有4個位元組,而DATETIME8個位元組。同時TIMESTAMP具有自動賦值以及⾃自動更新的特性。註意:在5.5和之前的版本中,如果一個表中有多個timestamp列,那麼最多只能有一列能具有自動更新功能。如何使用TIMESTAMP的自動賦值屬性?

a)自動初始化,而且自動更新:
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP


b)只是自動初始化:
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP


c)自動更新,初始化的值為0:
column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值為0:
1 column1 TIMESTAMP DEFAULT 0

 

12.索引欄位均定義為NOT NULL。

a)對錶的每一行,每個為NULL的列都需要額外的空間來標識。

b)B樹索引時不會存儲NULL值,所以如果索引欄位可以為NULL,索引效率會下降。

c)建議用0、特殊值或空串代替NULL值。

 

MySQL使用技巧

1.將大欄位、訪問頻率低的欄位拆分到單獨的表中存儲,分離冷熱數據。

有利於有效利用緩存,防⽌止讀入無用的冷數據,較少磁碟IO,同時保證熱數據常駐記憶體提⾼高緩存命中率。

2.禁止在資料庫中存儲明文密碼。

採用加密字元串存儲密碼,並保證密碼不可解密,同時採用隨機字元串加鹽保證密碼安全。

3.表必須有主鍵,推薦使用UNSIGNED自增列作為主鍵。

表沒有主鍵,INNODB會預設設置隱藏的主鍵列;沒有主鍵的表在定位數據行的時候非常困難,也會降低基於行複製的效率。

4.禁止冗餘索引。

索引是雙刃劍,會增加維護負擔,增⼤大IO壓力。(a,b,c)、(a,b),後者為冗餘索引。可以利用首碼索引來達到加速目的,減輕維護負擔

5.禁止重覆索引。

primary key a;uniq index a;重覆索引增加維護負擔、占用磁碟空間,同時沒有任何益處。

6.不在低基數列上建立索引,例如“性別”。

大部分場景下,低基數列上建立索引的精確查找,相對於不建立索引的全表掃描沒有任何優勢,而且增大了IO負擔。

7.合理使用覆蓋索引減少IO,避免排序。

覆蓋索引能從索引中獲取需要的索引欄位,從⽽而避免回表進行二次查找,節省IO。

INNODB存儲引擎中,secondary index(非主鍵索引,又稱為輔助索引、二級索引)沒有直接存儲行地址,而是存儲主鍵值。

如果用戶需要查詢secondary index中所不包含的數據列,則需要先通過secondary index查找到主鍵值,然後再通過主鍵查詢到其他數據列,因此需要查詢兩次。覆蓋索引則可以在⼀一個索引中獲取所有需要的數據,因此效率較高。

例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主鍵,適當時候可以將索引添加為index(uid,email),以獲得性能提升。

8.用IN代替OR。SQL語句中IN包含的值不應過多,應少於1000個。

IN是範圍查找,MySQL內部會對IN的列表值進行排序後查找,比OR效率更高。

9.表字元集使用UTF8,必要時可申請使用UTF8MB4字元集。

a)UTF8字元集存儲漢字占用3個位元組,存儲英文字元占用一個位元組。

b)UTF8統一而且通用,不會出現轉碼出現亂碼風險。

c)如果遇到EMOJ等表情符號的存儲需求,可申請使用UTF8MB4字元集。

10.用UNION ALL代替UNION。

UNION ALL不需要對結果集再進行排序。

11.禁止使用order by rand()。

order by rand()會為表增加一個偽列,然後用rand()函數為每一行數據計算出rand()值,然後基於該行排序,這通常都會生成磁碟上的臨時表,因此效率非常低。建議先使用rand()函數獲得隨機的主鍵值,然後通過主鍵

獲取數據。

12.建議使用合理的分頁方式以提高分頁效率。

假如有類似下麵分頁語句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 1000010;

 

這種分頁方式會導致大量的io,因為MySQL使用的是提前讀取策略。

推薦分頁方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC

LIMIT 10.SELECT * FROM table inner JOIN (SELECT id FROM table

ORDER BY TIME LIMIT 1000010) as tUSING(id)

 

13.SELECT只獲取必要的欄位,禁⽌止使用SELECT *。減少網路帶寬消耗;

能有效利用覆蓋索引;

表結構變更對程式基本無影響。

14.SQL中避免出現now()、rand()、sysdate()、current_user()等不確定結果的函數。

語句級複製場景下,引起主從數據不一致;不確定值的函數,產⽣生的SQL語句無法利用QUERY CACHE。

15.採用合適的分庫分表策略。例如千庫十表、十庫百表等。

採用合適的分庫分表策略,有利於業務發展後期快速對資料庫進行水平拆分,同時分庫可以有效利⽤用MySQL

的多線程複製特性。

16.減少與資料庫交互次數,儘量採用批量SQL語句。

使用下麵的語句來減少和db的交互次數:

a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE 
d)INSERT INTO VALUES()

 

17.拆分複雜SQL為多個小SQL,避免大事務。

簡單的SQL容易使⽤用到MySQL的QUERY CACHE;減少鎖表時間特別是MyISAM;可以使用多核 CPU。18.對同一個表的多次alter操作必須合併為一次操作。

mysql對錶的修改絕大部分操作都需要鎖表並重建表,而鎖表則會對線上業務造成影響。為減少這種影響,必須把對錶的多次alter操作合併為一次操作。例如,要給表t增加一個欄位b,同時給已有的欄位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);

 

19.避免使用存儲過程、觸發器、視圖、自定義函數等。

這些高級特性有性能問題,以及未知BUG較多。業務邏輯放到資料庫會造成資料庫的DDL、SCALE OUT、

SHARDING等變得更加困難。

20.禁止有super許可權的應用程式賬號存在。

安全第一。super許可權會導致read only失效,導致較多詭異問題而且很難追蹤。

21.不要在MySQL資料庫中存放業務邏輯。

資料庫是有狀態的服務,變更複雜而且速度慢,如果把業務邏輯放到資料庫中,將會限制業務的快速發展。建議把業務邏輯提前,放到前端或中間邏輯層,而把資料庫作為存儲層,實現邏輯與存儲的分離。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 晚上將老伺服器程式從win2008部署在新的雲伺服器win2012上,其實就是複製過去改改配置,啟動時突然報不能綁定到埠號:9194原因:Cannot assign requested address: JVM_Bind。 原因分析 1、首先分析可能是埠占用,輸入netstat -an,發現並沒 ...
  • 前段時間跟大家探討了 Vim 的顏色方案的話題,取得了不錯的反響,大家可以點擊以下鏈接回顧那篇文章: "誰說 Vim 不好用?送你一個五彩斑斕的編輯器!" 今天我們來介紹 Vim 的分屏功能 。 為什麼需要分屏功能?其實需求場合有很多。比如,我現在屏幕很大,但我們的代碼一般是左對齊,右邊很空,這樣我 ...
  • 本文主要記錄了連接到MySQL資料庫時出現的問題以及解決辦法。 問題重現 ...
  • 筆者一直維護的穩定基礎服務測試環境不穩定了,這能忍!盤他,雖然不一定能完全盤的了。 背景: hrexternal 基礎服務對外提供公司員工獲取的多個介面,很多介面訪問頻率比較高,加了緩存,使用的是redis,但是redis最近2個月測試環境已經出問題了,時不時的報錯,之前流程平臺也報過錯,只不過是隨 ...
  • MySQL學習——管理用戶許可權 摘要:本文主要學習了使用DCL語句管理用戶許可權的方法。 瞭解用戶許可權 什麼是用戶 用戶,指的就是操作和使用MySQL資料庫的人。使用MySQL資料庫需要用戶先通過用戶名和密碼進行連接,然後才能進行操作,不同的用戶可以設置不同的許可權,讓某些用戶只能操作許可權範圍內的數據, ...
  • 1.什麼是Grafana? Grafana是一款採用go語言和Angular框架編寫的開源的可視化工具,主要用於大規模指標數據的可視化展示,提供包括折線圖,餅圖,儀錶盤等多種監控數據可視化UI,是網路架構和應用分析中最流行的時序數據可視化工具,支持多種不同的時序資料庫數據源。github地址:htt ...
  • 一.備份的原因 運維工作的核心簡單概括就兩件事: 1)第一個是保護公司的數據. 2)第二個是讓網站能7 24小時提供服務(用戶體驗)。 1)備份就是為了恢復。 2)儘量減少數據的丟失(公司的損失) 二.備份的類型 冷備份: 這些備份在用戶不能訪問數據時進行,因此無法讀取或修改數據。這些離線備份會阻止 ...
  • [20191115]oracle實例占用記憶體計算.txt--//以前學習oracle資料庫時,總想瞭解實例占用記憶體多少,我曾經在一些會議底下問過一位高手,對方說計算這個相對很難,許多東西是共用的.--//很難有一個好方法計算,不過當時給了一個命令pmap,可以查看進程的記憶體使用情況,我當時使用命令看 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...