#### 翻译：PostgreSQL中文社区 / doudou586 #### 发布：2017-12-13 #### 欢迎大家踊跃投稿，投稿信箱： press@postgres.cn ## 数据库慢查询会导致多方面不利影响 数据库查询很慢会对公司产生多方面的不利影响。它们会影响一些原来很棒的应用的名声，让后台数据处理又慢又痛苦，并且会极大地增加基础硬件设施的成本。要成为一名经验丰富的网络应用开发者，绝对有必要学习和掌握数据层的优化策略。  在这篇文档中，我们将探索PostgreSQL的查询成本模型，如何理解查询规划的输出内容，更重要的是，如何使用这些数据来提升我们应用的数据吞吐能力。 ## 使用 PostgreSQL 的 Explain 命令 在我们的应用发布一个新查询前，一个比较好的习惯是先在PostgreSQL中使用explain指令来试运行一下，这样我们可以获取到新的查询会对我们的系统产生什么样的性能影响。 我们会从一个数据库表的示例开始演示explain的使用。这张表我们先填入100万条记录。 db # CREATE TABLE users (id serial, name varchar); db # INSERT INTO users (name) SELECT 'John' FROM generate_series(1, 1000000); db # SELECT count(*) FROM users; count --------- 1000000 (1 row) db # SELECT id, name FROM users LIMIT 10; id | name ----+------ 1 | John 2 | John 3 | John 4 | John 5 | John 6 | John 7 | John 8 | John 9 | John 10 | John (10 rows) 假定我们需要查询一个给定id的用户，便在真正使用这个查询前，我们需要预估一下这个操作的成本。让我们运行一下explain： db # EXPLAIN SELECT * FROM users WHERE id = 870123; QUERY PLAN -------------- Gather (cost=1000.00..11614.43 rows=1 width=9) Workers Planned: 2 -> Parallel Seq Scan on users (cost=0.00..10614.33 rows=1 width=9) Filter: (id = 870123) (4 rows) 上面的例子中，我们得到了很多的输出，但我们可以只关注一下要点。为了运行这个查询，PostgreSQL计划启动2个并行的进程（译者注：这个功能是10.0版本以后才有的功能），每个进程会对表进行顺序扫描，然后汇总进程会将两个工作进程的结果进行汇总。 在本文中，我们会聚焦于上面输出内容中的成本数据，以及PostgreSQL如何计算这个数据。 为了简化我们的成本计算，让我们在运行上面的查询时，限制并行的工作进程为0，即只有一个进程。 db # SET max_parallel_workers_per_gather = 0; db # EXPLAIN SELECT * FROM users WHERE id = 870123; QUERY PLAN -------------------- Seq Scan on users (cost=0.00..17906.00 rows=1 width=9) Filter: (id = 870123) (2 rows) 这个结果就简单一点了。仅有一个CPU核心，预计的成本为17906。 ## 成本值背后的数学 查询的成本值或是系统付出的消耗值是PostgreSQL中的一个抽像概念。在PostgreSQL中有很多方法来执行一个查询，PostgreSQL总是会选择一个成本值最低的查询计划方法（来执行查询）。 为了计算查询成本，PostgreSQL首先检查一下表的字节大小，然后计算用户表的大小。 db # select pg_relation_size('users'); pg_relation_size ------------------ 44285952(1 row) PostgreSQL会加上它顺序读出每一个数据块的成本值。如果我们知道每一个数据块为8kb，我们就可以计算出顺序读出我们的表的所有数据块的成本值。 block_size = 8192 # block size in byte srelation_size = 44285952 blocks = relation_size / block_size # => 5406 现在，我们知道了数据块的数量，让我们查一下PostgreSQL定位一个数据块需要的成本值。 db # SHOW seq_page_cost; seq_page_cost --------------- (1 row) 根据上面的结果，即PostgreSQL定位一个数据块需要一个成本值， 也即从表中读出那些值需要5406。 而从磁盘上读出这些值并不代表PostgreSQL就只需做这点事情，它还需要将这些值发给CPU，然后使用WHERE条件来过滤数据。有两个参数与成本计算有关。 db # SHOW cpu_tuple_cost; cpu_tuple_cost ---------------- db # SHOW cpu_operator_cost; cpu_operator_cost ------------------ 0.0025 现在我们有了所有的参数来计算在explain展示的结果了。 number_of_records = 1000000 block_size = 8192 # block size in byte srelation_size = 44285952 blocks = relation_size / block_size # => 5406 seq_page_cost = 1 cpu_tuple_cost = 0.01 cpu_operator_cost = 0.0025; cost = blocks * seq_page_cost + number_of_records * cpu_tuple_cost + number_of_records * cpu_operator_cost cost # => 17906 （译者注：原文中，最后一步的 cpu\_operator\_cost 为 cpu\_filter\_cost，应是作者手误，PG10中无此变量。） ## 索引与 PostgreSQL 成本模型 对一名数据库工程师来说，索引现在以及将来都是他生活中最为重要的事情。加一个索引可以减小我们这个查询的成本吗，让我们试一下。 首先，我们给我们的用户表增加一个索引： db # CREATE INDEX idx_users_id ON users (id); 让我们观察一下我们有索引后的的查询计划。 db # EXPLAIN SELECT * FROM users WHERE id = 870123; QUERY PLAN ---------------------- Index Scan using idx_users_id on users (cost=0.42..8.44 rows=1 width=9) Index Cond: (id = 870123) (2 rows) 现在成本数值大大减小了！带有索引的成本计算比顺序扫描要复杂一点，它包含两个阶段： PostgreSQL将考虑random_page_cost 和 cpu_index_tuple_cost 两个参数，然后基于索引树的高度来计算一个值。 db # SHOW random_page_cost; random_page_cost ------------------ db # SHOW cpu_index_tuple_cost; cpu_index_tuple_cost ---------------------- 0.005 对实际的计算，请参考阅读索引成本计算的源代码。 ## 多进程成本 PostgreSQL可以启动多个并行进程来执行查询。然而，启动新的进程本身对性能也是有一定影响的。 使用并行查询技术时计算成本时，PostgreSQL使用parallel_tuple_cost参数来下定义将记录从一个进程传至另一个进程的成本，使用parallel_setup_cost参数代表启动一个新进程的成本。 db # SHOW parallel_tuple_cost; parallel_tuple_cost --------------------- db # SHOW parallel_setup_cost; parallel_setup_cost ---------------------  xcvxcvsdf 回答于 2025-01-01 18:14:38+08 xcvxcvsdf 回答于 2024-10-23 02:59:00+08