PostgreSQL与MySQL语法差异详解：从建表到高级查询的实战对比

1. 从“差不多”到“差很多”：为什么需要比较PostgreSQL和MySQL语法

如果你是从MySQL转向PostgreSQL，或者反过来，你可能会觉得它们都是SQL数据库，语法应该“差不多”。刚开始用的时候，你可能会用SELECT * FROM users;，发现两边都能跑通，于是信心满满。但当你开始建表、写复杂查询、处理日期或者想用点高级功能时，各种“惊喜”就来了。一个在MySQL里跑得好好的LIMIT 10，在PostgreSQL里可能就得换成FETCH FIRST 10 ROWS ONLY（虽然它也支持LIMIT，但标准写法不同）；一个你以为通用的AUTO_INCREMENT，在PostgreSQL里压根不存在。

这就是为什么我们需要深入比较两者的语法。这不仅仅是记住几个关键词的差异，更是理解两种数据库背后不同的设计哲学和标准遵从度。MySQL以其快速、简单、对Web开发友好而著称，很多语法是“怎么方便怎么来”，带有很强的历史包袱和自身特色。PostgreSQL则以其严格的标准遵从性、强大的功能（如窗口函数、CTE、JSON支持）和扩展性闻名，它的语法更贴近SQL标准。

直接说结论：把MySQL的SQL直接扔进PostgreSQL，大概率会报错；反之，把标准的、复杂的PostgreSQL SQL扔给老版本MySQL，它可能完全看不懂。这种差异在日常的DDL（定义语言）、DML（操作语言）、函数和高级特性中无处不在。搞清这些差异，能让你在数据库选型、迁移、跨数据库支持时，少踩很多坑，写出更健壮、更高效的SQL。

2. 定义数据结构的差异：从建表开始就分道扬镳

建表是操作数据库的第一步，但在这第一步上，两者就有显著不同。这不仅仅是关键词的差异，更体现了对数据完整性、标准性和便利性的不同权衡。

2.1 自增主键：AUTO_INCREMENT vs SERIAL/IDENTITY

这是最经典的差异点，也是新手最容易踩坑的地方。

在MySQL中，你通常这样定义一个自增主键：

CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50), PRIMARY KEY (id) );

AUTO_INCREMENT是MySQL的专属关键字，简单直接。它告诉MySQL：“这个字段你帮我自动管理，每次插入新行时加1。”

在PostgreSQL中，情况就复杂一些，历史上主要有两种方式，代表了两代方法：

1. 传统方式：SERIAL类型

CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) );

SERIAL并不是一个真正的数据类型，它只是一个语法糖。实际上，SERIAL等价于INTEGER类型，并自动创建一个关联的序列（SEQUENCE），以及一个默认值nextval(‘序列名’)。你可以把它理解为PostgreSQL版的“AUTO_INCREMENT”，但它底层是通过序列对象实现的，更灵活（比如可以设置序列的起始值、步长）。

2. 现代标准方式：GENERATED AS IDENTITY（推荐）这是PostgreSQL 10+引入的，更符合SQL标准的方式。

CREATE TABLE users ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR(50) );

或者使用BY DEFAULT选项，允许手动指定值（类似于MySQL的AUTO_INCREMENT行为）：

CREATE TABLE users ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username VARCHAR(50) );

GENERATED AS IDENTITY是SQL:2003标准的一部分，它明确声明了该列的值是由数据库自动生成的。虽然看起来更复杂，但它是未来的方向，语义更清晰，也便于数据库工具进行识别和管理。

实操心得：在新项目中使用PostgreSQL时，我强烈建议使用GENERATED AS IDENTITY。它更标准，也避免了SERIAL的一些历史遗留问题（比如在pg_dump时可能的行为差异）。从MySQL迁移时，需要将AUTO_INCREMENT列转换为GENERATED BY DEFAULT AS IDENTITY，这样能最大程度保持原有允许手动插入ID的行为。

2.2 字符串与文本类型：VARCHAR(n) vs TEXT的哲学

在MySQL中，VARCHAR(255)非常常见，并且对于更长的文本，你可能会用TEXT、MEDIUMTEXT、LONGTEXT。MySQL对VARCHAR的长度限制在早期版本比较严格（如5.0之前是255），后来放宽了，但依然存在行大小限制（约65KB）。

在PostgreSQL中，情况截然不同：

