物理設計
物理設計就是根據所選擇的關系型數據庫的特點對邏輯模型進行存儲結構設計。它涉及的內容包含以下4方面：1. 定義數據庫、表及字段的命名規范；2. 選擇合適的存儲引擎；3. 為表中的字段選擇合適的數據類型；4. 建立數據庫結構。
定義數據庫、表及字段的命名規范
數據庫、表及字段的命名要遵守可讀性原則
數據庫、表及字段的命名要遵守表意性原則
- 數據庫、表及字段的命名
存儲引擎
- MyISAM存儲引擎是非事務的，鎖粒度是表級的，主要應用于select，insert，不適合應用于讀寫操作頻繁的場景，因為對于讀寫操作會進行鎖表操作。
- MRG_MYISAM和MyISAM差不多
- Innodb存儲引擎是支持事務，支持MVCC行級鎖，適合任何場景
- Archive存儲引擎不支持事務，支持行級鎖，支持insert、select，適用于隨機讀取、更新、刪除。
- Ndb Cluster是MySQL集群存儲引擎 ，支持事務，支持行級鎖，具有高可用性
數據類型
原則：當一個列可以選擇多種數據類型時，應該優先考慮數值類型，其次是日期或二進制類型，最后是字符類型。對于相同級別的數據
類型，應該優先選擇占用空間小的數據類型。
1. 數值類型
數值類型可以分為整數類型和實數類型。
其中，M表示整數類型的最大顯示寬度； 對于浮點和定點類型，M是可以存儲的總位數（精度）；對于字符串類型，M是最大長度。 M
的最大允許值取決于數據類型。注意：當我們只存儲兩位數時，一種錯誤用法就是int(2)，其實int(i)存儲空間是由數據類型和是否是
unsigned決定的，i只是表示顯示長度。int(11)和int(1)的存儲空間是沒差別的， int(11)的數據長度如果小于11，則默認補充空格，如果
int (11)字段被zerofill修飾，則默認補充0，而且字段被zerofill修飾會自動添加unsigned。因此，當我們只存儲兩位數時，使用tinyint才能
達到我們簡約空間的目的。
注意：Decimal類型是精確類型的，如果我們需要存儲精確數據，例如財務數據就必須使用Decimal類型，而不能使用float和double類型。
2. 字符串類型
字符串類型中有兩種類型：char和varchar。
varchar(n)的存儲規范
varchar存儲變長內容，varchar需要額外的空間記錄內容長度，當內容小于255字節時，需要一個額外字節，當內容大于255字節時，需要2個額外字節；
在jbk編碼下，char占2個字節，在UTF-8編碼下，char占用3個字節；
MySQL每行最多存儲65535個字節；
varchar中的第1個字節表示是否為空，第二個字節和第3個字節表示長度，剩下字節表示實際內容，因此最大可用存儲65535-1-2=65532?；
create table t1(c varchar(n)) charset=gbk,則此處n的最大值為(65535-1-2)/2=32766create t(c int ,c2 char(30),c3 varchar(n)) charset=utf8,則此處n的最大值（65535-1-2-4-90）/3=21812
*?varchar（100）中的100指的是100個字符數量；
- 使用場景：字符串列的最大長度比平均長度大很多；字符串列很少被更新，由于varchar類型長度不固定，那么進行更新時，可
能導致存儲頁的分裂，引起存儲碎片；使用多字節字符集存儲字符串，以UTF-8為例，存儲中文需要3個字節，而存儲英文或數字只需
要1個字節。
char(n)的存儲規范
使用char(1)就比varchar(1)更節省空間，因為varchar還需要存儲額外字節存儲其他信息；char類型適合存儲經常更新的字符序列，
由于char類型的長度是固定的，MySQL會一次性地分配存儲空間，在多次更新時也不會出現頁分裂的情況，減少存儲碎片。
3. 日期類型
注意：timestamp占4byte，實際上是用int存儲的。由于只有4byte，因此它只能顯示1970-01-01 到 2038-01-19，也正是這個原因，如
果在這個時間內的字段推薦使用timestamp。timestamp類型顯示依賴于所指定的時區。除此之外，還需要注意一點，**在行的數據
修改時可以自動修改timestamp列的值**，這個特性可以幫助我們在進行數據分析時自動提取出最新變化的數據。
MySQL5.5 datetime類型字段不能設置默認值為now()
MySQL5.6 datetime類型字段可以設置默認值為now()
建立數據庫結構
1. DML（Data manipulation language，數據操作語言）
- DML分成數據查詢和數據更新兩類，數據更新又分成插入、刪除和修改三種操作，主要命令：select、insert、delete、update、merge、call、explain、plan。
2. DDL（Data definition language，數據定義語言）
- DDL包括兩種類型數據：數據字典以及數據類型和結構定義。主要命令：create 、alter、drop、truncate、rename、comment。
- Create，用于創建語句，用于創建表或者數據庫或者存儲過程或者其他內容
create table tea(id int not null auto_increment,name varchar(6) not null,remark char(6),primary key(id));
show create table tea;
- Alter,一般用于添加或者修改表中的字段名或者字段定義。也可以用于修改字段順序等。同樣的也可以用于修改數據庫的名字或者編碼
修改字段定義:
ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-';
添加字段:
alter table emp add column age int(3) not null default 0;
修改字段名字，同時修改定義:
alter table emp change age age1 int(4) not null default 0;
在表Column的尾部追加新的column:
alter table emp add birth date not null after empno;
移動column到表column的首位:
alter table emp modify age1 int(3) not null default 0 first;
將某個column移動到另一個columon后面:
alter table department change deptno deptno int(11) NOT NULL after deptname;
刪除主鍵:
alter visit_log drop primary key;
修改表名:
alter table emp rename employees;
刪除字段
alter table guess_product_info drop column backstyle;
3. DCL（Data Control Language，數據控制語言）
- 設置或更改數據庫用戶或角色權限，包括對基本表和視圖的授權，完整性規則的描述，事務控制等內容。
- grant
賦予權限
grant all privileges
on *.*
to 'root'@
'localhost' with grant option; 授權遠程用戶注意不要
with grant option
grant
select,update,
create,delete
on *.*
to guest@
'115.220.46.%' identified
by 'test@home';創建業務用戶，并要求只限制IP段
查看權限
Select user,password,host
from mysql.user;
Show grants
for root@
'localhost';
select user,password,host ,grant_priv,super_priv
from mysql.user;
重命名用戶
rename mysql.user root@
' ' to admin@
' ';
刷新權限
flush privileges
回收權限
revoke
select,insesrt,update,delete,
create on *.*
from 'guest'@
'115.220.46.%';
show grants
for guest@
'115.220.46.%';
修改密碼
update mysql.user
set password =password(
'test')
where user=
'guest';password()函數對字符串進行MD5加密
mysqladmin -uroot -ptest password
12345-p 舊密碼 password 新密碼忘記密碼
mysqld_safe --defaults-file=/etc/mysql/my3306.cnf --
skip-grant-tables &
刪除用戶
drop user root@
'';
刪除test數據庫
select *
from mysql.db
where db like
'%test%'\G;
delete
from mysql.db
where db like
'%test%';
刪除不需要的用戶mysql>
select user,password,host
from mysql.user;
+------+-------------------------------------------+------------+
| user | password | host |
+------+-------------------------------------------+------------+
| root | | localhost |
| root | | nbview.com |
| root | |
127.0.
0.1 |
| root | | ::
1 |
| | | localhost |
| | | nbview.com |
| kewy | kewy126@home | % |
| root | *
08F411191A8F7130F09F0A961DB8E87983620D5B | % |
+------+-------------------------------------------+------------+
8 rows
in set (
0.00 sec)
mysql> delete
from mysql.user
where user=
'kewy';
ERROR
2006 (HY000): MySQL server
has gone away
No connection. Trying
to reconnect...
Connection id:
59
Current database: student_db1
Query OK,
1 row affected (
0.01 sec)
mysql> delete
from mysql.user
where user=
'root' and host=
'::1';
Query OK,
1 row affected (
0.00 sec)
mysql> delete
from mysql.user
where user=
'root' and host=
'nbview.com';
Query OK,
1 row affected (
0.00 sec)
mysql> delete
from mysql.user
where host=
'nbview.com';
Query OK,
1 row affected (
0.00 sec)mysql> delete
from mysql.user
where host=
'localhost' and user
is null;
Query OK,
0 rows affected (
0.00 sec)mysql> delete
from mysql.user
where host=
'127.0.0.1' ;
Query OK,
1 row affected (
0.00 sec)
mysql>
select user,password,host
from mysql.user;
+------+-------------------------------------------+-----------+
| user | password | host |
+------+-------------------------------------------+-----------+
| root | | localhost |
| root | *
08F411191A8F7130F09F0A961DB8E87983620D5B | % |
+------+-------------------------------------------+-----------+
2 rows
in set (
0.00 sec)
4. TCL（Transaction Control Language，事務控制語言）
- TCL用于控制事務內執行流程的語言
- start transaction|Begin:開始一個事務
- Savepoint：創建一個記錄點，方便回滾到這個地方
- Rollback:回滾事務
- Commit：提交事務
開始事務
START TRANSACTION
可選:創建一個存檔點
SAVEPOINT sp
開始操作SQL
select
insert
...
操作完成提交數據
COMMIT
或者，操作失敗，回滾數據(回滾到某個存檔點)
ROLLBACK
ROLLBACK TO sp
釋放存檔點
RELEASE SAVEPOINT sp
總結
在進行數據庫物理設計時，我們需要計算每張表的存儲空間， 選擇存儲引擎和表中的數據類型。
歡迎關注微信公眾號：木可大大，所有文章都將同步在公眾號上。
總結
以上是生活随笔為你收集整理的数据库物理设计的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
