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

请问除了加索引以外,还可以如何优化 sql 呢?

  •  
  •   nulihuxi · 2019-01-23 14:46:01 +08:00 · 3462 次点击
    这是一个创建于 2109 天前的主题,其中的信息可能已经有所发展或是发生改变。
    需要查询出总数、成功数和失败数渲染到页面,43W 条数据中查询出 600 多条需要查询 9s 以上。后来给所有 where 以及 order by 的字段加上了索引,用 EXPLAIN 检查发现创建了临时表和全表扫描,遂把 distinct 去掉,但效果还是不理想。想请教下还应该如何进行优化呢?
    初始 sql 如下:
    SELECT
    t.business_no AS businessNo,
    t.service_type AS serviceType,
    (
    SELECT
    count(d.datagram_id)
    FROM
    t_issue_plat_datagram d
    WHERE
    t.business_no = d.business_no
    ) AS limitCount,
    (
    SELECT
    count(b.datagram_id)
    FROM
    t_issue_plat_datagram b
    WHERE
    t.business_no = b.business_no
    AND b.batch_status = '2'
    ) AS sucessCount,
    (
    SELECT
    count(c.datagram_id)
    FROM
    t_issue_plat_datagram c
    WHERE
    t.business_no = c.business_no
    AND c.batch_status = '3'
    ) AS falseCount
    FROM
    (
    SELECT DISTINCT
    g.business_no,
    g.service_type
    FROM
    t_issue_plat_datagram g
    WHERE
    1 = 1
    ORDER BY
    g.updated_date DESC
    ) t
    修改后的 sql:
    SELECT
    t.business_no AS businessNo,
    t.service_type AS serviceType,
    (
    SELECT
    count(d.datagram_id)
    FROM
    t_issue_plat_datagram d
    WHERE
    t.business_no = d.business_no
    ) AS limitCount,
    (
    SELECT
    count(b.datagram_id)
    FROM
    t_issue_plat_datagram b
    WHERE
    t.business_no = b.business_no
    AND b.batch_status = '2'
    ) AS sucessCount,
    (
    SELECT
    count(c.datagram_id)
    FROM
    t_issue_plat_datagram c
    WHERE
    t.business_no = c.business_no
    AND c.batch_status = '3'
    ) AS falseCount
    FROM
    t_issue_plat_datagram t
    GROUP BY
    t.business_no
    ORDER BY
    t.updated_date
    6 条回复    2019-01-23 17:27:51 +08:00
    Rush9999
        1
    Rush9999  
       2019-01-23 15:27:01 +08:00
    SELECT
    businessNo,
    serviceType,
    SUM(s.num),
    SUM(
    CASE
    WHEN s.stauts = '2' THEN
    s.num
    END
    ),
    SUM(
    CASE
    WHEN s.stauts = '3' THEN
    s.num
    END
    )
    FROM
    (
    SELECT
    t.business_no AS businessNo,
    t.service_type AS serviceType,
    t.batch_status AS stauts,
    COUNT(t.datagram_id) AS num,
    updated_date AS date
    FROM
    t_issue_plat_datagram t
    GROUP BY
    t.business_no,
    t.batch_status
    ) s
    GROUP BY
    s.businessNo
    ORDER BY
    s.date
    nulihuxi
        2
    nulihuxi  
    OP
       2019-01-23 15:44:28 +08:00
    @Rush9999 十分感谢,查询时间从 10s 减少至 1.5s
    lxy42
        3
    lxy42  
       2019-01-23 16:33:04 +08:00
    SELECT
    business_no,
    service_type,
    COUNT(1) as limitCount,
    COUNT(CASE WHEN batch_status = '2' THEN 1 ELSE 0 END) as successCount,
    COUNT(CASE WHEN batch_status = '3' THEN 1 ELSE 0 END) as falseCount,
    FROM t_issue_plat_datagram
    GROUP BY business_no
    ORDER BY updated_date

    楼主试试这个,不知道是否符合查询需求
    nulihuxi
        4
    nulihuxi  
    OP
       2019-01-23 16:54:24 +08:00
    @lxy42 感谢回复!查询出的条数是对的,不过 limitCount、successCount、falseCount 的值是一样的
    lxy42
        5
    lxy42  
       2019-01-23 16:59:35 +08:00
    SELECT
    business_no,
    service_type,
    COUNT(1) as limitCount,
    COUNT(CASE WHEN batch_status = '2' THEN 1 ELSE NULL END) as successCount,
    COUNT(CASE WHEN batch_status = '3' THEN 1 ELSE NULL END) as falseCount,
    FROM t_issue_plat_datagram
    GROUP BY business_no
    ORDER BY updated_date

    这个呢?小改了一下。
    nulihuxi
        6
    nulihuxi  
    OP
       2019-01-23 17:27:51 +08:00
    @lxy42 这个非常 ok (去掉 as falseCount 后的逗号) :)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2794 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 12:34 · PVG 20:34 · LAX 05:34 · JFK 08:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.