这个sql如何优化 select * from a where type=? and date>='2009-1-1' and date<'2010-5-1' and t.status in (1,2,3,4,5,6..)1 date>='2009-1-1' and date<'2010-5-1' 是否需要 改写为between ('2009-1-1','2010-5-1')
2 status 是否必须单独建立一个只有该字段的索引 当前在线表已经有索引(Type`,`status`)(附后
explain
select * from a where type=? and date>='2009-1-1' and date<'2010-5-1' and t.status in (1,2,3,4,5,6..)-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------------------------------------------------+--------------------------+---------+-------+-------+-------------+
| 1 | SIMPLE | t | ref | idx_TE_sate,idx_TEnTypePard,idx_TE_d_tt_status | idx_TE__tt_status | 4 | const | 27563 | Using where |
+----+-------------+-------+------+--------------------------------------------------------------------------------+--------------------------+---------+-------+-------+-------------+
1 row in set可以看到他实际是走 索引(Type`,`status`) , 可以key_len=4| ref=const )
2 status 是否必须单独建立一个只有该字段的索引 当前在线表已经有索引(Type`,`status`)(附后
explain
select * from a where type=? and date>='2009-1-1' and date<'2010-5-1' and t.status in (1,2,3,4,5,6..)-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------------------------------------------------------+--------------------------+---------+-------+-------+-------------+
| 1 | SIMPLE | t | ref | idx_TE_sate,idx_TEnTypePard,idx_TE_d_tt_status | idx_TE__tt_status | 4 | const | 27563 | Using where |
+----+-------------+-------+------+--------------------------------------------------------------------------------+--------------------------+---------+-------+-------+-------------+
1 row in set可以看到他实际是走 索引(Type`,`status`) , 可以key_len=4| ref=const )
不需要2 status 是否必须单独建立一个只有该字段的索引 当前在线表已经有索引(Type`,`status`)
MySQL 一般情况下一次只会使用一个索引, 你估计需要考虑 (`status`) , (`status`,Type),(Type,status) 三者之间哪个最好。
date between '2009-1-1' and '2010-5-1'
在date上加个索引会比较好。status,type这种重复率极高的字段,没有索引比有索引可能更快