MySQL基礎篇從五個維度出發審視表結構策劃

2024-07-24 12:35:54

字體：大中小

來源：轉載

供稿：網友

MySQL基礎篇從五個維度出發審視表結構策劃：

　　一、數據場景
　　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

（編輯：武林網）

上一篇：如何運用MySQL 8.0用戶和角色管理

下一篇：mysql用戶管制的用法