The difference between MYSQL MyISAM and InnoDB

MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If the rollback of a thing causes incomplete rollback, it does not have atomicity. If executing a lot of select,myisam is a better choice.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table. For INNODB types of tables that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if autocommit is open), which can greatly improve performance

2. MySQL is MyISAM by default.
4. MyISAM does not support transactions, while InnoDB supports. InnoDB autocommit By default is open, that is, each SQL statement will be encapsulated into a transaction by default, automatically commit, which will affect the speed, so it is best to put a number of SQL statements between Begin and commit, compose a transaction to commit.
6. InnoDB supports data row locking, MyISAM does not support row locking and only supports locking the entire table. That is, MyISAM read and write locks on the same table are mutually exclusive, MyISAM concurrent read and write if the queue is waiting for both read requests and write requests, the default write requests high priority, even if the read request first arrived, so MyISAM is not suitable for a large number of queries and modifications coexist, so the query process will be blocked for a long time. Because MyISAM is a lock table, a read operation can be time-consuming to starve other write processes.
8. InnoDB supports foreign keys, MyISAM not supported.
10. The InnoDB has a greater range of primary keys, up to twice times the maximum of MyISAM.
12. InnoDB does not support full-text indexing, and MyISAM supports it. Full-text indexing refers to the backward-sort index of each word in char, varchar, and text (except for the inactive word). MyISAM's full-text index is actually useless, because it does not support Chinese word segmentation, must be used by the user to add a space after the word and then write to the data table, and less than 4 Chinese characters will be ignored as the word stop.
14. MyISAM supports GIS data, INNODB not supported. That is, MyISAM supports the following spatial data objects: Point,line,polygon,surface and so on.
16. No where COUNT (*) uses MyISAM much faster than InnoDB. Because MyISAM has a built-in counter, COUNT (*) reads directly from the counter, and InnoDB must scan the entire table. Therefore, when you execute count (*) on InnoDB, you typically accompany where, and where you want to include an index column other than the primary key. Why is this special emphasis on "outside the primary key"? Because primary index in INNODB is stored with raw data, secondary index is stored separately and a pointer to primary key. So just count (*) uses secondary index to scan faster, while primary key is mostly useful when scanning indexes while returning raw data

About MySQL engine
MySQL is a kind of database software that we use more often. It has many advantages, such as open source, free and so on. In fact, it has a very good feature, that is, there are a variety of engines to choose from. If the driver can change the terrain and the most suitable engine according to different road conditions, then they will create a miracle. However, they do not have the same easy to replace the engine, but we can!
The so-called know-how can be baizhanbudai, in order to bring them to the extreme, first of all we should come to understand the MySQL provides us with these kinds of engines.
In general, MySQL has the following engines: ISAM, MyISAM, HEAP, InnoDB, and Berkley (BDB). Note: Different versions of the supported engines are differentiated. Of course, if you feel you are really skilled, you can also use mysql++ to create your own database engine, this has out of my knowledge, cattle can refer to mysql++ API help to achieve. Each of the 5 types of engines is described below:

ISAM

ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.

MyISAM

MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management that are not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The cost is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most virtual hosting providers and Internet Platform providers (Internet presence Provider,ipp) only allow the use of the MyISAM format.

HEAP

The heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table. Let me repeat: Don't forget to delete the table after you have finished using the form.

InnoDB and Berkley DB

The InnoDB and Berkley DB (BDB) database engine is a direct product of the technology that makes MySQL flexible, which is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and the MyISAM database engine does not support transactional processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

Having met so many engines, and we know where they should be in case of emergency, then we have to learn how to change these engines.

Globle: One of the simplest ways is to change the server configuration and set it directly to the engine you need. This can be done under win by changing the Default-storage-engine entry in the Mysql.ini in the server installation directory, or by running the MySQL Server Instance Configuration Wizard to make simple settings.

Per table: In addition to the global approach, there is a more flexible configuration method, which is to set the engine according to the table, so that we can use the transaction processing of the table set to InnoDB, the other set to MyISAM, the performance to the extreme, this is not very exciting? The Setup method is also relatively simple:

1. You can add an extension statement at the end of the CREATE TABLE statement, such as type=myisam (or engine = INNODB) to specify the current target engine type. You can also use the ALTER statement to make changes after the table is established. You can use Show Table STATUS from dbname to view the engine of the tables in the current database when you are not sure.

