Interview with MySQL
Title: How to design a database under high concurrency?
Java Advanced Interview question: Why separate databases and tables? Which sub-database sub-table middleware have you used? What are the advantages and disadvantages of different sub-database and sub-table middleware? How exactly do you split the database vertically or horizontally?
MySQL > select * from ‘MySQL’; select * from ‘MySQL’
Java Advanced Architecture Interview Including message queue, Redis cache, MySQL sub-database sub-table, read and write separation, design of high concurrency system, distributed system, high availability architecture, SpringCloud micro-service architecture and other 8 categories of nearly 200 pages of content, from the interviewer psychological sharing to interview question analysis, step by step in-depth, easy to understand, is a rare dry product! ☛ original
Interviewer psychoanalysis
In fact, this must be pulled to the high concurrency, because the sub-database sub-table must be to support the high concurrency, large data two problems. And now to tell the truth, especially the Internet type of company interview, basically will come to so, sub-database sub-table so common technical problems, do not ask is not, and if you do not know that is really not the past!
Analysis of interview questions
Why separate database and table? (How to design database level when designing high concurrency system?)
To put it bluntly, sub – library sub – table is two things, we can not be confused, may be light sub – library sub – table, also may be light sub – table sub – library, are possible. Let me show you a scenario:
If we are now a small start-up company (or a new department just emerging from a BAT company), now there are 200,000 registered users, 10,000 daily active users, 1,000 daily single table data volume, and the maximum number of concurrent requests per second is 10 at the peak. God, it’s just this system, you take someone with a few years of experience, and you bring someone who’s just been trained, whatever.
We were lucky enough to meet a CEO who took us to the next level. The business grew rapidly, and within a few months it had 20 million registered users! 1 million daily active users! Daily single table data volume of 100,000! Peak peak of 1000 requests per second! At the same time, the company also incidentally with two rounds of financing, income several hundred million yuan ah! The valuation of the company reached a staggering several hundred million dollars! That’s the rhythm of the little unicorn!
Okay, that’s okay. Now you’re feeling a little stressed out. Why? Because 100,000 more data a day, 3 million more data a month, now we have millions of data in a single table, will soon be over 10 million. But I can barely hold on. Peak request is now 1000, we deployed several machines online, load balancing, database support 1000QPS is also ok. But everybody began to feel a little worried now, how to do next….. .
In the next few months, my god, the CEO is awesome, the company has reached 100 million users, the company continues to raise billions of yuan! The valuation of the company has reached a staggering billions of dollars, becoming the most awesome star start-up company in China this year! God, we are so lucky.
But we are also unfortunately, because at this time, the number of active users every day tens of millions, every day single table of new data up to 500,000, the total amount of a table has reached twenty or thirty million! Can’t carry ah! Database disk capacity is running out! Peak concurrency is staggering
5000 ~ 8000! Come on, brother. I promise you, your system won’t last until now. It’s down!
Okay, so you kind of get a sense of what’s going on with the split tables, and actually it’s going to follow your business, and the better your business grows, the more users you get, the more data you get, the more requests you get, and you can’t handle a single database.
table
For example, if you have tens of millions of data in a single table, are you sure you can handle it? No, the amount of data in a single table will greatly affect the performance of your SQL execution, and your SQL may slow down later. Generally speaking, as far as my experience is concerned, when the single table reaches millions, the performance will be relatively poor, and you will score the table.
What do you mean by a separate table? It is used to query data from one table into multiple tables. For example, to separate tables by user ID, one user’s data is placed in one table. And then when you do that you just do that table for a user. In this way, the amount of data in each table can be controlled within a controllable range, for example, each table is fixed within 2 million.
depots
What does separate library mean? Is you a library generally speaking, our experience, up to support 2000 concurrency, must expand, and a healthy single library concurrency value you had better keep at 1000 or so per second, not too big. Then you can split the data from one library into multiple libraries and access only one library.
This is called a separate list. Why do we have a separate list? You get the idea.
How exactly do you split the database vertically or horizontally?
Horizontal split means that the data of one table is divided into multiple tables in multiple libraries, but the table structure of each library is the same, but the data of each library table is different, and the data of all library tables adds up to the total data. The meaning of horizontal split is to evenly distribute data in more libraries, and then use multiple libraries to support higher concurrency, and also use the storage capacity of multiple libraries to expand.
Vertical split means to split a table with many fields into multiple tables or libraries. Each library table has a different structure, and each library table contains some fields. In general, fewer frequently accessed fields are placed in one table, and more infrequently accessed fields are placed in another table. Because the database is cached, the fewer rows you access frequently, the more rows you can cache in the cache, and the better the performance. This is usually done more on the surface.
There are surface split, is a table, a table into N tables, is to make each table data volume control within a certain range, to ensure the performance of SQL. Otherwise, the greater the amount of data in a single table, the worse the SQL performance. It’s usually about 2 million lines, not too much, but depending on how you do it, it could be 5 million, it could be 1 million. The more complex your SQL is, the fewer rows per table it is best to have.
What should I do if a high concurrency system is involved?
If you want to be more than just a low-level CRUD brick-lifting programmer, you need to master issues like high concurrency system design! Don’t panic, small make up 46 questions, throughout the whole high concurrency system design, involving: basic, database, cache, message queue, distributed, maintenance, practical training and other 7 parts of the content (and the answer to each question will be sorted out completely).
-
Why study high concurrency system design?
-
High Concurrency systems: What is its general design approach
-
Architectural layering: Why do we have to do it
-
System design Objective (1) : How to improve system performance
-
System design Objective (2) : How does the system achieve high availability
-
System design Objective (3) : How to make the system easy to expand
-
Is the interviewer giving you a hard time when asked how the components work?
-
Pooling techniques: How can you reduce the performance cost of frequently creating database connections?
-
Database optimization scheme (a) : when the query request increases, how to separate master from slave
-
Database optimization scheme (2) : how to realize the sub-database sub-table when the amount of written data increases
-
Number sender: how to ensure the global exclusivity of ID after sub-database sub-table?
-
NoSQL: How do databases and NoSQL complement each other in high concurrency scenarios?
-
Caching: How can queries for dynamic data be accelerated when the database becomes a bottleneck?
-
Cache usage posture (1) : How to choose the cache read and write strategy?
-
Cache usage posture (2) : How to make cache highly available?
-
Cache usage posture (3) : What if the cache is penetrated?
-
CDN: How can static resources be accelerated?
-
How should data migration be done?
-
Message queuing: How to handle the tens of thousands of order requests per second in seckill?
-
Message delivery: How to ensure that messages are consumed only once?
-
Message queuing: How can message latency be reduced in message queuing systems?
-
What is the interviewer looking for when asked about project experience
-
Starting from the “heart”, I still have countless possibilities
-
High concurrency system design midterm test problem analysis
-
System architecture: Should a system with 10,000 requests per second be servified unbundled?
-
Microservice Architecture: How to transform the system architecture after microservice?
-
RPC framework: How to implement the service invocation at the millisecond level under 100,000 QPS?
-
Registry: How are distributed systems addressed?
-
Distributed Trace: How do I troubleshoot slow requests that span dozens of distributed components?
-
Load balancing: How can systems scale horizontally?
-
A P I Gateway: What about the facade of the system?
-
Multi-room Deployment: What about distributed systems across geographies?
-
Service Mesh: How to mask the Service governance details of a servified system?
-
Adding eyes to the system: How to do server-side monitoring?
-
Application Performance Management: How should the user experience be monitored?
-
Pressure test: how to design a full-link pressure test platform?
-
Configuration management: How to manage thousands of configuration items?
-
Degraded fuses: How to shield non-core System failures?
-
Flow Control: How do we manipulate traffic in high-concurrency systems?
-
How do you prepare for a technical interview?
-
Counting system design (a) : how to do in the face of mass data counter
-
Counting system design (2) : 500,000 Q PS how to design not reading system
-
Information Flow design (I) : How to do the reasoning model of general information flow system
-
Information Flow design (II) : how to do the pull mode of the general information flow system
-
How to find and troubleshoot problems with high concurrency?
-
How do we prepare against peak traffic?
Answer Analysis Original (As shown in the figure below, there are too many high-concurrency system design documents of nearly 400 pages. It is impossible to upload all the answers, but the original answers can be shared with you who are interested in learning.)
In addition, how to liver MySQL?
If you want to be a Java senior programmer or even a Java architect, you want to get an offer from Ali P7-P8, put the following contents into your mind first!
The original answer analysis (as shown below, there are too many 64 pages of 167 ultra-high frequency MySQL interview documents, so we cannot upload all the answers, but we have sorted out the following documents)
In order to become an advanced programmer, this MySQL advanced Knowledge notes handwritten document (12 chapters) should also be missed!!
-
Key features of MySql
-
MySQL installation
-
Mysql privileges
-
MySql data type
-
Mysql architecture
-
The storage engine
-
The lock
-
The transaction
-
Business design
-
The slow query
-
Index and execution plan
-
SQL optimization
Interview assault MySQL, even if it involves high concurrency system design content, do not panic! Calm! Calm!
MySQL advanced Knowledge Notes: Java Advanced Architecture interview analysis, high concurrency system design 46 questions, 167MySQL high frequency question analysis, MySQL advanced knowledge notes documentation can be shared as a benefit to you!
Also please note: the words of recognition to support a wave of forwarding! Code words are not easy! Thanks a million!
Good good study, day day up! I wish you a bright future and a continuous Offer!