脚本宝典收集整理的这篇文章主要介绍了

MySQL存储引擎差异化实验

脚本宝典小编觉得挺不错的,现在分享给大家,也给大家做个参考,希望能帮助你少写一行代码,多一份安全和惬意。

本篇把MySQL最常用的存储引擎给大家做一个介绍,然后通过插入、修改和并发实验来了解和验证一下它们之间的一些差异。

一、MySQL存储引擎简介
存储引擎在MySQL结构里占据核心的位置,是上层抽象接口和存储的物理文件之间的桥梁。每一种storage engine 架构组件都是根据特定的场合来设计的,负责数据的 I/O 操作,并启用一些特性的支持。
请输入图片描述

MySQL存储引擎被设计为插件式结构,每种存储引擎可从运行的mysql里动态加载或卸载。我们可以在客户端连接后用show plugins;查看当前加载的插件,用install plugin xxx;或者 uninstall plugin xxx;来安装或卸载。

查看服务器当前支持的引擎命令:
mysql> show engines;

主要的几种引擎介绍如下:
InnoDB:支持事务操作,支持行级锁,支持外键。独立表结构的花每个表单文件存储,是MySQL5.5之后的默认引擎。
MyISAM:使用广泛,数据量不是特别大时性能很高,是5.5之前的默认引擎。
Memory:数据直接放在内存,极快的访问速度,但是空间很受限。
MRG_MYISAM:可以把MyISAM表分组管理。
Federated:可以把不同物理服务器连接成一个逻辑服务器,适合分布式管理。
CSV:导入导出成CSV格式,便于和其他软件数据交换。

我们可以配置php.ini文件或者在server启动时,可以通过--default-storage-engine参数来指定默认的存储引擎。也可以在mysql运行状态下动态改变默认引擎:
show variables like 'default_storage_engine';
SET default_storage_engine=MYISAM;

数据库的每个表可以使用不同的引擎:
create table t_a(uid int,uname varchar(50)) engine=innodb;
也可以动态修改表的引擎:
alter table t_a engine=MyISAM;

二、MySQL存储引擎的文件组成与备份
MySQL主要的动态文件有日志文件、配置文件和存储引擎的数据文件
1、日志文件
种类非常多,我们也可以在这些变量里找到innodb的特殊日志文件:
show variables like '%log%';
2、配置和连接文件
my.cnf是数据库的主要配置文件,如果我们做了主从配置,则还有master.info等配置信息文件。
linux下支持tcp和socket连接,可以通过配置my.cnf或者连接时增加参数来确定mysql --protocol=tcp,如果是socket方式则一般会通过socket文件来连接/tmp/mysql.sock。
3、数据文件
每一种存储引擎都有.frm 表元数据文件。然后每种引擎都有自己的一些特有特有格式的文件:
.myd (MyData)是MyISAM数据文件,.myi (MyIndex)是MyISAM索引文件(b-tree、full-text等)。
innodb的共享表空间存在ibdata文件里,如果配置成独享表空间的话(mysql默认)每个表还会有对应.ibb文件。我们可以通过变量查询和设置这些配置:
show variables like ‘%innodb%’; 其中innodb_file_per_table设置是否是独享表空间,innodb_data_file_path 和innodb_data_home_dir用来指定表的存放位置。

备份:
1、逻辑备份
逻辑备份是不停机的情况下比较好的备份方式,通过mysqldump或者其他方式来导出sql语句。
2、物理备份
物理备份在某些情况是更加直接和快速的方式。myisam引擎因为是非事务没有独立日志,一般备份3个文件即可,也可以通过mysqlhotcopy来进行物理备份。
innodb 因为事物需要有日志文件,如果在运行状态则不能手工来备份,需要一些商业化的工具比如ibbackup来支持物理备份。
3、主从物理备份
因为物理备份一般需要锁库,在线上数据库上我们如果设置了主从服务器并且有多台从库的话,可以暂停一台从库,然后实行物理备份。

三、插入和更新数据
我们先创建3个引擎的数据表user_myisam、user_innodb、user_memory,表的结构是一样的:

create table user_myisam (     uid int auto_increment,     uname varchar(50) not null default '',     type tinyint not null default 0,     ctime timestamp not null default current_timestamp,     primary key (uid) ) engine=myisam, charset=utf8; 

