mysql數據庫類型設計（mysql數據庫基本類型）

本文目錄一覽：

• 1、北大青鳥java培訓：Mysql數據庫的設計和優化？
• 2、MySQL 數據表優化設計（三）：CHAR 和 VARCHAR 怎麼選？
• 3、mysql 存儲金額類型，用什麼數據類型比較可靠，一般企業數據用什麼數據類型?
• 4、MYSQL數據庫設計數據類型選擇需要注意哪些地方
• 5、MYSQL數據庫的物理設計都包括哪些內容，怎麼設計？

北大青鳥java培訓：Mysql數據庫的設計和優化？

在JAVA開發中數據庫的學習也是我們需要了解的，截下來幾篇文章都是關於數據庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！  數據庫的設計  數據庫設計是基礎，數據庫優化是建立在設計基礎之上的。

好的數據庫一定擁有好的設計。

  數據庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。

  數據庫的三大範式  第一範式1NF:所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

  第二範式2Nf:第二範式在第一範式的基礎之上更進一層。

第二範式需要確保數據庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。

也就是說在一個數據庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張數據庫表中。

  第三範式3Nf:所有字段必須與主鍵直接相關，而不是間接相關。

也可以理解為字段不要和其他非主鍵字段相關.  注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使字段盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反.  數據庫設計的步驟  需求分析階段  準確了解與分析用戶需求(包括數據與處理)。

是整個設計過程的基礎，是最困難、最耗費時間的一步。

  概念結構設計階段  是整個數據庫設計的關鍵–設計數據庫的E-R模型圖，確認需求信息的正確和完整  Entity_Relationship—實體之間的關係  一對一  一對多  多對一

MySQL 數據表優化設計（三）：CHAR 和 VARCHAR 怎麼選？

VARCHAR 和 CHAR 是兩種主要的字符串類型，用於存儲字符。不幸的是，由於實現的方式依賴於存儲引擎，因此很難解釋這些字符串在磁盤和內存中如何存儲，除了除了常用的 InnoDB 和 MyISAM 外，假設你使用了其他存儲引擎，應當仔細閱讀存儲引擎的文檔。

VARCHAR 存儲可變長度的字符串，也是最常用的字符數據類型。相比固定長度的類型，VARCHAR 所需的存儲空間更小，它會儘可能少地使用存儲空間（例如，短的字符串佔據的空間）。對於 MyISAM 來說，如果創建表的時候指定了 ROW_FORMAT=FIXED 的話，那麼會使用固定的空間存儲字段而導致空間浪費。VARCHAR 使用1-2個額外的位元組存儲字符串的長度：當最大長度低於255位元組的時候使用1個位元組，如果更多的話就使用2個位元組。因此，拉丁字符集的 VARCHAR(10)會使用11個位元組的存儲空間，而 VARCHAR(1000)則會使用1002個位元組的存儲空間。

VARCHAR 由於能夠節省空間，因此可以改善性能。但是，由於長度可變，當更新數據表的時候數據行的存儲空間會變化，這一定程度上會帶來額外的開銷。如果數據行的長度導致原有的存儲位置無法存放，那麼不同的存儲引擎會做不同的處理。例如 MyISAM 可能產生數據行的碎片，而 InnoDB 需要進行磁盤分頁來存放更新後的數據行。

通常，如果最大的列長度遠遠高於平均長度的話（例如可選的備註字段），使用 VARCHAR 是划算的，同時如果更新的頻次很低，那麼碎片化也不會是一個問題。需要注意的是，如果使用的是 UTF-8字符集，則實際存儲的位元組長度是根據字符定的。對於中文，推薦的存儲字符集是 utf8mb4。

