相同模式/查询的MySQL和PostgreSQL之间的性能差异

Question

我是一个新手DBA，我有微软SQL Server的经验，但我想跳到牙线。

我正在创建一家公司，我们开发了一个带有Postgres后端的应用程序(PHP)，我们还做了一些与MySQL相比的测试。我们观察到MySQL的速度是PostgreSQL的两倍。

我做了一个具体的性能测试：

• 表中具有等效列数据类型的相同列。
• 行数相同。
• 两个索引相同(包括主键)。
• CPU负载是空闲的，而Postgres机器则要好得多。
• 以及相同的查询(很明显)。

我做错了什么？

P.S:我读过很多关于数据库引擎性能调优的"howtos“。

P.S(2)：我们在MySQL数据库上使用MySQL(每个表一个文件)。

嗨，马特！

我做了三个常见的select (也是最难的)查询。

关于磁盘的问题，当然不一样；在Postgres中，它是一个SSD (几乎是最快的三次)。

MySQL缓存数据：

+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 16777216             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 256                  |
| table_open_cache             | 64                   |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+

我不知道如何在PostgreSQL中看到这个。

提前谢谢。

Answer 1

MySQL和PostgreSQL在性能上差别很大。InnoDB和PostgreSQL表是针对不同类型的查询进行优化的。理解这些差异对于理解如何从这两者中获得良好的性能是很重要的。

举个例子，让我们看看最明显的区别。

PostgreSQL vs MySQL/InnoDB表结构及其对性能

的意义

通常，在复杂的工作负载上，PostgreSQL会更快，但是对于简单的主键查找，MySQL和InnoDB会更快。

PostgreSQL表是堆表。没有选项可以构建不是堆表的表。cluster命令只需重写由指定索引排序的堆。然后，索引为具有各种值的元组提供堆位置。索引不能按物理顺序遍历，只能按逻辑顺序遍历，因此它们具有大量随机磁盘I/O，而顺序读取表通常意味着大量的顺序磁盘I/O，因为您可以按物理顺序读取表。顺序磁盘I/O可以使用预读缓存和其他操作系统级别的优化。

这意味着，如果您需要大量的记录或超过几页，那么从磁盘读取页面通常会更快。另一方面，表的主键查找需要点击索引，查找文件中的位置，然后点击堆表并提取记录。这意味着一些随机磁盘I/O。

InnoDB使用一种不同的方法。对于InnoDB，表是一个b树索引，其中包含索引有效载荷中的实际数据。这意味着主键查找已经从叶页中提取数据，因此需要更少的随机磁盘I/O。同时，索引扫描需要遍历两个索引而不是一个索引，这意味着除主键之外的任何索引的使用都会变得更慢，而顺序扫描则会更慢。

在PostgreSQL

中的诊断

我想你想用这样的方法：

 EXPLAIN (analyse, buffers, verbose)
 [query];

这将为您提供查询计划、初始估计、实际时间、缓冲区使用情况等等。