簡介

一個有趣的面試問題，我已經聽到并問過很多次了。

"你將如何提高數據庫的性能？"

我喜歡這個問題，因為，就像我討論過，它從更廣泛的角度來衡量候選人的技能。云計算架構師會考慮數據庫的架構，考慮讀取復制和分片，后端或全棧工程師可能會考慮應用層面的變化，如優化的SQL查詢、連接池，而數據庫人員可能會考慮數據庫的配置、插件等。

這個問題可能有很多答案，因為我想深入了解每個答案，所以我將分別寫三篇文章，每篇都針對某一類答案。

第一篇將更多地討論應用層面和SQL的變化。這些可能是我對直接編寫代碼的開發人員的期望（例如，編寫與數據庫服務器互動的NodeJS、Python應用程序的開發人員）。

第二種是要更注重架構層面的變化，管理服務等。他們會更關注云計算架構師或對系統設計概念有良好了解的人。

第三組答案將更注重于數據庫和操作系統的配置。

請記住，這是一個非常廣泛的話題，這是我對如何回答這個問題的看法，我將提供進一步閱讀的鏈接，并盡可能多地提供實際的例子。

我也在使用軟件工程的stackexchange數據集作為我的例子，你可以找到在使用Pandas將數據加載到Postgres之前，我還對數據進行了一些轉換和調整，如果你有興趣了解更多，請告訴我，我可以分享jupyter-notebook。這些數據相當容易理解，我的大多數例子應該僅限于Posts表，它簡單地定義了StackOverflow上發布的問題，屬性包括標題、正文、創建日期等。

問題

問題是，"我的數據庫越來越慢，你會如何提高它的性能？". 在這篇文章中，我假設是一個SQL數據庫，特別是Postgres。

把這個問題看成是一個兩部分的問題，盡管它沒有明確這樣說。第一部分是 "為什么"，第二部分是如何解決。為了理解 "為什么"，你需要對問題進行調試，一旦你知道了數據庫性能緩慢的原因，你就可以提出一個可能的解決方案。

為了便于閱讀，我從可能的答案開始，涵蓋與每個答案相關的權衡，然后我將解決你需要提出的反面問題，以調試問題的原因。

在你向下滾動之前，想一想你會如何回答這個問題，如果你發現我的文章中沒有包括的內容，請在評論中告訴我。

可能的答案

請記住，每一個答案都是有取舍的。

索引

如果你的SELECT查詢變得非常慢，因為你在檢查某個條件，索引可以提供一種方法來改善你的數據庫讀取性能。

你可以在一組特定的列上創建一個索引，數據庫將創建一個數據結構來保存數據庫的這些列，這樣你就可以得到這些列的快速查詢。

例如，如果我想獲取用戶user8創建的帖子，我可以運行以下查詢

SELECT * FROM posts WHERE owner_display_name='user8';

然而，這迫使數據庫掃描表中的所有行，以找到owner_display_name為 "user8 "的帖子。這種類型的掃描被稱為順序掃描，因為數據庫正在掃描整個表。正如名字所暗示的，這種類型的查找不是最優化的解決方案。

運行上面的查詢，大約需要150ms的時間來執行。這個查詢的查詢計劃（數據庫對如何為你的查詢讀取表所做的規劃）也表明，它將對這個查詢進行順序掃描

要得到同樣的結果，一個更好的方法是在列owner_display_name上創建一個索引。這將創建一個單獨的數據結構，數據庫可以使用這個新的數據結構快速查詢該列的特定值。

創建一個索引很簡單。

CREATE INDEX posts_owner_display_name_idx ON posts (owner_display_name)。

一旦我們創建了索引，我們就不需要做任何其他事情來使用它。數據庫足夠聰明，知道什么時候使用索引，什么時候不使用。運行同樣的查詢。

SELECT * FROM posts WHERE owner_display_name='user8';

我們現在得到了完全不同的結果。現在查詢在2ms左右就完成了！這比沒有索引時快了50倍。這比沒有索引時快了50倍!

查詢計劃還指出，數據庫現在正在使用我們所創建的索引

雖然沒有什么是免費的，索引也有一定的成本。

由于我們現在正在創建一個不同的數據結構來存儲你表中的相同數據，所以你的表的每一行都需要更多的存儲空間。除此之外，現在數據庫必須確保每當你向表中添加新的行時，索引都是最新的，所以在編寫新的行時，它也為數據庫增加了更多的工作。

