有一个业务是查询最新审核的5条数据
SELECT `id`, `title`
FROM `th_content`
WHERE `audit_time` < 1541984478
AND `status` = 'ONLINE'
ORDER BY `audit_time` DESC, `id` DESC
LIMIT 5;
查看当时的监控情况 cpu 使用率是超过了100%,show processlist看到很多类似的查询都是处于create sort index的状态。
查看该表的结构
CREATE TABLE `th_content` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '内容标题',
`content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT '正文内容',
`audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '审核时间',
`last_edit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近编辑时间',
`status`