MySQL 性能调优——数据库存储引擎的选择

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/smartbetter/article/details/91492332

数据库存储引擎的选择会对性能产生直接的影响。MySQL体系结构如下:

MySQL体系结构

存储引擎是针对于表的而不是针对于库的(一个库中的不同表可以使用不同的存储引擎)。MyISAM 是 MySQL 5.5 之前版本默认的存储引擎。MySQL 5.5 及之后版本默认存储引擎改为了 InnoDB。

MyISAM 和 InnoDB 是最常用的两种存储引擎,除了这两种,还有一些具备特殊功能的存储引擎:CSV、Archive、Memory、Federated。简单对比如下:

存储引擎 事务 锁粒度 索引和数据存储 主要应用 忌用
MyISAM 不支持 支持并发插入的表级锁 会把索引缓存在内存中,而数据通过操作系统来进行缓存 select, insert 读写操作频繁
MRG_MyISAM 不支持 支持并发插入的表级锁 分段归档,数据仓库 全局查找过多的场景
InnoDB 支持,事务 ACID 特性 支持MVCC的行级锁 会同时在内存中缓存索引和数据,从而提高数据库的运行效率 事务处理
Archive 不支持 行级锁 日志记录,只支持insert, select 需要随机读取,更新,删除

1.MyISAM

MySQL 大部分系统表和临时表(在排序、分组等操作中,当数量超过一定大小后,由查询优化器建立的临时表)还是使用的 MyISAM。MyISAM 存储引擎表由 MYD 和 MYI 组成。

特性:

1、并发性与锁级别:非事务性存储引擎,不支持事务,表级锁。

在读取数据时,MyISAM 会对整个表加共享锁,但是共享锁之间是不会阻塞的。共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。

MyISAM 表在执行查询操作的时候,其他进程对该表的读操作没有影响,但是写操作会发生阻塞,直到第一个获得读锁的进程操作完成释放完读锁后,写操作才能进行。

2、表损坏修复:

check table tablename         //对表进行检查
repair table tablename         //对表进行恢复

3、MyISAM 表支持全文索引和空间函数。

4、MyISAM 表支持数据压缩,myisampack 命令。

限制:

1、版本 < MySQL 5.0 时默认单表最大文件为 4GB,如需存储大表则要修改 MAX_Rows 和 AVG_ROW_LENGTH;版本 >= MySQL 5.0 时默认单表最大文件为 256TB。

适用场景:

1、非事务型应用;
2、只读类应用(支持数据压缩,共享锁之间是不会阻塞);
3、空间类应用(GPS 数据等)。

2.InnoDB

InnoDB 有自己表空间的概念,InnoDB 使用表空间进行数据存储。具体存在什么样的表空间,则由 innodb_file_per_table 这个参数来决定,如果这个参数为 ON,则会为每个 InnoDB 表建立一独立的表空间(tablename.ibd),为 OFF 时则会把数据存储到系统的共享表空间(ibdataX,X代表的是一个从1开始的数字)。

在 MySQL 5.6 之前版本 innodb_file_per_table 默认 OFF,MySQL 5.6 及之后版本默认 ON。

系统表空间和独立表空间要如何选择?

1、首先比较一下系统文件对表空间的管理方式:

在一个繁忙的系统中,系统表空间会不断增长,一旦磁盘空间出现不足,我们为了释放磁盘空间,不得不在系统中删除大量的无效数据(长期不使用的、日志类的数据),在删除数据后,系统表空间并不会缩小,在这种情况下,我们想通过复制文件的方式对数据库进行备份,由于虽然删除了数据,但是文件大小并不会改变,这就意味着我们每次删除时都要浪费很大的空间。

而想要收缩系统表空间的唯一方法就是把整个数据库中的所有 InnoDB 表导出后,然后删除 InnoDB 相关的表空间文件,再重启 MySQL 进行表空间的重建,然后再导入数据,这个过程十分复杂,而且十分耗时。这在一个业务繁忙的生产环境中显然是不可能做到的。

