再次探讨MySQL MyISAM表性能问题 [英] MySQL MyISAM table performance problem revisited

问题描述

此问题与该问题有关.

我有一个具有以下结构的 page 表:

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;

数据列包含的文本大小约为80KB-每条记录200KB. 数据列中存储的数据总大小约为1.5GB.

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.

执行此查询需要 0.08 秒:

select pageid from page

但是执行此查询大约需要 130.0 秒:

But executing this query takes around 130.0 seconds:

select sourceid from page

如您所见，我在page.pageid上有一个主索引，在page.sourceid上有一个索引.那么第二个查询应该花很长的时间吗?

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?

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查询.

I'm sorry but profiling didn't work... MySQL (its 4.1.22) did not recognize SHOW PROFILE query.

SHOW INDEX 返回了

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 

推荐答案

您是否尝试强制使用索引?喜欢:

Did you try to enforce the use of the index? Like:

SELECT sourceid FROM page USE INDEX (sourceid_index)

像sgehrig注释一样，使用EXPLAIN检查是否使用了索引?并分享结果?

Like sgehrig comments, check using EXPLAIN if the index is used? And share the result?

EXPLAIN select sourceid from page

它也可能有助于共享索引的定义:

It could also help to share the definiton of the indexes:

SHOW INDEX FROM page

这篇关于再次探讨MySQL MyISAM表性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！