关于mysql 的btree索引（ACMAIN_CHM请进）

Question

http://forum.csdn.net/PointForum/Manage/TopicManageView.aspx?forumID=ba09fe7e-2fb7-42d3-805e-578a4a8485e1&topicID=a4b29946-d49e-4f87-881d-2fb6053c5294&date=2009-5-20+22:16:29 
接着这个帖子的问题。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          |                                                                                | 
+----+--------+-------------+--------+---------------+---------+---------+---------------------------+---------+----------------------------------------------+我怀疑是由于mysql btree索引原理的问题。
当用到按时间字段索引的时候，查询25669 会非常的慢！我查看了一下25669的结果，相对来说数量非常小，基本上是一个月有一条记录！
相应的413382非常多，基本不间断！
像这样的情况，是不是应该是跟btree的索引结构有关呢？
怎样才能使查询效率不受数据的分布情况干扰呢？

Answer 1

你执行两个查询时，分别退出MYSQL，再进入，测试一下速度，避免缓冲的影响MYSQL查询时只能使用一个索引，你要平衡JOIN 与 ORDER BY，
只有相对没有绝对，以不变应万变，基本不太可能。

Answer 2

哦，我后来查询的时候，用上了 SQL_NO_CACHE
一样的结果，差距太夸张，慢的4分多钟，快的立时得到结果！我刚接触比较复杂的sql,平衡JOIN 与 ORDER BY，有没有什么资料可看？
谢谢了！

Answer 3

简单地讲：平衡就是学会取舍
比如：
一般而言，在连接字段上建立索引
即在E_info.infoID = E_role_info.infoID 两个字段上建立索引，
因为你用ORDER BY，在info_time上建立索引，下述代码可以用到，测试一下，速度如何SELECT * FROM (
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) A
 ORDER BY info_time DESC LIMIT 0 , 10; 

Answer 4

哦，可是，查询这两个不同值的差距也太大了！强制索引与不强制索引彼此简直就是你死我活的关系！不好平衡！这条sql我执行了，6分多钟出的结果！

Answer 5

上面贴错了，应该是不用强制索引
SELECT * FROM (
ELECT 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 ) 
A ORDER BY info_time DESC LIMIT 0 , 10; 在info_time上建立索引没有？强制索引与不强制索引彼此简直就是你死我活的关系！不好平衡！
呵呵，是啊，所以要学会取舍。

Answer 6

执行一下
SELECT * FROM ( 
ELECT 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 ) 
A ORDER BY info_time DESC LIMIT 0 , 10;速度如何，能否将LEFT JOIN->INNER JOIN

Answer 7

SELECT * FROM ( 
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 ) 
A ORDER BY info_time DESC LIMIT 0 , 10;

Answer 8

WWWWA先生，很快啊！
可是如果把查询的值换为 413382 则速度又慢下来了！sql为：
SELECT * FROM ( 
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) 
A ORDER BY info_time DESC LIMIT 0 , 10;413382对应的记录很多，很不得每分钟就有好几百条插入！相应的25669非常少，在E_info中对应的记录1个月也就1~2条！

Answer 9

根据MySQL的优化条件，它会先以你的 roleID  来做判断，因为它认为你的 roleID  基本上的平均分布的，这样你表 E_role_info    中假设一共１０００００条记录，索引中一共有1000个不同值，则MySQL会以为（其实我也会这么认为）平均每个键有100000/1000 = 100行。　这样如果做查询，当然是首选先从表 E_role_info    进行筛选得到 100条记录的一个结果集，然后再进行下一步筛选。你的 info_time  在哪个表中？　如果分布不均，则你需要
1. analysis table E_role_info    ,让MySQL重新分析认识一下你的表（这种情况下用处不大）
2. 通过ＦＯＲＣＥ或ＳＱＬ语句的变型来强制使用人为的筛选顺序(这个在上个贴子中就提到，你现在认为的最佳顺序是什么？)

Answer 10

