V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
amiwrong123
V2EX  ›  MySQL

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

  •  
  •   amiwrong123 · 80 天前 · 1804 次点击
    这是一个创建于 80 天前的主题,其中的信息可能已经有所发展或是发生改变。

    在书中 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;
    

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

    13 条回复    2020-09-07 13:18:04 +08:00
    qiayue
        1
    qiayue   80 天前
    请问你说的更高效是通过哪些指标判断出来的。

    另外,第一个三表连接语句,看起来清晰明确,一眼就知道要查的是订单号为 20005 的订单相关信息。
    你改的,实话说,很难理解。
    amiwrong123
        2
    amiwrong123   80 天前
    其实我是根据书中前面内容瞎改了下,用 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
        3
    amiwrong123   80 天前
    @qiayue 忘 @了
    xupefei
        4
    xupefei   80 天前 via iPhone
    没啥区别。subquery flatten 是 query optimizer 的基本技能。
    zhangysh1995
        5
    zhangysh1995   79 天前
    @amiwrong123 我寻思着原始这条 query 应该可以更快,orderitems 里面 order_num 没有索引,那么 order_num = 20005 应该直接就给全表扫描了?总之这条查询感觉写法挺奇怪的。。
    Mroldx
        6
    Mroldx   79 天前
    这 query optimizer 是啥啊
    CRVV
        7
    CRVV   79 天前
    SQL 是声明式语言而不是命令式语言,也就是你把自己需要什么数据告诉数据库,然后数据库把数据取出来,至于是怎么取的,那是数据库内部的事情,不是用 SQL 写出来的。
    写 SQL 的点在于用精确简洁的方式描述清楚你到底要什么数据,比如前一种写法就很好了。

    虽然这么说,当然存在换一个写法能让查询变快的方法,但是这种事情并不那么常见。
    如果真要做优化,你把两句分别跑一下,如果变快了就是更好了。
    519718366
        8
    519718366   79 天前 via iPhone
    首先要说明的是: 你这两条 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
        9
    amiwrong123   79 天前
    >改写后的 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
        10
    amiwrong123   79 天前 via Android
    519718366
        11
    519718366   79 天前 via iPhone
    @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
        12
    encro   79 天前
    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
        13
    519718366   79 天前 via iPhone   ❤️ 1
    @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 出来更划算
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   4744 人在线   最高记录 5268   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 08:28 · PVG 16:28 · LAX 00:28 · JFK 03:28
    ♥ Do have faith in what you're doing.