The duration of this section needs to be controlled 10 Within minutes
mysql The library built in ===> Folder
Tables created in the library ===> file
In real life, there are different types of files that we use to store data , Each file type has its own processing mechanism ： For example, processing text with txt type , Processing forms with excel, Processing pictures with png etc.
There should also be different types of tables in the database , The types of tables are different , Will be corresponding mysql Different access mechanisms , Table types are also called storage engines .
The storage engine is how to store data 、 How to index and update stored data 、 Query data and other technologies
Law . Because in a relational database, data is stored in the form of tables , So a storage engine can also be called a table type （ That is, storage and
Operate on the type of this table ）
stay Oracle and SQL Server There is only one storage engine in the database , All data storage management mechanisms are the same . and MySql
The database provides a variety of storage engines . Users can choose different storage engines for data tables according to different requirements , Users can also
I need to write my own storage engine
SQL Parser 、SQL Optimizer 、 Buffer pool 、 Components such as the storage engine exist in every database , But not every Each database has so many storage engines .MySQL The plug-in storage engine can let the developers of the storage engine layer set Count the storage tiers they want , for example , Some applications need to meet the requirements of transactions , Some applications do not need to have this... For transactions Such a strong demand ; Some hope that data can be stored permanently , Some just want to put it in memory , Temporarily and quickly provide access to data Query for .
MariaDB [(none)]> show engines\G # See all the supported storage engines
MariaDB [(none)]> show variables like 'storage_engine%'; # Look at the storage engine in use
1、InnoDB Storage engine
Support transactions , Its design goal is mainly for online transaction processing (OLTP) Application . Its
It features row lock design 、 Support foreign keys , And support similar Oracle Non locked reading , That is, the default read operation will not generate locks . from MySQL 5.5.8 Version starts as the default storage engine .
InnoDB The storage engine places the data in a logical table space , This table space is like a black box InnoDB The storage engine itself manages . from MySQL 4.1( Include 4.1) Version start , Each InnoDB Storage engine The form is stored in a separate ibd In file . Besides ,InnoDB Storage engine support will be raw device (row disk) use To create its tablespace .
InnoDB By using multi version concurrency control (MVCC) To achieve high concurrency , And implemented SQL standard Of 4 Kind of isolation level , The default is REPEATABLE Level , Use one at the same time called netx-key locking The strategy to Avoid unreal reading (phantom) The emergence of phenomena . besides ,InnoDB The storage engine also provides insert buffering (insert buffer)、 The two time to write (double write)、 adaptive hash index (adaptive hash index)、 read-ahead (read ahead) And other high-performance and highly available functions .
For the storage of data in the table ,InnoDB The storage engine uses aggregation (clustered) The way , Every watch presses The primary key is stored in the order of , If the primary key is not explicitly specified when the table is defined ,InnoDB The storage engine will be for each Line generates a 6 Bytes of ROWID, And use this as the primary key .
InnoDB The storage engine is MySQL Database is the most commonly used engine ,Facebook、Google、Yahoo etc. The successful application of the company has proved InnoDB The storage engine has high availability 、 High performance and high scalability . For its It takes time and technology to master and understand the underlying implementation . If you want to know more about InnoDB Storage engine work principle 、 Implementation and Application , You can refer to 《MySQL Technology insider :InnoDB Storage engine 》 A Book .
2、MyISAM Storage engine
Unsupported transaction 、 Watch lock design 、 Full text index support , Mainly for OLAP Count Database application , stay MySQL 5.5.8 Before version, it was the default storage engine ( except Windows Out of version ). Database system A big difference from file systems is the support for transactions ,MyISAM The storage engine does not support transactions . The root is Ben , It's not hard to understand . Do users need transactions in all applications ? In the data warehouse , without ETL These operations , Do you need transaction support to simply query through reports ? Besides ,MyISAM Storage engine Another difference is , Its buffer pool only caches (cache) Index file , Instead of caching data files , This is related to Most databases are different .
3、NDB Storage engine
year ,MySQL AB Company from Sony Ericsson The company acquired NDB Storage engine . NDB The storage engine is a cluster storage engine , Be similar to Oracle Of RAC colony , But with the Oracle RAC Of share everything The difference in structure is , Its structure is share nothing Cluster architecture of , So it can provide a higher level of High availability .NDB The characteristic of storage engine is that all data is in memory ( from 5.1 Version start , You can put a non index number It's on disk ), So primary key lookup (primary key lookups) Very fast , And can add... Online NDB Data storage nodes (data node) In order to improve the database performance linearly . thus it can be seen ,NDB The storage engine is highly available 、 High performance 、 Highly scalable database cluster system , It's also about OLTP Database application type .
4、Memory Storage engine
As its name suggests ,Memory The data in the storage engine is stored in memory , The database is heavy Start or break down , The data in the table will disappear . It's great for storage OLTP A temporary table of temporary data in a database application , It can also be used as OLAP Dimension table of data warehouse in database application .Memory The storage engine uses hash by default Indexes , Rather than the familiar B+ Tree index .
5、Infobright Storage engine
Third party storage engines . It's characterized by storage by columns, not rows , So it's very fit OLAP Database application . Its official website is http://www.infobright.org/, There's a lot of success data Warehouse cases are available for analysis .
6、NTSE Storage engine
Netease's internal storage engine . The current version does not support transactions , But provide compression 、 Line level caching and other features , Memory oriented transaction support will be implemented in the near future .
7、BLACKHOLE
Black hole storage engine , It can be applied to the distribution master database in the active / standby replication .
MySQL There are many other storage engines in the database , These are just some of the most commonly used engines . If You like it , You can write your own engine , That's what open source gives us , It's also the charm of open source Where the force lies .
Method 1： When creating a table, specify
MariaDB [db1]> create table innodb_t1(id int,name char)engine=innodb;
MariaDB [db1]> create table innodb_t2(id int)engine=innodb;
MariaDB [db1]> show create table innodb_t1;
MariaDB [db1]> show create table innodb_t2;
Method 2： Specify the default storage engine in the configuration file
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1
see
[[email protected] db1]# cd /var/lib/mysql/db1/
[[email protected] db1]# ls
db.opt innodb_t1.frm innodb_t1.ibd innodb_t2.frm innodb_t2.ibd
practice
Create four tables , Separate use innodb,myisam,memory,blackhole Storage engine , Do insert data test
MariaDB [db1]> create table t1(id int)engine=innodb;
MariaDB [db1]> create table t2(id int)engine=myisam;
MariaDB [db1]> create table t3(id int)engine=memory;
MariaDB [db1]> create table t4(id int)engine=blackhole;
MariaDB [db1]> quit
[[email protected] db1]# ls /var/lib/mysql/db1/ # It is found that the latter two storage engines only have table structure , No data
db.opt t1.frm t1.ibd t2.MYD t2.MYI t2.frm t3.frm t4.frm
#memory, It's restarting mysql Or restart the machine , The data in the table is cleared
#blackhole, Insert any data into the table , It's like dropping into a black hole , There will never be a record in the table
