我有一个相对较大的 4-deep 关系数据设置，如下所示:

ClientApplication         has_many => ClientApplicationVersions
ClientApplicationVersions has_many => CloudLogs
CloudLogs                 has_many => Logs

client_applications:(可能有 1,000 条记录)
- ...
- account_id
- public_key
- deleted_at

client_application_versions:(可能有 10,000 条记录)
- ...
- client_application_id
- public_key
- deleted_at

cloud_logs:(可能有 1,000,000 条记录)
- ...
- client_application_version_id
- public_key
- deleted_at

日志:(可能有 1,000,000,000 条记录)
- ...
- cloud_log_id
- public_key
- 时间戳
- deleted_at

我仍在开发中，所以结构和设置不是一成不变的，但我希望它设置正常。使用 Rails 3.2.11 和 InnoDB MySQL。数据库当前充满了一小部分(与最终的数据库大小相比)数据集(logs 只有 500,000 行)我有 4 个范围查询，其中 3 个有问题，用于检索日志。

1. 抓取第一页日志，按时间戳排序，受account_id、client_application.public_key、client_application_version.public_key限制(超过100秒)
2. 抓取第一页日志，按时间戳排序，受account_id、client_application.public_key限制(超过100秒)
3. 抓取第一页日志，按时间戳排序，受account_id限制(超过100秒)
4. 获取日志的第一页，按时间戳排序(~2 秒)

我正在使用 rails scopes 来帮助进行这些调用:

  scope :account_id, proc {|account_id| joins(:client_application).where("client_applications.account_id = ?", account_id) }
  scope :client_application_key, proc {|client_application_key| joins(:client_application).where("client_applications.public_key = ?", client_application_key) }
  scope :client_application_version_key, proc {|client_application_version_key| joins(:client_application_version).where("client_application_versions.public_key = ?", client_application_version_key) }

  default_scope order('logs.timestamp DESC')

我在 public_key 上的每个表上都有索引。我在 logs 表上有几个索引，包括优化器喜欢使用的索引 (index_logs_on_cloud_log_id)，但查询仍然需要很长时间才能运行。

下面是我在 rails console 中调用方法的方式:

Log.account_id(1).client_application_key('p0kZudG0').client_application_version_key('0HgoJRyE').page(1)

...这是 Rails 将它变成的样子:

SELECT `logs`.* FROM `logs` INNER JOIN `cloud_logs` ON `cloud_logs`.`id` = `logs`.`cloud_log_id` INNER JOIN `client_application_versions` ON `client_application_versions`.`id` = `cloud_logs`.`client_application_version_id` INNER JOIN `client_applications` ON `client_applications`.`id` = `client_application_versions`.`client_application_id` INNER JOIN `cloud_logs` `cloud_logs_logs_join` ON `cloud_logs_logs_join`.`id` = `logs`.`cloud_log_id` INNER JOIN `client_application_versions` `client_application_versions_logs` ON `client_application_versions_logs`.`id` = `cloud_logs_logs_join`.`client_application_version_id` WHERE (logs.deleted_at IS NULL) AND (client_applications.account_id = 1) AND (client_applications.public_key = 'p0kZudG0') AND (client_application_versions.public_key = '0HgoJRyE') ORDER BY logs.timestamp DESC LIMIT 100 OFFSET 0

... 这是该查询的 EXPLAIN 语句。

+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                            | type   | possible_keys                                                                                                                                         | key                                               | key_len | ref                                                                    | rows | Extra                                        |
+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | client_application_versions      | ref    | PRIMARY,index_client_application_versions_on_client_application_id,index_client_application_versions_on_public_key                                    | index_client_application_versions_on_public_key   | 768     | const                                                                  |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | client_applications              | eq_ref | PRIMARY,index_client_applications_on_account_id,index_client_applications_on_public_key                                                               | PRIMARY                                           | 4       | cloudlog_production.client_application_versions.client_application_id  |    1 | Using where                                  |
|  1 | SIMPLE      | cloud_logs                       | ref    | PRIMARY,index_cloud_logs_on_client_application_version_id                                                                                             | index_cloud_logs_on_client_application_version_id | 5       | cloudlog_production.client_application_versions.id                     |  481 | Using where; Using index                     |
|  1 | SIMPLE      | cloud_logs_logs_join             | eq_ref | PRIMARY,index_cloud_logs_on_client_application_version_id                                                                                             | PRIMARY                                           | 4       | cloudlog_production.cloud_logs.id                                      |    1 |                                              |
|  1 | SIMPLE      | client_application_versions_logs | eq_ref | PRIMARY                                                                                                                                               | PRIMARY                                           | 4       | cloudlog_production.cloud_logs_logs_join.client_application_version_id |    1 | Using index                                  |
|  1 | SIMPLE      | logs                             | ref    | index_logs_on_cloud_log_id_and_deleted_at_and_timestamp,index_logs_on_cloud_log_id_and_deleted_at,index_logs_on_cloud_log_id,index_logs_on_deleted_at | index_logs_on_cloud_log_id                        | 5       | cloudlog_production.cloud_logs.id                                      |    4 | Using where                                  |
+----+-------------+----------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+------------------------------------------------------------------------+------+----------------------------------------------+

