MySQL exists还是慎用

最新推荐文章于 2025-07-04 16:23:31 发布

原创最新推荐文章于 2025-07-04 16:23:31 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

54 篇文章

订阅专栏

本文探讨了在使用MariaDB和MySQL时如何优化SQL查询性能，特别是针对存在大量数据的情况，对比了使用EXISTS子句与JOIN操作的不同执行计划，强调了合理选择查询方式的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

不管是MariaDB还是Oracle的MySQL，如果外面的表很大，要慎用exists。

select version();

+-----------------+
| version() |
+-----------------+
| 10.1.22-MariaDB |
+-----------------+

EXPLAIN SELECT CAC.*
FROM TOP_CFG_ATTRIBUTE_CONFIG CAC
WHERE EXISTS (SELECT 1
FROM TOP_CFG_CONFIG CC, TOP_CFG_ATTRIBUTE CA
WHERE CC.CONFIG_ID = CA.CONFIG_ID
AND CA.ATTRIBUTE_ID = CAC.ATTRIBUTE_ID
AND CC.CONFIG_FULLCODE = 'DISTRIBUTED_LOCK_OPEN_CONFIG'
AND CC.IS_VALID = 1);
id select_type TABLE TYPE KEY key_len ref ROWS Extra
--- ------------------ ------ ------ ----------------------- ------- --------------------- ------ ------------
1 PRIMARY CAC ALL (NULL) (NULL) (NULL) 393 USING WHERE
2 DEPENDENT SUBQUERY CC ref ind_tcc_CONFIG_FULLCODE 768 const 1 USING WHERE
2 DEPENDENT SUBQUERY CA eq_ref PRIMARY 110 CAC.attribute_id 1 USING WHERE

EXPLAIN SELECT CAC.*
FROM TOP_CFG_ATTRIBUTE_CONFIG CAC,TOP_CFG_CONFIG CC, TOP_CFG_ATTRIBUTE CA
WHERE CC.CONFIG_ID = CA.CONFIG_ID
AND CA.ATTRIBUTE_ID = CAC.ATTRIBUTE_ID
AND CC.CONFIG_FULLCODE = 'DISTRIBUTED_LOCK_OPEN_CONFIG'
AND CC.IS_VALID = 1;
id select_type TABLE TYPE KEY key_len ref ROWS Extra
--- ----------- ------ ------ ----------------------- ------- ----------------- ---- -------------
1 SIMPLE CC ref ind_tcc_CONFIG_FULLCODE 768 const 1 USING WHERE
1 SIMPLE CA ref ind_tca_CONFIG_ID 111 CC.config_id 1 USING INDEX
1 SIMPLE CAC ref ind_tcac_ATTRIBUTE_ID 111 CA.attribute_id 1 (NULL)

select version();
+------------+
| version() |
+------------+
| 5.6.37-log |
+------------+
EXPLAIN SELECT CAC.*
FROM TOP_CFG_ATTRIBUTE_CONFIG CAC
WHERE EXISTS (SELECT 1
FROM TOP_CFG_CONFIG CC, TOP_CFG_ATTRIBUTE CA
WHERE CC.CONFIG_ID = CA.CONFIG_ID
AND CA.ATTRIBUTE_ID = CAC.ATTRIBUTE_ID
AND CC.CONFIG_FULLCODE = 'DISTRIBUTED_LOCK_OPEN_CONFIG'
AND CC.IS_VALID = 1);
+----+--------------------+-------+--------+-------------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+-------------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | CAC | ALL | NULL | NULL | NULL | 490 | Using where |
| 2 | DEPENDENT SUBQUERY | CC | ref | ind_tcc_CONFIG_FULLCODE | 768 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | CA | eq_ref | PRIMARY | 326 | CAC.attribute_id | 1 | Using where |
+----+--------------------+-------+--------+-------------------------+---------+-----------------------+------+-------------+

EXPLAIN SELECT CAC.*
FROM TOP_CFG_ATTRIBUTE_CONFIG CAC,TOP_CFG_CONFIG CC, TOP_CFG_ATTRIBUTE CA
WHERE CC.CONFIG_ID = CA.CONFIG_ID
AND CA.ATTRIBUTE_ID = CAC.ATTRIBUTE_ID
AND CC.CONFIG_FULLCODE = 'DISTRIBUTED_LOCK_OPEN_CONFIG'
AND CC.IS_VALID = 1;
+----+-------------+-------+------+------------------------+---------+----------------------+------+-------------+
| id | select_type | table | type |key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+---------+----------------------+------+-------------+
| 1 | SIMPLE | CC | ref |ind_tcc_CONFIG_FULLCODE | 768 | const | 1 | Using where |
| 1 | SIMPLE | CA | ref |ind_tca_CONFIG_ID | 327 | CC.config_id | 1 | Using index |
| 1 | SIMPLE | CAC | ref |ind_tcac_ATTRIBUTE_ID | 327 | CA.attribute_id | 1 | NULL |
+----+-------------+-------+------+------------------------+---------+----------------------+------+-------------+