Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the high-frequency interview questions of MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. What is a relational database? Tell me what you know about MySQL.

This is a basic question, to examine the interviewer's understanding of the database, generally can briefly talk about their own cognition, organized. For example:

A relational database is a database that uses a relational model to organize data, which stores data in the form of rows and columns. The most important feature of relational database is that it supports transactions. The common relational databases are MySQL, Oracle, SQLServer and so on. MySQL is the most popular open source database today. Because of its small size, high speed and low total cost of ownership, especially open source, many companies use MySQL database to reduce costs. It is widely used in small and medium-sized websites on Internet, especially in the field of OLTP.

What are the common storage engines in 2.MySQL and what's the difference?

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

Several common storage engines:

InnoDB: MySQL's default storage engine, which supports transactions, MVCC, foreign keys, row-level locks, and self-incrementing columns.

MyISAM: supports full-text indexing, compression, spatial functions, table-level locks, does not support transactions, fast insertion.

Memory: the data is all in memory, so the processing speed of the data is fast, but the security is not high.

ARCHIVE: often used for historical archive tables. It takes up a small space and the data cannot be updated or deleted.

There are several differences between InnoDB and MyISAM engines:

InnoDB supports transactions, but MyISAM does not.

InnoDB supports foreign keys, but MyISAM does not.

InnoDB does not support full-text indexing, while MyISAM does.

InnoDB is a clustered index and MyISAM is a non-clustered index.

InnoDB does not save the specific number of rows of the table, while MyISAM holds the number of rows of the entire table with a variable.

The smallest lock granularity for InnoDB is row locks, and the smallest lock granularity for MyISAM is table locks.

The storage structure is different, MyISAM table is divided into three frm MYD MYI, InnoDB is generally divided into two frm ibd.

3. Describe the MySQL infrastructure.

This question examines the interviewer's understanding of the MySQL architecture, similar to the "one select statement execution process" question.

Logical architecture diagram of MySQL

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

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

The first layer: the client connection processing, security authentication, authorization, etc., each client connection will have a thread on the server, and the query initiated by each connection will be executed in the corresponding separate thread.

The second layer: the core service function layer of MySQL, including query parsing, analysis, query caching, built-in functions, stored procedures, triggers, views, etc. Select operation will first check whether it hits the query cache, and then directly return the cached data, otherwise, parse the query and create the corresponding parsing tree.

The third layer: 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 are: InnoDB, MyISAM.

A select statement execution flow:

The client establishes a connection with the MySQL server through the connector, obtains the user's read and write permissions, and then submits the query statement.

First of all, MySQL will query the submitted statement in the query cache. If it is hit and the user has permission to operate on the table, it will directly return the query result in the query cache as the result of this query. The query ends 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 syntax specification of MySQL, the executor will report an error and the query ends.

If the statement is legal, it will come to the optimizer, and the optimizer will choose the best execution plan for the SQL statement.

Finally, when it comes to the executor, if the user has permission to operate on 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.

4. Talk about several commonly used field types.

This question examines the interviewer's knowledge of the MySQL field type and can extend a number of small questions, such as the difference between char and varchar.

Commonly used field type categories:

Numerical type:

String type:

Date and time type:

Int (M) in the M represents the maximum display width, "maximum display width" our first reaction is the value of the field can be allowed to store the value of the width, thinking that we built an int (1), can not store data 10, in fact, this is not the meaning, int (5) and int (10) can be stored in the same range.

The CHAR type is of fixed length, and MySQL always allocates enough space based on the defined string length. When the Char value is saved, fill in the spaces to the right of them to reach the specified length, and when the Char value is retrieved, the trailing space is deleted. The VARCHAR type is used to store variable-length strings and does not fill in spaces if the characters do not reach the defined number of digits. The M in char (M) and varchar (M) both represents the maximum number of characters saved, and a single letter, number, Chinese, etc., all occupy one character.

5. Talk about the function, structure and usage specification of the index.

There can be a lot of questions about the index, and it may not be clear to write a few articles. Briefly share the answers to these questions:

The purpose of index is to improve query efficiency. You can compare the directory in the dictionary. When looking up the contents of the dictionary, you can find the location of the data according to the directory, and then get it directly. An index is the catalog of a table, and you can quickly locate the query data by looking for the index location in the catalog before looking for content.

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

From a physical storage point of view, InnoDB indexes can be divided into clustered indexes (clustered index) and secondary indexes (secondary index) or secondary indexes. The leaf node of the clustered index stores the whole row of data. when a query uses a clustered index, it only needs 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 operation, that is, to find the complete record in the clustered index after finding the primary key value through the secondary index.

The obvious advantage of an index is that it can speed up the query, but creating an index also comes at a cost. First of all, each index has to build a B+ tree for it, which will take up additional storage space; secondly, when the data in the table is added, deleted and modified, the index also needs dynamic maintenance, which reduces the speed of data maintenance. Therefore, the creation and use of the index has principles, generally only for the search, sorting, grouping, join columns to create an index, poor selectivity of the column as far as possible not to create an index.

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 learned in depth.

There are four features of ACID:

A (Atomicity): all operations in a transaction either succeed or fail.

C (Consistency, consistency): the database always changes from one consistency state to another. If the constraint is broken, the consistency condition will not be satisfied.

I (Isolation, isolation): the execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

D (Durability, persistence): after a transaction is committed, its changes are permanently saved to the database.

Transaction isolation level:

Read uncommitted (Read Uncommitted): changes in a transaction, even if they are not committed, are visible to other transactions.

Read committed (Read Committed): changes in a transaction are not visible to other transactions until they have been committed.

Repeatable Read: the same records are queried multiple times in a transaction, and the results are always consistent (the default isolation level).

Serializability (Serializable): transactions are executed serially, with read locks added to the read and write locks added to the write.

Problems caused by concurrent transactions:

Dirty Reads: transaction A reads the uncommitted data of transaction B, and then B rolls back the operation, so the data read by An is dirty data.

Non-repeatable read (Non-Repeatable Reads): transaction A reads the same data many times, and transaction B updates and commits the data in the process of reading the same data many times, resulting in inconsistent results when transaction A reads the same data many times.

Phantom Reads: phantom reading is similar to unrepeatable reading. It occurs when one transaction A reads several rows of data, and then another concurrent transaction B inserts some data. In the subsequent query, transaction A will find that there are some records that do not exist, as if there were hallucinations, so it is called phantom reading.

This is the end of the content of "what are the high-frequency interview questions for MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.