MySQL 与 PostgreSQL 性能与复杂查询匹配模式的比较

Question

我有一个复杂的数据库，大约有 30 个表。一个表有超过 500,000 行，另一个表有超过 15,000 行，我在一个单独的数据库中使用这两个表，直到今天我决定只在一个数据库中实现。

在今天之前，500,000 行的表位于 MySQL 数据库中，而 15,000 行的表位于 PostgreSQL 中。在一个大量使用的页面中，这是 PHP 基准测试的结果：

getSimilarAvaiable - 0.0287 s
getUnavaiable - 0.27 s
ProcessDataOfUnavaiable - 1.4701 s
Process - 1.8622 s
TotalPageTime - 3.631 s

在我将所有内容迁移到 PostgreSQL 并使用相同的 SQL 代码而不进行任何更改后，同一页面的结果是这样的：

getSimilarAvaiable - 2.7465 s
getUnavaiableCars - 9.0763 s
ProcesseDataOfUnavaiableCars - 1.4167 s
ProcessCars - 1.7207 s
TotalPageTime - 14.9602 s

我把MySQL 中的一切都是一样的，相同的索引，一切，但我不明白为什么会有如此巨大的差异。我应该做什么来优化这个？

编辑：现在更好地解释了。

500.00 表由以下结构组成：

id - bigint (primary key)
plate- varchar(10) Unique key
manufacturer - varchar(30)
vin - varchar(30)

主要查询是这样的：

