SQL 查询优化 23 倍！！！

我从事 Web 开发大概三年了，专业工作也一年多了。这是我第一次处理与数据库查询优化相关的问题，虽然我不是 SQL 专家，但我只能勉强应付。没有复杂的查询、触发器、存储过程等等。反正最后一个我得自己去 Google 一下。

长话短说...我们的 ORM（TypeORM）把我们搞得很惨...

免责声明：

这并不是要诋毁 TypeORM 或任何 ORM，它们本身就是非常出色的工具，就其用途而言。最后我附上了一些参考链接，这些链接是一些公开讨论的链接，大家也遇到过类似的问题。总之，我们继续看正文吧。

问题！

我们的提交表包含超过 70 万条记录，性能非常糟糕。从表中获取数据的最长时间就超过了 6 秒。

查询相当简单。我们只需要 4 个连接、几个 where 子句（大约 4 个）、排序（基于 created_at 时间字段的 DESC 排序）、限制和跳过。

根本原因分析..

导致我们的提交表速度大幅下降的几个因素如下：

索引- 用于连接表的列上的索引不正确或没有索引。
不必要的连接- 我们的查询中有一些不必要的连接，可以将其删除以获得更高的性能。
空字符串错误- 我们的代码中存在一个错误，如果没有为这些列提供用户输入，我们将这些列与空字符串（“”）进行比较。
ORM ——ORM 正在运行一个超级愚蠢的查询来获取数据。

这些是我在检查代码和数据库模式以及分析正在运行的查询以获取所需数据时发现的要点。

对提到的每个问题进行分析和测试。

原因 1：索引

在谷歌搜索并阅读了其他人的类似问题后，我发现我们的问题并不大。其他人都在处理数百万行数据，而我们的只是其中很小的一部分，所以肯定是我们哪里做错了。

之前解决这些问题的社区提出了很多建议。我发现，适当的索引编制很有帮助。

因此，为了进行测试，我从包含约100,000 多条记录的 beta 数据库中获取了提交内容。

在没有任何优化的情况下，运行整个过程平均需要2.3 秒。（当然，这个时间不仅包括在数据库上运行查询的时间，还包括通过网络传播数据的时间）

在为列添加索引后，我确实发现时间缩短了几毫秒，但这还不够。时间仍然在2 秒左右，而且通常还会更长。

真是太扫兴了！总之，我们继续讨论下一个话题。

原因 2：空字符串错误

因此我们将时间从2.3秒缩短到大约 2 秒，仅从索引来看这并不多。但后来我发现代码中有一个小错误，假设有四个输入字段供用户输入并根据四个不同的列过滤结果。如果用户没有在任何输入框中输入任何内容（这主要发生在页面首次加载时），API 调用只会获取最新数据，而不进行任何过滤、连接和排序。
所以在那一刻，我们为数据库中的所有列传递了“”字符串，这似乎无害，但实际上发生的情况是，对于所有四列，数据库都在使用你猜对了的“”字符串进行查找。因此，这实际上是无用地进行了大量查找。

因此，当我将其更改为空（例如空/空）（相当于从查询中删除 where 子句）时，查询时间从2.3 秒变为 1.3 秒。

如果你想知道使用用户提供的实际输入进行过滤需要多长时间。大约需要500 毫秒（这还算可以接受）。

结论 - 即使您的数据库已使用所有可搜索的列进行索引，“”字符串也无法很好地播放。

好的，我们正朝着正确的方向前进。我们缩短了整整 1 秒，但仍然需要控制在200/150 毫秒以内，所以还有很长的路要走。

原因 3：不必要的连接

在查询提交内容时，我们正在与竞赛和课程表进行连接，而这些表并非必需。所以我们干脆把它删掉了。我不知道这些内容是什么时候添加到代码中的，但这说明审阅者并没有太注意（我就是其中之一）。

原因 4：ORM

这是造成最严重问题的原因..嗯..问题!!。

因此，TypeORM 提供了一种称为活动记录模式的东西，它为我们的开发人员提供了使用类似 JSON 的对象生成 SQL 查询的功能，一个例子如下。

