索引

介紹

索引是數(shù)據(jù)庫提供的一種能夠快速查詢數(shù)據(jù)的方法，一般來說索引記錄了表中的一列或者多列的值與其存儲位置的對應關系，有時候還有排序等，很多人比喻索引就像一本的書的目錄一樣，直接通過目錄找到頁碼，這樣你就能快速的找到自己想要的內(nèi)容了（當然索引也有其他用處，比如唯一性的約束也很常見）。幾乎所有的 RDBMS 都提供索引，我們可以通過建立索引對表中的數(shù)據(jù)進行查詢或者排序，但是，代價是什么呢？

1. 本質(zhì)上索引是空間換時間，會增加存儲空間
2. 在插入和修改數(shù)據(jù)時索引也會更新，這會增加這些操作的時間消耗

不要小瞧索引的空間占用，特別是 RDS 中，某些情況下 Disk Space 會影響到 IOPS，所以可能你需要選擇容量時考慮下索引的空間，畢竟數(shù)據(jù)庫擴容也是一件比較麻煩的事情，以下是某個索引占用的空間描述：

oid          | 17461
table_schema | public
table_name   | messages
row_estimate | 9.14542e+06
total_bytes  | 123618959360
index_bytes  | 2265890816
toast_bytes  | 112305389568
table_bytes  | 9047678976
total        | 115 GB
index        | 2161 MB
toast        | 105 GB
table_bytes  | 8629 MB

PostgreSQL 支持這幾種常見的索引：

1. B-Tree：最常用的索引，處理等值查詢或者范圍查詢，基本上 99% 的情況下都是用這個。
2. Hash：可以做簡單的等值查詢（基本上用不到）。
3. GiST 與 SP-GiST：索引框架，可以在這種架構上實現(xiàn)很多不同的索引策略，基本上可以自己設計索引（比如地理范圍搜索），SP-GiST 提供了一些新的算法用來提高 GiST 在某些情況下的性能（雖然很強大，但是筆者的場景決定筆者很少用到）。
4. GIN：反轉(zhuǎn)索引，它可以處理多個鍵的值，常用它給數(shù)組類型建立索引（處理數(shù)組索引時會用到）。

在我們?nèi)粘ｍ椖恐?，B-Tree 與 GIN 是都采用過的，但是這些年的開發(fā)中 GIN 索引也就使用過了一次。對于一個進行應用開發(fā)的工程師來說，有時候并不需要了解索引具體是如何實現(xiàn)的，但是你需要知道什么時候需要使用索引。關于 B-Tree 的介紹與特點可以參閱 wiki https://en.wikipedia.org/wiki/B-tree，我也比較好奇國內(nèi)面試貌似挺喜歡問這個的。

創(chuàng)建索引

那么什么時候需要創(chuàng)建索引呢？簡而言之就是尋求功能與性能上的平衡。近年來存儲空間的問題往往不需要考慮太多，畢竟磁盤已經(jīng)很廉價了。如果你的應用是一個偏向查詢的應用，那么對每個查詢進行索引優(yōu)化是非常值得的，畢竟通常情況下存儲空間不是太大的問題，而且我們也不太在乎寫入的性能。但是如果你的應用有大量的寫入、更新等操作，這時候創(chuàng)建大量的查詢索引就需要考慮是否值得。

有時候我們常用備份恢復的數(shù)據(jù)庫（或者臨時表）進行業(yè)務數(shù)據(jù)的統(tǒng)計，這時候?qū)τ谝恍碗s的查詢是可以通過索引進行優(yōu)化的，建立索引再查詢的效率遠遠大于直接查詢，特別是對 JSON 內(nèi)容的查詢。

不要對一些內(nèi)容變化少的列建立索引，例如性別這種（只有男、女、其他），這種情況下應該考慮 partial index，例如：

CREATE INDEX male_author ON authors(name) WHERE gender = 'male';

很多表達式都可以寫在索引中，例如我們希望對郵件進行小寫字母的搜索，我們就可以對小寫字母進行一個特定的索引：

