四、開發(fā)進階

1. 范式和反范式

范式是數(shù)據(jù)庫規(guī)范化的一個手段，是數(shù)據(jù)庫設(shè)計中的一系列原理和技術(shù)，用于減少數(shù)據(jù)庫中的數(shù)據(jù)冗余，并增進數(shù)據(jù)的一致性。

范式

• 1.1 第一范式
  第一范式是指數(shù)據(jù)庫表的每一列（屬性）都是不可分割的基本數(shù)據(jù)項，這就要求數(shù)據(jù)庫的每一列都只能存放單一值，即實體中的某個屬性不能有多個值或不能有重復(fù)的屬性。

• 1.2 第二范式
  一個數(shù)據(jù)表符合第二范式的前提是該數(shù)據(jù)表符合第一范式。它的規(guī)則是要求數(shù)據(jù)表里的所有數(shù)據(jù)都要和該數(shù)據(jù)表的主鍵有完全相依的關(guān)系；如果有哪些數(shù)據(jù)只和主鍵的一部分有關(guān)的話，就得把它們獨立出來變成另一個數(shù)據(jù)表。如果一個數(shù)據(jù)表的主鍵只有單一一個字段的話，那么它就一定符合第二范式。

• 1.3 第三范式
  第三范式的所有非鍵屬性都只和候選鍵有相關(guān)性，也就是說所有非鍵屬性互相之間應(yīng)該是無關(guān)的。候選鍵指的是能夠唯一標識一筆記錄的屬性的最小集合，一般我們所說的候選鍵指的就是主鍵。

范式的好處是：使編程相對簡單，數(shù)據(jù)量更小，更適合放入內(nèi)存，更新更快，只需更新更少的數(shù)據(jù)。更少的冗余數(shù)據(jù)意味著更少地需要GROUP、DISTINCT之類的操作。

范式的壞處是：查詢會變得更加復(fù)雜，查詢時需要更多連接（JOIN）操作，一些可以復(fù)合索引的列由于范式化的需要被分布到了不同的表中，導致索引策略不佳。

反范式

反范式是試圖通過增加冗余數(shù)據(jù)或通過分組數(shù)據(jù)來優(yōu)化數(shù)據(jù)庫讀取性能的過程。

反范式的好處：減少了連接，因此可以更好地利用索引進行篩選和排序，對于一些查詢操作可以提高性能。

反范式的壞處：冗余數(shù)據(jù)意味著更多的寫入，如果冗余的數(shù)據(jù)量很大，還可能會碰到I/O瓶頸，這會導致性能變得更差，所以需要事先衡量對各個表的更新量和查詢量，評估對其他查詢的影響，避免引發(fā)性能問題。冗余數(shù)據(jù)也意味著可能要犧牲部分數(shù)據(jù)的一致性，我們有必要區(qū)分不同數(shù)據(jù)的一致性的優(yōu)先級，對于重要的、用戶比較敏感的數(shù)據(jù)一定要注意一致性的問題，以免影響用戶的體驗。

開發(fā)人員首先

創(chuàng)建一個完全規(guī)范化的設(shè)計，然后為了性能原因選擇性地對一些表進行反范式化設(shè)計。我們要牢記一個準則，設(shè)計的數(shù)據(jù)庫應(yīng)該按照用戶可能的訪問路徑、訪問習慣進行設(shè)計，而不是嚴格地按照數(shù)據(jù)范式來設(shè)計。

2. 權(quán)限機制和安全

權(quán)限可以分為兩類：

• 系統(tǒng)權(quán)限：系統(tǒng)權(quán)限允許執(zhí)行一些特定的功能，如關(guān)閉數(shù)據(jù)庫、終止進程、顯示數(shù)據(jù)庫列表、查看當前執(zhí)行的查詢等。
• 對象選項：對象權(quán)限是指對一些特殊的對象（表、列、視圖、數(shù)據(jù)庫）的訪問權(quán)限，例如是否允許訪問某張表，是否允許在某個庫中創(chuàng)建表。

一般不允許直接更改MySQL的權(quán)限表，而是通過GRANT和REVOKE語句進行權(quán)限的賦予和收回，這也是更安全可靠的辦法。授予的權(quán)限可以分為多個級別：服務(wù)器級別（全局）、數(shù)據(jù)庫級別、表級別、列級別、子程序級別。撤銷權(quán)限即回收已經(jīng)存在的權(quán)限。

GRANT和REVOKE的基本語法

GRANT [privileges] ON [objects] TO [user]
GRANT [privileges] ON [objects] TO [user] IDENTIFIED BY [password]
REVOKE [privileges] ON [objects] FROM [user]

MySQL為有SUPER權(quán)限的用戶專門保留了一個額外的連接，因此即使是所有的普通連接都被占用，MySQL root用戶仍可以登錄并檢查服務(wù)器的活動。

如果想要限制單個賬戶允許的連接數(shù)量，可以通過設(shè)置max_user_connections變量來完成。

MySQL允許對不存在的數(shù)據(jù)庫目標授予權(quán)限。這個特性是特意設(shè)計的，目的是允許數(shù)據(jù)庫管理員為將在此后被創(chuàng)建的數(shù)據(jù)庫目標預(yù)留用戶賬戶和權(quán)限。

2.1 權(quán)限更改何時生效

當mysqld啟動時，所有授權(quán)表的內(nèi)容將被讀進內(nèi)存并且從此時開始生效。當服務(wù)器注意到授權(quán)表被改變了時，現(xiàn)存的客戶端連接將會受到如下影響。

• 表和列的權(quán)限在客戶端的下一次請求時生效。
• 數(shù)據(jù)庫的權(quán)限改變在下一個USE db_name命令生效。
• 全局權(quán)限的改變和密碼改變在下一次客戶端連接時生效。

如果使用GRANT、REVOKE或SET PASSWORD命令對授權(quán)表進行修改，那么服務(wù)器會注意到更改并立即將授權(quán)表重新載入內(nèi)存。

如果手動地修改授權(quán)表（使用INSERT、UPDATE或DELETE等），則應(yīng)該執(zhí)行mysqladmin flush-privileges或mysqladmin reload告訴服務(wù)器再重新裝載授權(quán)表，否則手動的更改將不會生效，除非重啟服務(wù)器。

