One . Interview questions and Analysis

1. Interview questions of the day

Limit 20000 How to solve the problem of slow loading ？
The query is very slow. What's the reason ？ Solution ?
How to locate slow SQL？
How to query 100000 pieces of data ?

2. Problem analysis

Yige In the last article , We have carefully analyzed the contents of the index , I believe you can learn the function of the index 、 Advantages and disadvantages 、 The principle and creation of index , Yige I hope you must firmly grasp the relevant contents of the index , Because this is related to the optimization of the database . Our content today , The main reason is to analyze the reasons for the slow query , Among these reasons , Some have something to do with index failure . Of course , The reason for the slow query , Not just because of the index , There are other factors . So Yige will pass today's article , To analyze these factors , And the corresponding solution .

Two . Limit 20000 The problem of slow loading

Yige In the previous article , Explained in detail SELECT The execution order of query keywords , One of them is about limit Introduction of keywords , We know that this keyword is used for paging query . If you are not familiar with the keyword , You can refer to the previous content ：
High paid programmers & Interview questions series 81 Let's just say SQL Detailed explanation of the execution order of query statements - Function article ( On )
High paid programmers & Interview questions series 82 Let's just say SQL Detailed explanation of the execution order of query statements - Principles ( Next )

1. limit The problem is

In the previous post , Yige Just explained to you limit The role and existing problems of . So we can use that limit To achieve paging effect , For a small amount of data , Use LIMIT No problem ; but When the amount of data is very large , Use LIMIT n, m It's very inefficient , This is because LIMIT Every time I scan from the beginning . So if you need to start from 10 The journey begins , Read 5 Data , You need to locate the first 10 Line ten thousand , Then page reading . This is a very inefficient scanning process , Because every time the database needs to be scanned N + M Bar record , Give up the previous N Bar record , Costly .
Now come back to our question , namely ：

select * from table limit 20000,10;

here limit10000,10 We'll scan those that meet the conditions first 20010 That's ok , Get rid of the front one 20000 That's ok , Back to the last 10 That's ok . That's the problem , You just 10 Data , But I have to scan those useless in front of me 20000 strip , Isn't this a serious waste ？ Can't it be slow ？ Then how to solve this problem ？ Let's move on .

2. Solutions

since limit Paging has the above problems , So what's the solution ？ Yige The solutions provided to you are as follows , For your reference ：

Cache the front end ： Through caching , Reduce the direct query operation to the database , Can pass redis Implementing caching , Or by ElasticSearch Build a search system to realize ; Use deferred correlation + Subquery ： That is, first use limit Get the index field that needs data , Then, the required data is obtained by associating the original table with the index field , Like the following SQL sentence ：select a.* from table_a a,(select id from table_b limit 100000,20) b where a.id = b.id; Record the maximum of the last query id ： If id Is a continuous , Each time we can find the largest id recorded , Like the following SQL sentence ：select id,name from users where id>100020 order by id asc limit 20; Here it is SQL In the sentence, we added order by, And give order by Field indexing ; Use join The query ： We can also put SQL Change the sentence , Use join The query , This execution efficiency is also much faster than direct query , as follows ：SELECT * FROM tableName AS t1 JOIN (SELECT id FROM tableName ORDERBY id desc LIMIT 20000,10) AS t2 WHERE t1.id <= t2.id ORDERBY t1.id desc LIMIT 10; Business realization ： For example, don't do too much paging , Only the first 100 page , Paging query is not allowed later ; Don't use limit N,M ： have access to limit N, take offset Turn into where Conditions .

Of course , These are just Yige Solutions for you , If you have better solutions , Leave a comment in the comments section , We all exchange and learn from each other .

3、 ... and . SQL Query log

Yige I said it before , Most operations in the database , In fact, they are all query operations , Instead of adding, deleting and modifying . If the amount of data in our database is not very large , General query statements can meet the requirements , But when there's a lot of data , The execution performance of different query statements may vary greatly . So in order to improve the query performance of the database , We have to carefully analyze what we write SQL Query statement , Find out the slow execution SQL Query statement , Transform it , To improve query performance . Next Yige Analyze this problem , Find out what caused SQL The reason why it slows down , And give solutions .

