資料庫大表設計

㈠ 如何設計一個優秀的資料庫

資料庫設計(Database Design)是指對罩沒禪於一個給定的應用環境，構造最優的資料庫模式，建立資料庫及其應用系統，使之能夠有效地存儲數據，滿足各種用戶的應用需求（信息要求和處理要求）。 在資料庫領域內，常常把使用資料庫的各類系統統稱為資料庫資料庫設計(database design)是指對於一個給定的應用環境，構造最優的資料庫模式，建立資料庫及其應用系統，使之能夠有效地存儲數據，滿足各種用戶的應用需求（信息要求和處理要求）。 在資料庫領域內，常常把使用資料庫的各類系統統稱為資料庫應用系統。 一、資料庫和信息系統 (1)資料庫是信息系統的核心和基礎，把信息系統中大量的數據按一定的模型組織起來，提供存儲、維護、檢索數據的 功能，使信息系統可以方便、及時、准確地從資料庫中獲得所需的信息。 (2)資料庫是信息系統的各個部分能否緊密地結合在一起以及如何結合的關鍵所在。 (3)資料庫設計是信息系統開發和建設的重察棗要組成部分。 (4)資料庫設計人員應該具備的技術和知識： 資料庫的基本知識和資料庫設計技術 計算機科學的基礎知識和程序設計的方法和技巧 軟體工程的原理和方法 應用領域的知識 二、資料庫設計的特點 資料庫建設是硬體、軟體和干件的結合 三分技術，七分管理，十二分基礎數據 技術與管理的界面稱之為「干件」 資料庫設計應該與應用系統設計相結合 結構（數據）設計：設計資料庫框架或資料庫結構 行為（處理）設計：設計應用程序、事務處理等 結構和行為分離的設計 傳統的軟體工程忽視對應用中數據語義的分析和抽象，只要有可能就盡量推遲數據結構設計的決策早期的資料庫設計致力於數據模型和建模方法研究，忽視了對行為的設計 如圖： 三、資料庫設計方法簡述 手工試湊法 設計質量與設計人員的經驗和水平有直接關系 缺乏科學理論和工程方法的支持，工程的質量難以保證 資料庫運行一段時間後常常又不同程度地發現各種問題，增加了維護代價 規范設計法 手工設計方 基本思想 過程迭代和逐步求精 規范設計法(續) 典型方法： (1)新奧爾良（new orleans）方法：將資料庫設計分為四個階段 s.b.yao方法：將資料庫設計分為五個步驟 i.r.palmer方法：把資料庫設計當成一步接一步的過程 (2)計算機輔助設計 oracle designer 2000 sybase powerdesigner 四、資料庫設計的基本步驟 資料庫設計的過程(六個階段) 1.需求分析階段 准確了解與分析用戶需求（包括數據與處理） 是整個設計過程的基礎，是最困難、最耗費時間的一步 2.概念結構設計階段 是整個資料庫設計的關鍵 通過對用戶需求進行綜合、歸納與抽象，形成一個獨立於具體dbms的概念模型 3.邏輯結構設計階段 將概念結構轉換為某個dbms所支持的數據模型 對其進行優化 4.資料庫物理設計階段 為邏輯數據模型選取一個最適合應用環境的物理結構（包括存儲結構和存取方法） 5.資料庫實施階段 運用dbms提供的數據語言、工具及宿主語言，根據邏輯設計和物理設計的結果 建立資料庫，編制與調試應用程序，組織數據入庫，並進行試運行 6.資料庫運行和維護階段 資料庫應用系統經過試運行後即可投入正式運行。 在資料庫系統運行過程中必須不斷地對其物塵進行評價、調整與修改 設計特點: 在設計過程中把資料庫的設計和對資料庫中數據處理的設計緊密結合起來將這兩個方面的需求分析、抽象、設計、實現在各個階段同時進行，相互參照，相互補充，以完善兩方面的設計 設計過程各個階段的設計描述： 如圖： 五、資料庫各級模式的形成過程 1.需求分析階段：綜合各個用戶的應用需求 2.概念設計階段：形成獨立於機器特點，獨立於各個dbms產品的概念模式(e-r圖) 3.邏輯設計階段：首先將e-r圖轉換成具體的資料庫產品支持的數據模型，如關系模型，形成資料庫邏輯模式；然後根據用戶處理的要求、安全性的考慮，在基本表的基礎上再建立必要的視圖(view)，形成數據的外模式 4.物理設計階段：根據dbms特點和處理的需要，進行物理存儲安排，建立索引，形成資料庫內模式 六、資料庫設計技巧 1. 設計資料庫之前（需求分析階段） 1) 理解客戶需求，詢問用戶如何看待未來需求變化。讓客戶解釋其需求，而且隨著開發的繼續，還要經常詢問客戶保證其需求仍然在開發的目的之中。 2) 了解企業業務可以在以後的開發階段節約大量的時間。 3) 重視輸入輸出。 在定義資料庫表和欄位需求（輸入）時，首先應檢查現有的或者已經設計出的報表、查詢和視圖（輸出）以決定為了支持這些輸出哪些是必要的表和欄位。 舉例：假如客戶需要一個報表按照郵政編碼排序、分段和求和，你要保證其中包括了單獨的郵政編碼欄位而不要把郵政編碼糅進地址欄位里。 4) 創建數據字典和er 圖表 er 圖表和數據字典可以讓任何了解資料庫的人都明確如何從資料庫中獲得數據。er圖對表明表之間關系很有用，而數據字典則說明了每個欄位的用途以及任何可能存在的別名。對sql 表達式的文檔化來說這是完全必要的。 5) 定義標準的對象命名規范 資料庫各種對象的命名必須規范。 2. 表和欄位的設計（資料庫邏輯設計） 表設計原則 1) 標准化和規范化 數據的標准化有助於消除資料庫中的數據冗餘。標准化有好幾種形式，但third normal form（3nf）通常被認為在性能、擴展性和數據完整性方面達到了最好平衡。簡單來說，遵守3nf 標準的資料庫的表設計原則是：「one fact in one place」即某個表只包括其本身基本的屬性，當不是它們本身所具有的屬性時需進行分解。表之間的關系通過外鍵相連接。它具有以下特點：有一組表專門存放通過鍵連接起來的關聯數據。 舉例：某個存放客戶及其有關定單的3nf 資料庫就可能有兩個表：customer 和order。order 表不包含定單關聯客戶的任何信息，但表內會存放一個鍵值，該鍵指向customer 表裡包含該客戶信息的那一行。 事實上，為了效率的緣故，對表不進行標准化有時也是必要的。 2) 數據驅動 採用數據驅動而非硬編碼的方式，許多策略變更和維護都會方便得多，大大增強系統的靈活性和擴展性。 舉例，假如用戶界面要訪問外部數據源（文件、xml 文檔、其他資料庫等），不妨把相應的連接和路徑信息存儲在用戶界面支持表裡。還有，如果用戶界面執行工作流之類的任務（發送郵件、列印信箋、修改記錄狀態等），那麼產生工作流的數據也可以存放在資料庫里。角色許可權管理也可以通過數據驅動來完成。事實上，如果過程是數據驅動的，你就可以把相當大的責任推給用戶，由用戶來維護自己的工作流過程。 3) 考慮各種變化 在設計資料庫的時候考慮到哪些數據欄位將來可能會發生變更。 舉例，姓氏就是如此（注意是西方人的姓氏，比如女性結婚後從夫姓等）。所以，在建立系統存儲客戶信息時，在單獨的一個數據表裡存儲姓氏欄位，而且還附加起始日和終止日等欄位，這樣就可以跟蹤這一數據條目的變化。 欄位設計原則 4) 每個表中都應該添加的3 個有用的欄位 drecordcreationdate，在vb 下默認是now()，而在sql server ? 下默認為getdate() srecordcreator，在sql server 下默認為not null default ? user nrecordversion，記錄的版本標記；有助於准確說明記錄中出現null 數據或者丟失數據的原因 ? 5) 對地址和電話採用多個欄位 描述街道地址就短短一行記錄是不夠的。address_line1、address_line2 和address_line3 可以提供更大的靈活性。還有，電話號碼和郵件地址最好擁有自己的數據表，其間具有自身的類型和標記類別。 6) 使用角色實體定義屬於某類別的列 在需要對屬於特定類別或者具有特定角色的事物做定義時，可以用角色實體來創建特定的時間關聯關系，從而可以實現自我文檔化。 舉例：用person 實體和person_type 實體來描述人員。比方說，當john smith, engineer 提升為john smith, director 乃至最後爬到john smith, cio 的高位，而所有你要做的不過是改變兩個表person 和person_type 之間關系的鍵值，同時增加一個日期/時間欄位來知道變化是何時發生的。這樣，你的person_type 表就包含了所有person 的可能類型，比如associate、engineer、director、cio 或者ceo 等。還有個替代辦法就是改變person 記錄來反映新頭銜的變化，不過這樣一來在時間上無法跟蹤個人所處位置的具體時間。 7) 選擇數字類型和文本類型盡量充足 在sql 中使用smallint 和tinyint 類型要特別小心。比如，假如想看看月銷售總額，總額欄位類型是smallint，那麼，如果總額超過了$32,767 就不能進行計算操作了。 而id 類型的文本欄位，比如客戶id 或定單號等等都應該設置得比一般想像更大。假設客戶id 為10 位數長。那你應該把資料庫表欄位的長度設為12 或者13 個字元長。但這額外占據的空間卻無需將來重構整個資料庫就可以實現資料庫規模的增長了。 8) 增加刪除標記欄位 在表中包含一個「刪除標記」欄位，這樣就可以把行標記為刪除。在關系資料庫里不要單獨刪除某一行；最好採用清除數據程序而且要仔細維護索引整體性。 3. 選擇鍵和索引（資料庫邏輯設計） 鍵選擇原則： 1) 鍵設計4 原則 為關聯欄位創建外鍵。 ? 所有的鍵都必須唯一。 ? 避免使用復合鍵。 ? 外鍵總是關聯唯一的鍵欄位。 ? 2) 使用系統生成的主鍵 設計資料庫的時候採用系統生成的鍵作為主鍵，那麼實際控制了資料庫的索引完整性。這樣，資料庫和非人工機制就有效地控制了對存儲數據中每一行的訪問。採用系統生成鍵作為主鍵還有一個優點：當擁有一致的鍵結構時，找到邏輯缺陷很容易。 3) 不要用用戶的鍵(不讓主鍵具有可更新性) 在確定採用什麼欄位作為表的鍵的時候，可一定要小心用戶將要編輯的欄位。通常的情況下不要選擇用戶可編輯的欄位作為鍵。 4) 可選鍵有時可做主鍵 把可選鍵進一步用做主鍵，可以擁有建立強大索引的能力。 索引使用原則： 索引是從資料庫中獲取數據的最高效方式之一。95%的資料庫性能問題都可以採用索引技術得到解決。 1) 邏輯主鍵使用唯一的成組索引，對系統鍵（作為存儲過程）採用唯一的非成組索引，對任何外鍵列採用非成組索引。考慮資料庫的空間有多大，表如何進行訪問，還有這些訪問是否主要用作讀寫。 2) 大多數資料庫都索引自動創建的主鍵欄位，但是可別忘了索引外鍵，它們也是經常使用的鍵，比如運行查詢顯示主表和所有關聯表的某條記錄就用得上。 3) 不要索引memo/note 欄位，不要索引大型欄位（有很多字元），這樣作會讓索引佔用太多的存儲空間。 4) 不要索引常用的小型表 不要為小型數據表設置任何鍵，假如它們經常有插入和刪除操作就更別這樣作了。對這些插入和刪除操作的索引維護可能比掃描表空間消耗更多的時間。 4. 數據完整性設計（資料庫邏輯設計） 1) 完整性實現機制： 實體完整性：主鍵 參照完整性： 父表中刪除數據：級聯刪除；受限刪除；置空值 父表中插入數據：受限插入；遞歸插入 父表中更新數據：級聯更新；受限更新；置空值 dbms對參照完整性可以有兩種方法實現：外鍵實現機制（約束規則）和觸發器實現機制 用戶定義完整性： not null；check；觸發器 2) 用約束而非商務規則強制數據完整性 採用資料庫系統實現數據的完整性。這不但包括通過標准化實現的完整性而且還包括數據的功能性。在寫數據的時候還可以增加觸發器來保證數據的正確性。不要依賴於商務層保證數據完整性；它不能保證表之間（外鍵）的完整性所以不能強加於其他完整性規則之上。 3) 強制指示完整性 在有害數據進入資料庫之前將其剔除。激活資料庫系統的指示完整性特性。這樣可以保持數據的清潔而能迫使開發人員投入更多的時間處理錯誤條件。 4) 使用查找控制數據完整性 控制數據完整性的最佳方式就是限制用戶的選擇。只要有可能都應該提供給用戶一個清晰的價值列表供其選擇。這樣將減少鍵入代碼的錯誤和誤解同時提供數據的一致性。某些公共數據特別適合查找：國家代碼、狀態代碼等。 5) 採用視圖 為了在資料庫和應用程序代碼之間提供另一層抽象，可以為應用程序建立專門的視圖而不必非要應用程序直接訪問數據表。這樣做還等於在處理資料庫變更時給你提供了更多的自由。 5. 其他設計技巧 1) 避免使用觸發器 觸發器的功能通常可以用其他方式實現。在調試程序時觸發器可能成為干擾。假如你確實需要採用觸發器，你最好集中對它文檔化。 2) 使用常用英語（或者其他任何語言）而不要使用編碼 在創建下拉菜單、列表、報表時最好按照英語名排序。假如需要編碼，可以在編碼旁附上用戶知道的英語。 3) 保存常用信息 讓一個表專門存放一般資料庫信息非常有用。在這個表裡存放資料庫當前版本、最近檢查/修復（對access）、關聯設計文檔的名稱、客戶等信息。這樣可以實現一種簡單機制跟蹤資料庫，當客戶抱怨他們的資料庫沒有達到希望的要求而與你聯系時，這樣做對非客戶機/伺服器環境特別有用。 4) 包含版本機制 在資料庫中引入版本控制機制來確定使用中的資料庫的版本。時間一長，用戶的需求總是會改變的。最終可能會要求修改資料庫結構。把版本信息直接存放到資料庫中更為方便。 5) 編制文檔 對所有的快捷方式、命名規范、限制和函數都要編制文檔。 採用給表、列、觸發器等加註釋的資料庫工具。對開發、支持和跟蹤修改非常有用。 對資料庫文檔化，或者在資料庫自身的內部或者單獨建立文檔。這樣，當過了一年多時間後再回過頭來做第2 個版本，犯錯的機會將大大減少。 6) 測試、測試、反復測試 建立或者修訂資料庫之後，必須用用戶新輸入的數據測試數據欄位。最重要的是，讓用戶進行測試並且同用戶一道保證選擇的數據類型滿足商業要求。測試需要在把新資料庫投入實際服務之前完成。 7) 檢查設計 在開發期間檢查資料庫設計的常用技術是通過其所支持的應用程序原型檢查資料庫。換句話說，針對每一種最終表達數據的原型應用，保證你檢查了數據模型並且查看如何取出數據。

