mysql 必知必会的多表联结问题？

[amiwrong123]

在书中 15.2.3 中，让查询订单编号为 20005 的订单中的物品信息。

由于查询列来自三个表，所以书中连接了三个表。

select prod_name, vend_name, prod_price, quantity 
from orderitems, products, vendors 
where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;

我合计改一下，是不是能更高效，改完查到的东西也一样，但改完就没法查 quantity 列了（来自 orderitems ）

SELECT 
prod_name, 
prod_price, 
(SELECT vend_name FROM vendors WHERE vendors.vend_id = products.vend_id) AS vend_name 
FROM products WHERE prod_id IN 
    (SELECT prod_id FROM orderitems WHERE order_num = 20005)

1. 我这样改，效率有变好吗？或者能更好吗？
2. 怎么才能让我这种改法，能查到 quantity 列？

表信息：

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,#订单号
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,#产品 id
  quantity   int          NOT NULL ,#产品数量
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,#产品 id
  vend_id    int           NOT NULL ,#供应商 id
  prod_name  char(255)     NOT NULL ,#产品姓名
  prod_price decimal(8,2)  NOT NULL ,#产品价格
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,#供应商 id
  vend_name    char(50) NOT NULL ,#供应商姓名
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

我把有用的信息都标注出来了。

[qiayue]

请问你说的更高效是通过哪些指标判断出来的。

另外，第一个三表连接语句，看起来清晰明确，一眼就知道要查的是订单号为 20005 的订单相关信息。
你改的，实话说，很难理解。

[amiwrong123]

其实我是根据书中前面内容瞎改了下，用 explain 看了下，我这么改好像更不好了。本来以为先用子查询查出订单 20005 里的产品 id 会更好呢。

第一个图是第一个查询的。

<img src="https://s1.ax1x.com/2020/09/05/wZSgx0.png" alt="1599316644(1)" border="0">

第二个图是第二个查询的。

<img src="https://s1.ax1x.com/2020/09/05/wZSHR1.png" alt="1599316719(1)" border="0">

而且还报了个警告。。

[amiwrong123]

@qiayue 忘 @了

[xupefei]

没啥区别。subquery flatten 是 query optimizer 的基本技能。

[zhangysh1995]

@amiwrong123 我寻思着原始这条 query 应该可以更快，orderitems 里面 order_num 没有索引，那么 order_num = 20005 应该直接就给全表扫描了？总之这条查询感觉写法挺奇怪的。。

[Mroldx]

这 query optimizer 是啥啊

[CRVV]

SQL 是声明式语言而不是命令式语言，也就是你把自己需要什么数据告诉数据库，然后数据库把数据取出来，至于是怎么取的，那是数据库内部的事情，不是用 SQL 写出来的。
写 SQL 的点在于用精确简洁的方式描述清楚你到底要什么数据，比如前一种写法就很好了。

虽然这么说，当然存在换一个写法能让查询变快的方法，但是这种事情并不那么常见。
如果真要做优化，你把两句分别跑一下，如果变快了就是更好了。

[519718366]

首先要说明的是: 你这两条 sql 是不等价的。

原始 sql 是通过 join 的形式，join 的形式最大的特点是结果集无法去重，所以当你订单里有两个产品 A 时，返回的结果集里就有两条 A 的纪录

改写后的 sql 是通过子查询的形式，这样做最大的特点是产品信息的去重，即使订单里有两个商品 A，返回的结果里也只有一条 A 的纪录。
where 里的子查询没什么问题，用的时候注意 子查询返回结果过多，可能会导致外层查询不走索引。

select 里的子查询我是坚决反对的，因为我的理解中，select 里的子查询是对每一条返回的纪录再单独执行一条 sql 。假设你订单里有 10 个不同商品，最后你要执行的 sql 数量就是 1 条主的+10 条 select 里的子查询。10 条 select 就是 10 次 io 啊…

所以就单看你这个需求，原始 sql 是一般的做法吧，不会想着花里胡哨改写。

如果非要优化性能的话，我推荐把 sql 拆成多次执行用上覆盖索引。第一次带上查询条件，用上覆盖索引，把关键的 id 返回给服务器，服务器再根据第一次返回的 id 去对应的表查附属信息。

如果理解上有错，还请大家批评指正。

[amiwrong123]

>改写后的 sql 是通过子查询的形式，这样做最大的特点是产品信息的去重，即使订单里有两个商品 A，返回的结果里也只有一条 A 的纪录。

其实我那个子查询不会去重，不过你提醒了我，子查询应该这么写：
(SELECT prod_id, sum(quantity) FROM orderitems WHERE order_num = 20005 GROUP BY prod_id)
或者：
(SELECT distinct prod_id FROM orderitems WHERE order_num = 20005)


select 里的子查询确实不好，我也觉得。


>我推荐把 sql 拆成多次执行用上覆盖索引。第一次带上查询条件，用上覆盖索引，把关键的 id 返回给服务器，服务器再根据第一次返回的 id 去对应的表查附属信息。

本人比较菜，大概理解下。加覆盖索引就得 ALTER TABLE，以后你意思是 执行多个 sql 语句吗，这怎么搞，第一个 sql 语句的结果存起来吗

[amiwrong123]

@519718366

[519718366]

@amiwrong123
1. 改写后的 sql 具有了去重功能: 对于我想说的去重你理解歪了。
我想说的是你 where 里的子查询虽然没有去重，可能会查出产品 id 是:1,2,2,3,4 这样的结果。
但是在执行外层的 select from product where prod_id in (1,2,2,3,4)时，id=2 的纪录只会返回一条，所以说是去重了

[encro]

select oi.*,
p.prod_name,
v.vend_name,
from orderitems oi
inner join products p on prod_id,
inner join vendors v on vend_id,
where oi.order_num = 20005;

这个执行性能和第一条一样，但是更加容易读懂。

Mysql 多表查询优化最重要的一条就是先找准基础表，减少基准表返回的记录可以大大减少查询的解析行数，

第一条查询基本最简单了，假设订单 10 条商品，那么需要分析的记录就是 30 条，没有比这更加简单了。合理索引后基本不会有性能问题。

[519718366]

@amiwrong123
关于覆盖索引优化服务性能
我觉得这算是后端开发做复杂列表时在 mysql 上必备知识点了

原理和实操都很简单。
覆盖索引的意思就是你 select 的列都在索引里，不需要回表。

你原始 sql 肯定没用上覆盖索引。因为你的 select 里的 quantity 应该不在索引里吧。你只是因为正好要关联 product 表，就贪婪的把 quantity 带了出来。

所以改写的话，应该是第一个 sql 只 select prod_id: select prod_id from orderitems where number=xxx(手机回复的，无法对着你的字段回复)
然后服务器根据你返回的这个 prod_id 查产品信息，后端经常说:服务端多次单表查询就是这个意思。

我以前一个列表对应的 sql join 了 8,9 表，然后 select 了 10+个字段，那个 sql 要 2000+ms，后来用了覆盖索引，第一次只查那一页的关键 id，只需要 200+ms，然后服务端多次单表查询，服务最后也只要 600ms 左右。

但是你问我要不要把覆盖索引当个圣经或者必须的准则，我觉得 duck 不必。小表之间的小查询直接带出来需要的字段可能只要 5ms，但是你从服务端走一遭，网络请求都要 10+ms，显然直接 select 出来更划算