mysql查询中的索引问题

Question

我的sql语句如下，期待查询出与mytbl2中rid=5关联的最新的10条之内的记录。
SELECT * FROM `mytbl1` LEFT JOIN mytbl2 ON mytbl1.id = mytbl2.id WHERE mytbl2.rid = 5 ORDER BY time DESC LIMIT 0 , 10
但是问题出现了，当我查询5的时候，速度非常之慢，要6分钟之久，所以强制加上time的索引，则查询语句更改为：
SELECT * FROM `mytbl1` FORCE INDEX(time) LEFT JOIN mytbl2 ON mytbl1.id = mytbl2.id WHERE mytbl2.id = 5 ORDER BY time DESC LIMIT 0 , 10
速度非常快，立时就出结果。但用强制索引查询6的时候：
SELECT * FROM `mytbl1` FORCE INDEX(time) LEFT JOIN mytbl2 ON mytbl1.id = mytbl2.id WHERE mytbl2.id = 6 ORDER BY time DESC LIMIT 0 , 10
4分多钟才出结果。不可思议的是去掉强制索引反而很快：
SELECT * FROM `mytbl1` LEFT JOIN mytbl2 ON mytbl1.id = mytbl2.id WHERE mytbl2.rid = 6 ORDER BY time DESC LIMIT 0 , 10刚开始研究sql语句，很不明白这是为什么！5和6两个区别就是，5的数据集要远远多于6！但会和这个有关系吗？

Answer 1

time 在哪个表中？利用explain SELECT * FROM `mytbl1` LEFT JOIN mytbl2 ON mytbl1.id = mytbl2.id WHERE mytbl2.rid = 6 ORDER BY time DESC LIMIT 0 , 10;你可以看到MySQL是如何分析执行你的SQL语句的。

Answer 2

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#explain
7.2.1. EXPLAIN语法（获取SELECT相关信息）


Answer 3

time 在mytble1中，并建立了索引！
我大惑不解的是，不同的值怎么会差距那么大！

Answer 4

你具体表的情况不清楚，建议你把explain 的结果贴出来，对比一下。MySQL的SELECT优化会根据，索引，索引中分布（不同值的数量），WHERE后的条件进行优化。

Answer 5

好的，谢谢您，马上贴：
区别好像就在于 rows上，我本以为如果 Extra后边没有Using temporary; Using filesort就会很快，但是仔细查看，影响的行数差距那么大！这是我真实运行环境的结果：针对413382 这个值的查询：
mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10\G     *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_role_info
         type: ref
possible_keys: infoID,roleID
          key: roleID
      key_len: 3
          ref: const
         rows: 2870423
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: news_data.E_role_info.infoID
         rows: 1
        Extra: 
2 rows in set (0.00 sec)mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10\G     
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_info
         type: index
possible_keys: NULL
          key: info_time
      key_len: 8
          ref: NULL
         rows: 20041555
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_role_info
         type: ref
possible_keys: infoID,roleID
          key: infoID
      key_len: 4
          ref: news_data.E_info.infoID
         rows: 6
        Extra: Using where
2 rows in set (0.01 sec)以下是针对 25669的查询：
mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10\G                        *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_info
         type: index
possible_keys: NULL
          key: info_time
      key_len: 8
          ref: NULL
         rows: 20038496
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_role_info
         type: ref
possible_keys: infoID,roleID
          key: infoID
      key_len: 4
          ref: news_data.E_info.infoID
         rows: 6
        Extra: Using where
2 rows in set (0.00 sec)mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10\G                       
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_role_info
         type: ref
possible_keys: infoID,roleID
          key: roleID
      key_len: 3
          ref: const
         rows: 69
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E_info
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: news_data.E_role_info.infoID
         rows: 1
        Extra: 
2 rows in set (0.00 sec)

Answer 6

发现在查询413382（大数据量） 这个值的时候，差别在于 Using where; Using temporary; Using filesort 而查寻小数据量的25669时，则rows的差别很大！

Answer 7

