还是上次那个问题（ACMAIN_CHM wwwwA, wwwwb等请进）

netxuning 2009-05-26 05:54:22
加精
http://topic.csdn.net/u/20090520/16/a96a2e90-a935-4460-837e-e52b4557c519.html
这个帖子中我一直强调的大数据量和小数据量的问题其实是个误导！
并不是因为数据量，而是某个索引没有起作用，导致了全表扫描！应该是这样，我不是百分百确定！

现在，删除了几个不必要的索引，把问题集中锁定在如下语句：
EXPLAIN SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, 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 E_info.infoID DESC LIMIT 1;
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
| 1 | SIMPLE | E_info | index | PRIMARY | PRIMARY | 4 | NULL | 7300143 | |
| 1 | SIMPLE | E_role_info | ref | infoID | infoID | 4 | news_data.E_info.infoID | 7 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+-------------------------+---------+-------------+
E_info.infoID是主键,而且上边E_info的type项已经是index了，但不清楚为什么rows项的数值还是那么大，7300143 这个数字也恰恰是E_info的行数！
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM E_info;
+----------+
| COUNT(*) |
+----------+
| 7300143 |
+----------+


所用的存储引擎是MyISAM,索引文件E_info.MYI是100M大小，我怀疑是索引太大，内存撑不下，老得访问磁盘所致，故而将key_buffer_size增加到512，可依然很慢，查第一条记录要18秒。
mysql>SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, 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 E_info.infoID DESC LIMIT 1;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42570652 | 2009-04-14 13:27:45 | 25669 | 1001 |
+----------+---------------------+--------+---------+
1 row in set (18.89 sec)



再描述一下E_role_info表，
mysql> show columns from E_role_info;
+---------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| infoID | int(8) unsigned | NO | MUL | 0 | |
| roleID | mediumint(6) unsigned | NO | | 0 | |
| related | smallint(3) unsigned | NO | | 0 | |
+---------+-----------------------+------+-----+---------+-------+
我在该表中插入一条记录
INSERT INTO E_role_info(infoID, roleID, related) values(42880361, 25669, 1);

这时很快就列出了刚刚记录的结果：
mysql> SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 1;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880361 | 2009-04-18 08:58:28 | 25669 | 1 |
+----------+---------------------+--------+---------+
1 row in set (0.00 sec)

但，如果列出两条的话，就会由于以前数据的拖累，而很慢：
SELECT SQL_NO_CACHE E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 2;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880361 | 2009-04-18 08:58:28 | 25669 | 1 |
| 42570652 | 2009-04-14 13:27:45 | 25669 | 1001 |
+----------+---------------------+--------+---------+
2 rows in set (17.78 sec)



通过结果可以看出，两次列出的值中42880361比42570652要新！

所以，我估计是由于某种原因E_info的主键没有用上，即便explain的type值显示的是index.
extra中没有using filesort说明应该不是ORDER BY的问题，很可能是JOIN阶段的问题:
`E_info` LEFT JOIN E_role_info ON E_info.infoID = E_role_info.infoID
E_info.infoID是本表的主键，E_role_info.infoID也建立了索引！




...全文
2000 64 打赏 收藏 转发到动态 举报
写回复
用AI写文章
64 条回复
切换为时间正序
请发表友善的回复…
发表回复
kevin_尛柒 2011-11-29
  • 打赏
  • 举报
 回复
精华帖，学习！虽然没看太懂
GIS_Cloud 2011-10-25
  • 打赏
  • 举报
 回复
学习了。
我不是稻草人 2009-12-13
  • 打赏
  • 举报
 回复
看来手册要看几遍才有效啊
ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

不是E_role_info(infoID, roleID) 是 E_role_info(roleID ,infoID )


[Quote=引用 35 楼 ACMAIN_CHM 的回复:]
建索引
create index idx_E_role_info_roleID on E_role_info(roleID,infoID);


[/Quote]
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
[Quote=引用 49 楼 ACMAIN_CHM 的回复:]
由于 E_role_info.roleID 的 Cardinality = 71639, 而你有记录 83101830 行, 所以MySQL会直接先进行 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID

| 1 | SIMPLE | E_info | index | PRIMARY | PRIMARY | 4 | NULL | 17868847 | |
此时会用到 E_info 的 PRIMARY 然后会用到 E_role_info.infoID
| 1 | SIMPLE | E_role_info | ref | infoID,roleID | infoID | 4 | news_data2.E_info.infoID | 6 | Using where |
[/Quote]

