MySQL没有使用索引 [英] MySQL is not using an index

问题描述

我有下表

CREATE TABLE `Config` (
   `id` mediumint(9) NOT NULL AUTO_INCREMENT,
   `type_id` mediumint(9) DEFAULT NULL,
   `content_id` mediumint(9) DEFAULT NULL,
   `menu_id` int(11) DEFAULT NULL,
   `field` varchar(50) NOT NULL DEFAULT '',
   `value` text NOT NULL,
   PRIMARY KEY (`id`),
   KEY `menu_id` (`menu_id`) USING BTREE,
   KEY `type_id` (`type_id`,`content_id`,`menu_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1;

它充满了大约800k行的测试数据。每当我运行以下查询时，大约需要0.4秒才能完成：

It's filled with about 800k rows of test data. Whenever I run the following query it takes about 0.4 seconds to complete:

SELECT id, content_id, menu_id, field, `value`
FROM Config
WHERE type_id = ?
AND content_id = ?

解释告诉我，MySQL正在进行完整的表格扫描而不是使用索引：

An explain tells me, MySQL is doing a full tablescan instead of using an index:

id select_type table   type possible_keys key key_len ref   rows    Extra
1   SIMPLE     Config  ALL                  792674  Using where

有人可以解释我在这里做错了什么吗？索引是如何使用的，所以它在这里使用？有时候查询还有额外的条件 AND menu_id =？，这也应该从中受益。

Can someone please explain what I am doing wrong here? How has the index to be like so it's used here? Sometimes the query has the extra condition AND menu_id = ?, which should benefit from it, too.

推荐答案

我遇到一个问题，一个查询，它没有使用我指定的索引。事实证明，如果（查询的）结果超过某些行，MySQL将不会使用您的索引。例如，如果结果本身占用了大量的总行，则不会使用您的索引。但是，我没有具体的百分比。你可以尝试调整查询以返回较小的结果来测试这个理论。

I had a problem once with a query where it doesn't use the index that I specified. It turned out, MySQL won't use your index if the result (of your query) exceeds certain rows. For an example, if the result itself is taking a lot of your total rows, it won't use your index. However, I don't have the specific percentage. You could try adjusting the query to return smaller result to test this theory.

我对这个问题的疑问： MySQL日期时间索引无效

My question about the problem: MySQL datetime index is not working

这篇关于MySQL没有使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！