MySQL (data engine of two)

What is the data engine

Some of the concepts related to databases were mentioned in the preliminary data: Database table records, etc.

===> files created in the Library ===> folder library in MySQL the files we use to store the data in real life should have different

Types: such as TXT type for saving text, save form with Excel, save image with PNG, etc.

The tables in the database should also have different types, different types of tables, different access mechanisms for MySQL, and table types, also known as storage engines

Types of data engines

The storage engine plainly is how to store the data, how to index the stored data, and how to update, query the data and other technologies to implement the
Method. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, storage and
Manipulate the type of this table)

Data engine supported by MySQL

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. and MySQL
the database provides a variety of storage engines. but only the InnoDB data engine is most prominent for MySQL

View MySQL supported storage Engine:mysql> show engines \g;

Mysql> Show Engines \g;
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| Engine | Support | Comment | Transactions | XA | savepoints |
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |
| Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM Storage Engine | NO | NO | NO |
| CSV | YES | CSV Storage Engine | NO | NO | NO |
| ARCHIVE | YES | Archive Storage Engine | NO | NO | NO |
| Performance_schema | YES | Performance Schema | NO | NO | NO |
| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+-------------- +------+-

As you can see from the table above, MySQL supports a number of data engines but the default data engine in MySQL is InnoDB

View the storage engine in use

Show variables like ' storage_engine% '; #查看正在使用的存储引擎

MySQL Data storage Engine Introduction

the #InnoDB storage engine supports transactions and is designed primarily for online transaction processing (OLTP) applications. It features a row lock design, supports foreign keys, and supports non-locking reads like Oracle, where the default read operation does not generate locks. From MySQL5.5.8The version starts as the default storage engine. The InnoDB storage engine places the data in a logical table space, which is managed by the InnoDB storage engine itself like a black box. From MySQL4.1(including4.1version, you can store the table for each InnoDB storage engine in a separate IBD file. In addition, the InnoDB storage engine supports the use of a bare device (row disk) to establish its tablespace. InnoDB achieves high concurrency by using multi-version concurrency control (MVCC), and implements the SQL standard4Isolation level, which defaults to the repeatable level and uses a strategy called Netx-key locking to avoid the creation of Phantom Read (Phantom) phenomena. In addition to this, the InnoDB storage engine also provides insert buffers, two writes (Doublewrite), Adaptive hash Indexes (Adaptive Hash index), pre-read (read ahead), and other high-performance and highly available features. For storage of data in tables, the InnoDB storage engine uses aggregation (clustered), each table is stored in the order of the primary key, and if no primary key is explicitly specified when the table is defined, the InnoDB storage engine generates one for each row6the ROWID of the byte and takes this as the primary key. The InnoDB storage engine is the most commonly used engine for MySQL databases, and successful applications from companies such as Facebook, Google, and Yahoo! have proven that the InnoDB storage engine is highly available, high performance, and highly scalable. The mastery and understanding of its underlying implementation also requires time and technology to accumulate. If you want to learn more about how the InnoDB storage engine works, implemented, and applied, you can refer to the "MySQL Insider: InnoDB Storage Engine" book. #MyISAM storage Engine does not support transactions, table lock design, full-text indexing, mainly for some OLAP database applications, in MySQL5.5.8Before the version is the default storage engine (except for the Windows version). A big difference between a database system and a file system is the support for transactions, which are not supported by the MyISAM storage engine. It is not difficult to understand the fundamental. Does the user need a transaction in all applications? In the Data warehouse, if there is no ETL these operations, simply through the report query also need transaction support?In addition, another differentiator for the MyISAM storage engine is that its buffer pool caches only (cache) index files, not data files, which are not the same as most databases. #NDB Storage Engine2003MySQL AB acquired the NDB storage engine from Sony Ericsson Corporation. The NDB storage engine is a clustered storage engine, similar to Oracle's RAC cluster, but unlike the share everything structure of the Oracle RAC, its structure is a cluster architecture that share nothing, thus providing a higher level of high availability. The NDB storage engine is characterized by the fact that the data is all in memory (from5.1version, the non-indexed data can be placed on disk, so the primary key lookup (primary key lookups) is extremely fast and can be added online NDB data node to improve database performance linearly. Thus, the NDB storage engine is a highly available, high-performance, highly scalable database cluster system, which is also a database application type for OLTP. #Memory Storage engine as its name, the data in the memory storage engine is stored in RAM, the database restarts or crashes, and the data in the table disappears. It is ideal for staging tables that store temporary data in an OLTP database application or as a dimension table for the Data warehouse in an OLAP database application. The memory storage engine uses a hash index by default instead of the usual familiar B+Tree Index. #Infobright Storage Engine third-party storage engine. It is characterized by columns rather than rows, making it ideal for OLAP database applications. Its official website is http://www.infobright.org/, a number of successful data warehousing cases are available for analysis. #NTSE Storage Engine NetEase has developed a storage engine for its internal use. The current version does not support transactions, but provides features such as compression, row-level caching, and, in the near future, memory-oriented transaction support. #BLACKHOLE黑洞存储引擎 that can be applied to the distribution Master library in primary and standby replication. The MySQL database also has many other storage engines, which are just some of the most commonly used engines. If you like, you can write your own engine, this is the ability of open source to us, but also the charm of open source. 

MySQL Data Engine introduction

Use of the data engine

Method one specifies when creating a table:

int Char) engine=int) engine=innodb;show CREATE table innodb_t1;show create table innodb_t2;

Method two specify the default storage engine in the configuration file

/etc/my.cnf[mysqld]default-storage-engine=innodbinnodb_file_per_table=1

View Code

View

[Email protected] db1]# CD/var/lib/mysql/db1/[[email protected] db1]# lsdb.opt  innodb_t1.frm  INNODB_T1.IBD  innodb_t2.frm  innodb_t2.ibd

View Code

Data Engine related exercises

int) Engine=InnoDB; MariaDB [DB1]int) engine=MyISAM; MariaDB [DB1]int) engine=memory; MariaDB [DB1]int) engine=blackhole; MariaDB [DB1]>/var/lib/mysql/db1/ #发现后两种存储引擎只有表结构, no data db.opt  t1.frm  T1.IBD  T2. MYD  T2. MYI  t2.frm  t3.frm  t4.frm#memory, after restarting MySQL or restarting the machine, the data in the table is emptied #blackhole, inserting any data into the table is equivalent to throwing into a black hole, the table will never save records 

View Code

MySQL (data engine of two)