SELECT plate, vin, 1 as n, substr(plate,1,2) as l 
FROM imtt_vin WHERE substr(plate,1,1) >= 'A' and substr(plate,1,1) <= 'Z' AND
(manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
UNION
SELECT plate, vin, 3 as n, substr(plate,4,2) as l 
FROM imtt_vin WHERE substr(plate,4,1) >= 'A' and substr(plate,4,1) <= 'Z' AND
(manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
UNION
SELECT plate, vin, 2 as n, substr(plate,7,2) as l 
FROM imtt_vin WHERE substr(plate,7,1) >= 'A' and substr(plate,7,1) <= 'Z' AND 
(manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?
ORDER BY n, l, plate;

EDIT2：使用复杂的单个查询进行测试，我将其从 15 秒减少到 8/9 秒。即便如此，这对我来说还是太多了。

Answer 1

您需要发布 EXPLAIN yourquery (for mysql) 和 EXPLAIN ANALYZE yourquery (for postgres) ；如果没有这一点，就不可能说出任何相关的话。

另外 SELECT pg_relation_size('imtt_vin')

例如“?”的值是多少？在这个查询中？

SELECT plate, vin, 1 as n, substr(plate,1,2) as l 
FROM imtt_vin WHERE substr(plate,1,1) >= 'A' and substr(plate,1,1) <= 'Z' AND
(manufacturer ILIKE '%".self::$Manufacturer."%') AND vin LIKE ?

我不知道你工作的地方的牌照，但这部分：

WHERE substr(plate,1,1) >= 'A' and substr(plate,1,1) <= 'Z'

可能会选择数据库中的所有行，因此它的唯一目的是燃烧 CPU 周期。您至少可以像这样重写它（以及所有其他），以避免调用 substr() ：

WHERE substr(plate,1,1) BETWEEN 'A' AND 'Z'

当然，当条件没有用时删除它。

然后我们有：

manufacturer ILIKE '%".self::$Manufacturer."%'

糟糕的数据库设计：世界上有 500,000 个汽车制造商吗？可能不会。您应该将制造商放在另一个表中并使用外键。这会将这种不可转位的情况转变为可转位的情况。

其余的，请发布解释/解释分析。

Answer 2

如果您在 MySQL 中使用 MyISAM，理论上可以解释性能差异（因为关于您的数据库设计和执行的查询没有太多公开）。关于两个 RDBMS 之间的交叉性能，我建议您查看此比较页面 （锚定到 MyISAM 部分）。

Answer 3

MySQL 默认使用更多内存。我认为 def install 分配给它使用超过 256MB。不确定确切的数字。 PostgreSQL 默认设置为使用 32MB 之类的东西。尝试在配置文件中将每个内存增加到 1GB，然后运行基准测试并回复我们。

Answer 4

在我看来，您可能还没有更新 Postgres 数据库的统计信息。如果统计数据不正确，数据库的性能将不会很好。

Answer 5

查询

(
SELECT 1 AS n, left(plate, 2) AS l, plate, vin
FROM   imtt_vin
WHERE  left(plate, 1) BETWEEN 'A' AND 'Z'
AND    manufacturer ILIKE '%".self::$Manufacturer."%'
AND    vin LIKE ?   -- You probably mean: vin = ?
ORDER  BY l, plate
)

UNION ALL
(
SELECT 3 AS n, substr(plate, 4, 2) AS l, plate, vin
FROM   imtt_vin
WHERE  substr(plate, 4, 1) BETWEEN 'A' AND 'Z'
AND    manufacturer ILIKE '%".self::$Manufacturer."%'
AND    vin LIKE ?
ORDER  BY l, plate
)

UNION  ALL ...

• 使用 UNION ALL 。 UNION 将用于折叠重复项，这显然不是这里的情况，并且会更昂贵。
• 由于您的主要 ORDER BY 项是 n，因此对查询的各个部分进行排序可能会更有效。为此需要额外的一组括号。
• left (plate, 2) 比 substr(plate, 1, 2) 快一点。仅适用于前导子字符串（您的第一个 SELECT）。

索引

默认B 树索引仅适用于左锚定LIKE 表达式。但三元组 GiST 或 GIN 索引可用于非左锚定模式。您需要附加模块 pg_trgm 。使用 CREATE EXTENSION 对每个数据库安装一次在 PostgreSQL 9.1 或更高版本中。请参阅旧版本的手册。

CREATE EXTENSION pg_trgm;

我没有太多信息可以继续，基本的部分GIN索引 应该可以奇迹：

CREATE INDEX imtt_vin_partial_gist_idx ON imtt_vin
USING  gin (manufacturer gin_trgm_ops)
WHERE  left(plate, 1) BETWEEN 'A' AND 'Z';

CREATE INDEX imtt_vin_partial_gist_idx ON imtt_vin
USING  gin (manufacturer gin_trgm_ops)
WHERE  substr(plate, 4, 1) BETWEEN 'A' AND 'Z';

-- more ...

• 我没有在索引中包含 vin，因为您可能需要其中的相等运算符 = 。
• 部分索引上的谓词必须在查询中重复（或多或少），以便查询规划器了解索引是适用的。
• 三元组索引适用于不区分大小写的匹配。
• 使用EXPLAIN ANALYZE测试索引是否实际被使用。如果是的话，查询时间应该是毫秒，而不是秒。
• 该速度是以索引维护的写入操作的（小）成本为代价的。并且索引通常是磁盘上表大小的几倍。
• MySQL 无法做到这些。

Answer 6

您仍然没有提供足够的信息 - 您有哪些索引、慢速查询的 EXPLAIN ANALYZE 输出等。

关于优化示例查询的一些想法：

1：UTF-8 字符串函数通常不是很快。如果您想加快字符串函数的速度，请对此列使用 bytea 类型而不是 varchar （或者将整个数据库编码更改为 SQL_ASCII，但这是不可取的）

2：根据您的查询，数据库可能必须遍历表中的所有行并为每行计算这些字符串函数。

我不知道它们有多少匹配项，因此索引可能没有用，但功能索引可能会帮助您：

 CREATE INDEX imtt_vin_plate_1 ON imtt_vin (substr(plate,1,1));
 CREATE INDEX imtt_vin_plate_4 ON imtt_vin (substr(plate,4,1));
 CREATE INDEX imtt_vin_plate_7 ON imtt_vin (substr(plate,7,1));

3：如果您可以容忍重复输出，请使用 UNION ALL 而不是 UNION 在您的查询中——这将为您节省一些处理较大结果集的时间。

4：尽可能避免LIKE/ILIKE。