Go In-depth analysis of the MySQL Database engine introduction, differentiation, creation, and performance testing

This article is a detailed analysis of the MySQL Database engine introduction, differentiation, creation and performance testing, the need for a friend reference 

Introduction to the Database engine

The MySQL database engine depends on how MySQL was compiled when it was installed. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and heap. Two other types of InnoDB and Berkley (BDB) are also often available. If you're skilled, you can use MYSQL+API to make an engine yourself . Several database engines are described below:

ISAM: ISAM is a well-defined and time-tested method of data table 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 of MySQL and the default database engine. In addition to providing a number of functions for index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, at the expense of the need to run the Optimize Table command frequently to restore 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 web hosting providers and internet platform providers only allow the use of the MyISAM format. An important flaw in the MyISAM format is the inability to recover data after a table is corrupted.
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.
InnoDB: The InnoDB database engine is a direct product of the technology that makes MySQL flexible, and the technology is MYSQL+API. Every challenge you face when using MySQL comes from the ISAM and MyISAM database engine does not support transactional processing (transaction process) or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transactional 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.
If you feel you are 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, security accounts, and all the other myriad features needed to build a DBMS such as MySQL. An in-depth explanation of the API is beyond the scope of this article, but it is important to understand the existence of MYSQL+API and the technology behind its interchangeable engines. It is estimated that this plug-in database engine model can even be used to create a local XML provider (XML provider) for MySQL. (Any MYSQL+API developer who reads this article can take this as a requirement.) )
the difference between MyISAM and InnoDB
InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

The following are some of the details and the specific implementation differences:
1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) fromtable, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
5.LOAD Table Frommaster operation does not work for InnoDB, the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for 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 scan when executing an SQL statement, the InnoDB table also locks the full table, for example updatetable set num=1 where name like "a%"
The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.
I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

The reasons are as follows:
1, first of all, I am currently on the platform of the majority of projects are read more write less projects, and MyISAM reading performance is stronger than InnoDB.
2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.
3, from the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time MyISAM the superiority of the embodiment, casually from the day copy of the compressed package out of the corresponding table file, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the smallest database instance on my platform has a size of dozens of g of data.
4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.
5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.
6, if and myisam than insert write operation, InnoDB also can not reach MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency write, It is also a problem to be able to chase from the library, but it is better to solve it by the multi-instance sub-database table architecture.
7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some selectcount (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as journal, Survey statistics) business table.
of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I still headman MyISAM.
In addition, some people may say that you myisam can not resist too much write operation, but I can make up through the structure, say my existing database platform capacity: Master-slave data total at hundreds of T, more than 1 billion PV dynamic page per day, There are also several major items that are not counted into the PV total by means of data interface calls (including a large project because the initial memcached was not deployed, resulting in a single database processing 90 million of queries per day). My overall database server load averaged around 0.5-1.

Generally speaking, MyISAM is suitable for:
(1) do a lot of count calculation;
(2) The insertion is not frequent, the query is very frequent;
(3) No business.

InnoDB suitable for :
(1) Reliability requirements are relatively high, or require business;
(2) Table updates and queries are fairly frequent, and the chances of table locking are relatively large when specifying the creation of the data engine
The switch that makes all the flexibility possible is the MySQL extension--type parameter that is provided to ANSI SQL. MySQL allows you to specify the database engine at the level of the table, so they sometimes refer to table formats. The following example code shows how to create a table that uses the MyISAM, ISAM, and heap engines, respectively. Note that the code to create each table is the same, except for the last type parameter, which is used to specify the data engine.

The following is the referenced content:

Copy the Code code 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 the MyISAM table to the InnoDB engine:

The following is the referenced content:

Copy the Code code as follows:
ALTER TABLE tblmyisam Change Type=innodb
MySQL uses three steps to achieve this goal. First, a copy of the table is created. Any changes to the input data are then queued, and the copy is moved to another engine. Finally, any data changes queued to the queue are sent to the new table, and the original table is deleted.
Copy the Code code as follows:
ALTER Table Shortcut
If you just want to update the table from ISAM to MyISAM, you can use the Mysql_convert_table_format command without having to write an 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 use to 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:

Copy the Code code as follows:
SHOW TABLE STATUS from Tblinnodb
you can use show CREATE table [TableName] to retrieve the information that show table can retrieve.
In General, MySQL will provide a variety of storage engines by default, which can be viewed through the following:
(1) See what your MySQL has now provided for storage engine: 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 the table uses (in the result of the parameter engine is the storage engine that the table is currently using): Mysql> Show create table table name;
Finally, if you want to use an engine that has not been compiled into MySQL and is not activated, it is useless, and MySQL does not prompt this. And it will only provide you with a default format (MyISAM) table. In addition to using the default table format, there are ways to get MySQL to give error hints, but for now, if you are not sure whether a particular database engine is available, you need to use Show table to check the table format.
More choices mean better performance
the engine for a particular table needs to be recompiled and traced, and given this extra complexity, why do you want to use a non-default database engine? The answer is simple: adjust the database to meet your requirements.
to be sure, MyISAM is fast, but if your logic design requires transactional processing, you are free to use the engine that supports transaction processing. Further, since MySQL allows you to apply the database engine at the table level, you can optimize performance only for tables that require transaction processing, and leave the tables that do not require transaction processing to the lighter MyISAM engine. For MySQL, flexibility is the key.

Performance testing
all performance tests are tested on computers with: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.
test Method: Submit 10 consecutive query, table records total: 380,000, time unit s
Engine Type MYISAMINNODB performance Difference
Count 0.00083573.01633609
Query primary key 0.005708 0.157427.57
querying non-primary keys 24.01 80.37 3.348
Update primary key 0.008124 0.8183100.7
Update non-primary key 0.004141 0.02625 6.338
inserting 0.004188 0.369488.21
(1) After the index, for the MyISAM query can be accelerated: 4 206.09733 times times, the InnoDB query 510.72921 times times faster, while the MyISAM update slowed down to the original 1/2,innodb update speed slowed to the original 1/30. To see if the situation determines whether to index, such as the log table without querying, do not do any index.
(2) If your data volume is millions, and there is no transaction processing, then using MyISAM is the best choice for performance.
(3) The size of the InnoDB table is more large, with MyISAM can save a lot of hard disk space.

In this 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 occupied 67,564 KB 80,048 KB

Another 176W record table, where the table occupies space, 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
MySQL is the official explanation for InnoDB: InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row level and also provides an Oracle-style, non-locking read in the SELECT statement, which adds multiuser deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
InnoDB is designed for maximum performance when dealing with huge amounts of data, and its CPU efficiency can be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
the InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on the InnoDB, and there are other sites that handle an average of 800 insertions/updates per second on InnoDB.

Original link: http://www.jb51.net/article/38004.htm

Go In-depth analysis of the MySQL Database engine introduction, differentiation, creation, and performance testing