H5W3
当前位置:H5W3 > 其他技术问题 > 正文

jobsSQL数据库存储点引擎

目录
  • 1.MySQL引擎概述

    • 1.1.什么是存储引擎?
    • 1.2.MySQL存储引擎的架构
  • 2.查看MySQL支持的存储引擎
  • 3.MySQL5.6支持的存储引擎
  • 4.MySQL常用存储引擎特性对比
  • 5.设置与更改MySQL的引擎

    • 5.1.设置表的引擎
    • 5.2.更改表的引擎

      • 5.2.1.利用SQL命令语句修改引擎
      • 5.2.2.使用sed对备份的SQL文件进行批量转换
      • 5.2.3.mysql_convert_table_format命令修改
  • 6.MyISAM引擎

    • 6.1.什么是MyISAM引擎
    • 6.2.MyISAM引擎的存储方式
    • 6.3.MyISAM引擎的主要特点
    • 6.4.MyISAM引擎适用的生产业务场景
  • 7.InnoDB引擎

    • 7.1.什么是InnoDB引擎
    • 7.2.InnoDB引擎的存储方式
    • 7.3.InnoDB引擎的主要特点
    • 7.4.InnoDB引擎适用的生产业务场景
    • 7.5.InnoDB引擎相关参数介绍
    • 7.6.InnoDB引擎调优的基本方法
  • 8.Memory存储引擎
  • 9.ARCHIVE存储引擎
  • 10.NDB存储引擎

1.MySQL引擎概述

1.1.什么是存储引擎?

数据库表里的数据存储在数据库里及磁盘上,它跟视频格式及存储磁盘文件系统格式的特征类似,也有很多存储方式。

但是,对于用户和应用程序来说,同样一张表的数据,无论采用什么引擎来存储,用户看到的数据都是一样的。对于不同的引擎存取,引擎功能、占用的空间大小、读取性能等可能都有区别。

存储引擎是MySQL数据库用来处理不同表类型的SQL操作的组件。

MySQL早期最常用的存储引擎为:MyISAM和InnoDB。目前,InnoDB是最常用的存储引擎,也是MySQL5.6默认的存储引擎。

1.2.MySQL存储引擎的架构

MySQL的存储引擎是MySQL数据库的重要组成部分。MySQL的每种存储引擎在MySQL里都是通过插件的方式使用的,可以轻易地从MySQL中进行加载和卸载,MySQL中可以同时支持多种存储引擎。

MySQL体系结构的组成部分:

1、连接池部分。
2、数据库管理部分。
3、SQL接口、查询分析器、优化器、缓存缓冲。
4、存储引擎部分。
5、数据库数据文件和各种日志文件。
6、文件系统磁盘。

2.查看MySQL支持的存储引擎

可以在MySQL中使用显示引擎的命令来得到一个可用引擎的列表:

select version();
show engines;

命令的结果显示了数据库可用引擎的全部名单,以及在当前的数据库中是否支持这些引擎,其中前四列比较重要,第一列是引擎名字,第二列是当前数据库是否支持,第三列是描述,第四列表示是否支持事务。

3.MySQL5.6支持的存储引擎

存储引擎 说明(带*的为重点)
InnoDB InnoDB是MySQL5.6默认的存储引擎,InnoDB支持事务,具有提交、回滚的功能,并且可以通过崩溃恢复能力来保护用户的数据,读写数据是行级锁定,可提升多用户并发访问的能力,InnoDB以集群的索引方式存储用户数据,基于主键方式查询可提高I/O性能,InnoDB也支持外键,使得数据更完整、更安全。*
MyISAM MyISAM是MySQL5.5.5以前默认的存储引擎,曾经用的很多,现在用的少了,MyISAM仅支持表级锁,读写性能都很有限。可用于只读或者绝大多数以读为主的业务场景。
Memory Memory以内存的方式存储所有数据,访问速度很快,不过其使用场景也是越来越少了。InnoDB的Buffer pool内存也可以缓存绝大多数的数据了。
CSV CSV这个引擎所对应的数据表格实际上是带有逗号分隔值的文本文件。CSV表格允许您以CSV格式导入或者转储数据,以便于读取和写入相同格式的脚本,与应用程序进行数据交换。由于CSV表是没有索引的,因此通常应在正常操作期间将数据保存在InnoDB表中,并且只能在导入或导出阶段使用CSV表。
Archive 这些紧凑、无索引的引擎表旨在存储和检索大量参考的历史、归档或安全审核信息。
Blackhole Blackhole存储引擎接受但不存储数据,类似于Unix/dev/null设备。查询总是会返回一个空集。这些表可用于将DML语句发送到从属服务器的复制配置,但是主服务器不保留其自己的数据副本。
Merge 使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组,并将其作为一个对象引用。merge适用于数据仓库等VLDB环境。
Federated Federated可通过链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库。其非常适合于分布式或数据集环境。
Example 该引擎作为MySQL源代码中的一个例子,说明了如何开始编写新的存储引擎。这主要是开发商感兴趣的。存储引擎是一个什么都不做的“stub”。您可以使用此引擎创建表,但不能存储数据或从中检索数据。

