MySQL基礎篇(02)：從五個維度出發，審視表結構設計

来源:https://www.cnblogs.com/cicada-smile/archive/2019/12/27/12105387.html
-Advertisement-
Play Games

本文源碼： "GitHub·點這裡" || "GitEE·點這裡" 一、數據場景 1、表結構簡介 任何工具類的東西都是為瞭解決某個場景下的問題，比如Redis緩存系統熱點數據，ClickHouse解決海量數據的實時分析，MySQL關係型資料庫存儲結構化數據。數據的存儲則需要設計對應的表結構，清楚的表 ...


本文源碼：GitHub·點這裡 || GitEE·點這裡

一、數據場景

1、表結構簡介

任何工具類的東西都是為瞭解決某個場景下的問題，比如Redis緩存系統熱點數據，ClickHouse解決海量數據的實時分析，MySQL關係型資料庫存儲結構化數據。數據的存儲則需要設計對應的表結構，清楚的表結構，有助於快速開發業務，和理解系統。表結構的設計通常從下麵幾個方面考慮：業務場景、設計規範、表結構、欄位屬性、數據管理。

2、用戶場景

例如存儲用戶基礎信息數據，通常都會下麵幾個相關表結構：用戶信息表、單點登錄表、狀態管理表、支付賬戶表等。

  • 用戶信息表

存儲用戶三要素相關信息：姓名，手機號，身份證，登錄密碼，郵箱等。

CREATE TABLE `ms_user_center` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
  `user_name` varchar(20) NOT NULL COMMENT '用戶名',
  `real_name` varchar(20) DEFAULT NULL COMMENT '真實姓名',
  `pass_word` varchar(32) NOT NULL COMMENT '密碼',
  `phone` varchar(20) NOT NULL COMMENT '手機號',
  `email` varchar(32) DEFAULT NULL COMMENT '郵箱',
  `head_url` varchar(100) DEFAULT NULL COMMENT '用戶頭像URL',
  `card_id` varchar(32) DEFAULT NULL COMMENT '身份證號',
  `user_sex` int(1) DEFAULT '1' COMMENT '用戶性別:0-女,1-男',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';
  • 單點登錄表

用意是在多個業務系統中，用戶登錄一次就可以訪問所有相互信任的業務子系統，是聚合業務平臺常用的解決方案。

CREATE TABLE `ms_user_sso` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `sso_id` varchar(32) NOT NULL COMMENT '單點信息編號ID',
  `sso_code` varchar(32) NOT NULL COMMENT '單點登錄碼,唯一核心標識',
  `log_ip` varchar(32) DEFAULT NULL COMMENT '登錄IP地址',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶單點登錄表';
  • 狀態管理表

系統用戶在使用時候可能出現多個狀態，例如賬戶凍結、密碼鎖定等，把狀態聚合到一起，可以更加方便的管理和驗證。

CREATE TABLE `ms_user_status` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `account_status` int(1) DEFAULT '1' COMMENT '賬戶狀態：0-凍結,1-未凍結',
  `real_name_status` int(1) DEFAULT '0' COMMENT '實名認證狀態：0-未實名,1-已實名',
  `pay_pass_status` int(1) DEFAULT '0' COMMENT '支付密碼是否設置：0-未設置,1-設置',
  `wallet_pass_status` int(1) DEFAULT '0' COMMENT '錢包密碼是否設置：0-未設置,1-設置',
  `wallet_status` int(1) DEFAULT '1' COMMENT '錢包是否凍結:0-凍結,1-未凍結',
  `email_status` int(1) DEFAULT '0' COMMENT '郵箱狀態:0-未激活,1-激活',
  `message_status` int(1) DEFAULT '1' COMMENT '簡訊提醒開啟：0-未開啟,1-開啟',
  `letter_status` int(1) DEFAULT '1' COMMENT '站內信提醒開啟：0-未開啟,1-開啟',
  `emailmsg_status` int(1) DEFAULT '0' COMMENT '郵件提醒開啟：0-未開啟,1-開啟',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶狀態表';
  • 支付賬戶表

用戶交易的核心表，存儲用戶相關的賬戶資金信息。