索引可以是修復數據庫讀取性能的一個好方法，但可能會減慢寫入性能。

索引本身就是一個復雜的話題，網上有很多資源可以更深入地討論其性能影響。 如果你想更多地了解與索引相關的權衡，那么 "索引 "是一個很好的起點。 如果你想了解更多關于索引的信息，視頻也更詳細地解釋了索引。

創建物化視圖

你可以在數據庫中創建兩種類型的視圖，簡單視圖和物化視圖。

簡單視圖作為查詢的別名，而物化視圖則存儲查詢的結果。這意味著，你可以預先計算查詢，將結果存儲在物化視圖中，并在用戶實際要求時快速向用戶顯示結果，而不是在用戶請求時運行查詢。

讓我們用一個例子來討論這個問題。假設我想根據帖子的瀏覽量來了解帖子的類別 --

做到這一點的一個方法是運行以下查詢

SELECT
 CASE
  WHEN view_count < 10 THEN 1
  WHEN view_count < 100 THEN 2
  WHEN view_count < 1000 THEN 3
  WHEN view_count < 10000 THEN 4
  WHEN view_count < 100000 THEN 5
  ELSE 6
 END as view_count_category_index,
 CASE
  WHEN view_count < 10 THEN '0-10'
  WHEN view_count < 100 THEN '10-100'
  WHEN view_count < 1000 THEN '100-1000'
  WHEN view_count < 10000 THEN '1000-10000'
  WHEN view_count < 100000 THEN '10000-100000'
  ELSE '100000+'
 END as view_count_category,
 COUNT(*)
FROM
 posts
GROUP BY view_count_category, view_count_category_index
ORDER BY view_count_category_index ASC;

執行時間: 257.556 ms

250毫秒比我想要的要高一點。我希望它能低得多，比如說<50ms。創建一個視圖非常簡單，我只需要運行CREATE VIEW [VIEWNAME] AS [QUERY]。讓我們創建一個簡單的（非化的）視圖。

CREATE VIEW non_mat_view_count_view AS
SELECT
 CASE
  WHEN view_count < 10 THEN 1
  WHEN view_count < 100 THEN 2
  WHEN view_count < 1000 THEN 3
  WHEN view_count < 10000 THEN 4
  WHEN view_count < 100000 THEN 5
  ELSE 6
 END as view_count_category_index,
 CASE
  WHEN view_count < 10 THEN '0-10'
  WHEN view_count < 100 THEN '10-100'
  WHEN view_count < 1000 THEN '100-1000'
  WHEN view_count < 10000 THEN '1000-10000'
  WHEN view_count < 100000 THEN '10000-100000'
  ELSE '100000+'
 END as view_count_category,
 COUNT(*)
FROM
 posts
GROUP BY view_count_category, view_count_category_index
ORDER BY view_count_category_index ASC;

然后嘗試從視圖中獲取結果。

SELECT * FROM non_mat_view_count_view;

它確實給了我正確的輸出。但是當用EXPLAIN ANALYZE運行時，我得到的執行時間仍然非常相似，為250-260ms。發生這種情況的原因是，簡單的視圖只是存儲了查詢，當我們試圖使用該視圖時，會重新執行該查詢。

然而，創建一個物化視圖是不同的。在這里，當我們創建物化視圖時，它實際上會存儲查詢的結果，當用戶請求這些數據時，它不需要計算結果，由于它只需要獲取結果，所以它可以比執行查詢或簡單視圖快很多。

創建物化視圖與創建簡單視圖非常相似，只是我們使用CREATE MATERIALIZED VIEW而不是CREATE VIEW。

CREATE MATERIALIZED VIEW mat_view_count_view AS
SELECT
 CASE
  WHEN view_count < 10 THEN 1
  WHEN view_count < 100 THEN 2
  WHEN view_count < 1000 THEN 3
  WHEN view_count < 10000 THEN 4
  WHEN view_count < 100000 THEN 5
  ELSE 6
 END as view_count_category_index,
 CASE
  WHEN view_count < 10 THEN '0-10'
  WHEN view_count < 100 THEN '10-100'
  WHEN view_count < 1000 THEN '100-1000'
  WHEN view_count < 10000 THEN '1000-10000'
  WHEN view_count < 100000 THEN '10000-100000'
  ELSE '100000+'
 END as view_count_category,
 COUNT(*)
FROM
 posts
