php - 我需要添加哪些索引?
问题描述
我正在尝试加快此查询的速度,并且除了最后一部分之外,我拥有所需的所有索引/外键。
查看该type
列,我可以看到它pb
正在查看all
我需要纠正的记录。
我想知道您是否可以帮我弄清楚我需要哪些其他索引/FK,当然,如果您有任何一般性的指示,请告诉我。
询问:
explain SELECT
*
FROM `Publish` AS pb
LEFT JOIN Positions AS p ON pb.`Position_ID` = p.`Position_ID`
LEFT JOIN PositionDetails AS pd ON pb.PositionDetail_ID = pd.PositionDetail_ID
LEFT JOIN Posts AS ps ON pb.`Post_ID` = ps.`Post_ID`
INNER JOIN Users AS u ON pb.`User_ID` = u.`User_ID`
LEFT JOIN PublishWatch AS pw ON (p.`Position_ID` = pw.`Position_ID` OR ps.Post_ID = pw.Post_ID ) AND 1=pw.`User_ID`
LEFT JOIN UserFollow AS uf ON pb.User_ID = uf.FollowUser_ID AND 1= uf.`User_ID`
ORDER BY pb.Published desc
limit 0,50
解释输出:
完整的原始查询(在 php 中):
$StrategiesWhere1 = "";
if (isset($_POST["Strategies"]) && sizeof($_POST["Strategies"]) > 0) {
$in1 = "";
$whereIn = [];
foreach ($_POST["Strategies"] as $i => $item) {
$key1 = ":1id" . $i;
$param[$key1] = $item; // collecting values into key-value array
array_push($whereIn, $key1);
}
$StrategiesWhere1 = "AND p.PositionType_ID in (" . implode(",", $whereIn) . ")";
}
$sql = "SELECT
pb.User_ID AS USERID,
p.*,
p.CommentCount as PositionCommentCount,
p.WatchCount as PositionWatchCount,
n.Note,
pt.Strategy,
pt.isUndefinedRisk,
a.Name,
u.Display,
u.AllPosts as UserAllPosts,
u.AllPositions as UserAllPositions,
u.AllComments as UserAllComments,
u.isPro,
u.isSupporter,
ps.*,
ps.CommentCount as PostCommentCount,
ps.WatchCount as PostWatchCount,
pb.Published,
pb.Publish_ID,
pw.PublishWatch_ID,
pw.AllComments AS PublishWatchAllComments,
pd.isClosing,
pd.isOpening,
pd.Price,
uf.AllComments AS UserFollowAllComments,
uf.FollowUser_ID,
uf.UserFollow_ID
FROM `Publish` AS pb
LEFT JOIN Positions AS p ON pb.`Position_ID` = p.`Position_ID`
LEFT JOIN PositionDetails AS pd ON pb.PositionDetail_ID = pd.PositionDetail_ID
LEFT JOIN Posts AS ps ON pb.`Post_ID` = ps.`Post_ID`
LEFT JOIN PositionTypes AS pt ON p.`PositionType_ID` = pt.`PositionType_ID`
LEFT JOIN Accounts AS a ON p.`Account_ID` = a.`Account_ID`
INNER JOIN Users AS u ON pb.`User_ID` = u.`User_ID`
LEFT JOIN Notes AS n ON p.`LastestNote_ID` = n.`Note_ID`
LEFT JOIN PublishWatch AS pw ON (pb.`Position_ID` = pw.`Position_ID` OR pb.Post_ID = pw.Post_ID ) AND :User_ID4 = pw.`User_ID`
LEFT JOIN UserFollow AS uf ON pb.User_ID = uf.FollowUser_ID AND :User_ID5 = uf.`User_ID`
WHERE
(
(
:Type1 in ('Both','Only Positions')
AND p.`Position_ID` IS NOT NULL
AND p.`Security` LIKE :Security
" . $StrategiesWhere1 . "
)
OR
(
p.`Position_ID` IS NULL
)
)
-- Posts Filters
AND
(
(
:Type2 in ('Both','Only Posts')
AND ps.Post_ID IS NOT NULL
AND (
ps.Post like :PostContains1
OR ps.Subject like :PostContains2
)
OR
(
ps.Post_ID IS NULL
)
)
)
-- User Display name
AND
(
(
:Display1 != ''
AND u.Display = :Display2
)
OR
(
:Display3 = ''
)
)
-- Account
AND
(
(
:Account1 != ''
AND a.Name = :Account2
)
OR
(
:Account3 = ''
)
)
-- Only Watching
AND
(
(
:Watching1 = 'Both'
)
OR
(
:Watching2 = 'Only Watched'
AND pw.PublishWatch_ID IS NOT NULL
)
OR
(
:Watching3 = 'Only Unwatched'
AND pw.PublishWatch_ID IS NULL
)
)
-- Pro Limitations
AND
(
(
-- If this is pro they get everything
:isPro1 = 1
)
OR
(
-- Or if this isnt me
u.User_ID != 1
)
OR
(
-- If this is me and its on the small account
-- or a post
u.User_ID = 1
AND
( a.Account_ID = 1
OR
pb.Position_ID is null)
)
OR
(
-- If this is not pro and this is my publish and it isnt the small account or post
-- only show closing trade
u.User_ID = 1
AND a.Account_ID != 1
AND p.Closed is not null
)
)
ORDER BY pb.Published desc
limit :Offset,:Count;
";
使用 Thorsten 提供的密钥更新说明:
解决方案
首先,我会将连接条件放在它们所属Positions
的子句上:Posts
ON
LEFT JOIN Positions AS p ON pb.Position_ID = p.Position_ID
AND :Type1 in ('Both', 'Only Positions')
AND p.Security LIKE :Security
" . $StrategiesWhere1 . "
LEFT JOIN Posts AS ps ON pb.Post_ID = ps.Post_ID
AND :Type2 in ('Both', 'Only Posts')
AND
(
ps.Post like :PostContains1
OR
ps.Subject like :PostContains2
)
对我来说看起来有点可疑的一件事:您选择Positions
和PositionDetails
,但两者似乎并不相关。从表名中,我希望您只想要PositionDetails
与 相同Position_ID
的Positions
,但可能PositionDetails
没有Position_ID
. 这可能是一个问题,如果是,我不确定这是否真的是查询的问题,或者更确切地说是数据模型的问题。
至于性能;这个
ORDER BY pb.Published desc
limit :Offset,:Count;
可能很慢。这样做是获取整个结果集(我不知道在您的情况下这是数十行、数百行还是数千行),然后对整个结果集进行排序以获得所需的子集。没关系,如果您想要前 50 行或后 50 行或最后 50 行;DBMS 将始终必须选择完整的数据集并对其进行排序以返回所需的行。
您的所有联接仅将一个表行连接到Publish
一行,但PublishWatch
同一用户可以同时查看位置和帖子的情况除外,在这种情况下,您将获得一行的两个结果行,Publish
而不是仅一行。因此,您可以请求接下来的五十个Published
日期/时间,而不是请求接下来的五十行:
WHERE pb.Published > :last_max_published
ORDER BY pb.Published desc
limit :Count;
因此,您将始终只查看剩余的行,而不必对您已经处理过的所有行进行排序。
至于索引,您可以尝试以下方法。其中一些是覆盖索引,即它们包含查询中使用的所有列。这意味着如果 DBMS 使用这些索引,它就不必读取表。您可以提供所有这些索引,然后检查使用了哪些索引,然后删除其他索引。
CREATE INDEX idx01 ON Users(Display, User_ID);
CREATE INDEX idx02 ON Accounts(Name, User_ID, Account_ID);
CREATE INDEX idx03 ON Positions(Position_ID, Closed);
CREATE INDEX idx04 ON PublishWatch(User_ID, Position_ID, Post_ID, PublishWatch_ID, AllComments);
CREATE INDEX idx05 ON UserFollow(User_ID, FollowUser_ID);
CREATE INDEX idx06 ON UserFollow(FollowUser_ID, User_ID);
CREATE INDEX idx07 ON Positions(Position_ID, Security);
CREATE INDEX idx08 ON Posts(Post_ID, Post, Subject);
CREATE INDEX idx09 ON Accounts(Account_ID, Name);
CREATE INDEX idx10 ON Notes(Note_ID, Note);
CREATE INDEX idx11 ON PositionTypes(PositionType_ID, isUndefinedRisk, Strategy);
CREATE INDEX idx12 ON Publish(Published);