1. brief introduction

To solve the problem of slow SQL, First of all, we have to understand what is slow SQL. In fact, you can guess from the name , The query must be very slow SQL Chant . But here's the problem , This ” speed “ It's not a fixed standard , It belongs to relative concept , In other words, how slow is it ？ Never Yige Say it's slow, it's slow . Actually in MySQL Has set a general standard for us .
MySQL As a database system , Many aspects have been taken into account , It itself provides a logging of slow queries , Used to record MySQL That exceeds a certain threshold SQL sentence . Just one SELECT The execution time of the statement exceeds that in the log long_query_time The specified value , We can put the SELECT Statements are considered slow SQL, Then the statement will be recorded in the slow query log . By default , long_query_time The value of is 10 Second , in other words , Just one SQL The execution time of is longer than 10 second , It is slow SQL. Of course , This is a MySQL Official reference values , We can root # Make changes according to your own project requirements .

2. View slowly SQL threshold

That slow SQL Is the threshold of 10 In... Seconds ？ We can view it through the following statement ：

# Slow query sql Time set value show variables like 'long%';

The execution result is shown in the figure below ：

3. Slow change SQL threshold

By default ,MySQL It doesn't open slowly SQL Query log function , So if we want to detect slow SQL, You need to turn on this function first . Let's first check whether the slow... Is currently on SQL Log function , as follows ：

# Slow display sql The opening status of the log file and the storage location of the log file show variables like 'slow%';

The results are shown below ：

You can see , The log query function is not enabled by default , So we need to turn on this function first , The statement is as follows ：

# Turn on the query log function set global slow_query_log = 1;

But you should pay attention to , Here we open the slow query log , Only effective for the current database , It fails after restarting the database . If we need permanent effect , Need modification MySQL Profile of the database my.cnf.

[mysqld]# Enable the global query log function slow_query_log=1slow_query_log_file=E:\mysql-5.7.26-winx64\data\YYG39C2-slow.log# Set the global log query threshold time long_query_time=3log_output=FILE

After setting , Remember to restart mysql The server . After the query log function is enabled , We can see how many statements have been added to the slow SQL In the query log .

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4. Change the slow query time threshold

Yige It was said that , slow SQL There is a time threshold , The default is 10 second , We can modify this value , We can use the following SQL Statement to modify ：

# Modify the slow query time threshold set global long_query_time=3

Of course you can my.conf Global modification of configuration in the file , As in paragraph above 3 The section says ！

5. Turn on the query statement logging function without index

If we want to improve query efficiency , Index is often used , But a query statement , Whether the index is used or not , Sometimes we don't know , Is there any way to know ？ therefore MySQL To solve this problem , We also set up a log , Queries that do not use indexes can be recorded in the log . So we can also open this option here to facilitate later SQL Statement tuning . After the parameter is turned on ,full index scan Of SQL Statements will also be recorded in the slow query log .

#  Check whether the function is enabled show variables like 'log_queries_not_using_indexes';#  Turn on the index logging switch set global log_queries_not_using_indexes=1

Four . slow SQL

In the chapter above , Yige I've explained it to you SQL Relevant contents of log records , Next, we will analyze why we should optimize slow SQL, And slow SQL How did it happen and how to solve it .

1. The principle of production

Some of them will say , It's slow SQL There is , Why don't you let it stay there ？ Of course that's not good , Why? ？
Because this is what we do SQL Statements consume a certain amount of I/O resources ,SQL The speed of statement execution determines I/O The length of time the resource is occupied . Suppose our total resources are only 100 individual , One is slow SQL Occupied 30 A resource , It cost 1 minute , So here 1 Within minutes , other SQL The total amount of resources that can be allocated is only 70 individual . So when resources are not allocated enough , Other new SQL Statement to execute , You can only wait in line . And one is slow SQL Longer execution time , This means that there will be a long blocking wait , The user experience is also poor .
So we need to be slow SQL Governance optimization , That slow SQL How did it happen ？ have a look Yige Here are some common reasons to summarize .

