Sargable 查询和 MUL 索引；或者为什么我的查询很慢

你在运营。像往常一样在 Invoicing Rails 应用程序中调试 Error-s。它们存储在 MySQL 中并通过 ActiveRecord 和一些辅助方法访问。他们已经用 status_message 更新了，所以现在是深入研究的时候了。在另一个创纪录的热浪下午，阳光透过你的窗户照射进来，但你却像黄瓜一样凉爽：你带着太阳镜和一大杯可靠的 H2O。又过了 23.7 秒，您决定在导致生产事故之前按 CTRL-C 离开那里。您决定像普通人一样从 JIRA 票证中提取错误 ID。突然间，热浪感觉更热了。但这是在唠叨你。我的意思是，什么给？为什么这种方法不只是做它在罐头上说的事情？当然，如果我们使用 .no_status 范围（WHERE status_message IS NOT NULL）它似乎可以工作，但是为什么我们不能在此列上使用简单的文本搜索？当然有很多（很多 = 8612441）行，但我觉得我以前见过文本搜索工作。我们应该切换到 Postgres 吗？ Sargable 是一个 gate keep-y portmanteau（<- 也是 gate keep-y。一个 portmanteau 是一个词，它把两个词的声音和意义混在一起，比如勺子 + 叉子 = spork），用于“搜索参数”。我们使用该术语来描述数据库的 SQL 优化器利用索引的能力。有几件事情决定了查询的可搜索性（不确定这是否是一个词），但是在进行文本搜索时需要注意的一个重要因素是使用通配符 (%)。让我们看看 where_message_like 方法的实现，看看我们是否会遇到这样的事情。 irb ( main ): 001 : 0 > ActiveRecord :: Base 。联系 。 to_sql ( Error . where_message_like ( "NoMethodError: undefined method `is_approved_by_finance? \\' for nil:NilClass")) => "SELECT `errors`.* FROM `errors` WHERE（消息像'%NoMethodError: undefined method `is_approved_by_finance? \' 为 nil:NilClass%')"

啊哈，所以我们在 WHERE 子句中字符串的两端都有一个通配符 (%)：要理解为什么这是一个问题，让我们假设您的 SQL 查询优化器。假设我要求您在字典中查找包含子字符串 get 的所有单词：您必须扫描字典（条目表）中的每个单词（行）以查找所有实例：如果，而不是，我让你把字典里所有以get开头的词都弄出来，你可以翻到正确的页面，把所有的词都给我！这样，按字母顺序排列的字典是一种索引：“但是，托马斯”，我听到你问，“如果我们想进行子字符串搜索而不是从字符串的开头搜索怎么办？”这是一个很好的问题！对于我们的错误场景，这种情况很少发生，但是我们可以使用更多的有意索引来获得更强大的子字符串搜索，例如 MySQL 的 FULLTEXT 或 Postgres 的 ts_vector。值得注意的是，全文索引并不神奇，您的表和查询必须更改以支持它们。对于我们的示例，我们没有随时可用的这些东西，因此我们将继续假设我们正在搜索列内容开头的子字符串。

