Answers to several high-frequency MySQL interview questions

Preface:

In interviews for various technical positions, it seems that MySQL-related questions are often asked. Whether you are interviewing for a development position or an operations position, you will always be asked a few database questions. Friends often send me private messages asking how to deal with MySQL interview questions. In fact, many interview questions are similar, so it is necessary to prepare in advance. This article briefly talks about several common interview questions. Let’s learn together.

1. What is a relational database? Talk about your understanding of MySQL.

This is a basic question that tests the interviewee's understanding of databases. Generally, you can briefly talk about your understanding in an organized manner. for example:

A relational database is a database that uses a relational model to organize data and stores data in the form of rows and columns. The biggest feature of relational databases is that they support transactions. Common relational databases include MySQL, Oracle, SQLServer, etc. MySQL is the most popular open source database. Due to its small size, fast speed, low total cost of ownership, and especially its open source nature, many companies have adopted the MySQL database to reduce costs. It is currently widely used in small and medium-sized websites on the Internet, especially in the OLTP field.

2.What are the common storage engines for MySQL and what are their differences?

This question is also often asked, and is similar to the question "What is the difference between InnoDB and MyISAM engines?"

Several common storage engines:

• InnoDB: The default storage engine of MySQL, supporting transactions, MVCC, foreign keys, row-level locks, and auto-increment columns.
• MyISAM: Supports full-text indexing, compression, spatial functions, table-level locks, does not support transactions, and has fast insertion speed.
• Memory: All data is in memory, and the data processing speed is fast, but the security is not high.
• ARCHIVE: Commonly used for historical archive tables, it takes up little space and the data cannot be updated or deleted.

Several differences between InnoDB and MyISAM engines:

• InnoDB supports transactions, but MyISAM does not.
• InnoDB supports foreign keys, while MyISAM does not.
• InnoDB does not support full-text indexing, but MyISAM does.
• InnoDB is a clustered index, and MyISAM is a non-clustered index.
• InnoDB does not save the specific number of rows in the table, while MyISAM uses a variable to save the number of rows in the entire table.
• The smallest lock granularity of InnoDB is row lock, and the smallest lock granularity of MyISAM is table lock.
• The storage structure is different. MyISAM tables are divided into three types: frm, MYD, and MYI, while InnoDB tables are generally divided into two types: frm and ibd.

3. Describe the MySQL infrastructure.

This question tests the interviewee's understanding of MySQL architecture and is similar to the question "Execution flow of a select statement".

The logical architecture of MySQL is mainly divided into three layers:

• The first layer: client connection processing, security authentication, authorization, etc. Each client connection will have a thread on the server, and each query initiated by the connection will be executed in the corresponding separate thread.
• The second layer: MySQL's core service function layer, including query parsing, analysis, query cache, built-in functions, stored procedures, triggers, views, etc. The select operation will first check whether the query cache is hit. If it is hit, the cached data will be directly returned. Otherwise, the query will be parsed and the corresponding parsing tree will be created.
• The third layer: storage engine, responsible for data storage and extraction. The MySQL server communicates with the storage engine through the API, shielding the differences between various engines. Common storage engines include: InnoDB, MyISAM.

The execution flow of a select statement:

• The client establishes a connection with the MySQL server through the connector, obtains the user's read and write permissions, and then submits a query statement.
• First, MySQL will query the submitted statement in the query cache. If it hits and the user has permission to operate the table, it will directly return the query result in the query cache as the result of this query, and the query ends here.
• If the query cache misses, it goes to the analyzer, which parses the statement and checks its legality. If the statement does not conform to the MySQL syntax specification, the executor will report an error and the query will end.
• If the statement is legal, it will go to the optimizer, which will select the best execution plan for the SQL statement.
• Finally, the executor comes. If the user has permission to operate the table, the executor will call the interface provided by the storage engine to execute the SQL statement, and then return the query results to the client, and the query ends here.

4. Talk about several commonly used field types.

This question tests the interviewee's understanding of MySQL field types and can lead to many small questions, such as the difference between char and varchar.

Commonly used field type classification:

Numeric type:

