假设有一张 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
1
admol 3 天前
and sortware.software_id = CONVERT(company_software.software_id , char)
换一下顺序试试 |
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 |
3
wuych 3 天前 via iPhone
子查询的 where 里用了函数之后你的整个子查询就不走索引了吧,你把那个 convert 放到外面呗。
|
4
lyb11232345688 3 天前
数据库是 mysql 吗
|
6
Jxnujason OP @lyb11232345688 是的
|
7
Jxnujason OP 另外如果不使用 in 查询,直接使用等值查询,并不会出现慢查询
|
8
seedhk 3 天前
贴一下执行计划
|
9
CEBBCAT 3 天前
超过慢查询即为慢查询,这代表不了 SQL 的好坏,只是从时间上提供的一种辅助手段。
看 SQL 像是要看在 company_software 登记过但是软件在('china', 'korea', 'england')所有国家都没有推出过的软件 https://gist.github.com/Zhang-Siyang/255336b411a3000133b5486729a75f7f 你改成 IN 试试?原来的 EXIST 感觉每一行都会进行一次 SELECT 展开吧,感觉会是 n^2 |
10
Pythoner666666 3 天前 2
但凡不贴 explain 的帖子,建议大家不要回复。
|
11
fengpan567 3 天前
CONVERT(sortware.software_id , char) = company_software.software_id 导致的慢查询,分两步查吧
|
12
Chinsung 2 天前
你贴个 explain 结果然后说你不理解我都可以理解
|
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 这样会先按索引来一次性筛选,而不是每一条都要去筛选。 |
14
coderzhangsan 2 天前
有一点不明白,为什么 software_id 在一张表是主键 id(int 类型),另一张表则是 varchar 类型,难道不应该数据类型一致吗?开发中,如果不注意的化,查询会导致隐式转换,这就导致你的 SQL 必须用函数转换,因而 SQL 查询变得复杂。
|
15
isnullstring 2 天前
where 语句里使用函数 都是导致索引失效喔
相同字段在不同表定不同类型,一开始写代码赶工期、马虎了事,跟我现在接手的系统一毛一样 |
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 有多少条,有多少条就执行多少次子查询,这样就慢了。 |