2. Causes and solutions

The reason for the slow execution of database operations is not the only , The common reasons are as follows ：

SQL Statement problem ; Database and table locking ; The server hardware configuration is low ;MySQL It's faulty ; Other reasons

But for our average programmer ,MySQL When optimizing, you don't need to consider the configuration parameters of the database, hardware and other factors , These are more DBA Responsibility for , We mainly consider a certain SQL Whether there is a problem with the statement , And the time occupied by the execution of the statement I/O and CPU Resources . Generally speaking , The main factors affecting resource occupation are ：

The larger the amount of data in the table , Needed I/O The more times ;
How to read data ： cache 、 Indexes 、 Direct disk read ;
Data processing method ： Is the data sorted 、 The subquery etc. .

And for us programmers ,MySQL Too many resources 、SQL The solution to the slow execution of statements is roughly ：

Cache the data appropriately ： When the amount of data is small , The change frequency is not high , But when the frequency of visits is high , Consider storing data in Redis Wait for the cache ; Appropriate merger I/O ： Separately select c1 from t1 And select c2 from t1, And execution select c1,c2 from t1 comparison , The latter costs less ; Reasonable distributed architecture ： When dealing with massive data , Consider combining data with I/O Distributed to multiple hosts for execution .

here Yige We analyzed the causes of SQL Common reasons for slow implementation and solutions , today Yige I won't explain how to implement caching and distributed architecture . Because for general development , The most common cause of database slowness is that we write SQL There is a problem with the query statement , For example, the index fails 、 Too many table Association queries 、 The amount of data in a single table is too large . therefore Yige Today is mainly for SQL Slow query caused by statement , Give you some solutions .

3. Prevent slow SQL

This article focuses on SQL Slow due to improper statements SQL, If you can make these contents clear , This interview basically passed .
《 The yellow emperor's Canon 》 In the said , ” Cure the sick , Traditional Chinese medicine treats desire disease , I'll treat my illness “ , The same is true for our development , The most important thing is to prevent the occurrence of faults . So since it's because SQL Slow response caused by query statements SQL, We must first find ways to optimize SQL sentence , Improve query efficiency . as long as SQL Well written , Slow... Can be prevented naturally SQL Produced , Next Yige First, put some effective SQL Optimization techniques to tell you .

3.1 Avoid index invalidation

Yige In the last article, I told you , To improve our query efficiency , Using indexes is a very effective means , So we need to build an index , And ensure that the index does not become invalid . If you haven't seen Yige In my last article , Please turn left and turn forward ：
《MySQL What are the indexes ？ Are you familiar with the indexing principle ？ Under what circumstances will the index fail ？》
In the last article , Yige I told you , We need to make sure that the index doesn't fail , In this way, the execution speed of our query statements can be really fast , Therefore, those operations that may lead to index failure should be avoided , As shown below ：

l ike % Will invalidate the index ; * Wildcards will send Index failure ; NOT、!=、<>、NOT IN、NOT LIKE Such negative conditions will invalidate the ordinary index ; The index column participates in the calculation (+、-、*、/ Wait for the operation ) Will invalidate the index ; Using functions on indexed column fields may invalidate the index ; Perform on the field null Value judgment will invalidate the index ; Use or The connection invalidates the index ; Violating the leftmost prefix principle will invalidate the composite index ; Inconsistent data types invalidate the index ; > < Improper range query will invalidate the index ; order by Improper use will invalidate the index ;

These operations , We should try to avoid , This avoids index invalidation ,SQL The query speed can naturally be improved .

3.2 Optimize table structure design

Is our query efficient , It also depends on whether the table structure we design is good , A badly designed watch , Queries cannot be fast . When we design the watch , Try to meet the following characteristics ：

In case of meeting the demand , Try to use small data types ; Use as much as possible tinyint、smallint、mediumint As an integer type , Instead of int; Use as much as possible not null Define fields , because null Will occupy 4 Bytes . The number type defaults to 0 , String defaults to ""; Use less as far as possible text type , When you have to use it, you'd better make a separate table ; Use as much as possible timestamp, Instead of datetime; Don't have too many fields in a single table , It is suggested that 20 Within fields .

