文章目录

  • 前言
  • 一、MySQL存储引擎
  • 1.MyISAM的特点
  • 2.MyISAM适用的生产场景举例
  • 二、InnoDB 存储引擎
  • 1.InnoDB的特点
  • 2.InnoDB 适用的生产场景
  • 3.企业选择存储引擎的依据
  • 三、配置合适的存储引擎
  • 1.查看数据库可配置的存储引擎类型
  • 2.查看表正在使用的存储引擎类型
  • 3.配置存储引擎为所选择的类型
  • 总结


前言

存储引擎的实质就是如何实现存储数据，为存储数据建立索引以及查询、更改、删除数据等技术实现的方法，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适用的生产场景举例

  1. 场景一：
  • 公司业务不需要事务的支持，这点是非常明确的，因为 MyISAM 没有事务
  • 一般单方面读取或写入数据比较多的业务，因为 MyISAM 具有读写互相阻塞的特点，数据读写都比较频繁的场景不适合使用
  • 使用读写并发访问相对较低的业务
  • 数据修改相对较少的业务
  1. 场景二：
  • 公司的业务不需要事务的支持
  • 一般单方面读取数据比较多的业务，或单方面写入数据比较多的业务，以及读写并发访问相对较低的业务
  • 以读为主的业务，如 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支持事务