SQL 语句 NOT IN 和 != 如何优化？

Question

这条 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;

Answer 1

not in 可以换成id > 8 ... union all ... id < 7
!= 也可以这样换，不过没看到你给userid加索引
或许可以用with优化，mysql不熟，
postgresql可以用explain analyse分析，共同学习吧

共同学习为什么要踩一下？不明白

explain analyse SELECT * FROM filemail.bottles WHERE id>8 AND userid != 34790331 AND show_times <= 10 AND status = 1
UNION ALL
SELECT * FROM filemail.bottles WHERE id<7 AND userid != 34790331 AND show_times <= 10 AND status = 1
ORDER BY reply_rate DESC, reward DESC
这样id的索引可以使用

QUERY PLAN
Index Scan using bottles_pkey on bottles (cost=0.29..117.04 rows=9
Index Cond: (id < 1000)
Total runtime: 0.352 ms

EXPLAIN