Hello everyone, I’m Brother Yu. A few days ago, I was able to share the second interview question about the interview points of search engine in MySQL. This question is an interview at normal temperature

Haven’t seen my first share can go to the reference below:

MySQL > MySQL row lock, MySQL table lock, MySQL table lock, MySQL table lock, MySQL table lock

Launched on the official account [Terminal R&D Department]


1. Difference and connection between search engine InnoDB and MyISAM; 2. Supplement of relevant Memory knowledge

The target

1. The difference and connection between the search engine InnoDB and MyISAM; 2. Supplement of relevant Memory knowledge; 3

The difference between a primary key and a foreign key:

A primary key is the primary key of a table that uniquely identifies the record.

A foreign key refers to the value of a field in the primary table. Generally, one primary key record corresponds to multiple foreign key records. Typically, a foreign key field in one table refers to the primary key of another table.

! [ ](https://upload-images.jianshu…

First, let’s talk about why MyISAM is faster than InnoDB.

NODB has a lot more to maintain than the MyISAM engine when doing SELECT;

(1) Data blocks, InnoDB cache, MyISAM only cache index blocks, there is a swing-in/swing-out reduction;

(2) InnoDB addresses should be mapped to blocks and rows. MyISAM records the OFFSET of files directly, which is faster than InnoDB’s location

(3) InnoDB also needs to maintain MVCC consistency; Although your scenario does not have one, it is still necessary to check and maintain MVCC (Multi-Version Concurrency Control)

InnoDB storage engine

InnoDB has transaction, rollback, crash repair capabilities and multi-version concurrent transaction security.

InnoDB supports auto-growing columns, which cannot be empty and must have unique values

This column must be a primary key. When inserting, no value is specified. The default value is the increment value. When 0 or NULL is specified, it is also an incremented value. Specify a valid, non-existent value, and subsequent operations automatically start from that value

InnoDB Foreign Key:

InnoDB supports foreign keys. The table where the foreign key resides is usually called a child table. The dependent table is called the parent table. In the parent table, the fields associated with the quilt table must be the primary key of the parent table.

Foreign key: When parent table data is updated, deleted, or added, child table data is changed. There are many examples, I will not give them here.)

4, InnoDB storage engine and its advantages and disadvantages:

The storage format of the InnoDB storage engine has three files:

.frm table structure file, which holds information such as the field definitions when the table was created.

Innodb_data_home_dir (innodb_data_home_dir)

Table index file, stored in innodb_data_file_path directory

InnoDB storage engine has good ACID features.

Disadvantages of InnoDB: read and write efficiency is relatively poor compared to MyISAM. It takes a lot of disk space.

5, InnoDB’s ideal use:

High concurrency, more update operations of the table. Tables that need to use transactions. A table with requirements for automatic disaster recovery.

3: MyISAM Storage Engine:

1, MyISAM storage engine table storage has three files:

.frm file, which stores the structure of the table

.myd file, which stores the data of the table

.myi file, which stores the index of the table

2. The storage format of MyISAM storage engine

Static and dynamic

Shrinkage type:

3. Advantages and disadvantages of MyISAM storage engine:

Advantages: Small footprint, fast processing (relative to InnoDB) Determinations: No support for transaction integrity and concurrency

# # #

(1). Transaction processing:

MyISAM is non-transaction safe, InnoDB is transaction safe (support transaction processing, etc.)

(2) Different locking mechanisms:

MyISAM is a table lock, InnoDB is a row-level lock.

(3) Operation of adding, deleting, modifying and checking:

MyISAM: If you are performing a lot of query operations, MyISAM is a better choice

InnoDB: If your data is doing a lot of inserts/updates, you should use InnoDB tables for performance reasons

(4). The number of rows in the query table is not used:

MyISAM: When you execute a SELECT COUNT () FROM TABLE statement, MyISAM simply queries the number of rows saved. When the COUNT () statement contains the WHERE condition, the operation is the same for both tables.

InnoDB: InnoDB does not store the exact number of rows in the table. In other words, when InnoDB performs SELECT COUNT (*) FROM TABLE, InnoDB scans the entire table to calculate how many rows there are

(5). The foreign key

MyISAM does not support foreign keys

MySIAM tables do not support foreign keys, whereas InnoDB does

Supplement Memory storage engine

The Memory storage engine also forms a.frm table structure file on disk, but the table data is not stored on disk as a file. Since its data is stored in memory, it is faster to access. But there is one thing to consider: the persistence of the data in memory.

The index type of the Memory storage engine

Default hash index

Btree index is supported

Characteristics of the Memory

Data is stored in Memory. Once the server is shut down, the data no longer exists

The pros and cons of a Memory storage engine:

The Memory storage engine does not support variable-length table columns

Before MySQL4.1.0, AUTO_INCREMENT columns were not supported

Usage scenarios

1. MyISAM is suitable for:

(1) do a lot of calculations of count;

(2) Insertion is not frequent and query is very frequent;

(3) No transactions.

2, InnoDB suitable for:

(1) the reliability requirements are relatively high, or require transactions;

(2) The table update and query are quite frequent, and the chance of row locking is relatively high.

3. Where to use the Memory storage engine

The speed requires fast, temporary data

After the loss, there is no or little negative impact on the overall project.

PS: How to learn Java, recommend two Bai Qiao learning materials:

1. Books:


2. Video Tutorials:

SpringBoot, Spring, MyBatis, Redis, RabbitMQ, SpringCloud, High Concurrency (Continuous Update)_ This era, as a programmer may want to learn a small program -CSDN blog

The last

At the end of the day, we will continue to work under the same title: “Terminal R&D Department”. Now, every day, we will recommend a quality article about technology, mainly sharing JAVA-related technology and interview skills. Our goal is to know what it is and why, lay a good foundation and do a good job! This main technology public number is worth everyone’s attention.

If you have any technical questions, you can consult me. The road of technology is long and elegant. Brother Yu can always accompany you. If you can help, welcome thumb up!