mysql - 尝试为 MySQL 查询创建索引

Question

我在 MySQL 和一般数据库方面还没有很多经验，尽管我正准备开发一个大型网络应用程序。以下是我的应用程序的搜索查询，允许用户搜索其他用户。现在此查询的主表 dev_Profile 有大约 14K 行，查询速度相当慢(运行返回可能的最大集合的查询时大约 5 秒)。我敢肯定这里可以进行很多优化调整，但是创建索引是最基本的第一步吗？我首先尝试自己学习索引，以及如何为具有多个连接的查询创建索引，但我只是不太了解它。我希望在我的实际查询的上下文中查看事物会更有教育意义。

这是基本查询:

SELECT 
  dev_Profile.ID AS pid,
  dev_Profile.Name AS username,
  IF(TIMESTAMPDIFF(SECOND, st1.lastActivityTime, UTC_TIMESTAMP()) > 300 OR ISNULL(TIMESTAMPDIFF(SECOND, st1.lastActivityTime, UTC_TIMESTAMP())), 0, 1) AS online, 
  FLOOR(DATEDIFF(CURRENT_DATE, dev_Profile.DOB) / 365) AS age,
  IF(dev_Profile.GenderID=1, 'M', 'F') AS sex, 
  IF(ISNULL(st2.Description), 0, st2.Description) AS relStatus, 
  st3.Name AS country, 
  IF(dev_Profile.RegionID > 0, st4.Name, 0) AS region, 
  IF(dev_Profile.CityID > 0, st5.Name, 0) AS city, 
  IF(ISNULL(st6.filename), 0, IF(st6.isApproved=1 AND st6.isDiscarded=0 AND st6.isModerated=1 AND st6.isRejected=0 AND isSizeAvatar=1, 1, 0)) AS hasPhoto, 
  IF(ISNULL(st6.filename), IF(dev_Profile.GenderID=1, 'http://www.mysite.com/lib/images/avatar-male-small.png', 'http://www.mysite.com/lib/images/avatar-female-small.png'), IF(st6.isApproved=1 AND st6.isDiscarded=0 AND st6.isModerated=1 AND st6.isRejected=0 AND isSizeAvatar=1, CONCAT('http://www.mysite.com/uploads/', st6.filename), IF(dev_Profile.GenderID=1, 'http://www.mysite.com/lib/images/avatar-male-small.png', 'http://www.mysite.com/lib/images/avatar-female-small.png'))) AS photo,
  IF(ISNULL(dev_Profile.StatusMessage), IF(ISNULL(dev_Profile.AboutMe), IF(ISNULL(st7.AboutMyMatch), 0, st7.AboutMyMatch), dev_Profile.AboutMe), dev_Profile.StatusMessage) AS text
FROM 
  dev_Profile 
  LEFT JOIN dev_User AS st1 ON st1.ID = dev_Profile.UserID 
  LEFT JOIN dev_ProfileRelationshipStatus AS st2 ON st2.ID = dev_Profile.ProfileRelationshipStatusID 
  LEFT JOIN Country AS st3 ON st3.ID = dev_Profile.CountryID 
  LEFT JOIN Region AS st4 ON st4.ID = dev_Profile.RegionID 
  LEFT JOIN City AS st5 ON st5.ID = dev_Profile.CityID 
  LEFT JOIN dev_Photos AS st6 ON st6.ID = dev_Profile.PhotoAvatarID 
  LEFT JOIN dev_DesiredMatch AS st7 ON st7.ProfileID = dev_Profile.ID
WHERE 
  dev_Profile.ID != 11222 /* $_SESSION['ProfileID'] */
  AND st1.EmailVerified = 'true'
  AND st1.accountIsActive=1
ORDER BY st1.lastActivityTime DESC LIMIT 900;

此查询的速度(如您所见，太慢了):

900 rows in set (5.20 sec)

此查询的 EXPLAIN:

+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                                         | rows  | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | dev_Profile | range  | PRIMARY       | PRIMARY | 4       | NULL                                        | 13503 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | st2         | eq_ref | PRIMARY       | PRIMARY | 1       | syk.dev_Profile.ProfileRelationshipStatusID |     1 |                                              |
|  1 | SIMPLE      | st3         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.CountryID                   |     1 |                                              |
|  1 | SIMPLE      | st4         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.RegionID                    |     1 |                                              |
|  1 | SIMPLE      | st5         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.CityID                      |     1 |                                              |
|  1 | SIMPLE      | st1         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.UserID                      |     1 | Using where                                  |
|  1 | SIMPLE      | st6         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.PhotoAvatarID               |     1 |                                              |
|  1 | SIMPLE      | st7         | ALL    | NULL          | NULL    | NULL    | NULL                                        |   442 |                                              |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+

如果用户的搜索包含额外的条件，查询也可以有更多的 WHERE 和 HAVING 子句。附加子句是(使用示例值设置):

AND dev_Profile.GenderID = 1 
AND dev_Profile.CountryID=127
AND dev_Profile.RegionID=36
AND dev_Profile.CityID=601
HAVING (age >= 18 AND age <= 50)
AND online=1 
AND hasPhoto=1 

这是使用所有可能的 WHERE 和 HAVING 子句的查询的 EXPLAIN:

+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                                         | rows  | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | dev_Profile | range  | PRIMARY       | PRIMARY | 4       | NULL                                        | 13503 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | st2         | eq_ref | PRIMARY       | PRIMARY | 1       | syk.dev_Profile.ProfileRelationshipStatusID |     1 |                                              |
|  1 | SIMPLE      | st3         | const  | PRIMARY       | PRIMARY | 4       | const                                       |     1 |                                              |
|  1 | SIMPLE      | st4         | const  | PRIMARY       | PRIMARY | 4       | const                                       |     1 |                                              |
|  1 | SIMPLE      | st5         | const  | PRIMARY       | PRIMARY | 4       | const                                       |     1 |                                              |
|  1 | SIMPLE      | st1         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.UserID                      |     1 | Using where                                  |
|  1 | SIMPLE      | st6         | eq_ref | PRIMARY       | PRIMARY | 4       | syk.dev_Profile.PhotoAvatarID               |     1 |                                              |
|  1 | SIMPLE      | st7         | ALL    | NULL          | NULL    | NULL    | NULL                                        |   442 |                                              |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------------------------------+-------+----------------------------------------------+

我什至不确定这是 TMI 还是不够。

在这里采用索引是正确的步骤吗？如果是这样，有人可以让我朝着正确的方向前进吗？

Answer 1

正确的步骤是加快您的查询速度!

对于您的原始查询，我会说您最终对 dev_Profile 表进行了表扫描，因为它没有可索引的条件。对于修改后的查询，它取决于列中允许的不同值的数量 - 如果可能有重复值，则索引可能不会被使用，因为无论如何它都必须获取表才能完成其余查询。

我已经正确阅读了您的计划，然后您已经将所有其他表加入到索引的非可空列上(st7 除外，由于某种原因它似乎没有使用索引)。因此看起来您不应该使用左连接。这将允许在表 st1 上使用 (EmailVerified, accountIsActive, lastActivityTime) 的索引。