CREATE INDEX users_lower_email ON users(lower(email));

SELECT * FROM users WHERE lower(email) = 'test@test.com';

CREATE INDEX book_created_at_index ON books(date(created_at));

SELECT * FROM books WHERE date(created_at) = date('2019-03-07');

注意date 的例子，這個索引比直接對 timestamp 建立索引要高效。

針對不同列的查詢，推薦建立多列的索引，對于有排序的情況，排序索引也是可以的：

CREATE INDEX CONCURRENTLY IF NOT EXISTS "itemEvent$partial_localEventTimestamp"
ON item_event(local_event_timestamp DESC)
WHERE event_type = 'removeAnnotation'
    AND event_seq > 246500000;

并發(fā)創(chuàng)建

我們經(jīng)常會發(fā)現(xiàn)意料之外的由 SQL 引起性能問題，大多數(shù)是因為查詢時間過長，這自然很影響用戶體驗。可能的原因是一開始我們忽略了某個 SQL 的優(yōu)化，或者估計錯了數(shù)據(jù)量，或者數(shù)據(jù)的增長超過我們的預料等，這些情況基本上每個工程師都遇見過。解決方法也很簡單，如果是查詢數(shù)據(jù)庫引起的性能問題，那往往增加索引那就是第一選擇了。但問題就在執(zhí)行那句簡單的建立索引的語句上了，因為創(chuàng)建索引常常會阻止其他的一些數(shù)據(jù)庫常規(guī)的操作，PostgreSQL 需要阻止所有對目標表的寫操作，然后進行一次全表掃描去建立索引，所以在這個時候，你的 insert\update\delete 語句都會被阻塞。

當然，在我們的數(shù)據(jù)量不大的時候，全表掃描花不了多少時間，或者系統(tǒng)并沒有寫操作的時候，我們也不會在意是否需要鎖表去建立索引。但是對于一個生產(chǎn)環(huán)境的下的應用，鎖表基本上也就表示很多功能不可用了，這會極大的影響到用戶體驗，也會讓數(shù)據(jù)庫的變更非常昂貴。我想每個人幾乎都會有半夜進行數(shù)據(jù)庫變更的操作，我們會疲憊不堪，并且面帶血絲，然后擔心是不是自己做錯了什么。

簡而言之，我們現(xiàn)在已經(jīng)有很多的手段實現(xiàn)持續(xù)交付，特別是微服務化已經(jīng)可以讓我們做到一天部署好幾次自己的代碼到生產(chǎn)環(huán)境了，我們自然不想讓數(shù)據(jù)庫的變更成為持續(xù)交付的障礙，那么你就需要考慮使用并發(fā)創(chuàng)建索引這樣類似的功能，甚至在 CI 中進行數(shù)據(jù)庫的變更。當然有能力的進行非阻塞的變更是一個極大的好處，但并不是說我們需要經(jīng)常使用到這個功能，我們依舊需要認真設計自己的數(shù)據(jù)結(jié)構，認真考慮系統(tǒng)中的每一條查詢，因為數(shù)據(jù)結(jié)構在存儲層的變更成本還是大于軟件本身的成本的。

對于 PostgreSQL 來說，并發(fā)創(chuàng)建索引時非常簡單的，只需要在 CREATE INDEX 上加上 CONCURRENTLY， 這時 PostgreSQL 會進行兩次的全表掃描去等待正在執(zhí)行的 transaction 完成，這些 transaction 一般只有那些可能會使用或者修改索引的事務。所以顯而易見的是，使用 CONCURRENTLY 比普通的創(chuàng)建索引的方式慢的多，也會增加一定的 CPU 與 IO 負載，那么自然也會影響到數(shù)據(jù)庫的性能。那么，對于并發(fā)創(chuàng)建索引，我們應該注意什么呢？

CREATE INDEX CONCURRENTLY IF NOT EXISTS "itemEvent$partial_localEventTimestamp"
ON item_event(local_event_timestamp DESC)
WHERE event_type = 'removeAnnotation'
    AND event_seq > 246500000;

