还是那个老问题

[netxuning]

为了提高速度，我已经添加了包括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,这是为什么呢？

[qgylovelj]

[Quote=引用 1 楼 WWWWA 的回复:]
我认为，E_role_info到E_info的联合是通过infoID，所以如果不用到建立于，E_info(infoID)上的主键索引的话，其type指标应该是ALL,进行全表扫描

从你EXPLAIN的结果来看，E_info上是建立的主键infoID的

第一个SQL语句没有用到info_time索引，

两个SQL语句都是按info_reprint字段排序，都在此字段上建立的索引，且都是用到了此索引　，差别只是WHERE中info_time字段是否使用索引
[/Quote]
up

[冰岛男孩]

mark

[yao_ge]

哎，第一次进来。。居然看不懂。。哎。。苦死了

[cby666168]

我认为这个说法是正确的:

应该是在第二步的时候，做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 索引进行了筛选

[netxuning]

呵呵，第二个问题我是有点较真了！

OReIlly.High.Performance.MySQL.Second.Edition.Jun.2008
607页也说EXPLAIN不一定总是说实话！

[snailery]

帮顶

[netxuning]

[Quote=引用 3 楼 ACMAIN_CHM 的回复:]
引用
我认为，E_role_info到E_info的联合是通过infoID，所以如果不用到建立于，E_info(infoID)上的主键索引的话，其type指标应该是ALL,进行全表扫描，
但是上边却显示在E_info用idx_id_time为索引的时候type为ref,这是为什么呢？


这个问题有些难以解释。 这个问题可以简化为
explain select * from E_info where infoID in (40417005 ,40296524);
这个查询就会用 idx_id_time, 而 where infoID in (40417005); 则会用PRIMAR…
[/Quote]

仔细研究一下eq_ref和ref的区别，明白了您说的意思了，infoID是主键，那就一定是唯一的，所以idx_id_time和PRIMARY在查询速度上应该是一样！现在已经把idx_id_time这个索引给删了，没什么意义还干扰！

至于mysql优化器为什么要选择idx_id_time,算了，我也不认真了!呵呵

[netxuning]

ref说明，在join本表的过程中，参考(reference)外部值用索引进行匹配！

eq_ref与ref的不同就在于所使用的索引为unique index.primary key就在此列！

[ACMAIN_CHM]

楼上研究得很细了。

查询的优化本身就很复杂，大的算法和优化策略各数据库都差不多，不过细节上有很多差别。这些细节的确很令人头痛。

[ACMAIN_CHM]

[Quote]我认为，E_role_info到E_info的联合是通过infoID，所以如果不用到建立于，E_info(infoID)上的主键索引的话，其type指标应该是ALL,进行全表扫描，
但是上边却显示在E_info用idx_id_time为索引的时候type为ref,这是为什么呢？ [/Quote]

这个问题有些难以解释。 这个问题可以简化为
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 ）估计是其内部某些机制，因为两个索引完全相同。目前无法解释。

[xinsheng508]

学习了！

[qyh562329258]

我是新手··都还看不懂这些东东··哎··

[jin20000]

没这仔细

[chunhua1026]

没用过mysql

[qunyingkj]

虚心学习

[xiangyubruce]

第一次进来。。看不懂。。郁闷

[k441373477]

好东西

[tianyufeng5023]

看一下，不错

[Seeya0730]

完全看不懂~旁听~

[hailangbei]

有收获啊