MySQL存储引擎MyISAM 和 InnoDB

最新推荐文章于 2022-07-17 17:00:40 发布

玉树临风风流倜傥一树梨花压海棠

最新推荐文章于 2022-07-17 17:00:40 发布

阅读量209

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/Dadit/article/details/108133887

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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

存储引擎介绍

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的主要区别

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