String type:

Date and time types:

The M in int(M) stands for the maximum display width. Our first reaction to "maximum display width" is the maximum width of the value that the field can store. We might think that since we created int(1), we cannot store the data 10. In fact, this is not the case. int(5) and int(10) have the same storage range.

The CHAR type is fixed-length, and MySQL always allocates enough space based on the defined string length. When CHAR values ​​are stored, they are padded on the right with spaces to the specified length, and when CHAR values ​​are retrieved, trailing spaces are removed. The VARCHAR type is used to store variable-length strings. When storing, if the number of characters does not reach the defined number of digits, no spaces will be added at the end. The M in char(M) and varchar(M) represents the maximum number of characters that can be stored. A single letter, number, Chinese character, etc. takes up one character.

5. Talk about the role, structure and usage specifications of indexes.

There are so many questions about indexing that it may take more than a few articles to explain them clearly. Simply share the answers to these questions:

The purpose of indexing is to improve query efficiency. It can be compared to the directory in a dictionary. When searching for dictionary content, you can find the storage location of the data based on the directory and then obtain it directly. An index is a table directory. Before searching for content, you can first search for the index location in the directory to quickly locate the query data.

Under the InnoDB engine, the B+Tree index is mainly used. Each index is actually a B+ tree. The B+ tree is a balanced search tree (not a binary tree) designed for disks and other storage auxiliary devices. In the B+ tree, all data is in the leaf nodes, and each leaf node has a pointer to the next node, forming an ordered linked list.

From a physical storage perspective, InnoDB indexes can be divided into clustered indexes and secondary indexes or auxiliary indexes. The leaf nodes of the clustered index store the entire row of data. When a query uses the clustered index, you only need to scan a B+ tree of the clustered index to get the required records. If you want to find the complete record through the secondary index, you need to go back to the table, that is, find the primary key value through the secondary index and then find the complete record in the clustered index.

The obvious advantage of indexes is that they can speed up queries, but creating indexes also comes at a cost. First, each time an index is created, a B+ tree must be created for it, which will take up additional storage space; secondly, when the data in the table is added, deleted, or modified, the index also needs to be dynamically maintained, which reduces the speed of data maintenance. Therefore, there are principles for creating and using indexes. Generally, indexes are only created for columns used for searching, sorting, grouping, and joining. Indexes should not be created for columns with poor selectivity as much as possible.

6. Talk about the characteristics and isolation levels of MySQL transactions.

Questions related to MySQL transactions are also often asked, and some principles still need to be studied in depth.

ACID has four characteristics:

• A (Atomicity): All operations in a transaction either succeed or fail.
• C (Consistency): The database always switches from one consistent state to another. If the constraint is violated, the consistency condition is not met.
• I (Isolation): The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and concurrently executed transactions cannot interfere with each other.
• D (Durability): After a transaction is committed, the changes it makes will be permanently saved to the database.

Transaction isolation level:

• Read Uncommitted: Modifications in a transaction, even if not committed, are visible to other transactions.
• Read Committed: Modifications in a transaction are visible to other transactions only after they are committed.
• Repeatable Read: When querying the same record multiple times in a transaction, the results are always consistent (default isolation level).
• Serializable: Transactions are executed serially, reads are locked, and writes are locked.

Problems caused by concurrent transactions:

• Dirty Reads: Transaction A reads data that is not committed by transaction B, and then B rolls back the operation. In this case, the data read by A is dirty data.
• Non-Repeatable Reads: Transaction A reads the same data multiple times. During the process of transaction A reading the data multiple times, transaction B updates and commits the data, resulting in inconsistent results when transaction A reads the same data multiple times.
• Phantom Reads: Phantom Reads are similar to non-repeatable Reads. It happens when a transaction A reads a few rows of data, and then another concurrent transaction B inserts some data. In subsequent queries, transaction A will find some additional records that did not exist originally, just like an illusion, so it is called phantom read.

The above are the detailed answers to several high-frequency MySQL interview questions. For more information about high-frequency MySQL interview questions, please pay attention to other related articles on 123WORDPRESS.COM!