哦，原来会根据Cardinality 这个值来改变顺序啊！
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
呵呵，刚刚按照您说的建好了E_role_info(infoID, roleID)的主键（速度确实加快了不少）：
昨天晚上就开始弄，主要是E_role_info(infoID, roleID)含有大量的重复，还得过滤重复内容！

下面我列一下新的测试结果（语句还是上边那个语句）：



mysql> show index from E_info;

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| E_info |          0 | PRIMARY   |            1 | infoID      | A         |    17868847 |     NULL | NULL   |      | BTREE      |         |

| E_info |          1 | info_time |            1 | info_time   | A         |     8934423 |     NULL | NULL   |      | BTREE      |         |

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)









mysql> show index from E_role_info;

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| E_role_info |          0 | PRIMARY  |            1 | infoID      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |

| E_role_info |          0 | PRIMARY  |            2 | roleID      | A         |    82797994 |     NULL | NULL   |      | BTREE      |         |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)









mysql> EXPLAIN SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+

| id | select_type | table       | type   | possible_keys | key     | key_len | ref                            | rows     | Extra |

+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+

|  1 | SIMPLE      | E_info      | index  | PRIMARY       | PRIMARY | 4       | NULL                           | 17868847 |       |

|  1 | SIMPLE      | E_role_info | eq_ref | PRIMARY       | PRIMARY | 7       | news_data2.E_info.infoID,const |        1 |       |

+----+-------------+-------------+--------+---------------+---------+---------+--------------------------------+----------+-------+

2 rows in set (0.00 sec)









mysql> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)



mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |

| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |

| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |

| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |

| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |

| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |

| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |

| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |

| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |

| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |

+----------+---------------------+--------+---------+

10 rows in set (1 min 14.45 sec)



mysql> show profile;

+--------------------------------+-----------+

| Status                         | Duration  |

+--------------------------------+-----------+

| (initialization)               | 0.000002  |

| checking query cache for query | 0.0000900 |

| Opening tables                 | 0.000011  |

| System lock                    | 0.000005  |

| Table lock                     | 0.000008  |

| init                           | 0.000023  |

| optimizing                     | 0.000015  |

| statistics                     | 0.00002   |

| preparing                      | 0.000016  |

| executing                      | 0.000003  |

| Sorting result                 | 0.000003  |

| Sending data                   | 74.450095 |

| end                            | 0.000008  |

| query end                      | 0.000003  |

| freeing items                  | 0.00001   |

| closing tables                 | 0.000004  |

| logging slow query             | 0.000002  |

+--------------------------------+-----------+

17 rows in set (0.01 sec)
ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

下面是你目录的索引情况。
mysql> show index from E_info;

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| E_info |          0 | PRIMARY   |            1 | infoID      | A         |17868847 |     NULL | NULL   |      | BTREE      |         |

| E_info |          1 | info_time |            1 | info_time   | A         | 8934423 |     NULL | NULL   |      | BTREE      |         |

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.05 sec)



mysql> show index from E_role_info;

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| E_role_info |          1 | infoID   |            1 | infoID      | A         |    13850305 |     NULL | NULL   |      | BTREE      |         |

| E_role_info |          1 | roleID   |            1 | roleID      | A         |       71639 |     NULL | NULL   |      | BTREE      |         |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.03 sec)



mysql>


我们看一下MySQL会怎么执行？

SELECT STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID
WHERE E_role_info.roleID = 25669
ORDER BY E_info.infoID
DESC LIMIT 0 , 10;

由于 E_role_info.roleID 的 Cardinality = 71639, 而你有记录 83101830 行, 所以MySQL会直接先进行 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID

| 1 | SIMPLE | E_info | index | PRIMARY | PRIMARY | 4 | NULL | 17868847 | |
此时会用到 E_info 的 PRIMARY 然后会用到 E_role_info.infoID
| 1 | SIMPLE | E_role_info | ref | infoID,roleID | infoID | 4 | news_data2.E_info.infoID | 6 | Using where |


[Quote]这里我已经将ORDER BY info_time改为ORDER BY E_info.infoID，最后还是很慢！[/Quote]

