mysql查询中的索引问题

netxuning 2009-05-20 10:16:29
我的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！但会和这个有关系吗？
...全文
108 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2009-05-20
  • 打赏
  • 举报
 回复

和分面不均有关，因为MySQL中是认为分布是基本均匀的。

这种情况下你需要自己来确定最佳的逻辑。
是先取limit 10 ?还是先join ?
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
是这种分布极为不均导致的吗？
ACMAIN_CHM 2009-05-20
  • 打赏
  • 举报
 回复

那MySQL的运行计划还是正确的了。

如果你只需要显示前10个，那就试试把这个limit加到info_time所在的表中
select * from a, (select * from b order by info_time desc limit 10) b on a.id=b.id ....
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
是的，
select count(*) from E_role_info where roleID = 25669;
结果为45

select count(*) from E_role_info where roleID = 413382;
结果为3777809

但是这对我们的业务逻辑来说，是正常的！
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
针对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 | |
+----+--------+-------------+--------+---------------+---------+---------+---------------------------+---------+----------------------------------------------+
ACMAIN_CHM 2009-05-20
  • 打赏
  • 举报
 回复

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;
检查一下。
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
估计您看着还是不方便 我再整理一下

netxuning 2009-05-20
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 ACMAIN_CHM 的回复:]
能否不要用 \G,看起来不方便。
[/Quote]

好的，我这就改，重新贴上！

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)
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
发现在查询413382（大数据量） 这个值的时候，差别在于 Using where; Using temporary; Using filesort

而查寻小数据量的25669时，则rows的差别很大！
ACMAIN_CHM 2009-05-20
  • 打赏
  • 举报
 回复

能否不要用 \G,看起来不方便。
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
413382 这个值对应的数据集很大，
而25669则要少很多！
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 ACMAIN_CHM 的回复:]
你具体表的情况不清楚，建议你把explain 的结果贴出来，对比一下。

MySQL的SELECT优化会根据，索引，索引中分布（不同值的数量），WHERE后的条件进行优化。
[/Quote]

好的，谢谢您，马上贴：
区别好像就在于 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)


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

你具体表的情况不清楚，建议你把explain 的结果贴出来，对比一下。

MySQL的SELECT优化会根据，索引，索引中分布（不同值的数量），WHERE后的条件进行优化。
netxuning 2009-05-20
  • 打赏
  • 举报
 回复
time 在mytble1中，并建立了索引！
我大惑不解的是，不同的值怎么会差距那么大！


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

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


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

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语句的。

MYSQL索引优化与查询优化
课程总共分为三个章节： 第一章节主要对mysql常用的索引结构进行讲解 第二章将会从手把手带你演示sql索引优化的一些策略以及注意点 第三章将会演示工作中经常用到的sql查询优化知识点
Mysql | 查看表的索引
因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
MySQL索引性能优化分析
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql查看表索引_mysql查询表索引的命令show
mysql中查看索引我们会用到show命令,如果有show index或者show keys之类的,下面我们一起来看看mysql查询表索引的命令show具体方法吧.MySQL查询表索引命令的有两种命令形式,代码如下:mysql> SHOW index FROM `tblname`; 或者: mysql> SHOW keys FROM `tblname`;运行以上命令得到的结果,下面将将...
mysql如何查询索引状态？
mysql查询索引状态的方法：通过“SHOW INDEX”语句，语法格式“SHOW INDEX FROM [ FROM ]”；通过该语句可以查看索引的名称、是否是唯一索引、字段在索引中的位置，定义索引的列字段名等等。索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL 中，可以使用 SHOW INDEX 语句查看表中创建的索引。：指定需要查看索引的数据表所在的数据库，可省略。：指定需要查看索引的数据表名。SQL 语句和运行结果如下所示。...
MySQL

56,677

社区成员

56,708

社区内容

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

加载中

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

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

+ 用AI写文章