“This article has participated in the call for good writing activities, click to view: the back end, the big front end double track submission, 20,000 yuan prize pool waiting for you to challenge!”
The second part of the interview series has finally been completed. In this episode, we introduce MySQL, which mainly explains how to deal with the questions related to MySQL in the interview process. In addition, IN this series, I will gradually introduce some of my experiences and skills in preparing for the autumn recruitment interview process, hoping to be helpful to you.
Helpful hints
I have written this series for students who are about to take their autumn exams as an emergency, to help them save time on revision. Students who are not enrolled in autumn this year can browse the article to roughly understand the needs of the next autumn, and then go to my public number to reply to the background of the computer basic lead point related information to lay a good foundation, but also to do more projects to improve their practical ability. If you’re a freshman and you’re memorizing interview essays with me every day, you’re a loser, I tell you.
As mentioned in my last article, I should be familiar with two or three aspects such as Java foundation, JVM, Redis, MySQL, framework, design mode, data junction, and computer network that I need to master at the backend of an interview for a large company. I should be able to conduct in-depth communication with the interviewer as a highlight of the interview. Redis, MySQL and the Spring framework are the highlights of my interview when I make my autumn recruitment preparation plan. In order to prepare for MySQL, I spent about 4 weeks preparing for MySQL (I also spent some time to learn this knowledge repeatedly during the recruitment interview process). Of course, I did not stop preparing for MySQL during this period, except for LeetCode code questions every day. I also spent a little time looking at other things in between. If you’re not going to use MySQL as a highlight of your interview, and just want to answer the interviewer’s usual questions, you don’t need to use it for too long, and then repeat it at intervals.
Note: I mainly learned to do Java Web development when I was an undergraduate, but I did natural language processing when I was a graduate student, so I didn’t touch development for two years. My foundation is that I am familiar with the basic operation of MySQL, and I have actually used MySQL in my undergraduate project, but I am not clear about the underlying principle of MySQL. You can refer to my foundation and compare it with your own foundation to make your own learning plan.
In the process of preparing MySQL, I completed the following tasks:
(1) Remember some basic concepts often asked by MySQL interview (will be repeated in the process of preparing for the interview).
(2) I have some understanding of SQL optimization. And put part of the understanding of the application in their own kill system project, how to do specific I will not say, I hope you can also think about after learning, such as how to optimize the MySQL transaction according to the characteristics of the row lock, such as how to choose the index according to the actual scene of their own project, and so on. If you can apply it to your own projects, it will still interest the interviewer.
(3) FOR THE SQL code questions also carried out some training, some interviewers will also test in the interview.
If you don’t think MySQL is the focus of your interview preparation, just do (1) and (3). In addition, if MySQL is considered as the focus of the interview, you should also prepare (1) and (3) first, and then optimize the part further if time permits.
Tips for preparing for an interview
After a certain level of preparation, it is recommended that you conduct more interviews “to practice”. Because your mental focus is high during the interview, you will find many of your problems and remember some of them during the interview. Most people usually back a section of dozens of words of text, back several minutes can not back down, and even if back down for a while forget. But by asking them to repeat what the interviewer asked them in the past hour, they can remember clearly and write an interview. In addition, through the mock interview can help you comb out the idea of answering questions, answering questions is also very good for the interview. The form of interview can be a mock interview with a few friends or seniors, or an interview with a few companies you don’t want to go to in the early stage of autumn recruitment (a little unethical, but it can help the interviewer to complete the interview KPI).
We can about two weeks about a friend or elder sister to conduct mock interview, so the promotion of learning is also relatively large. Every day to see it is very easy to slack off, learning efficiency is not high. You can do code problems with several friends, and each day one of them talks about one problem, which is what I did. In fact, I feel that no matter what we do, we can accomplish more with more cooperation.
How to prepare for MySQL interview
Ok, now officially enter the MySQL interview eight-part essay highlights process. Here I would like to recommend a book called “deep and Simple MySQL”.
Recommended reasons: “Simple to understand MySQL” this book is written by netease database experts, from the database foundation, development, optimization, management and maintenance, architecture, five levels about MySQL (we interview the back-end of the management and maintenance chapter need not see), the content layer by layer. And each small knowledge point will be corresponding with examples, easier for readers to understand.
Learning content: The learning content is introduced according to the contents of MySQL in Brief. I will not attach the answers to the questions listed in the chapter. You can summarize them by yourself, which will have a better school effect. I will answer the questions in the supplement.
Fundamentals Chapter 2 Fundamentals of SQL
Basic SQL statements must be able to, and skilled, this is the most basic.
Interview questions for this chapter:
(1) Add, Delete, And check these are questions that interviewers generally don’t ask. But if you ask, for example, “Which keyword is used for the query,” you’re screwed.
(2) What are the words ORDER BY, LIMIT, GROUP BY, HAVING?
(3) Talk about the difference between left link and right link.
Foundation chapter 3, chapter 4, chapter 5
These three chapters are the data types, operators, and common functions of MySQL respectively. Just take a quick look at these three chapters, which are rarely asked in an interview.
Development chapter 7 selection of table types (storage engines)
InnoDB is the focus of the interview, and relevant knowledge should be reviewed in detail. In addition, we should compare InnoDB with MyISAM and MEMORY to understand the characteristics of InnoDB engine.
Interview questions for this chapter:
What is the difference between InnoDB, MyISM and MEMORY?
Development chapter 10: Index design and use
BTree index = BTree index = BTree index = Hash index
Interview questions for this chapter:
(1) The data structure used for the index and why it was designed this way.
(1) The principle of creating indexes in the database.
(2) Application scope of BTree index and Hash index.
Development Chapter 11 Views
This chapter does the basic understanding, the interview process asks little.
Development chapter 14 transaction control and locking statements
InnoDB is a row lock, MyISAM, MEMORY is a table lock. The transaction control examples in this chapter are worth a good look to deepen your understanding of database locks.
Optimization Chapter 18 SQL optimization
If you emphasize your familiarity with MySQL in your resume and in your introductory introduction, you’ll want to read this chapter. The interviewer wants to see if your MySQL skills match what you said before, and will tend to ask you a scenario question to design and optimize (of course, just reading this chapter won’t solve the problem completely, I’ll add a little bit later).
Interview questions for this chapter:
(1) what are the steps of optimizing SQL statements?
(2) Which scenarios can be indexed.
(3) Under which conditions the index will fail.
Although the interviewer won’t ask you directly what are your skills in optimizing SQL statements? For example, how to optimize Insert statements, and how to optimize order by statements. But you can learn some common techniques for optimizing SQL statements in this chapter. When the interviewer asks a specific question, you should mention that you usually use these techniques to optimize SQL statements.
Optimize chapter 20 locking Problems
This chapter belongs to chapter 14, we mainly look at table lock and row lock, page lock was asked less.
Interview questions for this chapter:
(1) Four features of transaction, and explain the meaning of these four features.
(2) What are the problems caused by concurrent affairs?
(3) Transaction isolation level.
(4) InnoDB row lock implementation
(5) Do you know the next-key lock?
(6) How to avoid deadlock in InnoDB.
(7) Database multi-version concurrency control (MVCC mechanism)
Chapter 21 optimizing MySQL Server
This chapter is not asked in the campus recruitment interview, but I learned the relevant knowledge here to tell the interviewer, the effect is good. You can choose to watch or not watch according to your own situation.
Things to start a conversation with your interviewer:
(1) MySQL memory management and optimization.
(2) The internal mechanism of InnoDB redo log, which can be related to transactions to the interviewer.
Architecture Chapter 31 MySQL Replication
The interviewer will rarely ask questions about this chapter if you don’t mention them intentionally. If you’re not going to talk to your interviewer in MySQL, you can skip this. If you’re going to talk to your interviewer in MySQL, this is a great place to talk to your interviewer.
Things to start a conversation with your interviewer:
(1) The master-slave replication principle of MySQL.
(2) Three types of MySQL replication.
(3) MySQL asynchronous replication and semi-synchronous replication.
(4) How to improve the performance of replication.
supplement
Next I will give you a little more knowledge, is my notes in learning Lin Xiaobin’s “MySQL Actual Combat 45”, this class is really good. This knowledge is not in the MySQL book, but it can be useful in the interview process (interviewers sometimes ask), and it can be useful in practice.
MySQL infrastructure
The infrastructure of MySQL can be divided into Server layer and storage engine layer. The Server layer contains most of the core service functions of MySQL, and the storage engine layer is responsible for data storage and extraction. The functions of each module are as follows:
Connector: Responsible for establishing a connection with the client.
Query cache: Previously executed query statements are stored in the cache as key-values. After receiving a query statement, MySQL checks whether the same key exists in the cache. If yes, the value is directly returned. However, do not use this feature in scenarios where tables are frequently updated because the cache is flushed with each table update.
Analyzer: performs lexical and syntax analysis on the ENTERED SQL statements.
Optimizer: The optimizer determines the execution plan of SQL statements, such as selecting the most appropriate index from multiple indexes in a table, or determining the join order of tables when a statement has multiple table associations.
Executor: Performs actions according to the optimizer’s customized execution plan and returns results.
Storage engine: Stores data and provides read and write interfaces.
MySQL code exercises
I’ve only been asked to write SQL code questions four or five times out of dozens of interviews. However, it is necessary to prepare properly. It does not take too much time to practice the simple and medium questions in the free code questions on LeetCode. It is good to be able to deal with the SQL code questions in the interview.
Well, the above is the preparation of autumn recruitment process all to learn MySQL knowledge, preparation process will appear back to forget the situation, we should often remember the knowledge repeatedly. Come and recite the Redis essay with me next.