Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the optimization of SQL?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Most people talk about SQL optimization.

Ah Fan has interviewed some people for the company before, because he interviewed with the boss before, so he has seen a lot of people's resumes, and many people have written about the database on their resumes.

Proficient in MySQL,SQLServer, familiar with Oracle, familiar with SQL sentence optimization.

Indeed, if you have not carried out in-depth understanding of the database, you do not dare to write above, only to write about the optimization of SQL statements, but very often, after you finish writing this SQL statement, you will not take the initiative to optimize, most of the time it is stopped, there is no problem, I will not change, as long as the function is realized, then everything is fine.

And this article, tell you, don't just know how to optimize SQL statements.

SQL sentence optimization during our interview, the interviewer saw that you wrote SQL sentence optimization, and sometimes asked questions, so tell me about the aspects of SQL sentence optimization.

There is a lot about SQL statement optimization.

In order to optimize the query, we should avoid full table scanning as far as possible, and we should first consider establishing indexes on the columns involved in where and order by.

Try to avoid using the! = or operator in the where clause, otherwise the engine will abandon the use of indexes and perform a full table scan.

Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to give up using the index and do a full table scan.

.

There are many SQL statement optimizations like this, but have you noticed what I marked above, the engine? I'm sure you all know that before, but we haven't done too much research, but for the sake of your interview, let's step up our analysis of this.

MySQL system the following full text, all according to MySQL analysis, analysis engine, we first from the MySQL analysis. The architecture diagram of MySQL is as follows:

We can see a little bit from the picture, such as the components of MySQL.

Connection pool component

Manage services and tool components

SQL interface component

Query Analyzer component

Optimizer component

Buffer (Cache) component

Plug-in storage engine

Physical file

I have to say, this plug-in storage engine summed up is extremely incisive. One of the most important features that distinguishes MySQL database from other databases is its plug-in table storage engine.

But the most important thing to note is that the storage engine is based on tables, not databases.

MySQL storage engine

Storage engine is the most important feature that distinguishes MySQL from other databases. Each storage engine has its own characteristics, different characteristics, and will be used in different scenarios. Although we often use one in development, it is the most powerful to be able to build different storage engine tables according to specific applications.

So let's see which storage engines MySQL supports and manually enter the query statement show engines into its own MySQL, as shown in the following figure.

Do you feel quite a lot after reading it, but the nine storage engines of MySQL have their own characteristics, and then according to the different needs, we can choose whether we have found another good opportunity to bring a sister? let's start to analyze it.

1.FEDERATED storage engine

I saw this when I was flipping through books before Ah Fan said that this engine is not an engine for storing data, but a point to a remote MySQL database server. What does that mean? in fact, there is a big vernacular: "I don't store table structure files and data files here, but I store them remotely." at this time, there is a more interesting place, as shown below:

As shown in the figure, the FEDERATED storage engine is divided into two parts, one is the local service and the other is the remote service, so if you switch to this engine and he is executing CRUD, he will send the command to the remote server, and then send it back to the local server, and then return the matching line from the local server.

Here, A Fan does not focus on this, because the focus is the one we use most often, as we all know, that is the InnoDB storage engine.

2.InnoDB storage engine

InnoDB storage engine is also the one that you can often talk to the interviewer during the interview, because it is the default database storage engine. Note that this default starts with MySQL 5.5.8

There are many characteristics, so let's start to analyze what the characteristics are.

Support transactions. The default transaction isolation level is repeatable, which we use a lot, so we must all know it.

Support for foreign keys, which we all know has both advantages and disadvantages. After all, the role of foreign keys is there (pros: increase readability, if there is a downtime, maximize the consistency and integrity of data, disadvantages: reduce the query speed of the table, if the data is too large, then you may insert database data for ten times longer than if you do not increase foreign keys)

Row locks are designed to support higher concurrency, which is why interviewers sometimes say that you are overqualified for ES, because MySQL itself can handle so much.