2.2 常用的權(quán)限

• SELECT、INSERT、UPDATE和DELETE權(quán)限允許用戶在一個數(shù)據(jù)庫現(xiàn)有的表上實施讀取、插入、更新和刪除記錄的操作。這也是一般程序賬號所需要的權(quán)限。
• SHOW VIEW權(quán)限允許用戶查看已經(jīng)創(chuàng)建了的視圖。
• ALTER權(quán)限允許用戶使用ALTER TABLE命令來修改現(xiàn)有數(shù)據(jù)表的結(jié)構(gòu)。
• CREATE和DROP權(quán)限允許用戶創(chuàng)建新的數(shù)據(jù)庫和表，或者刪除現(xiàn)存的數(shù)據(jù)庫和表。生產(chǎn)環(huán)境中一般不賦予程序賬號DROP的權(quán)限。
• GRANT權(quán)限允許用戶把自己擁有的權(quán)限授予其他的用戶。
• FILE權(quán)限允許被授予該權(quán)限的用戶都能讀或?qū)慚ySQL服務(wù)器能讀寫的任何文件。
• SHUTDOWN權(quán)限允許用戶使用SHUTDOWN命令關(guān)掉服務(wù)器?？梢詣?chuàng)建一個用戶專門用來關(guān)閉服務(wù)器。
• PROCESS權(quán)限允許用戶使用PROCESSLIST命令顯示在服務(wù)器內(nèi)執(zhí)行的進程的信息；使用KILL命令終止服務(wù)器進程。用戶總是能顯示或終止自己的進程，但是，顯示或終止其他用戶啟動的進程則需要PROCESS權(quán)限。一些監(jiān)控工具需要PROCESS權(quán)限查看正在執(zhí)行的命令。

2.3 強化安全

強化安全的目的有如下三點：

• 保護好MySQL主機的安全，同時也需要關(guān)注其他能訪問數(shù)據(jù)庫的主機的安全。

• 確保MySQL自身的安全，包括生產(chǎn)庫和備份，應(yīng)使用強密碼，盡可能分配最小的權(quán)限給用戶。

• 確保網(wǎng)絡(luò)、物理的安全，同時也需要關(guān)注信息內(nèi)容的保密。

安全的指導原則和注意事項：

• 加強安全意識。比如加密辦公電腦、個人筆記本上的重要數(shù)據(jù)，不要將未加密的數(shù)據(jù)上傳到各種公共云存儲中。在不安全的網(wǎng)絡(luò)環(huán)境下，比如一些公共Wi-Fi中，涉及賬號的操作可能會泄露你的信息。
• 一般將所有數(shù)據(jù)庫都部署于內(nèi)網(wǎng)（僅監(jiān)聽內(nèi)網(wǎng)IP），需要慎重對待跨IDC的數(shù)據(jù)庫同步，MySQL自身并沒有很好的方式加密數(shù)據(jù)傳輸。
• 開放外網(wǎng)訪問的MySQL服務(wù)器，需要有相應(yīng)的訪問控制策略，例如通過部署防火墻來限制來源IP。
• 如果條件允許，應(yīng)該增加網(wǎng)絡(luò)安全團隊進行安全檢查和審計。
• 在不安全的網(wǎng)絡(luò)環(huán)境中訪問公司或遠程維護機器，建議使用VPN。
• 不要讓任何人（除了MySQL root賬戶）訪問MySQL數(shù)據(jù)庫中的mysql系統(tǒng)庫！
• 用GRANT和REVOKE語句來控制對MySQL的訪問。不要授予超過需求的權(quán)限。絕對不能為所有主機授權(quán)。
• 不要給程序賬號授予SUPER權(quán)限。
• 生產(chǎn)庫上不要留研發(fā)人員的賬號。
• 隔離生產(chǎn)環(huán)境、開發(fā)環(huán)境和測試環(huán)境，不允許研發(fā)、測試人員有權(quán)限更改生產(chǎn)環(huán)境或知道生產(chǎn)環(huán)境的賬號密碼。
• 初始安裝后應(yīng)該移除匿名和空密碼賬號，可以嘗試用“mysql-u root”，如果你能夠成功連接服務(wù)器而沒有要求/輸入任何密碼，則說明有問題。
• 不要將純文本密碼保存到數(shù)據(jù)庫中，不要從字典中選擇密碼，如果你的程序是一個客戶端，必須用可讀的方式存儲密碼，那么建議使用可解碼的加密辦法來存儲。一些工具，如telnet、ftp，使用的是明文傳輸密碼，建議不要使用，使用ssh、sftp是更安全的方式。
• 使用更安全的算法加密密碼，一些流行算法，如MD5已經(jīng)被證明是弱加密，不適合用于加密密碼。曾經(jīng)比較流行的散列算法SHA-1也被證明不夠安全。推薦的方式是在將密碼傳入散列函數(shù)進行加密之前，將其和一個無意義的字符串拼接在一起，這樣即使用戶選擇了一個在字典中存在的單詞作為密碼，攻擊者也很難使用字典攻擊的手段破解密碼。
• 試試從Internet上使用工具掃描端口，或者使用shell命令shell>telnetserver_host 3306，如果得到連接并得到一些垃圾字符，則端口是打開著的，這種情況應(yīng)從防火墻或路由器上關(guān)閉端口，除非你有足夠合理的理由讓它開著。
• 避免SQL注入，不要信任應(yīng)用程序的用戶輸入的任何數(shù)據(jù)。
• 有時候人們會認為如果數(shù)據(jù)庫只包含供公共使用的數(shù)據(jù)，則不需要保護。這是不正確的。即使允許顯示數(shù)據(jù)庫中的任何記錄，也仍然應(yīng)該保護和防范、拒絕服務(wù)攻擊。
• 不要向非管理用戶授予FILE權(quán)限。擁有FILE權(quán)限的任何用戶都能在擁有mysqld守護進程權(quán)限的文件系統(tǒng)里寫入一個文件！
• FILE權(quán)限也可以被用來讀取任何作為運行服務(wù)器的Unix用戶可讀取或訪問的文件。使用該權(quán)限，可以將任何文件讀入數(shù)據(jù)庫表。這可能會被濫用，例如，通過使用LOADDATA裝載“/etc/passwd”進入一個數(shù)據(jù)庫表，然后就能用SELECT顯示它。