这个问题有 3 个部分:

1. 我能否使用额外的索引优化我的数据库，以帮助这些类型的依赖于连接的排序查询变得更高效？
2. 我能否优化 Rails 代码以帮助这种类型的 find 以更高效的方式运行？
3. 我是否只是以错误的方式处理大型数据集？

12 年 1 月 24 日更新
正如 Geoff 和 J_MCCaffrey 在答案中所建议的那样，我将查询分成 3 个不同的部分以尝试隔离问题。不出所料，这是处理最大表的问题。 MYSQL 优化器通过使用不同的索引以不同方式处理此问题，但延迟仍然存在。这是此方法的解释。

ClientApplication.find_by_account_id_and_public_key(1, 'p0kZudG0').versions.select{|cav| cav.public_key = '0HgoJRyE'}.first.logs.page(2)
  ClientApplication Load (165.9ms)  SELECT `client_applications`.* FROM `client_applications` WHERE `client_applications`.`account_id` = 1 AND `client_applications`.`public_key` = 'p0kZudG0' AND (client_applications.deleted_at IS NULL) ORDER BY client_applications.id LIMIT 1
  ClientApplicationVersion Load (105.1ms)  SELECT `client_application_versions`.* FROM `client_application_versions` WHERE `client_application_versions`.`client_application_id` = 3 AND (client_application_versions.deleted_at IS NULL) ORDER BY client_application_versions.created_at DESC, client_application_versions.id DESC
  Log Load (57295.0ms)  SELECT `logs`.* FROM `logs` INNER JOIN `cloud_logs` ON `logs`.`cloud_log_id` = `cloud_logs`.`id` WHERE `cloud_logs`.`client_application_version_id` = 49 AND (logs.deleted_at IS NULL) AND (cloud_logs.deleted_at IS NULL) ORDER BY logs.timestamp DESC, cloud_logs.received_at DESC LIMIT 100 OFFSET 100
  EXPLAIN (214.5ms)  EXPLAIN SELECT `logs`.* FROM `logs` INNER JOIN `cloud_logs` ON `logs`.`cloud_log_id` = `cloud_logs`.`id` WHERE `cloud_logs`.`client_application_version_id` = 49 AND (logs.deleted_at IS NULL) AND (cloud_logs.deleted_at IS NULL) ORDER BY logs.timestamp DESC, cloud_logs.received_at DESC LIMIT 100 OFFSET 100
EXPLAIN for: SELECT  `logs`.* FROM `logs` INNER JOIN `cloud_logs` ON `logs`.`cloud_log_id` = `cloud_logs`.`id` WHERE `cloud_logs`.`client_application_version_id` = 49 AND (logs.deleted_at IS NULL) AND (cloud_logs.deleted_at IS NULL) ORDER BY logs.timestamp DESC, cloud_logs.received_at DESC LIMIT 100 OFFSET 100
+----+-------------+------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+---------+-----------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table      | type        | possible_keys                                                                                                                                         | key                                                                              | key_len | ref                               | rows | Extra                                                                                                                                           |
+----+-------------+------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+---------+-----------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | cloud_logs | index_merge | PRIMARY,index_cloud_logs_on_client_application_version_id,index_cloud_logs_on_deleted_at                                                              | index_cloud_logs_on_client_application_version_id,index_cloud_logs_on_deleted_at | 5,9     | NULL                              | 1874 | Using intersect(index_cloud_logs_on_client_application_version_id,index_cloud_logs_on_deleted_at); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | logs       | ref         | index_logs_on_cloud_log_id_and_deleted_at_and_timestamp,index_logs_on_cloud_log_id_and_deleted_at,index_logs_on_cloud_log_id,index_logs_on_deleted_at | index_logs_on_cloud_log_id                                                       | 5       | cloudlog_production.cloud_logs.id |    4 | Using where                                                                                                                                     |
+----+-------------+------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------+---------+-----------------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+

12 年 1 月 25 日更新
以下是所有相关表格的索引:

