三個經(jīng)典的 MySQL 問題

大家好，今天給大家上3個經(jīng)典的MySQL問題，希望能對大家有幫助！但是因為筆者計算機水平有限，可能會存在一些錯誤，煩請指出、斧正！謝謝！

• 在MySQL中INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么區(qū)別
• 你會推薦使用 datetime 還是 timestamp 字段？為什么
• MyISAM 與 InnoDB，什么場景選擇哪一個

問題一

在MySQL中INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么區(qū)別？

我們有兩張表：

TableA：
id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc                                           
TableB：
id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai

INNER JOIN（內(nèi)連接）

這是最簡單，最常見，也是最容易理解的Join，兩張表使用內(nèi)連接查詢時，得到的結果是兩張表中完全匹配的行集。

對于上述兩張表，我們有：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
  FROM TableA
 INNER JOIN TableB
    ON TableA.id = TableB.id2;

得到的結果即為：

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm

得到的結果有4個字段，firstName 、 lastName 、 age 、 Place，就是我們上面SQL語句SELECT的4個字段，F(xiàn)ROM和INNER JOIN后面的兩個表名就是要內(nèi)連接的兩張表，ON后面就是在其中尋找共同點的字段。

LEFT JOIN（左連接）

左連接查詢會返回左表中所有行，無論這些行是不是有任何一行在右表中匹配。

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
  FROM TableA
  LEFT JOIN TableB
    ON TableA.id = TableB.id2;

查詢結果是：

firstName                lastName                 age   Place
.............................................................
arun                     prasanth                 24    kerala
ann                      antony                   24    usa
sruthy                   abc                      25    ekm
new                      abc                      NULL  NULL

我們可以看到，TableA中所有行都過來了，即使firstName為new，lastName為abc的那一行id為6，在TableB中找不到id為6的行，仍然在結果集中存在。值得注意的是，因為其id為6，在TableB中找不到對應的id，因此其沒有age和Place字段的內(nèi)容。

RIGHT JOIN（右連接）

右連接查詢會返回右表中所有行，無論這些行是不是有任何一行在左表中匹配。

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
  FROM TableA
 RIGHT JOIN TableB
    ON TableA.id = TableB.id2;

結果集：

firstName                lastName               age     Place
...............................................................
arun                     prasanth               24     kerala
ann                      antony                 24     usa
sruthy                   abc                    25     ekm
NULL                     NULL                   24     chennai

FULL JOIN（全連接）

SQL語句如下：

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
  FROM TableA
  FULL JOIN TableB
    ON TableA.id = TableB.id2;

結果集為：

firstName             lastName              age    Place
...........................................................
arun                  prasanth              24    kerala
ann                   antony                24    usa
sruthy                abc                   25    ekm
new                   abc                   NULL  NULL
NULL                  NULL                  24    chennai

查詢結果是左連接和右連接的并集。

問題二

你會推薦使用 datetime 還是 timestamp 字段？為什么？

1. 正如Mysql文檔描述的那樣——datetime的范圍是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”，而timestamp的范圍是 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。如果不想程序在2038年出問題，從這個方面作者建議還是使用datetime。
2. 有一種觀點是既不使用 DATETIME 也不使用 TIMESTAMP 字段。如果想將特定的一天作為一個整體來表示（例如生日），可以使用 DATE 類型，但是如果需要更具體的時間，不要使用 DATETIME 或 TIMESTAMP，而是使用 BIGINT，并簡單地存儲自紀元以來的毫秒數(shù)（如果使用的是 Java，則為 System.currentTimeMillis()）。

這樣有幾個優(yōu)點。其一，可以在遷移數(shù)據(jù)庫時避免因為數(shù)據(jù)類型差異，比如MySQL的DATETIME類型和Oracle的DATETIME類型之間可能存在差異，timestamp類型的精度可能也存在差異，MySQL的timestamp精度不是一開始就支持毫秒精度的。其二，沒有時區(qū)問題，無論是哪個時區(qū)，因為開始計算的時間不同，無論當前時間如何，跨度是一致的。其三，沒有timestamp和datatime的范圍問題，哪怕是datatime，8000年以后也不能用了，沒準我的數(shù)據(jù)庫8000年能用8000年呢。

