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

netxuning 2009-05-20 04:01:18
加精
https://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的索引结构有关呢？
怎样才能使查询效率不受数据的分布情况干扰呢？
...全文
6705 51 打赏 收藏 转发到动态 举报
写回复
用AI写文章
51 条回复
切换为时间正序
请发表友善的回复…
发表回复
我不是稻草人 2009-12-13
  • 打赏
  • 举报
 回复
好帖 好帖 顶
darkhorse00000111 2009-11-19
  • 打赏
  • 举报
 回复
这确实是个比较深奥的问题……
学习学习。
dinghdong 2009-06-21
  • 打赏
  • 举报
 回复
mark
chainjinghai 2009-05-25
  • 打赏
  • 举报
 回复
学习
ljd8726 2009-05-25
  • 打赏
  • 举报
 回复
定啊啊啊啊啊啊啊啊
owen_008 2009-05-25
  • 打赏
  • 举报
 回复
这个我还不懂，得先研究一下！
sql91 2009-05-25
  • 打赏
  • 举报
 回复
好东西
sanyecao111 2009-05-25
  • 打赏
  • 举报
 回复
如果不加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)

netxuning 2009-05-24
  • 打赏
  • 举报
 回复
如果不加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)

netxuning 2009-05-24
  • 打赏
  • 举报
 回复
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这个步骤上！

这个语句随着记录的增加反而越快！
netxuning 2009-05-22
  • 打赏
  • 举报
 回复
[Quote=引用 38 楼 ACMAIN_CHM 的回复:]
重新看了一遍你的数据。结果上次搞错了。本来以为你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

我能想到的方法就是改表结构了。临时表对…
[/Quote]

十分感谢！
E_info.infoID确实是主键！

改变表结构，思考中 ...
ACMAIN_CHM 2009-05-22
  • 打赏
  • 举报
 回复

重新看了一遍你的数据。结果上次搞错了。本来以为你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本身查询中的做法差不多了。
netxuning 2009-05-22
  • 打赏
  • 举报
 回复
加了联合索引
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 | |
+----+-------------+-------------+------+--------------------+--------------------+---------+------------------------------+---------+----------------------------------------------+

看来这是个不可调和的矛盾！




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

可以看一下官方文档中的说明。什么情况下，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扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。
WWWWA 2009-05-21
  • 打赏
  • 举报
 回复
是在一次查询中，一个表只能使用一个索引，也就是说，E_info表使用了在infoID
上建立的索引，就不能使用其它索引，如在info_time上的索引

而建立多列复合索引的时候，你实际上建立了MySQL可以使用的多而个索引。
复合索引可以作为多个索引使用
ACMAIN_CHM 2009-05-21
  • 打赏
  • 举报
 回复

关于为什么 单独字段的 (info_time)在这个查询中无法使用。 晚一些我准备个例子解释。
ACMAIN_CHM 2009-05-21
  • 打赏
  • 举报
 回复

楼上这种解释不对。建议用EXPLAIN看一下，一个SQL语句到底能用几个索引。
wwwwb 2009-05-21
  • 打赏
  • 举报
 回复
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
都可以使用索引
netxuning 2009-05-21
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 ACMAIN_CHM 的回复:]
你的表设计没有问题，符合范式的要求。

你的查询分析如下
从逻辑上来说，
１：只能先做 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);
[/Quote]

3. info_time确实是单独的索引！为什么单独索引没有作用了呢？语句对于infoID的顺序我并没有要求啊！

建立E_info(infoID,info_time)的索引估计得等到中午或下班了！
netxuning 2009-05-21
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 WWWWA 的回复:]
这个sql语句的初衷便是要取得413382这个专题底下最新的10篇文章！：
以info_time为标准？

[/Quote]

是的
加载更多回复（29）
MySQL索引
该课程介绍了数据库索引的实现原理，讲解了BTree和Hash的实现原理，通过原理分析，帮助学员分析如果使用索引，明白使用索引时的注意事项。
mysql最左索引理解_mysql索引最左匹配原则理解以及常见的sql使用的索引情况的实测...
# 一、常见例子 #mysql索引最左匹配原则主要是针对复合索引(联合索引)来说的，比如你在字段 a,b,c 建立了一个联合索引 index\_abcALTER TABLE `test`ADD INDEX `index_abc`(`a`,`b`, `c`) USING BTREE;1. explain ...
mysql索引类型btree，高级面试题+解析
多线程用过哪些，chm和countdownlatch在实习用过 二面： 自我介绍Java的集合类哪些是线程安全 分别说说这些集合类，hashmap怎么实现的，扯了很多 MySQL索引的实现，innodb的索引，b+tree索引是怎么实现的，为什么用...
MySQL索引相关原理解析
MySQL索引相关原理解析概述MySQL索引的数据结构B-Tree和B+TreeB-TreeB+Tree总结一下B+Tree和B-Tree哈希索引结构特点InnoDB支持哈希索引吗？聚簇索引和非聚簇索引概念：回表覆盖索引索引树InnoDB引擎下的表是索引组织...
MYSQL的索引和存储引擎
索引是通过某种算法,构建出一个数据模型,用于快速查出在某个列中有一特定值的行,不使用索引,MYSQL必须从第一行记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,...
MySQL

56,687

社区成员

56,710

社区内容

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

加载中

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

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

+ 用AI写文章