Python full stack-database storage engine

I. Overview of storage engines

In personal pc, different file types have different processing mechanisms to access from, such as text with TXT open, save, table with Excel reading, writing and so on. In the database, there are also many types of tables, so the database operating system should also have access to various tables to implement the operation of the data, and the table type is called the storage engine .

Simply put, the storage engine is the implementation method that makes the database store data, index, update data and query. In a relational database, the storage of data is stored as a table, so the storage engine can also be called a table type, which is the type that stores and operates this table.

In databases such as Oracle, there is only one storage engine, so the data storage management mechanism is the same, MySQL provides a variety of storage engines, users can choose different storage engine for the data table according to different requirements, but also can use their own development of the storage engine.

Components such as SQL parser, SQL optimizer, buffer pool, storage engine, and so on are present in each database, but not every database has so many storage engines. MySQL's plug-in storage engine allows developers of the storage engine layer to design the storage tier they want, for example, some applications need to meet the requirements of the transaction, some applications do not need to have such strong requirements for the transaction, some want the data to be persisted, and some only want to put in memory, temporarily and quickly to provide data query.

Second, view the supported storage engine under MySQL

View the supported search engines
Mysql>show engines;+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+----------------------------------------------------------------+--- -----------+------+------------+| Federated | NO | Federated MySQL Storage Engine | NULL | NULL | NULL | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES |                         CSV Storage Engine                    | NO | NO | NO | | MEMORY | YES |  Hash based, storedinchMemory, useful forTemporary Tables | NO | NO | NO | | ARCHIVE | YES | Archive Storage Engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, andForeign keys | YES | YES | YES | | Performance_schema | YES | Performance Schema | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------+----------- ---+------+------------+9 rowsinchSet (0.00 sec)

To view the search engines currently in use:

Mysql> Show variables like ' storage_engine% ';
+----------------+--------+
| variable_name | Value |
+----------------+--------+
| Storage_engine | InnoDB |
+----------------+--------+
1 row in Set (0.00 sec)

MySQL Storage engine Introduction

#InnoDB Storage Enginesupport transactions, whose design goals are 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 (including 4.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 standard4 isolation levels, default to the repeatable level, and a use of a type called netx-key locking strategy to avoid the creation of Phantom Reading (phantom) phenomenon. In addition to this, the InnoDB storage engine provides high performance and high-availability features such as insert buffer, two write (double write), Adaptive Hash Index (Adaptive hash indexes), pre-read (read ahead), and more. 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 EngineDoes not support transaction, table lock design, support full-text indexing, mainly for some OLAP database applications, in MySQL 5.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 to have a transaction in all applications? In the Data warehouse, if there is no ETL these operations, but simply through the report query also need transaction support? In addition, the MyISAM storage engine is unique in that its buffer pool caches only (cache) index files, Instead of caching data files, this is not the same as most databases. #NDB Storage EngineMySQL 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 (from 5.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 EngineAs its name holds, 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 normally familiar B +Tree Index. #infobright Storage EngineThird-party storage engines. It is characterized by columns rather than rows, making it ideal for OLAP database applications. Its official website is http://www.infobright.org/, there are a number of successful data warehousing cases to be analyzed. #Ntse Storage EngineNetEase 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. #blackholea black hole storage engine 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. 

Iii. use of the storage engine

Method One: Specify when adding a table

Mysql> CREATE TABLE tb1 (ID int,name char (+)) engine = InnoDB;

Method Two: Configure the default storage engine in the configuration file

Python full stack-database storage engine