• VARCHAR(n)：带长度限制的可变长字符串。如果你定义了VARCHAR(50)，那么最多存储50个字符。
• TEXT：无限长的可变长字符串。是的，在PostgreSQL中，TEXT类型可以存储任意长度的字符串（理论上1GB，实际受制于系统）。

这里就体现了一个重要的设计差异：在PostgreSQL中，对于字符串字段，如果没有特别的理由（比如业务上强制要求长度作为约束），通常直接使用TEXT类型。因为：

1. 性能无差异：在PostgreSQL内部，对于VARCHAR(n)和TEXT的存储和处理性能几乎没有区别。
2. 更灵活：避免了未来因业务变化需要修改字段长度的麻烦。
3. 更符合习惯：PostgreSQL社区和许多ORM框架（如Django的ORM）都默认或推荐使用TEXT。

而在MySQL中，由于历史原因和存储引擎的差异，VARCHAR和TEXT在性能、索引限制上是有区别的，通常会更谨慎地选择。

注意事项：虽然PostgreSQL的TEXT很强大，但如果你需要确保数据完整性，比如存储手机号、身份证号这种长度固定的数据，使用VARCHAR(11)或CHAR(18)仍然是更好的选择，因为这能在数据库层面施加约束。不要因为TEXT方便就滥用它。

2.3 模式（Schema）与数据库（Database）的概念

这是一个根本性的概念差异，影响着数据库的组织结构。

• MySQL：Database（数据库）是顶层容器。你创建一个数据库（CREATE DATABASE myapp;），然后在里面直接创建表。虽然MySQL也有Schema这个概念，但在MySQL中，Schema是Database的同义词。CREATE SCHEMA和CREATE DATABASE是等价的。用户权限通常直接授予整个数据库。
• PostgreSQL：Database是最高级别的隔离容器，每个数据库之间是完全隔离的，默认情况下不能跨数据库查询。在Database之下，还有Schema（模式）这一层。一个数据库可以包含多个模式（例如public、hr、finance），模式才是表的直接容器。权限可以精细地控制到模式级别甚至表级别。

这种差异导致连接和引用方式不同：

• MySQL连接：你连接到某个数据库。USE mydatabase;然后直接操作表。
• PostgreSQL连接：你连接到某个数据库，但默认在public模式下。你可以通过SET search_path TO hr, public;来设置搜索路径，或者使用模式限定符来访问表：SELECT * FROM hr.employees;。

经验技巧：在PostgreSQL中，利用Schema可以很好地组织大型应用。例如，你可以为不同的微服务模块划分不同的模式，或者将第三方扩展的表放在独立的模式里。这比在MySQL中创建多个数据库更灵活，因为同数据库下的不同模式可以共享连接、事务和某些高级功能。迁移MySQL应用时，通常将MySQL的一个Database映射为PostgreSQL的一个Schema（放在一个公共的Database里），而不是一个独立的Database。

3. 操作与查询数据：DML语法的细微之处与巨大鸿沟

日常的增删改查（INSERT, UPDATE, DELETE, SELECT）是使用频率最高的部分。这里面的差异，有些是细微的便利性差别，有些则是能力上的代差。

3.1 插入数据并返回：INSERT ... RETURNING的神奇能力

这是一个PostgreSQL强大而MySQL长期缺失的功能（直到MySQL 8.0.21才在有限支持）。

假设你插入一条用户记录，并想立刻获取数据库生成的自增ID。

在MySQL中（传统方式）：

INSERT INTO users (username, email) VALUES (‘alice‘, ‘alice@example.com‘); -- 然后你需要使用 LAST_INSERT_ID() 函数，但这必须在同一个连接会话中立即调用 SELECT LAST_INSERT_ID();

这是一个两步操作，并且在并发环境下需要确保SELECT和INSERT在同一个连接/事务中，否则会出错。

在PostgreSQL中：

INSERT INTO users (username, email) VALUES (‘alice‘, ‘alice@example.com‘) RETURNING id;

一句INSERT语句，直接返回你指定的字段（这里是id）。这不仅限于自增ID，可以返回插入行的任意列，甚至是计算表达式。

更强大的场景：插入多行并返回所有信息。

INSERT INTO users (username, email) VALUES (‘bob‘, ‘bob@example.com‘), (‘charlie‘, ‘charlie@example.com‘) RETURNING id, username, created_at;

这对于需要立即使用插入数据的后续逻辑（如日志记录、缓存更新）极其方便，消除了应用层的等待和额外查询，保证了原子性。

