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
Physical storage of table structure, data, and indexes
Physical file location of this type of data and index:
For innodb type tables The data and indexes create their own corresponding storage space:
- # #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:
Table file type |
Meaning |
##*.frm | Table structure file |
*.MYD | Table data file |
*.MYI |
Table index file
|
Features: Independent storage files can be backed up and restored separately.
Data storage order
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
Archiveinnodb 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
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)
# 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)
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!