InnoDB和MyISAM的索引都是密集索引？

以前从一些资料上看到InnoDB的索引是稀疏索引，而MyISAM的索引是密集索引，今天刻意测试了一下，发现竟然不是这样。

找时间研究下，mark一下先。

mysql> show   uniq_id\G
*************************** .  ***************************
       : uniq_id
 :   `uniq_id` (
  `id` int()  ,
  KEY `id` (`id`)
) ENGINE=InnoDB  CHARSET=latin1
    (. sec)

mysql> show   same_id\G
*************************** .  ***************************
       : same_id
 :   `same_id` (
  `id` int()  ,
  KEY `id` (`id`)
) ENGINE=InnoDB  CHARSET=latin1
    (. sec)

mysql>  count(*),count( id)  same_id;
+
| count(*) | count( id) |
+
|   |                   |
+
    (. sec)

mysql>   uniq_id  (id);
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

mysql>   same_id  (id);
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

-rw-rw
-rw-rw

mysql>   uniq_id engine=innodb;
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

mysql>   same_id engine=innodb;
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

-rw-rw
-rw-rw

mysql> show   uniq_id2\G
*************************** .  ***************************
       : uniq_id2
 :   `uniq_id2` (
  `id` int()  ,
  `v` int()  ,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  CHARSET=latin1
    (. sec)

mysql> show   same_id2\G
*************************** .  ***************************
       : same_id2
 :   `same_id2` (
  `id` int()  ,
  `v` int()  ,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  CHARSET=latin1
    (. sec)


-rw-rw
-rw-rw

mysql>   same_id2  (v);
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

mysql>   uniq_id2  (v);
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

-rw-rw
-rw-rw

mysql>   uniq_id2 engine=innodb;
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

mysql>   same_id2 engine=innodb;
Query OK,   affected (. sec)
Records:   Duplicates:   Warnings: 

-rw-rw
-rw-rw

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10478177/viewspace-696362/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10478177/viewspace-696362/