使用LIMIT、ORDER BY、bool子句和IN子句优化MySQL查询

Question

我需要一些建议。为了更通用，我在下面的例子中进行了解释:我有一个带有字段的MySQL InnoDB表'posts‘：

`id` - INT UNSIGNED AUTOINCREMENT
`title` - VARCHAR(100)
`abstract` - TEXT #can be converted to VARCHAR(2048)
`content` - TEXT #text + thml tags
`category` - SMALLINT
`created_on` - DATETIME
`modified_on` - DATETIME
`enabled` - TINYINT(1)
`deleted` - TINYINT(1)

打开inexes

`id` - PRIMARY
`category` - INDEX
`modified_on` - INDEX
(`enabled`,`deleted`) - INDEX

表包含1000-1,500万条记录。我在做查询

SELECT id, title, abstract FROM posts 
WHERE enabled = 1 AND deleted = 0 
    AND category IN ( {id's} )
ORDER BY modified_on DESC 
LIMIT {offset}, {limit}

当有50-100个并发请求发送到MySQL服务器时，它就会宕机。每个查询的执行时间是0.1-0.5s，这取决于limit子句。

我是否需要为'category‘建立单独的索引，因为有时我需要浏览所有类别的所有帖子，或者我可以将'category’添加到(enabled，deleted)索引，并在每次需要所有类别的帖子时传递所有类别的id (~200)？

有什么解决方案可以提高这种查询的速度？

提前谢谢。

Answer 1

我认为你应该在索引中添加包含“已启用”和“已删除”的类别。

希望它能减少您的查询时间