2. Create a table using the MySQL client that is available with the MySQL server and choose the storage engine to use when you create it.

Different engine options in different business processing, performance will be a world of difference!!

Off Topic:

To achieve optimal server performance, you need to consider the configuration of the server as well. If it is under win then it can be set by running the MySQL server Instance Configuration Wizard, which will guide you to set the current server type and so on.

There is also a lot of data to insert when you can consider using the Insert delayed statement (specific syntax reference MySQL manual). When a client uses insert delayed, it gets a confirmation from the server at once. And rows are queued, and the row is inserted when the table is not being used by another thread. Another important benefit of using insert delayed is that insertions from many clients are lumped together and written into a block. This is much faster than performing many separate inserts. Of course, it also has its scope of application, specific reference manual, I will not repeat it.






Introduction of Data Engine

In MySQL 5.1, MySQL AB introduced a new plug-in storage engine architecture that allows the storage engine to be loaded into the new MySQL server being shipped.

Using the MySQL plug-in storage engine architecture allows database professionals to choose a dedicated storage engine for specific application requirements, without the need to manage any special application coding requirements at all. With the MySQL server architecture, application programmers and DBAs can no longer consider all the underlying implementation details because of the consistent and simple application models and APIs that are available at the storage level. Therefore, although different storage engines have different capabilities, the application is decoupled from it.

MySQL supports several storage engines as a processor for different types of tables. The MySQL storage engine includes the engine that handles the transaction security table and the engine that handles the non-transactional security tables:

· MyISAM Manage non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations, it is the default storage engine, unless you configure MySQL by default using a different engine.

· The memory storage Engine provides an "in-store" table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, the memory and merge storage engines handle non-transactional tables, both of which are included by default in MySQL.

Note: The memory storage engine is formally identified as the heap engine.

· The InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in the Mysql-max binary distribution released for the operating system that supports it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to allow or disallow any engine as you prefer.

· The example storage engine is a "stub" engine that does nothing. You can use this engine to create a table, but no data is stored in it or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Similarly, its main interest is to developers.

· NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are partitioned into multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In a future MySQL distribution, we want to add support for this engine from other platforms, including Windows.

· The archive storage engine is used to easily overwrite a large amount of data that is stored in a non-indexed manner.

· The CSV storage engine stores data in a comma-delimited format in a text file.

· The Blackhole storage engine accepts but does not store data, and the retrieval always returns an empty set.

· The federated storage Engine has data in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In future distributions, we want to have it connect to another data source using a different drive or client connection method.

The plug-in storage engine architecture provides a standard set of management and support services that are common to all basic storage engines. The storage engine itself is the component of the database server and is responsible for the actual operation of the basic data maintained at the physical server level.

This is an efficient, modular architecture that provides great convenience and benefits for people who want to focus on specific application needs, including data warehousing, transactional processing, high availability scenarios, and a set of interfaces and services that are independent of any storage engine.

Application programmers and DBAs work with the MySQL database through the connector APIs and service tiers located on top of the storage engine. If the application changes need to change the underlying storage engine, or you need to add 1 or more additional storage engines to support the new requirements, you do not need to make large coding or process changes to achieve such requirements. The MySQL server architecture provides a consistent and easy-to-use API that is suitable for a variety of storage engines, which isolates the application from the underlying complexities of the storage engine.
In, graphically describes the MySQL plug-in storage engine architecture:

The MySQL pluggable Storage engine architecture


Second, select the storage engine

The various storage engines provided with MySQL are designed with different usage in mind. For more efficient use of the plug-in storage architecture, it is best to understand the advantages and disadvantages of various storage engines.

In the table below, an overview of the storage engine provided with MySQL is presented:

Storage Engine Comparison

The following storage engines are most commonly used:

· MyISAM: The default MySQL plug-in storage engine, which is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.

· InnoDB: For transactional applications, with many features, including acid transaction support.

· BDB: An alternative to the INNODB transaction engine that supports commit, rollback, and other transactional features.

· Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.

· Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.

· Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.

· Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

· Cluster/ndb:mysql's clustered database engine, especially suitable for applications with high performance lookup requirements, also requires the highest uptime and availability.

· Other: The other storage engines include CSV (referencing a comma-delimited file used as a database table), blackhole (for temporary suppression of application input to the database), and the example engine, which can help with the quick creation of a custom plug-in storage engine.

Keep in mind that you don't have to use the same storage engine for the entire server or scenario, and you can use a different storage engine for each table in the scenario, which is important.

