MySQL数据库存储引擎探析
摘要：介绍了mysql数据库存储引擎及其分类，并就最常用的myisam和innodb两种存储引擎展开研究分析，通过性能测试探究其使用特点，为用户选择合适的数据存储方式提供参考依据。
关键词：存储引擎；myisam；innodb
中图分类号：tp391文献标识码：a文章编号：1672-7800（2012）012-0129-03
mysql数据库以其简单高效可靠的特点，在最近几年的时间内从一个不出名的小型数据库系统，变成一个可广泛应用在嵌入式系统、web网站以及企业级系统的开源数据库管理系统，其成绩是众所周知的。究其原因后不难发现，其一是开源，优点是可获得较快的用户使用速度，开发方可获得较低的管理运营成本，可突破应用平台的局限；其二是操作数据库的实现机制，mysql数据库主要体现在支持插件式存储引擎，并且数据查询及事务处理的单项执行效率均优于大型数据库系统。本文就存储引擎的特点及分类进行比较分析，为用户选择合适的数据库数据表示方式提供参考。
1存储引擎及其类别分析
1.1存储引擎
存储引擎是存储数据、为存储的数据建立索引以及更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储，所以存储引擎也可以称为表类型（即存储和操作表的类型）。
在oracle 和sql server等数据库中只有一种存储引擎，所有数据存储管理机制都一样。而mysql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据具体的需求编写自定义存储引擎。
1.2分类
mysql数据库提供了多种存储引擎（在phpmyadmin界面中单击“引擎”选项，就会显示当前数据库支持的存储引擎），如表1所示。
其中使用最广泛的是myisam和innodb两种存储引擎。myisam是mysql早期的isam存储引擎
的升级版本，也是mysql默认的存储引擎，而innodb是由第三方软件公司innobase所开发，其最大的特点是提供事务控制的特性，所以使用者也很广泛。
其它存储引擎相对来说使用机会少一些，都是应用于某些特定的场景：ndbcluster虽然也支持事务处理，但主要用于分布式环境，属于一种sharenothing体系的分布式数据库存储引擎；maria是mysql最新开发的对myisam的升级版存储引擎；falcon是mysql公司自行研发的一款带有事务等高级特性的数据库存储引擎，目前正在研发阶段；memory存储引擎所有数据和索引均存储于内存中，并使用散列索引，所以数据存取速度非常快，因此主要用于临时表，或者对性能要求较高的场景；archive是一个数据经过高比例压缩存放的存储引擎，主要用于日志记录和聚合分析，不支持索引；merge和federated在严格意义上来说，并不能算作一个存储引擎。因为merge存储引擎主要用于将几个基表连接到一起，对外作为一个表来提供服务，基表可以基于其它的几个存储引擎；而federated主要用于远程存取其它mysql服务器上的数据。
1.3myisam存储引擎
myisam存储引擎是mysql最早提出并使用的存储引擎，其优点是对表数据的存取、查询、
更新效率高。该存储引擎根据应用数据的特点不同分为静态myisam、动态myisam 和压缩myisam 3种：
静态myisam，数据特点是：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复也比较容易。
动态myisam，数据特点是：如果数据表中出现varchar、text或blob字段时，服务器将自动选择这种表类型。相对于静态myisam，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散地存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
压缩myisam，数据特点是：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的空间，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先实行解压缩。因此执行效率较低。
不管是何种myisam表，目前它都不支持事务、行级锁和外键约束的功能。
1.4innodb存储引擎
相比myisam，innodb具有支持事务、行级锁和外键约束等功能。
（1）支持事务安全。innodb存储引擎最重要的一点就是对事务安全的支持，这也是让它成为最流行的存储引擎很重要的原因，而且实现了sql92标准所定义的4个级别 （read uncommitted，read committed，repeatable read，serializable）。
（2）数据库多版本读取。innodb在事务支持的同时，为了保证数据的一致性以及并发时刻的性能，通过对undo信息的聚簇索引实现对数据的多版本读取。
（3）锁定机制的改进。innodb改变了myisam的锁机制，实现了行锁。虽然innodb的行锁机制是通过索引来完成的，但是由于数据库中99%的sql语句都是通过索引来检索数据，所以行锁机制为innodb在承受高并发下的环境下增强了竞争力。
（4）实现外键。innodb实现了外键引用这一数据库的重要特性，使得在数据库端控制部分数据的完整性成为可能。
2myisam和innodb两种存储引擎性能测试
（1）软件环境。windows xp sp2，php5.2.1，mysql5.0.37，iis6。
（2）mysql表结构。分别创建两个表，使用不同的存储引擎。
create table `myisam` （
`id` int（11） not null auto_increment，
`name` varchar（100） default null，
`content` text，
primary key （`id`）
） engine=myisam default charset=gbk；
create table `innodb` （
`id` int（11） not null auto_increment，
`name` varchar（100） default null，
`content` text，
primary key（`id`）
） engine=innodb default charset=gbk；
（3） 插入数据1：
（innodb_flush_log_at_trx_commit=1）
myisam 1w：3/s innodb 1w：219/s
myisam 10w：29/s innodb 10w：2092/s
myisam 100w：287/s innodb 100w：没测试
（4） 插入数据2：
（innodb_flush_log_at_trx_commit=0）
myisam 1w：3/s  innodb 1w：3/s
myisam 10w：30/s  innodb 10w：29/s
myisam 100w：273/s  innodb 100w：423/s
（5） 插入数据3：
（innodb_buffer_pool_size=1024m）
myisam 1w：3/s innodb 1w：3/s
myisam 1w：31/s innodb 10w：33/s
myisam 1w：586/s innodb 100w：607/s
（6） 插入数据4：
（innodb_buffer_pool_size=256m， innodb_flush_log_at_trx_commit=1， set autocommit=0）
myisam 1w：3/s  innodb 1w：3/s
myisam 1w：25/s  innodb 10w：26/s
myisam 1w：304/s  innodb 100w：379/s
（7）  mysql 配置文件 （缺省配置）：
# mysql server instance configuration file
[client]
port=3306[mysql]
default-character-set=gbk[mysqld]
port=3306
basedir=“c：/mysql50/”
datadir=“c：/mysql50/data/”
default-character-set=gbk
default-storage-engine=innodb
sql-mode=“strict_trans_tables，no_auto_create_user，no_engine_substitution”
max_connections=100query_cache_size=0
table_cache=256
tmp_table_size=50m
thread_cache_size=8
myisam_max_sort_file_size=100g
myisam_max_extra_sort_file_size=100g
myisam_sort_buffer_size=100m
key_buffer_size=82m
read_buffer_size=64k
read_rnd_buffer_size=256k
sort_buffer_size=256kinnodb_additional_mem_pool_size=4m
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2m
innodb_buffer_pool_size=159m
innodb_log_file_size=80m
innodb_thread_concurrency=8
3结语
通过上述测试结果，可以看出在mysql 5.0里面，myisam和innodb存储引擎性能差别不是很大。针对innodb来说，影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为1，那么每次插入数据时都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0，能够看到效率明显提升。php远程连接mysql数据库

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。