CHAR 類型的長度是固定的，MySQL 會對每個字段分配足夠的存儲空間。 存儲CHAR 類型值的時候，MySQL 會移除後面多出來的空字符 。值是使用空字符進行對齊以便進行比較。對於短的字符串來說，使用 CHAR 更有優勢，而如果所有的值的長度幾乎一致的話，就可以使用 CHAR。例如存儲用戶密碼的MD5值時使用 CHAR 就更合適，這是因為 MD5的長度總是固定的。同時，對於字段值經常改變的數據類型來說，CHAR 相比 VARCHAR 也更有優勢，因為 CHAR 不會產生碎片。對於很短的數據列，使用 CHAR 比 VARCHAR更高效，例如使用CHAR(1)存儲邏輯值的 Y 和 N，這種情況下只需要1個位元組，而 VARCHAR 需要2個位元組。

對於移除空字符這個特性會感覺奇怪，我們舉個例子：

按上面的結果插入數據表後，string2中的前置空格不會移除，但使用 CHAR 類型存儲時，string3尾隨空格會被移除，使用 SQL 查詢結果來檢驗一下：

得出來的結果如下，可以看到 CHAR 類型的 string3後面的空格被移除了，而 VARCHAR類型的沒有。這種情況大多數時候不會有什麼問題，實際在應用中也經常會使用 trim 函數移除兩端的空字符，但是如果確實需要存儲空格的時候，那就需要注意不要選擇使用 CHAR 類型：

數據如何存儲是由存儲引擎決定的，而且存儲引擎處理固定長度和可變長度的數據的方式並不相同。Memory 引擎使用固定大小的行，因此它需要分配最大可能的存儲空間——即便數據長度是可變的。但是，對於字符串的對齊和空字符截斷是由 MySQL 服務端完成的，因此所有存儲引擎都是一樣的。

與 CHAR 和 VARCHAR 相似的是 BINARY和 VARBINARY，用於存儲二進制位元組字符，BINARY 的對齊使用字符0的位元組值來對齊，並且再獲取值的時候不會截斷。如果需要使用字符的位元組值而不是字符的話，使用 BINARY 會更高效，這是因為比較時，一方面不需要考慮大小寫，另一方面是MySQL一次只比較一個位元組。

mysql 存儲金額類型，用什麼數據類型比較可靠，一般企業數據用什麼數據類型?

對於遊戲幣等代幣，一般存儲為int類型是可行的。問題在於越界，int類型長度為11位。

在存儲人民幣相關的金額的時候，則只能存儲到9長度的人民幣，也就是說，最大只能存儲999999999，不到10億的數值，如果業務增長很快的話，就會給自己留下隱患。

Decimal：Decimal為專門為財務相關問題設計的數據類型。

DECIMAL從MySQL 5.1引入，列的聲明語法是DECIMAL(M,D)。在MySQL 5.1中，參量的取值範圍如下：M是數字的最大數（精度）。其範圍為1～65（在較舊的MySQL版本中，允許的範圍是1～254），M 的默認 值是10。

D是小數點右側數字的數目（標度）。其範圍是0～30，但不得超過M。說明：float佔4個位元組，double佔8個位元組，decimail(M,D)佔M+2個位元組。

如DECIMAL(5,2) 的最大值為9 9 9 9 . 9 9，因為有7 個位元組可用。能夠解決數據的範圍和精度的問題。

擴展資料

MySQL數據類型DECIMAL用法：

MySQL DECIMAL數據類型用於在數據庫中存儲精確的數值。我們經常將DECIMAL數據類型用於保留準確精確度的列，例如會計系統中的貨幣數據。

要定義數據類型為DECIMAL的列，請使用以下語法：column_name  DECIMAL(P,D);

在上面的語法中：

P是表示有效數字數的精度。 P範圍為1〜65。

D是表示小數點後的位數。 D的範圍是0~30。MySQL要求D小於或等於(=)P。

DECIMAL(P，D)表示列可以存儲D位小數的P位數。十進制列的實際範圍取決於精度和刻度。

與INT數據類型一樣，DECIMAL類型也具有UNSIGNED和ZEROFILL屬性。 如果使用UNSIGNED屬性，則DECIMAL UNSIGNED的列將不接受負值。

