查看当前的MySQL数据库都支持什么样的存储引擎:
mysql> show engines;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 170700
Current database: *** NONE ***
![mysql引擎笔记整理](/upload/otherpic60/23928.png)
注:
1、support的状态有default,yes,no,disabled。no表示该引擎不可用,disabled表示支持该引擎,但是当前被禁用。
2、Transactions表示是否支持事务。
3、XA表示是否支持分布式事务。
4、Savepoints表示是否支持保存点。
mysql中自带的也比较常见的存储引擎有:MyISAM,Innodb,NDB Cluster,Memory,Archive,Merge,Federated等。其中,最著名和广泛的就是MyISAM,Innodb。
1) MEMORY引擎
MEMORY存储引擎的表只拥有一个独立的磁盘文件,扩展名为.frm,用来存储表结构的定义,不包含数据。
MEMORY引擎表的数据保存在内存中,mysql服务端需要单独为其分配内存区域。分配给它的内存在正常状态下不会释放,会一直被持有。除非整个MEMORY表被删除或重建才会释放相关内存。
如果仅删除MEMORY引擎表中的数据,释放的内存会被该表新插入的数据所使用,无法被其他对象或者线程使用。
当mysql服务关闭时,MEMORY引擎表中的数据都会丢失,表结构会予以保留。
MEMORY引擎表占用的最大内存由参数max_heap_table_size系统变量来决定。该值默认识16M。可以为不同的MEMORY引擎表设置不同的最大内存。但是mysql服务重启后,所有的MEMORY引擎表的最大内存又会被系统变量的值覆盖掉。
---------设置最大内存为1M;----------
set max_heap_table_size=1024*1024;
create tabele test1(user varchar(10)) engine=memory;
---------设置最大内存为2M;----------
set max_heap_table_size=1024*1024*2;
create tabele test1(user varchar(10)) engine=memory;
除此之外,还可以在创建MEMORY引擎表时指定max_rows项的值来限制MEMORY引擎表所使用的内存。
2)csv存储引擎
csv存储引擎是基于csv格式文件存储数据的。
csv存储引擎表的所有列必须强制指定NOT NULL,而且csv存储引擎表不支持索引,不支持分区。
csv存储引擎表包含一个表的结构定义文件,扩展名为.frm,以及一个扩展名为.CSV的数据文件,还有一个同名的元信息文件,扩展名为.CSM,用来保存表状态和表中保存的数据量。
.CSV的数据文件就是CSV格式的平面文本文件,可以用excel打开。
如果.CSV文件中的内容被损坏,可以用CHECK TABLE或者REPAIR TABLE命令来进行检查和恢复。
CHECK TABLE命令用于检查文件中的每一行是否合法,如果又发现不合法的行,就抛出异常。
REPAIR TABLE命令用来修复文件,将文件中损坏的数据清除掉。注意它会将出现损坏行之后的所有记录行都清掉,而不管后面的记录行是否有合法的数据。
3)ARCHIVE存储引擎
基于ARCHIVE存储引擎的对象,能够将大量数据压缩存储,它使用了zlib无损数据压缩算法,并且还可以使用OPTIMIZE TABLE分析表使其打包成更小的格式。
ARCHIVE存储引擎的特点就是插入效率高,数据保存时占空间小。
ARCHIVE存储引擎目前仅能支持insert和select语句,不支持update,delete,replace语句。支持order by操作,BLOB列等常规列,可以使用行级锁定,但不支持索引。
ARCHIVE存储引擎拥有.frm结构定义文件,以及扩展名为.arz的数据文件。在执行优化操作时可能还会出现一个扩展名为.arn的文件
4)BLACKHOLE引擎
BLACKHOLE引擎虽然可以像其他引擎一样接受数据,但是数据并不会保存。BLACKHOLE引擎表中永远为空。但是,该引擎表支持各种索引。
尽管BLACKHOLE引擎表中不存放任何数据,但是如果启用了binlog,那么执行的sql语句实际上是会被记录的。
BLACKHOLE引擎表只有一个.frm格式的文件,用于保存表定义。
5)MRG_MYISAM存储引擎
又称为MERGE存储引擎,它是将一组MyISAM表聚合在一起,使用时就像一张表一样,以此来简化查询操作。
MRG_MYISAM存储引擎表,要求基表拥有相同的列和索引信息,并且列的定义和顺序,索引的顺序都必须一模一样。否则是无法聚合到一起的。
MRG_MYISAM存储引擎表本身并不存储数据,只是一个汇总作用,但是它除了支持查询操作外,也支持插入,修改,删除操作。操作方式和普通的MyISAM表一样。
只是插入的时候,需要一些配置,以指明往哪张MyISAM表中插入数据。这个配置就是创建MRG_MYISAM存储引擎表时的INSERT_METHOD选项。
INSERT_METHOD选项有三个值,分别是NO,FIRST,LAST。NO表示不允许插入,是默认值。FIRST是插入第一张表。LAST是插入最后一张表。
6)FEDERATED存储引擎
类似于oracle中的database link。默认mysql安装时不安装FEDERATED存储引擎的,如果需要使用该引擎,在编译安装时,添加参数-DWITH_FEDERATED_STORAGE_ENGINE。并且在启动mysql服务时,
附加参数--FEDERATED。或者修改my.cnf配置文件,加上FEDERATED一行,重启mysql服务。
FEDERATED存储引擎表,只有表结构,其物理数据是来自于远端的mysql
服务器。每个FEDERATED存储引擎表都包含了两个元素:一个远端的mysql数据库表,一个本地的数据库表。
FEDERATED存储引擎表在创建时,需要通过CONNECTION选项指定连接信息。CONNECTION选项有两种写法:一种通过字符串,一种是通过CREATE SERVER语句。通过CREATE SERVER语句创建的server信息都会被保存在mysql.servers表中。
7)MyISAM存储引擎
每个MyISAM表对象都是由3个独立的文件组成,扩展名分别是.frm,.MYD,.MYI。.frm文件用于存储表对象的结构,.MYD用于存储表数据,.MYI用于存储表的索引信息。
在MyISAM引擎表中,不管包含多少个CHAR/VARCHAR类型列,这些列的长度加起来也不能超过65535B,单表字符列最大长度不能超过65532B。
MyISAM引擎的主要优点是快,查询快,写入快。但是缺点也比较明显,不支持事务,锁粒度太粗(表级锁),在OLTP场景下不合适。
在存储数据时,MyISAM引擎表支持三种存储格式:FIXED(定长或称静态),DYNAMIC(动态),COMPRESSED(压缩)。
静态格式表就是表中不含变长度的列,比如varchar/varbinary/blob/text等,所定义的每一列保存的均是固定的字节数。或者创建时指定ROW_FORMAT=FIXED。优点:简单,查找数据最快,崩溃后最好恢复。缺点:费空间。
动态格式表就是和静态格式表相反的,或者创建时指定了ROW_FORMAT=DYNAMIC。除了字符串长度小于4的列以外,其他字符列的长度都是动态的。优点:省空间。缺点:查询相对较慢,存储空间碎片会比较多。崩溃后恢复操作比较麻烦。
(myisamchk工具:OPTIMIZE TABLE或者myisamchk -r命令用来消除碎片;myisamchk -ei用来查询表的统计信息;myisamchk -ed命令用来查询表对象的链接数。)
压缩格式表:创建只能用myisampack创建,解压缩用myisamchk命令。压缩表只能读,不能添加或修改记录。
8)InnoDB存储引擎
InnoDB拥有自己独立的缓存池,对应参数是innodb_buffer_pool_size系统变量,类似于oracle数据库中的SGA_TARGET,常用数据包括索引都在缓存中。
锁粒度为行级,提供一致性读。支持事务。支持外键约束。支持数据加密。支持查询、数据、索引缓存。支持备份/恢复到时间点。
InnoDB存储引擎表必须创建主键,一般选择将经常需要查询的列作为主键,如果没有合适的列,就将主键创造到自动增长的列。
InnoDB能够处理多会话并发读写同一个对象。
InnoDB有两种表空间方式:系统表空间和多重表空间。
InnoDB系统表空间对应哪些物理文件,是由系统变量innodb_data_file_path来设置的:
mysql> show variables like "%innodb_data%";
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
+-----------------------+------------------------+
2 rows in set (0.00 sec)
默认情况下,InnoDB数据文件时是存放在mysql的data目录中,如果想变更目录,可以通过系统变量innodb_data_home_dir来设置。
启动innodb_file_per_table选项,以启用多重表空间,使表中数据和索引保存在单独的文件中,而不是保存在系统表空间中。
设置为多重表空间时,truncate存储引擎为InnoDB的表速度很快,并且释放的空间能够被操作系统使用。
mysql5.6中,innodb_file_per_table选项默认是为启动的。
mysql> show variables like "%innodb_file%";
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+----------+
4 rows in set (0.00 sec)
InnoDB除了有表空间概念,还有自己专用的日志文件,即REDOLOG日志。
默认情况下,InnoDB引擎会创建两组大小均为5M的日志文件,分别名为ib_logfile0和ib_logfile1。
关于InnoDB引擎日志的系统参数有以下几个:innodb_log_group_home_dir redo log的保存路径,默认是在datadir变量指定的路径下
innodb_log_file_size 每一个redo日志的大小,最大不能超过512G
innodb_log_files_in_group 指定日志文件组的数量,默认是两个,最多不超过100个
调整InnoDB引擎日志比较复杂,因为这几个参数均是静态参数,并且调整它们还要依赖参数innodb_fast_shutdown。
innodb_fast_shutdown参数用来控制InnoDB的关闭模式,有三个值:
0 0模式类似于oracle的shutdown normal
1 1模式类似于oracle的shutdown immediate,默认值是1
2 2模式类似于oracle的shutdown abort
InnoDB引擎因为支持事务,所以一定会需要回滚段。关于InnoDB引擎的回滚段,有以下两个参数:innodb_undo_directory 指定UNDO日志的物理文件位置
innodb_undo_tablespaces 指定UNDO表空间的数量,每个undo表空间都是独立的.idb文件
innodb_undo_logs 指定undo表空间中回滚段的数量
undo表空间一旦创建,就无法删除。所以一旦使用了undo表空间,mysql的版本就无法从5.6降下去。
默认情况下,连接到MYSQL服务的客户端处于自动提交模式,即每条DML执行即提交。
如何启动InnoDB引擎的事务支持?有两种方式:
1、禁用事务的自动提交:
将autocommit系统变量置为0或者OFF。以后就由用户来决定是否提交或者回滚事务。
autocommit是会话级变量,只针对当前会话,无法全局生效。
mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
2、显式声明事务,通过语句start transaction语句。
mysql库中的表默认均为MYISAM引擎表,不可以更改引擎。
对于InnoDB引擎表而言,用show table status返回表的记录行数,行长度等状态信息并不精确,只是一个估算值。最好用count函数来进行统计。
InnoDB引擎表的逻辑存储结构从小到大分为页(pages)/块,扩展(extend)/区,段(segments),表空间。
页(pages)/块: 基本单位,默认大小为16K,可以通过参数innodb_page_size变量设置,可选值有4k,8k,16k
扩展(extend)/区:每个区固定1M大小,区由页构成。
段(segments):InnoDB中,段实际上指的是独立表空间对应的数据文件。
表空间:
InnoDB引擎内部在保存数据到数据库时,会自动生成3个内部列:DB_TRX_IDG标记事务的标识符,DB_ROLL_PRT为回滚标记,DB_ROW_ID为行ID
回滚段中的UNDO日志分为insert UNDO日志和update UNDO日志。
insert UNDO日志:仅在事务回滚时需要,事务提交后即被废弃
update UNDO日志:用于构造一致性读。
回滚段中的UNDO日志记录大小,一般会小于实际插入或者修改的记录大小。
purge线程:delete记录时,记录并不会立即在物理上被删除,只有当InnoDB废弃update UNDO日志后,才会从物理上移除关联的列和索引记录,这种移除操作称之为purge。
控制purge线程延迟可以用过系统变量innodb_max_purge_lag进行设置。
当前名称:mysql引擎笔记整理
分享URL:
http://cdkjz.cn/article/jsoidp.html