重温MySQL MyISAM表性能问题
这个问题与这个相关。
我有一个具有以下结构的 page 表：
CREATE TABLE mydatabase.page (
pageid int(10) unsigned NOT NULL auto_increment,
sourceid int(10) unsigned default NULL,
number int(10) unsigned default NULL,
data mediumtext,
processed int(10) unsigned default NULL,
PRIMARY KEY (pageid),
KEY sourceid (sourceid)
) ENGINE=MyISAM AUTO_INCREMENT=9768 DEFAULT CHARSET=latin1;
data 列包含每条记录大小约为 80KB - 200KB 的文本。 data列中存储的数据总大小约为1.5GB。
执行此查询需要 0.08 秒：
select pageid from page
但是执行此查询大约需要 130.0 秒：
select sourceid from page
如您所见，我在 page.pageid 上有一个主索引，在 page 上有一个索引.sourceid。 那么第二个查询应该花费那么那么长的时间吗？
编辑 #1
EXPLAIN 返回
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE page index sourceid 5 9767 Using index
我很抱歉，但分析不起作用... MySQL（其 4.1.22）无法识别 SHOW PROFILE 查询。
返回显示索引
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
page 0 PRIMARY 1 pageid A 9767 BTREE
page 1 sourceid 1 sourceid A 3255 YES BTREE
This question is related to this one.
I have a page table with the following structure:
CREATE TABLE mydatabase.page (
pageid int(10) unsigned NOT NULL auto_increment,
sourceid int(10) unsigned default NULL,
number int(10) unsigned default NULL,
data mediumtext,
processed int(10) unsigned default NULL,
PRIMARY KEY (pageid),
KEY sourceid (sourceid)
) ENGINE=MyISAM AUTO_INCREMENT=9768 DEFAULT CHARSET=latin1;
The data column contains text whose size is around 80KB - 200KB per record. The total size of the data stored in the data column is around 1.5GB.
Executing this query takes 0.08 seconds:
select pageid from page
But executing this query takes around 130.0 seconds:
select sourceid from page
As you see, I've got a primary index on page.pageid and an index on page.sourceid. So should the second query be taking THAT long?
Edit #1
EXPLAIN returned
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE page index sourceid 5 9767 Using index
I'm sorry but profiling didn't work... MySQL (its 4.1.22) did not recognize SHOW PROFILE query.
SHOW INDEX returned
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
page 0 PRIMARY 1 pageid A 9767 BTREE
page 1 sourceid 1 sourceid A 3255 YES BTREE
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您是否尝试强制使用索引？ 像：
像 sgehrig 注释一样，使用 EXPLAIN 检查是否使用了索引？ 并分享结果？
它还可以帮助分享索引的定义：
Did you try to enforce the use of the index? Like:
Like sgehrig comments, check using EXPLAIN if the index is used? And share the result?
It could also help to share the definiton of the indexes:
您的 sourceid 字段有何不同？ 如果与行数相比只有几个不同的 sourceid 值，那么您可以尝试增加索引的大小。
How different are your sourceid fields? If you have only a few different sourceid values compared to the number of rows then you can try increasing the size of the index.
由于 MySQL 4.1.22 相当旧（2006 年 11 月 2 日），我怀疑它不支持辅助键覆盖索引的概念。
EXPLAIN显示查询实际上使用了索引，因此我假设需要额外的时间来读取所有结果行（而不是在使用覆盖索引时仅返回索引内容 /em>) 来提取sourceid列。您是否可以在更新的 MySQL 服务器版本上检查查询？
As MySQL 4.1.22 is fairly old (02 November 2006) I'd suspect that it doesn't support the notion of covering indexes for secondary keys.
EXPLAINshows that the query actually uses the index, so I'd assume that the additional time is needed to read all the result rows (instead of just returning the index content when using covering indexes) to extract thesourceidcolumn.Do you have the possibility to check the query on a more recent MySQL server version?