model.find({
      select: { userName : true, firstName : true },
      where: { userName : “SomeUsername” },
      relations: { user : true, contest: true, problem: true },
      order: { created_at : “ASC/DESC” ,
      skip: 0,
      take: 10,
})

因此，这使得开发变得快速而简单，并且对于不擅长编写原始 SQL 查询的开发人员来说感觉非常直观，因为这是最抽象的版本，您实际上是在创建 JSON 对象来生成 SQL 查询。

这种方法似乎不错，并且大多数情况下都有效，但在我们的例子中，它做了一些非常愚蠢的事情，我不会输入它所做的事情，以便您可以自己查看查询。

简而言之，它运行了两个查询，首先，对于这种情况，根本不需要，只需使用我后来编写并测试的一个简单的单一查询就可以轻松完成。

它不仅运行了两个单独的查询（原因尚不清楚，因为这是一个已知问题，在使用 TypeOrm 的活动记录模式时有时会发生），而且还在两个查询中分别对四个表进行两次连接，然后在两个查询中分别进行两次排序。（这完全说不通）
而这正是性能受到最大影响的地方。请自行查看下面的查询。

SELECT DISTINCT 
`distinctAlias`.`Submission_id` AS `ids_Submission_id`, `distinctAlias`.`Submission_created_at` 
FROM (SELECT `Submission`.`id` AS `Submission_id`, ... more selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN 
`user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId`) `distinctAlias` 
ORDER BY `distinctAlias`.`Submission_created_at` DESC, `Submission_id` 
ASC LIMIT 10

SELECT `Submission`.`id` 
AS `Submission_id`, `Submission`.`language` 
AS `Submission_language`, `Submission`.`verdictCode` 
AS `Submission_verdictCode`, `Submission`.`tokens`
... shit ton of selects 
FROM `submission` `Submission` 
LEFT JOIN `problem` `SubmissionSubmission_problem` 
ON `SubmissionSubmission_problem`.`id`=`Submission`.`problemId`  LEFT JOIN `user` `SubmissionSubmission_user` 
ON `Submission_Submission_user`.`id`=`Submission`.`userId` WHERE `Submission`.`id` 
IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
ORDER BY `Submission`.`created_at` 
DESC

所以这两个查询是问题的主要原因，也是主要原因之一。

因此，我编写了一个简单的原始 SQL 查询来执行与它尝试使用两个单独的查询执行的操作完全相同的操作，查询如下：-

SELECT
  Submission.id,
  Submission.language,
  Submission.verdictCode,
...
FROM
  submission AS Submission
  LEFT JOIN problem ...
  LEFT JOIN user ...
ORDER BY
  Submission.created_at DESC
LIMIT 10

当我们运行此查询时，它仅需100 毫秒即可运行！！！

所以我们现在从1.3秒缩短到100 毫秒，总体上从2.3秒缩短到100 毫秒

性能提高了23 倍以上。

之后我就去睡觉了。还需要再做一些测试，尝试找出任何可能出现的极端情况，并找到编写查询的最佳方法。目前，我正在考虑使用存储库模式或 TypeORM 提供的查询构建器模式。

第二天：
我们又来了……

全文索引

全文索引可以进一步提升从这些索引列中搜索单词和短语的效率，我们也可以尝试一下。（这是我同事 Jay 提出的一个非常好的观点，它进一步提高了性能）。

发现了一些更重要的点。

在 MySQL 中优化LIKE具有唯一索引的列的查询时，可以采用一些策略来提高性能。以下是一些建议：

索引优化：
- 使用全文索引：如果您的查询涉及在列中搜索单词或短语，请考虑使用全文索引而不是常规唯一索引LIKE。全文索引专为基于文本的搜索而设计，可以提供更快、更准确的结果。
- 使用排序规则：确保列的排序规则不区分大小写和重音。这可以通过使用类似utf8_general_ci或的排序规则来实现utf8mb4_general_ci。这样可以更有效地利用索引，因为搜索时将不区分大小写和重音。
查询优化：
- 前缀搜索：如果LIKE查询在末尾使用通配符（例如column LIKE 'prefix%'），索引仍然可以高效使用。但是，如果通配符位于开头（例如column LIKE '%suffix'），则索引将无法使用。在这种情况下，请考虑使用其他技术，例如全文搜索或存储列的反转值，以实现高效的后缀搜索。
- 尽量减少通配符：模式开头 ( '%suffix') 处的通配符可能会显著降低查询速度。如果可能，请尝试构建查询，使通配符仅位于模式结尾 ( 'prefix%')。
- 参数绑定：如果您LIKE在应用程序内部执行查询，请使用参数绑定或预处理语句，而不是直接连接查询字符串。这有助于防止 SQL 注入，并允许数据库更有效地缓存执行计划。
缓存和查询结果：
- 缓存查询结果：如果LIKE查询结果相对静态或不需要实时获取，可以考虑实现 memcached 或 Redis 等缓存机制。缓存可以直接从内存中获取结果，从而显著缩短响应时间。
- 物化视图：如果LIKE查询频繁执行，且列的数据相对静态，请考虑创建一个物化视图来预先计算并存储查询结果LIKE。如果查询物化视图带来的性能提升大于额外的存储和维护需求，则此方法会很有益。

值得注意的是，这些优化策略的有效性可能因您的具体用例而异。

所有测试完成后建议改进点。

修复将空字符串传递到 where/filtering 条件的问题。
对于性能至关重要的读取操作，转而使用查询构建器而不是活动记录模式。
为用于搜索和过滤的列添加索引。此外，为用于搜索的非唯一列添加全文索引。
删除/避免不必要的连接。如果可能，请重构模式以在必要时复制数据。
使用 LIKE 运算符搜索时，请使用“prefix%”模式，而不是我们正在使用的默认模式“%suff+pref%”。使用前缀模式有助于数据库更好地利用索引并提供更好的结果。

通过所有这些，我们成功地将查询时间从7 秒缩短到<=150 毫秒以下，这样做之后感觉很好，因为这是我第一次涉足性能和优化，并寻找从现有资源中榨取更多资源的方法。

特别感谢Mitesh Sir指出潜在原因并在调查期间为我指明正确的方向，也感谢他一遍又一遍地重启测试服务器 😂 因为由于内存限制，在运行测试这么多次之后，数据库会变得非常慢。

如果您想谈论更多与此相关的事情，请关注我的 X，https://twitter.com/RishiNavneet

参考

PS-这些改进很久以前就完成了，我只是懒得发布它😬。

文章来源：https://dev.to/navneet7716/optimizing-sql-queries-h9j