InnoDB和MyISAM都是聚集索引么？

 1 以前从一些资料上看到InnoDB的索引是稀疏索引，而MyISAM的索引是密集索引，今天刻意测试了一下，发现竟然不是这样。
 2 
 3 找时间研究下，mark一下先。
 4 
 5 mysql> show create table uniq_id\G
 6 *************************** 1. row ***************************
 7        Table: uniq_id
 8 Create Table: CREATE TABLE `uniq_id` (
 9   `id` int(11) DEFAULT NULL,
10   KEY `id` (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
12 1 row in set (0.00 sec)
13 
14 mysql> show create table same_id\G
15 *************************** 1. row ***************************
16        Table: same_id
17 Create Table: CREATE TABLE `same_id` (
18   `id` int(11) DEFAULT NULL,
19   KEY `id` (`id`)
20 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
21 1 row in set (0.00 sec)
22 
23 mysql> select count(*),count(distinct id) from same_id;
24 +----------+--------------------+
25 | count(*) | count(distinct id) |
26 +----------+--------------------+
27 |  1000000 |                  1 |
28 +----------+--------------------+
29 1 row in set (0.21 sec)
30 
31 mysql> alter table uniq_id add index(id);
32 Query OK, 1000000 rows affected (1.27 sec)
33 Records: 1000000  Duplicates: 0  Warnings: 0
34 
35 mysql> alter table same_id add index(id);
36 Query OK, 1000000 rows affected (1.59 sec)
37 Records: 1000000  Duplicates: 0  Warnings: 0
38 
39 -rw-rw---- 1 mysql mysql 11326464 May 26 10:54 same_id.MYI
40 -rw-rw---- 1 mysql mysql 11326464 May 26 10:54 uniq_id.MYI
41 
42 mysql> alter table uniq_id engine=innodb;
43 Query OK, 1000000 rows affected (8.89 sec)
44 Records: 1000000  Duplicates: 0  Warnings: 0
45 
46 mysql> alter table same_id engine=innodb;
47 Query OK, 1000000 rows affected (8.48 sec)
48 Records: 1000000  Duplicates: 0  Warnings: 0
49 
50 -rw-rw---- 1 mysql mysql 58720256 May 26 10:56 same_id.ibd
51 -rw-rw---- 1 mysql mysql 58720256 May 26 10:56 uniq_id.ibd
52 
53 mysql> show create table uniq_id2\G
54 *************************** 1. row ***************************
55        Table: uniq_id2
56 Create Table: CREATE TABLE `uniq_id2` (
57   `id` int(11) NOT NULL,
58   `v` int(11) DEFAULT NULL,
59   PRIMARY KEY (`id`)
60 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
61 1 row in set (0.00 sec)
62 
63 mysql> show create table same_id2\G
64 *************************** 1. row ***************************
65        Table: same_id2
66 Create Table: CREATE TABLE `same_id2` (
67   `id` int(11) NOT NULL,
68   `v` int(11) DEFAULT NULL,
69   PRIMARY KEY (`id`)
70 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
71 1 row in set (0.00 sec)
72 
73 
74 -rw-rw---- 1 mysql mysql 10263552 May 26 11:00 same_id2.MYI
75 -rw-rw---- 1 mysql mysql 10263552 May 26 11:01 uniq_id2.MYI
76 
77 mysql> alter table same_id2 add index(v);
78 Query OK, 1000000 rows affected (9.06 sec)
79 Records: 1000000  Duplicates: 0  Warnings: 0
80 
81 mysql> alter table uniq_id2 add index(v);
82 Query OK, 1000000 rows affected (8.60 sec)
83 Records: 1000000  Duplicates: 0  Warnings: 0
84 
85 -rw-rw---- 1 mysql mysql 21605376 May 26 11:02 same_id2.MYI
86 -rw-rw---- 1 mysql mysql 21605376 May 26 11:02 uniq_id2.MYI
87 
88 mysql> alter table uniq_id2 engine=innodb;
89 Query OK, 1000000 rows affected (7.66 sec)
90 Records: 1000000  Duplicates: 0  Warnings: 0
91 
92 mysql> alter table same_id2 engine=innodb;
93 Query OK, 1000000 rows affected (8.19 sec)
94 Records: 1000000  Duplicates: 0  Warnings: 0
95 
96 -rw-rw---- 1 mysql mysql 54525952 May 26 11:05 same_id2.ibd
97 -rw-rw---- 1 mysql mysql 54525952 May 26 11:05 uniq_id2.ibd

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

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