和问题4相同。 JOIN完后，order by infoID 现在有什么索引可用吗？ 无
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
所以在这个sql语句中，我设想join阶段和order by E_info.infoID都会用到主键的索引，也就是E_info(infoID)上的索引！
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
如果是我考虑这些问题
问题１：你是先做 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID ,还是先做 WHERE E_role_info.roleID = 25669 ?
答：应该先做WHERE E_role_info.roleID = 25669 这样可以在join阶段过滤大量没有必要的结果集！
问题２：如果先做 WHERE E_role_info.roleID = 25669 这时候你会选择哪个索引　？　E_role_info(roleID)？
答：没错，就我粗浅的水平来说，我会选择E_role_info(roleID)
问题３：where 之后的结果集你已经有了。现在做 JOIN ，此时有哪些索引可以用？ E_info (infoID) 主键。
答：我认为，join会用到E_info(infoID)的索引，理由是，where的结果出来后，会遍历这个E_role_info.roleID = 25669 的结果集，遍历的过程中会针对每个E_role_info(infoID)去找到相应的E_info(infoID),这样的话应该是选择E_info(infoID)上的索引，这样会比较快！
总是４：JOIN完后，order by info_time 现在有什么索引可用吗？ 无
答：是的，我也觉得无。可是现在为什么order by E_info.infoID的情况下E_info(infoID)也没有用呢？


这里我已经将ORDER BY info_time改为ORDER BY E_info.infoID，最后还是很慢！


mysql> EXPLAIN SELECT STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

| id | select_type | table       | type  | possible_keys | key     | key_len | ref                      | rows     | Extra       |

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |

|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

2 rows in set (0.00 sec)
ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

是的，这个主键很难有什么作用，E_info(infoID) 是主键，但即使由你现在人工来决定，这个主键也没什么大用啊。只是在JOIN的时候会用上。

比如现在一切由你人工决定：
问题１：你是先做 `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID ,还是先做 WHERE E_role_info.roleID = 25669 ?
问题２：如果先做 WHERE E_role_info.roleID = 25669 这时候你会选择哪个索引　？　E_role_info(roleID)？
问题３：where 之后的结果集你已经有了。现在做 JOIN ，此时有哪些索引可以用？ E_info (infoID) 主键。
总是４：JOIN完后，order by info_time 现在有什么索引可用吗？ 无
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
[Quote=引用 43 楼 ACMAIN_CHM 的回复:]
SQL codemysql> select count(*) from E_role_info where roleID=413382;
+----------+
| count(*) |
+----------+
| 3089656 |
+----------+
1 row in set (0.94 sec)

mysql>




这种情况，换成人来决定，如何进行查询？


一种方法如下。

SQL codeselect b.infoID,b.info_time,a.roleID,a.related from ( select roleID,related,infoID from E_role_info where roleID=413382 order by infoID desc li…
[/Quote]

这个确实挺快的，已经开始应用，呵呵！谢谢您了！

不过，改为order by infoID 只是中间的权宜之计，最后还得改回order by info_time.
之所以目前改为order by infoID，是想看看join和order by统一了针对一个索引之后，是否能够很快。
可是还是不快！



mysql> EXPLAIN SELECT STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

| id | select_type | table       | type  | possible_keys | key     | key_len | ref                      | rows     | Extra       |

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

|  1 | SIMPLE      | E_info      | index | PRIMARY       | PRIMARY | 4       | NULL                     | 17868847 |             |

|  1 | SIMPLE      | E_role_info | ref   | infoID,roleID | infoID  | 4       | news_data2.E_info.infoID |        6 | Using where |

+----+-------------+-------------+-------+---------------+---------+---------+--------------------------+----------+-------------+

2 rows in set (0.00 sec)









mysql> SELECT STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42570652 | 2009-04-14 13:27:45 |  25669 |    1001 |

| 41944750 | 2009-04-03 20:22:06 |  25669 |    1007 |

| 41689393 | 2009-03-31 18:50:38 |  25669 |    1001 |

| 40621938 | 2009-03-17 19:46:21 |  25669 |    1008 |

| 39723719 | 2009-03-03 06:56:16 |  25669 |    1001 |

| 38720505 | 2009-02-13 17:19:20 |  25669 |    1002 |

| 35942322 | 2008-12-15 16:10:12 |  25669 |    1007 |

| 34660663 | 2008-11-20 21:19:51 |  25669 |    1003 |

| 34014787 | 2008-11-08 14:09:59 |  25669 |    1004 |

| 33803833 | 2008-11-04 21:31:38 |  25669 |    1002 |

+----------+---------------------+--------+---------+

10 rows in set (2 min 51.09 sec)



很疑惑的是，在这个问题中，即便infoID是主键，性能还是那么糟糕，而且在explain type那一栏显示了是index。
我现在很想知道，在这个sql语句中，主键没起作用吗？

我想先解决order by infoID在这个语句中索引失效的问题，然后再慢慢解决order by info_time的问题！不知道思路对不对！
xxfz0105 2009-05-29
  • 打赏
  • 举报
 回复
真么难
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
[Quote=引用 58 楼 ACMAIN_CHM 的回复:]
目前这种优化仅对你 WHERE E_role_info.roleID = 413382 ORDER BY E_role_info.infoID DESC LIMIT 10; where order 都从一表中取数据有作用。否则这个idx_E_role_info_roleID是用不上的了。


SQL codemysql> select SQL_NO_CACHE STRAIGHT_JOIN
-> E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related
-> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` …
[/Quote]

