非常简单的mysql查询不使用索引 [英] Very simple mysql query not using index

问题描述

我的mySQL表排序不使用索引，我也不知道为什么.

Sorting of my mySQL table does not use the index and I don't know why.

我有:

CREATE TABLE IF NOT EXISTS `test` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  KEY `kk` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

和这个:

EXPLAIN SELECT * 
FROM test
ORDER BY a

还有这个

EXPLAIN SELECT * 
FROM test
USE INDEX ( kk ) 
ORDER BY a

给我这个:

id select_type table type possible_keys key  key_len ref  rows  Extra
1  SIMPLE      test  ALL  NULL          NULL NULL    NULL 10009 Using filesort

我不想看到此文件排序，而是使用键kk对我的表进行排序.我在做什么错了?

I'd like not to see this filesort, and use the key kk to sort my table. What am I doing wrong?

谢谢您的发帖，他们回答了我的问题！但是，现在我不理解表扫描"和文件排序"的含义是什么?即使我选择一个表的所有字段和所有行，通过遍历该列索引的内部树O(n)然后在表中查找，按一列对该表进行排序不是更快吗?在O(1)中为每行归档请求的额外列=>索引文件将每行的物理位置存储在表文件中，或者?)，而不是进行排序，例如通过快速排序O(n * log n)(可能)在表文件中随机存储的行，而不触及索引?我想我对如何在MySQL中使用索引的理解是错误的.

Thank you for your posts guys, they answer my question! However, now I do not undestand what is meant by "table scan" and "filesort"? Even if I am selecting all fields and all rows of a table, isn't it faster to sort that table by one column by walking in O(n) the internal tree of the index of that column (and then looking up in the table file the extra columns requested, in O(1) for each row => the index file stores each row's physical position in the table file, or?), than to sort e.g. by quick sort in O(n * log n) the (potentially) randomly stored rows in the table file, without touching the index? I guess my understanding of how indexes work in mySQL is wrong.

推荐答案

1. 您要选择所有行
2. 您要选择所有列

按照我上面所说的-mysql估计使用完全扫描会更有效.

Following what I said above - mysql estimates it to be more efficient to use full scan.

要使用索引获取它，您需要添加一些WHERE，以将其限制为返回的合理行数(例如50)

To get it using index you need to add some WHERE that would limit it to reasonable number of rows returned (say 50)

这篇关于非常简单的mysql查询不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！