MySQL数据库存储引擎详解

存储引擎是什么？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中 这些技术中的每一种技术都使用不同的存储机制 索引技巧 锁定水平并且最终提供广泛的不同的功能和能力 通过选择不同的技术 你能够获得额外的速度或者功能 从而改善你的应用的整体功能

例如 如果你在研究大量的临时数据 你也许需要使用内存存储引擎 内存存储引擎能够在内存中存储所有的表格数据 又或者 你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型) MySQL默认配置了许多不同的存储引擎 可以预先设置或者在MySQL服务器中启用 你可以选择适用于服务器 数据库和表格的存储引擎 以便在选择如何存储你的信息 如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因 其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储 遗憾的是 其它类型的数据库解决方案采取的 一个尺码满足一切需求 的方式意味着你要么就牺牲一些性能 要么你就用几个小时甚至几天的时间详细调整你的数据库 使用MySQL 我们仅需要修改我们使用的存储引擎就可以了

在这篇文章中 我们不准备集中讨论不同的存储引擎的技术方面的问题(尽管我们不可避免地要研究这些因素的某些方面) 相反 我们将集中介绍这些不同的引擎分别最适应哪种需求和如何启用不同的存储引擎 为了实现这个目的 在介绍每一个存储引擎的具体情况之前 我们必须要了解一些基本的问题

如何确定有哪些存储引擎可用

你可以在MySQL(假设是MySQL服务器 以上版本)中使用显示引擎的命令得到一个可用引擎的列表

mysql> show engines   + + + +    | Engine     | Support | Comment                                                    |    + + + +    | MyISAM     | DEFAULT | Default engine as of MySQL   with great performance     |    | HEAP       | YES     | Alias for MEMORY                                           |    | MEMORY     | YES     | Hash based  stored in memory  useful for temporary tables  |    | MERGE      | YES     | Collection of identical MyISAM tables                      |    | MRG_MYISAM | YES     | Alias for MERGE                                            |    | ISAM       | NO      | Obsolete storage engine  now replaced by MyISAM            |    | MRG_ISAM   | NO      | Obsolete storage engine  now replaced by MERGE             |    | InnoDB     | YES     | Supports transactions  row level locking  and foreign keys |    | INNOBASE   | YES     | Alias for INNODB                                           |    | BDB        | NO      | Supports transactions and page level locking               |    | BERKELEYDB | NO      | Alias for BDB                                              |    | NDBCLUSTER | NO      | Clustered  fault tolerant  memory based tables             |    | NDB        | NO      | Alias for NDBCLUSTER                                       |    | EXAMPLE    | NO      | Example storage engine                                     |    | ARCHIVE    | NO      | Archive storage engine                                     |    | CSV        | NO      | CSV storage engine                                         |    + + + +     rows in set (  sec)  

这个表格显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎

对于MySQL 以前版本 可以使用mysql>show variables like have_% (显示类似 have_% 的变量):

mysql> show variables like  have_%      + + +     | Variable_name    | Value    |     + + +     | have_bdb         | YES      |     | have_crypt       | YES      |     | have_innodb      | DISABLED |     | have_isam        | YES      |     | have_raid        | YES      |     | have_symlink     | YES      |     | have_openssl     | YES      |     | have_query_cache | YES      |     + + +      rows in set (  sec)    

你可以通过修改设置脚本中的选项来设置在MySQL安装软件中可用的引擎 如果你在使用一个预先包装好的MySQL二进制发布版软件 那么 这个软件就包含了常用的引擎 然而 需要指出的是 如果你要使用某些不常用的引擎 特别是CSV RCHIVE(存档)和BLACKHOLE(黑洞)引擎 你就需要手工重新编译MySQL源码

使用一个指定的存储引擎

你可以使用很多方法指定一个要使用的存储引擎 最简单的方法是 如果你喜欢一种能满足你的大多数数据库需求的存储引擎 你可以在MySQL设置文件中设置一个默认的引擎类型（使用storage_engine 选项）或者在启动数据库服务器时在命令行后面加上 default storage engine或 default table type选项

更灵活的方式是在随MySQL服务器发布同时提供的MySQL客户端时指定使用的存储引擎 最直接的方式是在创建表时指定存储引擎的类型 向下面这样:

