什么是MySQL数据库引擎?[英] What are MySQL database engines?

本文是小编为大家收集整理的关于什么是MySQL数据库引擎?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我环顾四周,发现一些MySQL引擎是Innodb和Myisam.也许还有更多.我的问题是这些数据库引擎是什么?

不同的MySQL引擎有什么区别?更重要的是,我如何决定使用哪个?

推荐答案

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

如果我必须使用mySQL,我个人总是使用InnoDB.它支持交易和外国钥匙,而Myisam不得.

其他推荐答案

myisam和innodb是最常用的引擎.

myisam比InnoDB稍快,并且实现了全文索引,这对于集成搜索功能非常有用. Myisam没有进行交易,也没有实施外键约束,这是一个主要的缺点.

但是,您可以使用两者的最佳功能,并使用不同的存储引擎创建表.某些软件(我认为WordPress)使用INNO用于大多数数据,例如页面,版本之间的关系等.帖子的记录包含一个ID,该ID链接到使用MyISAM的单独内容表中的记录.这样,内容存储在具有最佳搜索功能的表中,而大多数其他数据都存储在强制数据完整性的表中.

如果我是你,我会选择Inno,因为它是最可靠的.如果需要,仅将Myisam用于特定目的.

您可以在创建新表时配置数据库默认情况下使用InnoDB.

其他推荐答案

可用的不同存储引擎,很少有理由不使用Myisam或InnoDB引擎类型. Myisam在大多数情况下都会做,但是如果您与搜索和选择相比,您的更新或插入量很大,那么您将从InnoDB引擎中获得更好的性能.要获得InnoDB的最佳性能,您需要调整服务器的参数,否则没有理由不使用它.

合并引擎是从多个,相同定义的表查询数据的极其有效的方法.内存引擎是对数据进行大量复杂查询进行大量复杂查询的最佳方法,这些查询将在基于磁盘的引擎上搜索效率低下. CSV引擎是导出可以在其他应用程序中使用的数据的好方法. BDB非常适合具有经常访问的唯一密钥的数据.

此图像提供了MySQL提供的一些存储引擎的概述:

本文地址:https://www.itbaoku.cn/post/597455.html

问题描述

I looked around and found some of the MySQL engines are innodb and MyISAM. Perhaps there are few more. My question is what are these database engines?

What are the differences between different MySQL engines? And more importantly, How do I decide which one to use?

推荐答案

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

I personally always use InnoDB if I have to use MySQL. It supports transaction and foreign keys while MyISAM doesn't.

其他推荐答案

MyISAM and InnoDB are the most commonly used engines.

MyISAM is slightly faster than InnoDB, and implements the FULLTEXT index which is quite useful for integrating search capabilities. MyISAM is not transacted and doesn't implement foreign key constraints, which is a major drawback.

But you can use the best of both and create tables with different storage engines. Some software (WordPress, I think) use Inno for most data, like relations between pages, versions etc. Records for the posts contain an ID that links to a record in a separate content table that uses MyISAM. That way, the content is stored in the table that has the best search capabilities, while most other data is stored in tables that enforce data integrity.

If I were you, I'd pick Inno, because it is the most reliable. Only use MyISAM for specific purposes if you need to.

You can configure your database to use InnoDB by default when creating new tables.

其他推荐答案

Different storage engines available, there are few reasons not to use either the MyISAM or InnoDB engine types. MyISAM will do in most situations, but if you have a high number of updates or inserts compared to your searches and selects then you will get better performance out of the InnoDB engine. To get the best performance out of InnoDB you need to tweak the parameters for your server, otherwise there is no reason not to use it.

The MERGE engine is an exceedingly effective way of querying data from multiple, identically defined, tables. The MEMORY engine is the best way to perform a large number of complex queries on data that would be inefficient to search on a disk based engine. The CSV engine is a great way to export data that could be used in other applications. BDB is excellent for data that has a unique key that is frequently accessed.

This Image provides an overview of some storage engines provided with MySQL: