Mysql:优化派生表的计数查询

Question

我正在尝试对派生表进行计数查询以进行分页。查询如下所示:

SELECT 
    assignment_completions.id as id,
    assignment_completions.first_name,
    assignment_completions.last_name,
    groups.name

FROM
    assignment_completions
        LEFT JOIN
    groups_users ON assignment_completions.user_id = groups_users.user_id
        LEFT JOIN
    groups ON groups_users.group_id = groups.id
WHERE
    assignment_completions.handler = 'course'
GROUP BY assignment_completions.id

计数查询只是像这样包装上面的查询:

SELECT COUNT(*) FROM (...) AS assignment_count

没有计数的查询在 0.005 秒内执行。带有计数的查询在 1.5 秒内执行。

我试过以下但没有成功:

1) 使用索引列(这里没有性能提升):

SELECT COUNT (id) FROM (...)

2) 我试过使用 SQL_CALC_FOUND_ROWS 但它实际上有点慢(2 秒左右)。

详细信息:

assignment_completions:20 万行

用户:35k 行

groups_users:50 万行

组:1k 行

表定义

CREATE TABLE `assignment_completions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `assignment_id` int(11) DEFAULT NULL,
  `handler` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `handler_id` int(11) DEFAULT NULL,
  `time_started` datetime DEFAULT NULL,
  `time_end` datetime DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `application_instance_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_assignment_completions_on_first_name` (`first_name`) USING BTREE,
  KEY `index_assignment_completions_on_last_name` (`last_name`) USING BTREE,
  KEY `index_assignment_completions_on_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `encrypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `reset_password_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reset_password_sent_at` datetime DEFAULT NULL,
  `remember_created_at` datetime DEFAULT NULL,
  `sign_in_count` int(11) NOT NULL DEFAULT '0',
  `current_sign_in_at` datetime DEFAULT NULL,
  `last_sign_in_at` datetime DEFAULT NULL,
  `current_sign_in_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_sign_in_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `application_instance_id` int(11) DEFAULT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `group_list_cache` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`) USING BTREE,
  UNIQUE KEY `index_users_on_username_and_application_instance_id` (`username`,`application_instance_id`) USING BTREE,
  KEY `index_users_on_application_instance_id` (`application_instance_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30006 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `groups_users` (
  `group_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  UNIQUE KEY `index_groups_users_on_group_id_and_user_id` (`group_id`,`user_id`) USING BTREE,
  KEY `index_groups_users_on_group_id` (`group_id`) USING BTREE,
  KEY `index_groups_users_on_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `application_instance_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `group_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1045 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

EXPLAIN 查询:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: assignment_completions
         type: index
possible_keys: PRIMARY,index_assignment_completions_on_first_name,index_assignment_completions_on_last_name,index_assignment_completions_on_user_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 199088
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: lms.assignment_completions.user_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: groups_users
         type: ref
possible_keys: index_groups_users_on_user_id
          key: index_groups_users_on_user_id
      key_len: 5
          ref: lms.users.id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: groups
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: lms.groups_users.group_id
         rows: 1
     filtered: 100.00
        Extra: Using index

EXPLAIN 用于计数查询:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 199088
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: assignment_completions
         type: index
possible_keys: PRIMARY,index_assignment_completions_on_first_name,index_assignment_completions_on_last_name,index_assignment_completions_on_user_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 199088
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: users
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: lms.assignment_completions.user_id
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: groups_users
         type: ref
possible_keys: index_groups_users_on_user_id
          key: index_groups_users_on_user_id
      key_len: 5
          ref: lms.users.id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 2
  select_type: DERIVED
        table: groups
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: lms.groups_users.group_id
         rows: 1
     filtered: 100.00
        Extra: Using index

我需要计算总结果以进行分页。

编辑

偶尔会修改此查询，这就是加入组的原因。有时会为组添加 where 子句:

AND groups.name LIKE "%abc%"

因此，与组表的连接是必要的。

Answer 1

To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count.

您还可以强制 InnoDB 使用索引:

  SELECT COUNT(id) FROM assignment_completions USE INDEX (PRIMARY);

除此之外，我看到您使用了很多索引，这会减慢您的查询速度。

尝试仅使用您将依赖的 id 索引。

Innodb does not have cached row count. so count(*) without where clause is slow with Innodb tables.

this may help you