有索引的情况下不应该是全表扫描啊。见如下的测试例子。或者你可以贴出你的EXPLAIN的结果大家一起分析一下。mysql> explain select * from t1 where id in (1,2,3,4,5,6,7,8,9); +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 9 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)mysql>
是列表形式的,数据会有几百个。 是类似这样做吗?SELECT * FROM TABLE WHERE UID NOT IN (SELECT UID FROM TABLE2 WHERE UID=N)购造一表存放再联接查询是什么样的形式 ?
mysql> explain select * from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889); +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | test | ALL | PRIMARY | NULL | NULL | NULL | 66723 | Using where | +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
不好意思,没看清楚,你用的是 NOT IN ,这样的话,一般来说MySQL会使用全表扫描而不是去利用索引。原因也很简单。因为MYSQL认为用索引反而更麻烦。比如让你从 1000 个人中间把编号为 145, 761 这两个学生之外的所有学生资料抄写一遍。你是先找出这两个学生的成绩放一边儿,然后再开始抄写,还是直接开始一份挨一份开始抄写,看到 这两个号直接跳过去?
创建索引肯定不会错的,但not in是否能用上索引就要看数据流了
mysql> explain select * from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889); +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | test | ALL | PRIMARY | NULL | NULL | NULL | 66723 | Using where | +----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+ 如果在需求上,只需列出 uid的话,可以将语句改为: select uid from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889); 如此mysql会利用上索引去找!如果不是单单列出uid,加上force index试试看!
select * from ipcstorage.cachetmp a left join (select lblid from ipcconfigdb.labelfilter where ftype=2 )b on a.lblid = b.lblid where b.lblid is null 搞定优化,来分享一下
把not in 改写成 >,<等
如果in后面的数据是范围性的,则用范围查询(>、<)代替;
如果in后面的值是列表形式的,且数据量很大,可以购造一表存放再联接查询。
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 9 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)mysql>
是类似这样做吗?SELECT * FROM TABLE WHERE UID NOT IN (SELECT UID FROM TABLE2 WHERE UID=N)购造一表存放再联接查询是什么样的形式 ?
mysql> explain select * from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889);
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | test | ALL | PRIMARY | NULL | NULL | NULL | 66723 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
mysql> explain select * from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889);
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | test | ALL | PRIMARY | NULL | NULL | NULL | 66723 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+-------+-------------+
如果在需求上,只需列出 uid的话,可以将语句改为:
select uid from test where uid not in(1014565,4197701,1896493,1086589,1004369,1071314,2969603,1017104,1003889);
如此mysql会利用上索引去找!如果不是单单列出uid,加上force index试试看!
from ipcstorage.cachetmp a
left join (select lblid from ipcconfigdb.labelfilter where ftype=2 )b on
a.lblid = b.lblid
where b.lblid is null
搞定优化,来分享一下