㈡ 資料庫表結構設計，常見的資料庫管理系統

一、數據場景 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')='' ; SELECT SHA('cicada')=''; SELECT PASSWORD('smile')='*' ; 可逆加密
安全性要求高的系統，需要做三級等保，對數據的安全性極高，數據在存儲時必須加密入庫，取出時候需要解密，這些就需要可逆加密。
SELECT DECODE(ENCODE('123456','key_salt'),'key_salt') ; SELECT AES_DECRYPT(AES_ENCRYPT('cicada','salt123'),'salt123');
上述數據安全的管理，也可以基於應用系統的服務(代碼)層進行處理，相對專業的流程是從數據生成源頭處理，規避數據傳遞過程泄露，造成不必要的風險。

㈢ 資料庫表結構設計

1.典型地質遺跡基本概況表

包括地理位置、園區范圍、自然條件等組成(表6-2)。

表6-2 基本概況(JBGK.MDB)資料庫結構表

數據項說明:

地質公園編號:前3位國別代碼，按行政區劃代碼(GB/T2260－98)填寫，後3位地質遺跡點的順序號。當公園范圍較大，跨國界時，其編號以主景區所在的區域為准進行編號，後6位為順序號，保證地質遺跡編號的唯一性。

地質遺跡分類:為適應現階段地質遺跡的調查，地質公園建設和發展規劃、科學普及的開展，以系統地球科學理論為指導，趙汀(2009)提出了顯性地質遺跡的概念並建立了學科分類系統，並在此基礎上完成了中國國家地質公園地質遺跡分類。

