MySQL数据库文件的结构及存储引擎Word文档下载推荐.docx
《MySQL数据库文件的结构及存储引擎Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《MySQL数据库文件的结构及存储引擎Word文档下载推荐.docx(17页珍藏版)》请在冰豆网上搜索。
![MySQL数据库文件的结构及存储引擎Word文档下载推荐.docx](https://file1.bdocx.com/fileroot1/2023-1/8/093ad441-dd18-446d-b11c-008e873fc003/093ad441-dd18-446d-b11c-008e873fc0031.gif)
c:
\temp\backupsales2006_05_05.sql
恢复mysqldumptest<
4)selectinto备份
备份表select*fromsalesintooutfile'
\\temp\\sales.dat'
恢复表loaddatainfile'
intotablesales
5)mysqlhotcopy备份
mysqlhotcopydatabasenamebackup_directory_path
6)数据库复制技术(...)
InnoDB概述
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。
InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。
这些特色增加了多用户部署和性能。
没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。
InnoDB也支持FOREIGNKEY强制。
在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。
InnoDB是为处理巨大数据量时的最大性能设计。
它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB存储引擎被完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。
这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。
InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。
InnoDB默认地被包含在MySQL二进制分发中。
WindowsEssentialsinstaller使InnoDB成为Windows上MySQL的默认表。
InnoDB被用来在众多需要高性能的大型数据库站点上产生。
著名的Internet新闻站点Slashdot.org运行在InnoDB上。
Mytrix,Inc.在InnoDB上存储超过1TB的数据,还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。
InnoDB配置
InnoDB存储引擎是默认地被允许的。
如果你不想用InnoDB表,你可以添加skip-innodb选项到MySQL选项文件。
被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。
如果你指定无InnoDB配置选项,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
注释:
InnoDB给MySQL提供具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。
如果拟运行的操作系统和硬件不能如广告说的那样运行,InnoDB就不能实现如上能力。
许多操作系统或磁盘子系统可能为改善性能而延迟或记录写操作。
在一些操作系统上,就是系统调用(fsync())也要等着,直到所有未写入已被刷新文件的数据在被刷新到稳定内存之前可以确实返回了。
因为这个,操作系统崩溃或掉电可能损坏当前提交的数据,或者在最坏的情况,因为写操作已被记录了,甚至破坏了数据库。
如果数据完整性对你很重要,你应该在用任何程序于生产中之前做一些“pull-the-plug”测试。
MacOSX10.3及以后版本,InnoDB使用一个特别的fcntl()文件刷新方法。
在Linux下,建议禁止回写缓存。
在ATAPI硬盘上,一个类似hdparm-W0/dev/hda命令可能起作用。
小心某些驱动器或者磁盘控制器可能不能禁止回写缓存。
要获得好的性能,你应该如下面例子所讨论那样,明确提供InnoDB参数。
自然地,你应该编辑设置来适合你的硬件和要求。
要建立InnoDB表空间文件,在f选项文件里的[mysqld]节里使用innodb_data_file_path选项。
在Windows上,你可以替代地使用my.ini文件。
innodb_data_file_path的值应该为一个或多个数据文件规格的列表。
如果你命名一个以上的数据文件,用分号(‘;
’)分隔它们:
innodb_data_file_path=datafile_spec1[;
datafile_spec2]...
例如:
把明确创建的具有相同特征的表空间作为默认设置的设置操作如下:
[mysqld]
innodb_data_file_path=ibdata1:
10M:
autoextend
这个设置配置一个可扩展大小的尺寸为10MB的单独文件,名为ibdata1。
没有给出文件的位置,所以默认的是在MySQL的数据目录内。
尺寸大小用M或者G后缀来指定说明单位是MB或者GB。
一个表空间,它在数据目录里包含一个名为ibdata1的固定尺寸50MB的数据文件和一个名为ibdata2大小为50MB的自动扩展文件,其可以像这样被配置:
50M;
ibdata2:
50M:
一个指定数据文件的完全后缀包括文件名,它的尺寸和数个可选属性:
file_name:
file_size[:
autoextend[:
max:
max_file_size]]
autoextend属性和后面跟着的属性只可被用来对innodb_data_file_path行里最后一个数据文件。
如果你对最后的数据文件指定autoextend选项。
如果数据文件耗尽了表空间中的自由空间,InnoDB就扩展数据文件。
扩展的幅度是每次8MB。
InnoDB并不感知最大文件尺寸,所以要小心文件系统,在那上面最大的文件尺寸是2GB。
要为一个自动扩展数据文件指定最大尺寸,请使用max属性。
下列配置允许ibdata1涨到极限的500MB:
autoextend:
500M
InnoDB默认地在MySQL数据目录创建表空间文件。
要明确指定一个位置,请使用innodb_data_home_dir选项。
比如,要使用两个名为ibdata1和ibdata2的文件,但是要把他们创建到/ibdata,像如下一样配置InnoDB:
F:
\mysql\data\innodb
innodb_data_home_dir=/ibdata
InnoDB不创建目录,所以在启动服务器之前请确认/ibdata目录的确存在。
这对你配置的任何日志文件目录来说也是真实的。
使用Unix或DOS的mkdir命令来创建任何必需的目录。
通过把innodb_data_home_dir的值原原本本地部署到数据文件名,并在需要的地方添加斜杠或反斜杠,InnoDB为每个数据文件形成目录路径。
如果innodb_data_home_dir选项根本没有在f中提到,默认值是“dot”目录./,这意思是MySQL数据目录。
处理InnoDB初始化问题
如果InnoDB在一个文件操作中打印一个操作系统错误,通常问题是如下中的一个:
·
你没有创建一个InnoDB数据文件目录或InnoDB日志目录。
mysqld没有访问这些目录的权限以创建文件。
mysqld不能恰当地读取f或my.ini选项文件,因此不能看到你指定的选项。
磁盘已满,或者超出磁盘配额。
你已经创建一个子目录,它的名字与你指定的数据文件相同。
在innodb_data_home_dir或innodb_data_file_path有一个语法错误。
当InnoDB试着初始化它的表空间或日志文件之时,如果出错了,你应该删除InnoDB创建的所有文件。
这意味着是所有ibdata文件和所有ib_logfiles文件。
万一你创建了一些InnoDB表,为这些表也从MySQL数据库目录删除相应的.frm文件(如果你使用多重表空间的话,也删除任何.ibd文件)。
然后你可以试着再次创建InnoDB数据库。
最好是从命令提示符启动MySQL服务器,以便你可以查看发生了什么。
AUTO_INCREMENT列在InnoDB里如何工作
如果你为一个表指定AUTO_INCREMENT列,在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器,它被用在为该列赋新值。
自动增长计数器仅被存储在主内存中,而不是存在磁盘上。
InnoDB使用下列算法来为包含一个名为ai_col的AUTO_INCREMENT列的表T初始化自动增长计数器:
服务器启动之后,当一个用户对表T做插入之时,InnoDB执行等价如下语句的动作:
SELECTMAX(ai_col)FROMTFORUPDATE;
语句取回的值逐次加一,并被赋给列和自动增长计数器。
如果表是空的,值1被赋予该列。
如果自动增长计数器没有被初始化,而且用户调用为表T显示输出的SHOWTABLESTATUS语句,则计数器被初始化(但不是增加计数)并被存储以供随后的插入使用。
注意,在这个初始化中,我们对表做一个正常的独占读锁定,这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意,如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0,InnoDB处理行,就仿佛值还没有被指定,且为它生成一个新值。
自动增长计数器被初始化之后,如果用户插入一个明确指定该列值的行,而且该值大于当前计数器值,则计数器被设置为指定列值。
如果没有明确指定一个值,InnoDB给计数器增加一,并且赋新值给该列。
当访问自动增长计数器之时,InnoDB使用专用的表级的AUTO-INC锁定,该锁持续到当前SQL语句的结束而不是到业务的结束。
引入了专用锁释放策略,来为对一个含AUTO_INCREMENT列的表的插入改善部署。
两个事务不能同时对同一表有AUTO-INC锁定。
注意,如果你回滚从计数器获得数的事务,你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。
如果用户给列赋一个赋值,或者,如果值大过可被以指定整数格式存储的最大整数,自动增长机制的行为不被定义。
在CREATETABLE和ALTERTABLE语句中,InnoDB支持AUTO_INCREMENT=n表选项来设置计数器初始值或变更当前计数器值。
因在本节早先讨论的原因,这个选项的影响在服务器重启后就无效了。
外键约束
InnoDB也支持外键约束。
InnoDB中对外键约束定义的语法看起来如下:
[CONSTRAINTsymbol]FOREIGNKEY[id](index_col_name,...)
REFERENCEStbl_name(index_col_name,...)
[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}]
[ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]
外键定义服从下列情况:
所有tables必须是InnoDB型,它们不能是临时表。
在引用表中,必须有一个索引,外键列以同样的顺序被列在其中作为第一列。
这样一个索引如果不存在,它必须在引用表里被自动创建。
在引用表中,必须有一个索引,被引用的列以同样的顺序被列在其中作为第一列。
不支持对外键列的索引前缀。
这样的后果之一是BLOB和TEXT列不被包括在一个外键中,这是因为对这些列的索引必须总是包含一个前缀长度。
如果CONSTRAINTsymbol被给出,它在数据库里必须是唯一的。
如果它没有被给出,InnoDB自动创建这个名字。
InnoDB拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的INSERT或UPDATE操作。
一个父表有一些匹配的行的子表,InnoDB对任何试图更新或删除该父表中候选键值的UPDATE或DELETE操作有所动作,这个动作取决于用FOREIGNKEY子句的ONUPDATE和ONDETETE子句指定的referentialaction。
当用户试图从一个父表删除或更新一行之时,且在子表中有一个或多个匹配的行,InnoDB根据要采取的动作有五个选择:
CASCADE:
从父表删除或更新且自动删除或更新子表中匹配的行。
ONDELETECASCADE和ONUPDATECASCADE都可用。
在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ONUPDATECASCADE子句。
SETNULL:
从父表删除或更新行,并设置子表中的外键列为NULL。
如果外键列没有指定NOTNULL限定词,这就是唯一合法的。
ONDELETESETNULL和ONUPDATESETNULL子句被支持。
NOACTION:
在ANSISQL-92标准中,NOACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber,掌握SQL,2000:
181)。
InnoDB拒绝对父表的删除或更新操作。
RESTRICT:
拒绝对父表的删除或更新操作。
NOACTION和RESTRICT都一样,删除ONDELETE或ONUPDATE子句。
(一些数据库系统有延期检查,并且NOACTION是一个延期检查。
在MySQL中,外键约束是被立即检查的,所以NOACTION和RESTRICT是同样的)。
SETDEFAULT:
这个动作被解析程序识别,但InnoDB拒绝包含ONDELETESETDEFAULT或ONUPDATESETDEFAULT子句的表定义。
当父表中的候选键被更新的时候,InnoDB支持同样选择。
选择CASCADE,在子表中的外键列被设置为父表中候选键的新值。
以同样的方式,如果在子表更新的列参考在另一个表中的外键,更新级联。
注意,InnoDB支持外键在一个表内引用,在这些情况下,子表实际上意味这在表内附属的记录。
InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。
对外键的索引被自动创建。
这是相对于一些老版本,在老版本中索引必须明确创建,否则外键约束的创建会失败。
在InnoDB内,外键里和被引用列里相应的列必须有类似的内部数据类型,以便它们不需类型转换就可被比较。
整数类型的大小和符号必须相同。
字符串类型的长度不需要相同。
如果你指定一个SETNULL动作,请确认你没有在子表中宣告该列为为NOTNULL。
如果MySQL从CREATETABLE语句报告一个错误号1005,并且错误信息字符串指向errno150,这意思是因为一个外键约束被不正确形成,表创建失败。
类似地,如果ALTERTABLE失败,且它指向errno150,那意味着对已变更的表,外键定义会被不正确的形成。
你可以使用SHOWINNODBSTATUS来显示一个对服务器上最近的InnoDB外键错误的详细解释。
InnoDB不对那些外键或包含NULL列的被引用键值检查外键约束。
对SQL标准的背离:
如果在父表内有数个行,其中有相同的被引用键值,然后InnoDB在外键检查中采取动作,就仿佛其它有相同键值的父行不存在一样。
例如,如果你已定义一个RESTRICT类型的约束,并且有一个带数个父行的子行,InnoDB不允许任何对这些父行的删除。
居于对应外键约束的索引内的记录,InnoDB通过深度优先选法施行级联操作。
如果ONUPDATECASCADE或ONUPDATESETNULL递归更新相同的表,之前在级联过程中该表一被更新过,它就象RESTRICT一样动作。
这意味着你不能使用自引用ONUPDATECASCADE或者ONUPDATESETNULL操作。
这将阻止级联更新导致的无限循环。
另一方面,一个自引用的ONDELETESETNULL是有可能的,就像一个自引用ONDELETECASCADE一样。
级联操作不可以被嵌套超过15层深。
类似一般的MySQL,在一个插入,删除或更新许多行的SQL语句内,InnoDB逐行检查UNIQUE和FOREIGNKEY约束。
按照SQL的标准,默认的行为应被延迟检查,即约束仅在整个SQL语句被处理之后才被检查。
直到InnoDB实现延迟的约束检查之前,一些事情是不可能的,比如删除一个通过外键参考到自身的记录。
当前,触发器不被级联外键的动作激活。
一个通过单列外键联系起父表和子表的简单例子如下:
CREATETABLEparent(idINTNOTNULL,
PRIMARYKEY(id)
)TYPE=INNODB;
CREATETABLEchild(idINT,parent_idINT,
INDEXpar_ind(parent_id),
FOREIGNKEY(parent_id)REFERENCESparent(id)
ONDELETECASCADE
如下是一个更复杂的例子,其中一个product_order表对其它两个表有外键。
一个外键引用一个product表中的双列索引。
另一个引用在customer表中的单行索引:
CREATETABLEproduct(categoryINTNOTNULL,idINTNOTNULL,
priceDECIMAL,
PRIMARYKEY(category,id))TYPE=INNODB;
CREATETABLEcustomer(idINTNOTNULL,
PRIMARYKEY(id))TYPE=INNODB;
CREATETABLEproduct_order(noINTNOTNULLAUTO_INCREMENT,
product_categoryINTNOTNULL,
product_idINTNOTNULL,
customer_idINTNOTNULL,
PRIMARYKEY(no),
INDEX(product_category,product_id),
FOREIGNKEY(product_category,product_id)
REFERENCESproduct(category,id)
ONUPDATECASCADEONDELETERESTRICT,
INDEX(customer_id),
FOREIGNKEY(customer_id)
REFERENCEScustomer(id))TYPE=INNODB;
InnoDB允许你用ALTERTABLE往一个表中添加一个新的外键约束:
ALTERTABLEyourtablename
ADD[CONSTRAINTsymbol]FOREIGNKEY[id](index_col_name,...)
记住先创建需要的索引。
你也可以用ALTERTABLE往一个表添加一个自引用外键约束。
InnoDB也支持使用ALTERTABLE来移除外键:
ALTERTABLEyourtablenameDROPFOREIGNKEYfk_symbol;
当年创建一个外键之时,如果FOREIGNKEY子句包括一个CONSTRAINT名字,你可以引用那个名字来移除外键。
另外,当外键被创建之时,fk_symbol值被InnoDB内部保证。
当你想要移除一个外键之时,要找出标记,请使用SHOWCREATETABLE语句。
例子如下:
mysql>
SHOWCREATETABLEibtest11c\G
***************************1.row***************************
Table:
ibtest11c
CreateTable:
CREAT