Click “like” to see again, form a habit, wechat search [Three prince Aobing] to pay attention to the Internet tools of survival.
This article has been included in GitHub github.com/JavaFamily, there are a line of large factory interview complete test sites, information and my series of articles.
preface
I am a title party, all the name of the article is just my gimmick, we should have a humble heart, so I hope everyone with empty cup mentality to learn well, progress together.
I think you should be familiar with the database, I think no matter what you write, database even if not used or heard, is an indispensable part of our project system.
Ask a circle, friends around the company either use their own research, or all MySQL, so want to see Oracle friends may be disappointed, but it does not affect your understanding of the general knowledge of the database.
The body of the
Do you know the basic architecture of MySQL? Can you rough out this diagram for me on paper?
Okay, so let’s go through this in order. What is a connector?
The first step to query is to link to the database, and that’s when the connector connects to us.
He is responsible for establishing links with clients, obtaining permissions, and maintaining and managing connections.
When we connect, we do a TCP handshake, then we authenticate, and then we enter the username and password.
After verifying ok, we are connected to the MySQL service, but at this point we are idle.
How do I view the free connection list?
Show processList, the following is the result of executing the Command in my database table, where the Command column is displayed as Sleep, indicating that there is a free connection in the system.
The important thing to note here is that if the client of our database does not respond for too long, the connector will automatically disconnect. This time parameter is controlled by wait_timeout, which is 8 hours by default.
An error is reported when reconnecting after disconnection. If you want to continue, you need to reconnect.
Here’s an example from a book I read:
A friend in the government said their system was so strange that they had to restart their application every morning or fail to connect to the database, and they didn’t know what to do.
I analyzed that, according to this error prompt, it should be the connection time is too long, disconnected. The default timeout time of the database is 8 hours, and you usually get off work at 6 o ‘clock, after work, the system will not be used, until the next morning at 9 or even 10 o ‘clock to go to work, this time has been more than 10 hours, the connection of the database will be disconnected.
Yes, the problem occurs when the timeout expires and the person who wrote the code doesn’t notice it.
Make the timeout longer and the problem is solved.
In fact, we may not be able to access these parameters, but when we really encounter a problem, know the general usage of each parameter, so that you will not become a headless chicken.
Is there another way to do that besides relink? Because establishing a link is still more troublesome.
Use long connections.
However, there is a drawback here. Memory can fly very fast with long connections. We know that memory used temporarily by MySQL during execution is managed in connection objects.
It can only be released if the connection is broken, so if you keep using a long connection, it will result in OOM (Out Of Memory), MySQL restart, and frequent Full GC in the JVM.
What would you do about it?
I usually disconnect the long connection periodically, after using it for a period of time, or after executing a query that occupies a large amount of memory in the program, and then reconnect it when necessary.
Is there another way? That’s not an elegant feeling, little man.
After executing a larger query, you can re-initialize the connection resource by executing mysQL_reset_connection. This procedure is better than the previous one, and does not require reconnection, but initializes the state of the connection.
Do you know MySQL’s query cache?
When MySQL gets a query request, it goes to the query cache to see if the query has been executed before.
If you’re curious, the same statement is executed twice in MySQL. The first time is different from the second time, which is significantly faster because of the cache.
Just like Redis, any statement you have executed before is stored in memory as a key-value.
The query will take the statement to the cache query, if it can hit the cache value returned, if not hit the implementation of the following stage.
But I still don’t like caching because it does more harm than good.
Oh? What do you mean?
Cache invalidation is very easy, as long as there is any update to the table, the table will be all the query cache is not used, is directly cleared, or accumulated a lot of cache ready to use, but an update back to the original form.
This results in a horrendously low hit rate of queries. Only tables that are only queried and not updated are suitable for caching, but such tables tend to be rare, and generally are configuration tables of whatever kind.
So how do we do queries when we don’t want to use caching, or what do we do when we use caching?
You can display the call and set query_cache_type to DEMAND so that SQL does not use caching by default and SQL_CACHE is used if you want to use caching.
One trick is to check the SQL execution time in the database, but there may be a cache, we usually use SQL_NO_CACHE in front of the SQL to know the actual query time.
select SQL_NO_CACHE * from B
Copy the codeCaching has been deprecated since MySQL8.0, so you don’t need to worry too much about it now. The main reason is that my previous versions of MySQL are not high enough, so caching has always been there. I read about caching in the book “High Performance MySQL”, so I thought I would mention it to you.
What should I do after cache query?
If there is no cache hit, the statement will be executed. If there is no syntax error in the statement you wrote, this is the next point MySQL will care about.
So what would he do? If your statement has so many words and Spaces, MySQL needs to figure out what each string represents, whether it’s a keyword, a table name, a column name, etc.
And then you start parsing, and based on the results of the lexical analysis, the parsing will tell you if your SQL is right or wrong, and it will tell you if you’re wrong, and it will tell you where you’re wrong.
Once the analysis is correct, move on to the next step, the optimizer.
What is the main optimization?
Optimization is easier, because we may create many indexes when creating a table. One step of optimization is to determine which index to use, such as using your primary key index, union index or what is better.
There is also the optimization of the execution order. There are so many conditions, which table to check first, or association first, there will be many schemes, and finally the optimizer decides which scheme to choose.
Finally, it’s execution, and execution is left to the executor.
The first step may be the judgment of permissions. In fact, ONE point I am not sure about is that many companies I contact are self-developed online query systems. We cannot use Navicat to directly connect to the library, but can only operate on the web page. MySQL might be open by default, but we don’t know the IP.
If you know, you can add me on wechat on the public account [Santaizi Ao Bing].
When it comes to execution, check whether the condition is met line by line. If there is an index, the execution may be better. The line by line judgment is like the interface is defined in advance in the engine, so it is faster.
The slow log for the database has a ROws_EXAMINED field where you can see how many rows were scanned, and explain where you can see the execution plan, how many rows were scanned.
Yes, young man, the basic framework is still very clear, we will take a deeper look at the index and some of the mechanism next time.
Ok, I’ll see you next time.
The relevant data
Tip: There are a lot of materials that I prepared for this column, but they are all external links, or inappropriate sharing methods. My sister who runs the blog reminds me, so please go to the official account to reply [information].
conclusion
Basically I talked about the logical architecture of MySQL briefly, of course, if you are confident to understand, you will find that there are many details, I just said some common problems, this is ali Dinqi’s “MySQL In Action” train of thought.
I may be more theoretical knowledge in MySQL, can not do in-depth understanding of the point, if you have the opportunity to learn about it.
omg
This should be the first technical article I opened this year, originally wrote a little index things, but later in the idea of the time to think, or from the basic knowledge of the database with everyone familiar, and then to understand the index will be better.
In the New Year, I may try some new writing styles, not all of which may be in the way of interview. Of course, this is the interview style, because the new style has not been decided yet. I will try to record several episodes by way of video after April. If you have any opinions, please tell me on wechat.
Later nagging I also put the last, so that we have a good reading experience, anyway, what direct feedback with me, I pet fans, have arranged.
We must put on good clothes recently don’t catch a cold what, egg to see a disease, almost scared to pee, let you see his equipment:
I am Aobing, a tool to survive on the Internet.
Creation is not easy, do not want to be white piao, everyone’s “three even” is the biggest power of creation of C c, we will see you next time!
[Information] [interview] [resume] I prepared the interview materials and resume template, this article GitHub github.com/JavaFamily has been included, there are big factory interview complete test site, welcome Star.
The more you know, the more you don’t know