所以我们使用系统表空间进行数据存储,所面临的问题就是无法简单的收缩文件大小,造成大量的空间浪费,并且会产生大量的磁盘碎片,降低系统性能。

而使用独立表空间这个问题就很好解决了,当对一个大表中的数据进行清理之后,可以很方便的使用 optimize table 命令对这一个表进行收缩系统文件操作,虽然这种方式也会对一个表进行重建,但是对比整个系统进行重建要快的多,而且不需要重启数据库服务器,甚至不会影响正常访问。

2、比较一下这两种表空间对IO方面有哪些影响:

对于系统表空间,由于只有一个文件,如果同时对多个表进行数据刷新时,实际上在文件系统层面上是顺序进行的,会产生I/O瓶颈。

独立表空间每一个表都有自己的表空间文件,可以同时向多个文件刷新数据。

到此,可以发现,使用独立表空间要比使用系统表空间好的多,建议对 InnoDB 使用独立表空间进行数据存储。

特性:

1、并发性与锁级别:事务性存储引擎,完美支持行级锁(行级锁比表级锁支持更大的并发,吞吐量也会越高),事务 ACID 特性。

为了实现事务的原子性、一致性、持久性,InnoDB 引用了两个特殊的日志类型 Redo Log(重做日志)和 Undo Log(回滚日志)。

行级锁可以最大程度的支持并发;

行级锁是在存储引擎层实现的。

2、InnoDB 状态检查

show engine innodb status

3、MySQL 5.7 及之后版本,InnoDB 表开始支持全文索引和空间函数。

适用场景:

1、InnoDB 适用于大多数 OLTP 应用。

附一:把原来存储在系统表空间中的表转移到独立表空间中的方法:

  1. 使用 mysqldump 导出所有数据库表数据;
  2. 停止 MySQL 服务,修改参数,并删除 InnoDB 相关文件;
  3. 重启 MySQL 服务,重建 InnoDB 系统表空间;
  4. 重新导入数据。

附二:锁

锁是数据库系统区别于文件系统的一个重要特性,锁的主要作用是管理共享资源的并发访问。

锁用于实现事务的隔离性。对于未提交的事务,锁定的数据是无法被其他事务所查询到的,

锁的类型 说明 索引和数据存储
共享锁(也称为读锁) 读锁是共享的,相互之间是不会被阻塞的,多个线程可以在同一时间读取同一资源
独占锁(也称为写锁) 写锁是排他的,一个写锁会阻塞其他的读锁和写锁,这是出于数据完整性的考虑,保证了在给定的时间里,只有一个线程能执行写入并防止其他用户读取正在写入的资源

看下兼容性:

- 写锁 读锁
写锁 不兼容 不兼容
读锁 不兼容 兼容

3.CSV

CSV 存储引擎可以将 CSV 文件作为 MySQL 的表来处理,存储格式就是普通的 CSV 文件,CSV 文件如果存储在 MyISAM 或 InnoDB 中, 数据文件是不能直接查看的,因为 MyISAM 和 InnoDB 存储格式都是以二进制格式进行存储的。

当我们在 MySQL 中建立了一个 CSV 存储引擎的表时,我们可以看到三个文件系统中的文件,这三个文件是以表名为文件名的,表数据存储在 CSV 为后缀的文件中,表的元数据(如表状态、数据量 )存储在 .CSM 为后缀的文件中,表结构信息存储在 .frm 为后缀的文件中。

特点:

1、以 CSV 格式进行数据存储,CSV 格式中的每一列都是以逗号分割的,文本数据是以双引号引起来的;
2、在建表时所有列的定义必须非 NULL 的;
3、不支持索引,索引的主要作用就是优化查询效率,CSV 不支持索引,那么每次查询都会进行全表扫描,不适合大表,不适合在线处理;
4、可以对数据文件直接编辑(其他存储引擎都是以二进制格式来存储的,不可编辑);

适用场景:

1、适合做为数据交换的中间表(可以将 Excel 文件存储为 CSV 文件,然后复制到 MySQL 数据目录下,就能在 MySQL 中打开使用了);

4.Archive

Archive 存储引擎会缓存所有的写,并且利用 zlib 算法对表数据进行进行压缩,磁盘 I/O 更少。比 MyISAM 和 InnoDB 更加节约磁盘空间。

Archive 表数据存储在 ARZ 为后缀的文件中,表结构信息存储在 .frm 为后缀的文件中。

特点:

1、Archive 只支持 insert 和 select 操作,支持行级锁和专用的缓冲区,所以可以实现高并发的插入;
2、只允许在自增 ID 列上建立索引。

适用场景:

1、不需要修改和删除数据的 日志和数据采集类应用(无法在 OLTP 应用中使用)。

5.Memory

Memory 存储引擎也称为 HEAP 存储引擎,数据保存在内存中,Memory 表的数据是易丢失的,一旦 MySQL 被重启,所有 Memory 表中的数据都会消失(表结构会保留)。

Memory 表数据存储在内存中,表结构信息存储在 .frm 为后缀的文件中。

Memory 的 I/O 效率要比 MyISAM 高很多。

特点:

1、支持 HASH 索引(适合等值查找)和 BTree 索引(适合范围查找);
2、所有字段都为固定长度,就算我们在定义表时使用了 varchar(10),在存储表时还是会使用 char(10);
3、不支持 BLOG 和 TEXT 等大字段;
4、Memory 使用的是表级锁;
5、Memory 表的最大大小是由 max_heap_table_size 参数决定的,默认 16MB(对已经存在的 Memory 表修改大小是无效的,需要重建表)。

适用场景(Memory 数据易丢失,所以要求数据可再生):

1、用于查找或者是映射表,例如邮编和地区的对应表;
2、用于保存数据分析中产生的中间表;
3、用于缓存周期性聚合数据的结果表;

6.Federated

MySQL 中默认是禁止的,启动 Federated 需要在 MySQL 启动时增加 federated 参数。

特点:

1、Federated 存储引擎提供了访问远程 MySQL 服务器上表的方法;
2、本地不存储数据,数据全部放到远程服务器上;
3、本地需要保存表结构和远程服务器的连接信息。

适用场景:

1、偶尔的统计分析及手工查询。

7.如何选择正确的存储引擎

无论是否需要事务支持,只要是不需要使用 InnoDB 不支持的特殊功能外,我们都应该在新的应用中使用 InnoDB 存储引擎。

如果不想使用 InnoDB,非要从众多存储引擎中进行筛选,可以参考以下几个因素:

1、应用是否需要事务支持?

InnoDB 是目前最稳定的支持事务的存储引擎,所以还是选择 InnoDB 吧 。

2、备份?

只有 InnoDB 有在线热备方案,其他存储引擎要么不能在线热备,要么是收费的方案,所以还是选择 InnoDB 吧 。

mysqldump 并不是热备方案,这里注意一下。

3、崩溃恢复?

一般而言,MyISAM 发生崩溃后损坏数据的概率要比 InnoDB 高很多。MyISAM 恢复速度也慢,因此即使不需要事务支持,很多时候也要选择 InnoDB 存储引擎。

4、存储引擎的特有特性?

比如很多应用依赖聚集索引进行优化,那么就要选择 InnoDB。

有些应用需要使用地理空间搜索,如果使用的是 MySQL 5.7 之前的版本,只能使用 MyISAM 了,如果使用得是 MySQL 5.7 及之后的版本,还是应该使用 InnoDB。

一个数据库中,除非万不得已,否则不建议混合使用多种存储引擎,不然可能会带来一些复杂的问题和潜在的 bug。比如混合使用 MyISAM 和 InnoDB,一旦在一个事务中对两种存储引擎的表进行操作,如果出现回滚,只有 InnoDB 表中的数据会回滚,而 MyISAM 不回滚,这样就会给应用带来数据逻辑上的一些问题。

展开阅读全文

没有更多推荐了,返回首页