Iii. Assigning a storage engine to a table

You can specify the storage engine when you create a new table, or specify the storage engine by using the ALTER TABLE statement.

To specify the storage engine when creating a table, use the engine parameter:
CREATE TABLE Enginetest (
ID INT
) ENGINE = MyISAM;

You can also use the type option to the CREATE TABLE statement to tell MySQL what type of table you want to create.

CREATE TABLE Enginetest (
ID INT
) TYPE = MyISAM;

Although the type is still supported in MySQL 5.1, the engine is now the preferred term.

If you omit the engine or type option, the default storage engine is used. The general default is MyISAM, but you can change it with the--default-storage-engine or--default-table-type server startup option, or by setting the Storage_engine or Table_ Type system variable to change.

To change the storage engine for an existing table, you can use the ALTER TABLE statement:
ALTER Tableenginetestengine =archive;
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

If you try to use a storage engine that is not compiled into MySQL, or if you try to use a storage engine that is compiled into MySQL but not activated, MySQL instead creates a MyISAM-type table. This behavior is handy when you copy tables between MySQL servers that support different storage engines. (for example, in a replication setup, your primary server might support the transactional storage engine for added security, but the non-transactional storage engine is used only for faster speeds from the server.) )

When an unavailable type is specified, it is automatically replaced with a MyISAM table, which confuses new users of MySQL. Whenever a table is automatically changed, a warning is generated.

MySQL always creates a. frm file to maintain the definition of tables and columns. Table indexes and data may be stored in one or more files, depending on the type of table. The server creates a. frm file above the storage engine level. A separate storage engine creates any additional files that need to be used to manage tables.

A database can contain different types of tables.

Iv. Storage engines and transactions

The following storage engines support transactions:

· InnoDB: Supports transactions through MVCC, allowing commit, rollback, and savepoint.

· NDB: Supports transactions through MVCC, allowing commit and rollback.

· BDB: Supports transactions, allowing commit and rollback.
The Transaction Security table (TST) has several advantages over non-transactional security Tables (NTST):

· More secure. Even if MySQL crashes or encounters a hardware problem, either automatically recovers or recovers from the backup plus transaction log, you can retrieve the data.

· You can combine many statements and accept them all at the same time with a commit statement (if AUTOCOMMIT is disabled).

· You can execute rollback to ignore your changes (if autocommit is forbidden).

· If the update fails, all your changes are changed back to the original. (with non-transactional security tables, all occurrences of the change are permanent).

· The transactional secure storage engine provides a better deployment for tables that are currently read with many updates.

The non-transactional security table itself has several advantages, because there is no transaction expense, and all the benefits can occur:

· Faster

· Requires less disk space

· Requires less memory to perform the update

You can combine transaction security and non-transactional security tables in the same statement to get the best of both worlds. However, in a autocommit-forbidden transaction, the transition to a non-transactional security table is still committed immediately and will not be rolled back.

Although MySQL supports several transactional secure storage engines, for best results, you should not mix different table types in one transaction. If you mix table types, you'll get a problem.

V. Inserting a search engine

Before you can use the storage engine, you must use the Install plugin statement to load the storage engine plugin (plug-in) to MySQL. For example, to load the example engine, the Ha_example.so module should first be loaded:
INSTALL pluginha_examplesoname ' ha_example.so ';

File. So must be located under the MySQL Server Library directory (typically Installdir/lib).

Six, pull out the storage engine

To unplug the storage engine, you can use the Uninstall plugin statement:
UNINSTALL pluginha_example;

If you unplug the storage engine that is being used by existing tables, these tables will become inaccessible. Before you unplug the storage engine, make sure that no tables use the storage engine.

In order to install the plug-in storage engine, the plugin file must be located in the appropriate MySQL library directory, and the user issuing the Install plugin statement must have super privileges.



When you create a table, you can specify the storage engine used by the engine keyword, and if omitted, use the system default storage engine:
CREATE TABLE T (i INT) ENGINE = MYISAM;

To view the types of storage engines supported in the system:
Mysql> show engines;

Only part of the engine support is available in the standard installer, and if you need to use a different storage engine, you will need to recompile with the source code plus the various parameters. Where default indicates that the system defaults to the storage engine, it can be changed by modifying the configuration parameters:
Default-storage-engine=myisam

View specific information for a storage engine
Mysql> show engine InnoDB status/g;

The difference between MYSQL MyISAM and InnoDB