👋 欢迎来到吴老二技术博客,专注运维 / 数据库 / 云原生
首页/数据库/正文

JOIN 的写法,决定优化器有多少选择

吴
吴老二
2026-11-19数据库2 次浏览0 条评论

导语

INNER JOIN 和 LEFT JOIN 的结果差异人人都能说清楚,但为什么专家总说“能写 INNER JOIN 就别写 LEFT JOIN”?为什么 COUNT(*) 和 COUNT(o.id) 在左连接里结果不一样?为什么 user_id 这种重复度很高的列还值得建索引?

本篇是系列第三篇。接着前两篇的 test_ops 库和 users 表(5 个用户,赵六 id=4),建 orders 表并插入订单,重点讲三件事:JOIN 算法与优化器自由度、NULL 在聚合中的语义、索引选择性的判断。

一、建 orders 表:为什么只建普通索引,不建物理外键

CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'paid',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

两个设计决策:

不建物理外键(FOREIGN KEY),只在 user_id 上建普通索引。 互联网业务里物理外键会带来跨表锁和级联操作的不可控性,应用层保证引用完整性更常见。但索引必须建——user_id 是 JOIN 的连接键,没索引就是全表扫描。

金额用 DECIMAL(10,2) 而不是 FLOAT。 浮点数在二进制下无法精确表示大部分十进制小数,金额计算会出现精度误差。DECIMAL 是精确十进制类型,这是财务相关字段的底线。

插入订单,赵六(id=4)故意不插,供后面对比:

INSERT INTO orders (user_id, amount, status) VALUES
(1, 199.00, 'paid'), (1, 88.50, 'paid'),
(2, 320.00, 'paid'), (3, 45.00, 'refund'),
(5, 660.00, 'paid');

二、INNER JOIN 与 LEFT JOIN:结果之外,还有执行计划

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

结果差异:INNER JOIN 不出现赵六,LEFT JOIN 出现赵六且 amount 为 NULL。这是语义层。

执行层的关键区别:LEFT JOIN 要求左表所有行保留,优化器不能随意调整驱动表顺序,执行计划的选择空间比 INNER JOIN 小。 能表达成 INNER JOIN 的查询,优化器可以自由选择用 users 还是 orders 做驱动表、是否走 hash join;写成 LEFT JOIN 就把这条路堵死了一部分。

用 EXPLAIN 验证:

EXPLAIN SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id;

看 table 列的执行顺序,看被驱动表的 type 是不是 ref(走了 idx_user_id)。EXPLAIN 是判断 JOIN 好坏的唯一客观依据,不靠猜。

三、聚合里的 NULL:COUNT(*) 和 COUNT(o.id) 不是一回事

SELECT u.name,
       COUNT(o.id) AS order_count,
       IFNULL(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

两个语义细节:

COUNT(o.id) 而不是 COUNT(\*)。 在左连接结果里,赵六那一行的 o.id 是 NULL。COUNT(*) 数的是行数,赵六会算 1;COUNT(o.id) 只数非 NULL 值,赵六算 0。要统计“订单数”,必须用 COUNT(o.id)。

IFNULL 处理 NULL 参与运算。 NULL + 任何值 = NULL,赵六的 SUM(o.amount) 返回 NULL 而不是 0,直接展示会误导。IFNULL(..., 0) 把 NULL 转成 0,结果才符合业务语义。

这两点不是语法问题,是语义问题。写错了不报错,但结果错。

四、索引选择性:重复度高的列为什么还值得建索引

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

判断索引是否值得建,看选择性:COUNT(DISTINCT column) / COUNT(*)。user_id 在订单表里重复度高(一个用户多单),选择性不算高。但它值得建,原因有两个:

  1. 它是 JOIN 连接键,等值查询和连接场景下索引收益远大于写入维护成本。
  2. 重复度高的索引仍然能大幅减少扫描行数,尤其是订单表远大于用户表的时候。

反过来,status 只有 paid/refund 几个值,选择性极低,单独建索引基本没用——优化器很可能直接忽略它走全表扫描。索引价值由选择性和使用场景共同决定,不是“有列就建”。

顺手练一下改表结构:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
DESC users;

ALTER 在大表上可能锁表,生产环境需先评估或用在线 DDL 工具,测试库可放心练。这个坑留到第四篇讲备份和变更时再展开。

本篇小结

JOIN 的写法影响优化器自由度,聚合函数对 NULL 的处理是语义问题,索引价值由选择性和使用场景共同决定。下一篇是运维自保篇:备份一致性从哪来、权限模型怎么生效、慢查询和锁等待怎么定位。

评论 0

评论需 登录 后发表(注册并验证邮箱)。
还没有评论,来抢沙发~