CREATE TABLE mytable (id int title char( )) ENGINE = INNODB

你还可以改变现有的表使用的存储引擎 用以下语句:

ALTER TABLE mytable ENGINE = MyISAM

然而 你在以这种方式修改表格类型的时候需要非常仔细 因为对不支持同样的索引 字段类型或者表大小的一个类型进行修改可能使你丢失数据 如果你指定一个在你的当前的数据库中不存在的一个存储引擎 那么就会创建一个MyISAM(默认的)类型的表

各存储引擎之间的区别

为了做出选择哪一个存储引擎的决定 我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能 这种功能使我们能够把不同的存储引擎区别开来 我们一般把这些核心功能分为四类:支持的字段和数据类型 锁定类型 索引和处理 一些引擎具有能过促使你做出决定的独特的功能 我们一会儿再仔细研究这些具体问题

字段和数据类型

虽然所有这些引擎都支持通用的数据类型 例如整型 实型和字符型等 但是 并不是所有的引擎都支持其它的字段类型 特别是BLOG（二进制大对象）或者TEXT文本类型 其它引擎也许仅支持有限的字符宽度和数据大小

这些局限性可能直接影响到你可以存储的数据 同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响 这些区别能够影响你的应用程序的性能和功能 因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策

锁定

数据库引擎中的锁定功能决定了如何管理信息的访问和更新 当数据库中的一个对象为信息更新锁定了 在更新完成之前 其它处理不能修改这个数据(在某些情况下还不允许读这种数据)

锁定不仅影响许多不同的应用程序如何更新数据库中的信息 而且还影响对那个数据的查询 这是因为查询可能要访问正在被修改或者更新的数据 总的来说 这种延迟是很小的 大多数锁定机制主要是为了防止多个处理更新同一个数据 由于向数据中插入信息和更新信息这两种情况都需要锁定 你可以想象 多个应用程序使用同一个数据库可能会有很大的影响

不同的存储引擎在不同的对象级别支持锁定 而且这些级别将影响可以同时访问的信息 得到支持的级别有三种:表锁定 块锁定和行锁定 支持最多的是表锁定 这种锁定是在MyISAM中提供的 在数据更新时 它锁定了整个表 这就防止了许多应用程序同时更新一个具体的表 这对应用很多的多用户数据库有很大的影响 因为它延迟了更新的过程

页级锁定使用Berkeley DB引擎 并且根据上载的信息页( KB)锁定数据 当在数据库的很多地方进行更新的时候 这种锁定不会出现什么问题 但是 由于增加几行信息就要锁定数据结构的最后 KB 当需要增加大量的行 也别是大量的小型数据 就会带来问题

行级锁定提供了最佳的并行访问功能 一个表中只有一行数据被锁定 这就意味着很多应用程序能够更新同一个表中的不同行的数据 而不会引起锁定的问题 只有InnoDB存储引擎支持行级锁定

建立索引

建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能 不同的存储引擎提供不同的制作索引的技术 有些技术也许会更适合你存储的数据类型

有些存储引擎根本就不支持索引 其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)

事务处理

事务处理功能通过提供在向表中更新和插入信息期间的可靠性 这种可靠性是通过如下方法实现的 它允许你更新表中的数据 但仅当应用的应用程序的所有相关 *** 作完全完成后才接受你对表的更改 例如 在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改 你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成 才接受所做的修改 如果任一项 *** 作失败了 你都可以取消这个事务处理 这些修改就不存在了 如果这个事务处理过程完成了 我们可以通过允许这个修改来确认这个 *** 作

lishixinzhi/Article/program/MySQL/201311/29301

（1）CIFS面向网络连接的共享协议，对网络传输的可靠性要求高，常使用TCP/IP；NFS是独立于传输的，可使用TCP或UDP；（2）NFS缺点之一，是要求client必须安装专用软件；而CIFS集成在OS内部，无需额外添加软件；（3）NFS属无状态协议，而CIFS属有状态协议；NFS受故障影响小，可以自恢复交互过程，CIFS不行；从传输效率上看，CIFS优于NFS，没用太多冗余信息传送；（4）两协议都需要文件格式转换，NFS保留了unix的文件格式特性，如所有人、组等等；CIFS则完全按照win的风格来作。（5）iscsi则是针对块存储的，其它两个是针对共享文件存储的。