2.景區、景點數據(表6-3，表6-4)

表6-3 景區表結構(JQ.MDB)表

表6-4 景點表結構(JD.MDB)表

景區和景點數據關系(表6-5)為一對多關系。即，一個景區至少有一處以上的景點，任意一個景點只能屬於某一個景區。

㈣ 大型資料庫的設計原則與開發技巧

隨著計算機技術越來越廣泛地應用於國民經濟的各個領域 在計算機硬體不斷微型化的同時 應用系統向著復雜化 大型化的方向發展 資料庫是整個系統的核心 它的設計直接關系系統執行的效率和系統的穩定性 因此在軟體系統開發中 資料庫設計應遵循必要的資料庫範式理論 以減少冗餘 保證數據的完整性與正確性 只有在合適的資料庫產品上設計出合理的資料庫模型 才能降低整個系統的編程和維護難度 提高系統的實際運行效率 雖然對於小項目或中等規模的項目開發人員可以很容易地利用範式理論設計出一套符合要求的資料庫 但對於一個包含大型資料庫的軟體項目 就必須有一套完整的設計原則與技巧

一 成立數據小組

大型資料庫數據元素多 在設計上有必要成立專門的數據小組 由於資料庫設計者不一定是使用者 對系統設計中的數據元素不可能考慮周全 資料庫設計出來後 往往難以找到所需的庫表 因此數據小組最好由熟悉業務的項目骨幹組成