GROUP BY view_count_category, view_count_category_index
ORDER BY view_count_category_index ASC;

當我們運行EXPLAIN ANALYZE SELECT * FROM mat_view_count_view；我們得到的執行時間是0.027ms。這比執行查詢或使用我們以前的簡單視圖要快得多!事實上，這是12000倍的速度!

然而，這也是有代價的。由于結果是預先計算的，對表的任何更新都不會自動更新結果。你需要手動運行REFRESH MATERIALIZED VIEW mat_view_count_view來更新視圖。如果你想實現自動化，有很多方法可以做到這一點，正如在《MATERIALIZED VIEW》中解釋的那樣。 但所有這些方法都有一些缺點。缺點包括數據不一致（例如，用戶獲取過時的視圖計數類別數據），以及數據庫服務器的性能問題，因為更新視圖意味著再次計算這個查詢，等等。

因此，雖然物化視圖是提高讀取性能的好方法，但在向數據庫寫入時可能會導致性能問題或一致性問題。當數據的更新頻率較低，并且可以容忍輕微的數據不一致或不準確時，這種解決方案可能是有意義的。

應用層面上的連接池

在我談論連接池之前，讓我解釋一下什么是連接。

要連接到一個數據庫，你的應用程序需要建立一個數據庫連接。把這個連接看成是一個假想的管道，請求和響應將通過這個管道流動。

你需要數據庫主機名、數據庫名、憑證等來建立一個新的連接，這個連接確實有一些數據與之相關。我認為維基百科上關于連接的文章把它說得非常好。

"數據庫連接是有限的和昂貴的，相對于在其上進行的操作來說，創建的時間可能長得不成比例。當一個應用程序需要更新數據庫時，創建、使用和關閉數據庫連接的效率很低。"

一旦你有一個連接，你就可以開始向數據庫發送請求。大多數數據庫只允許你在每個連接上一次執行一個操作。這意味著，如果一個事務的執行需要100毫秒，那么每個連接只能實現每秒10個事務（或10TPS）。因此，使用單一連接限制了你可以運行的事務數量。

因此，讓我們回顧一下。我們知道打開和關閉連接是昂貴的，我們也知道我們不能使用單一的連接，因為那會使我們的系統陷入瓶頸。

那么，解決方案是什么呢？

好吧，我們可以維護一些連接并重復使用它們。這就是所謂的連接池。想象一下一個連接池，如果這能讓人更容易記住的話。

幸運的是，大多數客戶端庫都具備相當好的連接池功能，我們可以在代碼中快速編寫。

事實上，它是如此廣泛，以至于pg庫文檔中的例子，這是一個非常流行的用于postgres的nodejs庫，它同時具有連接到數據庫的連接池方法和直接客戶端方法

const { Pool, Client } = require('pg')

// pools will use environment variables
// for connection information
const pool = new Pool()
pool.query('SELECT NOW()', (err, res) => {
  console.log(err, res)
  pool.end()
})

// you can also use async/await
const res = await pool.query('SELECT NOW()')
await pool.end()

// clients will also use environment variables
// for connection information
const client = new Client()
await client.connect()

const res = await client.query('SELECT NOW()')
await client.end()

還有一些重要的配置，我們可以在數據庫服務器上做連接池，這可能會影響性能，但由于我想在另一篇文章中介紹數據庫服務器和操作系統的配置，我現在先不談這個。

說到權衡，我不認為連接池有什么大的權衡，至少我沒有遇到過，也沒有讀到過。如果你碰巧知道，請留言幫助我，也幫助其他會讀這篇文章的人。

最后。 這是一個了不起的起點，如果你想了解更多關于連接池的信息。

應用層面的緩存

對于很多應用來說，大多數的讀取只針對少量的數據。考慮一下Twitter的情況。大多數被瀏覽的推文可能是重要和受歡迎的人，如政治家、名人等。類似的趨勢也可能存在于許多流行的閱讀量大的網站上。

事實上，讓我們看看到目前為止我們一直在使用的數據。請記住，這是來自softwareengineering.stackexchange.com的真實流量數據。

為了分析這些數據，我按瀏覽量對數據進行排序，然后按十分位數進行分組。簡而言之，下圖顯示了哪一個十分位數獲得了多少百分比的總瀏覽量。

數據顯示，前10%（第一個十分位數）的帖子占了約95%的瀏覽量，接下來的10%（第二個十分位數）占了約4.8%的瀏覽量。