好的，我这就改，重新贴上！mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10;
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key       | key_len | ref                     | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | NULL          | info_time | 8       | NULL                    | 20045840 |             | 
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID    | 4       | news_data.E_info.infoID |        6 | Using where | 
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10;                       
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+---------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                          | rows    | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | E_role_info | ref    | infoID,roleID | roleID  | 3       | const                        | 2872538 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY       | PRIMARY | 4       | news_data.E_role_info.infoID |       1 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+---------+----------------------------------------------+
mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10;      
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key       | key_len | ref                     | rows     | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | NULL          | info_time | 8       | NULL                    | 20046053 |             | 
|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID    | 4       | news_data.E_info.infoID |        6 | Using where | 
+----+-------------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10;                       
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                          | rows | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | E_role_info | ref    | infoID,roleID | roleID  | 3       | const                        |   68 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY       | PRIMARY | 4       | news_data.E_role_info.infoID |    1 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+------------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

Answer 8

E_role_info 表中25669只有 68条，而 413382 有 2872538 这么多重复条数？建议用
select count(*) from E_role_info  where roleID = 25669;
select count(*) from E_role_info  where roleID = 413382;
检查一下。

Answer 9

针对413382的查询mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10; 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ 
| id | select_type | table               | type      | possible_keys     | key              | key_len    | ref                                       | rows          | Extra               | 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ 
|  1 | SIMPLE      | E_info              | index    | NULL                   | info_time     | 8              | NULL                                    | 20045840   |                       | 
|  1 | SIMPLE      | E_role_info       | ref        | infoID,roleID       | infoID         | 4              | news_data.E_info.infoID        |        6         | Using where     | 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY info_time DESC LIMIT 0 , 10;   
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+----------------------------------------------+ 
| id | select_type | table               | type      | possible_keys     | key              | key_len    | ref                                       | rows          | Extra                                                                         | 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+----------------------------------------------+ 
|  1 | SIMPLE      | E_role_info       | ref        | infoID,roleID      | roleID          | 3              | const                                    | 2872538    | Using where; Using temporary; Using filesort             |
|  1 | SIMPLE      | E_info              | eq_ref   | PRIMARY            | PRIMARY      | 4              | news_data.E_role_info.infoID |      1          |                                                                                 | 
+--+----------+-------------+-------+---------------+-----------+---------+------------------------- +----------+----------------------------------------------+
针对25669的查询：
mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10;   
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ 
| id | select_type | table               | type      | possible_keys     | key              | key_len    | ref                                       | rows          | Extra               | 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ 
|  1 | SIMPLE      | E_info              | index    | NULL                   | info_time     | 8              | NULL                                    | 20046053   |                       | 
|  1 | SIMPLE      | E_role_info       | ref       | infoID,roleID        | infoID         | 4               | news_data.E_info.infoID        |        6        | Using where    | 
+--+----------+-------------+-------+---------------+-----------+---------+-------------------------+----------+-------------+ mysql> EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY info_time DESC LIMIT 0 , 10;    
+--+----------+-------------+--------+---------------+---------+---------+---------------------------+---------+----------------------------------------------+ 
| id | select_type | table               | type       | possible_keys      | key          | key_len    | ref                                          | rows         | Extra                                                                         | 
+--+----------+-------------+--------+---------------+---------+---------+------------------------------+------+----------------------------------------------+ 
|  1 | SIMPLE      | E_role_info      | ref          | infoID,roleID       | roleID      | 3              | const                                       |  68           | Using where; Using temporary; Using filesort             | 
|  1 | SIMPLE      | E_info              | eq_ref    | PRIMARY             | PRIMARY  | 4              | news_data.E_role_info.infoID    |    1           |                                                                                 | 
+----+--------+-------------+--------+---------------+---------+---------+---------------------------+---------+----------------------------------------------+ 

Answer 10

是的，
select count(*) from E_role_info  where roleID = 25669;
结果为45select count(*) from E_role_info  where roleID = 413382; 
结果为3777809但是这对我们的业务逻辑来说，是正常的！

Answer 11

那MySQL的运行计划还是正确的了。如果你只需要显示前10个，那就试试把这个limit加到info_time所在的表中
select * from a, (select * from b order by info_time desc limit 10) b on a.id=b.id ....

Answer 12

和分面不均有关，因为MySQL中是认为分布是基本均匀的。这种情况下你需要自己来确定最佳的逻辑。
是先取limit 10 ?还是先join ?