數據小組的職能並非是設計資料庫 而是通過需求分析 在參考其他相似系顫腔統的基礎上 提取系統的基本數據元素 擔負對資料庫的審核 審核內容包括審核新的資料庫元素是否完全 能否實現全部業務需求 對舊資料庫（如果存在舊系統）的分析及數據轉換 資料庫設計的審核 控制及必要調整

二 設計原沖遲則

規范命名 所有的庫名 表名 域名必須遵循統一的命名規則 並進行必要說明 以方便設計 維護 查詢

控制欄位的引用 在設計時 可以選擇適當的資料庫設計管理工具 以方便開發人員的分布式設計和數據小組的集中審核管理 採用統一的命名規則 如果設計的欄位已經存在 可直接引用 否則 應重新設計

庫表重復控制 在設計過程中 如果發現大部分欄位都已存在 開發人員應懷疑所設計的庫表是否已存在 通過對欄位所在庫表及相應設計人員的查詢 可以確認庫表是否確實重復

並發控制 設計中應進行並發控制 即對於同一個庫表 在茄判衫同一時間只有一個人有控制權 其他人只能進行查詢

必要的討論 資料庫設計完成後 數據小組應與相關人員進行討論 通過討論來熟悉資料庫 從而對設計中存在的問題進行控制或從中獲取資料庫設計的必要信息