2.4 SQL注入

SQL注入的原理有如下4點：

• 拼接惡意查詢。SQL命令可查詢、插入、更新、刪除數(shù)據(jù)，以分號字符分隔不同的命令。

  select * from users where user_id = $user_id
  # user_id是傳入的參數(shù)，如果傳入了“1234;deletefromusers”
  select * from users where user_id = 1234; delete from users
  

• 利用注釋執(zhí)行非法命令。SQL命令中，可以插入注釋。

  select count(*) as 'num' from game_score where game_id=24411 and platform_id=11 and version=$version and session_id = sessid='d7a157-0f-48b6-98-c35592'
  # 如果version包含了惡意的字符串“'-1'OR3 ANDSLEEP(500)--”
  select count(*) as 'num' from game_score where game_id=24411 and platform_id=11 and version='-1' OR 3 AND SLEEP(500)-- 'and session_id = sessid='d7a157-0f-48b6-98-c35592'
  

• SQL命令對于傳入的字符串參數(shù)是用單引號引起來的。如果字符串本身包含單引號而沒有被處理，則可能會篡改原本的SQL語法的作用。

  select * from user_name where user_name = $user_name
  # 如果user_name傳入的是G'chen
  select * from user_name where user_name ='G'chen'
  

• 添加一些額外的條件為真值表達式，改變執(zhí)行行為。

  update users set userpass=SHA2('$userpass') where user_id=$user_id;
  # 如果user_id被傳入惡意的字符串“1234 ORTRUE”
  update users set userpass=SHA2('123456') where user_id=1234 OR TRUE;
  

下面是避免SQL注入的一些方法。

• 過濾輸入內(nèi)容，校驗字符串

  應(yīng)該在將數(shù)據(jù)提交到數(shù)據(jù)庫之前，就把用戶輸入中的不合法字符剔除掉。

• 參數(shù)化查詢

  參數(shù)化查詢目前已被視作是最有效的預(yù)防SQL注入攻擊的方法。不同于在SQL語句中插入動態(tài)內(nèi)容，查詢參數(shù)的做法是在準備查詢語句的時候，就在對應(yīng)參數(shù)的地方使用參數(shù)占位符。然后，在執(zhí)行這個預(yù)先準備好的查詢時提供一個參數(shù)。

  但是綁定參數(shù)也有如下一些限制。

  • 不能讓占位符“?”代替一組值

    SELECT * FROM departments WHERE userid IN ( ? )
    

  • 不能讓占位符“?”代替數(shù)據(jù)表名或列名

  • 不能讓占位符“?”代替SQL關(guān)鍵字

• 安全測試、安全審計

3. 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩究梢杂脕矶ㄎ粓?zhí)行時間很長的查詢。

3.1 查看慢查詢?nèi)罩?/h4>

優(yōu)化策略

性能優(yōu)化的一個很重要的步驟是識別導致問題的BADSQL。對于一般的數(shù)據(jù)庫調(diào)優(yōu)，調(diào)優(yōu)人員往往會采用調(diào)優(yōu)TOP 10的策略，如果我們把最“昂貴”的10個查詢優(yōu)化完（更高效地運行它們，例如添加一個索引），那么就會立即看到對整體MySQL的性能的提升。然后就可以重復(fù)這一過程，并優(yōu)化新的前10名的查詢。

慢查詢?nèi)罩镜母袷?/strong>

不同數(shù)據(jù)庫TOP 10基于的標準可能不太一樣，商業(yè)數(shù)據(jù)庫提供了更完善的成本分析方法，MySQL的慢查詢?nèi)罩颈容^粗略，主要是基于以下3項基本的信息：

• Query_time：查詢耗時。
• Rows_examined：檢查了多少條記錄。
• Rows_sent：返回了多少行記錄（結(jié)果集）。

其他信息：

• Time：執(zhí)行SQL的開始時間。
• Lock_time：等待tablelock的時間，注意InnoDB的行鎖等待是不會反應(yīng)在這里的。
• User@Host：執(zhí)行查詢的用戶和客戶端IP。

可以使用mysqldumpslow命令獲得慢查詢?nèi)罩菊獊硖幚砺樵內(nèi)罩?#xff0c;或者使用更好的第三方工具pt-query-digest。

如何識別需要關(guān)注的SQL

第一步，確認已經(jīng)開啟了慢查詢?nèi)罩?#xff0c;并記錄了合理的閾值。

命令將查看慢查詢是否啟用

mysql> show variables like'%query_log%';
--------------
show variables like'%query_log%'
--------------
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file |/path/to/log3304/slowquery.log |
+---------------------+-----------------------------------------+
2rows in set (0.00sec)

如果配置文件或啟動參數(shù)沒有給出file_name值，慢查詢?nèi)罩緦⒛J命名為“主機名-slow.log”，如果給出了文件名，但不是絕對路徑名，文件則寫入數(shù)據(jù)目錄。

使用命令“SHOW VARIABLES LIKE’%query_time%'”查看全局變量long_query_time。所有執(zhí)行時間超過long_query_time秒的SQL語句都會被記錄到慢查詢?nèi)罩纠铩?/p>

3.2 使用工具分析慢查詢?nèi)罩?/h4>

如果慢查詢?nèi)罩驹鲩L得非?？?#xff0c;很難篩選和查找里面的信息，那么在這種情況下，有如下兩種選擇：

• 調(diào)整閾值，先設(shè)置為較大的閾值，這樣慢查詢記錄就很少了，等優(yōu)化得差不多了，再減少閾值，不斷進行優(yōu)化。
• 使用命令/腳本、工具進行分析，如mysqldumpslow、pt-query-digest等。

使用操作系統(tǒng)命令分析

可以使用操作系統(tǒng)自帶的命令進行一些簡單的統(tǒng)計，如grep、awk、wc，但不容易實現(xiàn)更高級的篩選排序。

