SQL Query Optimization Strategy (Technical Note)

The time difference between users requesting data and getting responses should be minimized for a better user experience. In this article, I will note down some SQL performance optimizing strategies.

Vertical Slice

Vertical segmentation is to divide tables into different databases by modules, which is very common in the evolution of large websites. When a website is still very small, only a small number of people develop and maintain it, and all modules and tables are together. There is a need to divide the table by modules and functions.

Horizontal segmentation

As mentioned above, vertical segmentation only divides tables into different databases according to modules, but does not solve the problem of large data volume in a single table, while horizontal segmentation is to divide the data of a table into different tables or databases according to certain rules. For example, in a billing system, it is more appropriate to divide the table by time, because the system processes the data of a certain period of time. For SaaS applications, it is more appropriate to divide data by user dimension, because users are isolated from each other, and there is generally no case of processing multiple user data.

Let's review a few simple SQL texts:

1. count(expr)

count(column) and count(*) are completely different operations and represent completely different meanings.

• count(column) is the number of records in the result set whose column fields are not empty

• count(*) is how many records there are in the entire result set

2. String concatenation

The first sql has the same effect as the second sql.

SELECT * FROM book WHERE isbn = '1464540496384'
SELECT * FROM book WHERE isbn = CONCAT('146454049638','4')

3. Use limit 1 when only one row of data is required

The MySQL database engine stops searching after a piece of data is found, rather than continuing to find the next matching record.

SELECT * FROM book WHERE isbn = '1464540496384'; //0.060s
SELECT * FROM book WHERE isbn = '1464540496384' limit 1; //0.024s

4. Avoid SELECT *

The more data is read from the database, the slower the query becomes. And, if your database server and web server are two separate servers, it will also increase the load of network transmission. So, you should develop a good habit of taking whatever you need.

SELECT id,Name from book; //0.090s
SELECT * from book; //0.145s

5. Batch insert efficiency is higher than one-by-one insert

insert into book (NAME,AUTHOR,CATEGORY_ID,ISBN) VALUES('java','tom',2,'123456'); //0.074

insert into book (NAME,AUTHOR,CATEGORY_ID,ISBN) VALUES('javascript','lili',2,'7891011'); //0.076

insert into book (NAME,AUTHOR,CATEGORY_ID,ISBN) VALUES('c++','lulu',4,'789234'); //0.067s

insert into book (NAME,AUTHOR,CATEGORY_ID,ISBN) VALUES('java','tom',2,'123456'),
('javascript','lili',2,'7891011'),
('c++','lulu',4,'789234'); //0.065s

Reference:

Definition of Verticle Slice & Horizontal Segmentation: The Internet

Tags: #SQL