mysql - 无法让我的查询在具有 2M 条目的 MySQL 数据库上运行得更快

Question

我有这个付款表，大约有 200 万个条目

CREATE TABLE IF NOT EXISTS `payments` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) unsigned NOT NULL,
    `date` datetime NOT NULL,
    `valid_until` datetime NOT NULL,
     PRIMARY KEY (`id`),
     KEY `date_id` (`date`,`id`),
     KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2113820 ;

这个用户表来自 CodeIgniter 的 ion_auth 插件/库，大约有 320k 个条目

CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `ip_address` varbinary(16) NOT NULL,
    `username` varchar(100) NOT NULL,
    `password` varchar(80) NOT NULL,
    `salt` varchar(40) DEFAULT NULL,
    `email` varchar(100) NOT NULL,
    `activation_code` varchar(40) DEFAULT NULL,
    `forgotten_password_code` varchar(40) DEFAULT NULL,
    `forgotten_password_time` int(11) unsigned DEFAULT NULL,
    `remember_code` varchar(40) DEFAULT NULL,
    `created_on` int(11) unsigned NOT NULL,
    `last_login` int(11) unsigned DEFAULT NULL,
    `active` tinyint(1) unsigned DEFAULT NULL,
    `first_name` varchar(50) DEFAULT NULL,
    `last_name` varchar(50) DEFAULT NULL,
    `company` varchar(100) DEFAULT NULL,
    `phone` varchar(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=322435 ;

我正在尝试获取用户信息和他的最后一笔付款。按 ID、名字和姓氏、付款日期或付款到期日期排序(ASC 或 DESC)。创建一个表来显示付款过期的用户和有效的用户

我已设法正确获取数据，但大多数情况下，我的查询对单个用户需要 1 秒以上，对 30 个用户需要 40 秒以上。老实说，我不知道是否有可能在 1 秒内获取信息。也可能我的应用程序永远不会达到这个数量的条目，可能最多 10k 支付和 300 个用户

我的查询在条目很少的情况下运行良好，而且很容易更改顺序:

SELECT users.id, users.first_name, users.last_name, users.email, final.id AS payment_id, payment_date, final.valid_until AS payment_valid_until 
FROM users 
LEFT JOIN ( 
    SELECT * FROM ( 
        SELECT payments.id, payments.user_id, payments.date AS payment_date, payments.valid_until 
        FROM payments 
        ORDER BY payments.valid_until DESC 
        ) AS p GROUP BY p.user_id
) AS final ON final.user_id = users.id 
ORDER BY id ASC 
LIMIT 0, 30"

解释:

id  select_type         table               type              possible_keys   key       key_len   ref    rows      Extra
1   PRIMARY             users               ALL               NULL            NULL      NULL      NULL   322269    Using where; Using temporary; Using filesort
1   PRIMARY             <derived2>          ALL               NULL            NULL      NULL      NULL   50 
4   DEPENDENT SUBQUERY  users_deactivated   unique_subquery   user_id         user_id   4         func   1         Using index
2   DERIVED             <derived3>          ALL               NULL            NULL      NULL      NULL   2072327   Using temporary; Using filesort
3   DERIVED             payments            ALL               NULL            NULL      NULL      NULL   2072566   Using filesort

我乐于接受任何建议和技巧，因为我是 PHP、MySQL 和其他东西的新手，而且我真的不知道我的做法是否正确

Answer 1

我首先建议从您的子查询中删除 ORDER BY 子句——我看不出它有什么帮助，因为您在外部查询中按 id 重新排序。

您还应该能够将您的 GROUP BY 语句移动到您的子查询中:

SELECT users.id, users.first_name, users.last_name, users.email, final.id AS payment_id, payment_date, final.valid_until AS payment_valid_until 
FROM users 
    LEFT JOIN ( 
        SELECT payments.id, payments.user_id, payments.date AS payment_date, payments.valid_until 
        FROM payments 
        GROUP BY payments.user_id
    ) AS final ON final.user_id = users.id 
ORDER BY users.id ASC 
LIMIT 0, 30

---

鉴于您的评论，这个怎么样——不确定它会比您当前的查询更好，但是 ORDER BY 可能很昂贵:

SELECT users.id, users.first_name, users.last_name, users.email, p.id AS payment_id, p.payment_date, p.valid_until AS payment_valid_until 
FROM users 
    LEFT JOIN payments p ON p..user_id = users.id 
    LEFT JOIN ( 
        SELECT user_id, MAX(valid_until) Max_Valid_Until
        FROM payments 
        GROUP BY user_id
    ) AS maxp ON p.user_id = maxp.user_id and p.valid_until = maxp.max_valid_until
ORDER BY users.id ASC 
LIMIT 0, 30