下面來看個示例，通過如下命令可以看到每秒的慢查詢的統(tǒng)計，當檢查到有突變時，往往會有異常發(fā)生，這時便可以更進一步到具體的慢查詢?nèi)罩纠锶ゲ檎铱赡艿脑颉?/p>

awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slowquery.log > /tmp/aaa.log

mysqldumpslow

mysqldumpslow命令是官方自帶的，此命令可獲得日志中的查詢摘要。

# 訪問時間最長的10個sql語句的命令
mysqldumpslow -t10 /path/to/log3304/slowquery.log
# 訪問次數(shù)最多的10個sql語句的命令
mysqldumpslow -s c -t10 /path/to/log3304/slowquery.log
# 訪問記錄集最多的10個sql語句的命令
mysqldumpslow -s r -t10 /path/to/log3304/slowquery.log

pt-query-digest

pt-query-digest可以生成一份比官方mysqldumpslow可讀性好得多的報告。安裝也很簡單：

wget www.percona.com/get/pt-query-digest
chmod u+x pt-query-digest
# 基本語法格式如下所示
pt-query-digest [OPTIONS] [FILES] [DSN]# 直接分析慢查詢的命令
pt-query-digest /path/of/slow.log > slow.rtf
# 分析半個小時內(nèi)的慢查詢的命令
pt-query-digest --since 1800s /path/of/slow.log > slow.rtf
# 分析一段時間范圍內(nèi)的慢查詢的命令
pt-query-digest --since '2014-04-14 22:00:00' --until '2014-04-14 23:00:00' /path/of/slow.log > slow.rtf
# 顯示所有分析的查詢命令
pt-query-digest --limit 100% /path/of/slow.log > slow.rtf # 其中，“--limit”參數(shù)默認是“95%:20”，表示顯示95%的最差的查詢，或者20個最差的查詢。

也可以用這個工具來分析二進志日志，以查看我們?nèi)粘５男薷恼Z句是如何分布的，首先需要把二進志日志轉(zhuǎn)換為文本格式。

mysqlbinlog mysql-bin.012639 > /tmp/012639.log
pt-query-digest --type binlog /tmp/012639.log

如何查看pt-query-digest報告

以下是一個輸出報告，為了節(jié)省篇幅，刪除了部分信息。

# 140.9s user time, 1.4s system time, 57.93M rss, 154.03M vsz
# Current date: Sun Feb 16 09:16:39 2011

上面是執(zhí)行pt-query-digest工具的時間

# Hostname: db1000
# Files: /usr/lcoal/mysql/data/slowquery.log
# Overall: 304.88k total, 159 unique, 0.22 QPS, 0.15x concurrency

上面是慢查詢次數(shù)一共是304.88k，唯一的查詢159個。

# Time range: 2010-12-01 00:00:01 to 2010-12-17 09:05:17

這里記錄的是發(fā)現(xiàn)第一條慢查詢的時間到最后一條慢查詢的時間。

# Attribute total min max avg 95% stddev median
# ============ ======= ======== ======= ======= ====== ======= =======
# Exec time 216112s 500ms 21s 709ms 1s 968ms 552ms
# Lock time 414s 21us 101ms 1ms 626us 7ms 84us
# Rows sent 169.69M 0 213.73k 583.60 97.36 10.75k 9.83
# Rows examine 60.26G 0 866.23k 207.25k 328.61k 70.68k 201.74k
# Query size 120.31M 35 21.07k 413.76 719.66 148.97 363.48

• Exectime：執(zhí)行時間。
• Lock time：表鎖的時間。
• Rows sent：返回的結(jié)果集記錄數(shù)。
• Rowsexamine：實際掃描的記錄數(shù)。
• Query size：應(yīng)用和數(shù)據(jù)庫交互的查詢文本大小。

# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M Item
# ==== ================== ================ ====== ======= ==== ===== =====
# 1 0x5931CCE8168ECE59 92062.4390 42.6% 168672 0.5458 1.00 0.01 SELECT game_info game_stat
# 2 0x0E8691F18411F3DC 23404.4270 10.8% 18602 1.2582 0.60 0.04 SELECT game_info game_stat game_info_2

• Rank：所有查詢?nèi)罩痉治鐾戤吅?#xff0c;此查詢的排序。
• Query ID：查詢的標識字符串。
• Responsetime：總的響應(yīng)時間，以及總占比。一般小于5%可以不用關(guān)注。
• Calls：查詢被調(diào)用執(zhí)行的次數(shù)。
• R/Call：每次執(zhí)行的平均響應(yīng)時間。
• Apdx：應(yīng)用程序的性能指數(shù)得分。（Apdex響應(yīng)的時間越長，得分越低。）
• V/M：響應(yīng)時間的方差均值比（變異數(shù)對平均數(shù)比，變異系數(shù)）。可說明樣本的分散程度，這個值越大，往往是越值得考慮優(yōu)化的對象。
• Item：查詢的簡單顯示，包括查詢的類型和所涉及的表。

以下將按默認的響應(yīng)時間進行排序，并列出TOP n條查詢。并且pt-query-digest輸出了EXPLAIN的語句，以方便我們驗證查詢計劃。

# Query 1: 0.12 QPS, 0.07x concurrency, ID 0x5931CCE8168ECE59 at byte 243208985
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.01
# Query_time sparkline: | ^__|
# Time range: 2010-12-01 00:00:01 to 2010-12-17 09:04:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 55 168672
# Exec time 42 92062s 500ms 11s 546ms 640ms 77ms 501ms
# Lock time 68 283s 58us 101ms 2ms 690us 8ms 80us
# Rows sent 1 2.04M 10 100 12.67 9.83 14.86 9.83
# Rows examine 54 33.12G 204.96k 208.16k 205.90k 201.74k 0.00 201.74k
# Query size 50 60.64M 376 378 376.97 363.48 0 363.48
# String:
# Hosts
# Users sd_game
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s #
# 10s+ #
# Tables
# SHOW TABLE STATUS LIKE 'game_info'\G
# SHOW CREATE TABLE 'game_info'\G
# SHOW TABLE STATUS LIKE 'game_stat'\G
# SHOW CREATE TABLE 'game_stat'\G
# EXPLAIN /*!50100 PARTITIONS*/
select ...

