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

询问一个慢查询的问题

  •  
  •   Jxnujason · 3 天前 · 1222 次点击

    假设有一张 software 表,表结构如下

    software_id (int) author_id (int) create_time update_time
    1 1 2024-11-05 11:00:00 2024-11-05 11:00:00
    2 2 2024-11-05 11:00:00 2024-11-05 11:00:00

    其中 software_id 是主键,author_id 是普通索引

    还有一张 company_software 表,表结构如下

    company_software_id (int) author_id (int) country(varchar) software_id (varchar)
    1 1 china 1
    2 1 china kkk

    其中 company_software_id 是主键,author_id 、country 、software_id 是一个复合索引。

    以上表数据量只有 1~5w 。

    1 、SQL1

    SELECT 1
    FROM company_software AS company_software
    WHERE company_software.author_id  = 1
      and company_software.country  in ('china', 'korea', 'england')
      and company_software.software_id    = '1'
    

    并不会导致慢查询

    2 、SQL2

    SELECT software_id
    FROM sortware
    WHERE author_id  = 1
      and NOT EXISTS (SELECT 1
                      FROM company_software AS company_software
                      WHERE company_software.author_id = 1
                        and company_software.country in ('china', 'korea', 'england')
                        and CONVERT(sortware.software_id , char) = company_software.software_id)
    LIMIT 0, 100
    

    为什么会导致慢查询,懂的大佬帮忙分析下

    第 1 条附言  ·  3 天前

    SQL

    explain SELECT software_id
    FROM software
    WHERE author_id  = 1
      and NOT EXISTS (SELECT 1
                      FROM company_software AS company_software
                      WHERE company_software.author_id = 1
                        and company_software.country in ('china', 'korea', 'england')
                        and CONVERT(software.software_id , char) = company_software.software_id)
    LIMIT 0, 100;
    
    id select_type table partitions type possible_keys key key_len ref rows filtered Extra
    1 PRIMARY software ref idx_authorid idx_authorid 5 const 1 100 Using where; Using index
    2 DEPENDENT SUBQUERY company_software ref idx_authorid_country_softwareid idx_authorid_country_softwareid 5 const 8 50 Using where; Using index

    software只有一条数据,company_software有八条数据,country都是china

    16 条回复    2024-11-07 14:16:49 +08:00
    admol
        1
    admol  
       3 天前
    and sortware.software_id = CONVERT(company_software.software_id , char)

    换一下顺序试试
    Debug1998
        2
    Debug1998  
       3 天前
    1.not exist 性能差
    2.CONVERT(sortware.software_id , char) = company_software.software_id)使用函数
    3.子查询中 SELECT_TYPE 可能是依赖子查询
    建议 EXPLAIN 执行看一下。
    参考: https://juejin.cn/post/7432694809904889895
    wuych
        3
    wuych  
       3 天前 via iPhone
    子查询的 where 里用了函数之后你的整个子查询就不走索引了吧,你把那个 convert 放到外面呗。
    lyb11232345688
        4
    lyb11232345688  
       3 天前
    数据库是 mysql 吗
    Jxnujason
        5
    Jxnujason  
    OP
       3 天前
    @wuych explain 后是有走索引的,但是 rows 数量比较大
    Jxnujason
        6
    Jxnujason  
    OP
       3 天前
    Jxnujason
        7
    Jxnujason  
    OP
       3 天前
    另外如果不使用 in 查询,直接使用等值查询,并不会出现慢查询
    seedhk
        8
    seedhk  
       3 天前
    贴一下执行计划
    CEBBCAT
        9
    CEBBCAT  
       3 天前
    超过慢查询即为慢查询,这代表不了 SQL 的好坏,只是从时间上提供的一种辅助手段。

    看 SQL 像是要看在 company_software 登记过但是软件在('china', 'korea', 'england')所有国家都没有推出过的软件

    https://gist.github.com/Zhang-Siyang/255336b411a3000133b5486729a75f7f

    你改成 IN 试试?原来的 EXIST 感觉每一行都会进行一次 SELECT 展开吧,感觉会是 n^2
    Pythoner666666
        10
    Pythoner666666  
       3 天前   ❤️ 2
    但凡不贴 explain 的帖子,建议大家不要回复。
    fengpan567
        11
    fengpan567  
       3 天前
    CONVERT(sortware.software_id , char) = company_software.software_id 导致的慢查询,分两步查吧
    Chinsung
        12
    Chinsung  
       2 天前
    你贴个 explain 结果然后说你不理解我都可以理解
    redog
        13
    redog  
       2 天前
    如果我没有弄错的话 是因为 NOT EXISTS 的原因,这里会导致你最后一个 where CONVERT(sortware.software_id , char) = company_software.software_id 这个是无效的,因为在 mysql 看来,这里不用专门去匹配,直接按前面的条件取回 N 条记录,在这 N 条记录里去检查 software_id 就行了。

    这样会导致外层查询时子查询里返回的不是一条记录,而是一堆记录,在这一堆记录里去判断 software_id 是否相等,应该试试用 not in 本质是 inner join 这样会先按索引来一次性筛选,而不是每一条都要去筛选。
    coderzhangsan
        14
    coderzhangsan  
       2 天前
    有一点不明白,为什么 software_id 在一张表是主键 id(int 类型),另一张表则是 varchar 类型,难道不应该数据类型一致吗?开发中,如果不注意的化,查询会导致隐式转换,这就导致你的 SQL 必须用函数转换,因而 SQL 查询变得复杂。
    isnullstring
        15
    isnullstring  
       2 天前
    where 语句里使用函数 都是导致索引失效喔


    相同字段在不同表定不同类型,一开始写代码赶工期、马虎了事,跟我现在接手的系统一毛一样
    redog
        16
    redog  
       1 天前
    另外你的类型不一致,要进行一次手动转换,不然还是会回到 DEPENDENT SUBQUERY ,具体的话,你应该试试:
    SELECT software_id from software where author_id = 1 and CONVERT(software_id,char) not in ( select software_id FROM company_software WHERE author_id = 1 and country in ('china', 'korea', 'england') )
    这样在 mysql 看来子查询是独立于主查询的,这样只会执行一次子查询,上面都使用了索引,如果你不使用 convert 去转换,因为类型不一致,mysql 又会先看 author_id 有多少条,有多少条就执行多少次子查询,这样就慢了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2448 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 15:42 · PVG 23:42 · LAX 07:42 · JFK 10:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.