4.MySQL常用存储引擎特性对比

特性 MyISAM Memory InnoDB Archive NDB
存储限制 256TB RAM 64TB NONE 384EB
事务 NO NO YES NO NO
锁粒度 TABLE TABLE ROW ROW ROW
B-tree索引 YES YES YES NO NO
T-tree索引 NO NO NO NO YES
Hash索引 NO YES NO NO YES
Full-text search索引 YES NO YES NO NO
Clustered索引 NO NO YES NO NO
数据缓存 NO N/A YES NO YES
索引缓存 YES N/A YES NO YES
压缩数据 YES NO YES YES NO
加密数据 YES YES YES YES YES
集群数据库支持 NO NO NO NO YES
主从复制支持 YES YES YES YES YES
外键支持 NO NO YES NO NO

5.设置与更改MySQL的引擎

5.1.设置表的引擎

如果建表的时候不指定引擎,那么表的引擎就会和数据库的默认配置一致。

指定表的引擎建立表,建立一个学生表:

create tables `student` (
`Sno` int(10) not null comment '学号',
`Sname` varchar(16) not null comment '姓名',
`Ssex` char(2) not null comment '性别',
`Sage` varchar(16) default null,
`Sdept` varchar(16) default null comment '学生所在系别',
key `ind_sage` (`Sage`),
key `ind_sno` (`Sno`)
) engine=myisam default charset=utf8  # 最后一行括号外,指定引擎。

5.2.更改表的引擎

一般来说,更改MySQL引擎的需求并不多见,但偶尔也会有。更改表的引擎的几种修改方法。

5.2.1.利用SQL命令语句修改引擎

alter table oldboy engine = innodb;
alter table oldboy engine = myisam;

更改引擎:

show create table test\G
alter table test engine = myisam;
show create table test\G

使用此方法若要批量修改,则需要通过开发脚本实现,与分表分库脚本差不多。

5.2.2.使用sed对备份的SQL文件进行批量转换

使用sed对备份内容进行引擎转换:

nohup sed -e 's/MyISAM/InnoDB/g' oldboy.sql > oldboy_1.sql &

5.2.3.mysql_convert_table_format命令修改

mysql_convert_table_format --user=root --password=oldboy123 --socket=/data/3306/mysql.sock --engine=myisam oldboy test

该命令需要一些依赖包,安装方法为:

yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes

6.MyISAM引擎

6.1.什么是MyISAM引擎

MyISAM引擎是MySQL关系型数据库管理系统的默认存储引擎(MySQL5.5.5以前)。这种MySQL表存储结构可从旧的ISAM代码中扩展出许多有用的功能。在新版本的MySQL中,InnoDB引擎由于支持事务、外键等,有利于数据的一致性,以及其能支持更高的多用户并发性等优点,InnoDB已经取代了曾经常用的MyISAM引擎,不过由于数据库中的MySQL库的大部分表主要用于读取,因此,MyISAM引擎依然在使用。

6.2.MyISAM引擎的存储方式

每一个MyISAM引擎的表都对应于硬盘上的三个文件。这三个文件虽然具有一样的文件名,但是其不同的扩展名指示了其不同的类型用途:“.frm”文件用于保存表的定义,该文件并不是MyISAM引擎的一部分,而是服务器的一部分;“.MYD”用于保存表的数据;“.MYI”则是表的索引文件。“.MYD”和.MYI是MyISAM的关键点。

MySQL数据库系统的表大多数都使用MyISAM引擎。

6.3.MyISAM引擎的主要特点