4. 數(shù)據(jù)庫設(shè)計

數(shù)據(jù)庫設(shè)計中DBA關(guān)注的兩個階段：邏輯數(shù)據(jù)庫設(shè)計階段和物理數(shù)據(jù)庫設(shè)計階段。

4.1 邏輯設(shè)計

邏輯數(shù)據(jù)庫的設(shè)計大體可以分為以下這些步驟

• 創(chuàng)建并檢查ER模型

  此步驟主要是標識實體及實體之間的關(guān)系。標識實體的一種方法就是研究用戶需求說明里的名詞或名詞短語。

  標識關(guān)系也可以通過研究需求說明書來實現(xiàn)，需求說明書里的動詞或動詞短語往往表征了某種關(guān)系。大多數(shù)情況下，關(guān)系都是二元的。

• 將ER模型映射為表

  這個步驟的主要目的是為步驟1建立的ER模型產(chǎn)生表的描述。這組表應(yīng)該代表邏輯數(shù)據(jù)模型中的實體、關(guān)系、屬性和約束。產(chǎn)生表的描述后，需要檢查表是否滿足用戶的需求和業(yè)務(wù)規(guī)則。

4.2 物理設(shè)計

物理數(shù)據(jù)庫設(shè)計用于確定邏輯設(shè)計如何在目標關(guān)系數(shù)據(jù)庫中物理地實現(xiàn)。它描述了基本表、文件組織、用戶高效訪問數(shù)據(jù)的索引、相關(guān)的完整性約束及安全性限制。

物理設(shè)計又可以分為如下幾步：

把邏輯設(shè)計轉(zhuǎn)換為物理表、分析事務(wù)、選擇文件組織方式、選擇索引（基于最重要的事務(wù)），以及適當?shù)剡M行反范式設(shè)計（這么做是為了擁有更好的性能）、列出最終表的詳細說明。

• 將邏輯設(shè)計轉(zhuǎn)換為物理表
  將邏輯設(shè)計轉(zhuǎn)換為物理表即用特定的數(shù)據(jù)庫語言來實現(xiàn)邏輯設(shè)計過程中產(chǎn)生的表的描述，可以輸出的信息有表匯總。

• 分析事務(wù)
  分析事務(wù)指的是分析數(shù)據(jù)庫需要滿足的用戶需求，只有了解了必須要支持的事務(wù)的細節(jié)，才能做出有意義的物理設(shè)計抉擇。分析預(yù)期的所有事務(wù)是極為耗時的，只需研究最重要的那部分事務(wù)即可。
  最重要的事務(wù)一般是指如下兩種事務(wù)：

  • 經(jīng)常運行的事務(wù)和對性能產(chǎn)生重大影響的事務(wù)。
  • 業(yè)務(wù)操作的關(guān)鍵事務(wù)。

• 選擇文件組織方式
  選擇文件組織方式是指選擇表數(shù)據(jù)的存放方式。
  物理設(shè)計數(shù)據(jù)庫的目標之一就是以有效的方式存儲數(shù)據(jù)。如果目標DBMS允許，則可以為每個表選擇一個最佳的文件組織方式。一般有如下兩種方法。

  • 保持記錄的無序性并且創(chuàng)建所需數(shù)目的二級索引。
  • 通過指定主鍵或聚簇索引使表中記錄為有序的。這種情況下，應(yīng)該選擇如下的列來排序或聚簇索引記錄。
    • 經(jīng)常用于連接操作的列，因為這樣會使連接更有效率。
    • 在表中經(jīng)常按某列的順序訪問記錄的列。

• 選擇索引
  設(shè)計索引需要平衡性能的提升和維護的成本。以下是創(chuàng)建索引的一些基本指導原則：

  • 不必為小表創(chuàng)建索引。在內(nèi)存中查詢該表會比存儲額外的索引結(jié)構(gòu)更加有效。
  • 為檢索數(shù)據(jù)時大量使用的列增加二級索引。
  • 為經(jīng)常有如下情況的列添加二級索引。
    • 查詢或連接條件
    • ORDERBY
    • GROUP BY
    • 其他操作（如UNION或DISTINCT）
  • 考慮是否可以用覆蓋索引（covering index）。
  • 如果查詢將檢索表中的大部分記錄（例如25%），即使表很大，也不創(chuàng)建索引。這時候，查詢整表可能比用索引查詢更有效。
  • 避免為由長字符串組成的列創(chuàng)建索引。

• 反范式設(shè)計
  反范式的一些方法包括但不限于如下幾點

  • 合并表。
  • 冗余列減少連接。
  • 引入重復(fù)組。
  • 創(chuàng)建統(tǒng)計表。
  • 水平/垂直分區(qū)。

• 列出最終表的詳細說明

  只需要列出重要的表即可。

  以下索引是否建立、數(shù)據(jù)量及數(shù)據(jù)增長的情況要根據(jù)具體的業(yè)務(wù)需求來確定。

  • 記錄數(shù)：記錄數(shù)，可補充說明未來半年、1年或2年的記錄數(shù)。
  • 增長量：單位時間的數(shù)據(jù)增長量。如果量大可以按每天；如果量不大則可以按每月。
  • 表字段的區(qū)別度：主要是考慮到將來在此字段上建立索引類型選擇時作參考，當字段值唯一時可以不考慮；當字段值不唯一時，估算一個區(qū)別度，近似即可。
  • 表的并發(fā)：根據(jù)具體的業(yè)務(wù)需求預(yù)測表的并發(fā)訪問，或者說明高峰期的并發(fā)程度。

5. 導入導出數(shù)據(jù)

MySQL提供了好幾種導出導入數(shù)據(jù)的方法：LOADDATA、mysqlimport、SELECT…INTO OUTFILE、mysqldump、mysql。其中，mysqldump和mysqlimport是相反的操作，SELECT…INTO OUTFILE和LOADDATAINFILE是相反的操作。

5.1 規(guī)則簡介