踩坑实录：早期从PostgreSQL切回MySQL开发时，我经常忘记RETURNING在MySQL里不可用，写出的代码一运行就报语法错误。现在即使MySQL 8.0有了类似功能（INSERT ... RETURNING），其支持范围和稳定性与PostgreSQL仍有差距。在设计跨数据库兼容的应用层时，这是一个需要抽象的关键点。

3.2 更新与删除的关联操作：FROM子句的妙用

在MySQL中，如果你想基于另一张表的数据来更新或删除本表的记录，通常需要使用子查询或JOIN。

例如，根据user_logs表中的最后登录时间，来更新users表的last_active字段。

MySQL写法（使用多表UPDATE）：

UPDATE users u JOIN user_logs ul ON u.id = ul.user_id SET u.last_active = ul.last_login WHERE ul.action = ‘LOGIN‘;

MySQL的多表UPDATE语法比较独特，它直接在UPDATE后面跟多个表，并在SET和WHERE中使用别名。

PostgreSQL写法（使用FROM子句）：

UPDATE users SET last_active = ul.last_login FROM user_logs ul WHERE users.id = ul.user_id AND ul.action = ‘LOGIN‘;

PostgreSQL的写法更接近于SELECT ... FROM ... WHERE的思维模式。UPDATE后面跟要更新的表，FROM后面引入关联表，条件写在WHERE中。这种语法对于熟悉标准SQL的人来说更直观。

DELETE操作也类似：删除没有订单的用户。MySQL:

DELETE u FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;

PostgreSQL:

DELETE FROM users u USING orders o WHERE u.id = o.user_id AND o.id IS NULL; -- 或者使用更标准的子查询 DELETE FROM users WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

PostgreSQL的USING子句类似于UPDATE的FROM子句。

3.3 分页查询：LIMIT/OFFSET的“方言”与标准

分页是最常见的需求。虽然现在两者都支持LIMIT和OFFSET，但它们的“血统”不同。

• MySQL：LIMIT和OFFSET是MySQL的“方言”，很早就被广泛支持。语法是LIMIT [offset,] row_count。

  SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20; -- 跳过20条，取10条 SELECT * FROM products ORDER BY price DESC LIMIT 20, 10; -- 同上，另一种写法

• PostgreSQL：同样支持LIMIT/OFFSET（为了兼容性），但更推荐使用标准的SQL:2008语法：FETCH FIRST ... ROWS ONLY和OFFSET ... ROWS。

  -- PostgreSQL 推荐的标准写法 SELECT * FROM products ORDER BY price DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY; -- 也支持的MySQL方言写法 SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

为什么推荐标准写法？首先是语义更清晰，OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY读起来就像“跳过20行，然后取接下来的10行”。其次，在一些复杂的分析查询或窗口函数中，标准语法的行为可能更一致。虽然目前区别不大，但使用标准语法能让你的SQL更具可移植性和未来兼容性。

4. 函数与运算符：日常使用中的“坑点”合集

这是语法差异最琐碎，但也最容易导致错误的地方。同一个功能，函数名或行为可能完全不同。

4.1 字符串拼接：CONCAT() vs ||

• MySQL：主要使用CONCAT()函数。它接受多个参数，如果任何参数为NULL，则返回NULL。

  SELECT CONCAT(‘Hello‘, ‘ ‘, ‘World‘); -- ‘Hello World‘ SELECT CONCAT(‘Hello‘, NULL, ‘World‘); -- NULL

  MySQL也支持||运算符，但默认情况下它是逻辑OR运算符。需要通过设置SQL_MODE为PIPES_AS_CONCAT来启用其字符串拼接功能，不常用。

• PostgreSQL：首选||运算符进行字符串拼接。这是标准SQL的字符串连接运算符。

  SELECT ‘Hello‘ || ‘ ‘ || ‘World‘; -- ‘Hello World‘

  如果拼接的值为NULL，NULL会被当作空字符串处理（除非所有值都是NULL，则结果为NULL）。

  SELECT ‘Hello‘ || NULL || ‘World‘; -- ‘HelloWorld‘

  PostgreSQL也提供了CONCAT()和CONCAT_WS()函数，它们会忽略NULL参数，更安全。

  SELECT CONCAT(‘Hello‘, NULL, ‘World‘); -- ‘HelloWorld‘ SELECT CONCAT_WS(‘-‘, ‘2023‘, NULL, ‘01‘); -- ‘2023-01‘ (用‘-‘连接，跳过NULL)