首先，慢的不是一星半点，大概4分多钟！其次，如果加上FORCE INDEX 
查413382会相当快，但记录少的25669反而相当慢，也是4分多钟这个级别！

Answer 11

分段运行：
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 = 413382sELECT 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再加上ORDE BY ，看看时间用到哪里去了

Answer 12

最初就是运行这个语句：
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;
遇上 413382 这个大数据量的查询的时候，非常慢！然后花两天学习了一下，期间去掉"ORDER BY info_time DESC",发现很快！
然后通过EXPLAIN 发现没用上info_time 的索引，故而加上FORCE INDEX(info_time),速度变得非常快！
本来非常有成就感，可是晴天霹雳！加上FORCE INDEX(info_time)碰上25669这样小数据量的查询的时候，非常的慢！！！
就是说，这两者倒了个！这是不知道为什么！

Answer 13

没错，现在还是这样，只要是不加ORDER BY, 不论413382还是25669，都非常快！

Answer 14

这些SQL语句不是我写的，我得先研究一下！

Answer 15

与B-tree索引结构无关，你这些有着相同KEY的值都会在一个结点下。假设你有两个表tA,tB , 都有10,000条记录  tA中INDEX(a)上有 1000个不同的KEY， tB 上 index(col2)上有不同的KEY 2000个。 这 样，当你 select ta.*,tb.* from ta,tb where ta.a=123 and tb.cl2=789 的时候你会怎么选？如果假设你的KEY分布极不均匀。 tA, index(a) 中 456 有2个，tB  index(col2) 中 555 结果有 4,000 个
而相反 tA, index(a) 中 666 有6000个,tB  index(col2) 中 999 结果有 3 个你很难用一个相同的SQL语句来进行查询。碰到这种情况，需要人为的找出一种平衡方案。所以需要知道，你想实现的功能是什么？取前10条记录？

Answer 16

这样，建立一个临时表， 结构与
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
一致，在info_time上建立索引，
执行
TRUNCATE 临时表
INSERT INTO 临时表
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 = 413382SELECT * FROM 临时表 ORDER BY info_time DESC LIMIT 0 , 10;测试一下速度如何

Answer 17

这样建立索引试试
create index idx_key on E_info(infoID,info_time);

Answer 18

真是太感谢了，即便最终解决不了，也学了不少知识！本帖追加100分！略表对二位的感激之心！我想想，好好想想！从最初开始：
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;
我这条sql语句的初衷是这样的：
E_role_info中roleID代表一个专题，一个专题会对应许多与该专题对应的文章，也就是 E_role_info.infoID!
所以E_role_info就是这二者多对多的对应表！而存储文章的信息记录在E_info这个表中！
这个sql语句的初衷便是要取得413382这个专题底下最新的10篇文章！所以联合查询之后，要按照时间去ORDER BY.
以下是这两个表部分截取的情况：
E_role_info表：
 SELECT * FROM E_role_info WHERE roleID = 413382 LIMIT 0,10;
+----------+--------+---------+
| infoID   | roleID | related |
+----------+--------+---------+
| 42245717 | 413382 |    1004 | 
| 42245750 | 413382 |    1006 | 
| 42245774 | 413382 |    1006 | 
| 42245776 | 413382 |    1004 | 
| 42245820 | 413382 |    1004 | 
| 42245766 | 413382 |    1004 | 
| 42245799 | 413382 |    1006 | 
| 42245812 | 413382 |    1005 | 
| 42245822 | 413382 |    1004 | 
| 42245679 | 413382 |    1004 | 
+----------+--------+---------+E_info表:
SELECT E_info.infoID, E_info.info_title, E_info.info_time FROM E_info WHERE E_info.infoID = 42245717 OR E_info.infoID = 42245774;
+----------+---------------------------------------+---------------------+
| infoID   | info_title                            | info_time           |
+----------+---------------------------------------+---------------------+
| 42245717 | 南昌工商巧助企业护“英雄”            | 2009-04-09 12:33:43 | 
| 42245774 | 4月12日北京龙翔永恒台球俱乐部千元周赛 | 2009-04-09 11:36:36 | 
+----------+---------------------------------------+---------------------+如您所见记录文章发布时间的info_time是在E_info这个表中，而在设计的时候为了避免冗余，没有在E_role_info中记录时间（这也许是个败笔），所以每次想按时间得到413382这个专题下最新的10篇文章时，总是那么麻烦，得先联合一下然后排序等等！

