这条 sql 语句查询的是同一张表,但是带有 not in 和 != 索引用不上,如何优化?
SELECT * FROM filemail.bottles WHERE id NOT IN(8,7) AND userid != 34790331 AND show_times <= 10 AND status = 1 ORDER BY reply_rate DESC, reward DESC
表结构:
CREATE TABLE `bottles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL DEFAULT '0' COMMENT '发送者userid',
`content` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '漂流瓶内容',
`reward` int(11) NOT NULL DEFAULT '0' COMMENT '打赏金额',
`show_times` int(11) NOT NULL DEFAULT '0' COMMENT '曝光次数',
`reply_times` int(11) NOT NULL DEFAULT '0' COMMENT '回应次数',
`reply_rate` decimal(4,2) NOT NULL DEFAULT '0.00' COMMENT '回复率(回复数/展现量)',
`location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '位置',
`status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态 0 无效 1有效',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_of_userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;