特性 支持情况 说明
存储限制 256TB
事务支持 NO
锁表粒度 TABLE 即数据更新时锁定整个表:其锁定机制是表级锁定,这虽然可以让锁定的实现成本很小,但是同时也大大降低了其并发性能
全文索引 YES
数据缓存 NO 不会缓存数据
索引缓存 YES MyISAM可以通过key_buffer_size缓存索引,以大大提高访问性能,减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据
外键支持 NO 不支持外键
资源占用 因为功能不多,且管理粒度较粗,因此,MyISAM消耗系统资源比InnoDB少很多
读写是否阻塞 YES 不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读
是否默认 NO MyISAM是MySQL5.5.5之前默认的存储引擎,因为性能问题,在MySQL后期版本中被取代

6.4.MyISAM引擎适用的生产业务场景

MyISAM引擎可以使用的生产业务场景。

1、不需要事务支持并且对数据一致性要求不高的业务。
2、一般适用于读请求较多的应用,读写都频繁的场景不适合。
3、读写并发访问相对较低的业务。
4、数据修改相对较少的业务(阻塞问题)。
5、硬件资源比较差的服务器。
6、使用读写分离的MySQL从库可以使用MyISAM。

当下99%的企业业务场景,都不需要使用MyISAM了,而是选择更有优势的InnoDB。

7.InnoDB引擎

7.1.什么是InnoDB引擎

InnoDB引擎是当下MySQL数据库最重要的存储引擎,其正在成为目前MySQL AB所发行新版的标准,被包含在所有的安装包里。与其他的存储引擎相比,InnoDB引擎的优点是更新数据行级锁定、支持ACID的事务、支持外键,它的设计目标是面向在线事务处理的应用,目前绝大多数互联网公司都在使用InnoDB引擎,该引擎替代了其他的引擎。MySQL5.6版本的默认引擎已变为InnoDB引擎。

7.2.InnoDB引擎的存储方式

InnoDB存储引擎将数据存放在一个像黑盒一样的逻辑表空间中,这个表空间分为共享表空间和独立表空间,从MySQL5.6开始,即默认支持将InnoDB引擎的表数据单独存放到各自独立的ibd文件中(独立表空间)。

7.3.InnoDB引擎的主要特点

特性 支持情况 说明
存储限制 64TB 存储限制有些小
事务 YES 支持4个事务隔离级别,支持多版本读
锁粒度 ROW 更新数据仅锁定行
B-tree索引 YES
T-tree索引 NO
Hash索引 NO
Full-text search索引 YES 从5.5开始支持全文索引
Clustered索引 YES 数据和主键以Cluster方式进行存储,组成一颗平衡树
数据缓存 YES 高效缓存特性:能缓存索引,也能缓存数据
索引缓存 YES 高效缓存特性:能缓存索引,也能缓存数据
压缩数据 YES 可以压缩数据
加密数据 YES 可以加密数据
集群数据库支持 NO 不支持MySQL集群,NDB是集群的引擎
主从复制支持 YES 支持主从复制集群
资源占用 由于其功能和粒度都更强,因此对硬件的要求很高
分区支持 YES 支持分区,可以提升扩展性和性能
表空间支持 YES 支持共享和独立表空间,有利于管理和提升性能

7.4.InnoDB引擎适用的生产业务场景

1、需要事务支持的业务(具有很好的事务特性)。
2、行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引来完成的。
3、数据读写及更新都较为频繁的场景,如BBS、SNS、微博、微信等。
4、数据一致性要求较高的业务,例如:充值转账、银行卡转账等。
5、硬件设备资源较好,特别是内存要大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO。

7.5.InnoDB引擎相关参数介绍

