1, Foreword
Storage engine is a special name for MySQL. Oracle, the big brother in the database industry, SQL Server, PostgreSQL and so on do not have a storage engine.
An important feature that distinguishes MySQL from other databases is its pluggable table storage engine.
Engine is a foreign transliterated word, which is traditionally regarded as an engine. It seems that it has nothing to do with the database.
The earliest MySQL storage engine was called "data table processor", which may sound too old-fashioned. Later, it was changed to a tall storage engine.
The function of the storage engine is to receive the instructions from the upper layer, and then read or write the data in the table. Remind me that the storage engine operates on tables, not databases.
MySQL5. Since version 5, InnoDB has been adopted as the default storage engine, and the default storage engine in previous versions is MyISAM.
Let's take a look at MySQL 8 Which storage engines are supported by 0:
mysql> showengines; +--------------------+---------+---------+--------------+------+------------+ | Engine | Support | Comment | Transactions| XA | Savepoints | +--------------------+---------+---------+--------------+------+------------+ | FEDERATED | NO | | NULL | NULL | NULL | | MEMORY | YES | | NO | NO | NO | | InnoDB | DEFAULT | | YES | YES | YES | |PERFORMANCE_SCHEMA | YES | | NO | NO | NO | | MyISAM | YES | ... | NO | NO | NO | | MRG_MYISAM | YES | | NO | NO | NO | | BLACKHOLE | YES | | NO | NO | NO | | CSV | YES | | NO | NO | NO | | ARCHIVE | YES | | NO | NO | NO | +--------------------+---------+---------+--------------+------+------------+ 9 rows in set (0.00sec)
You can see mysql8 0 supports 9 storage engines. InnoDB is used by default, and only InnoDB supports transactions and distributed transactions (XA). Savepoints are the functions required for transaction rollback.
Kunlun distributed database realizes the function of distributed database cluster and uses InnoDB storage engine.
2, Features of various storage engines
2.1 Federated
Federated storage engine provides a way to access the tables on the remote MySQL database server. The data is not stored locally. All the data is placed on the remote server. The structure of the table and the connection information of the remote server need to be saved locally.
2.2 Memory
Memory storage engine, also known as HEAP storage engine, saves data in memory and table structure on disk.
If the database restarts or crashes, the data in the table will disappear. Ideal for temporary tables that store temporary data. Its data is only stored in memory. Of course, reading and writing is very fast, but memory consumption should be considered when using it.
2.3 Performance_schema
Performance_schema storage engine is a special engine for MySQL database system. Users cannot create tables of this storage engine.
System default database performance_ This storage engine is used for tables in schema. The database performance schema is used to monitor the resource consumption and resource waiting of MySQL during a lower level operation.
2.4 Blackhole
Blackhole storage engine, which acts as a "black hole", accepts data but throws it away. It does not store data, which is similar to the / dev/null file in Linux system.
The main functions of such a special black hole storage engine are: relay or filter the Replication scenario, verify the syntax of dump files, measure the additional overhead caused by opening binlog logs, and find other performance bottlenecks unrelated to the storage engine.
2.5 CSV
The CSV storage engine will generate a database with the same name as the database in the MySQL installation directory data folder CSV file, which can treat CSV files as tables. Compared with the file contents of other storage engines, it can be viewed and edited directly.
2.6 Archive
The archive storage engine only supports two basic functions: insert and select. Archive has a good compression mechanism, which saves more storage space than MyISAM and InnoDB storage engines.
It can be used for logging, clock in recording, weather information recording and other scenes that do not need data update.
2.7 MyISAM
MyISAM storage engine is the default storage engine in early MySQL. It has high insertion and query speed, table lock design, and supports full-text indexing, but does not support transactions and foreign keys.
If the table is mainly used to insert new records and read records, selecting MyISAM can achieve high processing efficiency.
2.8 MRG_MyISAM
MRG_MyISAM storage engine is a combination of a group of MyISAM, that is, it aggregates multiple tables of MyISAM engine, but there is no data in it. The real data is still in the tables of MyISAM engine, but it can be queried, deleted and updated directly.
2.9 InnoDB
InnoDB storage engine is the default storage engine in the current version of MySQL. It supports transaction security table (ACID), row locking and foreign keys.
Because it supports transaction processing, foreign keys, crash repair capability and concurrency control. If you need a database with high requirements for transaction integrity, concurrency control and frequent update and deletion operations, InnoDB has great advantages.
3, Comparison of differences between myinnodb and MyISAM under different scenarios
Test environment, centos8 virtual machine of mechanical hard disk, MySQL latest version 8.26, mariadb client link library. Create two tables whose storage engines are MyISAM and InnoDB:
create table tb_myisam( id integer primarykey, value integer) engine=myisam; create table tb_innodb( id integer primarykey, value integer) engine=innodb;
3.1 insert comparison
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "insert into tb_xxx(id,value)values(%d,%d)", i, i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);The time consumption is 15 seconds (MyISAM) and 39 seconds (InnoDB).
3.2 query and comparison of common key values
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000;i++)
{
sprintf(buf,"select * from tb_xxx where value=%d", i);
mysql_real_query(&conn,buf, strlen(buf));
MYSQL_RES*mysql_res = mysql_store_result(&conn);
MYSQL_ROW row =mysql_fetch_row(mysql_res);
mysql_free_result(mysql_res);
}
clock_gettime(CLOCK_REALTIME,&ts_end);The time consumption is 140 seconds (MyISAM) and 23 seconds (InnoDB).
3.3 primary key index query comparison
Change the query statement in the previous step to query by primary key index
sprintf(buf, "select * from tb_xxx where id=%d", i);
The time consumption is 1.25 seconds (MyISAM) and 1.30 seconds (InnoDB).
3.4 update comparison
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "update tb_xxx setvalue=%d where id=%d", i+1, i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);The time consumption is 15 seconds (MyISAM) and 44 seconds (InnoDB).
3.5 delete comparison
clock_gettime(CLOCK_REALTIME,&ts_start);
for(int i=1; i<=10000; i++)
{
sprintf(buf, "delete from tb_xxxwhere id=%d", i);
mysql_real_query(&conn, buf,strlen(buf));
}
clock_gettime(CLOCK_REALTIME, &ts_end);Time consuming: 14 seconds (MyISAM) and 45 seconds (InnoDB).
According to the comprehensive test results, the general query speed of MyISAM is much slower than that of InnoDB, and the performance of other tests is better than that of InnoDB. Of course, this is a test without transaction. The test example of the author is relatively simple, which can only be used as a reference and cannot represent the actual application scenario.
KunlunDB project is open source
[GitHub: ]
https://github.com/zettadb
[Gitee: ]
https://gitee.com/zettadb
END