Why do code standards require SQL statements not to have too many joins?

Free points

Interviewer : Have you ever used Linux?

Me : Yes

Interviewer : I want to check the memory usage. What command should I use?

Me : free or top

Interviewer : Then tell me what information you can see using the free command

Me : Well, as shown in the figure below, you can see the usage of memory and cache

• total total memory
• used memory used
• free free memory
• buff/cache used cache
• avaiable available memory

Interviewer : Do you know how to clean up the used cache (buff/cache)?

Me : em… I don’t know

Interviewer : sync; echo 3 > /proc/sys/vm/drop_caches can clean up buff/cache . Do you think it is good for me to execute this command online?

Me : (Free points, very happy) There are great benefits. By clearing the cache, we will have more available memory space, just like the little rocket of xx guard on PC, click it and it will release a lot of memory.

Interviewer : Em..., go back and wait for notification

SQL Join

Interviewer : Let's change the topic and talk about your understanding of join.

Me : OK (If I answer wrong again, it will be all over, so I’ll seize the opportunity)

review

join in SQL can combine the specified tables according to certain conditions and return the data to the client

The join methods are

inner join join

left join join

right join

full join

Interviewer : If you need to use join statements in project development, how can you optimize and improve performance?

Me : There are two situations, small data scale and large data scale.

Interviewer : And then?

Me : For

• The data size is small, so just put it all into memory.
• Large data size

You can optimize the execution speed of join statements by adding indexes. You can reduce the number of join by using redundant information. Try to reduce the number of table joins. The number of table joins in one SQL statement should not exceed 5.

Interviewer : So we can conclude that join statements are relatively performance-intensive, right?

Me : Yes

Interviewer : Why?

Buffer

Me : When executing a join statement, there must be a comparison process.

Interviewer : Yes

Me : Comparing two tables one by one is slow, so we can read the data from the two tables into a內存塊one by one. Taking MySQL's InnoDB engine as an example, we can definitely find the relevant memory area using the following statement: show variables like '%buffer%'

As shown in the figure below, the size of join_buffer_size will affect the execution performance of our join statement

Interviewer : Besides that?

A major premise

Me : Any project will eventually go online, and it is inevitable to generate data, and the scale of data cannot be too small.

Interviewer : That’s right.

Me : Most of the data in the database will eventually be saved to硬盤and stored in the form of files.

Take MySQL's InnoDB engine as an example

• InnoDB uses頁as the basic IO unit, and the size of each page is 16KB
• InnoDB creates an .ibd file for each table to store data

verify

Me : This means that we need to read as many files as we have to connect to the tables. Although we can use indexes, we still have to move the hard disk head frequently.

Interviewer : So frequent head movement will affect performance, right?

Me : Yes, don’t the current open source frameworks like to say that they have greatly improved performance through sequential reading and writing, such as hbase and kafka ?

Interviewer : That's right. Do you think Linux has optimized this? Hint: You can run free command again to see.

Me : Why is the cache taking up more than 1.2G?

Interviewer : Have you ever thought about

• What is stored in buff/cache ?
• Why does buff/cache occupy so much memory, while the availlable memory is still 1.1G ?
• Why can you clean up the memory used by buff/cache with two commands, but you can only release used by ending the process?

Taste, taste carefully

After thinking for a few minutes

Me : If you just release the memory used by buff/cache so casually, it means it is not important, and clearing it will not affect the operation of the system.

Interviewer : Not entirely true.

Me : Could it be? I remember a sentence in "CSAPP" (In-depth Understanding of Computer Systems)

The essence of the memory hierarchy is that each storage layer is a cache for the devices in the lower layers.

Translated into human language, it means that Linux will treat the memory as a hard disk cache

Interviewer : Now you know how to answer that easy question, right?

Me : I…

Join Algorithm

Interviewer : Let me give you another chance. If I ask you to implement the Join algorithm, what would you do?

Me : If there is no index, nested loops will do the trick. If there is an index, you can use it to improve performance.

Interviewer : Let’s talk about join_buffer What do you think is stored in join_buffer ?

Me : During the scan process, the database will select a table and put the data it wants to return and compare with other tables into join_buffer

Interviewer : How do you handle it when there is an index?

Me : This is relatively simple. Just read the index trees of the two tables and compare them. I will introduce the method of dealing with no index.

Nested Loop Join

Nested loops only read one row of data in the table each time. That is to say, if outerTable has 100,000 rows of data and innerTable has 100 rows of data, it needs to be read 10000000 times (assuming that the files of these two tables are not cached to memory by the operating system, we call them cold data tables)

Of course, no database engine uses this algorithm now (too slow)

Block nested loop

Block , that is, each time a piece of data is taken into memory to reduce I/O overhead

MySQL InnoDB uses this algorithm when no index is available.

Consider the following two tables t_a and t_b

When the join operation cannot be performed using the index, InnoDB automatically uses the Block nested loop algorithm.

Summarize

When I was in school, the database teacher liked to test me on database paradigms. It was not until I started working that I learned that performance should be the basis for everything. If redundancy is possible, then use it. If it is really not possible, then join if join really affects performance. Try increasing your join_buffer_size , or changing the SSD.

This concludes the article on why code standards require SQL statements not to have too many joins. For more information on why SQL statements should not have too many joins, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!