SELECT
 (SUM(view_count) * 100.0) / (SELECT SUM(view_count) FROM actual_posts) as percentage,
    SUM(view_count),
    decile
FROM (
    SELECT
        post_id,
        view_count,
        ntile(10) over (order by view_count DESC) as decile
    FROM actual_posts
) sum_data
GROUP BY decile
ORDER BY decile

當按百分位數而不是十位數計算時，瀏覽量的差異更加明顯，前1%的帖子占了50%以上的瀏覽量。

這意味著，通過找到一種方法來服務前1%的帖子，你可以加快你得到的50%的請求，或者找到一種方法來服務前10%的帖子，你可以加快你得到的95%的請求!由于這為其他請求釋放了你的服務器，其他請求也可以使用更多的資源，因此速度會更快！"。

因此，讓我們來看看問題的陳述，我們需要找到一種方法來存儲相對較少的數據，但能夠非常快速地獲取它。另一方面，我們仍然需要存儲其余的數據，但我們不需要超快的檢索。

實現這一目標的一個好方法是將經常被獲取的少量數據存儲在RAM中，而將大量數據存儲在SSD中。這樣，你就可以非常迅速地滿足大部分的請求，并在需要的時候偶爾打到SSD上。這就是所謂的高速緩存。

有很多方法可以實現這一點，但由于我在這篇文章中討論的是應用層面的變化，所以一個簡單的實現方法是在你的應用中添加一個簡單的哈希圖。請注意，這絕對不是最好的方法，還有其他更好的緩存方案，但我將在后面介紹更復雜的方案。

這個想法很簡單，為你經常收到的請求在哈希姆中建立一個小的緩沖區。當用戶請求一個帖子時，檢查它是否存在于你的緩存中，如果存在，那么就把帖子發給用戶，如果不存在，那么你就可以沖擊你的數據庫，把數據存儲在SSD中。對我們來說，幸運的是，已經有很多庫實現了這一點。

不過這也有很大的缺點。

一個大的問題是與數據的一致性有關。如果你在RAM上存儲了少量的數據，你需要確保在數據庫中的數據被更新時更新這些數據，這樣用戶就不會收到過時的數據。這就打開了一個難題，因為現在你需要決定你更新數據的頻率，是每分鐘、每秒鐘、每一次交易等等。

這個解決方案可能會出現的另一個問題（盡管我們在未來的文章中可能會討論的其他緩存解決方案不會出現這個問題）是使你的服務器更加復雜和有狀態。終止你的服務器將意味著失去這些存儲在RAM中的數據，而啟動新的服務器將意味著從數據庫中快速獲取大量的數據（這被稱為 thundering herd problem).當一個新的服務器突然出現時，這種大量的取數會使數據庫在短時間內變慢。

別擔心，這些問題的解決方案是存在的，在很多情況下，緩存是一個非常好的通用解決方案，特別是當數據一致性不是問題的時候。

編寫優化的SQL查詢

在應用層面，提高性能的一個好方法是編寫優化的SQL查詢。即使性能不是一個問題，為了未來的可擴展性，編寫優化的SQL查詢仍然是更好的做法。

有相當多的方法可以讓你寫出優化的SQL查詢，例如，試圖避免OFFSET和找到更好的方法來實現分頁，或者避免SELECT * ，等等。網上有很多很好的資源，有助于幫助你理解優化的SQL查詢。

另一個好的組織技術可以是編寫非物化視圖，并在應用層面的SQL中使用它們來代替長的SQL語句。

對于那些不知道什么是非物化視圖的人來說，可以把它看作是一種查詢的別名。例如，我們假設我有一個名為post的表。這個表有以下的模式---

在大多數情況下，我們將考慮到以下欄目

比方說，我想寫一個SQL查詢，用于根據帖子的瀏覽量進行分類，例如，如果我想要這樣的輸出

這方面的查詢是。

SELECT
 CASE
  WHEN view_count < 10 THEN 1
  WHEN view_count < 100 THEN 2
  WHEN view_count < 1000 THEN 3
  WHEN view_count < 10000 THEN 4
  WHEN view_count < 100000 THEN 5
  ELSE 6
 END as view_count_category_index,
 CASE
  WHEN view_count < 10 THEN '0-10'
  WHEN view_count < 100 THEN '10-100'
  WHEN view_count < 1000 THEN '100-1000'
  WHEN view_count < 10000 THEN '1000-10000'
  WHEN view_count < 100000 THEN '10000-100000'
  ELSE '100000+'
 END as view_count_category,
 COUNT(*)
