| 3. 实验过程
--实验用表：mytable3， InnoDB类型。
表有三个列，52万行数据，后两个列用随机函数产生，重复行很少。都创建了索引。此时该表数据占用空间256MB，索引占用空间240MB，InnoDB表空间914MB。 mysql> desc mytable3;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(100) | YES | MUL | NULL | |
| name2 | char(120) | YES | MUL | NULL | |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select count(*) from mytable3;
+----------+
| count(*) |
+----------+
| 524288 |
+----------+
1 row in set (0.57 sec)
mysql> select table_name, table_schema, data_length, index_length from information_schema.tables where table_name = 'mytable3';
+------------+--------------+-------------+--------------+
| table_name | table_schema | data_length | index_length |
+------------+--------------+-------------+--------------+
| mytable3 | testbk | 269271040 | 252706816 |
+------------+--------------+-------------+--------------+
1 row in set (0.00 sec)
[mysql@testlocal data]$ ls -ltr
total 947188
drwxr-xr-x 2 mysql mysql 4096 Aug 19 09:53 test
drwx--x--x 2 mysql mysql 4096 Aug 19 09:53 mysql
-rw-rw---- 1 mysql mysql 125 Aug 19 10:43 mysql-bin.000001
-rw-rw---- 1 mysql mysql 125 Aug 19 10:43 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1086 Aug 19 17:18 mysql-bin.000003
-rw-rw---- 1 mysql mysql 693 Aug 19 20:51 mysql-bin.000004
-rw-rw---- 1 mysql mysql 125 Aug 19 20:54 mysql-bin.000005
-rw-rw---- 1 mysql mysql 125 Aug 19 20:58 mysql-bin.000006
-rw-rw---- 1 mysql mysql 6 Aug 19 20:59 testlocal.sohu.com.pid
srwxrwxrwx 1 mysql mysql 0 Aug 19 20:59 mysql.sock
-rw-rw---- 1 mysql mysql 133 Aug 19 20:59 mysql-bin.index
-rw-r----- 1 mysql mysql 12432 Aug 21 11:20 testlocal.sohu.com.err
drwx------ 2 mysql mysql 4096 Aug 21 11:22 testbk
-rw-rw---- 1 mysql mysql 5010 Aug 21 11:22 mysql-bin.000007
-rw-r----- 1 mysql mysql 5242880 Aug 21 11:22 ib_logfile1
-rw-r----- 1 mysql mysql 5242880 Aug 21 11:22 ib_logfile0
-rw-r----- 1 mysql mysql 958398464 Aug 21 11:22 ibdata1 |
--将该表改变类型为MyISAM
发现InnoDB表空间未回缩，但MyISAM占用磁盘容量减少为仅约8MB。此时该表数据占用空间222MB，索引占用空间8MB，InnoDB表空间914MB。 mysql> alter table mytable3 engine myisam;
Query OK, 524288 rows affected (8.51 sec)
Records: 524288 Duplicates: 0 Warnings: 0
mysql> select table_name, table_schema, data_length, index_length from information_schema.tables where table_name = 'mytable3';
+------------+--------------+-------------+--------------+
| table_name | table_schema | data_length | index_length |
+------------+--------------+-------------+--------------+
| mytable3 | testbk | 233308160 | 8528896 |
+------------+--------------+-------------+--------------+
1 row in set (0.00 sec)
[mysql@testlocal testbk]$ ls -ltr
total 236456
-rw-rw-r-- 1 mysql mysql 3307 Aug 19 12:30 bak_testbk
-rw-rw---- 1 mysql mysql 8556 Aug 19 19:35 mytable.frm
-rw-rw---- 1 mysql mysql 8556 Aug 19 20:43 mytable2.frm
-rw-rw---- 1 mysql mysql 8618 Aug 21 11:32 mytable3.frm
-rw-rw---- 1 mysql mysql 233308160 Aug 21 11:32 mytable3.MYD
-rw-rw---- 1 mysql mysql 8528896 Aug 21 11:32 mytable3.MYI |
|
