面试经典问题，如何提高数据库的性能？

简介

一个有趣的面试问题，我已经听到并问过很多次了。

"你将如何提高数据库的性能？"

我喜欢这个问题，因为，就像我讨论过，它从更广泛的角度来衡量候选人的技能。云计算架构师会考虑数据库的架构，考虑读取复制和分片，后端或全栈工程师可能会考虑应用层面的变化，如优化的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属性方面有多大的灵活性。例如，如果用户可以接受过时的数据，那么你可以考虑使用刷新频率相对较低的物化视图。

时间表

另一个要始终牢记的重要因素是需要多快的解决方案。它是一个紧急问题，数据库完全无法使用？或者是一个轻微的性能下降，公司希望确保系统的可扩展性。

总结

这是我对如何回答这个问题的看法。我还会谈论更多关于架构方面的事情，也会谈论一点关于配置方面的事情，但我会在另一篇文章中包括这些。