Home > Database > Mysql Tutorial > body text

Detailed explanation of the storage engine of mysql database

韦小宝
Release: 2018-03-14 10:27:08
Original
2037 people have browsed it

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.

  • Detailed explanation of the storage engine of mysql database

    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:
    Detailed explanation of the storage engine of mysql database

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

Detailed explanation of the storage engine of mysql database

Physical storage of table structure, data, and indexes

  • Create an innodb data table:
    Detailed explanation of the storage engine of mysql database

  • Table structure file:

Detailed explanation of the storage engine of mysql database

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

Detailed explanation of the storage engine of mysql database

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

Detailed explanation of the storage engine of mysql database

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

Detailed explanation of the storage engine of mysql database

  • Re-create the order2 data table:

Detailed explanation of the storage engine of mysql database

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

Detailed explanation of the storage engine of mysql database

    ##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.

Detailed explanation of the storage engine of mysql databaseThis 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
    Detailed explanation of the storage engine of mysql database

  • The structure, data, and index of each myisam data table have independent storage files
    Detailed explanation of the storage engine of mysql database

##*.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.
    Detailed explanation of the storage engine of mysql database

    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:
    Detailed explanation of the storage engine of mysql database

  • Continuous copying makes the data of the order3 data table become more than 2 million Articles:
    Detailed explanation of the storage engine of mysql database

  • The corresponding physical size of the file storing the 2 million pieces of information is more than 40 MB:
    Detailed explanation of the storage engine of mysql database

Start compressing the data of the order3 data table

  • Compression tool: myisampack.exe Table name
    Detailed explanation of the storage engine of mysql database

  • Rebuild index: myisamchk.exe -rq table name
    Detailed explanation of the storage engine of mysql database

  • Decompression tool: myisamchk.exe –unpack table name
    Detailed explanation of the storage engine of mysql database

  • order3 table information is compressed 60% of the space:
    Detailed explanation of the storage engine of mysql database

  • order3 data table is compressed, but the index is gone :
    Detailed explanation of the storage engine of mysql database

  • Rebuild the index:
    Detailed explanation of the storage engine of mysql database

  • The index is indeed rebuilt:
    Detailed explanation of the storage engine of mysql database

  • Refresh the data table: flush table table name
    Detailed explanation of the storage engine of mysql database

  • Occurrence: The compressed data table is read-only Table, information cannot be written:
    Detailed explanation of the storage engine of mysql database

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)
    Detailed explanation of the storage engine of mysql database

  • Data decompression is completed:
    Detailed explanation of the storage engine of mysql database

  • 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
    Detailed explanation of the storage engine of mysql database

  • Allowed at this time Continue to write data to order3:
    Detailed explanation of the storage engine of mysql database

##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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template