• 文本文件里的特殊字符處理

  LOADDATA和SELECT…INTO OUTFILE、mysqlimport和mysqldump有一組專門的用來處理文本文件中特殊字符的選項，具體如下所示。

  • FIELDS TERMINATED BY’fieldtermstring’：各列（字段）之間用什么字符分隔，默認是tab，一般設(shè)置為逗號“,”。
  • [OPTIONALLY]ENCLOSED BY’char’：值被什么字符引起來，一般設(shè)置為引號’"'，如果指定了OPTIONALLY，則ENCLOSEDBY’char’只對字符串數(shù)據(jù)類型（比如CHAR、BINARY、TEXT或ENUM）生效。
  • ESCAPED BY’escchar’：定義轉(zhuǎn)義字符，默認是“\”。
  • LINES TERMINATED BY’linetermstring’：定義行結(jié)束符，用于分隔行。

• 文本文件的數(shù)據(jù)格式
  所有命令都要求有關(guān)的文本文件必須嚴格遵守一種數(shù)據(jù)格式，具體如下所示

  • 數(shù)值：可以用科學計數(shù)法。
  • 字符串：字符串里的特殊字符必須加上反斜線字符作為識別標志，以區(qū)別于各種分隔符。
  • NULL值：假設(shè)“\”作為轉(zhuǎn)義前導字符，“‘”作為字符串的前后綴標記，那么在導出操作中，NULL值將被表示為\N；在沒有指定轉(zhuǎn)義前導字符的導出操作中，NULL值將被表示為由4個字符構(gòu)成的字符串。在指定了轉(zhuǎn)移前導字符的操作中，MySQL將把NULL、\N、’\N’都解釋為NULL值，但’NULL’將被解釋為一個字符串’NULL’。

5.2 使用mysqldump導出，使用mysql導入

雖然mysqldump速度較慢，但這種方式有最好的兼容性，這也是目前使用最為廣泛的備份數(shù)據(jù)的方式。

導出數(shù)據(jù)：

1. 導出指定的表

   mysqldump test --tables test1 test4 > test1_test4.sql
   

2. 分別導出sql文件和數(shù)據(jù)文件（數(shù)據(jù)值以tab分隔）。

   mysqldump --tab=/home/garychen/tmp test
   

