“This is the third day of my participation in the First Challenge 2022, for more details: First Challenge 2022”.

Hi, everyone. I’m Melo, a sophomore backstage trainee. Recently, I was beaten by the MySQL interview questions in advance.

Column to review

  • Principles of indexing && design principles
  • Explain SQL, index failures && common optimization scenarios

Welcome to this column: Advanced MySQL

The introduction

In previous articles, we talked about the principles of indexing && design principles and how indexes are used. And learn to use EXPLAIN to analyze SQL, optimize SQL this article, let’s broaden our horizons, overview of the logical architecture and storage engine of MySQL, and then we pick the important points, in the next article detailed analysis.

👉 This quick review

InnoDB, a very familiar noun, what is this thing in the end is east east, ta also which several brothers and sisters

This article will take you through the logical architecture and storage engines from the following points:

  • Logical architecture hierarchy
  • Different storage engines
  • Difference between InnoDB and MyISAM
  • Other Storage Engines
  • Storage engine selection rules

Logical architecture

An analogy can be made to our MVC architecture

  1. The first is the connection layer, showing to the outside world, so that the outside world can connect to our mysql through certain technologies, such as JDBC,.NET, PHP and so on

  2. Then there is an entire Server service layer with many modules

    1. Connection Pool: indicates a Connection Pool component
    2. Management Services & Utilities: Components that manage Services and tools
    3. SQL interface to distinguish between DML and DDL, view, trigger, etc
    4. Parser that parses requests from clients and removes comments? Like an hourglass
    5. Optimizer Optimizer
    6. Caches & Buffers

  3. Engine layer

    1. Pluggable Storage Engines are Pluggable Storage Engines

  4. Storage layer

    1. -Rufus: File System.

1) At the top of the connection layer are some client and link services, including local SOCK communication and most communication similar to TCP/IP implemented based on client/server tools. Mainly complete some similar connection processing, authorization and authentication, and related security schemes. On this layer, the concept of thread pools is introduced to provide threads for clients that are securely accessed through authentication. SSL – based secure links can also be implemented at this layer. The server also validates that it has operational permissions for each client that is securely connected.

2) Service layer The second layer architecture mainly completes most of the core service functions, such as SQL interface, and completes the cache query, SQL analysis and optimization, and the execution of some built-in functions. All cross-storage engine functionality is also implemented in this layer, such as procedures, functions, and so on. In this layer, the server will parse the query and create the corresponding internal parse tree, and perform the corresponding optimization, such as determining the order of the query table, whether to use the index, and finally generate the corresponding execution operations. If the select statement is used, the server also queries the internal cache. If the cache space is large enough, the system performance can be improved in an environment where a large number of read operations are performed.

3) Engine layer Storage engine layer, storage engine is really responsible for the storage and extraction of data in MySQL, the server communicates with the storage engine through API. Different storage engines have different functions. In this way, you can select a proper storage engine based on your own needs.

4) Storage layer The data storage layer stores data on file systems and interacts with storage engines.

MySQL is a bit different from other databases in that its architecture can be used and used well in many different scenarios. Mainly reflected in the storage engine, plug-in storage engine architecture, query processing and other system tasks and data storage extraction separation. In this architecture, you can select an appropriate storage engine based on service requirements.

🎮 hotplug

  • The plug type here can be understood as hot plug oh, that is, the engine can be replaced at any time without affecting the original system.

Mechanical keyboard players say the old line, hot plug yyDS keyboard

For comparison, this is conventional welding (non-hot-swappable) :

This is hot plug:

Photo source: www.zhihu.com/question/40…

The storage engine

An overview of the

Unlike most databases, MySQL has a storage engine concept. You can choose the optimal storage engine for different storage requirements.

A storage engine is a way to store data, build indexes, update query data, and so on. Storage engines are table based, not library based. So storage engines can also be called table types.

Databases such as Oracle and SqlServer have only one storage engine. MySQL provides a plug-in storage engine architecture. Therefore, MySQL has various storage engines. You can use them or write storage engines as required.

