In depth analysis of the introduction differentiation creation and performance testing of the MySQL database engine

Introduction to database engine

The MySQL database engine depends on how MySQL was compiled at the time of installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. Two other types, INNODB and BERKLEY (BDB), are also commonly used. If you are skilled, you can also use MySQL+API to make your own engine. Here are some database engines:

ISAM: ISAM is a well-defined, time-tested approach to data table management that is designed with the database being queried far more often than it is updated. As a result, ISAM performs read operations quickly and does not consume a large amount of memory and storage resources. The two main drawbacks of ISAM are that it does not support transactions and is not fault-tolerant: if your hard drive crashes, your data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all of your real-time data. With its replication feature, MYSQL can support such backup applications.
MyISAM: MyISAM is MySQL's ISAM extension format and the default database engine. In addition to providing a lot of indexing and field management features not available in ISAM, MyISAM also USES a table-locking mechanism to optimize multiple concurrent reads and writes at the expense of running the OPTIMIZE TABLE command frequently to recover the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool for fixing database files and the MyISAMPACK tool for recovering wasted space. MYISAM emphasizes quick read operations, which is probably the main reason why MySQL is so popular with WEB development: most of the data operations you do in WEB development are read operations. As a result, most web hosting providers and INTERNET platform providers are only allowed to use the MYISAM format. One important drawback of the MyISAM format is the inability to recover data after a table is corrupted.
HEAP: HEAP allows temporary tables that reside only in memory. Staying in memory makes HEAP faster than ISAM and MYISAM, but the data it manages is unstable, and if it is not saved before the shutdown, all the data is lost. HEAP also doesn't waste a lot of space when rows are deleted. The HEAP table is useful when you need to use SELECT expressions to select and manipulate data. Remember to delete the table when you're done with it.
InnoDB: the InnoDB database engine is a direct product of the same technology that makes MySQL flexible: MYSQL+API. Almost every challenge you face with MYSQL stems from the fact that the ISAM and MyISAM database engines do not support transaction processing (transaction process) or foreign keys. Although much slower than the ISAM and MyISAM engines, InnoDB includes support for transaction processing and extranet keys, both of which were not available in the first two engines. As mentioned earlier, if your design requires either one of these features or both, you will be forced to use one of the latter two engines.
If you feel really skilled, you can also use MySQL+API to create your own database engine. This API gives you the ability to manipulate fields, records, tables, databases, connections, secure accounts, and all the myriad other functions you need to set up DBMS like MySQL. Going deep into API is beyond the scope of this article, but it is important that you understand the existence of MySQL+API and the technology behind the exchangeable engine. It is estimated that this plug-in database engine model can even be used to create a local XML provider for MySQL (XML provider). (any MySQL+API developer reading this article can take this 1 point as a requirement.)
The difference between MyISAM and InnoDB
InnoDB and MyISAM are the two table types most commonly used by many people when using MySQL. Each table type has its merits and demerits, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. Tables of type MyISAM emphasize performance, and the number of executions is faster than those of type InnoDB, but do not provide transaction support, while InnoDB provides transaction support and advanced database functions such as external keys.

Here are some details and implementation differences:
1.InnoDB does not support indexes of type FULLTEXT.
2.InnoDB does not store the exact number of rows in the table, that is, select count(*) fromtable, InnoDB scans the entire table once to calculate the number of rows, but MyISAM simply reads out the number of rows saved. Note that when an count(*) statement contains an where condition, the operations for both tables are identical.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index only for that field, but in the MyISAM table, it can be co-indexed with other fields 1.
4. When DELETE FROM table, InnoDB will not re-establish the table, but delete 1 row and 1 row.
5. The LOAD TABLE FROMMASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table, and then to InnoDB table after importing the data, but it does not work for tables that use additional InnoDB features (such as foreign keys).

In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, InnoDB table will also lock the full table, such as updatetable set num=1 where name like "a%".
The main difference between the two types is that Innodb supports transaction processing and foreign key and row-level locking, while MyISAM does not, so it is easy to think that MyISAM is only suitable for small projects.
As a user of MySQL, I prefer Innodb and MyISAM, but MyISAM is definitely my first choice for the database platform I currently operate and maintain: 99.9% stability, convenient scalability and high availability.