1. 需要注意的是，bigint是占用8個字節(jié)，而timestamp只占用4個字節(jié)。從MySQL 5.6.4開始，Datetime的存儲空間變成了5個字節(jié)了(準確的說應該是5字節(jié)+0~3個字節(jié)的FSP分秒精度）。

問題三

MyISAM 與 InnoDB，什么場景選擇哪一個？

MyISAM 和 InnoDB 之間的主要區(qū)別在于參照完整性和事務。還有其他區(qū)別，例如鎖定、回滾和全文搜索。

參照完整性

參照完整性確保表之間的關系保持一致。更具體地說，當一個表（例如 Listings）有一個外鍵（例如 Product ID）指向另一個表（例如 Products）時，當指向的表發(fā)生更新或刪除時，這些更改會級聯(lián)到鏈接的表。在該示例中，如果重命名產(chǎn)品，則鏈接的表的外鍵也會更新；如果從Products表中刪除產(chǎn)品，則指向已刪除條目的 Listings 表中得到任何列表也將被刪除。此外，任何 Listings 表中的新列表都必須具有指向有效的現(xiàn)有條目的外鍵。

InnoDB 是一個關系型 DBMS (RDBMS)，因此具有參照完整性，而 MyISAM 則沒有。

事務和原子性

使用數(shù)據(jù)操作語言 (DML) 語句管理表中的數(shù)據(jù)，例如 SELECT、INSERT、UPDATE 和 DELETE。事務將兩個或多個 DML 語句組合成一個工作單元，因此要么應用整個單元，要么不應用。

MyISAM 不支持事務，而 InnoDB 支持。

如果在使用 MyISAM 表時操作被中斷，該操作將立即中止，并且受影響的行（甚至每行中的數(shù)據(jù)）仍然受到影響，即使該操作沒有完成。

如果一個操作在使用 InnoDB 表時被中斷，因為它使用具有原子性的事務，任何沒有完成的事務都不會生效，因為沒有提交。

表鎖定與行鎖定

當查詢 MyISAM 表時，正在查詢的整個表將被鎖定。這意味著后續(xù)查詢將僅在當前查詢完成后才能執(zhí)行。如果您正在讀取一個大表，并且有頻繁的讀寫操作，這可能意味著大量的查詢積壓。

而當查詢 InnoDB 表時，只有涉及的行被鎖定，表的其余部分仍然可進行 CRUD 操作。這意味著查詢可以在同一個表上同時運行，只要它們不使用同一行。

InnoDB 中的此功能稱為并發(fā)。盡管并發(fā)性很好，但在表的范圍查詢時有一個缺點，因為在內(nèi)核線程之間切換存在開銷，我們應該對內(nèi)核線程設置限制以防止服務器停止。

事務和回滾

當在 MyISAM 中執(zhí)行一個操作時，更改會立刻生效；在 InnoDB 中，這些更改可以回滾。用于控制事務的最常用命令是 COMMIT、ROLLBACK 和 SAVEPOINT。

1. COMMIT - 您可以編寫多個 DML 操作，但只有在進行 COMMIT 時才會保存更改
2. ROLLBACK - 您可以丟棄任何尚未提交的操作
3. SAVEPOINT - 實現(xiàn)回滾到指定保存點

可靠性

MyISAM 不提供數(shù)據(jù)完整性——硬件故障、不正常的關機操作都可能導致數(shù)據(jù)損壞。這將需要修復或重建索引和表。

而InnoDB 使用事務日志、雙寫緩沖區(qū)和自動校驗和和驗證來防止數(shù)據(jù)損壞。在 InnoDB 進行任何更改之前，它會將事務之前的數(shù)據(jù)記錄到一個名為 ibdata1 的系統(tǒng)表空間文件中。如果發(fā)生崩潰，InnoDB 將通過這些日志來自動恢復。

全文索引

InnoDB 在 MySQL 5.6.4 版本之前不支持 FULLTEXT 索引。

但是，這不是使用 MyISAM 的理由。最好使用最新版本的 MySQL 。并不是說使用 FULLTEXT 索引的 MyISAM 表不能轉(zhuǎn)換為 InnoDB 表。