[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Time:2021-7-25

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine

If you haven’t read my first share, you can refer to it:

Phase I sharing:[the first bullet of Q & a sharing] about MySQL row lock, table lock, exclusive lock and shared lock, after watching the hanging interviewer!

First official account [terminal R & D]

Core point

1. Differences and connections between search engines InnoDB and MyISAM
2. Memory related knowledge supplement

target

1. Differences and connections between search engines InnoDB and MyISAM
2. Memory related knowledge supplement
3. About the difference and relationship between primary key and foreign key
4. Application scenario

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

About the differences and relationships between primary keys and foreign keys:

The primary key is the primary key of a table, which can uniquely determine the of this record.

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

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

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

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

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

When selecting, nnodb needs to maintain more things than MyISAM engine;

(1) For data blocks, InnoDB needs to be cached, and MyISAM only caches index blocks, which also reduces the number of exchange in and out;

(2) InnoDB addressing should be mapped to blocks and then to lines. MyISAM records the offset of the file directly, and the positioning is faster than InnoDB

(3) InnoDB also needs to maintain mvcc consistency; Although your scenario does not, it still needs to check and maintain mvcc (multi version concurrency control) multi version concurrency control

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

InnoDB storage engine

1. InnoDB has transaction, rollback, crash repair capabilities and multi version concurrent transaction security
2. About InnoDB Auto_ Increment column:

InnoDB supports automatic growth column. This column cannot be empty and the value must be unique
This column must be a primary key. When inserting, no value is specified. The default value is self incrementing. When 0 or null is specified, it is also a self incremented value. If a legal and nonexistent value is specified, the subsequent automatically starts from that value
3. Foreign key in InnoDB:
InnoDB supports foreign keys. The table where the foreign key is located is usually called a child table. The dependent table is called the parent table. In the parent table, the fields associated with the child table must be the primary key of the parent table.
(foreign key: it can be simply understood as: when the data of the parent table is updated, deleted or added, the data of the child table will also change. There are many examples, not here)
4. InnoDB’s storage engine and its advantages and disadvantages:
The InnoDB storage engine has three storage formats:
. frm table structure file, which stores information such as field definitions when the table is created.
The data file of the table is stored in InnoDB_ data_ home_ Dir directory,
The index file of the table is stored in InnoDB_ data_ file_ Path directory
InnoDB storage engine has good acid characteristics.
Disadvantages of InnoDB: the reading and writing efficiency is relatively poor compared with MyISAM. It takes up a lot of disk space.
5. Ideal application of InnoDB:
Tables with high concurrency and many update operations. Tables that require transactions. Table with requirements for automatic disaster recovery.

3: MyISAM storage engine:

1. There are three files stored in the MyISAM storage engine table:

File structure of table, FRM
. MyD file to store the data of the table
. MYI file that stores the index of the table

2. Storage format of MyISAM storage engine

Static type
Dynamic type

Shrink type:

3. Advantages and disadvantages of MyISAM storage engine:

Advantages: small footprint and fast processing speed (compared with InnoDB)
OK: transaction integrity and concurrency are not supported

###Summary

(1) . transactions:

MyISAM is non transaction safe, while InnoDB is transaction safe (supports transaction processing, etc.)

(2) . different locking mechanisms:

MyISAM is a table lock and InnoDB is a row lock.

(3) . add, delete, modify query:

MyISAM: if you perform a lot of query operations, MyISAM is a better choice

InnoDB: if your data performs a large number of inserts / updates, you should use the InnoDB table for performance reasons

(4) . no. of rows in query table:

MyISAM: when executing the select count() from table statement, MyISAM only needs to simply query the saved rows. When the count() statement contains the where condition, the operations of the two tables are the same.

InnoDB: the specific number of rows in the table is not saved in InnoDB, that is, when you execute select count (*) from table, InnoDB needs to scan the whole table to calculate the number of rows

(5) . foreign keys

MyISAM does not support foreign keys

Mysiam table does not support foreign keys, while InnoDB does

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Supplementary memory storage engine

File storage form of memory storage engine
The memory storage engine will also form a. Frm table structure file on the disk, but the data pieces of the table are not stored on the disk in the form of files. Since the data is stored in memory, the access speed is faster. But what needs to be considered is the persistence of data in memory.

Index type of memory storage engine

Default hash index

Support BTREE index

Memory features

The data storage cycle data of the memory storage engine is stored in memory. Once the server is shut down, the data will no longer exist

Advantages and disadvantages of memory storage engine:

Memory storage engine does not support variable length table columns
Before MySQL 4.1.0, auto is not supported_ Increment column

Usage scenario

1. MyISAM is suitable for:

(1) Do a lot of count calculations;
(2) Insert infrequently and query very frequently;
(3) No transactions.

2. InnoDB is suitable for:

(1) High reliability requirements or transaction requirements;
(2) Table updates and queries are quite frequent, and row locking is more likely.

3. Usage of memory storage engine

Fast, temporary data
When the loss has no or little negative impact on the whole project.

PS: how to learn Java? I recommend two materials for white whoring:

1. Books:

codeGoogler/ProgramBooks

2: Video tutorial:

Download springboot, spring, mybatis, redis, rabbitmq, springcloud and high concurrency (continuous update) from free Java resources throughout the network_ In this era, as a programmer, you may have to learn small programs – CSDN blog

last

Finally, as usual, Amway Yibo’s job number: “terminal R & D department”, currently recommends a high-quality technology related article every day, mainly sharing Java related technologies and interview skills. Our goal is to know what it is and why, lay a good foundation and do everything well! This public official account is worth our attention.

If you have any technical problems, you can consult me. The technical road is long and elegant, and brother Yu can always accompany you. If you are helpful, welcome to praise!

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!