Here's why:
1. First of all, most of the projects I currently carry on the platform are those that read more and write less, while the reading performance of MyISAM is much better than that of Innodb.
2. The index of MyISAM is separated from the data, and the index is compressed, so the memory utilization rate is improved a lot. It can load more indexes, while Innodb is tightly bound with data. Without compression, Innodb is larger than MyISAM.
3, from the perspective of platform, often occurs between 1, 2 months application developers accidentally update1 table where write wrong, lead to this table can't normal use, MyISAM superiority will show up at this time, just copy from the package to take out the corresponding table files, literally in a database directory, and then dump into sql lead back to the main library, and the corresponding binlog. If it is Innodb, I am afraid it is impossible to have such a fast speed. Don't tell me to ask Innodb to regularly use the export xxx.sql mechanism for backup, because the data volume of the smallest database instance on my platform is basically several 10G.
4. According to the application logic I have contacted, select count(*) and order by are the most frequent operations, accounting for more than 60% of the total statement of sql. In fact, Innodb can lock the table, and many people think that Innodb is a row-level lock.
5, and there are often many application department I need to give them some tables of data on a regular basis, MyISAM words is very convenient, as long as you send them correspond to the table of frm. MYD, MYI files, let them in the corresponding version of the database is started, and Innodb requires export xxx. sql, because light to others files, influenced by dictionary data file, the other party is unable to use.
6. If MyISAM is better than insert for write operations, Innodb is not as good as MyISAM for write operations. If MyISAM is better than insert for write operations based on indexes, MyISAM may be worse than Innodb for write operations based on indexes.
7. If MyISAM is used, merge engine can greatly accelerate the development speed of application department. They only need to do some selectcount(*) operations on the merge table, which is very suitable for a certain type of rows (such as log, survey statistics) with a total amount of several hundred million in a large project.
Of course, Innodb is not absolutely unnecessary. I use Innodb for transaction projects such as stock trading project simulation. When there are more than 200,000 active users, it is also very easy to deal with.
In addition, one could argue you MyISAM cannot too much resistance to write operation, but I can through the architecture to make up for, say that I use existing database platform capacity: master-slave data amount in more than a few hundred T, every day more than 10 pv dynamic pages, there are several big projects data interface, by calling is not counted in pv, total (including a large project for early memcached deployment, leading to a single database query processing 90 million) every day. The average load of my overall database server is around 0.5-1.

1 generally speaking, MyISAM is suitable for:
(1) do a lot of count calculations;
(2) infrequent inserts and very frequent queries;
(3) no transactions.

InnoDB suitable for:
(1) high reliability requirements, or require transactions;
(2) the creation of the data engine is specified when the table is updated and queried fairly frequently and there is a high chance of table locking
The switch that makes all this flexibility possible is the MySQL extension provided to ANSI SQL -- the TYPE parameter. MySQL allows you to specify database engines at this level of the table, so they sometimes refer to table formats as well. The following sample code shows how to create a table using the MyISAM, ISAM, and HEAP engines, respectively. Note that the code to create each table is the same, except for the final TYPE parameter, which specifies the data engine.

The following is a quote:

