mysql - 解释 MySQL 连接性能
问题描述
我有一些 mySQL 表,它们反映了 BlogPost、User 之间的关系以及 User 和 BlogPost 之间的交互
我写了一些查询来获取用户没有交互过的所有帖子。此查询是从 EntityFramework 生成的
_dbContext.Post.Where(!p.UserPostInteractions.Any(u => u.User.IndexedProperty == "SomeSpecificString")
SQL
SELECT COUNT(*)
FROM `Post` AS `p`
WHERE ( NOT EXISTS (
SELECT 1
FROM `UserPostInteractions` AS `u`
LEFT JOIN `Users` AS `u.User` ON `u`.`UserId` = `u.User`.`Id`
WHERE (`u.User`.`IndexedPropertyOnUserTable` = 'SomeSpecificString') AND (`p`.`Id` = `u`.`PostId`)) )
此查询运行 3.8 秒。说明表如下
+---+--------------------+--------+-------+---------------------------------------------------------------+--------------------------------+-----+-------------------+------+--------------------------+
| 1 | PRIMARY | p | index | | IX_Post_ParentPostId | 5 | | 7261 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | u.User | ref | PRIMARY,IX_Users_IndexedProperty | IX_Users_IndexedProperty | 383 | const | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | u | ref | IX_UserPostInteractions_PostId,IX_UserPostInteractions_UserId | IX_UserPostInteractions_UserId | 5 | db-name.u.User.Id | 17 | Using where |
+---+--------------------+--------+-------+---------------------------------------------------------------+--------------------------------+-----+-------------------+------+--------------------------+
我试图将其修改为
SELECT COUNT(*)
FROM `Post` AS `p`
WHERE ( NOT EXISTS (
SELECT 1
FROM `UserPostInteractions` AS `u`
LEFT JOIN `Users` AS `u.User` ON `u`.`UserId` = `u.User`.`Id`
WHERE (`u.User`.`Id` = 'usingIdInstead') AND (`p`.`Id` = `u`.`PostId`)) )
此查询运行 0.6 秒
这有什么不正常的吗?我可以做些什么来提高这个查询的性能?
解决方案
这是同一件事的另一种说法:
SELECT COUNT(*)
FROM post p
LEFT
JOIN (
SELECT up.postid
FROM userpostinteractions up
JOIN users u
ON up.userid = u.id
WHERE u.indexedpropertyonusertable = 'SomeSpecificString'
) x
ON p.id = x.postid
WHERE x.postid IS NULL
推荐阅读
- javascript - 提取字符串的特定部分
- blazor - 为什么 ValidationMessage 组件在使用 EditContext 时在 Blazor wasm 中不显示错误消息
- html - 如果输入类型是数字,如何更改输入框大小?
- reactjs - React hook 有不必要的依赖
- amazon-web-services - Cloudfront 访问被拒绝
- php - 使用 LDAP 时 XAMPP 需要强(更)身份验证
- python - 如何结合这两个领域?
- java - Java 循环未关闭
- umbraco - Umbraco 未发布的内容后备?
- android - 准备exoplayer时小懒惰