询问一个慢查询的问题

[Jxnujason]

假设有一张 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

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

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

[admol]

and sortware.software_id = CONVERT(company_software.software_id , char)

换一下顺序试试

[Debug1998]

1.not exist 性能差
2.CONVERT(sortware.software_id , char) = company_software.software_id)使用函数
3.子查询中 SELECT_TYPE 可能是依赖子查询
建议 EXPLAIN 执行看一下。
参考： https://juejin.cn/post/7432694809904889895

[wuych]

子查询的 where 里用了函数之后你的整个子查询就不走索引了吧，你把那个 convert 放到外面呗。

[lyb11232345688]

数据库是 mysql 吗

[Jxnujason]

@wuych explain 后是有走索引的，但是 rows 数量比较大

[Jxnujason]

@lyb11232345688 是的

[Jxnujason]

另外如果不使用 in 查询，直接使用等值查询，并不会出现慢查询

[seedhk]

贴一下执行计划

[CEBBCAT]

超过慢查询即为慢查询，这代表不了 SQL 的好坏，只是从时间上提供的一种辅助手段。

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

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

你改成 IN 试试？原来的 EXIST 感觉每一行都会进行一次 SELECT 展开吧，感觉会是 n^2

[Pythoner666666]

但凡不贴 explain 的帖子，建议大家不要回复。

[fengpan567]

CONVERT(sortware.software_id , char) = company_software.software_id 导致的慢查询，分两步查吧

[Chinsung]

你贴个 explain 结果然后说你不理解我都可以理解

[redog]

如果我没有弄错的话 是因为 NOT EXISTS 的原因，这里会导致你最后一个 where CONVERT(sortware.software_id , char) = company_software.software_id 这个是无效的，因为在 mysql 看来，这里不用专门去匹配，直接按前面的条件取回 N 条记录，在这 N 条记录里去检查 software_id 就行了。

这样会导致外层查询时子查询里返回的不是一条记录，而是一堆记录，在这一堆记录里去判断 software_id 是否相等，应该试试用 not in 本质是 inner join 这样会先按索引来一次性筛选，而不是每一条都要去筛选。

[coderzhangsan]

有一点不明白，为什么 software_id 在一张表是主键 id(int 类型)，另一张表则是 varchar 类型，难道不应该数据类型一致吗？开发中，如果不注意的化，查询会导致隐式转换，这就导致你的 SQL 必须用函数转换，因而 SQL 查询变得复杂。

[isnullstring]

where 语句里使用函数 都是导致索引失效喔


相同字段在不同表定不同类型，一开始写代码赶工期、马虎了事，跟我现在接手的系统一毛一样

[redog]

另外你的类型不一致，要进行一次手动转换，不然还是会回到 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 有多少条，有多少条就执行多少次子查询，这样就慢了。