In-depth analysis of MySQL Database Engine introduction, differences, creation and performance testing

Database Engine Introduction

The MySQL Database Engine depends on how MySQL is compiled during installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. The other two types of INNODB and BERKLEY (BDB) are also frequently used. If the technology is superb, you can use MySQL + API to build an engine. The following describes several database engines:

ISAM: ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications.
MyISAM: MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, the cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool used to fix database files and the MyISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. An important defect in MyISAM format is that data cannot be restored after the table is damaged.
HEAP: HEAP allows only temporary tables in memory. HEAP is faster than ISAM and MYISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up.
InnoDB: The InnoDB database engine is a direct product that creates MySQL flexibility. This technology is MYSQL + API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.
If you feel that you are indeed highly skilled, you can also use MySQL + API to create your own database engine. This API provides you with the functions of operation fields, records, tables, databases, connections, and security accounts, as well as all the other numerous functions required to create DBMS such as MySQL. An in-depth explanation of the API is beyond the scope of this article, but you need to understand the existence of MySQL + API and the technology behind the interchangeable engine. This is very important. It is estimated that the plug-in database engine model can even be used to create a local XML provider for MySQL ). (Any MySQL + API developer who reads this article may regard this as a requirement .)
Differences between MyISAM and InnoDB
InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

The following are some differences between details and specific implementations:
1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) fromtable, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table frommaster operation does not work for InnoDB. The solution is to change the InnoDB TABLE to the MyISAM TABLE first, and then the InnoDB TABLE after the data is imported, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, updatetable set num = 1 where name like "a %"
The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.
From the perspective of MySQL users, Innodb and MyISAM both prefer to use MySQL, but from the perspective of my current O & M database platform, we need to meet the following requirements: 99.9% stability, for convenient scalability and high availability, MyISAM is definitely my first choice.

The reason is as follows:
1. First, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.
2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.
3. From the platform perspective, it is often the next two months that application developers may accidentally update the where write range of a table, which makes the table unusable, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. Don't tell me to let Innodb export xxx regularly. SQL mechanism backup, because the data volume of the smallest database instance on my platform is basically dozens of GB.
4. From the application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.
5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because the dictionary data files cannot be used by the other party.
6. If Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to Innodb, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.
7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some selectcount (*) operations on the merge table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.
Of course, Innodb is not absolutely unnecessary. I use Innodb to simulate stock trading projects with transaction projects, for example. When more than 0.2 million of active users are active, it is also very easy to cope, therefore, I personally like Innodb, but if I start from the database platform application, I will still start with MyISAM.
In addition, some people may say that MyISAM cannot defend against too many write operations, but I can make up for it through the architecture, saying that it is my existing database platform.Capacity:The total volume of Master/Slave Data is more than several hundred TB, and more than million pv dynamic pages are generated every day. There are also several major projects that do not count as the total number of PVS for calling through data interfaces, (this includes a large project where a single database processes tens of millions of queries every day because memcached was not deployed in the early stage ). The average load of my entire database server is between 0.5 and 1.

In general, MyISAM is suitable:
(1) perform a lot of count calculations;
(2) insertion is not frequent, and queries are very frequent;
(3) No transactions.

InnoDB is suitable:
(1) high reliability requirements or transactions;
(2) When tables are updated and queried frequently, and tables are locked for a great opportunity, specify the creation of the Data Engine.
The switch that makes all flexibility possible is to provide the MySQL extension-TYPE parameter for ansi SQL. MySQL allows you to specify the database engine at the table layer, so they sometimes refer to table formats. The following sample code shows how to create tables using the MyISAM, ISAM, and HEAP engines. Note that the code for creating each table is the same except for the final TYPE parameter, which is used to specify the Data Engine.

Reference content is as follows:

Copy codeThe Code is as follows: 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 original TABLE from one engine to another. The following code shows how to use alter table to move a MyISAM TABLE to the InnoDB engine:

Reference content is as follows:

Copy codeThe Code is as follows: alter table tblMyISAM change type = InnoDB

