In-depth analysis based on Mysql storage engine

MySQL has many storage engines. for different applications, you can select an appropriate storage engine for each table, which helps improve MySQL performance.
Create a news table:

Copy codeThe Code is as follows: create table 'sandbox'. 'News'
(
'Id' int not null AUTO_INCREMENT,
'Name' VARCHAR (45) NULL,
'Content' VARCHAR (45) NULL,
'Created 'VARCHAR (45) NULL,
Primary key ('id ')
) ENGINE = MyISAM;

Note:MySQL is case sensitive and is determined by the platform you use. In Windows, Mysql is case insensitive. in Linux, MySQL is case sensitive.
The last SQL statement ENGINE = "storage ENGINE name" is to select a storage ENGINE for the table. MySQL allows us to select different storage engines when creating a table, what storage engines does MySQL have?

MySQL storage engines include: MyISAM engine, MyISAM Merge engine, InnoDB engine, Memory engine, Archive engine, CSV engine, Federated engine, Blackhle engine, NDB Cluster engine, Falcon engine, and SolidDB engine, PBXT engine,
Maria engine and other engines. It is important to select an appropriate storage engine when creating a table. It will be troublesome to change the storage engine later.Here, we will only introduce three common storage engines:

MyISAM Engine
MyISAM engine is the default storage engine of MySQL. MyISAM does not support transactions and row-level locks. Therefore, MyISAM engine is fast and has excellent performance. MyISAM can lock the entire table, support Concurrent Insertion, and support full-text indexing.
If you do not need transaction support, we usually use the MyISAM storage engine when creating tables, and there is no need to support transactions like news tables.

InnoDB Engine
InnoDB is a storage engine designed for transactions. It supports transactions, foreign keys, and high concurrency processing capabilities. However, InnoDB is slower than MyISAM when creating indexes and loading data.
Transactions are generally required for currency operations. Everything can be wrong and money cannot be wrong.

Memory Engine
Memory tables, the Memory engine stores data in the Memory, the table structure is not stored in the Memory, the query does not need to perform I/O operations, so it is much faster than MyISAM and InnoDB, however, after the database is powered off or restarted, the data in the table will be lost, and the table structure will not
Lost. If you need to store SESSION data in the database, using the Memory engine is a good choice.
Generally, we can use these three storage engines. For more information, see the MySQL manual.