Detailed explanation of the storage engine of mysql database

This article talks about the storage engine of mysql database, so students who don’t know much about the storage engine of mysql database and our engine should take a look at this article about the storage engine of mysql database!

Optimization structure introduction:

Type	Meaning
Storage layer	Storage engine, field type selection, paradigm design
Design layer	Index, cache, partition ( Sub-table)
ArchitectureLayer	Multiple mysql server settings, read and write separation (master-slave mode)
sql statement layer	When multiple sql statements can achieve the purpose, you must choose a sql statement with high performance and fast speed

Storage Engine

Storage engine: The data we use is stored in the database through certain technologies. The database data is stored in the hard disk in the form of files. There is more than one technology, and each technology has its own unique performance and functionality. The combination of technology and functionality for storing data is called a "storage engine."

• Storage engines often used in mysql: Myisam or Innodb, etc.

• 

  Database data is stored in different storage engines, and all features are related to the current storage engine. Different storage engines need to be selected according to the needs and characteristics of the project.
  

• View all storage engines supported in mysql:
  

innodb

Database Three aspects of data design information for each data table: table structure, data, index

• Technical features: support transactions, row-level locking, foreign keys

Physical storage of table structure, data, and indexes

• Create an innodb data table:
  

• Table structure file:

Physical file location of this type of data and index:

• The data and index information of all innodb tables are stored in the following ibdata1 file

For innodb type tables The data and indexes create their own corresponding storage space:

• By default, the data and indexes of each innodb table will not create separate file storage

• Set variables so that each innodb table has unique data and index storage files:

• Re-create the order2 data table:

• # #At this time, the order2 data table has separate data and index storage files:

##No matter how the setting status of innodb_file_per_table changes later , the data and index of order2 have independent storage locations
Data storage order

Innodb table data is stored according to the primary key Arrange each written data in order.

This feature determines that the write operation of this type of table is slow.

Transactions and foreign keys

This type of data table supports transactions and foreign keys

Transactions: http://blog.csdn.net/change_any_time /article/details/79488020
Foreign key: Two data tables A and B. The primary key of table B is an ordinary field of table A. When you look at this ordinary field in table A, it is the The "foreign key" of the table, the use of foreign key has "constraints".

Constraints: For the above two tables, the data of table B must be written first, and then the data of table A, and the foreign key value of table A must come from the primary key id value of table B and cannot exceed its range.

"Foreign keys" are rarely used in real projects because of constraints.
Concurrency

The concurrency of this type of table is very high. Multiple people operate the data table at the same time. In order to operate the data table, the data content will not happen randomly. Changes require "locking" information

The lock level of this type is: row lock. Only the current record being operated on is locked.

Myisam

The structure, data, and indexes are stored independently. This type of data table has independent storage files for the table structure, data, and indexes:

• Create Myisam Data table
  

• The structure, data, and index of each myisam data table have independent storage files
  

##*.frmTable structure file*.MYDTable data file*.MYI

Features: Independent storage files can be backed up and restored separately.

Data storage order

• The storage of myisam table data is to arrange each written data in natural order.
  

  This feature determines that the writing operation of this type of table is faster.
  

Concurrency

This feature determines that the writing operation of this type of table is faster.

Compression mechanism

If a data table contains a lot of data, in order to save storage space, the table needs to be compressed.

• Copy the data of the current data table:
  

• Continuous copying makes the data of the order3 data table become more than 2 million Articles:
  

• The corresponding physical size of the file storing the 2 million pieces of information is more than 40 MB:
  

Start compressing the data of the order3 data table

• Compression tool: myisampack.exe Table name
  

• Rebuild index: myisamchk.exe -rq table name
  

• Decompression tool: myisamchk.exe –unpack table name
  

• order3 table information is compressed 60% of the space:
  

• order3 data table is compressed, but the index is gone :
  

• Rebuild the index:
  

• The index is indeed rebuilt:
  

• Refresh the data table: flush table table name
  

• Occurrence: The compressed data table is read-only Table, information cannot be written:
  

Compressed data tables have characteristics: frequent writing operations cannot be performed, but data tables with fixed content can be compressed, for example (Data table that stores national regional information, etc.)
If you must write data: decompress the data table, write the data, and then compress

• Decompress the order3 data table so that it can write data: (Decompress and index automatically Reconstruction)
  

• Data decompression is completed:
  

• Perform the flush operation and update the decompressed Data: flush table table name; This operation will also delete the compressed backup file of order3.MYD.00996D46.deleted
  

• Allowed at this time Continue to write data to order3:
  

##innodb storage engine: suitable for modification and deletion

Myisam storage engine: suitable for query and writing

Archive

innodb storage engine: suitable for modification and deletion

Myisam storage engine: suitable for querying and writing

memory

Memory storage engine, the operation speed is very fast, more suitable for storing temporary information, if the server is powered off, the data to the storage engine will be lost immediately .

Storage engine selection

Myisam and innodb

1. In most cases, the website has a lot of "read and write" operations, so it is suitable to choose the Myisam type (for example: dedecms , phpcms content management system (news website), discuz forum)

2. # The website has certain requirements for business logic (office website, shopping mall), it is suitable to choose innodb (the default storage engine of Mysql5.5 is innodb)

Table file type	Meaning
	Table index file

The above is the detailed content of Detailed explanation of the storage engine of mysql database. For more information, please follow other related articles on the PHP Chinese website!