1. 對于任何的 DDL 操作，你都需要評估這個操作在生產(chǎn)環(huán)境中情況，包括：步驟、時間、影響性能、有無阻塞、風險等，即使 CONCURRENTLY 可以帶來非阻塞，但是數(shù)據(jù)庫的性能卻會受到影響，所以你肯定不希望這個事情在系統(tǒng)繁忙時發(fā)生。
2. 不論 DDL 操作在哪里執(zhí)行，都需要考慮改變過的 schema 是否與當前代碼版本兼容，有時候 migration 很順利，但是因為疏忽忘記了當前的代碼還是老代碼，而這種情況下又無法回滾程序，那基本上就是級別較高生產(chǎn)環(huán)境錯誤了。雖然這個問題很初級，但是有些團隊不一定有較強的數(shù)據(jù)庫能力，失去了對 DB 的敬畏也是很危險的。
3. 任何時候都需要考慮 rollback 或者錯誤補救方案，并且在補救方案中，每一步都必須是簡單的、命令式的語句。如果你需要手動的進行數(shù)據(jù)庫操作，那你就一定需要考慮自己的退路，一般來說人在面向錯誤時會慌亂，這時候再進行操作很容易會造成更嚴重的二次破壞。

DB migration for normal developer

這里我想說說跟 PostgreSQL 無關的事情，即 migration。我們與數(shù)據(jù)庫的關系在改變，以前可能一個公司就一個數(shù)據(jù)庫（它可能是分布式的或者是小型機上的），然后我們會雇傭薪水高的嚇人的 DBA 去照顧好這個最重要的資產(chǎn)（因為數(shù)據(jù)是最重要的資產(chǎn)），這個 DBA 或者 DBA 團隊會滿足我們的一切數(shù)據(jù)庫需求，例如幫我們建表、建索引，幫我們處理復雜查詢、優(yōu)化性能，或者幫我們編寫復雜難懂的存儲過程，這種方式是傳統(tǒng)單塊架構的典型的最終形態(tài)，強大的商用數(shù)據(jù)庫在這一層就能解決很多業(yè)務問題，即使它有著難以維護、難以測試等問題。還記得我們?yōu)槭裁匆呶⒎栈瘑?？很重要的原因是，單塊應用呆板、脆弱、難以修改維護，并不能很好的適應現(xiàn)在基礎設施的進步，我們推薦使用“笨拙”的中間件來代替復雜的、可怕的 ESB，我們也希望能夠根據(jù)業(yè)務選擇不同的數(shù)據(jù)存儲技術，并且將存儲封裝在服務的邊界之內(nèi)。無論怎么樣，現(xiàn)在輪到一般的開發(fā)人員進行數(shù)據(jù)庫操作了，如果微服務轉(zhuǎn)型的拆表做的很好，你的每個小服務應該沒有幾張表來存放你的數(shù)據(jù)，基本上很難有三張表以上 join 的場景了，但即使是這樣，你也需要建表，建索引，創(chuàng)建用戶或是進行表結(jié)構的修改，我們將這些步驟一般稱作 DB migration，這與 data migration 不同。

編寫

幾乎所有的 web 框架都提供了進行 DB migration 的工具或者能力，筆者使用過 active record、php Laravel、flyway、Liquibase 等等，也有一些 ROM 自帶的工具，這些工具都有類似的能力：在特定的時刻被觸發(fā)，然后執(zhí)行我們的 plan 對數(shù)據(jù)庫進行修改，并且記錄版本，如果出現(xiàn)錯誤也可以根據(jù)我們的預設進行回滾。目前進行 plan 編寫有 DSL 的方式或者 RAW SQL，大約是這樣：

這是一個典型的 DSL 的 migration plan 的一部分：

class CreateApiConsumers < ActiveRecord::Migration
  def change
    create_table :api_consumers do |t|
      t.string :name, null: false
      t.string :token, limit: 32, null: false
      t.string :description
      t.timestamps
    end
  end
end

或者這種：

