Police millet: “tell me, what’s going on?”
Interviewer Wang Xiaolong: “Comrade policeman, he hit me with his resume and cut my delicate skin!”
Job seeker Li erhu: “Oh, you owe me a beating!”
Policeman: “be serious about your mistakes!”
Li erhu: “yes, comrade police! But you don’t know how angry he is!”
Policeman: “irritating? Tell me.”
Li erhu: “it’s like this. I’m a programmer. I went to their company for an interview today, mainly about the idea of MySQL database optimization. He asked me ‘what are the reasons why an SQL statement executes very slowly?’ after I answered, he said no. I’m not angry.”
Policeman: “I understand this question a little. How did you answer it?”
Li erhu: “I said that maybe the configuration of the host itself is not good, or other processes are on!”
Policeman: “what else?”
Li erhu: “no”
Policeman: “young man, this is your mistake. This problem seems simple, but it can involve a lot of core knowledge of MySQL. There are many reasons. It may be:
(1) The database is refreshing dirty pages. For example, the redo log is full and needs to be synchronized to the disk; Not enough memory;
(2) When executing, it encounters locks, such as table locks and row locks;
(3) No index is used;
(4) Wrong index selected in the database;
Of course, SQL itself may be written very well, so SQL optimization is needed.
These will not be discussed in detail, otherwise we will have to talk all day. “
Wang Xiaolong and Li erhu: “great God!”
Police: “in view of the simple cause and clear incident, the handling results are as follows: Erhu, you have to apologize to Comrade Wang Xiaolong; the cost of band aid is borne by you; at the same time, punish you to master MySQL!”
Wang Xiaolong: “thank you, comrade police!”
Li erhu: “well, I just want to know what are the optimization ideas of MySQL?”
To optimize mysql, we must first know: what affects the speed of database query? What affects MySQL performance? According to my understanding, database optimization mainly includes the following four points:
1. SQL statement optimization
2. Index optimization
3. Database structure optimization (normal form optimization, anti normal form optimization, split table)
4. Server hardware optimization
MySQL is commonly used in Java enterprise development. It is open source, free and easy to expand. MySQL is also widely used in Alibaba database system, and its stability is guaranteed. The principle, bottom layer and optimization of MySQL are the technical points that must be asked in the interview of large factories. Whether you are a qualified programmer depends on how well you master mysql.
The golden three silver four in 2022 is coming soon, and many people are preparing for the interview. Some time ago, a P7 employee in Alibaba integrated Alibaba’s training materials in the second half of 2021 and summarized this set of “Java anti evil sword spectrum”.
The whole manual is divided into seven chapters: high concurrency, distributed, middleware, database, design pattern, data structure and algorithm, and summary of Alibaba’s annual interview questions!
Let’s take a look at the screenshot of the content first. The full version of the PDF document can be obtained free of charge at the end of the text.
The core idea of second kill: layer by layer filtering
Try to intercept the request upstream and reduce the pressure downstream
Make full use of cache and message queue to improve request processing speed and the role of peak shaving and valley filling
Difficulties and solutions of large-scale Internet Applications (big data, high concurrency and diverse data types)
Sort the results according to the relevance of the obtained documents and query statements
Data structure and algorithm
Put method for analyzing HashMap
Summary of interview questions of Alibaba in 2021
Simplified version of configuration center
Opportunities are reserved for those who are prepared, so no matter how early you prepare in advance, what you learn now will eventually be used one day in the future. So, ape friends, learn. Success will surely belong to you!
If you need a full version, you can click the business card below, scan the code and get it for free with the screenshot.