MySQL 数据库--存储引擎(MyISAM 与 InnoDB)

x33g5p2x  于9个月前 转载在 Mysql  
字(2.8k)|赞(0)|评价(0)|浏览(70)

前言

存储引擎的实质就是如何实现存储数据,为存储数据建立索引以及查询、更改、删除数据等技术实现的方法,MYSQL数据库支持多种不同的数据引擎,可以根据具体的要求,针对每一个表使用不同的数据引擎,摒弃了很多其他数据库采取的“一个尺码满足一切需求”的解决方案,正是这种灵活性,使得 MySQL 数据库更加受欢迎

一、MySQL存储引擎

MySQL数据库中的组件,负责执行实际的数据I/O操作
*
MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储
*
最为常用的存储引擎是 MyISAM 和 InnoDB
表——>存储引擎——>数据

1.MyISAM的特点

  • 不支持事务,也不支持外键约束,支持全文索引数据文件和索引文件时分开保存的

  • 访问速度快,对事务完整性没有要求

  • 适合查询(select)、插入(insert into)为主的应用

  • MyISAM在磁盘上存储三个文件,文件名和表名都相同,但是扩展名分别为:

  • .frm文件存储表结构的定义

  • 数据文件的扩展名.MYD(MYData)

  • 索引文件的扩展名时.MYI(MYIndex)

  • 表级锁定形式,数据在更新时锁定整个表

  • 数据库在读写的过程中相互阻塞

  • 会在数据写入的过程中阻塞用户数据的读取

  • 也会在数据读取的过程中阻塞用户的数据写入

  • 数据单独写入或读取,速度过程较快且占用资源相对少

  • MyISAM支持的存储格式

  • 静态(固定长度)表(默认的存储格式,静态表中的字段都是非可变字段,这中存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复,缺点是占用的空间通常比动态多)——char的数据类型

  • 动态表(包含可变字段,记录不是固定长度的,这样存储优点是占用空间少,但是频繁的更新、删除记录会产生碎片,需要定期 OPTIMIZETABLE 语句或 myisamchk -r 命令来改善性能,并且出现故障的时候恢复相对比较困难)——varchar的数据类型

  • 压缩包(由myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支)

2.MyISAM适用的生产场景举例

场景一:

  • 公司业务不需要事务的支持,这点是非常明确的,因为 MyISAM 没有事务
  • 一般单方面读取或写入数据比较多的业务,因为 MyISAM 具有读写互相阻塞的特点,数据读写都比较频繁的场景不适合使用
  • 使用读写并发访问相对较低的业务
  • 数据修改相对较少的业务

场景二:

  • 公司的业务不需要事务的支持
  • 一般单方面读取数据比较多的业务,或单方面写入数据比较多的业务,以及读写并发访问相对较低的业务
  • 以读为主的业务,如 www、blog 图片信息库、用户数据库、商品库等业务
  • 对业务一致性要求不是非常高的业务
  • 服务器硬件资源相对比较差

二、InnoDB 存储引擎

1.InnoDB的特点

  • 支持事务,支持4个事务隔离级别
  • 支持外键约束,MySQL 5.5 以前不支持全文索引,5.5 版本以后支持
  • 读写阻塞与事务隔离级别相关
  • 能非常高效的缓存索引和数据
  • 表的主键以簇的方式存储
  • 支持分区、表空间,类似oracle数据库
  • 对硬件资源要求还是比较高的场合
  • 行级锁定,但是全表扫描仍然会是表级锁定

2.InnoDB 适用的生产场景

业务需要事务的支持
*
行级锁定对高并发有很好的适应能力,但需要确保查询时通过索引来完成
*
业务数据更新较为频繁的场景,如论坛、微博等
*
业务数据一致性要求较高,例如银行业务
*
硬件设备内存较大,利用 InnoDB 较好的缓存能力来提高内存利用率,减少磁盘 IO 的压力

3.企业选择存储引擎的依据

选择哪种存储引擎,应根据企业系统的实际需要,考虑每个存储引擎提供了哪些不同的核心功能及应用的常见
主要依据以下几个方面来决定:

①存储引擎支持的字段和数据类型

  • 所有的引擎都支持通用的数据类型,但不是所有的引擎都支持其他的字段类型
  • 如二进制对象

②锁定类型

  • 不同的存储引擎支持不同级别的锁定
  • 表锁定:MyISAM 支持
  • 行锁定:InnoDB 支持

③索引的支持

  • 建立索引在搜索和恢复数据库中的数据时能够显著提高性能
  • 不同的存储引擎提供不同的制作索引的技术
  • 有些存储引擎根本不支持索引

④事务处理的支持

  • 事务处理功能提供向表中更新和插入信息期间的可靠性
  • 可根据企业业务是否要支持事务选择存储引擎

三、配置合适的存储引擎

1.查看数据库可配置的存储引擎类型

show engines;
参数解释
Engine当前版本的 MySQL 所支持的引擎类型
Support对应引擎是否能使用,DEFAULT 指默认值
Comment存储引擎的简要说明
Transactions对应引擎是否支持事务
XA存储引擎是否支持XA事务
Savepoints存储引擎是否支持保存点

2.查看表正在使用的存储引擎类型

方法一

show table status from 库名 where name = '表名'\G;
#加\G能竖向查看,不加则是横向

例:
show table status from school where name='class'\G;

方法二

use 库名;
show create table 表名;

例:
use school;
show create table class\G;

3.配置存储引擎为所选择的类型

方法一: 通过使用 alter table 命令修改

use 库名;
alter table 表名 engine=MyISAM;

例:
use school;
alter table class engine=myisam;
show create table class;

方法二: 通过修改 MySQL 的配置文件 my.cnf,可以指定 default-storage-engine 选项设置默认的存储引擎

quit

vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB

systemctl restart mysql.service
#一定记得重启服务以让修改的配置生效

#注:此方法只针对新建的表,对以前已存在的表没有影响,即不会修改之前表的存储引擎类别

方法三: 通过使用 create table 创建表时用 engine 指定存储引擎,会使用指定的存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

例:
mysql -uroot -p123123

use school;
create table fanren (name varchar(10),age char(4))engine=myisam;

show create table fanren;

总结

InnoDB 中不保存表的行数,需要扫描一遍整个表来计算有多少行,但是 MyISAM 只要简单的读出保存好的行数即可
*
需要注意的是,当"count( )"语句包含 where 条件时,MyISAM 也需要扫描整个表
*
对于自增长的字段,InnoDB 中必须包含只有该字段的索引,但是在 MyISAM 表中可以和其他字段一起建立组合索引
*
清空整个表时,InnoDB 是一行一行的删除,效率非常慢;而MyISAM 则会重建表
*
MyISAM不支持事务,InnoDB支持事务

相关文章