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
mechille
V2EX  ›  MySQL

一条多表联查SQL语句优化的问题

  •  
  •   mechille · 2014-01-23 18:21:39 +08:00 · 4191 次点击
    这是一个创建于 3941 天前的主题,其中的信息可能已经有所发展或是发生改变。
    SELECT *,
    (SELECT name FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as name,
    (SELECT detail FROM tbl_b WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2 ORDER BY date DESC LIMIT 1) as detail
    FROM tbl_a
    WHERE id1='1';

    暂且无视那个*号。请问这个语句怎么优化到最佳
    9 条回复    1970-01-01 08:00:00 +08:00
    lenmore
        1
    lenmore  
       2014-01-23 18:38:14 +08:00
    SQL Server可以这样写的,减少一半IO:
    SELECT *
    FROM tbl_a
    CROSS APPLY (SELECT TOP (1) name, detail FROM tbl_b WHERE id1=tbl_a.id1 AND id2=tbl_a.id2 ORDER BY date DESC) AS T
    WHERE id1='1'
    cxe2v
        2
    cxe2v  
       2014-01-23 21:00:30 +08:00
    我能看出来的也就是楼上那种,把后面两个括号里的select写成一句
    mechille
        3
    mechille  
    OP
       2014-01-23 22:08:13 +08:00
    @lenmore 呃,如果是MYSQL呢,貌似MYSQL没有CROSS APPLY这样的语句
    yangqi
        4
    yangqi  
       2014-01-23 22:47:46 +08:00
    你这不就是一个简单的Join么?还是我理解错了?

    SELECT tbl_a.*, tbl_b.name, tbl_b.detail
    FROM tbl_a
    JOIN tbl_b ON tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
    WHERE tbl_a.id1='1'
    ORDER BY tbl_b.date DESC LIMIT 1
    cjjer
        5
    cjjer  
       2014-01-24 00:32:09 +08:00
    @yangqi

    仿佛你理解错了。

    楼主获取的是top 1,多条记录的话就。。。

    我不折腾的试了。

    楼主可以试试先主表 inner join一下得到表x,然后主表left join这个表x。。。。
    yangqi
        6
    yangqi  
       2014-01-24 00:38:45 +08:00
    @cjjer 没错啊,我给出的query里也是top 1 order by date啊。。。感觉楼主简单问题复杂化了吧。。。
    yangqi
        7
    yangqi  
       2014-01-24 00:47:54 +08:00
    @cjjer 好吧,之前是理解错了,楼主中间俩个select为啥要分开,其实就是一条query

    SELECT *, tmp.name, tmp.detail
    FROM tbl_a,
    (SELECT name, detail
    FROM tbl_b
    WHERE tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
    ORDER BY date DESC LIMIT 1
    ) as tmp,
    WHERE tbl_a.id1='1';
    mechille
        8
    mechille  
    OP
       2014-01-24 10:24:41 +08:00
    @yangqi

    严格按照你这个语句写的,结果

    1054 Unknown column 'tbl_a.id1' in 'where clause'
    yangqi
        9
    yangqi  
       2014-01-24 12:55:44 +08:00
    @mechille

    SELECT *, tmp.name, tmp.detail
    FROM tbl_a,
    (SELECT name, detail
    FROM tbl_b
    JOIN tbl_a
    ON tbl_b.id1 = tbl_a.id1 AND tbl_b.id2 = tbl_a.id2
    ORDER BY date DESC LIMIT 1
    ) as tmp
    WHERE tbl_a.id1='1';
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1510 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 23:56 · PVG 07:56 · LAX 15:56 · JFK 18:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.