太谢谢了，好好学习，加深理解！
ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

目前这种优化仅对你 WHERE E_role_info.roleID = 413382 ORDER BY E_role_info.infoID DESC LIMIT 10; where order 都从一表中取数据有作用。否则这个idx_E_role_info_roleID是用不上的了。

mysql> select SQL_NO_CACHE STRAIGHT_JOIN

    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related

    ->  FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID

    ->  WHERE E_role_info.roleID = 413382

    ->  ORDER BY E_role_info.infoID DESC LIMIT 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42880359 | 2009-04-18 07:54:14 | 413382 |       0 |

| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 |

| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880341 | 2009-04-18 06:23:47 | 413382 |       0 |

| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 |

| 42880339 | 2009-04-18 07:58:28 | 413382 |       0 |

| 42880338 | 2009-04-18 06:23:47 | 413382 |       0 |

| 42880336 | 2009-04-18 06:23:47 | 413382 |       0 |

+----------+---------------------+--------+---------+

10 rows in set (0.06 sec)



mysql>
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
知道了，ACMAIN_CHM先生一直在让我缩小join的范围，并提高，从而加快速度！
所以，您一直在考虑如何优化这个语句：


SELECT SQL_NO_CACHE STRAIGHT_JOIN

     E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related

     FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID

     WHERE E_role_info.roleID = 25669

     ORDER BY E_info.infoID DESC LIMIT 10;



而我一直抓住E_info表中的主键为什么不起作用而不放！
在琢磨：


SELECT SQL_NO_CACHE STRAIGHT_JOIN 

     E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related 

     FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID 

     WHERE E_role_info.roleID = 25669 

     ORDER BY E_info.infoID DESC LIMIT 0 , 10;


用先生您提供的语句查413382时也还慢！


mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN      E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related      FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID      WHERE E_role_info.roleID = 413382      ORDER BY E_info.infoID DESC LIMIT 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42880359 | 2009-04-18 07:54:14 | 413382 |       0 |

| 42880353 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880351 | 2009-04-18 08:58:20 | 413382 |       0 |

| 42880348 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880347 | 2009-04-18 08:58:21 | 413382 |       0 |

| 42880341 | 2009-04-18 06:23:47 | 413382 |       0 |

| 42880340 | 2009-04-18 08:57:59 | 413382 |       0 |

| 42880339 | 2009-04-18 07:58:28 | 413382 |       0 |

| 42880338 | 2009-04-18 06:23:47 | 413382 |       0 |

| 42880336 | 2009-04-18 06:23:47 | 413382 |       0 |

+----------+---------------------+--------+---------+

10 rows in set (33.43 sec)



现在我的困扰还是413382,25669这两个值不可兼得！
总之在我的应用中，413382，25669这两个值恰好反映了我们系统中两个极端的情况，我也不知道是否能够达到一个平衡，使得查询这两个值都能够很迅速！这两天也很急躁，不过跟着您的思路也学到了不少知识，非常感谢！

现在我就顺着您的思路走，这样应该会更有效！
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
[Quote=引用 55 楼 ACMAIN_CHM 的回复:]
楼主啊，注意细节，你的语句和我37楼 的并不一样啊。
[/Quote]

