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

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

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

  1 mysql> show create table uniq_id;
  2 +---------+---------------------------------------------------------------------------------------------+
  3 | Table   | Create Table                                                                                |
  4 +---------+---------------------------------------------------------------------------------------------+
  5 | uniq_id | CREATE TABLE `uniq_id` (
  6   `id` int(11) DEFAULT NULL
  7 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
  8 +---------+---------------------------------------------------------------------------------------------+
  9 1 row in set (0.00 sec)
 10 
 11 mysql> select count(*),count(distinct id) from uniq_id;
 12 +----------+--------------------+
 13 | count(*) | count(distinct id) |
 14 +----------+--------------------+
 15 |  1000000 |            1000000 |
 16 +----------+--------------------+
 17 1 row in set (1.53 sec)
 18 
 19 
 20 mysql> show create table same_id;
 21 +---------+---------------------------------------------------------------------------------------------+
 22 | Table   | Create Table                                                                                |
 23 +---------+---------------------------------------------------------------------------------------------+
 24 | same_id | CREATE TABLE `same_id` (
 25   `id` int(11) DEFAULT NULL
 26 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 27 +---------+---------------------------------------------------------------------------------------------+
 28 1 row in set (0.00 sec)
 29 
 30 mysql> select count(*),count(distinct id) from same_id;
 31 +----------+--------------------+
 32 | count(*) | count(distinct id) |
 33 +----------+--------------------+
 34 |  1000000 |                  1 |
 35 +----------+--------------------+
 36 1 row in set (0.21 sec)
 37 
 38 mysql> alter table uniq_id add index(id);
 39 Query OK, 1000000 rows affected (1.27 sec)
 40 Records: 1000000  Duplicates: 0  Warnings: 0
 41 
 42 mysql> alter table same_id add index(id);
 43 Query OK, 1000000 rows affected (1.59 sec)
 44 Records: 1000000  Duplicates: 0  Warnings: 0
 45 
 46 -rw-rw---- 1 mysql mysql 11326464 May 26 10:54 same_id.MYI
 47 -rw-rw---- 1 mysql mysql 11326464 May 26 10:54 uniq_id.MYI
 48 
 49 mysql> alter table uniq_id engine=innodb;
 50 Query OK, 1000000 rows affected (8.89 sec)
 51 Records: 1000000  Duplicates: 0  Warnings: 0
 52 
 53 mysql> alter table same_id engine=innodb;
 54 Query OK, 1000000 rows affected (8.48 sec)
 55 Records: 1000000  Duplicates: 0  Warnings: 0
 56 
 57 -rw-rw---- 1 mysql mysql 58720256 May 26 10:56 same_id.ibd
 58 -rw-rw---- 1 mysql mysql 58720256 May 26 10:56 uniq_id.ibd
 59 
 60 mysql> show create table uniq_id2\G
 61 *************************** 1. row ***************************
 62        Table: uniq_id2
 63 Create Table: CREATE TABLE `uniq_id2` (
 64   `id` int(11) NOT NULL,
 65   `v` int(11) DEFAULT NULL,
 66   PRIMARY KEY (`id`)
 67 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 68 1 row in set (0.00 sec)
 69 
 70 mysql> show create table same_id2\G
 71 *************************** 1. row ***************************
 72        Table: same_id2
 73 Create Table: CREATE TABLE `same_id2` (
 74   `id` int(11) NOT NULL,
 75   `v` int(11) DEFAULT NULL,
 76   PRIMARY KEY (`id`)
 77 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 78 1 row in set (0.00 sec)
 79 
 80 
 81 -rw-rw---- 1 mysql mysql 10263552 May 26 11:00 same_id2.MYI
 82 -rw-rw---- 1 mysql mysql 10263552 May 26 11:01 uniq_id2.MYI
 83 
 84 mysql> alter table same_id2 add index(v);
 85 Query OK, 1000000 rows affected (9.06 sec)
 86 Records: 1000000  Duplicates: 0  Warnings: 0
 87 
 88 mysql> alter table uniq_id2 add index(v);
 89 Query OK, 1000000 rows affected (8.60 sec)
 90 Records: 1000000  Duplicates: 0  Warnings: 0
 91 
 92 -rw-rw---- 1 mysql mysql 21605376 May 26 11:02 same_id2.MYI
 93 -rw-rw---- 1 mysql mysql 21605376 May 26 11:02 uniq_id2.MYI
 94 
 95 mysql> alter table uniq_id2 engine=innodb;
 96 Query OK, 1000000 rows affected (7.66 sec)
 97 Records: 1000000  Duplicates: 0  Warnings: 0
 98 
 99 mysql> alter table same_id2 engine=innodb;
100 Query OK, 1000000 rows affected (8.19 sec)
101 Records: 1000000  Duplicates: 0  Warnings: 0
102 
103 -rw-rw---- 1 mysql mysql 54525952 May 26 11:05 same_id2.ibd
104 -rw-rw---- 1 mysql mysql 54525952 May 26 11:05 uniq_id2.ibd

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

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