Storage engines supported by MySQL5.0 include: InnoDB, MyISAM, BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc. InnoDB and BDB provide transaction security table, Other storage engines are non-transactional security tables.

🍠 key

  1. Storage engines are table based, not library based ！！！！
  2. Hot plug: We can change the engine at any time without restarting MySQL without affecting the original system. You can also choose a different storage engine for each table, although this is often not recommended.

Check the supported engines

show engines;
Copy the code

Query the storage engines supported by the current database

  • Engine: storage Engine name.
  • Support: indicates whether the engine is supported and whether the engine is the DEFAULT storage engine. YES indicates that the engine is supported, NO indicates that the engine is not supported, and DEFAULT indicates that the engine is the DEFAULT storage engine.
  • Comment: A brief introduction to the storage engine.
  • Transactions: indicates whether the engine supports Transactions.
  • XA: Indicates whether the storage engine supports distributed transactions.
  • Savepoints: indicates whether the storage engine supports partial transaction rollback.

If you do not specify a storage engine when creating a new table, the system will use the default storage engine. Before MySQL5.5, the default storage engine was MyISAM. After 5.5, the default storage engine was InnoDB. This is often asked in an interview

Relevant command

View the supported storage engines
SHOW ENGINES

View the default storage engine
SHOW VARIABLES LIKE 'storage_engine'

To view the storage engine used by a particular table,
show create table tablename

See exactly which storage engine is used by a table in a database
show table status like 'tablename'
show table status from database where name="tablename"
Copy the code

Setting the Storage Engine

Specify storage engine when creating table. The default is INNODB, no setting required
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = MEMORY;

-- Modify the storage engine
ALTER TABLE t ENGINE = InnoDB;

Change the default storage engine, or change the default engine in the configuration file my.cnf
SET default_storage_engine=NDBCLUSTER;
Copy the code

View the default storage engine

show variables like '%storage_engine%' ； 
Copy the code

Various storage engine features

The two most commonly used storage engines: InnoDB, MyISAM;

The characteristics of INNODB MYISAM MEMORY MERGE NDB
Storage limits 64TB There are There are There is no There are
The transaction security support
Locking mechanism Row locking (suitable for high concurrency) Table locks Table locks Table locks Row locks
B-tree indexes support support support support support
The hash index support
The full text indexing Support (after 5.6) support
The cluster index support
Data index support support support
The index buffer support support support support support
Data compressibility support
Space use high low N/A low low
Memory usage high low medium low high
Batch insertion speed low high high high high
Support foreign keys support

InnoDB

InnoDB storage engine is the default storage engine for Mysql. InnoDB storage engine provides transaction security with commit, rollback, crash recovery capabilities. But Compared to MyISAM’s storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes.

InnoDB storage engine differs from other storage engines in the following aspects:

MVCC

InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. The default level is REPEATABLE READ, and the next-key locking policy prevents phantom reading. Gap locking allows InnoDB to lock not only the rows involved in the query, but also gaps in the index to prevent phantom rows from being inserted.

These four isolation levels, gap locks, and so on, will be discussed in more detail later in the locks and transactions section.

Transaction control

