Differences between common storage engines of Beckham _ mysql and the storage engine of Beckham _ mysql

Differences between common storage engines of Beckham _ mysql and the storage engine of Beckham _ mysql
Storage engine differencesBrief:
1. Storage Engine
2. Differences between myisam and innodb

 

I. Storage Engine

1. What is a storage engine?

In general, it is the way to store and manage data.

Image example:

Bicycle administrators in a certain area: Li, Zhang. Many people access bicycles every day. Li's management method is: the car owner stores the car on his own, and Li does not record what car the other car stores. The administration fee is required when the car is picked up. It does not check whether it is the other party's own car. On the contrary, Zhang records the characteristics of the person and information of the car when the car owner stores the car. When a person comes to pick up a car, Zhang also needs to carefully check whether the characteristics of the person and the car correspond.

We can see from the above example:

A. From the perspective of efficiency: Li is faster than Zhang because he does not need to proofread

B. from the security point of view: Zhang is more secure than Li, because he has proofread.

Conclusion: in fact, the storage engine of mysql works like this. Each storage engine has its own access method. Therefore, you should choose based on the actual situation.

 

2. View All mysql Engines

Mysql> show engines;

 

Ii. Differences between Myisam and Innodb

The two storage engines need to be differentiated because they are currently the most frequently used mysql engines. It is conducive to flexible use in actual situations.

1. myisam storage engine

A. Table Structure

Create a table mytest

Create table mytest (id int unsigned, content char (5) engine = myisam;

Files on the disk corresponding to the table

Summary:

A1, Suffix: frm is the table structure file, MYD is the data file, MYI is the index file

A2. Create tables of the myisam engine. These three files are created for convenient operation during mysql database backup or transfer.

B. Batch insert data

Insert 1000 rows of data

 

C. query the number of tables

Conclusion: we can see that mysiam records the number of rows in a table, rather than one record.

D. whether transactions are supported

Summary: myisam does not support transactions. Therefore, if you perform transaction operations in the database but the transaction fails, you need to check whether your table engine supports transactions.

E. Operation on the auto_increment column (modify the id column of mytest)

Insert data

Delete the data with id 3, and then insert the data

At this time, we found that after deleting id 3, we can continue to add data. id does not start from 3, but skips 3 and starts from 4.

Solution:

Reset auto_increment after deleting data

Summary: tables of the myisam engine are stored in the auto_increment table and stored on the file disk. Even if you restart the service, it will not be lost.

 

2. innodb Storage Engine

A. Table Structure

Create a myinnodb table

Create table myinnodb (id int unsigned, content char (5) engine = innodb;

Frm is a table structure file, while ibd is a data and index file.

B. insert data in batches (insert 1000 rows of data)

 

C. query the total number of tables

Summary: The innodb Engine tables are calculated in a row when calculating the total number of rows.

D. whether transactions are supported

Summary: innodb Engine tables support transaction operations

E. Operation on the auto_increment column (modify the id column of myinnodb)

Insert data

Delete data with id 3

Continue to insert data

This is the same as that of the myisam engine.

Solution:

Summary: innodb can reset auto_increment, but its auto_increnment column is only stored in the primary memory, rather than on the disk.

Restart service

3. Differences between myisam and innodb

	Myisam	Innodb
File structure	Frm, MYD, MYI	Frm, ibd
Transaction supported?	MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB tables, but transactions are not supported.	InnoDB provides advanced database functions such as transactions and external keys.
Calculate the number of rows in a table	Select count (*) from table, MyISAM simply reads the number of rows saved. Note that when the count (*) statement contains the where condition, the operations of the two tables are the same.	InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows.
Lock Granularity	Table lock	Row lock
Operation on AUTO_INCREMENT	Stored on the disk, which can be reset through alter	It is stored only in the primary memory, not on the disk.

 

4. Differences between table locks and row locks

When you perform the insert, update, and delete operations on the database, the mysiam table locks the table, while the innodb table locks the row. In other words, if you execute an update statement, the mysiam table will lock the entire table and other insert, delete, and update statements will be rejected, the update statement is executed in sequence after it is executed.

The lock row means that if you execute the update statement, only the record will be locked, only other write and update operations for this record will be blocked and executed after the update statement is executed. Write operations for other records will not be affected.

 

5. Selection of myisam and innodb

A. the business needs to support transactions. Select innodb

B. For business queries, select myisam.

 

6. Summary

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If the application needs to execute a large number of SELECT queries, MyISAM is a better choice.

InnoDB is used for transaction processing applications and has many features, including ACID transaction support. If the application requires a large number of INSERT or UPDATE operations, InnoDB should be used to improve the performance of multi-user concurrent operations.

Supplement:

1. For the data structure of innodb, we must first understand two conceptual issues: the shared tablespace and the exclusive tablespace.

What are shared and exclusive tablespaces?

Shared and exclusive tablespaces are used for data storage.

Shared tablespace: All table data of a database is stored in one file. By default, the file path of the shared tablespace is in the data directory. The default file name is: ibdata1 initialized to 10 M.

Exclusive tablespace: each table is generated and stored in an independent file. Each table has a. frm table description file and A. ibd file. The file contains the data content and index content of a single table. By default, the file is stored in the table.

The quieter you become, the more you are able to hear!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.