MySQL存储引擎简介（转）

MySQL存储引擎简介（转）

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

    例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持 事务 处理的数据库(以确保 事务 处理不成功时数据的回退能力).这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。 MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用。

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

如何确定有哪些存储引擎可用
你可以在 MySQL (假设是 MySQL 服务器4.1.2以上版本)中使用显示引擎的命令得到一个可用引擎的列表。
mysql > mysql > show engines;
Engine        Support Comment
MyISAM     DEFAULT Default engine as of MySQL 3.23 with great performance    
MEMORY     YES   Hash based, stored in memory, useful for temporary tables  
InnoDB           YES     Supports transactions, row-level locking, and foreign keys   
MRG_MYISAM YES                  Collection of identical MyISAM tables  

使用一个指定的存储引擎
* 你可以使用很多方法指定一个要使用的存储引擎。你可以在 MySQL 设置文件中设置一个默认的引擎类型（使用 storage_engine 选项）或者在启动数据库服务器时在命令行后面加上--default-storage-engine或--default-table-type选项 。

* 更灵活的方式是在随 MySQL 服务器发布同时提供的 MySQL 客户端时指定使用的存储引擎。最直接的方式是在创建表时指定存储引擎的类型，向下面这样:
CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB

* 你还可以改变现有的表使用的存储引擎，用以下语句:
ALTER TABLE mytable ENGINE = MyISAM

* 你还可以查看现有的表使用的存储引擎，用以下语句：
SHOW TABLE STATUS FROM dbname


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

各存储引擎之间的区别
为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功 能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。

字段和数据类型
    虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是， 并不是所有的引擎都支持其它的字段类型，特别是BLOG（二进制大对象）或者TEXT文本类型。 其它引擎也许仅支持有限的字符宽度和数据大小。
    这些局限性可能直接影响到你可以存储的数据，同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。

锁定
    数据库引擎中的锁定功能决定了如何管理信息的访问和更新 。 当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修改这个数据(在某些情况下还不允许读这种数据) 。
    锁定 不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查询 。这是 因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同一个数据。由于向数据中插入信息 和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。
    不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持 的级别有三种:表锁定、块锁定和行锁定。支持最多的是 表锁定，这种锁定是在MyISAM中提供的 。在数据更新时，它锁定了整个表。这就防止了许多应用程序 同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。
    页级锁定使用Berkeley DB引擎， 并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。
    行级锁定提供了最佳的并行访问功能， 一个表中只有一行数据被锁定。 这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不会引起锁定的问题 。只有InnoDB存储引擎支持行级锁定 。

建立索引
    建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你存储的数据类型。
    有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)。

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

InnoDB和MyISAM是在使用MySQL 最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM类型不支持事务 处理等 高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务 支持，而InnoDB提供事务 支持已经外部键等高级数据库功能。

MyIASM是IASM表的新版本，有如下扩展：
二进制层次的可移植性。
NULL列索引。
对变长行比ISAM表有更少的碎片。
支持大文件。
更好的索引压缩。
更好的键吗统计分布。
更好和更快的auto_increment处理。

以下是一些细节和具体实现的差别：

1.InnoDB不支持FULLTEXT(全文搜索)类型的索引。
2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL 不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”

任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL 的性能优势。

######

查询放在事务 里是没有意义的。
事务 是为了保持数据的一致性，而查询不会造成数据的变动。
比如说，你的a表使用了b表的一个数据，现在改动b表后，a表的数据要随之更改。你需要2个sql语句来完成这个动作，sql1   和sql2   ，   现在执行之中如果发生意外，比如：机器断电。   sql1执行了，而sql2没有执行，那么你需要的结果没有出现，可能下次使用a表数据时就会发现数据不对了。使用事务 后，   如果sql1完成后，断电（或者其它方式中断），数据库会将未完成的事务 回滚，也就是把sql1执行的结果undo，这样数据还是一致，只是没有完成。你可以再次尝试。