Preface:

In all kinds of technical job interviews, it seems that MySQL related questions are frequently asked. Whether you’re interviewing for a development role or an operations role, there are always a few database questions to ask. I often get messages from friends asking how to deal with MySQL interview questions. In fact, many interview questions are similar, so it is necessary to prepare in advance. Here are some common interview questions.

1. What is relational database? Talk about what you know about MySQL.

This is a basic question to test the interviewees’ knowledge of the database. Generally speaking, you can briefly explain your cognition and organize it. Such as:

A relational database is a database that uses a relational model to organize data in the form of rows and columns. The biggest feature of relational databases is transaction support. Common relational databases are MySQL, Oracle, SQLServer and so on. MySQL is the most popular open source database. Because of its small size, fast speed, low total cost of ownership, especially the characteristics of open source, so that many companies have adopted MySQL database to reduce costs, is widely used in small and medium-sized websites on the Internet, especially in the FIELD of OLTP.

2. What are the common storage engines of MySQL? What are the differences?

This question is also frequently asked, similar to the “InnoDB and MyISAM engine difference” question.

Common storage engines:

  • InnoDB: MySQL’s default storage engine, supports transactions, MVCC, foreign keys, row-level locking, and auto-increment columns.
  • MyISAM: supports full-text indexing, compression, spatial functions, table level locking, does not support transactions, fast insertion.
  • Memory: Data is stored in Memory. Data processing is fast but not secure.
  • ARCHIVE: Used for historical ARCHIVE tables. Data cannot be updated or deleted because it occupies a small space.

Some differences between InnoDB and MyISAM:

  • InnoDB supports transactions, MyISAM does not.
  • InnoDB supports foreign keys, while MyISAM does not.
  • InnoDB does not support full-text indexing, while MyISAM does.
  • InnoDB is a clustered index, MyISAM is a non-clustered index.
  • InnoDB does not store the exact number of rows of a table, whereas MyISAM stores the number of rows of the entire table in a variable.
  • InnoDB’s minimum lock size is row lock, MyISAM’s minimum lock size is table lock.
  • Different storage structure, MyISAM table is divided into FRM, MYD, MYI three, InnoDB generally divided into FRM, IBD two.

3. Describe the MySQL infrastructure.

This question tests the interviewer’s knowledge of the MySQL architecture and is similar to the “a SELECT statement execution process” question.

MySQL logical architecture diagram

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

  1. Layer 1: For client connection processing, security authentication, authorization, etc., each client connection will have a thread on the server side, and the query initiated by each connection will be executed in the corresponding separate thread.
  2. Layer 2: the core service function layer of MySQL, 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 matched, and then directly return the cache data. Otherwise, the query is parsed and the corresponding parse tree is created.
  3. The third layer is storage engine, which is responsible for data storage and extraction. MySQL server communicates with storage engine through API, shielding the differences between various engines. Common storage engines include InnoDB and MyISAM.

A SELECT statement execution flow:

  • The client connects to the MySQL server through the connector, obtains the user’s read and write permission, and submits the query statement.
  • First, MySQL queries the submitted statement in the query cache. If a match is found and the user has the operation permission on the table, the query result in the query cache is returned as the result of this query. The end of the query is here.
  • If the query cache misses, it comes to the parser, which parses the statement and checks its validity. If the statement does not conform to the MySQL syntax, the executor reports an error and the query ends.
  • If the statement is valid, it goes to the optimizer, which selects the best execution plan for the SQL statement.
  • Finally, it comes to the executor. If the user has operation permission on the table, the executor will call the interface provided by the storage engine to execute the SQL statement, and then return the query result to the client. The query ends here.

4. Describe some common field types.

This question tests the interviewer’s knowledge of MySQL field types and can lead to a number of small questions, such as the difference between char and vARCHar.

Common field types:

The numerical model:

String type:

Date and time type:

Int (5) and int(10) can be stored in the same range as the value of int(1). The value of int(5) can be stored in the same range as the value of int(10).

The CHAR type is fixed length, MySQL always allocates enough space based on the length of the string defined. When CHAR values are saved, Spaces are padded to the right of them to the specified length, and trailing Spaces are removed when CHAR values are retrieved. The VARCHAR type is used to store variable-length strings that are not followed by a space if the character does not reach the specified number of digits. The M in char(M) and varchar(M) indicates the maximum number of characters that can be saved. A single letter, digit, or Chinese character can occupy one character.

5. Talk about the function and structure of index and its use standard.

About index, can have a lot of a lot of questions, may write a few articles also do not understand. Here are some answers to these questions:

The purpose of indexing is to improve query efficiency. It can be likened to a directory in a dictionary. When searching for the contents of a dictionary, you can find the location of the data according to the directory, and then directly obtain the data. An index is a table directory. You can search for the index position in the table before searching for the content to quickly locate the query data.

InnoDB engine, the main use is B + Tree index, each index are a B + Tree, B + Tree is designed to disks and other storage auxiliary equipment a balance search trees (not binary Tree), in the B + Tree, all the data in the leaf nodes, and each leaf node with a pointer to the next node, It forms an ordered linked list.

From a physical storage perspective, InnoDB indexes can be divided into clustered index and secondary index or secondary index. Clustering index of leaf node is the entire line of data, when a query is using a clustering index, only need to scan the cluster index is a B + tree can get the required records, if you want to through the secondary indexes to find the complete record, need operation by back to the table, which is after the primary key is found through a secondary indexes to clustering index finds the complete record.

The obvious advantage of indexes is that they speed up queries, but creating indexes comes at a cost. First, a B+ tree is created for each index, which takes up extra storage space. Secondly, when the data in the table is added, deleted, or modified, the index also needs dynamic maintenance, which reduces the data maintenance speed. Therefore, indexes are created and used in principle. Generally, indexes are only created for columns that are used for searching, sorting, grouping, and joining, and indexes are not created for columns with poor selectivity.

6. Talk about MySQL transaction features and isolation levels.

MySQL transaction related questions are often asked, some of the fundamental things still need to learn in depth.

Four properties of ACID:

  • A (Atomicity) : Operations in A transaction either all succeed or all fail.
  • C (Consistency) : the database always changes from one Consistency state to another Consistency state. If the constraint is destroyed, the Consistency condition is not met.
  • I (Isolation) : the execution of a transaction cannot be interfered by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions. Concurrent transactions cannot interfere with each other.
  • D (Persistence) : After a transaction commits, its modifications are permanently saved to the database.

Transaction isolation level:

  • Read Uncommitted: Changes made in a transaction, even when not committed, are visible to other transactions.
  • Read Committed: Changes in a transaction are not visible to other transactions until they are Committed.
  • Repeatable Read: The same record is queried multiple times in a transaction and the results are always consistent (default isolation level).
  • Serializable: Transactions are executed serially, read with read locks, and write with write locks.

Problems with concurrent transactions:

  • Dirty Reads: When transaction A Reads uncommitted data from transaction B and then transaction B rolls back the data, A Reads Dirty data.
  • Non-repeatable Reads: When transaction A Reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times, resulting in inconsistent results when transaction A Reads the same data for many times.
  • Phantom Reads: Phantom Reads are similar to unrepeatable Reads. It occurs when one transaction, A, reads A few rows of data, and then another concurrent transaction, B, inserts some data. In subsequent queries, transaction A will find more records that did not originally exist, as if an illusion occurred, so it is called phantom read.

Reference:

  • Juejin. Cn/post / 689552…