优化查询/子查询以获得更好的性能(慢)

r6vfmomb 于 2021-06-21 发布在 Mysql

关注(0)|答案(4)|浏览(382)

我有一个查询，子查询大约需要15秒才能完成。即使我不连接这两个表，它也相当慢，所以不是连接占用了时间。josèu payplansèsubscription表有8200行，大小为3.3MB。
某些用户同时具有活动成员身份和过期成员身份。我想找到所有用户如何不活跃（状态=1603）和没有其他活动成员。
查询工作很慢。
问题：我能做得更好吗？
谢谢

SELECT c.id, c.name, c.username, c.email, a.plan_id, d.title, a.subscription_date, a.expiration_date
FROM jos_payplans_subscription a
    LEFT JOIN jos_users c ON c.id = a.user_id
    LEFT JOIN jos_payplans_plan d ON d.plan_id = a.plan_id
WHERE a.status = 1603 
AND a.user_id NOT IN (SELECT b.user_id FROM jos_payplans_subscription b WHERE b.status = 1601)

下面是三个表的create表

CREATE TABLE `jos_payplans_subscription` (
  `subscription_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `total` decimal(15,5) DEFAULT '0.00000',
  `subscription_date` datetime DEFAULT '0000-00-00 00:00:00',
  `expiration_date` datetime DEFAULT '0000-00-00 00:00:00',
  `cancel_date` datetime DEFAULT '0000-00-00 00:00:00',
  `checked_out` int(11) DEFAULT '0',
  `checked_out_time` datetime DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime DEFAULT '0000-00-00 00:00:00',
  `params` text NOT NULL,
  PRIMARY KEY (`subscription_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=15103 DEFAULT CHARSET=utf8

CREATE TABLE `jos_payplans_plan` (
  `plan_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `published` tinyint(1) DEFAULT '1',
  `visible` tinyint(1) DEFAULT '1',
  `ordering` int(11) DEFAULT '0',
  `checked_out` int(11) DEFAULT '0',
  `checked_out_time` datetime DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime DEFAULT '0000-00-00 00:00:00',
  `description` text,
  `details` text,
  `params` text,
  PRIMARY KEY (`plan_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

CREATE TABLE `jos_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `sendEmail` tinyint(4) DEFAULT '0',
  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `params` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date of last password reset',
  `resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',
  `otpKey` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Two factor authentication encrypted keys',
  `otep` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'One time emergency passwords',
  `requireReset` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Require user to reset password on next login',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_block` (`block`) USING BTREE,
  KEY `username` (`username`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `idx_name` (`name`(100)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=23158 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql query-optimization

来源：https://stackoverflow.com/questions/49734496/optimize-query-subquery-for-better-performance-slow

4条答案

按热度按时间

s5a0g9ez1#

如果表中没有索引，请不要忘记在搜索字段上添加索引。

赞(0）回复(0）举报 2021-06-21

r1zk6ea12#

我认为下面的查询应该适合您。它使用left join，而不是not in，一般来说，它的执行速度要快得多。

SELECT c.id, c.name, c.username, c.email, a.plan_id, d.title, a.subscription_date, a.expiration_date
FROM jos_payplans_subscription a
LEFT JOIN jos_users c 
ON c.id = a.user_id
LEFT JOIN jos_payplans_plan d 
ON d.plan_id = a.plan_id
LEFT JOIN jos_payplans_subscription b 
ON b.status = 1601
AND b.user_id = a.user_id
WHERE a.status = 1603
AND b.plan_id IS NULL

这是假设 jos_payplans_subscription.plan_id 不能为空。如果可以，则选择任何其他非空的列用于 b.plan_id IS NULL 查询的一部分。
如果查询仍然太慢，那么我们需要查看索引和列数据类型，所以添加 SHOW CREATE TABLE tablename 所有涉及你问题的表格。

赞(0）回复(0）举报 2021-06-21

a9wyjsp73#

因为我们正在寻找所有只有1603成员资格的用户，所以我们可以使用except来尝试with子句。
edit：好吧，除了mysql之外，没有别的，所以这个查询没有什么帮助。

WITH 
 users_1601 as (
  SELECT user_id FROM jos_payplans_subscription WHERE status = 1601
 ),
 subs_1603_only as (
  SELECT a.user_id, a.plan_id, a.subscription_date, a.expiration_date
  FROM jos_payplans_subscription a 
  LEFT JOIN users_1601 b ON a.user_id = b.user_id
  WHERE a.status = 1603
  AND b.user_id is NULL
 )
SELECT 
   c.id, c.name, c.username, c.email, 
   subs.plan_id, subs.subscription_date, subs.expiration_date,
   d.title
FROM subs_1603_only subs
LEFT JOIN jos_users c ON c.id = subs.user_id
LEFT JOIN jos_payplans_plan d ON d.plan_id = subs.plan_id

实际上，我只是把事情弄得乱七八糟。假设完成了其他实践，例如正确的索引，我认为这个查询的目标是确保子查询（状态1601的用户id）不会对主查询中的每一行执行。有时把它拉到with子句中会有帮助。

赞(0）回复(0）举报 2021-06-21

7bsow1i64#

改变 NOT IN ( SELECT id ... ) 至 NOT EXISTS ( SELECT * ... AND b.user_id = a.user_id ) . 原因：不需要收集“所有”的ID，只需要查看是否存在任何ID。
切换到innodb；有许多优化只在innodb中可用。
收集你想要的身份证 JOINing 至 c 以及 d . 也就是说， SELECT ... FROM ( SELECT ... ) LEFT JOIN c ... LEFT JOIN d ...

赞(0）回复(0）举报 2021-06-21

我来回答

优化查询/子查询以获得更好的性能(慢)

4条答案

相关问题

热门标签

最新问答