测试

 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-696352/，如需转载，请注明出处，否则将追究法律责任。

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