Mysql Database Engine Introduction, differentiation, creation and in-depth analysis of performance testing _mysql

Introduction to the 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. The other two types of InnoDB and Berkley (BDB) are also often available. If the technology is superb, you can also 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, designed to take into account that the number of times a database is queried is much larger than the number of updates. So ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two major drawbacks of ISAM are that it does not support transaction processing and is not capable of 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 MySQL can support such a backup application by replicating the features.
MyISAM: MyISAM is the ISAM extended format and default database engine for MySQL.In addition to providing a wide range of indexes and field management features not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations, at the expense of frequently running optimize table commands to restore the space wasted by the updated mechanism。 MyISAM also has some useful extensions, such as MYISAMCHK tools for repairing database files and Myisampack tools to restore wasted space.MyISAM emphasizes fast read operations, this may be the main reason why MySQL is so favored by Web development: The vast amount of data you do in Web development is read. SoMost virtual host providers and internet platform providers only allow the use of the MyISAM format。An important flaw in the MyISAM format is that data cannot be recovered after the table is corrupted.
HEAP：Heap allows temporary tables that reside only in memory. resides in memory to make heap faster than ISAM and MyISAM, but the data it manages is unstable, and if not saved before shutdown, all data will be lost。 When data rows are deleted, heap does not waste a lot of space. Heap tables are useful when you need to select and manipulate data using a select expression.Remember to delete a table after you finish using it。
InnoDB: The InnoDB database engine is a direct product of the technology that creates MySQL flexibility, and this technology is MYSQL+API.When using MySQL, almost every challenge you face stems from the ISAM and MyISAM database engine does not support transaction processing (transaction process) or foreign keys. Although it is much slower than the ISAM and MyISAM engines, InnoDB includes support for transaction processing and foreign keys, which are not in the top two engines.As mentioned earlier, if your design requires one or both of these features, you will be forced to use one of the last two engines.
If you feel you are really skilled, you can use MYSQL+API to create your own database engine. This API provides you with the ability to manipulate fields, records, tables, databases, connections, security accounts, and all the myriad other features needed to build a DBMS such as MySQL. Delving into the API is beyond the scope of this article, but it is important to understand the MYSQL+API and the technology behind the interchangeable engine. Estimating the plug-in database engine's model can even be used to create a local XML provider (XML provider) for MySQL. (Any MYSQL+API developer who reads this article can see this as a requirement.) ）
the difference between MyISAM and InnoDB
InnoDB and MyISAM are the two most commonly used table types used by many people when using MySQL, each with its own pros and cons, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys.

The following are some details and specific implementations of the differences:
The fulltext type index is not supported for 1.InnoDB.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) fromtable is performed, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.
4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.
5.LOAD Table Frommaster operation does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.

In addition, row locks on the InnoDB table are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, 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 with foreign keys and row-level locks. And MyISAM is not supported. So MyISAM are often considered only suitable for use in small projects.
I use MySQL as a user perspective, InnoDB and MyISAM are more like, but from my current operation of the database platform to achieve demand: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

The reasons are as follows:
1, first of all, I currently hosted on the platform most of the project is read more write less items, and MyISAM read performance is much stronger than the InnoDB.
2, MyISAM index and data are separate, and the index is compressed, memory usage rate on the corresponding increase a lot. Can load more indexes, while InnoDB is tightly bundled with the index and data, no compression is used to create innodb larger than MyISAM.
3, from the platform point of view, often 1, 2 months will occur application developers inadvertently update a table where the scope is not correct, resulting in the table can not be used normally, this time the superiority of the MyISAM is reflected, casually from the day copy of the compressed package to remove the corresponding table files, Casually put in a database directory, and then dump into SQL and then guide back to the main library, and the corresponding binlog to fill up. If it's InnoDB, I'm afraid it can't be that fast, don't talk to me. Let InnoDB periodically back up with the export xxx.sql mechanism, because the minimum amount of data for one of the smallest database instances on my platform is dozens of g in size.
4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably accounted for the entire SQL total statement more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is row-level lock, That's just where the primary key is valid and the Non-key key will lock the entire table.
5, there is often a lot of application departments need me to give them regular data on certain tables, MyISAM words is very convenient, as long as they correspond to the table frm. myd,myi files, let themselves in the corresponding version of the database to start on the line, and InnoDB need to export the xxx.sql, because the light to others file, by the dictionary data file, the other side is not available.
6, if and myisam than insert write operation, InnoDB also can not reach the MyISAM write performance, if it is for indexing update operation, although MyISAM may be inferior innodb, but so high concurrent write, from the library can chase is also a problem, It is better to solve this problem by using a multi-instance sub-table architecture.
7, if the use of MyISAM, the merge engine can greatly accelerate the development of the application department, they only do some selectcount (*) operation of the merge table, very suitable for a large project volume of about hundreds of millions of of the rows of a certain type (such as log, survey statistics) business table.
Of course, InnoDB is not absolutely not, with transactions such as simulation of the stock project, I was using InnoDB, active users more than 200,000 times, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I still head myisam.
In addition, some people may say that you myisam can not resist too much write, but I can make up by the architecture, say a database platform that I currently useCapacity:Master and subordinate data total in hundreds of t above, more than 1 billion PV dynamic pages per day, there are several major projects through the data interface to call the total number of PV, (including a large project because the initial memcached did not deploy, resulting in a single database processing 90 million of the query every day). My overall database server load averaged around 0.5-1.

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

