Meituan interviewer: Explain clearly the MySQL structure system and make an offer immediately

Story

Continue to share with you, I will The technical questions encountered during the interviews at Meituan in Shanghai were so-so at that time. They could not say bad or good, but they did not give the interviewer a pleasant feeling anyway.

It’s bad, it’s a lot of things. I usually feel fine, but once it’s time for the interview, I can’t think of anything.

Although, I have been developing Java for almost five years (2017), and I have used OracleDatabase (the system in the bank), but most of the time I use the MySQL database, but I am still confused when faced with this question (I bit the bullet and made some mistakes), and I thought the interviewer You need to ask about indexing, slow queries, performance optimization, etc. (because these are all interview questions found online and memorized). <figure data-tool="mdnice编辑器" style="margin-top: 10px;margin-bottom: 10px;display: flex;flex-direction: column;justify-content: center;align-items: center;"><img src="https://img.php.cn/upload/article/001/273/727/6b0135fbb00d408ab4d424ca864e24bb-0.png" alt="Meituan interviewer: Explain clearly the MySQL structure system and make an offer immediately" ><figcaption style="max-width:90%"><br></figcaption></figure><p data-tool="mdnice编辑器" style="padding-top: 8px;padding-bottom: 8px;line-height: 26px;margin-top: 1px;margin-bottom: 1px;">Today we will talk about the architecture system of <code style='font-size: 14px;padding: 2px 4px;border-radius: 4px;margin-right: 2px;margin-left: 2px;background-color: rgba(27, 31, 35, 0.05);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);'>MySQL, even though we are Java developers , but in the daily development process, I often deal with the MySQL database. It would be slightly better if the company has a DBA that can do something. If there is no DBA or the DBA is of no use, we still need to understand## The entire system of #MySQL, and meeting him in the interview is also a plus.

If you want to know how a

SQL is queried, you only need to understand the entire system of MySQL before you can say it. 123.

Therefore, it is necessary for us to learn the architecture system of

MySQL.

Usually, when I chat with my friends, I often think of

MySQL as a software system we develop. Since it is a software system, there is an architecture diagram and the architecture is How it is layered and what is the function of each layer.

What is MySQL?

• MySQL is a relational database management system developed by the Swedish MySQL AB company. Currently Belongs to Oracle company.
• MySQL is a relational database management system that stores data in different tables instead of putting all data in a large warehouse, so that Increased speed and improved flexibility.
• MySQL is open source, so you don’t need to pay extra.
• MySQL supports large databases and can handle large databases with tens of millions of records.
• MySQLUse standard SQL data language form.
• MySQL can be used on multiple systems and supports multiple languages. These programming languages ​​include C, C, Python, Java, Ped, PHP, Eifel , Ruby and TCL, etc.
• MySQL has good support for PHP, which is currently the most popular web development language.
• MySQL Supports large databases and data warehouses with 50 million records. 32-bit system table files can support up to 4GB, and 64-bit systems support the largest table files. is 8TB.
• MySQL is customizable and adopts the GPL protocol. You can modify the source code to develop your own MySQL system.

Please pay attention to the spelling of MySQL. In addition, many people may have questions, why is the logo of MySQL a dolphin?

Let’s take a look at the overall architecture diagram of MySQL.

MySQL Architecture Diagram

# #Let’s take a look at the system architecture diagram we developed:

In fact, they are quite similar, with the concept of layering. Since the software system we develop can be layered, can

MySQL be layered?

The answer is: Yes, let’s talk about the layering of MySQL and the functions of each layer.

Architecture diagram layering

We can split the above architecture diagram and make a brief illustrate.

Connection layer

##Dealing with the client, above The supported languages ​​have been specified. The client link supports many protocols, such as

JDBC that we are developing in Java.

Is this layer a bit like the

gateway layer in our project? If you are not familiar with the gateway, then we can understand my controller layer.

Service layer

##This layer is quite The service layer in our business system is a hodgepodge of business-related operations, code optimization, caching, etc.

Connection pool is mainly responsible for storing and managing the connection between the client and the database. One thread is responsible for managing one connection. Since the introduction of the connection pool, official reports: When the number of database connections reaches 128, the performance of using the connection pool and without the connection pool is improved by n times (anyway, the performance is greatly improved).

After the connection is established, you can execute the select statement. The execution logic will first come to the cache module.

Cache

After MySQL gets a query request, it will first go to the query cache to see if this statement has been executed before. Previously executed statements and their results are stored in memory in the form of key-value pairs. The key is the query statement, and the value is the query result. If your query can directly find the key (hit) in this cache, then the value will be returned directly to the client.

If there is a miss in the cache, the subsequent execution phase will continue. After the execution is completed, the execution results will be stored in the query cache. As you can see here, if the query hits the cache, MySQL can directly return the result without performing subsequent complex operations, which is very efficient.