• create procedure用来创建 存储过程 ，create function用来创建 函数

• Delimiter命令是改变语句的结束符 ，MySQL默认的结束符为号，由于procedure和function中的号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束

• rontine_body子句可以包含一个简单的SQL语句，也可以包含多个SQL语句， 通过begin…end将这多个SQL语句 包含在一起

• MySQL存储过程和函数中也可以包含类似create和drop等DDL语句

• comment子句用来写入对存储过程和函数的注释

• Language子句用来表示此存储过程和函数的创建语言

• 存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic

• 相关属性短语只有咨询含义，并不是强制性的约束

• Drop procedure/function语句用来 删除指定名称的存储过程或函数

• Begin…end语句通常出现在存储过程、函数和触发器中，其中 可以包含一个或多个语句 ，每个语句用号隔开

• 标签label可以加在begin…end语句以及loop, repeat和while语句

• 语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签

• Declare语句通常用来声明本地变量、游标、条件或者handler

• Declare语句只允许出现在begin … end语句中而且必须出现在第一行

• Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

• 本地变量可以通过declare语句进行声明

• 声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值

• 通过declare声明变量方法：

• MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的 流程控制语句 ，另外return语句也是函数中的特定流程控制语句

• Case语句在存储过程或函数中表明了 复杂的条件选择语句

• IF语句在存储过程或函数中表明了 基础的条件选择语句

其中在 function 里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的 function 指定一个参数。

在 MySQL 中创建函数时出现这种错误的解决方法：

set global log_bin_trust_function_creators=TRUE

• Iterate语句 仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环

• Leave语句表明 退出指定标签的流程控制语句块

• 通常会用在begin…end，以及loop,repeat,while的循环语句中

• Loop语句是存储过程或函数中表达 循环执行 的一种方式

• repeat语句是存储过程或函数中表达 循环执行 的一种方式

• while语句是存储过程或函数中表达 循环执行 的一种方式

• Return语句用在 函数中，用来终结函数的执行并将指定值返回给调用者

• Cursor游标用来 声明一个数据集

• 游标的声明必须在变量和条件声明之后，在handler声明之前

• Cursor close语句用来 关闭之前打开的游标

• Cursor declare语句用来声明一个游标和指定游标对应的数据集合， 通常数据集合是一个select语句

• Cursor fetch语句用来获取游标指定数据集的 下一行数据 并将各个字段值赋予后面的变量

• Open cursor语句用来打开一个之前已经 声明好的游标

• Declare condition语句命名 特定的错误条件 ，而该特定错误可以在declare…handler中指定 处理方法

• 比如在MySQL中1051error code表示的是unknown table的错误，如果要对这

个错误做特殊处理，可以用三种方法：

• Declare handler语句用来声明一个handler来处理一个或多个特殊条件，当其中的某个条件满足时则触发其中的statement语句执行

• Statement可以是一个简单SQL语句，也可以是begin…end组成的多个语句

• Handler_action子句声明当执行完statement语句之后应该怎么办

Condition_value的值有以下几种：

• 当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理

• create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行

• 触发器创建时需要 指定对应的表名 tbl_name

• Definer关键词用来指定trigger的安全环境

• Trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的 每行数据修改前或者后 执行

• Trigger_event指定触发该触发器的具体 事件

• INSERT当新的一行数据插入表中时触发，比如通过执行insert,load data,replace语句插入新数据

• UPDATE当表的一行数据被修改时触发，比如执行update语句时

• DELETE当表的一行数据被删除时触发，比如执行delete,replace语句时

• 当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器

• 从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下按照创建的时间依次执行，通过 指定FOLLOWS/PRECEDES改变执行顺序 ，即FOLLOWS时表示新创建的触发器后执行，PRECEDES则表示新触发器先执行

• Trigger_body表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段， OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

• Drop trigger语句用来 删除一个触发器

• If exists短语用来避免删除不存在的触发器时引发报错

• 当你执行drop table时，表上的触发器也被drop掉了

---