3.3 Other matters needing attention

use exists Instead of in, such as select num from a where exists(select y from b where num=a.num); Avoid frequent creation and deletion of temporary tables , To reduce the consumption of system table resources ; use join Instead of in,join Is more efficient than using in, because join There is no need to create a temporary table ; Try to avoid large transaction operations , Improve system concurrency ; Optimize paging queries ： Reference resources Yige Front facing limit Keyword optimization content ; Other matters needing attention

4. Positioning solution is slow SQL

If our prevention work is not done well , Or lead to slow SQL, Then don't be afraid , Just find a way to solve it .

4.1 Governance priorities

When we want to manage slow SQL when , If there are many places where slow SQL, And our time and energy are limited , What to do at this time ？ We don't want to scratch our eyebrows and moustaches , We should still focus on . Generally speaking , Slow in positioning SQL when , The following governance priority principles should be followed ：

If there is master-slave separation , First solve master Main library , Solve again slave Slave Library ; Many times SQL Give priority to governance ; If a SQL Statement will access a table with high concurrency , Priority should be given to governance .

4.2 View slow query log

First of all, we need to confirm where the slow SQL, Then I'll prescribe the right medicine , Find where the problem arises , To find a solution . Yige In the third chapter , I have already explained how to open SQL Query log function , Let's turn forward . Because it's necessary to be slow SQL Query log, as I mentioned earlier , Here I will directly query how many slow messages are generated SQL sentence , as follows ：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

The results are shown below ：

If there is no slow SQL,Slow_queries Of value The value is 0. Of course , If it's slow SQL,value That's the number of entries .
Next we can write mysql slow Statement to test whether to write slow data ：

mysql> select sleep(10) as a, 1 as b;

The results are shown below ：

Then we can go to mysql Database data Directory , Open the previously configured mysql-slow.log Log files , The contents are as follows ：

We can see , Now? slow.log Log files , Has successfully slowed down SQL Recorded it , In this way, other execution will exceed 3 Seconds SQL Statements will be recorded in this log file , We can analyze the log file to check the slow SQL 了 .
In addition to using the above methods, we can locate slow SQL outside , You can also use third-party tools , such as APM Come on, slow down SQL Statement monitoring , here Yige No more explanation .

4.3 install ActivePerl

because Yige Is in windows Interview questions explained to you in the environment , Now let's analyze the slow SQL, In addition to opening slowly SQL In addition to the function of querying logs , We need another MySQL Built-in tools --mysqldumpslow.pl, The order is in MySQL Of bin Under the table of contents , As shown in the figure below ：

mysqldumpslow.pl It's a perl Script file written , By default windows It can't be carried out . We need to install it first ActivePerl, This is a program that allows you to execute Perl The tool software of the program , So we need to download and install ActivePerl Tools . as for ActivePerl Download and install process for , You can do it by yourself , It's a fool installation , There is no difficulty , After installation, you can execute mysqldumpslow.pl The script commands .

4.4 mysqldumpslow.pl command

mysqldumpslow Common parameters of the command are as follows ：

--help Output help information ;-v Output details ;-d debugging ; -s In what order , The default is 'at', The display order is in reverse order ; al: Average watch lock time ;ar: Average number of result lines ;at: Average query time ;c: frequency ;l: Lock table time ; r: Sum up the number of rows ; t: Total query time ;-r Positive sequence sort , That is, sort from small to large ;-t NUM Limit the number of items displayed ;-a Display numbers and strings , The default number is N String is 'S';-g PATTERN Filter strings , Followed by regular expression , Such as '10$' With 10 Conditions for ending .

Yige Execute the following command here ：

# Query to get the most recordsets 10 individual SQL# This command is followed by your previously configured slow SQL Log file location is OK mysqldumpslow.pl -s r -t 10 ..\data\YYG39C2-slow.log

The execution result is shown in the figure below ：

We can also use the following command ：

# The most visited 10 individual SQLmysqldumpslow.pl -s c -t 10 slow.log# Get the top... In chronological order 10 There are left connected query statements in the bar mysqldumpslow.pl -s t -t 10 -g "left join" slow.log

