Mysql SubQuery优化

Question

我有一个包含450,000条记录的users表。表结构如下：

  UserID Int(11) Primary Key
  Firstname (50) Varchar
  Lastname  (50) Varchar

我还需要检查另外两个表，看看UserID是否在这些表中。(结构略有不同，但表具有相同的UserID)我正在运行下面的这个子查询，并且运行非常慢。感谢第二双眼睛提供了一个新的视角，有助于跑得更快一些……

SELECT 
`Users`.`Firstname`, 
`Users`.`Lastname`, 
`Users`.`UserID` 
FROM `Users`
WHERE `Users`.`UserID` IN  (SELECT `admin`.`UserID` FROM `admin` WHERE `admin`.`UserID`=`User`.`UserID`)
AND `Users`.`UserID` IN  (SELECT `elite`.`UserID` FROM `elite` WHERE `elite`.`UserID`=`Users`.`UserID`)
AND `Users`.`Lastname` LIKE '%smith%'

Answer 1

在MySQL中优化查询时，绝对最重要的事情是确保您有适当的索引。检查您有哪些索引(使用SHOW INDEX)，以及查询正在使用哪些索引(使用EXPLAIN SELECT)。一旦你的表变得很大，索引对于良好的性能是绝对至关重要的。

特别是，您应该确保在所有三个表中的UserID列上都有一个索引。如果适合您的情况，我还建议添加一个外键约束。

但是，如果您已经检查了正确的索引，但仍未获得适当的性能，则可以尝试使用joins而不是subselects：

SELECT 
    Users.Firstname, 
    Users.Lastname, 
    Users.UserID 
FROM Users
JOIN admin USING (UserID)
JOIN elite USING (UserID)
WHERE Users.Lastname LIKE '%smith%'

请注意，LIKE '%smith%'无法有效地使用索引。如果可能，您应该避免使用以通配符开头的类表达式。

Answer 2

我猜你应该使用内部连接，而不是子查询，现在你是从管理表，精英表中获取用户id，并检查用户id必须在用户连接中，继续使用joins...because，看看有什么不同。

Answer 3

从joins开始；

SELECT 
`Users`.`Firstname`, 
`Users`.`Lastname`, 
`Users`.`UserID` 
FROM `Users`
INNER JOIN `admin` ON (`Users`.`UserID` = `admin`.`Users`)
INNER JOIN `elite` ON (`Users`.`UserID` = `elite`.`UserID`)
WHERE `Users`.`Lastname` LIKE '%smith%'

然后检查你的索引。

您还应该避免您的姓氏搜索会很慢，因为它不是锚定在Lastname字符串的开头或结尾。