优化MySQL排序查询
例如，我有以下查询来获取上个月提交的浏览次数最多的文章。
explain
SELECT *
FROM article
WHERE date > 1315391769
ORDER BY views DESC
LIMIT 10
如何为此查询选择正确的索引？或者如何重写它以避免扫描大量行或文件排序？
这是我尝试过的当前索引的表方案：
CREATE TABLE `article` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`body` text NOT NULL,
`date` int(32) NOT NULL,
`views` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `views` (`views`),
KEY `date_2` (`date`,`views`),
KEY `views_2` (`views`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=200003 ;
--
-- Dumping data for table `article`
--
INSERT INTO `article` VALUES (1, 'title test113', 'test body118', 1317912183, 5017);
INSERT INTO `article` VALUES (2, 'title test193', 'test body193', 1313441124, 5943);
INSERT INTO `article` VALUES (3, 'title test112', 'test body116', 1312773586, 653);
INSERT INTO `article` VALUES (4, 'title test378', 'test body374', 1316786646, 4589);
INSERT INTO `article` VALUES (5, 'title test335', 'test body3310', 1319173694, 6224);
注意：我还尝试了 mysql date 而不是 Unix timestamp，但得到了相同的结果。
这是 EXPLAIN 的输出：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE article range date,date_2 date 4 NULL 107245 Using where; Using filesort
I have the following query to get the most viewed articles that submitted in last month for example.
explain
SELECT *
FROM article
WHERE date > 1315391769
ORDER BY views DESC
LIMIT 10
how do I choose the right index for this query? or how do I re-write it to avoid scanning a lot of rows or file sorting?
This is the table scheme with the current indexes I tried:
CREATE TABLE `article` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
`body` text NOT NULL,
`date` int(32) NOT NULL,
`views` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `date` (`date`),
KEY `views` (`views`),
KEY `date_2` (`date`,`views`),
KEY `views_2` (`views`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=200003 ;
--
-- Dumping data for table `article`
--
INSERT INTO `article` VALUES (1, 'title test113', 'test body118', 1317912183, 5017);
INSERT INTO `article` VALUES (2, 'title test193', 'test body193', 1313441124, 5943);
INSERT INTO `article` VALUES (3, 'title test112', 'test body116', 1312773586, 653);
INSERT INTO `article` VALUES (4, 'title test378', 'test body374', 1316786646, 4589);
INSERT INTO `article` VALUES (5, 'title test335', 'test body3310', 1319173694, 6224);
Notice: I also tried mysql date instead of Unix timestamp but I got the same result.
This is the output of EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE article range date,date_2 date 4 NULL 107245 Using where; Using filesort
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于此类查询，只能使用
(date)或(views)索引，而不能使用复合(date,views)。 MySQL 选择使用的内容可能是最优的，也可能不是最优的。 2 月份的最佳数据可能不适用于 4 月份的数据！您可以尝试强制两者之一并衡量性能。
您可以使用
CHAR(6)（例如 2011 年 4 月的201104）添加包含Year-Month数据的计算列（该列可以使用 INSERT 和 UPDATE 触发器进行更新）或int如24136(24136 = 2011*12 + 4)。那么您的条件将是：
或
并且可以使用
(YearMonth,views)索引。For this kind of queries, only either
(date)or(views)index can be used and not the compound(date,views). What MySQL chooses to use may or may not be optimal. And what is optimal for February may not be for April data!You can try forcing one of the two and measure preformance.
You could add a calculated column with
Year-Monthdata, using aCHAR(6)like201104for April 2011 (that column can be updated using INSERT and UPDATE triggers) or anintlike24136(24136 = 2011*12 + 4).Then your condition would be:
or
and a
(YearMonth, views)index could be used.通过强制索引，就实现了。
解释一下计划：
By forcing index, it is achieved.
Explain plan:
单个键
date_2（date、views）应该为相关查询提供最佳性能。请参阅此处 我不认为另一个索引会有所帮助。由于查询如此简单，我想不出任何其他优化！A single key
date_2(date,views) should give optimal performance for the query in question. See here I don't think the other indexes are going to help. Since the query is so simple I can't think of any other optimization!