create table goods_innodb(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL.primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;

start transaction;

insert into goods_innodb(id,name)values(null.'Meta20');

commit;
Copy the code

🧂 Foreign key constraint

InnoDB is the only storage engine in MySQL that supports foreign keys. When creating foreign keys, the parent table must have corresponding indexes, and the child table will automatically create corresponding indexes when creating foreign keys.

This is why we can only select the primary key as the foreign key when we do not create the index, because the primary key will create the index by default

​

When creating indexes, you can specify RESTRICT, CASCADE, SET NULL, and NO ACTION that can be performed on child tables when the parent table is deleted or updated.

  • RESTRICT, which is the same as NO ACTION, indicates that -> when a parent table is deleted, the parent table cannot be deleted if the child table has associated records.

If the child table is dropped, there is no constraint.

  • CASCADE: When the parent table is updated or deleted, the records corresponding to the child table are updated or deleted.

It’s like they live and die together

  • SET NULL: When the parent table is updated or deleted, the corresponding fields of the child table are SET NULL.
  • For the two tables created above, the foreign key of the child table is set to ON DELETE RESTRICT. Therefore, when the primary table drops a record, the child table cannot drop a record if it has a corresponding record.
    • ON UPDATE CASCADE: When the primary table updates records, if the child tables have corresponding records, the child tables are updated.

Small demo

In the following two tables, COUNTRY_innodb is the parent table, country_id is the primary key index, city_innoDB is the child table, and country_ID field is the foreign key corresponding to country_ID, the primary key of country_INNODB.

create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL.primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL.primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ON DELETE RESTRICT: Deletes the primary table. If there are associated records, the primary table will not be deleted
-- ON UPDATE CASCADE: Updates the primary table. If the sub-tables have associated records, UPDATE the sub-table records

insert into country_innodb values(null.'China'), (null.'America'), (null.'Japan');
insert into city_innodb values(null.'Xian'.1), (null.'NewYork'.2), (null.'BeiJing'.1);
Copy the code

Delete country_id 1 from country_id where country_id = 1:

 delete from country_innodb where country_id = 1;
Copy the code

Check the foreign key

show create table city_innodb ;
Copy the code

File storage mode

InnoDB stores tables and indexes in two ways: The table structure of a table created in this way is stored in a. FRM file. Data and indexes are stored in the innodb_datA_home_DIR and innodb_datA_file_PATH tablespaces, which can be multiple files.

② With multi-tablespace storage, the table structure of the table created in this way still exists in the.frm file, but the data and indexes of each table are stored separately in.ibd.

Locking mechanism

  • Row locks instead of table locks

Specific knowledge about locking, we will discuss in detail in the follow-up lock and transaction!

MyISAM

  • Does not support transactions, the natural access speed is relatively fast, more suitable for a large number of read operations SELECT and insert operations;
  • Foreign keys are not supported.

File storage mode

Each MyISAM is stored on disk as 3 files with the same file name as the table name, but with the extension name:

  • .frm (store table definitions);
  • .myd (MYData, store data);
  • .myi (MYIndex, store index);

🍿InnoDB is different from MyISAM

  1. Foreign keys and transactions, and locking mechanisms (mentioned above)
  2. InnoDB is a clustered index, MyISAM is a non-clustered index.

Also called primary key index and secondary index

  • The files of the clustered index are stored on the leaf nodes of the primary key index, whereas the non-clustered index requires two queries, first to the primary key and then to the data through the primary key. (The index classification in the index section has a more specific explanation, interested partners can take a look first).

  • MyISAM is a non-clustered index, the data file is separated, the index holds the pointer to the data file, primary key index and secondary index are independent.

  1. InnoDB does not store the exact number of rows in a table. A full table scan is required when performing select count(*). MyISAM uses a variable to store the number of rows in the entire table, so you only need to read the variable when executing the above statement, which is much faster.

Of course, this is not absolute, look at the following analysis:

Select count(*); select count(*); select count(*); select count(*);

🌭 Autoincrement primary key correlation

This could be an interview question!!

Version 8.0

  1. MySQL > alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM alter table MyISAM
  2. InnoDB doesn’t. The next insert starts at 14. Since the InnoDB table only records the maximum ID of the self-increment primary key into memory, restarting MySQL or performing an OPTION operation on the table will cause the maximum ID to be lost.

Version 5.7

  • In version 5.7, the maximum ID will be lost after the restart.

The MEMORY (understand)

The Memory storage engine stores the table’s data in Memory. Each MEMORY table actually corresponds to a disk file in the format of. FRM. This file stores only the structure of the table, while its data files are stored in MEMORY, which facilitates fast data processing and improves the efficiency of the entire table.

Memory-type tables are very fast to access because their data is stored in MEMORY and HASH indexes are used by default, but when the service is shut down, the data in the table is lost.

MERGE(I know, but I feel good about it)

MERGE a storage engine is a group of MyISAM tables. The MERGE tables must have the same structure. The MERGE tables do not store data themselves.

For MERGE table INSERT_METHOD, the INSERT_METHOD clause defines the table to be inserted. The INSERT_METHOD clause can have three different values. FIRST or LAST values are used so that the insert is applied to the FIRST or LAST table. Indicates that an insert cannot be performed on the MERGE table.

You can DROP a MERGE table, but this operation simply drops the MERGE table definition and has no impact on the internal tables.1) create 3 test table order_1990, order_1991, order_all, including order_all is two tables before the MERGE table:

create table order_1990(
	order_id int ,
	order_money double(10.2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_1991(
	order_id int ,
	order_money double(10.2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_all(
	order_id int ,
	order_money double(10.2),
	order_address varchar(50),
	primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;
Copy the code

2) Insert records into the two tables separately

insert into order_1990 values(1.100.0.'Beijing');
insert into order_1990 values(2.100.0.'Shanghai');

insert into order_1991 values(10.200.0.'Beijing');
insert into order_1991 values(11.200.0.'Shanghai');
Copy the code

If a record is inserted into order_all, INSERT_METHOD selects LAST when the MERGE table is defined, then the inserted data is inserted into the LAST table.

insert into order_all values(100.10000.0.'xi 'an');Copy the code

🎆 Engine selection rules

When selecting a storage engine, select an appropriate one based on the characteristics of the application system. For complex application systems, you can combine multiple storage engines based on actual conditions. However, do not mix multiple storage engines unless you have to. Otherwise, a series of complex problems may occur. The following are common storage engine environments:

  • InnoDB: is the default storage engine for Mysql, used for transaction processing applications, and supports foreign keys. InnoDB storage engine is a good choice if the application has high requirements for transaction integrity, data consistency under concurrent conditions, data operations in addition to insert and query, but also a lot of update and delete operations. InnoDB storage engine not only reduces locking due to deletion and update, but also ensures complete commit and rollback of transactions. InnoDB is the best choice for systems that require high data accuracy, such as billing or accounting systems.

  • MyISAM: If the application is mainly read and insert operations, with few updates and deletes, and the transaction integrity and concurrency requirements are not very high, then this storage engine is very suitable.

  • MEMORY: Keeps all data in RAM, providing access to several blocks in situations where records and other similar data need to be located quickly. MEMORY has limitations on the size of tables that are too large to be cached in MEMORY, and the need to ensure that table data can be recovered after a database abort. MEMORY tables are often used to update small tables that are less frequent to get access results quickly.

  • MERGE: Used to combine a series of equivalent MyISAM tables in a logical manner and reference them as an object. MERGE tables have the advantage of overcoming the size limitation of a single MyISAM table and improving the access efficiency of MERGE tables by distributing different tables on multiple disks. This is ideal for VLDB environments such as data warehousing.

Backup Angle

  • If the server can be shut down periodically for backup, it can be ignored. On the other hand, if online hot backup is required, InnoDB is the most basic requirement.

🍙 summary

  1. Logical architecture and layering enable us to better locate bugs. High performance hot plug is the unique point of MySQL.
  2. The storage engine is table-based, not library-based, and can be viewed and modified using commands.
  3. No special requirements, we all choose InnoDB unless:
    1. Full text indexing is required, and only MyISAM supports it, but we try to choose other possible solutions, such as adding some third-party tools or engines on top of InnoDB.
    2. Business cases where insert and query operations dominate and other operations are almost minimal.
  4. InnoDB good MyISAM difference, interview may be asked, need to pay attention to.
  5. Other storage engines, just understand.

💠 Next post

This article is a general overview of the logical architecture and storage engine, but not into the specific knowledge, such as MVCC, transaction isolation level, locking mechanism, etc., these, we leave to the next article, to have a good chat, this piece of knowledge, is also the most important!!

🖨 References

  • Dark Horse MySQL Advanced section

Collection = white whoring, praise + attention is true love!! If there is anything wrong with this article, please also point out in the comment area, welcome to add my wechat, together with communication: Melo__Jun

🧿 friend chain

  • 🎉 my one year backstage practice career

  • Talk about Java

  • Distributed development practice

  • Redis introduction and actual combat

  • Data structures and algorithms

  • MySQL senior post