CREATE TABLE tblMyISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=MyISAM
CREATE TABLE tblISAM (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=ISAM
CREATE TABLE tblHeap (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
value_a TINYINT
) TYPE=Heap

You can also use the ALTER TABLE command to move the old table from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM table to InnoDB's engine:

The following is a quote:

ALTER TABLE tblMyISAM CHANGE TYPE=InnoDB

MySQL does this in three steps. First, a copy of the table is created. Then, any changes to the input data are queued, and the copy is moved to another engine. Finally, any queued data changes are sent to the new table, and the original table is deleted.

ALTER TABLE A shortcut 

If you just want to update the table from ISAM to MyISAM, you can use the MySQL_convert_table_format command instead of writing the ALTER TABLE expression.

You can use the SHOW TABLE command (which is another extension of MySQL to the ANSI standard) to determine which engine is managing a particular table. SHOW TABLE returns a result set with multiple data columns that you can query for all types of information: the name of the database engine is in the Type field. The following sample code illustrates the use of SHOW TABLE:

SHOW TABLE STATUS FROM tblInnoDB

You can use SHOW CREATE TABLE [TableName] to fetch the information that SHOW TABLE can fetch.
1 in general, MySQL will provide multiple storage engines by default, which can be viewed as follows:
(1) see what storage engine your MySQL now offers: mysql > show engines;
(2) look at your MySQL current default storage engine: mysql > show variables like '%storage_engine%';
(3) you want to see what engine a table is using (in the display result, the following parameter engine represents the current storage engine of the table): mysql > show create table table names;
Finally, if you want to use an engine that has not been compiled to MySQL and has not been activated, it is useless. MySQL will not prompt this point. It will only give you one table in the default format (MyISAM). In addition to using the default table format, there are ways to get an error from MySQL, but for now, if you're not sure if a particular database engine is available, you'll need to use SHOW TABLE to check the table format.
More choice means better performance
Given the extra complexity of recompiling and tracing engines for specific tables, why would you want to use a non-default database engine? The answer is simple: tweak the database to suit your needs.
To be sure, MyISAM is fast, but if your logical design requires transaction processing, you are free to use an engine that supports transaction processing. As a further step, since MySQL allows you to apply the database engine at the table tier 1, you can optimize the performance of only tables that require transaction processing, leaving tables that do not require transaction processing to the more portable MyISAM engine. For MySQL, flexibility is key.

The performance test
All performance tests were performed on Micrisoft window xp sp2, Intel(R) Pentinum(R) M processor 1.6oGHz 1G memory computers.
Test method: 10 query were submitted in succession, the total number of records in the table was 380,000, and the time unit was s
Engine type MyISAMInnoDB performance difference
count 0.00083573.01633609
Query the primary key 0.005708 0.157427.57
Query the non-primary key 24.01 80.37 3.348
Update the primary key 0.008124 0.8183100.7
Update non-primary key 0.004141 0.02625 6.338
Insert the 0.004188 0.369488.21
(1) after adding the index, the speed of the MyISAM query can be accelerated: 4 206.09733 times, the speed of the InnoDB query can be accelerated 510.72921 times, and the update speed of the MyISAM is slowed down to 1/2 of the original speed, and the update speed of the InnoDB is slowed down to 1/30 of the original speed. It depends on whether you want to index, such as the log table, which is not queried. Do not index anything.
(2) if your data is in the millions and you don't have any transactions, MyISAM is the best performance option.
(3) the size of InnoDB table is much larger. Using MyISAM can save a lot of hard disk space.

In the 38w table we tested, the table takes up space as follows:

Engine type MyISAM InnoDB
Data 53,924 KB 58,976 KB
Index 13,640 KB 21,072 KB

Total space 67,564 KB 80,048 KB

Another 1 176W million record table, the space occupied by the table is as follows:
Engine type MyIsam InnorDB
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB

Total space occupied 123,269 KB179,584 KB

other
The official explanation of MySQL for InnoDB is as follows: InnoDB provides MySQL with the ability to commit, rollback, and crash recovery for transaction security (ACID compatible) storage engine. InnoDB locks at the row level and also provides a non-locked read with Oracle style 1 in SELECT statements. These features increase multi-user deployment and performance. There is no need to extend locking in InnoDB, because row-level locking in InnoDB is suitable for very small Spaces. InnoDB also supports FOREIGN KEY coercion. In an SQL query, you are free to mix tables of type InnoDB with tables of type other MySQL, even in the same query.
InnoDB is designed for maximum performance when handling large amounts of data, and its CPU efficiency is probably unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, which maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in one table space, which can contain several files (or raw disk partitions). This is different from MyISAM tables, where each table exists in a separate file, for example. The InnoDB table can be any size, even on operating systems where the file size is limited to 2GB.
InnoDB is included by default in the MySQL2 base distribution. Windows Essentials installer makes InnoDB the default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The well-known Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1TB data on InnoDB, and one other site handles an average of 800 inserts/updates per second on InnoDB.