我们在生成数据时,可以使用一条条数据插入、导入sql文件、或者批量插入的方式进行。
导入sql文件是有大小限制的,我们可以通过max_allowed_packet变量来查看,一般默认为1M,所以导入大量数据时需要增大这个变量:
show variables like 'max_allowed_packet';
显然,数据量很大时,批量插入的方式是效率最高的:
insert into tbl values(),(),()...
经过对比,虽然memory引擎插入和查询修改的速度都极快,单只支持几万行数据,即使调大了内存参数也只能支持10多万行。所以memory一般用在一些数据量比较小的特殊场合,比如在线用户表、或者缓存一些配置信息等。
我们用批量插入的方式把myisam和innodb的表各插入了1千万行数据(每次插入1万行或更多),myisam的速度要稍快些,没有调优的情况下几分钟时间就可以了。

更新和查询的数据对比:
在一个进程操作的情况下,myisam的更新和查询速度都会稍快于innodb。
特别注意的一点是,innodb查询表的行数需要全表扫描,速度会非常慢,查询1千万行数据的表最多时要6、7s,所以在项目里一定要控制innodb表的总数查询,一定要缓存。而myisam因为保存了总行数是极快的。
请输入图片描述

请输入图片描述

四、innodb的事务支持和锁
innodb的事物支持4种隔离级别:
read uncommitted:脏读,在自己的事务里能看到别的事务修改但未提交的数据。
read committed:不可重复读,虽然别的事务未提交的数据看不到,但是提交后就可以了,所以不能多次读取,数据可能不一致。
repeatable read:可重复读,事务做了隔离,但还是可以并发的。
serializable:串行,最严格的方式,事务单行处理,不会并行。
查看当前和全局的事务隔离级别:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
可以通过以下命令来改变设置:
set global transaction isolation level read uncommitted;
我们可以通过2个session然后设置set autocommit=0来进行测试和验证这4种事务隔离级别的差别,在自己的项目里也可以根据情况来改变。越高的隔离级别对性能影响越大,innodb默认是repeatable read方式。

mysql有3种锁:
1、表级锁:myisam的默认形式,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。可以查看表锁的一些情况:
show status like 'table%';
2、行级锁:innodb的默认形式,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
需要注意的是,innodb只有在能利用索引的操作时才执行行级锁,如果查询或更新操作不能利用索引还是会使用表级锁的。查看行锁状态:
show status like 'innodb_row_lock%';
3、页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

五、并发测试与参数调优总结
虽然在上面单进程的情况下,myisam在插入查询和更新等操作中性能都比较高,但是在我们模拟高并发的情况下,可以看出innodb的销量明显占优了。
我们用apache的ab工具来测试3000次30个并发的请求,每个请求在1千万数据里随机找5行数据进行修改和查询(用到索引),测试结果如下:
myisam的测试数据:
请输入图片描述

innodb的测试结果:

MySQL存储引擎差异化实验-脚本宝典
myisam的一些参数优化:
read_buffer_size缓存大小
设置concurrent_insert为2,在尾部插入数据,不影响select
打开delay_key_write

innodb的一些参数:
设置事务提交后数据保存方式:
innodb_flush_log_at_trx_commit
0 每秒保存 1 每事务保存 2 系统决定

innodb_buffer_pool缓存大小:
show status like 'innodb_buffer_pool%';

可以用show engine innodb statusG查看innodb的一些情况:
innodb_read_io_threads读写进程数
innodb_write_io_threads
innodb_io_capacity合并写入数量
innodb_io_capacity=5000;
set global innodb_stats_on_metadata=0;关闭元数据更新

经过我们的一些操作对比,可以看出:
Memory虽然是高效的引擎,但是由于是临时数据而且有数据量的限制,适合与性能要求高数据量小的地方,和缓存的效果类似。
MyISAM适合数据量不是特别大并发不太高的大部分场合,性能都占优,并且也支持全文检索。如果不需要事务支持的话MyISAM绝对是最优的方式。
而InnoDB 则更适合与大并发大数据量的场合,除了支持事务,在高并发时行级锁的优势就会发挥出来。当然我们需要在代码和设计里去规避innodb本身的一些的问题,例如尽可能使用到索引,缓存表的行数等。

总结

以上是脚本宝典为你收集整理的

MySQL存储引擎差异化实验

全部内容,希望文章能够帮你解决

MySQL存储引擎差异化实验

所遇到的程序开发问题,欢迎加入QQ群277859234一起讨论学习。如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典网站推荐给程序员好友。 本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。

80%的人都看过