如果使用ZEROFILL，MySQL將把顯示值填充到0以顯示由列定義指定的寬度。 另外，如果我們對DECIMAL列使用ZERO FILL，MySQL將自動將UNSIGNED屬性添加到列。

MYSQL數據庫設計數據類型選擇需要注意哪些地方

•VARCHAR和CHAR類型，varchar是變長的，需要額外的1-2個位元組存儲，能節約空間，可能會對性能有幫助。但由於是變長，可能發生碎片，如更新數據；

•使用ENUM代替字符串類型，數據實際存儲為整型。

•字符串類型

•要儘可能地避免使用字符串來做標識符，因為它們佔用了很多空間並且通常比整數類型要慢。特別注意不要在MYISAM表上使用字符串標識符。MYISAM默認情況下為字符串使用了壓縮索引（Packed Index），這使查找更為緩慢。據測試，使用了壓縮索引的MYISAM表性能要慢6倍。

•還要特別注意完全『隨機』的字符串，例如由MD5（）、SHA1（）、UUID（）產生的。它們產生的每一個新值都會被任意地保存在很大的空間範圍內，這會減慢INSERT及一些SELECT查詢。1）它們會減慢INSERT查詢，因為插入的值會被隨機地放入索引中。這會導致分頁、隨機磁盤訪問及聚集存儲引擎上的聚集索引碎片。2)它們會減慢SELECT查詢，因為邏輯上相鄰的行會分佈在磁盤和內存中的各個地方。3）隨機值導致緩存對所有類型的查詢性能都很差，因為它們會使緩存賴以工作的訪問局部性失效。如果整個數據集都變得同樣「熱」的時候，那麼把特定部分的數據緩存到內存中就沒有任何的優勢了。並且如果工作集不能被裝入內存中，緩存就會進行很多刷寫的工作，並且會導致很多緩存未命中。

•如果保存UUID值，就應該移除其中的短橫線，更好的辦法是使用UHEX（）把UUID值轉化為16位元組的數字，並把它保存在BINARY（16）列中。

MYSQL數據庫的物理設計都包括哪些內容，怎麼設計？

Log File物理結構

從 ib_logfile0和 ib_logfile1這兩個文件的物理結構可以看出，在Log Header部分還是有些許差異的， ib_logfile0會多一些額外的信息，主要是checkpoint信息。

並且每個Block的單位是512位元組，對應到磁盤每個扇區也是512位元組，因此redo log寫磁盤是原子寫，保證能夠寫成功，而不像index page一樣需要double write來保證安全寫入。

我們依次從上到下來看每個Block的結構

Log File Header Block

Log Goup ID，可能會配置多個redo組，每個組對應一個id，當前都是0，佔用4位元組

Start LSN，這個redo log文件開始日誌的lsn，佔用8位元組

Log File Number，總是為0，佔用4位元組

Created By，備份程序所佔用的位元組數，佔用32位元組

另外在ib_logfile0中會有兩個checkpoint block，分別是 LOG_CHECKPOINT_1/ LOG_CHECKPOINT_2，兩個記錄InnoDB Checkpoint信息的字段，分別從文件頭的第二個和第四個block開始記錄，並且只在每組log的第一個文件中存在，組內其他文件雖然沒有checkpoint相關信息，但是也會預留相應的空間出來。這裡為什麼有兩個checkpoint的呢？原因是設計為交替寫入，避免因為介質失敗而導致無法找到可用的checkpoint的情況。

Log blocks

請點擊輸入圖片描述

log block結構分為日誌頭段、日誌記錄、日誌尾部

Block Header，佔用12位元組

Data部分

Block tailer，佔用4位元組

Block Header

這個部分是每個Block的頭部，主要記錄的塊的信息

Block Number，表示這是第幾個block，佔用4位元組，是通過LSN計算得來的，佔用4位元組

Block data len，表示該block中有多少位元組已經被使用了，佔用2位元組

First Rec offet，表示該block中作為第一個新的mtr開始的偏移量，佔用2位元組

Checkpoint number，表示該log block最後被寫入時的檢查點的值，佔用4位元組