Use multi-version concurrency control (MVCC) for high concurrency and implement four isolation levels of the SQL standard, which defaults to the REPEATABLE READ level.

Use a strategy called next-key locking (some call it Gap Lock) to avoid phantom

Data storage uses clustered, and each table is stored in the order of primary keys.

InnoDB's index structure is different from other MySQL storage engines. Clustered indexes have very high query performance for primary keys. At this time, there must be a restriction that if there are more indexes on the table, the primary key should be as small as possible.

InnoDB supports true hot backup, that is, online hot backup, through a number of mechanisms and tools.

The data is stored in a table space (tablespace), which is actually a black box managed by InnoDB and consists of a series of files.

Architecture of 2.1InnoDB

As we can see from the figure above, the InnoDB storage engine has many blocks of memory, which can be thought of as a large memory pool, that is, a thread pool is similar.

Now that we see the background thread in the picture, what is this background thread?

The InnoDB storage engine is a multithreaded model, so there are several different background threads in the background that handle different tasks.

The background thread is also divided into two parts, one is the core thread, the other is the IO thread.

Core thread Master Thread

IO thread IO Thread

Purify thread Purge Thread

Clean up thread Page Cleaner Thread

Core thread

The role of the core thread is to asynchronously refresh the data in the buffer pool to disk to ensure the consistency of the data.

IO thread

The IO thread is simple and is mainly used for callback processing of IO requests.

Purify thread

The main function is to recycle undo pages that have been used and allocated after the transaction is committed.

Clean up thread

His role is to refresh dirty pages in previous versions into a separate thread.

If A Fan could give these contents to the interviewer during the interview at that time, I believe that the salary would certainly be a little more.

3.Memory storage engine

The Memory storage engine actually stores the data in the table in memory, and if the database restarts or crashes, the data in the table will disappear. In other words, if your data is stored in the Memory storage engine, if the power is accidentally lost in the computer room, everything in it will be gone, just like if you use a rm-rf, but one is passive and the other is active.

This kind of use is relatively less, A Fan will no longer introduce to you, just talk about its characteristics, after all, a shutdown, directly gone.

TEXT and BLOB types are not supported. For string data, only fixed-length rows are supported. VARCHAR will be automatically stored as CHAR type.

Very fast, only supports table locks, poor concurrency performance, and does not support TEXT and BLOB column types

As soon as the server goes down, all the data disappears

Variable length fields (varchar) are stored as constant fields (char), so memory is wasted

4.MyISAM storage engine

This engine has obvious characteristics, it does not support transactions, but it supports full-text retrieval and should be oriented to some OLAP (online Analytical processing) database applications.

5.BLACKHOLE storage engine

This engine is just like his name, meat buns beat dogs, there is no return, and its use is relatively simple.

Verification of SQL File Syntax

Used to find performance bottlenecks that are not related to the storage engine itself

6.CSV storage engine

CSV storage engine actually operates a standard CSV file, and its characteristic is that it does not support indexes, that is to say, if it does not support indexes, then the efficiency is bound to be very low, which I believe many people will not choose to use it.

For these engines, A Fan said, now that we have learned so much, we must not continue to say that our SQL sentence has been optimized. When you are in the interview, you should be targeted.

If during the interview, the interviewer asks you: now there is a function, the feedback from the test is that the response time of this feature exceeds the expected value, what aspects do you take to deal with the problem?

This problem does not seem to be very difficult, but many problems can be extended according to this problem.

Question 1: if you start with SQL sentence optimization, you're bound to check the index, and the interviewer's next question may be why indexing makes it faster.

Question 2: if you talk about the configuration of the server, then the interviewer may think, for a functional feedback, you asked me to add configuration to the server, the cost is too high.

If you do not have a deep understanding of the index, at this time you can optimize the SQL statement, and see if the database table engine can be modified, if so, then you can start to take these advantages of the SQL engine in your own direction.

This is the end of the content of "what are the optimizations for SQL"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.