时间戳

首页 > 解决方案 > 如何加速 mariadb 连接表

sql - 如何加速 mariadb 连接表

问题描述

我有 2 个表，我从中加入某些列。它们在 VARCHAR 列上连接（在两个表中都有索引）。表 A 有超过 800.000 条记录，表 B 有 20.000 条记录。表 A 有一个 auto_inc 主键。表 B 没有主键，只有提到的 VARCHAR 列上的索引。

查询大约需要 48 秒，这太慢了。我该怎么做才能提高速度？在表 B 中创建主键 auto_incr 会有所帮助吗？即使这不是连接发生的列？

SQL 中的初级用户。这两个表都是 InnoDB，我使用 Mariadb。

询问：

select distinct 
    `pr`.`ProductIdentifier` AS `ProductIdentifier`,
    `pr`.`Datum` AS `Datum`,
    `pr`.`Retailer` AS `Retailer`,
    `pr`.`Prijs` AS `Prijs`,
    `pm`.`Merk` AS `Merk`,
    `pm`.`Product` AS `Product`,
    `pm`.`Formaat` AS `Formaat` 
from 
   (`prices`.`prices_table` `pr` 
    join `prices`.`product_match_table` `pm` 
        on(`pr`.`ProductIdentifier` = `pm`.`ProductIdentifier`))

解释选择：

解释表

标签： sqlindexingmariadb

解决方案

这个答案是基于我对索引的一般知识；MariaDB 可能有一些我不知道的更专业的选项。

然而，索引以两种方式广泛地加速查询

  • 通过只拥有所需的列，这意味着要读取和处理的数据更少
  • 通过以适当的方式进行排序以帮助处理

首先，您通常需要一个覆盖索引。

第二，这包括

  • 与在查询中加入的表的排序方式相同（例如，在相同的字段上建立索引）
  • 被排序以便 WHERE 子句和其他类型的过滤可以直接使用排序转到索引/表中的适当位置

在实践中，性能的最佳改进通常是最后一个 - 但是您的代码中没有 WHERE 子句。如果（通常）用户过滤结果（例如，只显示 ProductName = 'Handbag' 的结果），那么您可能需要调整这些索引（稍后会详细介绍）。

覆盖上面查询的索引

我认为使用当前查询（并且没有过滤等），您可以获得的最快速度是使用两个索引

CREATE INDEX `IX_prices_ProductIdentifier` ON `prices`.`prices_table` 
    (`ProductIdentifier`,
    `Datum`,
    `Retailer`,
    `Prijs`);
CREATE INDEX `IX_productmatch_ProductIdentifier` ON `prices`.`product_match_table`
    (`ProductIdentifier`,
    `Merk`,
    `Product`,
    `Formaat`);

它们为所示查询提供覆盖索引，并且都以相同的方式排序（按 productIdentifier）以使连接更容易。

搜索/过滤（初始示例中未指定）

但是，如果人们经常先按特定字段进行搜索，那么对相关表中的字段进行重新排序（因此搜索的字段在前）是有意义的，或者有多个索引，搜索字段在前面。

例如，人们可能能够在pr. Retailer, pm. Merk, 或pm. Product. 因此，您可以添加这些附加索引

CREATE INDEX `IX_prices_Retailer` ON `prices`.`prices_table` 
    (`Retailer`,
    `ProductIdentifier`,
    `Datum`,
    `Prijs`);
CREATE INDEX `IX_productmatch_Merk` ON `prices`.`product_match_table`
    (`Merk`,
    `ProductIdentifier`, 
    `Product`,
    `Formaat`); 
CREATE INDEX `IX_productmatch_Product` ON `prices`.`product_match_table`
    (`Product`,
    `ProductIdentifier`, 
    `Merk`,
    `Formaat`);

请注意，字段顺序很重要。数据（索引）按第一个字段排序，然后是第二个字段，然后是第三个字段等。为了有效地使用索引，您的过滤/WHERE 子句需要至少包含第一个字段，如果不是更多的话。

这些索引（用于过滤的索引）的替代方法是使用上面的原始两个索引，然后在他们可以搜索的每个字段上放置一个单独的索引，例如，如果用户可以过滤零售商、merk 和产品, 然后创建

  • 上的一个索引pr。Retailer
  • 一上pm。Merk， 和
  • 一上pm。Product

注意事项

添加索引会使数据插入到相关表中（并且经常删除/更新），比没有索引时要慢。原因是它不仅需要更新表中的数据，还需要更新索引。

通常这不是什么大问题，除非您经常从表中添加和删除大量数据。但值得在添加索引后检查您的“产品维护”界面（例如，添加产品、更新价格等）以确认它们仍然运行良好。

推荐阅读