CREATE TABLE item (
   id UUID NOT NULL,
   resource_id VARCHAR(128) NOT NULL,
   resource_type VARCHAR(128) NOT NULL,
   updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   CONSTRAINT item$pk PRIMARY KEY (id)
);

我是推薦第二種方式的，首先 DSL 并不能完全正確的使用數(shù)據(jù)庫的特性，這取決于 DSL 的編寫能力和數(shù)據(jù)庫驅(qū)動的功能支持，例如 Active Record 就無法異步并行的創(chuàng)建索引；然后 DSL 并不能直接對應具體做了什么，我們都知道 ROM 可能去做一些額外的事情比如獲取 schema 創(chuàng)建數(shù)據(jù)結(jié)構等，所以你很難確定 DSL 式的 migration 到底做了什么，不確定就會出現(xiàn)風險；因為 DSL 的功能是受限的，甚至有時候你需要再寫一些 SQL 來實現(xiàn)自己的想要的功能，維護性下降；最后，很多團隊使用 DSL 更多的是想解耦數(shù)據(jù)庫（同樣的 DSL 配合不同的驅(qū)動操作不同的數(shù)據(jù)庫），即線上 MySQL，開發(fā) H2 或者 SQLite，誠然這對開發(fā)非常友好，但是如果你的服務依賴一些數(shù)據(jù)庫的特性，或者一些數(shù)據(jù)結(jié)構其實并不兼容，那么這是極度危險的，我們推薦在任何時候都應該優(yōu)先考慮能否創(chuàng)造對等的開發(fā)環(huán)境。

當然，如果你的 migration 是有很多創(chuàng)建表的話，往往并不會有太大的問題，但一旦牽扯到修改的 DDL：例如增加索引、增加新列或者修改名稱這種操作，都是我們常說的"破壞性"的操作，破壞性的操作大多數(shù)情況是與代碼不兼容，或者發(fā)生了意外的鎖表，比如這個例子：某同學為某表創(chuàng)建了索引，并且在本地、測試環(huán)境都進行了測試，十分順利，因為這個索引并沒有修改表結(jié)構，所以并不擔心會對線上造成問題，但是他忽略了線上數(shù)據(jù)庫的流量是遠遠大于本地與測試環(huán)境的，而創(chuàng)建索引則會進行表鎖定，導致寫阻塞，從而造成了運營事故。編寫 migration 腳本時，也要考慮錯誤處理，或者 rollback。很多框架都提供了這樣的功能，你可以編寫 up 來進行正向的 migrate，然后 down 進行撤回。這個規(guī)則很簡單，出現(xiàn)了錯誤就執(zhí)行 down 中的命令。但是，這一點也需要慎重考慮，有時候的 down 可能是刪除表，或者移除列，這會造成意外的數(shù)據(jù)丟失。

最后我強烈推薦將 migration 的腳本 check-in 到代碼庫，保證不論是哪個環(huán)境的數(shù)據(jù)庫，都必須執(zhí)行完全一樣的代碼，盡量不要使用不同的環(huán)境變量去創(chuàng)造 if-else 的邏輯，例如 prod 或者 preprod。另外，我們 db migration 往往是追加的，而不是修改，也請考慮腳本的精簡與描述準確，我曾經(jīng)見過類似于 v96_add_index_for_new_tables.sql 這樣的文件，這樣的文件名幾乎沒有任何描述意義，請問在 96 次修改后，我怎么知道哪些表是 new_tables？或者，你真的需要 96 次修改？我覺得這時候我們需要進行一次整理了。

測試

這里的測試是指開發(fā)人員的測試，我們鼓勵開發(fā)人員去進行測試去獲取更快的反饋，這樣對項目質(zhì)量會有很大的提升。對于 migration plan，我們在測試時，需要確保：