In this way, the above tools are used to execute , We can know how many and which are slow now SQL 了 , This is for our follow-up SQL The analysis laid the foundation .

4.5 show profile

We can also use another tool --Show Profile, This is a MySQL Provided to analyze a SQL Command of the resource consumed during statement execution , It can be for SQL The tuning of statements provides quantitative indicators . But the command is off by default , We need to turn this function on manually , After opening, you can save the query SQL The execution details and life cycle of statements in the server .
4.5.1 see show profile Opening status

SHOW VARIABLES LIKE 'profiling';

The results are as follows ：

We can see , By default, this function is not enabled .
4.5.2 Turn on show profile function
Next, let's turn on show profile function , Easy on the back SQL analysis of sentences .

SET profiling=ON;

So it turns on show profile function , If you check the on state again , You can see that profiling=ON 了 .
4.5.3 see SQL Execution history
Next, we can use the following command to view profiles situation .

show profiles;

This command can take the SQL Write down all the sentences , For example, the following execution results ：

From the execution result , We can see that there are many Query_ID, Let's find out what's here first ID value , Then you can use these ID Value for further query analysis .
4.5.4 The diagnosis SQL
Use the above query Query_ID, Next we diagnose SQL The resources consumed by the statement .

show profile cpu,block io for query  A certain... Found above Query_ID value ;

We can in the above statement , Add and execute the following different commands ：

ALL： Show all overhead information ;
BLOCK IO： Show IO Related expenses ;
CONTEXT SWITCHES： Show context switching related overhead ;
CPU： Show CPU Related expenses ;
IPC： Show the cost of sending and receiving ;
MEMORY： Shows memory related overhead ;
PAGE FAULTS： Display page error related overhead ;
SOURCE： Display and Source_function,Source_file,Source_line Related expenses ;
SWAPS： Displays the cost associated with the number of exchanges

For example, we can put the top Query_ID=39 Of SQL Sentence analysis , As shown below ：

When executing the above order , If we encounter the following situations, we need to consider optimization ：

Converting HEAP to MyISAM： Query result is too large , Out of memory ; Creating tmp table： Create a temporary table ; Copying to tmp table on disk： Copy the temporary table in memory to disk ; locked： There's a lock .

4.6 explain Analysis of the slow SQL sentence

In this way, through the above series of steps , We can know how many slow SQL, And what's slow SQL 了 . Next, we can use another explain command , To further analyze , See what causes the slow SQL The birth of .explain Can simulate optimizer execution SQL Query statements , So they know MySQL How to deal with SQL Of the statement , Then it analyzes the performance bottleneck of query statement or table structure . adopt expalin Command, we can get the following information ：

Read order of tables ; The operation type of the read operation of the table ; Which indexes can be used ; Which indexes are actually used ; References between tables ; How many rows per table are queried by the optimizer

This way explain, We'll know if we hit the index , Is there a temporary table . About explain Use details of keywords , I explained it very carefully in my offline course , Yige There is no detailed explanation here , I will explain it in the next article .

5. MySQL Server parameter tuning

If the above measures are taken ,SQL The execution effect of the statement is still not ideal , For example, when order by and group by When the index cannot be used , Consider increasing MySQL Server's max_length_for_sort_data and sort_buffer_size And other parameter settings .

6、 ... and . Conclusion

The above is our target for slow SQL Various preventive and solving measures taken , But because MySQL Optimization itself is a very huge and complex work , Just one article , We can't fully explain the relevant contents , Here we are mainly to sort out the core content of the interview . If there is something wrong in the text , Also ask everybody not hesitate to grant instruction ！
thus , Yige Just lead to SQL The reasons for the slow query and the solutions are explained in detail , Can you now ？ If you still don't understand , You can give a comment to Yige Leave a message . Originality is not easy. , If you think this article is good , You can give Yige Like it , Your support is my motivation to continue to create ！
author ： One brother Sun

Game programming ️, A game development favorite ~

If the picture is not displayed for a long time , Please use Chrome Kernel browser .