今天无意中发现的情况,请帮忙解答下,按说下面的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)
为什么优化器却走了非聚集索引,这样的话不是要遍历两个索引树么?难道优化器二了,我加了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)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货