Mysql-storage engine, mysql-engine

Mysql-storage engine, mysql-engine

Storage engine: Also called table type, it refers to the data table storage mechanism, index scheme and other supporting functions. Different engines may bring about different functions or optimization due to different processing methods. Select a proper engine based on actual needs.

Select the standard storage engine for the table:
Whether transaction support is required.
Whether foreign keys are supported.
Concurrency requirements.
Troubleshooting of error recovery.
Whether certain functions are supported.

The most common storage engine:
MyISAM, Indexed Sequential Access Method (Sequential Access Method with indexes)
InnoDB
Differences:
Data and indexes are stored in different files: MyISAM is saved separately, while innodb is saved to the tablespace.

MyISAM supports index compression, while Innodb indexes and data are bound and saved without compression, which is large in size.

In many cases, Innodb uses row-level locks, while myisam uses table-level locks. innodb has a high concurrency.

InnoDB does not support FULLTEXT indexes (the new InnoDB version also supports indexes ).

Innodb supports transactions, foreign keys, and strong data integrity constraints. MYISAM does not.

InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

 

The default storage engine of mysql is INNODB.
You can find it in the mysql my. ini (Path:... \ MySQL \ my. ini) configuration file, for example:

 

You can specify the storage engine of a table when creating or editing a table.
Using table attributes: engine type
Alter table tb_name engine type

 

 

 

Specify the storage engine when creating a table:
 

Differences between innoDB and myisam:

Different file storage methods
Myisam: one table, three files
Tb_name.frm table structure file
Tb_name.MYD data file (storage engine)
Tb_name.MYI Index

INNODB a table and a file
Tb_name.frm

All innoDB tables use the same innoDB bucket to store data and indexes (... \ MySQL \ data \ ibdata1)
 

As the database grows, there may be more than one ibdata1 file in innodb under the data directory, but the table is not distinguished.

In addition, when installing the mysql file, the installation path of the tablespace file is also prompted during mysql configuration.

 

 

Briefly describe several MYSQL database storage engines and briefly describe their features

MySQL has multiple storage engines. Each storage engine has its own advantages and disadvantages. You can select the preferred one:
MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, and BLACKHOLE.
MySQL supports several storage engines as processors for different table types. The MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables:
· MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default.
· The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default.
Note: The MEMORY storage engine is officially identified as the HEAP engine.
· InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like.
· The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers.
· NDB Cluster is a storage engine used by MySQL Cluster to split tables into multiple computers. It is provided in MySQL-Max 5.1 binary distribution. Currently, this storage engine is only supported by Linux, Solaris, and Mac OS X. In the future MySQL distribution version, we want to add support for this engine from other platforms, including Windows.
· The ARCHIVE storage engine is used to cover a large amount of data stored without indexing.
· The CSV storage engine stores data in text files in comma-separated formats.
· The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.
· FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it only works with MySQL and uses the MySQL C Client API. In future distributions, we want to connect it to another data source using another drive or client connection method.
MyISAM and InnoBD are commonly used.
 
MYSQL storage engine problems

View default and supported storage engines
Mysql> show engines;

View the storage engine of a table
Mysql> show create table users;

To modify the storage engine, you must specify the storage engine when creating a table or modify it later.
Create table mytable (id int, titlechar (20) ENGINE = INNODB

Alter table engineTest ENGINE = INNODB;

The default storage engine cannot be modified in real time (at least I have not found any modification method)