还是那个老问题

Question

为了提高速度，我已经添加了包括info_time在内的很多冗余字段，以下语句我希望能够查询一个时间段 '2009-03-01 00:00:00' 到 '2009-04-01 00:00:00' 的内容，但是按照E_role_info.info_reprint 这个字段排序！
强制索引idx_E_role_info_roleID_reprint是在E_role_info(roleID, info_reprint)上建立的。
由于没有用到相应时间索引，我本以为筛选出'2009-03-01 00:00:00' 到 '2009-04-01 00:00:00'的数据会用到filesort。
但以下却没filesort。而且运行结果也很快！我想求证的是，mysql是不是在筛选WHERE E_role_info.roleID = 413382 的过程中，同时也在数据表中检查了E_role_info.info_time 是否符合E_role_info.info_time > '2009-03-01 00:00:00' AND E_role_info.info_time < '2009-04-01 00:00:00' ？
mysql> EXPLAIN
    -> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_title, E_info.info_time, E_info.info_reprint 
    -> FROM E_role_info FORCE INDEX(idx_E_role_info_roleID_reprint) INNER JOIN E_info IGNORE INDEX(idx_id_time) USING(infoID) 
    -> WHERE E_role_info.roleID = 413382 AND E_role_info.info_time > '2009-03-01 00:00:00' AND E_role_info.info_time < '2009-04-01 00:00:00' 
    -> ORDER BY E_role_info.info_reprint DESC LIMIT 0, 10;+----+-------------+-------------+--------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
| id | select_type | table       | type   | possible_keys                  | key                            | key_len | ref                           | rows    | Extra       |
+----+-------------+-------------+--------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
|  1 | SIMPLE      | E_role_info | ref    | idx_E_role_info_roleID_reprint | idx_E_role_info_roleID_reprint | 3       | const                         | 2707604 | Using where | 
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY                        | PRIMARY                        | 4       | news_data2.E_role_info.infoID |       1 |             | 
+----+-------------+-------------+--------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
还有一个疑问，该语句中我在E_info后添加了IGNORE INDEX(idx_id_time)， （注：idx_id_time是E_info(info_time)）上的索引），这样才得以在上边的explain的结果中得到E_info用eq_ref的结果，这样算是比较好的效果了！但是如果不忽略idx_id_time索引，则结果如下：mysql> explain SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_title, E_info.info_time, E_info.info_reprint  FROM E_role_info FORCE INDEX(idx_E_role_info_roleID_reprint) INNER JOIN E_info USING(infoID)  WHERE E_role_info.roleID = 413382 AND E_role_info.info_time > '2009-03-01 00:00:00' AND E_role_info.info_time < '2009-04-01 00:00:00'  ORDER BY E_role_info.info_reprint DESC LIMIT 0, 10; +----+-------------+-------------+------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
| id | select_type | table       | type | possible_keys                  | key                            | key_len | ref                           | rows    | Extra       |
+----+-------------+-------------+------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
|  1 | SIMPLE      | E_role_info | ref  | idx_E_role_info_roleID_reprint | idx_E_role_info_roleID_reprint | 3       | const                         | 2707604 | Using where | 
|  1 | SIMPLE      | E_info      | ref  | PRIMARY,idx_id_time            | idx_id_time                    | 4       | news_data2.E_role_info.infoID |       1 |             | 
+----+-------------+-------------+------+--------------------------------+--------------------------------+---------+-------------------------------+---------+-------------+
我认为，E_role_info到E_info的联合是通过infoID，所以如果不用到建立于，E_info(infoID)上的主键索引的话，其type指标应该是ALL,进行全表扫描，但是上边却显示在E_info用idx_id_time为索引的时候type为ref,这是为什么呢？

Answer 1

我认为，E_role_info到E_info的联合是通过infoID，所以如果不用到建立于，E_info(infoID)上的主键索引的话，其type指标应该是ALL,进行全表扫描从你EXPLAIN的结果来看，E_info上是建立的主键infoID的第一个SQL语句没有用到info_time索引，两个SQL语句都是按info_reprint字段排序，都在此字段上建立的索引，且都是用到了此索引　，差别只是WHERE中info_time字段是否使用索引

Answer 2

应该是在第二步的时候，做join的时候，ref news_data2.E_role_info.infoID 的时候 MySQL利用了 idx_E_role_info_roleID_time   索引进行了 E_role_info.info_time > '2009-03-01 00:00:00' AND E_role_info.info_time < '2009-04-01 00:00:00 的筛选。第一步，仅利用 idx_E_role_info_roleID_reprint 得到了 where roleID = 413382  ORDER BY info_reprint DESC 的集，这个集已经是排好序的了，自然不用 filesort, 所以此时返回的 rows  = 2707604第二步，利用 E_info   PRIMARY  来做 JOIN 时应该是同时使用了 idx_E_role_info_roleID_time 索引进行了筛选

Answer 3

这个问题有些难以解释。 这个问题可以简化为
explain select * from E_info where infoID in (40417005 ,40296524);
这个查询就会用 idx_id_time, 而 where infoID in (40417005); 则会用PRIMARY.有些奇怪。理论上你的 PRIMARY 和 idx_id_time 是完全等同的。 infoID 都已经是主键了，不会有重复的，按照（ infoID  ， info_time   ）和按照 （infoID  ）排序是完全相同的。 所以这种情况在实际中不应该发生。但至于MySQL为什么在IN多于一条记录时会选择（ infoID  ， info_time   ）而不是 （infoID  ）估计是其内部某些机制，因为两个索引完全相同。目前无法解释。

Answer 4

我认为这个说法是正确的:应该是在第二步的时候，做join的时候，ref news_data2.E_role_info.infoID 的时候 MySQL利用了 idx_E_role_info_roleID_time  索引进行了 E_role_info.info_time > '2009-03-01 00:00:00' AND E_role_info.info_time < '2009-04-01 00:00:00 的筛选。 第一步，仅利用 idx_E_role_info_roleID_reprint 得到了 where roleID = 413382  ORDER BY info_reprint DESC 的集，这个集已经是排好序的了，自然不用 filesort, 所以此时返回的 rows  = 2707604 第二步，利用 E_info  PRIMARY  来做 JOIN 时应该是同时使用了 idx_E_role_info_roleID_time 索引进行了筛选 

Answer 5

呵呵，第二个问题我是有点较真了！OReIlly.High.Performance.MySQL.Second.Edition.Jun.2008
607页也说EXPLAIN不一定总是说实话！

Answer 6

仔细研究一下eq_ref和ref的区别，明白了您说的意思了，infoID是主键，那就一定是唯一的，所以idx_id_time和PRIMARY在查询速度上应该是一样！现在已经把idx_id_time这个索引给删了，没什么意义还干扰！至于mysql优化器为什么要选择idx_id_time,算了，我也不认真了!呵呵

Answer 7

ref说明，在join本表的过程中，参考(reference)外部值用索引进行匹配！eq_ref与ref的不同就在于所使用的索引为unique index.primary key就在此列！

Answer 8

这个第一步中，是不是limit 0, 10 之后才join的呢？
high performance mysql上说explain并不显示limit后的rows

Answer 9

high performance mysql上说explain并不显示limit后的rows 
是的，这个你做个简单测试就可以知道了 select * from tbl1 where id<1000 order by id limit 10;LIMIT应该是在 第二步的时候做的。

Answer 10

意思是说join的同时limit 10
limit 出了十个后就不再join了？

Answer 11

意思是说join的同时limit 10 
limit 出了十个后就不再join了？