呵呵，我有点乱！先屡屡这个帖子先！

ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

楼主啊，注意细节，你的语句和我37楼 的并不一样啊。

mysql> explain

    -> select SQL_NO_CACHE STRAIGHT_JOIN

    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related

    -> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID

    -> WHERE E_role_info.roleID = 25669

    -> ORDER BY E_info.infoID DESC LIMIT 10;

+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---

| id | select_type | table       | type   | possible_keys          | key                    | key_len | ref                           | rows | Ex

+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---

|  1 | SIMPLE      | E_role_info | ref    | idx_E_role_info_roleID | idx_E_role_info_roleID | 3       | const                         |   40 | Us

|  1 | SIMPLE      | E_info      | eq_ref | PRIMARY                | PRIMARY                | 4       | news_data2.E_role_info.infoID |    1 |   

+----+-------------+-------------+--------+------------------------+------------------------+---------+-------------------------------+------+---

2 rows in set (0.05 sec)



mysql> select SQL_NO_CACHE STRAIGHT_JOIN

    ->  E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related

    -> FROM E_role_info FORCE INDEX( idx_E_role_info_roleID ) INNER JOIN `E_info` ON E_info.infoID = E_role_info.infoID

    -> WHERE E_role_info.roleID = 25669

    -> ORDER BY E_info.infoID DESC LIMIT 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |

| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |

| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |

| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |

| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |

| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |

| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |

| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |

| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |

| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |

+----------+---------------------+--------+---------+

10 rows in set (1.00 sec)



mysql>
netxuning 2009-05-29
  • 打赏
  • 举报
 回复
[Quote=引用 53 楼 ACMAIN_CHM 的回复:]
看到你的索引了。

SQL codemysql> show index from E_role_info;
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-
…
[/Quote]



mysql> explain SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+

| id | select_type | table       | type  | possible_keys          | key                    | key_len | ref                            | rows     | Extra |

+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+

|  1 | SIMPLE      | E_info      | index | PRIMARY                | PRIMARY                | 4       | NULL                           | 17868847 |       |

|  1 | SIMPLE      | E_role_info | ref   | idx_E_role_info_roleID | idx_E_role_info_roleID | 7       | const,news_data2.E_info.infoID |        1 |       |

+----+-------------+-------------+-------+------------------------+------------------------+---------+--------------------------------+----------+-------+

2 rows in set (0.00 sec)



mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM `E_info` INNER JOIN E_role_info FORCE INDEX(idx_E_role_info_roleID) ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 25669 ORDER BY E_info.infoID DESC LIMIT 0 , 10;

+----------+---------------------+--------+---------+

| infoID   | info_time           | roleID | related |

+----------+---------------------+--------+---------+

| 42570652 | 2009-04-14 13:27:45 |  25669 |       0 |

| 41944750 | 2009-04-03 20:22:06 |  25669 |       0 |

| 41689393 | 2009-03-31 18:50:38 |  25669 |       0 |

| 40621938 | 2009-03-17 19:46:21 |  25669 |       0 |

| 39723719 | 2009-03-03 06:56:16 |  25669 |       0 |

| 38720505 | 2009-02-13 17:19:20 |  25669 |       0 |

| 35942322 | 2008-12-15 16:10:12 |  25669 |       0 |

| 34660663 | 2008-11-20 21:19:51 |  25669 |       0 |

| 34014787 | 2008-11-08 14:09:59 |  25669 |       0 |

| 33803833 | 2008-11-04 21:31:38 |  25669 |       0 |

+----------+---------------------+--------+---------+

10 rows in set (1 min 13.66 sec)




为什么我这还不行？在查询25669的时候？

不过，这个时间倒是比以前的4分多钟快多了！确实是门学问啊，呵呵！
ACMAIN_CHM 2009-05-29
  • 打赏
  • 举报
 回复

看到你的索引了。
mysql> show index from E_role_info;

+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-

| Table       | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | 

+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-

| E_role_info |          0 | PRIMARY                |            1 |      infoID | A         |        NULL | 

| E_role_info |          0 | PRIMARY                |            2 |      roleID | A         |    82797994 | 

| E_role_info |          1 | idx_E_role_info_roleID |            1 |      roleID | A         |       71624 | 