數據小組的審核 庫表的定版 修改最終都要通過數據小組的審核 以保證符合必要的要求

頭文件處理 每次數據修改後 數據小組要對相應的頭文件進行修改（可由管理軟體自動完成） 並通知相關的開發人員 以便進行相應的程序修改

三 設計技巧

分類拆分數據量大的表 對於經常使用的表（如某些參數表或代碼對照表） 由於其使用頻率很高 要盡量減少表中的記錄數量 例如 銀行的戶主賬表原來設計成一張表 雖然可以方便程序的設計與維護 但經過分析發現 由於數據量太大 會影響數據的迅速定位 如果將戶主賬表分別設計為活期戶主賬 定期戶主賬及對公戶主賬等 則可以大大提高查詢效率

索引設計 對於大的資料庫表 合理的索引能夠提高整個資料庫的操作效率 在索引設計中 索引欄位應挑選重復值較少的欄位 在對建有復合索引的欄位進行檢索時 應注意按照復合索引欄位建立的順序進行 例如 如果對一個 萬多條記錄的流水表以日期和流水號為序建立復合索引 由於在該表中日期的重復值接近整個表的記錄數 用流水號進行查詢所用的時間接近 秒 而如果以流水號為索引欄位建立索引進行相同的查詢 所用時間不到 秒 因此在大型資料庫設計中 只有進行合理的索引欄位選擇 才能有效提高整個資料庫的操作效率