InnoDB Suitable for:
(1) High reliability requirements, or require business;
(2) Table updates and queries are quite frequent, and table locking opportunities are relatively large specifies the creation of the data engine
The switch that makes all the flexibility possible is the MySQL extended--type parameter provided to ANSI SQL. MySQL allows you to specify the database engine at this 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 separately. Note that the code to create each table is the same, except for the last type parameter, which specifies the data engine.

The following are the referenced contents:

Copy 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 existing form 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 are the referenced contents:

Copy Code code as follows:

ALTER TABLE Tblmyisam Change Type=innodb

MySQL uses three steps to achieve this. First, a copy of this table is created. Any changes to the input data are then queued, and the replica is moved to another engine. Finally, any changes to the queued data are sent to the new table, and the original table is deleted.

Copy Code code as follows:

ALTER Table Shortcuts

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 ALTER TABLE expressions.

You can use the Show Table command (which is another extension of MySQL's 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 example code illustrates the use of Show table:

Copy Code code as follows:

Show TABLE STATUS from Tblinnodb

You can use show CREATE table [TableName] to retrieve information that the 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 now offers storage engine: Mysql> show engines;
(2) See your MySQL current default storage engine: Mysql> show variables like '%storage_engine% ';
(3) You want to see what engine a table uses (after the parameter engine in the display result indicates the storage engine that the table is currently using): Mysql> show create table name;
Finally, if you want to use an engine that has not been compiled into MySQL and is not activated, it is no use, and MySQL will not prompt this. And it will only give you a table with a default format (MyISAM). In addition to using the default table format, there is a way to get MySQL to give you the wrong hints, but for now, if you are not sure whether a particular database engine is available, you should use Show table to check the table format.
More choices means better performance
The engine for a particular table needs to be recompiled and tracked, and given this additional 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 really fast, but if your logic design requires transactions, 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 transactions, and give tables that do not require transactions to a lighter MyISAM engine. For MySQL, flexibility is the key.

Performance test
All performance tests are tested on computers in: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.
Test method: Submit 10 query consecutively, total table record: 380,000, time unit s
Engine Type MYISAMINNODB Performance difference
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.369488.21
(1) After adding the index, for MyISAM query can be accelerated: 4 206.09733 times times, the InnoDB query speed up 510.72921 times times, while the MyISAM update speed to the original 1/2,innodb update speed slowed to the original 1/30. It depends on the situation to decide whether to add the index, such as not query the log table, do not do any index.
(2) If your data volume is millions other, and does not have any transaction processing, then uses MyISAM is the performance best choice.
(3) InnoDB table size is larger, 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

Occupy Total space 67,564 kb 80,048 kb

Another 176W-million-logged table that takes up space as follows:
Engine type myisam   innordb
data   56,166 kb   90,736 KB
Index   67,103 kb   88,848 KB

Occupy total space   123,269 kb179,584 KB

other
     MySQL The official explanation for InnoDB is this: InnoDB provides MySQL with transaction security (acid-compatible) storage engine with Commit, rollback, and crash recovery capabilities. InnoDB locks the row level and also provides an Oracle-style, non-lock read in the SELECT statement, which adds to multi-user deployment and performance. There is no need to enlarge the lock in InnoDB because the InnoDB row-level locking fits very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you can freely mix innodb types of tables with other types of MySQL tables, even in the same query.
   &NBSP InnoDB is the maximum performance design for processing large amounts of data, and its CPU efficiency may be unmatched by any other disk-based relational database engine.
   &NBSP The InnoDB storage engine is fully consolidated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in a tablespace, a tablespace can contain several files (or a raw disk partition). This is different from the MyISAM table, for example in the MyISAM table where each table is separated from the file. The InnoDB table can be any size, even on an operating system that has a file size limited to 2GB.
  &NBSP  innodb is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB the default table for MySQL on Windows. The
     innodb is used in many large database sites that require high performance. The famous Internet news site slashdot.org runs on the InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 inserts/updates per second on the InnoDB.