1. 目標數(shù)據(jù)庫是否和線上數(shù)據(jù)庫一致？
   如果你本地有版本一致的數(shù)據(jù)庫運行，那這一條基本上沒什么問題。如果沒有，而且你不想裝太多的數(shù)據(jù)庫服務的話，可以考慮使用 docker，我們常常使用 docker-compose 創(chuàng)建 dev 環(huán)境，它可以很方便的幫你啟動數(shù)據(jù)庫實例，并且運行 migration，甚至你可以將示例數(shù)據(jù)導入其中，而且也不會讓你的筆記本電腦裝滿各種各樣的 daemon。最重要的是，它幾乎可以幫你找到任何版本的數(shù)據(jù)庫，特別是 MySQL 與 PostgreSQL。

2. 所修改的數(shù)據(jù)結(jié)構，與現(xiàn)在運行的版本是否兼容？
   這就是我們鼓勵 dev 進行測試的另一個原因，往往開發(fā)人員很清楚的知道自己修改了哪些地方，那么他就會知道需要測試哪些可變的部分。如果你的交付單位是一天一次或者幾次，你是幾乎沒有可能去進行回歸測試的，所有的測試就是基于可變以及程序設計時的邊界隔離。我們鼓勵小步提交或者小步交付，也就是說，你要很清楚的知道，需要測試的是哪個版本。

3. 演練 migration 失敗
   "任何時候都需要考慮失敗"，這是我的前輩很早以前告訴我的一句話，即使我的工程經(jīng)驗已經(jīng)很豐富了，我也不想在數(shù)據(jù)庫變更時放水。所以，在進行測試的時候最好考慮或者實踐一下那些會導致失敗時的 case，這會讓我們面對異常時不那么慌亂。

運行

我理想中的進行數(shù)據(jù)庫變更是發(fā)生在 CI 中的，而且是在應用部署之前完成，我們可能會有這樣一段 pipeline：

image.png

（僅供舉例，不代表真實情況）

1. 盡量避免手動的去“觸碰”數(shù)據(jù)庫

不論是最小權限原則還是數(shù)據(jù)安全或者是公司的規(guī)則，我一直反對可以直接接觸到生產(chǎn)環(huán)境數(shù)據(jù)的情況，雖然這樣會喪失一定的靈活性，但是數(shù)據(jù)作為最重要的資產(chǎn)，而操作數(shù)據(jù)的只有程序，而不是人。人需要程序去操作數(shù)據(jù)，而為程序的質(zhì)量負責的是小組，組織會阻止部分的人為因素，降低人犯錯的概率。在線上數(shù)據(jù)庫上，這樣的錯誤我認為是很難承受的，例如 Gitlab 這樣的事件。既然進行 migration 都已經(jīng)程序化了，還有什么理由自己去手動的執(zhí)行呢？

跳板機也是比較危險的存在，因為這破壞了生產(chǎn)環(huán)境的邊界，但是本文不講太多安全方面，所以也是不推薦的。

2. 可以在應用程序啟動前去做嗎？

我們在程序啟動時進行 migration 是一個常見的做法，特別是 flyway 或者 Liquibase 都支持這一套做法，畢竟簡單。但是我們還是推薦稍微麻煩一些使用 out-of-process 的方式進行（即使應用和 plan 都是在一個 codebase 中）。好處是：

• 我可以知道 migration 的具體情況，時間，完成后的輸出是否符合預期等，往往這些信息存在在 log 中，和應用程序并沒有關系。
• 第二，如果 migration 需要的時間很長，我們就不需要部署應用程序，不會出現(xiàn)程序啟動了三十分鐘 migration 還沒做完的情況。
• 對于微小的改動，我們改了數(shù)據(jù)庫又加了一些代碼，我們可以做一個 pull request 并進行一次部署，這樣的 pipeline 可以做到先 migration 再部署代碼。
• 還有就是可以動態(tài)的調(diào)整 pipeline，如果這次的代碼修改只是 migration，那我自然就不會部署應用了。
• 最后，如果部署應用程序是啟動多個實例或者容器的話（這太常見了），那么請問哪個實例或者容器上的進程是在進行 migration 呢？那其他的進程是直接工作呢還是大家一起等？這種復雜度不是我們愿意面對的。