MySQL uses three steps to achieve this goal. First, a copy of the table is created. Then, any changes to input data are queued, and the copy is moved to another engine. Finally, any changes made to data in the queue are sent to a new table, and the original table is deleted.Copy codeThe Code is as follows: alter table shortcut

If you want to update a TABLE from ISAM to MyISAM, you can use the MySQL_convert_table_format command without writing the alter table expression.

You can use the show table command (another extension of MySQL to the ANSI standard) to determine which engine is managing specific tables. Show table returns a result set with multiple data columns. You can use this result set to query all types of information: the database engine name is in the Type field. The following sample code illustrates how to use show table:

Copy codeThe Code is as follows: show table status from tblInnoDB

You can use show create table [TableName] to retrieve the information that can be retrieved by show table.
In general, MySQL provides multiple storage engines by default. You can view the following information:
(1) Check what storage engine your MySQL currently provides: mysql> show engines;
(2) Check your current default MySQL storage engine: mysql> show variables like '% storage_engine % ';
(3) What engine is used for a table (the storage engine used for the table is displayed after the parameter engine in the displayed result): mysql> show create table name;
Finally, if you want to use an engine that is neither compiled into MySQL nor activated, it is useless. MySQL will not prompt this. However, it only provides you with a table in the default format (MyISAM. In addition to using the default table format, there are also ways to make MySQL give an error message, but for now, if you are not sure whether a specific database engine is available, you need to use show table to check the TABLE format.
More options mean better performance
Engines used for specific tables need to be re-compiled and tracked. Considering this extra complexity, why do you still want to use non-default database engines? The answer is simple: you need to adjust the database to meet your requirements.
Certainly, MyISAM is indeed fast, but if your logic design requires transaction processing, you can freely use the engine that supports transaction processing. Further, because MySQL allows you to use the database engine at the table layer, You can optimize the performance of the tables that require transaction processing, instead, the tables that do not need to be processed are handed over to the MyISAM engine, which is lighter. For MySQL, flexibility is the key.

Performance Testing
All performance tests are performed on: Micrisoft window xp sp2, Intel (R) Pentinum (R) M processor 1.6 oGHz 1 GB memory computer.
Test method: 10 queries are submitted consecutively. Total table records: 0.38 million, in seconds.
Performance difference of engine type MyISAMInnoDB
Count 0.00083573.01633609
Query primary key 0.005708 0.157427.57
Query non-primary key 24.01 80.37 3.348
Update primary key 0.008124 0.8183100.7
Update non-primary key 0.004141 0.02625 6.338
Insert 0.004188 0.20.488.21
(1) After the index is added, MyISAM queries can be accelerated: 4 to 206.09733 times, InnoDB queries can be accelerated by 510.72921 times, And MyISAM updates can be slowed down to the original 1/2, the update speed of InnoDB slowed down to 1/30. It depends on the situation to determine whether to add indexes, such as non-query log tables. do not perform any indexes.
(2) If your data volume is millions and you do not have any transaction processing, MyISAM is the best choice for performance.
(3) InnoDB tables are larger in size. Using MyISAM can save a lot of hard disk space.

In the table we tested, the table occupies the following space:

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

Occupied Space: 67,564 KB 80,048 KB

In another million recorded table, the table occupies the following space:
Engine type MyIsam InnorDB
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB

Occupied Space: 123,269 KB179, 584 KB

Others
MySQL officially explains InnoDB as follows: InnoDB provides MySQL with a transaction security (ACID compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an Oracle-style non-locked read in SELECT statements. These features increase multi-user deployment and performance. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.
InnoDB is designed for the maximum performance when processing a large amount of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory. InnoDB stores its tables and indexes in a tablespace. The tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB.
InnoDB is included in MySQL binary distribution by default. Windows Essentials installer makes InnoDB the default MySQL table on Windows.
InnoDB is used to generate large databases that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1 TB of data on InnoDB, and some other sites process an average of 800 inserts/updates per second on InnoDB.