MySQL存储引擎MyISAM 和 InnoDB

存储引擎的实质就是如何实现存储数据,为存储的数据建立索引以及查询、更改、删除数据等技术实现的方式。

存储引擎介绍

  • MySQL中的数据用各种不同的技术存储在文件中,每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力,这些不同的技术以及配套的功能在MySQL中称为存储引擎
  • 存储引擎就是MySQL将数据存储在文件系统中的存储方式或者存储格式
  • 目前MySQL常用的两种存储引擎
    • MyISAM
    • InnoDB
  • MySQL存储引擎是MySQL数据库服务器中的组件,负责为数据库执行实际的数据I/O操作
  • 使用特殊存储引擎的主要优点之一在于,仅需提供特殊应用所需的特性,数据库中的系统开销较小,具有更有效和更高的数据库性能
  • MySQL系统中,存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储
    在这里插入图片描述

MyISAM存储引擎

  • MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎,前身是ISAM
  • ISAM是一个定义明确且历经时间考验的数据表格管理方法,在设计之时就考虑到数据库被查询的次数要远大于更新的次数

MyISAM的特点

  • 不支持事务
  • 表级锁定形式,数据在更新时锁定整个表
  • 数据库在读写过过程中相互阻塞:
    • 在数据写入过程中阻塞用户对数据的读取
    • 在数据读取过程中阻塞用户写入数据
  • 通过key_buffer_size 来设置缓存索引,但不能缓存数据
  • 单独进行写入或读取,速度较快,占用资源相对较少
  • 不支持外键约束、只支持全文索引
  • 引擎文件在磁盘上存储成三个文件
    • 表定义:.frm
    • 数据文件 :.MYD
    • 索引文件:.MYI

MyISAM适用的生产环境

  • 公司业务不需要事务的支持
  • 一般单方面读取数据比较多的业务,或单方面写入数据比较多的业务
  • MyISAM存储引擎数据读写都比较频繁场景不适合
  • 使用读写并发访问相对较低的业务
  • 数据修改相对较少的业务
  • 对数据业务一致性要求不是非常高的业务
  • 服务器硬件资源相对比较差

InnoDB

特点介绍

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

InnoDB适用生产场景分析

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

企业选择存储引擎依据

  • 需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
  • 支持的字段和数据类型
    • 所有引擎都支持通用的数据类型
    • 但不是所有的引擎都支持其它的字段类型,如二进制对象
  • 锁定类型:不同的存储引擎支持不同级别的锁定
    • 表锁定
    • 行锁定
  • 索引的支持
    • 建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能
    • 不同的存储引擎提供不同的制作索引的技术
    • 有些存储引擎根本不支持索引
  • 事务处理的支持
    • 事务处理功能通过提供在向表中更新和插入信息期间的可靠性
    • 可根据企业业务是否要支持事务选择存储引擎

配置存储引擎

在企业中选择好合适的存储引擎之后,就可以进行修改了
修改步骤:

  • 查看数据库可配置的存储引擎
  • 查看表正在使用的存储引擎
  • 配置存储引擎为所选择的类型

查看系统支持的存储引擎

在这里插入图片描述

查看表使用的存储引擎

  • 方法1:

show table status from 库名 where name=‘表名';
在这里插入图片描述

  • 方法2:

show create table 表名;在这里插入图片描述

修改存储引擎

  • 方法1: alter table 修改;
MySQL [auth]> alter table  user_info engine=MyISAM;
  • 方法2: 修改my.cnf,指定默认存储引擎并重启服务

在这里插入图片描述

方法3:create table创建表时指定存储引擎

在这里插入图片描述
在这里插入图片描述

  • 方法4: Mysql_convert_table_format 转化存储引擎
[root@Mysql1 /]# yum install perl-DBI perl-DBD-MySQL –y 
[root@Mysql1 /]# /usr/local/mysql/bin/mysql_convert_table_format\
   --user=root --password='123' --sock=/tmp/mysql.sock auth

MyISAM与INnoDB的主要区别

  1. InnoDB 支持事务,MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
  2. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
  3. InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  4. InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
  5. InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值