Shulou(Shulou.com)06/01 Report--

Storage Engine refers to the table type, that is, the storage engine it uses when the table is created. Different tables in the same library can use different storage engines, but it is recommended to use the same storage engine.

MySQL architecture

Storage engine

InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and later.

Storage engine comparison: https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

MyISAM storage engine

MyISAM engine features:

Transactions are not supported

Table level locking

Read and write block each other, write cannot be read, and cannot be written when reading

Cache index only

Foreign key constraints are not supported

Clustering index is not supported

Read data faster and take up less resources

Does not support MVCC (multi-version concurrency control mechanism) high concurrency

The collapse recovery is poor.

Default database engine before MySQL5.5.5

Applicable scenarios: read-only (or write less), small table (can accept repair operation for a long time)

MyISAM engine files:

Tbl_name.frm: table format definition

Tbl_name.MYD: data fil

Tbl_name.MYI: index fil

InnoDB storage engine

InnoDB engine features:

Support transactions, suitable for handling a large number of short-term transactions

Row level lock

Read-write blocking is related to transaction isolation level

Cacheable data and index

Support for clustered index

Crash recovery is better.

Support for MVCC high concurrency

Full-text indexing is supported after MySQL5.5

Starting with MySQL5.5.5 as the default database engine

InnoDB database file

The data and indexes of all InnoDB tables are placed in the same tablespace

Tablespace files: under the directory defined by datadir

Data files: ibddata1, ibddata2,...

Each table uses a separate table space to store the data and indexes of the table

Enable: innodb_file_per_table=ON

Two types of files are placed in a separate directory of the database

Data files (storing data and indexes): tb_name.ibd

Table format definition: tb_name.frm

Other storage engines

Performance_Schema:Performance_Schema database

Memory: store all data in RAM for quick access in environments where you need to quickly find references and other similar data. Suitable for storing temporary data. The engine used to be called the HEAP engine

MRG_MyISAM: enables MySQL DBA or developers to logically group a series of identical MyISAM tables and reference them as an object. Suitable for VLDB (Very Large Data Base) environments such as data warehouses

Archive: supports only SELECT and INSERT operations for storing and retrieving a large number of rarely referenced archived or security audit information; supports row-level locks and dedicated caches

Federated federation: an agent for accessing other remote MySQL servers, which creates a client connection to a remote MySQL server, transfers the query to the remote server for execution, and then completes data access, providing the ability to link to a single MySQL server to create a logical database from multiple physical servers. Ideal for distributed or data Mart environments

BDB: a transaction engine that replaces InnoDB and supports COMMIT, ROLLBACK, and other transaction features

Cluster/NDB:MySQL 's clustered database engine is especially suitable for applications with high-performance lookup requirements, which also require the highest uptime and availability

The CSV:CSV storage engine stores data in a text file using a comma-separated value format. You can use the CSV engine to import and export data exchange between other software and applications in CSV format

BLACKHOLE: the black hole storage engine accepts but does not store data, and retrieval always returns an empty set. This function can be used for distributed database design, automatic data replication, but not local storage (can be used as an intermediate library between master and slave in MySQL master-slave replication)

Example: the "stub" engine, which does nothing. You can use this engine to create tables, but you cannot store or retrieve data in them. The goal is to illustrate how to start writing a new storage engine as an example

Other storage engines supported by MariaDB:

OQGraph

SphinxSE

TokuDB

Cassandra

CONNECT

SQUENCE

Manage Storage Engin

View the storage engines supported by mysql:

Show engines

View the current default storage engine:

Show variables like'% storage_engine%'

Set the default storage engine:

Vim / etc/my.conf

[mysqld]

Default_storage_engine= InnoDB

View the storage engine used by all tables in the library

Show table status from db_name

View the storage engine for the specified table in the library

Show table status like 'tb_name'

Show create table tb_name

Set up the storage engine for the table:

CREATE TABLE tb_name (...) ENGINE=InnoDB

ALTER TABLE tb_name ENGINE=InnoDB

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.