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

今日遇到的神奇的 sql 优化问题

  •  
  •   zzfer · 2021-10-27 18:56:45 +08:00 · 3066 次点击
    这是一个创建于 1155 天前的主题,其中的信息可能已经有所发展或是发生改变。

    下午测试那边说有个项目列表查询有点慢,于是我就开始排查。列表 sql 长这样(简化版)(好像 v2markdown 格式出问题了)

    select * from (select * from table1 where xxx )

    我一看这 sql 当时觉得问题就在这了,复制粘贴,navicat 执行,0.1 秒?嗯?没问题?那可能是业务有问题,仔细看完业务流程,并打断点执行,发现也并不慢。

    看下日志,发现还执行了分页插件查询的 count 语句,继续复制粘贴,navicat 执行,我靠,居然要四五秒才执行完,万万没想到查询慢的居然是 count 语句,count 语句长这样(简化版)

    select count(*) from (select * from (select * from table1 where xxx) table2 where xxx)

    执行 explain 发现 count 的语句查询行数有好几万行

    想着优化方案既然查询列表语句很快,我在查询结果上加上序号,然后直接 max (序号),这样应该很快

    于是我在子查询加上了 @x:=IFNULL(@x,0)+1 as rownum,执行了下里面的子查询,我发现 rownum 里都是 1 ,我寻思既然没自增,我直接 count ( rownum )试试,一试,嘿 0.1 秒。数量也对。感觉很神奇,下面是 sql

    select count(*) from (select @x:=IFNULL(@x,0)+1 as rownum,table2.* from (select * from table1 where xxx) table2 where xxx)

    我想,那自增既然没生效,我直接改成 1 as rownum 行不行,试了下,不行,语句速度又慢了,下面是 sql

    select count(*) from (select 1 as rownum,table2.* from (select * from table1 where xxx) table2 where xxx)

    我虽然优化了这个问题,但我并不知道原理,因此想问问大佬们,有没有知道原理的?或者遇到过相同情况

    第 1 条附言  ·  2021-10-28 10:10:23 +08:00

    附加一下,优化前的 explain 和优化后的 explain。

    优化前

    select_type table type row filtered
    PRIMARY <derived2> ALL 55859 100.00
    DERIVED t1 ALL 149 10.00
    DERIVED t3 ALL 160 10.00
    DERIVED t2 ALL 703 33.00

    优化后

    select_type table type row filtered
    PRIMARY <derived2> ALL 5585 100.00
    DERIVED t3 ALL 160 10.00
    DERIVED <derived3> ALL 3491 10.00
    DERIVED t1 ALL 149 10.00
    DERIVED t2 ALL 703 33.00
    第 2 条附言  ·  2021-10-28 11:42:44 +08:00
    不知道为什么仅仅是多加了一个查询字段 @x:=IFNULL(@x,0)+1 as rownum ,explain 就边了这么多,多了一个临时表,数量上变化也很大。
    9 条回复    2024-03-05 15:51:22 +08:00
    2i2Re2PLMaDnghL
        1
    2i2Re2PLMaDnghL  
       2021-10-27 19:17:38 +08:00
    0. SQL 是声明式语言,其优化依赖于具体实现。你的数据库和版本号也建议发出来
    1. 显然这时候我们首先需要分别 explain 一下
    Sasasu
        2
    Sasasu  
       2021-10-27 20:36:05 +08:00
    > 执行 explain 发现 count 的语句查询行数有好几万行
    > @x:=IFNULL(@x,0)+1 as rownum ,执行了下里面的子查询,直接 count(rownum ) 0.1 秒。数量也对

    你最后结果是好几万么?
    evilboy
        3
    evilboy  
       2021-10-27 20:46:38 +08:00
    查一个表用得着这么嵌套吗?
    heliotrope
        4
    heliotrope  
       2021-10-28 09:36:00 +08:00
    mark 一下

    count 就是会比较慢 特别是有条件的时候

    offset 数量一多也慢的不行

    搜出来的解决方案都是什么 先查出 id 再 in id 根本就不可行
    zzfer
        5
    zzfer  
    OP
       2021-10-28 09:52:34 +08:00
    @2i2Re2PLMaDnghL 数据库版本就是 mysql5.7 。我分别 explain 了,查询慢的,count 的时候 是好几万。优化过后的 count 只有几千。

    @Sasasu 最后的结果才一百多

    @evilboy 不止一个表,具体业务不是我负责的,我没了解,负责这的同事休婚假去了

    @heliotrope 我这次优化就很奇怪,就多加了一行查询字段,就变快了
    liangcj
        6
    liangcj  
       307 天前
    我也遇到和你同样的问题,但是我在字查询中加上了自增序号,外面用了 count ( rownum ),其执行计划还是和之前一样。复现不出你这种多了一个临时表的情况。
    zzfer
        7
    zzfer  
    OP
       305 天前
    @liangcj 解决了吗,太久了我也忘了
    liangcj
        8
    liangcj  
       296 天前
    @zzfer 没,解决不了。难顶
    zzfer
        9
    zzfer  
    OP
       295 天前
    @liangcj 问问 gpt 吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   995 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 21:03 · PVG 05:03 · LAX 13:03 · JFK 16:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.