主要考慮的點(diǎn)
- 數(shù)據(jù)量大小
- 阻塞的時(shí)間長短
Mysql構(gòu)建索引
頁管理、目錄項(xiàng)記錄、頁分裂
PG構(gòu)建索引
排序，元數(shù)據(jù)操作，物理復(fù)制
--------------------關(guān)于mysql和pg構(gòu)建索引的處理過程-----------------------
MySQL和PostgreSQL（PG）作為流行的開源關(guān)系型數(shù)據(jù)庫系統(tǒng)，它們在索引的創(chuàng)建和構(gòu)建過程上有一些共同之處，同時(shí)也存在一些差異。
MySQL索引構(gòu)建過程：
頁管理：MySQL中的InnoDB存儲(chǔ)引擎使用頁作為存儲(chǔ)空間管理的單位，每個(gè)頁通常大小為16KB。數(shù)據(jù)存儲(chǔ)在頁中，并通過雙向鏈表進(jìn)行鏈接。在創(chuàng)建索引時(shí)，InnoDB會(huì)按照主鍵值的順序?qū)?shù)據(jù)組織到索引頁中，主鍵值較小的數(shù)據(jù)放在前面，較大的數(shù)據(jù)放在后面。
目錄項(xiàng)記錄：為了快速定位數(shù)據(jù)，InnoDB會(huì)為每個(gè)頁創(chuàng)建一個(gè)目錄項(xiàng)記錄，其中包括該頁的最小主鍵值和頁目錄號（頁號）。這樣，通過主鍵可以快速地定位到數(shù)據(jù)所在的頁。
頁分裂：當(dāng)插入新數(shù)據(jù)時(shí)，如果當(dāng)前頁的主鍵值超過了該頁的最大主鍵值，InnoDB會(huì)分配一個(gè)新的頁來存放數(shù)據(jù)，并更新鏈表，這個(gè)過程稱為頁分裂。頁分裂可能會(huì)導(dǎo)致數(shù)據(jù)的移動(dòng)和額外的I/O操作。
PostgreSQL索引構(gòu)建過程：
排序：PostgreSQL在創(chuàng)建B-Tree索引時(shí)，會(huì)先對待創(chuàng)建索引的數(shù)據(jù)進(jìn)行排序，然后按照B-Tree的頁面格式直接寫入頁面，避免了不必要的頁分裂。
元數(shù)據(jù)操作：在PostgreSQL中，創(chuàng)建索引的過程涉及到一系列的元數(shù)據(jù)操作，包括在Catalog系統(tǒng)中生成新索引的元數(shù)據(jù)，以及在索引列上進(jìn)行排序并生成B-Tree的page。
物理復(fù)制：PostgreSQL的標(biāo)準(zhǔn)復(fù)制使用WAL進(jìn)行物理復(fù)制，而MySQL的標(biāo)準(zhǔn)復(fù)制使用binlog進(jìn)行邏輯復(fù)制。PostgreSQL也支持通過發(fā)布/訂閱模式進(jìn)行邏輯復(fù)制。
對比分析：
頁管理：MySQL使用頁和雙向鏈表進(jìn)行管理，而PostgreSQL使用進(jìn)程而非線程模型，這可能導(dǎo)致更多的系統(tǒng)資源消耗，但也提供了更好的隔離性。
排序：PostgreSQL在創(chuàng)建索引前對數(shù)據(jù)進(jìn)行排序，而MySQL在插入數(shù)據(jù)時(shí)進(jìn)行排序，這可能會(huì)影響兩者的性能表現(xiàn)。
復(fù)制：MySQL的邏輯復(fù)制可能與PostgreSQL的物理復(fù)制在數(shù)據(jù)一致性和復(fù)制速度上有所不同。
ACID事務(wù)支持：兩者都支持ACID事務(wù)，但PostgreSQL提供了更強(qiáng)大的支持，例如行級安全（RLS）。
查詢優(yōu)化器：PostgreSQL通常被認(rèn)為擁有更優(yōu)秀的查詢優(yōu)化器。
擴(kuò)展性：PostgreSQL支持多種擴(kuò)展，如PostGIS提供地理空間能力，而MySQL的擴(kuò)展性主要體現(xiàn)在可插拔的存儲(chǔ)引擎架構(gòu)。
易用性：MySQL在某些方面更為寬容，例如在GROUP BY子句的SELECT語句中允許包含非聚合列。
生態(tài)：PostgreSQL的可擴(kuò)展架構(gòu)使其生態(tài)系統(tǒng)近年來更加繁榮。
在選擇數(shù)據(jù)庫時(shí)，需要考慮以上各點(diǎn)，并根據(jù)具體的應(yīng)用場景和業(yè)務(wù)需求來決定最適合的數(shù)據(jù)庫系統(tǒng)。
