mysql 前30秒_mysql – 简单查询需要15-30秒

以下查询非常简单.它从消息表中选择最后20条记录,以便在分页方案中使用.第一次运行此查询时,需要15到30秒.后续运行只需不到一秒钟(我预计会涉及一些缓存).我试图确定为什么第一次这么长时间.

这是查询：

SELECT DISTINCT ID,List,`From`,Subject, UNIX_TIMESTAMP(MsgDate) AS FmtDate

FROM messages

WHERE List='general'

ORDER BY MsgDate

LIMIT 17290,20;

MySQL版本：4.0.26-log

这是表格：

messages CREATE TABLE `messages` (

`ID` int(10) unsigned NOT NULL auto_increment,

`List` varchar(10) NOT NULL default '',

`MessageId` varchar(128) NOT NULL default '',

`From` varchar(128) NOT NULL default '',

`Subject` varchar(128) NOT NULL default '',

`MsgDate` datetime NOT NULL default '0000-00-00 00:00:00',

`TextBody` longtext NOT NULL,

`HtmlBody` longtext NOT NULL,

`Headers` text NOT NULL,

`UserID` int(10) unsigned default NULL,

PRIMARY KEY (`ID`),

UNIQUE KEY `List` (`List`,`MsgDate`,`MessageId`),

KEY `From` (`From`),

KEY `UserID` (`UserID`,`List`,`MsgDate`),

KEY `MsgDate` (`MsgDate`),

KEY `ListOnly` (`List`)

) TYPE=MyISAM ROW_FORMAT=DYNAMIC

这是解释：

table type possible_keys key key_len ref rows Extra

------ ------ ------------- -------- ------- ------ ------ --------------------------------------------

m ref List,ListOnly ListOnly 10 const 18002 Using where; Using temporary; Using filesort

当我在所有相关列上都有索引时,为什么使用filesort？我添加了ListOnly索引,看看它是否有用.我原本以为List索引会处理列表选择和MsgDate上的排序,但事实并非如此.现在我添加了ListOnly索引,这是它使用的索引,但它仍然在MsgDate上执行一个文件排序,这是我怀疑花了这么长时间.

我尝试使用FORCE INDEX如下：

SELECT DISTINCT ID,List,`From`,Subject, UNIX_TIMESTAMP(MsgDate) AS FmtDate

FROM messages

FORCE INDEX (List)

WHERE List='general'

ORDER BY MsgDate

LIMIT 17290,20;

这似乎迫使MySQL使用索引,但它根本不会加速查询.

以下是此查询的说明：

table type possible_keys key key_len ref rows Extra

------ ------ ------------- ------ ------- ------ ------ ----------------------------

m ref List List 10 const 18002 Using where; Using temporary

更新：

我从查询中删除了DISTINCT.它根本没有帮助.

我删除了UNIX_TIMESTAMP调用.它也没有影响性能.

我在我的PHP代码中做了一个特例,这样如果我检测到用户正在查看结果的最后一页,我会添加一个WHERE子句,它只返回结果的最后7天：

SELECT m.ID,List,From,Subject,MsgDate

FROM messages

WHERE MsgDate>='2009-11-15'

ORDER BY MsgDate DESC

LIMIT 20

这要快得多.但是,只要我导航到另一个结果页面,它就必须使用旧的SQL并且需要很长时间才能执行.我想不出一个实用,现实的方法来为所有页面执行此操作.此外,这种特殊情况使我的PHP代码更加复杂.

奇怪的是,只有第一次运行原始查询需要很长时间.后续运行相同的查询或显示不同结果页面的查询(即,只有LIMIT子句发生变化)非常快.如果查询尚未运行约5分钟,则查询会再次变慢.

解：

我想出的最佳解决方案是基于Jason Orendorff和Juliet的想法.

首先,我确定当前页面是否更接近总页数的开头或结尾.如果它更接近结尾,我使用ORDER BY MsgDate DESC,应用适当的限制,然后反转返回记录的顺序.

这使得检索页面接近结果集的开头或结尾的速度要快得多(第一次现在需要4-5秒而不是15-30秒).如果用户想要导航到中间附近的页面(当前在第430页左右),那么速度可能会下降.但那是一种罕见的情况.

因此,虽然似乎没有完美的解决方案,但这比大多数情况要好得多.

谢谢你,杰森和朱丽叶.

解决方法:

而不是ORDER BY MsgDate LIMIT 17290,20,尝试ORDER BY MsgDate DESC LIMIT 20.

当然结果将以相反的顺序出现,但这应该很容易处理.

编辑：您的MessageId值是否总是随时间增加？它们是独特的吗？

如果是这样,我会做一个索引：

UNIQUE KEY `ListMsgId` ( `List`, `MessageId` )

并根据消息ID而不是可能的日期进行查询.

-- Most recent messages (in reverse order)

SELECT * FROM messages

WHERE List = 'general'

ORDER BY MessageId DESC

LIMIT 20

-- Previous page (in reverse order)

SELECT * FROM messages

WHERE List = 'general' AND MessageId < '15885830'

ORDER BY MessageId DESC

LIMIT 20

-- Next page

SELECT * FROM messages

WHERE List = 'general' AND MessageId > '15885829'

ORDER BY MessageId

LIMIT 20

我认为你也支付varchar列,其中int类型会快得多.例如,List可以是指向单独表中的条目的ListId.您可能想在测试数据库中尝试一下,看看是否真的如此;我不是MySQL专家.

标签：performance,mysql,limit

来源： https://codeday.me/bug/20190827/1742271.html