为什么mySQL在我的测试查询中不使用我的索引？

Question

我有一个简单的mysql表，我想用于日常的状态跟踪。在将一些信息放入其中并对测试结果进行测试后，我将使用它，它不会使用我的索引！我无法弄清楚为什么。

我试着放入更多的测试数据--200行。我也试着改变select *来选择created，value1。没有骰子。

mysql> show create table stat_general\G 
*************************** 1. row *************************** 
     Table: stat_general 
Create Table: CREATE TABLE `stat_general` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    `nameid` smallint(6) NOT NULL, 
    `value1` int(11) NOT NULL, 
    `value2` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `created` (`created`,`nameid`,`value2`) 
) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

mysql> select * from stat_general; 
+----+---------------------+--------+--------+--------+ 
| id | created    | nameid | value1 | value2 | 
+----+---------------------+--------+--------+--------+ 
| 1 | 2011-06-09 09:43:41 |  1 | 511803 | NULL | 
| 2 | 2011-06-09 09:43:41 |  2 |  44 | NULL | 
| 3 | 2011-06-09 09:43:41 |  3 | 9128 | NULL | 
| 4 | 2011-06-09 09:43:41 |  4 | 219 | NULL | 
| 5 | 2011-06-09 09:43:41 |  5 |  0 | NULL | 
| 6 | 2011-06-09 09:43:41 |  6 |  0 | NULL | 
| 7 | 2011-06-09 09:43:41 |  7 |  0 | NULL | 
| 8 | 2011-06-09 09:43:41 |  8 |  0 | NULL | 
| 9 | 2011-06-09 09:43:41 |  9 |  0 | NULL | 
| 10 | 2011-06-09 09:43:41 |  10 | 140 | NULL | 
| 11 | 2011-06-09 09:43:41 |  11 |  0 | NULL | 
| 12 | 2011-06-09 09:43:41 |  12 | 146 | NULL | 
| 13 | 2011-06-09 09:43:41 |  13 |  0 | NULL | 
| 14 | 2011-06-09 09:43:41 |  14 |  1 | NULL | 
| 15 | 2011-06-09 09:43:41 |  15 | 8981 | NULL | 
| 16 | 2011-06-09 09:43:41 |  16 |  1 | -127 | 
| 17 | 2011-06-09 09:43:41 |  16 |  2 |  -2 | 
| 18 | 2011-06-09 09:43:41 |  16 | 939 |  -1 | 
| 19 | 2011-06-09 09:43:41 |  16 | 146 |  1 | 
| 20 | 2011-06-09 09:43:41 |  16 | 8011 |  3 | 
| 21 | 2011-06-09 09:43:41 |  16 |  28 | 127 | 
| 22 | 2011-06-09 09:43:41 |  16 |  1 | 128 | 
| 23 | 2011-06-09 09:43:41 |  17 | 146 |  1 | 
| 24 | 2011-06-09 09:43:41 |  18 | 146 |  1 | 
| 25 | 2011-06-09 09:44:08 |  1 | 511803 | NULL | 
| 26 | 2011-06-09 09:44:08 |  2 |  44 | NULL | 
| 27 | 2011-06-09 09:44:08 |  3 | 9128 | NULL | 
| 28 | 2011-06-09 09:44:08 |  4 | 219 | NULL | 
| 29 | 2011-06-09 09:44:08 |  5 |  0 | NULL | 
| 30 | 2011-06-09 09:44:08 |  6 |  0 | NULL | 
| 31 | 2011-06-09 09:44:08 |  7 |  0 | NULL | 
| 32 | 2011-06-09 09:44:08 |  8 |  0 | NULL | 
| 33 | 2011-06-09 09:44:08 |  9 |  0 | NULL | 
| 34 | 2011-06-09 09:44:08 |  10 | 140 | NULL | 
| 35 | 2011-06-09 09:44:08 |  11 |  0 | NULL | 
| 36 | 2011-06-09 09:44:08 |  12 | 146 | NULL | 
| 37 | 2011-06-09 09:44:08 |  13 |  0 | NULL | 
| 38 | 2011-06-09 09:44:08 |  14 |  1 | NULL | 
| 39 | 2011-06-09 09:44:08 |  15 | 8981 | NULL | 
| 40 | 2011-06-09 09:44:08 |  16 |  1 | -127 | 
| 41 | 2011-06-09 09:44:08 |  16 |  2 |  -2 | 
| 42 | 2011-06-09 09:44:08 |  16 | 939 |  -1 | 
| 43 | 2011-06-09 09:44:08 |  16 | 146 |  1 | 
| 44 | 2011-06-09 09:44:08 |  16 | 8011 |  3 | 
| 45 | 2011-06-09 09:44:08 |  16 |  28 | 127 | 
| 46 | 2011-06-09 09:44:08 |  16 |  1 | 128 | 
| 47 | 2011-06-09 09:44:08 |  17 | 146 |  1 | 
| 48 | 2011-06-09 09:44:08 |  18 | 146 |  1 | 
+----+---------------------+--------+--------+--------+ 
48 rows in set (0.00 sec) 

mysql> select * from stat_general where created>'2011-06-09 9:44' AND nameid=1; 
+----+---------------------+--------+--------+--------+ 
| id | created    | nameid | value1 | value2 | 
+----+---------------------+--------+--------+--------+ 
| 25 | 2011-06-09 09:44:08 |  1 | 511803 | NULL | 
+----+---------------------+--------+--------+--------+ 
1 row in set (0.00 sec) 

mysql> explain select * from stat_general where created>'2011-06-09 9:44' AND nameid=1; 
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ 
| id | select_type | table  | type | possible_keys | key | key_len | ref | rows | Extra  | 
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ 
| 1 | SIMPLE  | stat_general | ALL | created  | NULL | NULL | NULL | 48 | Using where | 
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ 
1 row in set (0.00 sec) 

Answer 1

两个原因：

首先，你的表是太小了。有了这几行，无论查询如何，打开整个表格并逐行筛选行速度都会更快。

接下来，即使您有更多行的数据非常相似，您的当前索引也不够有选择性。在这种情况下，不值得随意打开磁盘页面;按顺序打开它们并将无效行过滤出来会更快。

Answer 2

只是为了咧嘴笑...您的查询主要基于给定的“nameID”或“Created”。如果填充NameID（即：发生了什么特定的人），我将改变指数和标准

KEY `byName` (`nameid`,`created`,`value2`) 

然后将查询到...

where nameid = 1 AND created > '2011-06-09 9:44' 

我还不能肯定，但如果该标准按照匹配索引的自然顺序，它可能直接利用它与猜测索引...（特别是对于较大的数据集）...否则@Denis是正确的，它只是查询数据页面，因为它很小，通过他们而不是试图想到应该使用哪个索引。