Answer 19

这个sql语句的初衷便是要取得413382这个专题底下最新的10篇文章！：
以info_time为标准？

Answer 20

你的表设计没有问题，符合范式的要求。你的查询分析如下
从逻辑上来说，
１：只能先做 WHERE E_role_info.roleID = 413382 ，
２：搜索 E_info 中 E_info.infoID = E_role_info.infoID 的，
３：根据 info_time排序，此时如果你的 info_time是单独的索引就已经没有作用了建议：创建一个 E_info(infoID,info_time)的索引。
create index idx_E_info_id_time ON E_info (infoID,info_time);

Answer 21

建立一个复合索引试试查询速度
create index idx_key on E_info(infoID,info_time); 

Answer 22

3. info_time确实是单独的索引！为什么单独索引没有作用了呢？语句对于infoID的顺序我并没有要求啊！建立E_info(infoID,info_time)的索引估计得等到中午或下班了！

Answer 23

MYSQL查询时只能使用一个索引，因为E_info.infoID = E_role_info.infoID
已经使用了索引，ORDER BY就不能使用了，info_time没有在使用
的索引中，
建立复合索引E_info(infoID,info_time)
E_info.infoID = E_role_info.infoID、ORDER BY
都可以使用索引

Answer 24

楼上这种解释不对。建议用EXPLAIN看一下，一个SQL语句到底能用几个索引。

Answer 25

关于为什么 单独字段的 (info_time)在这个查询中无法使用。 晚一些我准备个例子解释。

Answer 26

是在一次查询中，一个表只能使用一个索引，也就是说，E_info表使用了在infoID
上建立的索引，就不能使用其它索引，如在info_time上的索引而建立多列复合索引的时候，你实际上建立了MySQL可以使用的多而个索引。
复合索引可以作为多个索引使用

Answer 27

可以看一下官方文档中的说明。什么情况下，MySQL会进行多个索引合并。http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#index-merge-optimization
7.2.6. 索引合并优化
7.2.6.1. 索引合并交集访问算法
7.2.6.2. 索引合并并集访问算法
7.2.6.3. 索引合并排序并集访问算法
索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。

Answer 28