避坑指南：在编写跨数据库SQL时，字符串拼接是个大麻烦。一个常见的策略是在应用层进行字符串处理，或者使用ORM的表达式功能来屏蔽差异。如果必须在SQL中处理，可以考虑使用COALESCE(field, ‘’)先将NULL转为空字符串，再使用||或CONCAT。

4.2 获取当前时间：NOW() vs CURRENT_TIMESTAMP

两者都支持NOW()和CURRENT_TIMESTAMP，但有一些细微差别。

• MySQL：NOW()返回语句开始执行的时间，在整个语句执行过程中保持不变。SYSDATE()则返回函数执行时的实时时间。CURRENT_TIMESTAMP是NOW()的同义词。
• PostgreSQL：NOW()和CURRENT_TIMESTAMP功能相同，都返回事务开始的时间，在同一个事务中多次调用返回相同值。如果需要实时时间，可以使用clock_timestamp()函数。

更重要的差异在于时间运算：

• MySQL：使用DATE_ADD()和DATE_SUB()函数，或INTERVAL关键字。

  SELECT NOW() + INTERVAL 1 DAY; SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

• PostgreSQL：直接支持+和-运算符与INTERVAL进行日期时间运算，更为直观和强大。

  SELECT NOW() + INTERVAL ‘1 day‘; SELECT NOW() - INTERVAL ‘2 hours 30 minutes‘; -- 甚至可以这样 SELECT NOW() + ‘1 day‘::interval; -- 类型转换写法

  PostgreSQL的INTERVAL类型非常灵活，可以表示复杂的时间段。

4.3 条件判断：IF() vs CASE WHEN vs COALESCE()

• MySQL：提供了流程控制函数IF(expr, true_value, false_value)。

  SELECT IF(score >= 60, ‘Pass‘, ‘Fail‘) AS result FROM exams;

  还有IFNULL(expr1, expr2)（等同于COALESCE）和NULLIF(expr1, expr2)。

• PostgreSQL：没有IF()函数。它使用标准的CASE ... WHEN ... THEN ... ELSE ... END表达式。

  SELECT CASE WHEN score >= 60 THEN ‘Pass‘ ELSE ‘Fail‘ END AS result FROM exams;

  对于简单的空值处理，两者都支持标准的COALESCE()（返回第一个非NULL参数）和NULLIF()（两个参数相等则返回NULL）。

个人体会：虽然CASE WHEN写起来比IF()长，但它是SQL标准，可读性更强，尤其是在多重条件判断时。从MySQL迁移到PostgreSQL，需要把所有的IF()函数重写为CASE WHEN表达式。很多ORM框架生成的SQL会使用CASE WHEN以保证兼容性。

4.4 类型转换：CAST() vs :: 操作符

将一种数据类型转换为另一种是常见操作。

• MySQL：使用CAST(expr AS type)函数，或CONVERT(expr, type)函数。

  SELECT CAST(‘123‘ AS UNSIGNED); SELECT CONVERT(‘2023-01-01‘, DATE);

• PostgreSQL：除了支持标准的CAST(expr AS type)，更常用、更简洁的是::操作符（PostgreSQL特有的语法糖）。

  SELECT ‘123‘::INTEGER; SELECT ‘2023-01-01‘::DATE; SELECT some_jsonb_column::TEXT;

  ::操作符在PostgreSQL的SQL编写中无处不在，特别是在处理JSON、数组、几何类型等复杂类型时，非常方便。

5. 高级特性与扩展性：PostgreSQL的“火力展示区”

如果说基础语法是“生存技能”，那么高级特性就决定了数据库的“生产力上限”。在这一领域，PostgreSQL的优势非常明显。

5.1 公共表表达式（CTE）与递归查询

CTE（WITH子句）允许你定义一个临时的结果集，在后续的主查询中引用它。这极大地提高了复杂查询的可读性和可维护性。两者都支持非递归CTE。

但递归查询（Recursive CTE）是PostgreSQL的一大亮点，用于处理树形或图状数据（如组织架构、评论树、路径查找）。

示例：查询一个评论树的所有子评论（假设表comments有id和parent_id字段）

WITH RECURSIVE comment_tree AS ( -- 锚点部分：找到根评论 SELECT id, content, parent_id, 1 AS depth FROM comments WHERE parent_id IS NULL AND post_id = 1 UNION ALL -- 递归部分：连接子评论 SELECT c.id, c.content, c.parent_id, ct.depth + 1 FROM comments c INNER JOIN comment_tree ct ON c.parent_id = ct.id ) SELECT * FROM comment_tree ORDER BY depth, id;

