本篇把 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 的测试结果:
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 status\G 查看 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 本身的一些的问题, 例如尽可能使用到索引, 缓存表的行数等.
来源: https://www.cnblogs.com/hunhunrensheng/p/3797309.html