CLIENT_APPLICATIONS:
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_client_applications_on_key` (`key`),
  KEY `index_client_applications_on_account_id` (`account_id`),
  KEY `index_client_applications_on_deleted_at` (`deleted_at`),
  KEY `index_client_applications_on_public_key` (`public_key`)

CLIENT_APPLICATION_VERSIONS:
  PRIMARY KEY  (`id`),
  KEY `index_client_application_versions_on_client_application_id` (`client_application_id`),
  KEY `index_client_application_versions_on_deleted_at` (`deleted_at`),
  KEY `index_client_application_versions_on_public_key` (`public_key`)

CLOUD_LOGS:
  PRIMARY KEY  (`id`),
  KEY `index_cloud_logs_on_api_client_version_id` (`api_client_version_id`),
  KEY `index_cloud_logs_on_client_application_version_id` (`client_application_version_id`),
  KEY `index_cloud_logs_on_deleted_at` (`deleted_at`),
  KEY `index_cloud_logs_on_device_id` (`device_id`),
  KEY `index_cloud_logs_on_public_key` (`public_key`),
  KEY `index_cloud_logs_on_received_at` (`received_at`)

LOGS:
  PRIMARY KEY  (`id`),
  KEY `index_logs_on_class_name` (`class_name`),
  KEY `index_logs_on_cloud_log_id_and_deleted_at_and_timestamp` (`cloud_log_id`,`deleted_at`,`timestamp`),
  KEY `index_logs_on_cloud_log_id_and_deleted_at` (`cloud_log_id`,`deleted_at`),
  KEY `index_logs_on_cloud_log_id` (`cloud_log_id`),
  KEY `index_logs_on_deleted_at` (`deleted_at`),
  KEY `index_logs_on_file_name` (`file_name`),
  KEY `index_logs_on_method_name` (`method_name`),
  KEY `index_logs_on_public_key` (`public_key`),
  KEY `index_logs_on_timestamp` USING BTREE (`timestamp`)

最佳答案

用更好的结构显示它，查询看起来像这样(已经重新安排)

SELECT
  `logs`.*
FROM
  `logs` as l
  INNER JOIN `cloud_logs` as cl1
    ON
      cl1.id = l.cloud_log_id
  INNER JOIN `cloud_logs` as cl2
    ON
      cl2.id = l.cloud_log_id
  INNER JOIN `client_application_versions` as cav1
    ON
      cav1.id = cl1.client_application_version_id
  INNER JOIN `client_application_versions` as cav2
    ON
      cav2.id = cl2.client_application_version_id
  INNER JOIN `client_applications` as ca
    ON
      ca.id = cav1.client_application_id
WHERE
  (l.deleted_at IS NULL)
    AND
  (ca.account_id = 1)
    AND
  (ca.public_key = 'p0kZudG0')
    AND
  (cav.public_key = '0HgoJRyE')
ORDER BY
  logs.timestamp DESC
LIMIT
  0, 100

当查看 cav1/cl1 和 cav2/cl2 时，可以看到 cav2 和 cl2 从未使用过。 除了 ON 语句外，没有对它们应用过滤器。

因此 cav1 链接到正确的帐户，cav2 未链接到任何帐户，而是包含所有匹配的帐户。 这不是查询结果的问题，而是连接缓冲区的大小。

删除连接(以及它们的 ON 部分)会产生:

SELECT
  `logs`.*
FROM
  `logs` as l
  INNER JOIN `cloud_logs` as cl1
    ON
      cl1.id = l.cloud_log_id
--  INNER JOIN `cloud_logs` as cl2
--    ON
--      cl2.id = l.cloud_log_id
  INNER JOIN `client_application_versions` as cav1 use index for join (`index_cavs_on_client_application_id_and_public_key`)
    ON
      cav1.id = cl1.client_application_version_id
        AND
      cav1.public_key = '0HgoJRyE'

--  INNER JOIN `client_application_versions` as cav2
--    ON
--      cav2.id = cl2.client_application_version_id
  INNER JOIN `client_applications` as ca
    ON
      ca.id = cav1.client_application_id
WHERE
  (l.deleted_at IS NULL)
    AND
  (ca.account_id = 1)
    AND
  (ca.public_key = 'p0kZudG0')
ORDER BY
  logs.timestamp DESC
LIMIT
  0, 100

这个应该更快。

将其打包到控制台中可用的东西中(假设正确的表关系和 meta_where):

Log.where(:deleted_at.ne => nil).order("logs.timestamp desc").joins(:cloud_logs) & \
CloudLog.joins(:client_application_versions) & \
ClientApplicationVersion.where(:public_key => '0HgoJRyE').joins(:client_applications) & \
ClientApplication.where(:public_key => 'p0kZudG0', :account_id => 1)

由于我无法在此处重现，您可能需要自己尝试(或在末尾添加一个 to_sql) 并为上面的缩短查询添加解释。

结果可能很有趣。

更新: 看到结果和定义后(下面的评论):

尝试添加一个 key :

alter table client_application_versions add key (`client_application_id`, `public_key`);

这应该可以防止文件排序并加快速度。

编辑: 更新查询以提示 mysql 关于 key 。

关于mysql - 什么是适当的范围/索引来帮助通过 rails 使范围内的发现具有更高的性能？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14418134/