房源管理免费系统,章丘市网站建设seo,优惠券直播网站怎么做的,2024免费网站推广本篇主要介绍MySQL中常见的存储引擎。
目录
一、InnoDB引擎
简介
特性
最佳实践
创建InnoDB 存储文件
二、MyISAM存储引擎
简介
特性
创建MyISAM表
存储文件
存储格式
静态格式
动态格式
压缩格式
三、MEMORY存储引擎
简介
特点
创建MEMORY表 存储文件
内…本篇主要介绍MySQL中常见的存储引擎。
目录
一、InnoDB引擎
简介
特性
最佳实践
创建InnoDB 存储文件
二、MyISAM存储引擎
简介
特性
创建MyISAM表
存储文件
存储格式
静态格式
动态格式
压缩格式
三、MEMORY存储引擎
简介
特点
创建MEMORY表 存储文件
内存管理 一、InnoDB引擎
简介
InnoDB是一款兼具高性能和高可靠性的存储引擎。在MySQL中默认以InnoDB作为存储引擎在创建表时如果我们没有指定其它存储引擎作为存储引擎将默认使用InnoDB作为存储引擎。
特性
InnoDB的主要特点如下
InnoDB支持使用事务事务遵从ACID特性且事务具有回滚提交和崩溃恢复的功能以保护用户的数据。InnoDB支持行级锁能够带来更多的并发。InnoDB在内存中维护了一个缓冲池将常用的数据保存到缓冲池中如果要使用的数据包含在缓冲池中则直接从缓冲池中读取如果不包含则从磁盘中读取并使用并将从磁盘中读取到的数据保存到缓冲池中从而减少磁盘IO的次数从而大大提高SQL的执行性能。InnoDB支持使用外键约束以保证数据的完整性在进行插入插入更新删除时确保关联表之间的一致性。在InnoDB中实现了一个自适应哈希的功能当某条查询语句被执行多次时会自动将该语句和执行结果通过Key-value的结构组织起来下次进行相同的查询时直接返回查询结果。
最佳实践
下面我们来了解一下如何使用才能发挥出InnoDB的最佳效果。
在表中为查询最频繁的列或多个列 指定主键或复合主键如果没有合适的列作为主键则创建一个自增的列作为主键。在多个表根据相同的ID查询数据建议使用表连接可以在连接的列上定义外键并在每个表中使用相同的数据类型来声明这些列添加外键可以确保被外键引用的列使用索引从而提高性能。将多个DML操作组合在自己创建的事务中统一提交或者回滚以免创建事务过于频繁DML操作会自动创建一个事务并自动提交。由于DML语句自动创建的事务是自动提交的因此在每秒提交数百次事务的服务器上应该结合存储设备的写入速度关闭事务的自动提交可以通过系统变量autocommitoff设置。不使用Lock Tabel来对表加锁因为InnoDB可以在不牺牲可靠性和高性能的情况下同时保证对一张表的并发访问。
创建InnoDB
创建InnoDB表的语法如下 use test_db #选择数据库 create table InnoDB_test(id int primary key auto_increment,name varchar(20)) engine InnoDB; #创建一个InnoDB表 存储文件 创建的InnoDB表的表空间文件会保存到数据目录中的数据库同名目录中例如上面创建的这个表的文件就会被保存如下目录中文件名为 表名.ibd,里面包含了表的具体数据和相关表描述信息。 我们是无法直接该.ibd文件中的表描述信息的如果需要读取的话可以使用ibd2sdi工具进行读取使用方法如下 ibd2sdi --dump-fileInnoDB_test.txt InnoDB_test.ibd 通过上述命令就能提取我们的.ibd文件中的表描述信息并在当前目录生成一个我们可以读懂的.txt文件文件的内容如下 在MySQL5.x版本以前的版本中会生成一个.frm文件来单独描述表描述信息。
二、MyISAM存储引擎
简介
相对于InnoDB引擎而言MyISAM引擎的表空间文件占用的空间相对较小但MyISAM引擎采用的一个表级锁因此在并发场景下读写性能相对较低MyISAM存储引擎通常在中小型web项目以及级数据仓库中的只读或者主要是读的场景中使用。
特性
MyISAM表中最大可以存放2^64行数据每个MyISAM表中最多可以创建64个索引每个索引中最多包含16个列支持并发插入可以将数据文件和索引文件放到不同设备的不同目录中从而提高访问速度BLOB和TEXT类型的数据也能够被索引。索引列中允许出现null值可以设置myisam_recover_options系统变量来使mysqld在启动时自动修复上一次没有正确关闭的MyISAM表。表中varchar 和char列总和最多可达64kbunique约束的长度不受限制。
创建MyISAM表
在创建MyISAM表需要显示指定使用MyISAM引擎具体语法如下 CREATE TABLE myisam_test(id int primary key auto_increment ,name varchar(20))ENGINE MyISAM [DATA DIRECTORY PATH][INDEX DIRECTORY PATH]; 其中DATA DIRECTORY和INDEX DIRECTORY表示数据文件和索引文件的绝对地址我们可以显示指定不指定则使用默认的数据目录来存放数据文件和索引文件。
存储文件
创建MyISAM表时会根据表名生成三个不同后缀名的文件分别是.MYD数据文件、.MYI(索引文件、.sdi表信息描述文件JSON格式。这三个文件和InnoDB表的.ibd文件一样都默认保存在MySQL的数据目录中。
存储格式
MyISAM表支持三种不同的存储格式其中FIXED(静态格式和DYNAMIC动态格式根据使用的列类型自动选择而另一种格式只能使用myisampack实用程序生成并且为只读。
当表中的列不存在BLOB类型和TEXT类型的列时在使用CREATE TABLE 或者ALTER TABLE语句创建或者修改表时可以提过ROW_FORMAT将表设置成动态格式或者静态格式。
使用myisamchk可以将压缩格式的MyISAM表进行解压语法为myisamcnk --uppack.下面我们来具体了解一下这三种存储格式。
静态格式
静态格式是MyISAM表的默认格式一般在表中不含可变类型varchar,BLOB,TEXT,varbinary)的列时使用,如果包含且长度小于固定的列宽则字符串用空格补充到列宽二进制用0x00字节补充到列宽因此静态格式中所有列的长度都是固定的。因为长度固定我们可以直接通过行号和固定行的长度来直接定位到目标行在磁盘的位置从而增加磁盘访问的速度除此之外固定的位置还能让我们在数据损坏时更好的进行恢复但由于无论可变长度的类型是否达到最长都会补到最长的列宽这样会造成一定的空间浪费。另外需要注意的是如果当前类可为null,会用1bit的额外空间来标识当前是否为null。总的来说静态格式的安全性访问速度和简易性相对更好但会带来一些空间浪费。
动态格式
当表中包含可变长度类型的列时通常使用动态格式也可以通过ROW_FORMAT DYNAMIC选项设置。动态格式中每一行的长度都不是固定的在每一行中都包含一个标记来指示当前行有多长如果行因为更新操作而使长度增加下次如果长度减少就可能会带来一些空间碎片可以使用OPTIMIZE TABLE table_name 语句或者myisamcnk -r来整理这些空间碎片。除此之外在每一行中还包含一个位图来标识哪些列为空字符串或者值为0这些被标识的列将不会被保存到磁盘中因此动态格式的空间占用要小很多和静态格式一样会额外用1bit的空间来标识可能为null的列是否为null。使用动态格式时每一行每一列都可以使用单独的方式来进行压缩常用的压缩方式如下
如果某列的值为0无论原来该列为哪种数据类型都只用一个bit储存如果列中可能出现的不同的值的数量较少则数据类型转为枚举如果数据取值范围较小则统一使用更小的数据类型来进行存储例如一个bigint列其取值范围只有-128127那么就转用tinyint来存储
总的来说动态格式的空间占用要比固定格式小很多但由于不固定的长度访问速度相对较慢。
压缩格式
对于使用myisampack生成的只读数据表统一使用压缩格式压缩格式的特点如下
压缩格式的磁盘占用非常小实现了最大限度的空间节省适用于固定长度和动态长度的数据行数据只读不能进行删除和更新等修改操作
三、MEMORY存储引擎
简介
使用MEMORY引擎创建的表其所有数据都保存到内存中但由于内存的特殊性一断机器关闭或者断电都会使数据丢失因此使用MEMORY存储引擎创建的表一般只用作临时表或者只读的缓存。
特点
MEMORY存储引擎具有如下特点
使用固定长度的存储格式当列为可变长度类型时使用固定长度来存储。不能包含BLOB、TEXT类型的列非临时MEMORY表在所有客户端之间共享支持hash索引默认使用和B树索引不支持表分区使用单线程来操作表在高并发的场景下可能会出现较为严重的锁竞争因此即使数据保存在内存中使用MEMORY表的性能不一定比InnoDB高
创建MEMORY表 创建MEMORY表时需要在建表语句后面显示指定具体如下 CREATE TABLE memory_test(id int primary key auto_increment ,name varchar(20))ENGINE MEMORY 存储文件
由于memory表的所有数据都保存到内存中因此没有专门的文件用来专门保存数据只有一个sdi文件来保存表描述信息和数据字典。 内存管理
接下来我们来了解一下MEMORY存储引擎是如何管理内存中的数据时具体可以总结为以下几点
MEMORY存储引擎只有在删除整张表或者删除表中的所有行时才会真的将表中的数据所占用的内存进行回收如果只是删除单行不会真的去回收该数据的内存。如果需要回收已删除行的数据可以使用ALTER TABLE ENGINE MEMORY来强制对表进行重建。对于表中一行数据所占内存可以通过下面的公式进行计算 所有B树索引长度SUM_OVER_ALL_BTREE_KEYS(max_length_of_key sizeof(char*) * 4) 所有hash索引的长度SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2) 单行数据长度ALIGN(length_of_row1, sizeof(char*))。其中ALIGN函数是使行长度为char类型大小的精确倍数。需要注意的是sizeof(char*在32位机器上的长度为4在64位上则为8.max_heap_tbale_size系统变量用来设置一张MEMORY表的最大容量默认为16mb如果需要修改通常只修改当前会话的值只有在要确定所有会话表都采用统一的大小时才修改全局的值每次重启服务器MEMORY表中的数据将全部被清楚其数据永远不会写入磁盘持久化保存
