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

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; 

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) 

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 出来更划算