數據操作的優化 在大型資料庫中 如何提高數據操作效率值得關注 例如 每在資料庫流水表中增加一筆業務 就必須從流水控製表中取出流水號 並將其流水號的數值加一 正常情況下 單筆操作的反應速度尚屬正常 但當用它進行批量業務處理時 速度會明顯減慢 經過分析發現 每次對流水控製表中的流水號數值加一時都要鎖定該表 而該表卻是整個系統操作的核心 有可能在操作時被其他進程鎖定 因而使整個事務操作速度變慢 對這一問題的解決的辦法是 根據批量業務的總筆數批量申請流水號 並對流水控製表進行一次更新 即可提高批量業務處理的速度 另一個例子是對插表的優化 對於大批量的業務處理 如果在插入資料庫表時用普通的Insert語句 速度會很慢 其原因在於 每次插表都要進行一次I/O操作 花費較長的時間 改進後 可以用Put語句等緩沖區形式等滿頁後再進行I/O操作 從而提高效率 對大的資料庫表進行刪除時 一般會直接用Delete語句 這個語句雖然可以進行小表操作 但對大表卻會因帶來大事務而導致刪除速度很慢甚至失敗 解決的方法是去掉事務 但更有效的辦法是先進行Drop操作再進行重建

資料庫參數的調整 資料庫參數的調整是一個經驗不斷積累的過程 應由有經驗的系統管理員完成 以Informix資料庫為例 記錄鎖的數目太少會造成鎖表的失敗 邏輯日誌的文件數目太少會造成插入大表失敗等 這些問題都應根據實際情況進行必要的調整

必要的工具 在整個資料庫的開發與設計過程中 可以先開發一些小的應用工具 如自動生成庫表的頭文件 插入數據的初始化 數據插入的函數封裝 錯誤跟蹤或自動顯示等 以此提高資料庫的設計與開發效率

避免長事務 對單個大表的刪除或插入操作會帶來大事務 解決的辦法是對參數進行調整 也可以在插入時對文件進行分割 對於一個由一系列小事務順序操作共同構成的長事務（如銀行交易系統的日終交易） 可以由一系列操作完成整個事務 但其缺點是有可能因整個事務太大而使不能完成 或者 由於偶然的意外而使事務重做所需的時間太長 較好的解決方法是 把整個事務分解成幾個較小的事務 再由應用程序控制整個系統的流程 這樣 如果其中某個事務不成功 則只需重做該事務 因而既可節約時間 又可避免長事務

適當超前 計算機技術發展日新月異 資料庫的設計必須具有一定前瞻性 不但要滿足當前的應用要求 還要考慮未來的業務發展 同時必須有利於擴展或增加應用系統的處理功能

lishixin/Article/program/SQL/201311/16498