mysql – 在三个不同的列上使用MAX,WHERE和GROUP BY提高SQL查询的速度

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 在三个不同的列上使用MAX,WHERE和GROUP BY提高SQL查询的速度，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5652字，纯文字阅读大概需要9分钟。

内容图文

我试图加快一个查询,大约需要60秒才能完成约2000万行的表.

对于此示例,该表有三列(id,dateAdded,name).
id是主键.
我添加到表中的索引是：

(dateAdded)
(name)
(id, name)
(id, name, dateAdded)

我试图运行的查询是：

SELECT MAX(id) as id, name 
FROM exampletable 
WHERE dateAdded <= '2014-01-20 12:00:00' 
GROUP BY name 
ORDER BY NULL;

日期在查询之间是可变的.

这样做的目的是在添加日期或之前获取每个名称的最新条目.

当我在查询上使用explain时,它告诉我它正在使用(id,name,dateAdded)索引.

+----+-------------+------------------+-------+------------------+----------------------------------------------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table            | type  | possible_keys    | key                                          | key_len | ref  | rows     | Extra                                                     |
+----+-------------+------------------+-------+------------------+----------------------------------------------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | exampletable     | index | date_added_index | id_element_name_date_added_index             | 162     | NULL | 22016957 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------------+-------+------------------+----------------------------------------------+---------+------+----------+-----------------------------------------------------------+

编辑：
从评论中添加了两个新索引：

(dateAdded, name, id)
(name, id)

+----+-------------+------------------+-------+---------------------------------------------------------------+----------------------------------------------+---------+------+----------+-------------------------------------------+
| id | select_type | table            | type  | possible_keys                                                 | key                                          | key_len | ref  | rows     | Extra                                     |
+----+-------------+------------------+-------+---------------------------------------------------------------+----------------------------------------------+---------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | exampletable     | index | date_added_index,date_added_name_id_index                     | id__name_date_added_index                    | 162     | NULL | 22040469 | Using where; Using index; Using temporary |
+----+-------------+------------------+-------+---------------------------------------------------------------+----------------------------------------------+---------+------+----------+-------------------------------------------+

编辑：
添加了创建表脚本.

CREATE TABLE `exampletable` (
  `id` int(10) NOT NULL auto_increment,
  `dateAdded` timestamp NULL default CURRENT_TIMESTAMP,
  `name` varchar(50) character set utf8 default '',
  PRIMARY KEY  (`id`),
  KEY `date_added_index` (`dateAdded`),
  KEY `name_index` USING BTREE (`name`),
  KEY `id_name_index` USING BTREE (`id`,`name`),
  KEY `id_name_date_added_index` USING BTREE (`id`,`dateAdded`,`name`),
  KEY `date_added_name_id_index` USING BTREE (`dateAdded`,`name`,`id`),
  KEY `name_id_index` USING BTREE (`name`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22046064 DEFAULT CHARSET=latin1

编辑：
以下是HeavyE提供的答案中的解释.

+----+-------------+--------------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+--------------------------------------------------+------+---------------------------------------+
| id | select_type | table        | type  | possible_k                                                                               | key                      | key_len | ref                                              | rows | Extra                                 |
+----+-------------+--------------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+--------------------------------------------------+------+---------------------------------------+
|  1 | PRIMARY     | <derived2>   | ALL   | NULL                                                                                     | NULL                     | NULL    | NULL                                             | 1732 | Using temporary; Using filesort       |
|  1 | PRIMARY     | example1     | ref   | date_added_index,name_index,date_added_name_id_index,name_id_index,name_date_added_index | date_added_name_id_index | 158     | maxDateByElement.dateAdded,maxDateByElement.name |    1 | Using where; Using index              |
|  2 | DERIVED     | exampletable | range | date_added_index,date_added_name_id_index                                                | name_date_added_index    | 158     | NULL                                             | 1743 | Using where; Using index for group-by |
+----+-------------+--------------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+--------------------------------------------------+------+---------------------------------------+

解决方法:

有一个很棒的Stack Overflow帖子,用于优化选择列中具有最大值的行：https://stackoverflow.com/a/7745635/633063

这似乎有点乱,但效果很好：

SELECT example1.name, MAX(example1.id)
FROM exampletable example1
INNER JOIN (
select name, max(dateAdded) dateAdded
from exampletable
where dateAdded  <= '2014-01-20 12:00:00' 
group by name
) maxDateByElement on example1.name = maxDateByElement.name AND example1.dateAdded = maxDateByElement.dateAdded
GROUP BY name;

内容总结

以上是互联网集市为您收集整理的mysql – 在三个不同的列上使用MAX,WHERE和GROUP BY提高SQL查询的速度全部内容，希望文章能够帮你解决mysql – 在三个不同的列上使用MAX,WHERE和GROUP BY提高SQL查询的速度所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端