跳轉到

MySQL 與 PostgreSQL

查詢效能比較

假設 table 有 id (PK)、name, age 三個欄位，且在 name, age 有分別創建索引。

Example

SELECT p.*
FROM person p
WHERE p.id >  10;
  • Select 全部的欄位
  • Innodb 直接從 clustered index 搜尋
  • PostgreSQL 需要用指標去 heap table找

因此 MySQL 會比較快。

Example

SELECT p.*
FROM person p
WHERE p.age > 50;
  • Select 全部的欄位
  • 查詢條件是 non-clustered index, Innodb 必須再去 clustered index 搜尋

因此 PostgreSQL 會比較快。

Example

SELECT p.name
FROM person p
WHERE p.name = "Jojo";

由於是覆蓋索引查詢，跟是用 cluster index 還是 heap table 就沒有關係了，因此不會有太大差異。

MySQL (InnoDB) PostgreSQL
儲存資料的地方 IOT (Index-Organized Table) heap table
有查詢效能優勢的情境 以 cluster index 查詢時 以 non-cluster index 查詢時

Isolation

MySQL

  • 用 Read View 和 Undo Log 實現 MVCC
  • Read Committed 下每一次的 select 都會產生新的 Read View
  • Repeatable Read 下只有第一次的 select 產生 Read View

Warning

MySQL 的 Repeatable Read 會有 lost update /write non repeatable read /phantom read 問題，因為只有 select 讀的是 READ VIEW

Example

sessnio1 session2
start transaction repeatable read;
select amount from user*balance where id = 'Kenny'; /* return 100 */
start transaction repeatable read;
select amount from user*balance where id = 'Kenny'; /* return 100 */
update user_balance set amount = amount - 90 where id = 'Kenny';
commit;
update user_balance set amount = amount - 90 where id = 'Kenny'; /* return -80 */
commit;

解法：

  • 樂觀鎖
  • select … for update
  • table constraint

PostgreSQL

  • 用 WAL 實現 MVCC
  • 沒有 Gap Lock 跟 Next-Key Lock
  • For Update 只對指定的 record 加上 X lock
  • 在 Repeatable Read 下只會考慮 tx 開始前的 committed 版本，改動資料會檢查該 record 是否存在 tx 開始後的 committed 版本，有的話就會強制 rollback

Reference