FROM
 posts
GROUP BY view_count_category, view_count_category_index
ORDER BY view_count_category_index ASC;

這有點復雜。我可以創建一個視圖，而不是在我的代碼中寫這個。??

CREATE VIEW view_count_category_view AS
SELECT
 CASE
  WHEN view_count < 10 THEN 1
  WHEN view_count < 100 THEN 2
  WHEN view_count < 1000 THEN 3
  WHEN view_count < 10000 THEN 4
  WHEN view_count < 100000 THEN 5
  ELSE 6
 END as view_count_category_index,
 CASE
  WHEN view_count < 10 THEN '0-10'
  WHEN view_count < 100 THEN '10-100'
  WHEN view_count < 1000 THEN '100-1000'
  WHEN view_count < 10000 THEN '1000-10000'
  WHEN view_count < 100000 THEN '10000-100000'
  ELSE '100000+'
 END as view_count_category,
 COUNT(*)
FROM
 posts
GROUP BY view_count_category, view_count_category_index
ORDER BY view_count_category_index ASC;

那么我就可以不在代碼中寫這個SQL邏輯，而是簡單地使用

SELECT * FROM view_count_category_view

當我在上面的視圖上執行查詢時，在幕后，數據庫實際上會執行創建視圖的實際查詢。

?這使得我的代碼更加簡潔，而且在將來，我可以更新視圖邏輯，而不需要在應用層面上進行修改和重新部署。還有一種方法可以實現類似的功能，叫做存儲過程

在回答問題前先反問

在回答這個問題之前，你一般應該問幾個反面的問題，以幫助更好地理解這個問題。這些可以幫助你衡量系統中的瓶頸問題。整個系統可能相當復雜，可能有很多原因導致數據庫開始表現不佳。為了更好地了解原因，并更好地了解系統的要求，你可以向面試官提出一些問題，這些問題可以幫助你找出最佳解決方案。

由于這一部分需要對上面的答案有一定的了解，所以我在討論了可能的答案后將其列入，但你在回答之前可能應該提出反問。

是讀取性能慢還是寫入性能慢？

一個非常重要的因素可以推動你的決策，就是有關數據庫的讀寫性能如何。許多改善一個的解決方案可能也會以消極的方式影響另一個。例如，創建物化視圖會改善你的讀取性能，但會在數據庫服務器上增加額外的負載，可能會影響寫入性能。

我們使用的是哪個數據庫？?

另一個重要因素可能是我們正在使用的數據庫。每個數據庫都是為一個特定的使用情況而建立的。使用錯誤的數據庫類型會嚴重影響你的性能。

?例如，當你想進行分析性查詢時，使用Postgres這樣的關系型數據庫通常不是一個好的做法。雖然關系型數據庫可以執行大量的分析功能，但它們在該領域的功能和性能比專門為這些類型的操作建立的數據庫（例如，Cassandra或Redshift）要有限得多。

很多數據庫都是為特定的問題而存在的，一般來說，它們在處理問題陳述時的表現會好很多。有一些數據庫用于搜索（例如ElasticSearch），用于地理空間數據（例如Neo4J），用于時間序列數據（例如Prometheus），用于存儲臨時數據（例如Redis或Memcached），等等。

了解用戶如何使用你的服務

另一個需要了解的重要因素是用戶如何使用你的服務。用戶是全天都在發送請求，還是有特定的高峰時間？?

對不準確或過時的數據的容忍度是多少（例如，用戶一般不會介意一個帖子的喜歡數過時了幾分鐘）？

他們主要是在進行讀取查詢，還是大部分在進行寫入查詢？

他們所存儲的數據有多敏感？你需要考慮的任何監管要求？

?這些問題可以幫助你了解你應該關注什么，是讀取性能還是寫入性能，以及你在ACID屬性方面有多大的靈活性。例如，如果用戶可以接受過時的數據，那么你可以考慮使用刷新頻率相對較低的物化視圖。

時間表

另一個要始終牢記的重要因素是需要多快的解決方案。它是一個緊急問題，數據庫完全無法使用？或者是一個輕微的性能下降，公司希望確保系統的可擴展性。

總結

這是我對如何回答這個問題的看法。我還會談論更多關于架構方面的事情，也會談論一點關于配置方面的事情，但我會在另一篇文章中包括這些。