But in most cases I would recommend you not to use query caching, why? Because query caching often does more harm than good.

The query cache fails very frequently. As long as a certain piece of data in a table is updated, all query caches on this table will be cleared.

So it may be very difficult to save the results, but before they are used, they will be completely cleared by an update. For databases with heavy update pressure, the hit rate of the query cache will be very low. Unless your business has a static table that will only be updated once a long time.

For example: a system configuration table, then the query on this table is suitable for query cache.

FortunatelyMySQL also provides this "use on demand" method. You can set the parameter query_cache_type to DEMAND so that the query cache is not used for the default SQL statements.

「Note」：MySQL 8.0 version directly deletes the entire query cache function, marking the beginning of MySQL 8.0 without the cache function at all.

Parser

If the query cache is not hit, the actual execution of the statement will begin. First, MySQL needs to know what you want to do, so it needs to parse the SQL statement.

The analyzer will first do "lexical analysis". What you input is an SQL statement composed of multiple strings and spaces. MySQL needs to identify what the strings in it are and what they represent.

After completing the lexical analysis, we need to do "grammatical analysis". Based on the results of lexical analysis, the syntax analyzer will determine whether the SQL statement you entered satisfies MySQL syntax based on grammatical rules.

If we are missing or misspelling a letter when spelling SQL, we will receive the error message "You have an error in your SQL syntax".

For example, in the following case:

The error is that there is an E missing in the WHERE keyword.

Similarly, we use SQL if a field does not exist.

Generally, grammatical errors will prompt the first position where the error occurs, so you should pay attention to the words immediately following "use near" The content is for reference only. Sometimes this tip is not very reliable.

The SQL was analyzed by the analyzer and no error was reported. Then enter the optimizer at this time to optimize the SQL.

Optimizer

The optimizer mainly determines which one to use if there are multiple indexes in our database table. Index; or when a statement has multiple table associations (join), determine the connection order of each table.

For example:

SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

It will optimize the conditional query.

After the optimizer processing is completed, the SQL execution plan has been determined. Then continue into the actuator.

Executor

First of all, you must determine the permissions, that is, whether you have permission to execute this SQL. Permissions may be controlled on some clients during work.

For example: In the production environment, most developers only have query permissions and no permissions to add, delete or modify (except for some small companies).

If you have permission, open the table and continue execution. When a table is opened, the executor will use the interface provided by the engine based on the table's engine definition.

Storage engine layer

In this layer, we It can be understood as the persistence layer in our business system.

The concept of storage engine is unique to MySQL. Not all relational databases have the concept of storage engine.

The database storage engine is the underlying software organization of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. Using different storage engines, you can also obtain specific functions. Many different database management systems today support a variety of different data engines.

Because data is stored in the form of a table in a relational database, the storage engine can also be called a table type (Table Type, that is, the type of storage and operation of this table).

• MySQL5.5 version (mysql version < 5.5 version) Previously, the default storage engine was MyISAM.
• MySQL5.5 version (mysql version >= 5.5 version) From now on, the default storage engine is InnoDB.

The following is a comparison of some relatively commonly used engines:

In In actual projects, most use InnoDB, then MyISAM, and at least other storage engines are used.

We can use the command to see what storage engines MySQL has provided:

show engies;

We can also use the command To view MySQL’s current default storage engine:

show variables like '%storage_engine%';

##MyISAM and InnoDBThe difference between engines

MySQLThe default storage engine before version 5.5 is the MyISAM storage engine. Many system tables in MySQL use the MyISAM storage engine and system temporary tables. The MyISAM storage engine will also be used, but after Mysql5.5, the default storage engine is the InnoDB storage engine.

How to choose between the two storage engines?

• #Are there any transaction operations? Yes, InnoDB.
• Do you want to store concurrent modifications? Yes, InnoDB.
• Are you pursuing fast query with less data modification? Yes, MyISAM.
• Do you want to use full-text indexing? If you do not reference a third-party framework, you can choose MyISAM, but it will be more efficient to use a third-party framework and InnDB.

The InnoDB storage engine mainly has the following features:

1. Support transactions

2. Support 4 levels of transaction isolation

3. Supports multi-version reading

4. ##Supports row-level locks

5. Read and write blocking is related to transaction isolation level

6. Support caching, which can cache both index and data

7. # #The entire table and primary key are stored in Cluster mode to form a balanced tree

Of course, this does not mean that InnoDB is necessarily good. In actual development, you still have to choose whether to use InnoDB or MyISAM based on specific scenarios.

MyIASM (this engine is the default storage engine in MySQL databases before 5.5) Features:

1. MyISAM does not provide support for database transactions

2. Does not support row-level locks and foreign keys

3. Due to 2, when executing the INSERT insertion or UPDATE update statement, that is, the entire table needs to be locked to perform the write operation, so it will cause Reduced efficiency

