运行慢查询的简单MySQL表

I have a very simple table with two columns, but has 4.5M rows.

我有一个非常简单的表,有两列,但有4.5M行。

CREATE TABLE `content_link` (
  `category_id` mediumint(8) unsigned NOT NULL,
  `content_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`content_id`,`category_id`),
  KEY `content_id` (`content_id`,`category_id`)
) ENGINE=MyISAM;

When I run a simple query like:

当我运行一个简单的查询,如:

SELECT
    *   
FROM
    content_link
WHERE
    category_id = '11';

mysql spikes the CPU and takes 2-5 seconds before returning about 10 rows. The data is spread very evenly across the table and I'm accessing indexed fields (I've also analyzed/optimized the table and I never change the content of the table), so what reason is there for the query to take so long?

mysql激活CPU并在返回大约10行之前需要2-5秒。数据在表中非常均匀地传播,我正在访问索引字段(我还分析/优化了表,我从不更改表的内容),那么查询需要这么长时间的原因是什么?

Edit: It seems navicat was lying to me and my primary key was not actually keyed in the right order as it was displaying the table to me.

编辑:似乎navicat对我撒谎,我的主键实际上没有按正确的顺序键入,因为它正在向我显示表格。

3 个解决方案

#1

category_id is not the first column in any index.

category_id不是任何索引中的第一列。

Recreate your secondary key as follows:

重新创建您的二级密钥如下:

UNIQUE KEY `ix_category_id` (`category_id`, `content_id`)

Note the column order, it matters.

注意列顺序,这很重要。

#2

The UNIQUE KEY ordering is a good solution and you should add a partition strategy to your table too.

UNIQUE KEY排序是一个很好的解决方案,您也应该在表中添加分区策略。

By partitioning the table in fragments, MySQL will query the specific partition with the right data set. I've applied and I had excellent results.

通过将表分割成片段,MySQL将使用正确的数据集查询特定分区。我申请了,我的成绩非常好。

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

You need MySQL 5.1.

你需要MySQL 5.1。

Try http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

#3

You are not using the index. When you have a composite index like (content_id, category_id) you can use the index by using content_id or you can use content_id and category_id. You can't use category_id and utilize the index.

您没有使用索引。如果您有像(content_id,category_id)这样的复合索引,则可以使用content_id来使用索引,也可以使用content_id和category_id。您不能使用category_id并使用索引。

Try changing:

KEY `content_id` (`content_id`, `category_id`)

KEY `category_id` (`category_id`, `content_id`)

#1