这个查询会从根评论开始，不断递归查找所有层级的子评论。

在MySQL 8.0之前，实现这样的递归查询需要借助存储过程或应用层多次查询，极其繁琐。MySQL 8.0也引入了递归CTE，语法类似，但在处理深度递归或复杂过滤时，性能和功能完整性上PostgreSQL通常表现更优。

5.2 窗口函数（Window Functions）

窗口函数允许你在不折叠行的前提下，对一组相关的行进行计算（如排名、移动平均、累计求和）。这是现代数据分析的基石。

两者（MySQL 8.0+和PostgreSQL）现在都支持强大的窗口函数，但PostgreSQL的支持历史更久远、更成熟。

示例：计算每个部门内员工的薪水排名

SELECT department_id, employee_name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary FROM employees;

OVER子句定义了“窗口”——数据分区和排序的方式。PARTITION BY类似于GROUP BY的分组，但不会将多行合并为一行。

核心差异点：虽然语法标准一致，但PostgreSQL的窗口函数实现通常性能更好，并且支持更多种类的窗口函数和帧定义（如RANGE和GROUPS模式）。在MySQL 8.0早期版本中，窗口函数的使用可能存在一些限制或性能瓶颈。对于重度依赖窗口函数的报表或分析系统，PostgreSQL通常是更稳妥的选择。

5.3 JSON/JSONB支持

处理半结构化数据是现代应用的常见需求。

• MySQL：从5.7开始支持JSON数据类型，提供了JSON_EXTRACT()、JSON_SET()等函数，并支持在生成的列上创建索引以加速查询。

  SELECT * FROM products WHERE JSON_EXTRACT(specs, ‘$.color‘) = ‘red‘;

• PostgreSQL：提供了两种JSON类型：JSON（存储原始文本，校验格式）和**JSONB（Binary JSON，推荐使用）**。JSONB将JSON数据以二进制格式存储，支持索引（GIN索引），查询性能极高，并且提供了极其丰富的操作符和函数。

  -- 使用 -> 和 ->> 操作符提取元素 SELECT specs->‘color‘ AS color FROM products; SELECT * FROM products WHERE specs->>‘color‘ = ‘red‘; -- JSONB 包含操作符 @> SELECT * FROM products WHERE specs @> ‘{“color“: “red“}‘;

  JSONB的@>（包含）操作符配合GIN索引，可以实现对JSON文档内部字段的极速查询，这是PostgreSQL在处理半结构化数据方面的杀手锏之一。

5.4 全文搜索：LIKE vs 全文索引 vs 专用类型

简单的模式匹配，两者都用LIKE或正则表达式。但对于真正的全文搜索（如搜索文章内容）：

• MySQL：提供了FULLTEXT索引（仅适用于MyISAM和InnoDB存储引擎），使用MATCH() ... AGAINST()语法。它支持自然语言模式和布尔模式，但功能相对基础，对中文等复杂语言的分词支持需要依赖第三方插件或应用层处理。

  SELECT * FROM articles WHERE MATCH(title, body) AGAINST(‘数据库 优化‘ IN BOOLEAN MODE);

• PostgreSQL：提供了更强大、更灵活的全文搜索功能。核心是tsvector（文本搜索向量）和tsquery（文本搜索查询）数据类型，以及GIN索引。

  -- 创建支持全文搜索的列 ALTER TABLE articles ADD COLUMN body_tsvector tsvector; UPDATE articles SET body_tsvector = to_tsvector(‘english‘, body); CREATE INDEX idx_fts ON articles USING GIN(body_tsvector); -- 查询 SELECT * FROM articles WHERE body_tsvector @@ to_tsquery(‘english‘, ‘database & optimization‘);

  PostgreSQL的全文搜索支持多种语言（包括通过插件支持中文分词），可以自定义词典、配置权重，并且查询功能非常强大（支持&(AND),|(OR),!(NOT),<->(相邻)等操作符）。对于需要高质量全文搜索的应用，PostgreSQL常常可以替代专门的搜索引擎（如Elasticsearch）的简单场景。

6. 性能相关语法：隐式转换、索引提示与查询计划

数据库的“快慢”不仅取决于硬件和配置，也与你写的SQL语法密切相关。一些语法细节会直接影响查询优化器的决策。

