MySQL中的数据库引擎 [英] Database engines in MySQL

问题描述

MySQL似乎附带了许多用户可选的数据库引擎，例如InnoDB，MyISAM等.对于MySQL的初学者来说，这些似乎是压倒性的.从SQL Server迁移到MySQL的哪一个是好的开始?在MySQL中选择存储引擎的基础/最佳实践是什么?

MySQL seems to be shipping with a lot of user-selectable database engines like InnoDB, MyISAM etc. For a starter to MySQL, these seems to be overwhelming. Which one is a good to start for the one migrating from SQL Server to MySQL? What is the basis / best practices for selecting a storage engine in MySQL?

推荐答案

从此处开始: http://stackoverflow.com/questions/129/how-to-export-data-from-sql-server-2005-to-mysql [ http://dba.stackexchange.com/questions/3153/need- to-migrate-sql-server-to-mysql [ ^ ]

Start here: http://www.mysql.com/why-mysql/white-papers/mysql_wp_mssql2mysql.php[^]. Registration is needed.

Other references:
http://stackoverflow.com/questions/129/how-to-export-data-from-sql-server-2005-to-mysql[^]
http://dba.stackexchange.com/questions/3153/need-to-migrate-sql-server-to-mysql[^]

每个存储引擎都有自己的优缺点，因此请选择要使用的存储引擎使用情况很大程度上取决于您的应用程序需求.

还值得注意的是，可以为每个表选择存储引擎，因此您可以在同一数据库中使用不同的存储引擎来拥有不同的表.如果您在创建表时未指定表的存储范围，那么系统范围的存储引擎设置只会告诉MySQL该表使用哪种存储引擎.

主要区别如下:

InnoDB支持事务(即您可以进行更改，然后在发生错误时回滚)，而MyISAM不支持.

InnoDB可以强制执行外键约束，而MyISAM允许您定义它们但不强制执行.

MyISAM允许对TEXT字段(即文本斑点)进行全文索引，而InnoDB不允许-您也不能在InnoDB内的TEXT字段上设置默认值.

对于大多数应用程序，我建议使用InnoDB，因为它与您期望从Sql Server中获得的最接近.

但是，如果您最关心速度，那么MyISAM是值得考虑的. MyISAM非常快，因为它不需要检查外键或担心交易. TEXT字段上的全文索引也是一个很大的收获.

其他存储引擎(例如ARCHIVE)更加关注空间. ARCHIVE表是压缩的，因此它们很小，但是您不能从它们中删除或对其进行索引.这听起来好像没什么用，但它们是审核跟踪的理想选择.

这里有一篇关于比较的文章:

Each storage engine has its own pro''s and con''s, so choosing which storage engine to use largely depends on your application needs.

It''s also worth noting that the storage engine can be chosen per table, so you can have different tables using different storage engines in the same database. The system wide storage engine setting just tells MySQL what storage engine to use for a table if you don''t specify one when creating it.

The main differences are as follows:

InnoDB supports transactions (i.e. you can make changes then rollback on error), MyISAM doesn''t.

InnoDB can enforce foreign key constraints, whereas MyISAM allows you to define them but doesn''t enforce them.

MyISAM allows full-text indexing on TEXT fields (i.e. text blobs), whereas InnoDB doesn''t - you also can''t set default values on TEXT fields within InnoDB.

For most applications, I''d recommend InnoDB as it most closely matches what you''d expect coming from Sql Server.

However, if speed is your main concern, then MyISAM is worth considering. MyISAM is VERY fast, as it doesn''t need to check foreign keys or worry about transactions. The full-text indexing on TEXT fields is also a big bonus.

Other storage engines such as ARCHIVE are more concerned with space. ARCHIVE tables are compressed so they''re small, but you can''t delete from them or index them. This may sound like they''re pretty useless but they''re ideal for audit trails.

There''s an article here on comparisons: http://www.supportsages.com/blog/2010/08/mysql-storage-engines-an-overview-their-limitations-and-an-attempt-for-comparison/
For a full comparison, see the MySQL manual - it explains it all.

这篇关于MySQL中的数据库引擎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！