登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

关于InnoDB和MyISAM数据存储特性的研究  

2008-08-29 09:41:21|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

InnoDB和MyISAM是MySQL最重要的两种数据存储引擎,两者都可用来存储表和索引,InnoDB的索引和表存储在同一个文件中,MyISAM的索引和表存储在不同文件,但即使多个索引,也共存于一个文件。

  网络上很多比较都是基于两者的事务性处理和锁处理机制等,似乎InnoDB比MyISAM没有缺点了。但研究发现,MyISAM在数据特别是索引存储方面具有突出的优势,下面就此开展讨论。

  1. 研究结论

  1)InnoDB占用磁盘空间比MyISAM大,MyISAM存储数据可节省空间12%,存储索引可节省95%;

  2)InnoDB对空闲存储空间的使用不优。

  研究发现,MyISAM可大量节省磁盘空间,特别是对索引的存储上,优势巨大,这对大型MySQL数据库的数据表和索引的物理设计,具有较大的指导意义。

  2. 研究对象及获得的数据

    MySQL版本:5.1.26-rc,平台:RHEL 4,Linux 2.6.9-67.ELsmp x86_64

  研究对象为创建的一个表,mytable3,初始为InnoDB类型。有54万行非重复数据(用随机函数产生),两个索引。共进行两个实验,获得的实验数据如下。

 

表占用_MB

索引占用_MB

InnoDB文件占用_MB

MyISAM文件占用_MB

初始类型InnoDB

256

240

914

0

类型改为MyISAM

222

8

914

230

类型改回InnoDB

256

240

1034

0

 

    从实验数据可以看出,表类型alter为MyISAM后,所占磁盘空间仅8MB,为InnoDB的4%。而且随着表类型改回InnoDB,InnoDB表空间被迫扩充120MB,达到1034MB,以支持该表数据的回迁。

 

     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

  --重新改回InnoDB

  发现InnoDB表空间又扩充了。此时该表数据占用空间256MB,索引占用空间240MB,InnoDB表空间1034MB

mysql> alter table mytable3 engine innodb;
        Query OK, 524288 rows affected (17.41 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       |   269271040 |    252706816 | 
         +------------+--------------+-------------+--------------+
         1 row in set (0.00 sec)
 
[mysql@testlocal data]$ ls -ltr
total 1070188
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:48 testbk
-rw-rw----  1 mysql mysql       5208 Aug 21 11:48 mysql-bin.000007
-rw-r-----  1 mysql mysql    5242880 Aug 21 11:48 ib_logfile1
-rw-r-----  1 mysql mysql    5242880 Aug 21 11:48 ib_logfile0
-rw-r-----  1 mysql mysql 1084227584 Aug 21 11:48 ibdata1 
  

  评论这张
 
阅读(401)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018