当前位置：网站首页>MySQL principle and optimization: Limit the query optimization

MySQL principle and optimization: Limit the query optimization

2022-08-09 16:03:00 【InfoQ】

Suppose there is a table tb_sku whose tableThe structure is as follows.

Approximately in the tableThere are 200w records, it takes about 4.36s to execute the following sql statement to return data

select count(*) from tb_sku;

Then we use to paginate it:

select * from tb_skulimit 0,10;

limit statement where 0 represents the starting position and 10 is the amount of data returned per page.

As shown abovedisplay, the query results are returned soon.

Then we use the SQL statement

select * from tb_sku limit 10,10;

The statement starts from the record position 10 and returns 10 items downrecord, that is, the information on the second page.The return time is also faster.

Then, we increase the starting position to 100w as follows,

select * from tb_sku limit 1000000,10;

At this time, the return time takes 0.74 s, which shows that the use of limit is not suitable for largeThe table with the amount of data is paginated, and the later the position is, the less efficient it is.Take the above example, limit will sort 100w data first, then return 10 data, and only return 100w to 100w zero 10 records, other query records will be discarded, this method of query sortingThe price is very high.

Therefore, we need to optimize the limit operation of the large data scale. The official solution is to use the method of

covering index and subquery

Optimization

According to this idea, first query the id

select id from tb_sku order by id limit 1000000,10;

The query result only takes 0.34s which is much faster than the previous 0.74s.The reason is that because the information of the id is directly returned, there is no return table operation, so the speed of select * is faster.

Because we need to obtain the information of select *, that is, the information of all fields of tb_user, soThe above query results need to be jioned with tb_user.

select s.* from tb_sku s,(select id from tb_sku order by id limit 1000000,10 ) t where s.id = t.id;

The query result is shortened here by query id and sub queryIt is 0.38s, which is twice the query time compared to the previous way of directly using select *.

原网站

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/221/202208091446469195.html

当前位置：网站首页>MySQL principle and optimization: Limit the query optimization

MySQL principle and optimization: Limit the query optimization

边栏推荐

猜你喜欢

随机推荐