提高MySQL查询的性能

Question

我有两张桌子，users和points。目前，users有84,263行，而points有1,636,119行。每个用户可以有0个或多个点，我需要提取哪个点是最后创建的。

show create table users
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `role` varchar(15) COLLATE utf8_unicode_ci DEFAULT 'consument',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `email_verified_at` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_verify_token` text COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=84345 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

show create table points
CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `tablet_id` int(10) unsigned DEFAULT NULL,
  `parent_company` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `points` int(10) unsigned NOT NULL,
  `mutation_type` tinyint(3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `points_user_id_foreign` (`user_id`),
  KEY `points_company_id_foreign` (`company_id`),
  KEY `points_parent_company_index` (`parent_company`),
  KEY `points_tablet_id_index` (`tablet_id`),
  KEY `points_mutation_type_company_id_created_at_index` (`mutation_type`,`company_id`,`created_at`),
  KEY `created_at_user_id` (`created_at`,`user_id`),
  CONSTRAINT `points_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `points_parent_company_foreign` FOREIGN KEY (`parent_company`) REFERENCES `parent_company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `points_tablet_id_foreign` FOREIGN KEY (`tablet_id`) REFERENCES `tablets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `points_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1798627 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我尝试过查询，但花费的时间太长(我们说的是分钟，而不是秒)：

select
       `users`.`id`,
       `users`.`email`,
       `users`.`role`,
       `users`.`created_at`,
       `users`.`updated_at`,
       max(pt.created_at) as `last_transaction`
from `users`
left join points as pt on pt.user_id = users.id
where `users`.`role` = 'consument' and `users`.`deleted_at` is null
group by users.id

select
       `users`.`id`,
       `users`.`email`,
       `users`.`role`,
       `users`.`created_at`,
       `users`.`updated_at`,
       pt.created_at as `last_transaction`
from `users`
left join (select points.user_id, points.created_at from points order by points.created_at desc) as pt on pt.user_id = users.id
where `users`.`role` = 'consument' and `users`.`deleted_at` is null
group by users.id

为什么我不限制结果，一次只返回100个呢？因为我正在为Laravel使用Yajra DataTables，并且当限制结果时，它只返回有限的结果，并且它不能识别更多的结果。因此，我只得到100行，而不是84,263行，仅此而已。

Answer 1

基本上，您的"users“表有一个"role”列。它没有索引。因此，您的查询是在有84263行的"users“表上执行全表扫描。优化它的一种方法是在“角色”列上建立索引。但是我可以看到"consument“是默认值&您是通过该值查询的。现在假设95%的用户都是“消费者”角色。那么，即使在“角色”上添加索引也不会有多大帮助。您必须添加更多的条件来过滤掉查询&为该条件建立一个索引。

你的第一个查询更好，因为它可以避免第二个查询的不必要的内部查询。

如果您需要返回84263行，那么这是一个非常严重的问题。以某种方式，您将不得不引入分页。您必须将您的查询分解为多个查询。假设在每次调用中返回500个用户的数据。您可以按id对其进行排序。在随后的调用中，您可以要求返回next 500，其中id大于前一个查询中返回的上一个id (对于第一个调用，last id值将为0)。然后查询可以使用"id“作为索引。

您可以使用"explain“关键字检查查询计划&可以更好地理解。

Answer 2

编辑

我尝试在1000个用户和50000个点的users表的role上添加一个索引，您的第一次查询花费了大约4秒，这太长了。

所以我尝试了这个查询，花了大约0.5秒，仍然太长了：

select
       `users`.`id`,
       `users`.`email`,
       `users`.`role`,
       `users`.`created_at`,
       `users`.`updated_at`,
       pt.created_at as `last_transaction`
from `users`
left join points pt on pt.id = (select pt2.id from points pt2 WHERE pt2.user_id = users.id ORDER BY pt2.created_at DESC limit 1)
where `users`.`role` = 'consument' and `users`.`deleted_at` is null

因此，我在points.created_at上添加了一个索引，现在查询花费了0.05秒，这更容易接受

Answer 3

看起来您需要一个结果集，其中包含users表中的一些列，以及每个用户的points表中的最新created_at值。

所谓的复合覆盖索引通常有助于加快这些类型的查询。那么，让我们从您需要从points得到什么开始。这个子查询得到了它。

               SELECT user_id, MAX(created_at) last_transaction
                 FROM points
                GROUP BY user_id

这为您提供了一个虚拟表，其中包含每个user_id和所需的created_at值。下面的索引

CREATE INDEX points_maxcreated ON points (user_id, created_at DESCENDING);

将会让MySQL用一个几乎神奇的快loose index scan来满足子查询。

然后，让我们考虑查询的其余部分。

select
       `users`.`id`,
       `users`.`email`,
       `users`.`role`,
       `users`.`created_at`,
       `users`.`updated_at`
from `users`
where `users`.`role` = 'consument' and `users`.`deleted_at` is null

为此，您需要以下索引

CREATE INDEX users_del_role_etc 
    ON users 
      (deleted_at, role, id, email, created_at, updated_at);

MySQL可以直接从这个索引中满足您的查询。把这些索引看作是按顺序存储的。MySQL随机访问第一个符合条件的行(null deleted_at，role = 'consument')的索引，然后逐行读取索引，而不是表，以获得所需的数据。

把所有这些放在一起，你会得到

select
       `users`.`id`,
       `users`.`email`,
       `users`.`role`,
       `users`.`created_at`,
       `users`.`updated_at`,
       `subquery`.`last_transaction`
from `users`
left join (
                   SELECT user_id, MAX(created_at) last_transaction
                     FROM points
                    GROUP BY user_id
          ) subquery ON users.id = subquery.user_id
where `users`.`role` = 'consument' and `users`.`deleted_at` is null

对于您给我们的查询来说，这应该是相当快的。尽管如此，您预计会返回数万行的查询也应该需要一些时间。没有什么魔法可以让SQL快速处理非常大的结果集。它的设计目的是从大量的表中快速检索小的结果集。

恕我直言，您对如何从结果集中对行进行分页的理解并不完全正确。很难相信你的用户真的会检查成千上万的行。如果查询中没有ORDER BY操作，LIMIT是一个非常廉价的操作。如果您需要ORDER BY ... LIMIT对结果进行分页，请提出另一个问题，因为性能也是可以管理的。