| E_role_info |          1 | idx_E_role_info_roleID |            2 |      infoID | A         |    82797994 | 

+-------------+------------+------------------------+--------------+-------------+-----------+-------------+-

4 rows in set (0.05 sec)



mysql> explain

    -> select roleID,related,infoID

    -> from E_role_info

    -> where roleID=413382

    -> order by infoID

    -> desc limit 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 | idx_E_role_info_roleID | 3       | const | 2753512 | Using where |

+----+-------------+-------------+------+------------------------+------------------------+---------+-------+---------+-------------+

1 row in set (0.03 sec)



mysql> select roleID,related,infoID

    -> from E_role_info

    -> where roleID=413382

    -> order by infoID

    -> desc limit 10 ;

+--------+---------+----------+

| roleID | related | infoID   |

+--------+---------+----------+

| 413382 |       0 | 42880359 |

| 413382 |       0 | 42880353 |

| 413382 |       0 | 42880351 |

| 413382 |       0 | 42880348 |

| 413382 |       0 | 42880347 |

| 413382 |       0 | 42880341 |

| 413382 |       0 | 42880340 |

| 413382 |       0 | 42880339 |

| 413382 |       0 | 42880338 |

| 413382 |       0 | 42880336 |

+--------+---------+----------+

10 rows in set (0.05 sec)



mysql>


这样，速度应该没问题了，但如果想解决 order by info_time 仍是不行。关键是要先能够人为的发现一条查找的路径。
ACMAIN_CHM 2009-05-28
  • 打赏
  • 举报
 回复 

mysql> select count(*) from E_role_info where roleID=413382;

+----------+

| count(*) |

+----------+

|  3089656 |

+----------+

1 row in set (0.94 sec)



mysql>



这种情况，换成人来决定，如何进行查询？


一种方法如下。
select b.infoID,b.info_time,a.roleID,a.related from ( select roleID,related,infoID from E_role_info where roleID=413382 order by infoID desc limit 10) a inner join E_info b on a.infoID=b.infoID order by b.info_

这样通过 select roleID,related,infoID from E_role_info where roleID=413382 order by infoID desc limit 10 只返回10条，速度应该可以提高。
但不幸的是，你的索引不对, 为了加快这个查询 where roleID=413382 order by infoID 需要一个 index (roleID,infoID)的索引

mysql> show index from E_role_info;

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| E_role_info |          1 | infoID   |            1 | infoID      | A         |    13850305 |     NULL | NULL   |      | BTREE      |         |

| E_role_info |          1 | roleID   |            1 | roleID      | A         |       71639 |     NULL | NULL   |      | BTREE      |         |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.05 sec)

加载更多回复（41）
MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论
ACMAIN_CHM06-26 16:36 等级 84次回复 [求证&散分]MySQL 中 where id in (1,2,3,4,...) 的效率问题讨论 庆祝本月大版得分过万，兼把在这段论坛中经常被问到的一个问题拿出来大家讨论一下。 命题...
mysqldump导出数据库视图_mysql中如何用mysqldump批量如何导出视图view啊？
问题详情mysql中如何用mysqldump批量如何导出视图view啊？mysql中如何用mysqldump批量如何导出视图view啊？我有3个库act、fdp、vtd里面有80张左右的视图，我想搬到生产环境中，mysqldump能不能一次批量导出啊？作者...
mysql dump 字段_mysqldump如何导出部分字段的部分数据
你的位置:问答吧->... 问题详情mysqldump如何导出部分字段的部分数据表结构如下CREATE TABLE `info` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`ReporterName` varchar(50) NOT NULL,`Re...
linux mysql 创建触发器_mysql创建触发器问题
你的位置:问答吧->... 问题详情mysql创建触发器问题我想在mysql上创建一个触发器，原代码如下CREATE TRIGGER comment_afterdelete_on_mynewbefore DELETE ON tb1_mynewFOR EACH ROWBEGINselect * from...
php 创建mysql 触发器_mysql创建触发器问题
你的位置:问答吧->... 问题详情mysql创建触发器问题我想在mysql上创建一个触发器，原代码如下CREATE TRIGGER comment_afterdelete_on_mynewbefore DELETE ON tb1_mynewFOR EACH ROWBEGINselect * from...
MySQL

56,681

社区成员

56,709

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章