InnoDB引擎的重要参数 说明
innodb_buffer_pool_size = 2048M InnoDB使用一个缓冲池来保存索引和原始数据,缓冲池设置的越大,理论上在存取表里面的数据时所需要的磁盘I/O就越少。官方建议将InnoDB的Buffer Pool值配置为物理内存的50%~80%
innodb_data_file_path = ibdata1:12M:autoextend InnoDB数据文件的路径,默认为12MB大小ibdata1的单独文件,默认以64MB为单位自增(autoextend)
innodb_additional_mem_pool_size = 16M 该参数用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小。应用程序里的表越多,就需要在其中分配越多的内存。对于一个相对稳定的应用来说,这个参数的大小也是相对稳定的,没有必要预留非常大的值。如果InnoDB将开始从操作系统分配内存,并且向MySQL错误日志中记录警告信息。默认为1MB,当发现错误日志中已经有相关的警告信息时,就应该适当地增加该参数的大小
innodb_file_io_threads = 4 InnoDB中的文件I/O线程。通常设置为4,如果是Windows则可以设置更大的值以提高磁盘I/O
innodb_thread_concurrency = 8 你的服务器中有几个CPU就设置为几,建议使用默认设置,一般设置为8
innodb_flush_log_at_trx_commit = 2 若设置为0,就相当于innodb_log_buffer_size队列满后再统一存储,默认值为1,该值也是最安全的设置
innodb_log_buffer_size = 16M 默认为1MB,通常设置为8~16MB就足够了
innodb_log_file_size = 128M 确定日志文件的大小,更大的设置可以提高性能,但也会增加数据库恢复的时间
innodb_log_files_in_group = 3 为提高性能,MySQL可以以循环的方式将日志文件写到多个文件。推荐设置为3
innodb_max_dirty_pages_pct = 90 InnoDB主线程刷新缓存池中的数据
innodb_lock_wait_timeout = 120 InnoDB事务被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。默认值为50秒
innodb_file_per_table = 1 InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。值为0表示关闭,值为1表示开启
innodb_data_home_dir = /data/xxx InnoDB数据的存放路径
innodb_log_group_home_dir = /data/xxx 日志分组的目录路径

7.6.InnoDB引擎调优的基本方法

1、主键应尽可能小,以避免对Secondary index带来过大的空间负担。
2、建立有效索引避免全表扫描,因为会使用表锁。
3、尽可能缓存所有的索引和数据,提高响应速度,减少磁盘IO消耗。
4、在进行大批量小插入的时候,应尽量自己控制事务而不要使用autocommit自动提交。若有开关则可以控制提交方式。
5、合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性。
6、应避免主键更新,因为这会带来大量的数据移动。

8.Memory存储引擎

Memory就是内存的意思,因此Memory存储引擎(又称为heap引擎)的数据存储是放在内存(注意:由max_heap_table_size参数控制内存占用大小,默认为16MB。)中的,因此存取速度特别快,但是如果数据库宕机或重启,那么所有的数据就都会丢失,因此它比较适合用于存放临时表的数据,例如,discuz论坛数据库中的统计在线人数的session表采用的就是Memory引擎。Memory存储引擎默认采用的是Hash索引,而不像其他引擎(MyISAM和InnoDB)默认的是B-tree索引。

Memory存储引擎在使用上也有一些限制,例如,仅支持表锁,不支持TEXT和B1OB数据类型,还有当存储变长字段(varchar)时按照定长字段(char)来进行的,这也会浪费一些内存空间。Memory存储引擎在企业工作中应用的不是很多。

9.ARCHIVE存储引擎

ARCHIVE的中文意思是归档,因此ARCHIVE适用于存放大量归档历史数据(可查询但不能删除)的保存。

ARCHIVE引擎仅支持select、insert操作;MySQL5.1以后开始支持索引等操作。

ARCHIVE引擎使用zlib无损数据压缩算法,压缩比可达10:1,可大量节省磁盘空间,设计ARCHIVE引擎的目标是提供高速的插入和压缩等功能。

建立两个不同存储引擎的表,测试ARCHIVE存储引擎的表其占用空间的情况:

首先建立一个MyISAM存储引擎的表,插入数据:

create table t1 engine=myisam as select * from information_schema.columns;
show table status like 't1'\G

再建立一个ARCHIVE引擎表,插入数据:

create table t2 engine=archive as select * from information_schema.columns;

数据文件形式:

.ARZ是数据压缩文件,.frm是表结构定义文件

10.NDB存储引擎

NDB存储引擎是一个集群存储引擎,类似于oracle的RAC集群,但它是share nothing的架构,因此NDB能够提供更高级别的高可用和可扩展性。NDB的特点是数据全部存放在内存中,因此,通过主键进行查找的速度非常快。

关于NDB,有一个问题需要注意,它的连接(join)操作是在MySQL数据库层完成的,而不是在存储引擎层完成的,这就意味着,复杂的Join操作需要巨大的网络开销,查询速度会很慢,在中小型企业中,NDB引擎的使用频率极少。

未经允许不得转载:H5W3 » jobsSQL数据库存储点引擎

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址