Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to optimize the data table in the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn how to optimize the data table in the database.

Introduction

When paging, MySQL does not skip the offset line, but takes the offset+N row, then discards the previous offset row and returns N rows. For example, limit 10000, 20. After mysql sorting takes out 10020 pieces of data, only 20 pieces of data are returned, and the cost of query and sorting is very high. When the offset is very large, the efficiency is very low, so we have to rewrite the sql.

Use bookmarks

Use bookmarks to record the location of the last data, and filter out some of the data

Such as the following statement

SELECT id, name, description FROM film ORDER BY name LIMIT 1000, 10

Can be changed to

SELECT id, name, description FROM film WHERE name > 'begin' ORDER BY name LIMIT 10

Name is the maximum value since the last paging. Note that this scenario is only applicable to scenarios where there are no duplicate values.

Delayed correlation

Delayed association: by using the overlay index query to return the required primary key, and then associate the original table according to the primary key to get the required data

SELECT id, name, description FROM film ORDER BY name LIMIT 100,5

Id is the primary key value, and name has an index on it. In this way, each query will first find the id value from the name index column, and then go back to the table and query all the data. You can see that it is not necessary to return a lot of tables. You can find the id on the name index first (note that only querying id will not return the table, because the values contained in the nonclustered index are index column values and primary key values, which is equivalent to getting all the column values from the index, so there is no need to go back to the table), and then associate the table again to get all the data

So it can be changed to

SELECT film.id, name, description FROM film JOIN (SELECT id from film ORDER BY name LIMIT 100J. 5) temp ON film.id = temp.id

Reverse order query

If you query the last page, offset may return very large

SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10

Is it much faster to change to paging in reverse order?

SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10; thank you for reading, these are the contents of "how to optimize the data tables in the database". After the study of this article, I believe you have a deeper understanding of how to optimize the data tables in the database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.