可以观察到是全表扫描 type=>all;;
mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid in (111) ;
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_mobilesms_11 | NULL | ALL | userid | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 rows in set (0.11 sec)
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
SELECT*FROM tb_ams_inf_repay_stat a
JOIN(select id from tb_ams_inf_repay_stat limit1000010,10) b ON a.ID = b.id
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
PRIMARY
<derived2>
ALL
NULL
NULL
NULL
NULL
1000020
NULL
1
PRIMARY
a
eq_ref
PRIMARY
PRIMARY
8
b.id
1
NULL
2
DERIVED
tb_ams_inf_repay_stat
index
NULL
idx_orgcd_loannum
216
NULL
19753500
Using index
1.4、其他优化
1.4.1、适合带有条件的,id连续的查询
0.03s
select*from tb_ams_inf_repay_stat where id between1000000and1000010;
1.3.2、带有条件id不连续的查询,考虑建立索引
20s 慢死了
select*from tb_ams_inf_repay_stat where org_cd ='xmsd'limit1000000,10;
select *
from tb_ams_inf_repay_stat
where org_cd = 'xmsd'
and id > (select id from tb_ams_inf_repay_stat where org_cd = 'xmsd' limit 1000000,1)
limit 0,10 ;
0.2s 可以说相当的快了