3. info_time确实是单独的索引！为什么单独索引没有作用了呢？语句对于infoID的顺序我并没有要求啊！
关于为什么在这个查询　WHERE E_role_info.roleID = 413382 order by info_time 中 (info_time)索引没有作用？原因是在这种情况下无论是谁都没法使用这个索引！假设有表如下
mysql> select * from tbl order by id;
+----+------+------+
| id | c1   | t    |
+----+------+------+
|  1 |    1 |   32 |
|  2 |    3 |   46 |
|  3 |    2 |   36 |
|  4 |    1 |   43 |
|  5 |    4 |    7 |
|  6 |    5 |    6 |
|  7 |    4 |    8 |
|  8 |    3 |   24 |
|  9 |    1 |   45 |
| 10 |    2 |    1 |
| 11 |    2 |   20 |
| 12 |    4 |   50 |
| 13 |    1 |   37 |
| 14 |    2 |   38 |
| 15 |    4 |   27 |
| 16 |    3 |   23 |
| 17 |    4 |   34 |
| 18 |    1 |   50 |
| 19 |    4 |   47 |
| 20 |    2 |   36 |
| 21 |    3 |   37 |
| 22 |    1 |   28 |
| 23 |    3 |   27 |
| 24 |    4 |    3 |
| 25 |    2 |   33 |
| 26 |    5 |    8 |
| 27 |    3 |   41 |
+----+------+------+
27 rows in set (0.00 sec)这个表中，id 为主键，自然有主键索引。然后我们也创建了 idx_t (t) 这个字段的索引。那么索引中放的是什么？B-Tree中放是的索引的值和指向记录的地址指针.
我们简化一下 idx_t (t) 这个索引，（这里我们直接用链表，b-tree的效果是一样的，不过每个非叶节点均是一个判断点)
那么idx_t (t) 应该如下，索引中有t本身的值，然后是记录的位置(这里简化了，直接用记录编号代替了)
+------+----+
| t    | id |
+------+----+
|    1 | 10 |
|    3 | 24 |
|    6 |  6 |
|    7 |  5 |
|    8 |  7 |
|    8 | 26 |
|   20 | 11 |
|   23 | 16 |
|   24 |  8 |
|   27 | 15 |
|   27 | 23 |
|   28 | 22 |
|   32 |  1 |
|   33 | 25 |
|   34 | 17 |
|   36 |  3 |
|   36 | 20 |
|   37 | 13 |
|   37 | 21 |
|   38 | 14 |
|   41 | 27 |
|   43 |  4 |
|   45 |  9 |
|   46 |  2 |
|   47 | 19 |
|   50 | 12 |
|   50 | 18 |
+------+----+
这样有了这个索引，当你想查找 最大的三个t值时，你可以很方便从最后取三条。当你想查找t=27的时候，最多log2(27)= 5次二分搜索你就可以定位了。现在假设我们在 c1 上也有单独的索引　idx_c(c1) 当我们要查找 c1=2 的 t 最三条记录怎么做？
通过 idx_c(c1) 我们可以快速的找到记录
+------+----+
| c1   | id |
+------+----+
|    2 | 10 |
|    2 | 11 |
|    2 | 25 |
|    2 |  3 |
|    2 | 20 |
|    2 | 14 |
|    3 | 16 |
|    3 |  8 |然后呢？这时你会发现  idx_t (t) 这个索引 根本无法使用！你只能通过  idx_c(c1) 得到的这个结果去取出所有记录然后进行排序！

Answer 29

