今天无意中发现的情况,请帮忙解答下,按说下面的t1表的select * from t1的查询应该走主键索引才最快,
为什么优化器却走了非聚集索引,这样的话不是要遍历两个索引树么?难道优化器二了,我加了force index(id)都不走主键,非常不解,求解释。mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  5 | NULL |
|  6 | NULL |
|  7 | NULL |
|  8 | NULL |
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
8 rows in set (0.00 sec)mysql> explain select * from t1;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_name | 48      | NULL |    8 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)