4. MyISAM saves the number of rows in the table. When executingSELECT COUNT(*) FROM TABLE, you can read the relevant values ​​directly without scanning the whole table, which is fast.

The difference between the two:

1. MyISAM is non-transactionally safe, while InnoDB is transactionally safe

2. MyISAM lock granularity is table-level, while InnoDB supports Row-level lock

3. MyISAM supports full-text indexes, while InnoDB does not support full-text indexes before MySQL5.6. It supports FULLTEXT indexes after MySQL5.6.

Usage scenario comparison:

1. If you want to perform a large number of select operations, you should choose MyISAM

2. ##If you want to perform a large number of insert and update operations, you should choose InnoDB

3. Large size data sets tend to choose the InnoDB engine because it supports transaction processing and failure recovery. The size of the database determines the length of failure recovery time. InnoDB can use transaction logs for data recovery, which is faster. Primary key queries will also be quite fast under the InnoDB engine, but it should be noted that if the primary key is too long, it will also cause performance problems.

Relatively speaking, InnoDB is used more by Internet companies.

System file storage layer

##This On the first level, we can also understand it as the database in our business system.

The system file storage layer is mainly responsible for storing database data and logs in system files, and at the same time completing the transaction with the storage engine. It is the physical storage layer of files.

For example: data files, log files, pid files, configuration files, etc.

Data file

「db.opt file」

: Record the default character set and calibration of this database test rules.

「frm file」

: Metadata information stored in the edge, including definition information of the table structure, etc. Each table will have a frm file corresponding to it.

「MYD file」

: A file dedicated to the MyISAM storage engine, which stores the data information of the MyISAM table. Each MyISAM table has a .MYD file.

「MYI文件」：也是MyISAM存储引擎专用的文件，存放MyISAM表的索引相关信息，每一张MyISAM表都有对应的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独立表空间和共享表空间。

• 独享表空间使用ibd文件来存放数据，并且每一张InnoDB表存在与之对应的.ibd文件。
• 共享表空间使用ibdata文件，所有表共同使用一个或者多个.ibdata文件。

「ibdata1文件」：系统表空间数据文件，存储表元数据、Undo日志等。

「ib_logfile0、ib_logfile0文件」：Redo log日志文件。

日志文件

错误日志：默认是开启状态，可以通过命令查看：

show variables like '%log_error%';

二进制日志binary log：记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行耗时；但是不记录查询select、show等不修改数据的SQL。主要用于数据库恢复和数据库主从复制。也是大家常说的binlog日志。

show variables like '%log_log%';//查看是否开启binlog日志记录。
show variables like '%binllog%';//查看参数
show binary logs;//查看日志文件

慢查询日志：记录查询数据库超时的所有SQL，默认是10秒。

show variables like '%slow_query%'；//查看是否开启慢查询日志记录。
show variables '%long_query_time%'；//查看时长

通用查询日志：记录一般查询语句；

show variables like '%general%'；

配置文件

用于存放MySQL所有的配置信息的文件，比如：my.cnf、my.ini等。

「pid文件」

pid文件是mysqld应用程序在Linux或者Unix操作系统下的一个进程文件，和许多其他Linux或者Unix服务端程序一样，该文件放着自己的进程id。

「socket文件」

socket文件也是Linux和Unix操作系统下才有的，用户在Linux和Unix操作系统下客户端连接可以不通过TCP/IP网络而直接使用Unix socket来连接MySQL数据库。

SQL查询流程图

##Summary

MySQL We can think of the entire system as a software system we develop daily. It also has an access layer, which is specifically designed to connect to external clients. It is very similar to the gateway of our system. The cache is similar to the cache used in our business code. Analysis The processor can be understood as parameter parsing and parameter verification in the business system. The optimization layer can be used as a means of optimizing our code development. Then the storage engine is equivalent to our persistence layer, and the file system is equivalent to the database in the entire business system.

Maybe the metaphor is not very appropriate, but I hope everyone can understand the meaning of severity. There is only one purpose, and that is to allow everyone to easily grasp the overall situation of

MySQL.

Some pictures in the article come from the Internet and have been deleted!

Don’t envy any great people or great gods every day, they have also come step by step. Be confident, as long as you do it bit by bit and work down-to-earth, you will become a great master.

Recommended reading

• ##If you don’t move, I won’t move- --Observer pattern

• Summary of interviews with 4 companies in 10 days after resignation
  

• A set of Spring Cloud Alibaba videos Tutorial
  

• Backend developer, learning path

• Backend 230,000 interviews, more than 500 pages, awesome!

• Summary of 112 interviews, it is recommended to collect

The above is the detailed content of Meituan interviewer: Explain clearly the MySQL structure system and make an offer immediately. For more information, please follow other related articles on the PHP Chinese website!