CREATE TABLE `ms_user_wallet` (
  `wallet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '錢包ID',
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `wallet_pwd` varchar(32) DEFAULT NULL COMMENT '錢包密碼',
  `total_account` decimal(20,2) DEFAULT '0.00' COMMENT '賬戶總額',
  `usable_money` decimal(20,2) DEFAULT '0.00' COMMENT '可用餘額',
  `freeze_money` decimal(20,2) DEFAULT '0.00' COMMENT '凍結金額',
  `freeze_time` datetime DEFAULT NULL COMMENT '凍結時間',
  `thaw_time` datetime DEFAULT NULL COMMENT '解凍時間',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  `state` int(1) DEFAULT '1' COMMENT '是否可用,0-不可用,1-可用',
  PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶錢包';

二、設計規範

1、涉及模塊

通過上面幾個表設計的案例，可以看到表設計關聯到資料庫的各個方面知識：數據類型，索引，編碼，存儲引擎等。表設計是一個很大的命題，不過也遵循一個基本規範：三範式。

2、三範式

  • 基礎概念

一範式

表的列的具有原子性,不可再分解，即列的信息，不能分解,關係型資料庫MySQL、Oracle等自動的滿足。

二範式

每個事實的數據記錄只會出現一次, 不會冗餘, 通常設計一個主鍵來實現。

三範式

要求一個表中不包含已經存在於其它表的非主鍵信息，例如部門和員工的信息，員工表包含部門表的主鍵ID，則可以關聯獲取相關信息，沒必要在員工表保存相關信息。

  • 優缺點對比

範式化設計

範式化結構設計通常更新快，因為冗餘數據較少，表結構輕巧，也更好的寫入記憶體中。但是查詢起來涉及到關聯，代價非常高，非常損耗查詢性能。

反範式化設計

所有的數據都在一張表中，避免關聯查詢，索引的有效性更高，但是數據的冗餘性極高。

  • 建議結論

上述的兩種設計方式在實際開發中都是不存在的，在實際開發中都是混合使用。比如彙總統計，緩存數據，都會基於反範式化的設計。

三、欄位屬性

合適的欄位類型對於高性能來說非常重要，基本原則如下：簡單的類型占用資源更少；在可以正確存儲數據的情況下，選最小的數據類型。

1、數據類型選擇

  • 整數類型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，根據數據類型範圍合理選擇即可。

  • 實數類型

FLOAT、DOUBLE、DECIMAL，建議資金貨幣相關類型使用高精度DECIMAL存儲，或者把數據成倍擴大為整數，採用BIGINT存儲，不過處理相對麻煩。

  • 字元類型

CHAR、VARCHAR，長度不確定建議採用VARCHAR存儲，不過VARCHAR類型需要額外開銷記錄字元串長度。CHAR適合存儲短字元，或者定長字元串，例如MD5的加密結構。

  • 時間類型

DATETIME、TIMESTAMP，DATETIME保存大範圍的值，精度秒。TIMESTAMP以時間戳的格式，範圍相對較小，效率也相對較高，所以通常情況建議使用。

MySQL的欄位類型有很多種，可以根據數據特性選擇合適的，這裡只描述常見的幾種類型。

2、基礎用法操作

  • 數據類型

修改欄位類型

ALTER TABLE ms_user_sso MODIFY state CHAR(1) DEFAULT '0' ;

ALTER TABLE ms_user_sso 
MODIFY state INT(1) DEFAULT '1' COMMENT '狀態：0不可用,1可用';

修改名稱位置

ALTER TABLE ms_user_sso 
CHANGE log_ip login_ip VARCHAR(32) AFTER update_time ;
  • 索引使用

索引類型：主鍵索引，普通索引，唯一索引，組合索引，全文索引。這裡演示普通索引的操作。MySQL的核心模塊，後續詳說。

添加索引

ALTER TABLE ms_user_wallet ADD INDEX user_id_index(user_id) ;
CREATE INDEX state_index ON ms_user_wallet(state) ;

查看索引

SHOW INDEX FROM ms_user_wallet;

刪除索引

DROP INDEX state_index ON ms_user_wallet ;

修改索引

不具有真正意義上的修改，可以把原有的索引刪除之後，再次添加索引。

  • 外鍵關聯

用處：外鍵關聯的作用保證多個數據表的數據一致性和完整性，建表時先有主表，後有從表；刪除數據表，需要先刪從表，再刪主表。複雜場景不建議使用，實際開發中用的也不多。

添加外鍵

ALTER TABLE ms_user_wallet 
ADD CONSTRAINT user_id_out_key FOREIGN KEY(user_id) REFERENCES ms_user_center(id) ;

刪除外鍵

ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key ;

四、表結構管理

1、查看結構

DESC ms_user_status ;
SHOW CREATE TABLE ms_user_status ;

2、欄位結構

  • 添加欄位
ALTER TABLE ms_user_status 
ADD `delete_time` datetime DEFAULT NULL COMMENT '刪除時間' ;
  • 刪除欄位
ALTER TABLE ms_user_status DROP COLUMN delete_time ;

3、修改表名

ALTER TABLE ms_user_center RENAME ms_user_info ;

4、存儲引擎

  • 存儲引擎
SELECT VERSION() ; SHOW ENGINES ;

MySQL 5.6 支持的存儲引擎有InnoDB、MyISAM、Memory、Archive、CSV、BLACKHOLE等。一般預設使用InnoDB，支持事務管理。該模塊MySQL核心，後續詳解。

  • 修改引擎

數據量大的場景下，存儲引擎修改是一個難度極大的操作，容易會導致表的特性變動，引起各種後續反應，後續會詳說。

ALTER TABLE ms_user_sso ENGINE = MyISAM ;

5、修改編碼

表字元集預設使用utf8，通用，無亂碼風險，漢字3位元組，英文1位元組，utf8mb4是utf8的超集，有存儲4位元組例如表情符號時使用。

  • 查看編碼
SHOW VARIABLES LIKE 'character%';
  • 修改編碼
ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4;

五、數據管理

1、增刪改查

添加數據

INSERT INTO ms_user_sso (
    user_id,sso_id,sso_code,create_time,update_time,login_ip,state
)
VALUES
    (
        '1','SSO7637267','SSO78631273612',
        '2019-12-24 11:56:57','2019-12-24 11:57:01','127.0.0.1','1'
    );

更新數據

UPDATE ms_user_sso SET 
 user_id = '1',sso_id = 'SSO20191224',sso_code = 'SSO20191224',
 create_time = '2019-11-24 11:56:57',update_time = '2019-11-24 11:57:01',
 login_ip = '127.0.0.1',state = '1'
WHERE user_id = '1';

查詢數據

一般情況下都是禁止使用 select* 操作。

SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state 
FROM ms_user_sso WHERE user_id = '1';

刪除數據

DELETE FROM ms_user_sso WHERE user_id = '2' ;

不帶where條件，就是刪除全部數據。原則上不允許該操作，優化篇會詳解。TRUNCATE TABLE也是清空表數據，但是占用的資源相對較少。

2、數據安全

  • 不可逆加密

這類加密演算法，多用來做數據驗證操作，比如常見的密碼驗證。

SELECT MD5('cicada')='94454b1241ad2cfbd0c44efda1b6b6ba' ;
SELECT SHA('cicada')='0501746a2e4fd34e1d14015fc4d58309585edc7d';
SELECT PASSWORD('smile')='*B4FB95D86DCFC3F33A3852714DC742C77504479D' ;
  • 可逆加密

安全性要求高的系統，需要做三級等保，對數據的安全性極高，數據在存儲時必須加密入庫，取出時候需要解密，這些就需要可逆加密。

SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ;
SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');

上述數據安全的管理，也可以基於應用系統的服務(代碼)層進行處理，相對專業的流程是從數據生成源頭處理，規避數據傳遞過程泄露，造成不必要的風險。

六、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base


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

-Advertisement-
Play Games
更多相關文章
  • 獲取數據表列相關數據
    雖然本人在開發資料庫時，不太使用視圖，但是還是知道 如何獲取視圖中使用的所有表列： SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE 如何獲取包含視圖所有表： SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_ ...
  • MS SQL為欄位添加說明
    以ms sql server 14 v17為例。 如下表dbo.Q中有一個欄位'' 首先在資料庫的系統存儲過程列表中： 找到sys.sp_addextendedproperty，使用這個為欄位添加一個說明。 EXECUTE [sys].[sp_addextendedproperty] @name=N ...
  • 剛刪除數據，已提交或預設提交，閃回數據
    --閃回 回退已刪除的數據 select * from NCMS_SPECIALIST_CHRONIC as of timestamp to_timestamp('2019-12-16 9:04:00', 'yyyy-mm-dd hh24:mi:ss'); --開啟這張表的狀態 alter tabl ...
  • springboot 報錯 org.springframework.beans.factory.NoSuchBeanDefinitionException:No qualifying bean of type 'com.example.service.HrService' available: 有沒有大佬出個主意，我找了一天，剛入門springboot
    話不多說先上圖，這是啟動類的配置，這裡配置了@ComponentScan("我的mapper的介面") 接下來是我的項目結構截圖 然後是service 的截圖，我在這裡加了註解@Service 最後我在測試類裡面 的截圖 最後附上我的maven的pom 我開始懷疑是這個配置的有問題了 <?xml v ...
  • Vmvare擴展虛擬機磁碟大小
    Vmvare設置好虛擬機的磁碟大小之後，發現磁碟空間不夠了，這個時候怎麼擴展磁碟的大小呢？ 首先，在確保虛擬機關閉的情況下，右鍵設置，選擇硬碟，擴展，這樣就可以增加磁碟的大小。 但是由於未進行分區和磁碟掛載的設置，我們啟動虛擬機以後並不能使用增加的磁碟空間，這個時候怎麼辦呢？有兩種辦法 先用root ...
  • sql server通過臨時存儲過程實現使用參數添加文件組腳本復用
    create procedure #pr_CreateFileGroup @dbname nvarchar(max), @filegroupname nvarchar(max) as begin /* 腳本來源：https://www.cnblogs.com/zhang502219048/p/121 ...
  • 【資料庫】到底什麼是關係？
    電腦學習者經常會用到諸如Sqlserver Mysql Orcal 等“關係型”資料庫。 問題一：那麼，到底什麼是“關係”呢？ 首先，我們來看以下三組數據 註：A1,A2表示具體的某個人，比如張三，李四 我們用數學集合的形式對其進行表達，得到三個集合 男人: D1 { A1,A2,A3 } 女人: ...
  • Mysql基礎04-查詢
    關聯查詢 1、內連接：實現A∩B select 欄位列表 from A表 inner join B表 on 關聯條件 where 等其他子句; 2、左外連接 #實現查詢結果是A select 欄位列表 from A表 left join B表 on 關聯條件 where 等其他子句; #實現A - ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類