In-depth analysis of MySQL engine

MySQL 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. Several database engines are 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 shortcomings of ISAM are that it does not support the processing of things 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 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 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 is not saved until it is shut down, so all data is 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 the direct product of the technology that makes MySQL flexible, the technology that is the mysql++ API. every challenge you face when using MySQL comes from the ISAM and MyISAM data engines do not support the handling of things or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for handling things 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.

The official MySQL InnoDB explains this :InnoDB provides MySQL with a secure storage engine for things that commit, rollback, and crash resilience. 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.

Generally speaking:

MYISAM Suitable for: (1) do a lot of count calculation, (2) Insert infrequently, query very frequently; (3) No things.

InnoDB Suitable for: (1) High reliability requirements, or requirements, (2) The table updates and queries are quite frequent, and the table is more likely to lock the situation.

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 to offer now what storage engine:mysql> show engines;

(2) View your mSQL 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;

In-depth analysis of MySQL engine