ACMAIN_CHM 先生：
根据您所讲解的，回到我自己的例子上，是否可以这么理解！当我强制用info_time的去查询的时候，ORDER BY是痛快了，因为有info_time为索引： 
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;但是在JOIN阶段的时候，对于E_info.infoID的查询则没有用到主键索引！
这个结论可以用EXPLAIN来验证：
EXPLAIN SELECT * FROM `E_info` FORCE INDEX(info_time) LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID; 
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+----------+-------------+
| id | select_type | table       | type | possible_keys | key    | key_len | ref                     | rows     | Extra       |
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | ALL  | NULL          | NULL   | NULL    | NULL                    | 20161160 |             | 
|  1 | SIMPLE      | E_role_info | ref  | infoID        | infoID | 4       | news_data.E_info.infoID |        6 | Using index | 
+----+-------------+-------------+------+---------------+--------+---------+-------------------------+----------+-------------+
这里看到，E_info的key栏中为NULL.相应的：
EXPLAIN SELECT * FROM `E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID;
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+----------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref                     | rows     | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+----------+-------------+
|  1 | SIMPLE      | E_info      | index | NULL          | PRIMARY | 4       | NULL                    | 20160644 | Using index | 
|  1 | SIMPLE      | E_role_info | ref   | infoID        | infoID  | 4       | news_data.E_info.infoID |        6 | Using index | 
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+----------+-------------+
则有主键PRIMARY来索引。这也就是您在前边一直提到的 JOIN和ORDER BY之间的平衡！当初我怀着无比的成就感，自以为立下了汗马功劳，偏心于对ORDER BY的优化，用了如下语句：
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;
是不是就是因为，在JOIN阶段没有用到infoID主键的索引而导致速度奇慢？
但是为什么用同样的语句在查询413382这个数据量超级大的数值的时候，却很快？同样也没有在联合阶段主键的索引啊？
这种查询的速度跟数据两多寡有什么联系呢？

Answer 30

加了联合索引
create index idx_E_info_id_time ON E_info (infoID,info_time);
结果是25669这个少数据量的值算是快，但413382很慢。
EXPLAIN SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX(idx_E_info_id_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_role_info | ref  | infoID,roleID      | roleID             | 3       | const                        | 2895079 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | E_info      | ref  | idx_E_info_id_time | idx_E_info_id_time | 4       | news_data.E_role_info.infoID |       1 |                                              | 
+----+-------------+-------------+------+--------------------+--------------------+---------+------------------------------+---------+----------------------------------------------+看来这是个不可调和的矛盾！

Answer 31

重新看了一遍你的数据。结果上次搞错了。本来以为你E_info表中infoID是E_role_info表的roleID，以为这样会在E_info表中有很多重复的，所以需要建个index (infoID,info_time) 就象33楼例子中做一个 (c1,t)的索引。表E_info 中如果infoID本身就是主键，那么 index (infoID,info_time) 就根本没有任何意义了。这个索引完全等同于 infoID 上的主键索引 :-(不好意思，误导了你和wwwb/a我能想到的方法就是改表结构了。临时表对这个操作应该也没什么效率上的提高。把2895079丢到临时表，然后再排序，这和MySQL本身查询中的做法差不多了。

Answer 32

十分感谢！
E_info.infoID确实是主键！改变表结构，思考中 ...

Answer 33

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX (idx_e_info_time) INNER 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;mysql> show profile for query 1;
+--------------------+------------+
| Status             | Duration   |
+--------------------+------------+
| starting           |   0.000188 | 
| Opening tables     |   0.000025 | 
| System lock        |   0.000013 | 
| Table lock         |   0.000017 | 
| init               |   0.000076 | 
| optimizing         |   0.000029 | 
| statistics         |   0.000126 | 
| preparing          |   0.000034 | 
| executing          |   0.000007 | 
| Sorting result     |   0.000013 | 
| Sending data       | 986.438623 | 
| end                |   0.000020 | 
| query end          |   0.000006 | 
| freeing items      |   0.001498 | 
| logging slow query |   0.000011 | 
| logging slow query |   0.000006 | 
| cleaning up        |   0.000009 | 
+--------------------+------------+查看了这个语句的分段状态！主要是Sending data部分占了时间！
换个值：
mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT E_info.*, E_role_info.roleID, E_role_info.related FROM `E_info` FORCE INDEX (idx_e_info_time) INNER 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;mysql> show profile for query 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000296 | 
| Opening tables     | 0.000027 | 
| System lock        | 0.000010 | 
| Table lock         | 0.000018 | 
| init               | 0.000106 | 
| optimizing         | 0.000061 | 
| statistics         | 0.000490 | 
| preparing          | 0.000051 | 
| executing          | 0.000007 | 
| Sorting result     | 0.000024 | 
| Sending data       | 0.003657 | 
| end                | 0.000011 | 
| query end          | 0.000007 | 
| freeing items      | 0.001858 | 
| logging slow query | 0.000011 | 
| cleaning up        | 0.000010 | 
+--------------------+----------+
16 rows in set (0.00 sec)时间快很多，但终归还是花在sending data这个步骤上！这个语句随着记录的增加反而越快！

Answer 34

如果不加FORCE INDEX的话，效果就完全相反：mysql> SET profiling = 1; 
Query OK, 0 rows affected (0.00 sec)mysql> 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; mysql> show profile for query 1;
+------------------------------+------------+
| Status                       | Duration   |
+------------------------------+------------+
| starting                     |   0.000163 | 
| Opening tables               |   0.000026 | 
| System lock                  |   0.000010 | 
| Table lock                   |   0.000018 | 
| init                         |   0.000085 | 
| optimizing                   |   0.000029 | 
| statistics                   |   0.000126 | 
| preparing                    |   0.000036 | 
| Creating tmp table           |   0.000152 | 
| executing                    |   0.000007 | 
| Copying to tmp table         |  64.227596 | 
| converting HEAP to MyISAM    |   0.165946 | 
| Copying to tmp table on disk | 367.520187 | 
| Sorting result               | 129.176583 | 
| Sending data                 |   0.000237 | 
| end                          |   0.000004 | 
| removing tmp table           |   0.183795 | 
| end                          |   0.000012 | 
| query end                    |   0.000003 | 
| freeing items                |   0.000816 | 
| logging slow query           |   0.000005 | 
| logging slow query           |   0.000004 | 
| cleaning up                  |   0.000006 | 
+------------------------------+------------+
23 rows in set (0.02 sec)mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)mysql> 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;mysql> show profile for query 1; 
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000269 | 
| Opening tables       | 0.000027 | 
| System lock          | 0.000010 | 
| Table lock           | 0.000028 | 
| init                 | 0.000102 | 
| optimizing           | 0.000061 | 
| statistics           | 0.049792 | 
| preparing            | 0.000070 | 
| Creating tmp table   | 0.000135 | 
| executing            | 0.000007 | 
| Copying to tmp table | 0.322404 | 
| Sorting result       | 0.000086 | 
| Sending data         | 0.026573 | 
| end                  | 0.000014 | 
| removing tmp table   | 0.000045 | 
| end                  | 0.000010 | 
| query end            | 0.000006 | 
| freeing items        | 0.001571 | 
| logging slow query   | 0.000011 | 
| cleaning up          | 0.000012 | 
+----------------------+----------+
20 rows in set (0.01 sec)

Answer 35

如果不加FORCE INDEX的话，效果就完全相反： mysql> SET profiling = 1; 
Query OK, 0 rows affected (0.00 sec) mysql> 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; mysql> show profile for query 1; 
+------------------------------+------------+ 
| Status                      | Duration  | 
+------------------------------+------------+ 
| starting                    |  0.000163 | 
| Opening tables              |  0.000026 | 
| System lock                  |  0.000010 | 
| Table lock                  |  0.000018 | 
| init                        |  0.000085 | 
| optimizing                  |  0.000029 | 
| statistics                  |  0.000126 | 
| preparing                    |  0.000036 | 
| Creating tmp table          |  0.000152 | 
| executing                    |  0.000007 | 
| Copying to tmp table        |  64.227596 | 
| converting HEAP to MyISAM    |  0.165946 | 
| Copying to tmp table on disk | 367.520187 | 
| Sorting result              | 129.176583 | 
| Sending data                |  0.000237 | 
| end                          |  0.000004 | 
| removing tmp table          |  0.183795 | 
| end                          |  0.000012 | 
| query end                    |  0.000003 | 
| freeing items                |  0.000816 | 
| logging slow query          |  0.000005 | 
| logging slow query          |  0.000004 | 
| cleaning up                  |  0.000006 | 
+------------------------------+------------+ 
23 rows in set (0.02 sec) 
mysql> SET profiling = 1; 
Query OK, 0 rows affected (0.00 sec) mysql> 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; mysql> show profile for query 1; 
+----------------------+----------+ 
| Status              | Duration | 
+----------------------+----------+ 
| starting            | 0.000269 | 
| Opening tables      | 0.000027 | 
| System lock          | 0.000010 | 
| Table lock          | 0.000028 | 
| init                | 0.000102 | 
| optimizing          | 0.000061 | 
| statistics          | 0.049792 | 
| preparing            | 0.000070 | 
| Creating tmp table  | 0.000135 | 
| executing            | 0.000007 | 
| Copying to tmp table | 0.322404 | 
| Sorting result      | 0.000086 | 
| Sending data        | 0.026573 | 
| end                  | 0.000014 | 
| removing tmp table  | 0.000045 | 
| end                  | 0.000010 | 
| query end            | 0.000006 | 
| freeing items        | 0.001571 | 
| logging slow query  | 0.000011 | 
| cleaning up          | 0.000012 | 
+----------------------+----------+ 
20 rows in set (0.01 sec)