For the technical interview, the core content of the investigation has the following two characteristics:
- Involves a wide range of knowledge, because different companies and different interviewers skill knowledge structure is different, so the focus of the questions and interview content is also different, so with the characteristics of a wide range of knowledge;
- The knowledge point of investigation is deep, take Alibaba as an example (other big factories are similar situation), usually will start from a big direction, and then layer upon layer of in-depth, until asked you will not so far. For example, what database engines do you know? What are the characteristics of this database engine? How does the database engine store data? Why this storage? And so on.
Therefore, in view of the above two problems, these 8 modules of knowledge are sorted out, which basically cover all the knowledge of MySQL, including the following contents:
This MySQL database contains 8 contents:
- The transaction
- The lock
- SQL Optimization Principles
- JOIN
- Execution plan and details
- Execute the process
- Impact of table structure on performance
- The index
About these 8 big questions also specially collated MySQL100 ask analysis to share with you, there is a need for friends to help forward the private letter reply 【 note 】 you can obtain information free access!
The transaction
- features
- classification
- Isolation level
MySQL transaction related interview questions
- What is a transaction?
- What is ACID? Can you explain it in detail?
- What about having multiple transactions going on at the same time?
- How to solve these problems? MySQL transaction isolation level
- What isolation level does Innodb use?
- Do you know about MySQL locks?
- What locks does MySQL have? Isn’t locking like the above kind of hinders concurrency efficiency?
The lock
- lock
- Latch < lightweight lock, which is very short in duration and used to manipulate critical resources >
- Consistent unlocked reads
- Consistent lock read
- A deadlock
SQL Optimization Principles
- Select the SQL to optimize
- Start with Explain and Profile
- Always use small result sets to drive large result sets
- Finish sorting in the index
- Using minimal Columns
- Use the most efficient filter conditions
- Avoid complex joins and subqueries
The JOIN principle
- The principle of the JOIN
- Optimization principle of JOIN
Execution plan and details
- Explain
- Profiling
The index
- type
- methods
- create
Index interview related questions
Impact of table structure on performance
- Processing of redundant data
- The big watch is divided into the small watch
- Show a more reasonable table structure based on requirements
- Common attributes are separated into small tables
Table structure design related
- Why try to have a primary key?
- Does the primary key use an autoincrement ID or a UUID?
- Why is the field required to be not NULL?
- If you want to store user password hashes, what fields should be used for storage?
Execute the process
- The query cache
- The parser generates the parse tree
- Preprocessing generates the parse tree again
- Query optimizer
- Query execution Plan
- Query execution engine
- Result of querying data is returned
MySQL optimized learning thinking notes
More advanced learning materials to share
Friends in need trouble to help forward after the private letter reply [notes] you can get information for free!
The small needle DUI MySQL optimization and architecture design knowledge difficulty specially share a PDF document “MySQL performance optimization and parse the document architecture design”, at the same time with the higher-ups MySQL video, it will get twice the result with half the effort, then read on.
This document is divided into three parts
- This chapter introduces the basic knowledge, architecture composition, storage engine, security management and basic backup and recovery knowledge of MySQL software.
- The performance optimization chapter starts from the factors that affect the performance of MySQL database application system, and conducts a targeted tuning analysis on each factor
- The architecture design is mainly to design a highly available and scalable distributed enterprise database cluster environment as the goal, analyzes and introduces a variety of architecture through MySQL to achieve this goal.
Due to space limitations, xiaobian here will only the actual combat document contains all the contents of the show, need to obtain a complete document to learn friends can pay attention to xiaobian, background private letter: [notes] get free access!
Based on some
- Introduction to MySQL
- MySQL Architecture Composition
- Introduction to MySQL Storage engine
- MySQL Security Management
- MySQL backup and restore
Contents and content display
Performance Optimization
Section 1: Factors related to MySQLServer performance
Section 2: MySQL database locking mechanism
The main content includes: introduction of MySQL locking mechanism, analysis of various locking mechanisms, reasonable use of locking mechanism optimization of MySQL, summary
Section 3: MySQL database Query optimization
The main contents include: understanding the QueryOptimizer of MySQL, basic ideas and principles of Query statement optimization, making full use of Explain and Profiling, rational design and utilization of indexes, implementation principles and optimization ideas of Join, ORDERBY, GROUPBY and DISTINCT optimization
Verse 4 through 6
- MySQL database Schema design performance optimization
- MySQLServer performance optimization
- Common storage engine optimization
Architectural Design
This part is divided into seven sections as follows:
- Basic principles of MySQL extensible design
- Extensible design for MySQLReplication
- Scalable design for data sharding
- Use of Cache and Search in scalable design
- MySQLCluster
- High availability design ideas and schemes
- MySQL monitoring in a highly available design