3. 分離導出sql文件和數(shù)據(jù)文件（定制數(shù)據(jù)格式，數(shù)據(jù)值以逗號分隔）

   mysqldump --tab=/home/garychen/tmp --fields-terminated-by=',' --fields-enclosed-by=''' test
   

4. 導出某個庫

   mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases test > test_db.sql
   

   參數(shù)說明：

   –complete-insert：導出的dump文件里，每條INSERT語句都包括了列名。

   –force：即使出現(xiàn)錯誤（如VIEW引用的表已經(jīng)不存在了），也要繼續(xù)執(zhí)行導出操作（mysqldump會打印出錯誤，注釋完VIEW定義后繼續(xù)后續(xù)的數(shù)據(jù)導出）。

   –insert-ignore：生成的INSERT語句是INSERT IGNORE的形式，如果導入此文件，即使出錯了也仍然可以繼續(xù)導入數(shù)據(jù)（當作警告）。

   –databases：類似–tables，后面可以跟多個值。

   –compatible=name：導出的文件和其他數(shù)據(jù)庫更兼容（但不確保），name的值可以是ANSI、MYSQL323、MYSQL40、POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO_KEY_OPTIONS、NO_TABLE_OPTIONS或NO_FIELD_OPTIONS。

5. 導出所有的數(shù)據(jù)庫。

   mysqldump --all-databases --add-drop-database > db.sql
   

6. 導出xml格式的數(shù)據(jù)

   mysqldump -u root -p --xml mylibrary > /tmp/mylibrary.xml
   

   如果有二進制數(shù)據(jù)，則要使用選項–hex-blob。

   InnoDB若想獲得一致性的數(shù)據(jù)庫副本，則要啟用選項–single-transaction。

mysqldump不能利用通配符導出多個表，表比較多的時候，可以先SELECT出要導出的表，如下語句即可查詢到所有的表。

select group_concat(table_name SEPARATOR ' ') from information_schema.tables where table_schema ='db_name' and table_name
like 'prefix%';

或者，可以采用如下方式將表名導出到一個文件

mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt

然后運行如下命令導出數(shù)據(jù)

mysqldump db 'cat tbs.txt' > dump.sql

也可以忽略部分表，加上參數(shù)–ignore-table=db_name.tbl_name1、–ignore-table=db_name.tbl_name2。

mysqldump可以把警告和錯誤追加記錄在文件中，加上參數(shù)–log-error=file_name即可。

如果使用mysqldump導出數(shù)據(jù)，可以考慮的優(yōu)化的方式有如下5種。

• 選擇I/O活動低的時候。
• I/O分離（數(shù)據(jù)盤和備份盤I/O分離）。
• 輸出到管道壓縮（gzip）。
• –quick跳過內(nèi)存緩沖（–opt默認啟用）。
• 從數(shù)據(jù)保留策略上想辦法，把不需要修改的大量數(shù)據(jù)放到歷史表中，而不是每次都備份。

導入數(shù)據(jù)：

mysqldump導出的SQL轉(zhuǎn)儲文件，可以用如下的形式將數(shù)據(jù)導入到數(shù)據(jù)庫中。

mysql db_name < db_name.sql

轉(zhuǎn)儲文件（dump文件）里面一般指定了set names utf8，所以我們在導入的時候不再需要指定特殊的字符集。有一些特殊的場合，SQL文件是以其他的字符集導出的，這個時候?qū)胍⒁獗３治募淖址?、客戶端字符集和連接的字符集的一致性。

mysql --default-character-set=charset_name database_name < import_table.sql

–default-character-set的意思是，客戶端和連接都默認使用charset_name字符集。

5.3 使用SELECT INTO OUTFILE命令導出數(shù)據(jù)

如果想要進行SQL級別的表備份，可以使用SELECT INTO OUTFILE命令語句。對于SELECT INTO OUTFILE，輸出的文件不能先于輸出存在。

SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM exporttable;
SELECT * INTO OUTFILE '/tmp/testfile.txt' FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!' FROM
exporttable;
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM test_table;

5.4 使用LOAD DATA導入數(shù)據(jù)

LOAD DATA INFILE是讀取文件導入表中。

如果MySQL服務(wù)器和LOADDATA命令不在同一臺計算機上執(zhí)行，當想導入本地文件系統(tǒng)的文件時，則需要使用語法變體LOAD DATA…LOCAL INFILE…，也就是說，如果指定LOCAL關(guān)鍵詞，則表明從客戶主機讀文件。如果沒指定LOCAL，那么文件必須位于服務(wù)器上。

如果LOADDATAINFILE不支持指定字符集，那么在導入前需要確認當前數(shù)據(jù)庫的字符集，如果與當前數(shù)據(jù)庫的字符集不符，則使用SET character_set_database命令進行更改。

LOAD DATA的優(yōu)化

對于InnoDB的優(yōu)化，建議的方式如下。

• 將innodb_buffer_pool_size設(shè)置得更大些。
• 將innodb_log_file_size設(shè)置得更大些，如256MB。
• 設(shè)置忽略二級索引的唯一性約束，SETUNIQUE_CHECKS=0。
• 設(shè)置忽略外鍵約束，SET FOREIGN_KEY_CHECKS=0。
• 設(shè)置不記錄二進制日志，SET sql_log_bin=0。
• 按主鍵順序?qū)霐?shù)據(jù)。由于InnoDB使用了聚集索引，如果是順序自增ID的導入，那么導入將會更快，我們可以把要導入的文件按照主鍵順序先排好序再導入。
• 對于InnoDB引擎的表，可以在導入前，先設(shè)置autocommit=0
• 可以將大的數(shù)據(jù)文件切割為更小的多個文件，例如使用操作系統(tǒng)命令split切割文件，然后再并行導入數(shù)據(jù)。

對于MyISAM的優(yōu)化，建議的方式如下

• 將bulk_insert_tree_size、myisam_sort_buffer_size、key_buffer_size設(shè)置得更大些。
• 先禁用key（ALTERTABLE…DISABLEKEYS），然后再導入數(shù)據(jù)，然后啟用key（ALTERTABLE…ENABLEKEYS）。重新啟用key后，可以批量重新創(chuàng)建索引，批量創(chuàng)建索引的效率比在逐筆插入記錄時創(chuàng)建索引要高效得多。注意ALTERTABLE…DISABLEKEYS禁用的只是非唯一索引，唯一索引或主鍵是不能禁用的，除非你先手動移除它。
• 使用LOAD DATA INFILE，tab分隔的文件更容易解析，比其他方式更快。

5.5 用mysqlimport工具導入

mysqlimport命令的語法格式如下

mysqlimport databasename tablename.txt

5.6 用split切割文件，加速導入數(shù)據(jù)

split命令的作用是切割文件，語法格式如下所示。

split [OPTION] [INPUT [PREFIX]]

如果不加入任何參數(shù)，默認情況下是以1000行的大小來分割的。

6. 事務(wù)和鎖

MySQL Server級別的鎖大致有兩種：

• Table locks（表鎖）
• Global locks（全局鎖）

6.1 MyISAM的表鎖

表鎖定的原理：

• 對于WRITE，MySQL使用的表鎖定方法原理如下。

  • 如果在表上沒有鎖，則在它上面放一個寫鎖。

  • 否則，把鎖定請求放在寫鎖定隊列中。

• 對于READ，MySQL使用的鎖定方法原理如下。

  • 如果在表上沒有寫鎖定，則把一個讀鎖定放在它上面。

  • 否則，把鎖定請求放在讀鎖定隊列中

當一個鎖定被釋放時，鎖定可先被寫鎖定隊列中的線程得到，然后是讀鎖定隊列中的線程。

以上的機制，在很多基于MyISAM引擎表的程序中可能會導致嚴重的性能問題。建議的解決方案是設(shè)置變量-low-priority-updates=1，即可以在系統(tǒng)級別進行設(shè)置，以避免SELECT查詢線程大量累計。

6.2 事務(wù)定義和隔離級別

事務(wù)是數(shù)據(jù)庫管理系統(tǒng)執(zhí)行過程中的一個邏輯單元，由有限的操作序列構(gòu)成。

事務(wù)的ACID特性

• 原子性（Atomic）

  事務(wù)作為一個整體被執(zhí)行，包含在事務(wù)中的對數(shù)據(jù)庫的操作要么全部被執(zhí)行，要么全部都不執(zhí)行。MyISAM引擎的表，它不支持事務(wù)，那么在出

  錯之前的值是可以被正常插入到表中的。

• 一致性（Consistency）

  事務(wù)應(yīng)確保數(shù)據(jù)庫的狀態(tài)從一個一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€一致狀態(tài)。一致狀態(tài)的含義是數(shù)據(jù)庫中的數(shù)據(jù)應(yīng)滿足約束。

• 隔離性（Isolation）

  多個事務(wù)并發(fā)執(zhí)行時，一個事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行。

• 持久性（Durability）

  已被提交的事務(wù)對數(shù)據(jù)庫的修改應(yīng)該被永久保存在數(shù)據(jù)庫中。

事務(wù)的隔離級別

事務(wù)隔離級別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對并發(fā)性能的影響也會越大。MySQL事務(wù)包含如下4個隔離級別，按隔離級別從低到高排列如下。

• read uncommitted（dirty read）

  read uncommitted也稱為讀未提交，事務(wù)可以看到其他事務(wù)更改了但還沒有提交的數(shù)據(jù)，即存在臟讀的情況。

• read committed

  read committed也稱為讀提交，事務(wù)可以看到在它執(zhí)行的時候，其他事務(wù)已經(jīng)提交的數(shù)據(jù)，已被大部分數(shù)據(jù)庫系統(tǒng)采用。允許不可重復(fù)讀，但不允許臟讀。

• repeatable read

  repeatableread也稱為可重復(fù)讀。同一個事務(wù)內(nèi)，同一個查詢請求，若多次執(zhí)行，則獲得的記錄集是相同的，但不能杜絕幻讀。

• serializable

  serializable也稱為序列化，最高級別的鎖，它解決了幻讀，它將鎖施加在所有訪問的數(shù)據(jù)上。

6.3 InnoDB的行鎖

行級鎖定的優(yōu)點：

• 當在很多線程中訪問不同的行時只存在少量鎖定沖突。
• 回滾時只有少量的更改。
• 可以長時間鎖定單一的行

幾種行鎖技術(shù)

• 記錄鎖（index-rowlocking）：這是一個索引記錄鎖。
• 間隙鎖：這是施加于索引記錄間隙上的鎖。
• next-key鎖：記錄鎖加間隙鎖的組合。

等待行鎖超時

有時我們在慢查詢?nèi)罩局袝吹揭恍┖芎臅r的查詢，但單獨執(zhí)行卻很快，此時有可能就是因為該查詢因等待InnoDB行鎖而超時。

MVCC簡要介紹

單純靠行級別的鎖，是不可能實現(xiàn)好的并發(fā)性的，MySQL InnoDB還需要配合MVCC（MultiversionConcurrencyControl）技術(shù)來提供高并發(fā)訪問。在很多情況下MVCC可以不需要使用鎖，即可實現(xiàn)更新數(shù)據(jù)時的無阻塞讀。

在常用的事務(wù)隔離級別read committed和repeatableread級，都應(yīng)用了MVCC技術(shù)。MVCC會保存某個時間點上的數(shù)據(jù)快照。這就意味著事務(wù)可以看到一個一致的數(shù)據(jù)視圖，不管它們還需要運行多久。

7. 死鎖

死鎖是指兩個或兩個以上的事務(wù)在執(zhí)行過程中，因爭奪資源而造成的一種互相等待的現(xiàn)象，若無外力作用，它們都將無法進行下去。

產(chǎn)生死鎖有4個必要條件。

• 禁止搶占（no preemption）
• 持有和等待（hold and wait）
• 互斥（mutualexclusion）
• 循環(huán)等待（circular waiting）

MySQL死鎖的解決方法

經(jīng)常提交你的事務(wù)。小事務(wù)更少地傾向于沖突。

• 以固定的順序訪問你的表和行。這樣事務(wù)就會形成定義良好的查詢并且沒有死鎖。
• 將精心選定的索引添加到你的表中。這樣你的查詢就只需要掃描更少的索引記錄，并且因此也可以設(shè)置更少的鎖定。
• 不要把無關(guān)的操作放到事務(wù)里面。
• 在并發(fā)比較高的系統(tǒng)中，不要顯式加鎖，特別是在事務(wù)里顯式加鎖。
• 盡量按照主鍵/索引去查找記錄，范圍查找增加了鎖沖突的可能性，也不要利用數(shù)據(jù)庫做一些額外的計算工作。
• 優(yōu)化SQL和表設(shè)計，減少同時占用太多資源的情況。

8. 其他特性

8.1 臨時表

臨時表指的是CREATE TEMPORARYTABLE命令創(chuàng)建的臨時的表，臨時表只對當前連接可見，對其他連接不可見，結(jié)束連接或中斷，數(shù)據(jù)表（數(shù)據(jù)）將丟失。常使用臨時表來存儲一些中間結(jié)果集，如果需要執(zhí)行一個很耗資源的查詢或需要多次操作大表，那么把中間結(jié)果或小的子集放到一個臨時表里，可能會有助于加速查詢。

8.2 分區(qū)表

分區(qū)表技術(shù)允許按照設(shè)置的規(guī)則，跨文件系統(tǒng)分配單個表的多個部分。實際上，表的不同部分在不同的位置被存儲為單獨的表。用戶所選擇的、實現(xiàn)數(shù)據(jù)分割的規(guī)則被稱為分區(qū)函數(shù)，在MySQL中它可以是模數(shù)，或者是簡單地匹配一個連續(xù)的數(shù)值區(qū)間或數(shù)值列表，或者是一個內(nèi)部HASH函數(shù)，或者是一個線性HASH函數(shù)。

分區(qū)類型，RANGE分區(qū)、LIST分區(qū)、HASH分區(qū)、KEY分區(qū)和子分區(qū)。常用的存儲引擎，如InnoDB、MyISAM、MEMORY都支持分區(qū)表。

• RANGE分區(qū)的表是通過如下這種方式進行分區(qū)的，基于一個連續(xù)區(qū)間的列值，把多行分配給分區(qū)，例如某個時間段的值屬于某個分區(qū)，某個數(shù)值范圍的值應(yīng)該屬于某個分區(qū)。
• LIST分區(qū)在很多方面都類似于RANGE分區(qū)。和按照RANGE進行分區(qū)的方式一樣，每個分區(qū)都必須明確定義。它們的主要區(qū)別在于，LIST分區(qū)中每個分區(qū)的定義和選擇是基于值列表的，而RANGE分區(qū)是從屬于一個連續(xù)區(qū)間值的集合的。
• HASH分區(qū)是基于用戶定義的表達式的返回值選擇分區(qū)。它主要用來確保數(shù)據(jù)在預(yù)先確定了數(shù)目的分區(qū)中是平均分布的。
• 按照KEY進行分區(qū)類似于按照HASH進行分區(qū)，除了HASH分區(qū)使用的是用戶自定義的表達式，而KEY分區(qū)的散列函數(shù)是由MySQL服務(wù)器提供的。
• 子分區(qū)是分區(qū)表中每個分區(qū)的再次分割。

由于分區(qū)表的不成熟，可能會給整個系統(tǒng)帶來隱患。這里有一些通用的建議。

• 只有大表才可能需要分區(qū)，幾百萬筆記錄的表并不算大，對于一些高配置的數(shù)據(jù)庫主機，幾千萬甚至上億條數(shù)據(jù)的表也不算大。
• 分區(qū)數(shù)不能過多，很難想象大于500的分區(qū)數(shù)。
• 查詢的時候，不要跨越多個分區(qū)，建議最多跨越1~2個分區(qū)。
• 索引的列應(yīng)該是分區(qū)的列，或者有其他條件限制的分區(qū)，否則訪問所有分區(qū)上面的索引進行查找，開銷會比較大。