6.1 隐式类型转换的“宽容”与“严格”

这是MySQL和PostgreSQL在行为上非常不同的一点，也容易引发性能问题甚至错误。

• MySQL：以“宽容”著称，会进行大量的隐式类型转换。例如，将字符串与数字比较，MySQL会尝试将字符串转换为数字。

  SELECT * FROM users WHERE id = ‘123abc‘; -- 这里‘123abc‘会被转换为123，查询可能返回结果，但逻辑上是错误的。

  这种宽容性带来了便利，但也可能导致索引失效（因为对字段进行了函数计算）或意想不到的结果。

• PostgreSQL：以“严格”著称，默认情况下拒绝大多数隐式类型转换。

  SELECT * FROM users WHERE id = ‘123‘; -- 如果id是INTEGER类型，这里会报错：操作符不存在: integer = text

  你必须显式地进行类型转换：

  SELECT * FROM users WHERE id = ‘123‘::INTEGER; -- 或者 CAST(‘123‘ AS INTEGER)

  这种严格性保证了数据的准确性和查询意图的清晰，也使得优化器能更准确地使用索引。在PostgreSQL中，WHERE id = 123和WHERE id = ‘123‘::int对于优化器来说是等价的，都能利用索引。

重要经验：从MySQL迁移到PostgreSQL，隐式类型转换错误是最常见的报错之一。养成在应用层或SQL中明确数据类型的习惯，不仅能避免PostgreSQL的报错，也能让MySQL的查询更加健壮和高效。在MySQL中，也应尽量避免在WHERE子句的字段侧进行运算或转换，以保证索引的有效使用。

6.2 索引提示（Index Hints）的哲学

当优化器没有选择最优索引时，开发者有时想手动干预。

• MySQL：支持索引提示语法，如USE INDEX、FORCE INDEX、IGNORE INDEX。这在某些复杂查询或统计信息不准时是最后的“逃生舱口”。

  SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE ‘%@example.com‘;

• PostgreSQL：官方不支持，也不鼓励使用索引提示。PostgreSQL社区认为，优化器应该足够聪明，如果优化器选错了索引，那应该是数据库的问题（统计信息不准、成本估算模型需要调整），应该通过优化数据库本身来解决，而不是让SQL语句来“打补丁”。替代方案是：

  1. 更新统计信息：ANALYZE table_name;
  2. 调整成本估算参数（如random_page_cost,effective_cache_size）。
  3. 使用更复杂的查询重写来引导优化器。
  4. 在极少数情况下，可以禁用某些索引或扫描类型，但这不是常规手段。

这种差异体现了两种哲学：MySQL提供了更多“手动控制”的工具，而PostgreSQL更倾向于相信并优化其自动化的查询规划器。

6.3 查看执行计划：EXPLAIN的输出解读

两者都使用EXPLAIN命令来查看查询计划，但输出格式和细节深度有差异。

• MySQL：EXPLAIN输出一个表格，展示id,select_type,table,type,possible_keys,key,rows,Extra等关键信息。EXPLAIN FORMAT=JSON可以提供更详细的树状结构信息。

  EXPLAIN SELECT * FROM users WHERE age > 30;

  重点关注type（访问类型，如ALL全表扫描、index索引扫描、ref/eq_ref索引查找）、key（使用的索引）、rows（预估行数）。

• PostgreSQL：EXPLAIN输出一个树状结构的文本，更直观地展示了执行计划的层次。它提供了极其详细的开销信息。

  EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE age > 30;

  ANALYZE选项会实际执行查询并给出真实时间，BUFFERS会显示缓存命中情况。PostgreSQL的EXPLAIN输出中，你需要关注：

  • Seq Scan（顺序扫描） vsIndex Scan（索引扫描）vsBitmap Index Scan（位图索引扫描）。
  • 每个节点的cost（预估成本，是一个相对值）、rows（预估行数）、width（预估行宽）。
  • 实际的执行时间（actual time）和循环次数（loops）。

排查技巧：对于慢查询，我习惯在PostgreSQL中使用EXPLAIN (ANALYZE, BUFFERS)。ANALYZE能暴露预估和实际的巨大差异（说明统计信息有问题），BUFFERS能看出查询是吃内存（缓存命中率高）还是吃IO（缓存命中率低）。在MySQL中，EXPLAIN FORMAT=JSON结合性能模式（performance_schema）是深入分析的利器。理解两者的EXPLAIN输出，是进行SQL调优的基本功。