本文目錄一覽：
北大青鳥設計培訓：Mysql資料庫的設計和優化？
在JAVA開發中資料庫的學習也是我們需要了解的，截下來幾篇文章都是關於資料庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！ 資料庫的設計 資料庫設計是基礎，資料庫優化是建立在設計基礎之上的。
好的資料庫一定擁有好的設計。
資料庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。
資料庫的三大範式 第一範式1NF:所有的域都應該是原子性的，即資料庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。
第二範式2Nf:第二範式在第一範式的基礎之上更進一層。
第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。
也就是說在一個資料庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張資料庫表中。
第三範式3Nf:所有欄位必須與主鍵直接相關，而不是間接相關。
也可以理解為欄位不要和其他非主鍵欄位相關. 注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使欄位盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反. 資料庫設計的步驟 需求分析階段 準確了解與分析用戶需求(包括數據與處理)。
是整個設計過程的基礎，是最困難、最耗費時間的一步。
概念結構設計階段 是整個資料庫設計的關鍵–設計資料庫的E-R模型圖，確認需求信息的正確和完整 Entity_Relationship—實體之間的關係 一對一 一對多 多對一
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′);
上述數據安全的管理，也可以基於應用系統的服務(代碼)層進行處理，相對專業的流程是從數據生成源頭處理，規避數據傳遞過程泄露，造成不必要的風險。
如何用mysql設計表
選中某個表，然後右鍵點擊，選擇「設計表」即可。
也可以左鍵點擊某個表（即選中某表），在上面輔助菜單欄里有「打開表」、「設計表」、「新建表」等按鈕可點擊，點擊「設計表」按鈕即可。
進入後，會彈出新的操作窗口，新窗口的菜單欄里有常用的修改表結構的按鈕，右鍵點擊某列欄位也可以彈出相應的修改表的操作按鈕。
其它摸索著看提示操作即可，還是很簡單的。
修改好表後點擊菜單欄上的「保存」按鈕即可。
註：若要查看修改表的sql語句，必須在「保存」之前點擊「SQL預覽」按鈕。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/201154.html
微信掃一掃 
支付寶掃一掃 