Compare the performance of query based on the number of paging data that meet the query conditions

Form writing habits together ！ This is my participation 「 Nuggets day new plan · 4 Yuegengwen challenge 」 Of the 21 God , Click to see the event details .

Antecedents feed

• As a front end , We don't just have to be able to write pages , Interface , Sometimes we need to use node To write some interfaces for the project . As the most common list interface , I believe we are all familiar , After all, this is the most commonly used .
  

• Ordinary list interfaces generally have conditional search , Pagination, etc . And as paging , As image java Such a big brother , The ecology is complete , General framework will help you do paging query , You don't need to do more by yourself .
  

• And as a newborn Nodejs, Although some frameworks are available , But I still prefer to write by myself sql sentence , And this is the time to encounter a problem ： It is during list query , Usually, we need to return the number of qualified data to the front end .
  

• After unremitting efforts, we found two solutions

  • One is to query paging data , Execute another statement to query all the conditions , Then get the total . It needs to be performed twice sql sentence .
  • One is through SQL_CALC_FOUND_ROWS Complex queries , Get paging data and total number at the same time . There are two statements , But there is only one implementation .

Comparison of solutions

I am here Navicat Premium Two statements are used on the tool to 1k、10k、100k、1000k、10000k The amount of data tested .

• The first statement template is ：select * from xxx limit 1,1000;select COUNT(*) from xxx;

• The second statement template is ：select SQL_CALC_FOUND_ROWS * from xxx;select FOUND_ROWS() total;

• notes ： The above example is just an ordinary query statement , If you use a connected table , Multi table query, etc , You can change it yourself .

• Simply use tools to query , Of the query sql Statement is a join table query ： give the result as follows

  • 1k data ：

    • The result of the first scheme ：

      • 

    • The result of the second scheme ：

      • 
  • 10k data ：

    • First option ：

      • 

    • Second option ：

      • 
  • 100k data
    • First option :
      • 
    • Second option ：
      • 
  • 1000k data
    • The first solution ：
      • 
    • The second solution ：
      • 
  • 10000k data
    • The first solution
      • 
    • The second solution ：
      • 

• summary ： According to the above results, we can see , Without considering the specific connection mode of the actual back-end to the database , Complex query, that is, the first query method, takes several times as long as non complex query .

summary

• Model ：MacBook pro m1pro chip ,10 Core CPU ,16G Memory .
• The above experiments are used when generating data npm-mysql Package to connect to the database , And insert data . The connection method is connection pool .
• Then it will combine mysql package , and mysql2 Package and real node Compare the two ways of connecting data query .
• The above experimental data are not enough to generalize , But it also has reference value . If other students have any suggestions, please speak enthusiastically .
• I hope the comment area is a peaceful technical exchange , Comments with strong personal negative emotions will be deleted .