现在，让我们通过查看查询计划来看看这个可交易的业务的实际运行情况！ irb ( main ): 002 : 0 > puts ActiveRecord :: Base 。联系 。解释（错误。where_message_like（“NoMethodError：未定义的方法`is_approved_by_finance？\\'为nil：NilClass”）。to_sql）解释（0.5毫秒）解释选择`错误`。 * FROM `errors` WHERE（消息如 '%NoMethodError: undefined method `is_approved_by_finance?\' for nil:NilClass%' ）+----+-------------+--- -------------+------+---------------+------+------ ---+------+---------+------------+ |身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ | 1 |简单 |错误 |所有 |空 |空 |空 |空 | 6739497 |使用 where | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ set (0.00 sec ) irb ( main ) 中的 1 行：003 : 0 > puts ActiveRecord :: Base 。联系 。解释（错误。其中（“消息 LIKE \' NoMethodError：未定义的方法`is_approved_by_finance？\\' for nil:NilClass% \'”）。to_sql）解释（0.5 毫秒）解释选择`错误`。 * FROM `errors` WHERE ( message LIKE 'NoMethodError: undefined method `is_approved_by_finance?\' for nil:NilClass%' ) +----+-------------+---- ------------+------+--------------+------+------- --+------+---------+-------------+ |身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ | 1 |简单 |错误 |所有 |空 |空 |空 |空 | 6739497 |使用 where | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ 1 行（0.00 秒）哦，废话。是什么赋予了？！它们看起来完全一样！为什么 MySQL 没有优化！我们警告（？）它！不幸的是，我们的消息没有索引。此外，该列是一个 TEXT 列，如果我们想添加一个索引，MySQL 要求我们指定一个前缀长度，它告诉 MySQL 应该索引字符串开头的多少（考虑到它的长度变化很大且未定义） .更进一步，只有 InnoDB 表可以通过这种方式建立索引。 （我们的错误表使用 InnoDB 引擎，所以没问题）。即使我们的 message TEXT 列没有索引，我们的 ticket_id 列有： irb ( main ): 004 : 0 > puts ActiveRecord :: Base 。联系 。解释（'错误'）解释（2.6毫秒）解释错误+------+-------------- +------+-----+---------+----------------+ |领域 |类型 |空 |钥匙 |默认 |额外 | +---------------------+--------------+------+----- +---------+----------------+ |身份证 |整数 ( 11 ) |否 | PRI |空 |自动增量| |留言 |文字 |是 | |空 | | |回溯|文字 |是 | |空 | | | status_message | varchar ( 255 ) |是 |多|空 | | | created_at |日期时间 |是 | |空 | | |更新时间 |日期时间 |是 | |空 | | |运行时环境 |文字 |是 | |空 | | |错误类型 | varchar ( 255 ) |是 |多|空 | | |票号| varchar ( 255 ) |是 |多|空 | | +---------------------+--------------+------+----- +---------+----------------+ 9 行（0.00 秒）

ticket_id 列的类型是 varchar(255)，这意味着我们可以在那里放置索引没有问题。而这正是我们所做的！在我们的例子中，它使用 MUL 或“Multiple”键，这意味着它的值用在非唯一键的开头（多个记录可以具有相同的索引）。让我们看看我们对 sargable 通配符查询的了解如何在这种类型的列中发挥作用。 irb ( main ): 005 : 0 > puts ActiveRecord :: Base 。联系 。解释（错误。where（'ticket_id LIKE \'%A00057440\''）。to_sql）解释（0.5毫秒）解释选择`错误`。 * FROM `errors` WHERE (ticket_id LIKE '%A00057440%') +----+-------------+--------------- -+------+---------------+------+-------+------+- --------+------------+ |身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ | 1 |简单 |错误 |所有 |空 |空 |空 |空 | 8612441 |使用 where | +----+-------------+----------------+------+------ ---------+------+---------+------+-------+------ -------+ set (0.00 sec ) irb ( main ) 中的 1 行：006 : 0 > puts ActiveRecord :: Base 。联系 。解释（错误。where（'ticket_id LIKE \'%A00057440\''）。to_sql）解释（1.2毫秒）解释选择`错误`。 * FROM `errors` WHERE (ticket_id LIKE 'A00057440%') +----+-------------+---------------- +-------+----------------------------------------------+-- -------------------------+------------+--- ---+------+-----------------------+ |身份证 |选择类型 |表|类型 |可能的密钥|关键|密钥长度 |参考 |行 |额外 | +----+-------------+----------------+-------+----- ---------------------------------+---------------- --------------+--------------+------+------+--- --------------------+ | 1 |简单 |错误 |范围| index_errors_on_ticket_id | index_errors_on_ticket_id |第768话空 |第214话使用索引条件 | +----+-------------+----------------+-------+----- ---------------------------------+---------------- --------------+--------------+------+------+--- --------------------+ 1 行（0.00 秒）啊哈！在第一个查询计划中，MySQL 告诉我们它将使用 where 扫描所有 8612441 行。第二个 sargable 查询计划告诉我们它只需要通过使用索引条件扫描 214 的范围！我将把它留给读者练习 SET profiling = 1;看看真正的成本。参见：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html 啊，太阳终于要落山了。我们还没有完成（或什至开始）我们的调查，但我们对 MySQL 的索引模式有点了解。

我们如何利用这些知识让我们的工作更轻松一些并战胜高温？我想首先要知道的是，如果没有其他 WHERE 条件， where_message_like 可能需要很长时间。这是因为它是一个没有索引的 TEXT 列，但即使它有索引，在我们的搜索字符串前面使用通配符也无济于事。此外，很高兴知道ticket_id 列已编入索引，如果我们想搜索，我们可以使用 sargable 查询来更快地获得结果！可能需要考虑为 ticket_id 使用一致的格式；如果我们能推断出字符串的开头是什么，那么查找所有相关记录可能会非常高效！最后，如果我们确实想要索引消息列，我们有几个选择： 我们可以将它从 TEXT 迁移到 VARCHAR。这样做的好处是可以访问一个简单的索引，但我们首先放弃了选择 TEXT 的好处。我们可以通过指定前缀限制来使用列前缀键。如果我们使用 sargable 查询，这可能会给我们带来一些好处。或者，我们可以投资全文索引并真正利用 InnoDB 的功能！但是，这将需要我们稍微改变我们的表并调整我们的查询模式。这是否适合 Rails 是我们需要回答的另一个问题。

尽管如此，现在是下午 5:00（下午 4:56，但谁在计算），所以是时候收工了，在水边捕捉最后几缕阳光。在你用 ⌘-Q 退出 Slack 之前，你会记住一些东西。 “……布丽安娜不是说他们要研究这个问题吗？？”