Shulou(Shulou.com)06/02 Report--

What is MySQL paging optimization? in view of this question, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

MySQL paging optimization requirement background of check-in reminder push

Check-in reminder function, push service notice at 18:00 every day to remind users to check in. The object of the push is the users who checked in yesterday and did not check in before the push deadline.

Development process

Database table structure:

CREATE TABLE `cultivate_game_ signin` (`id`bigint (20) NOT NULL COMMENT 'key', `uid` DEFAULT NULL COMMENT 'user id', `signin_ time` bigint (20) DEFAULT NULL COMMENT' check-in time', `continue_ times` int (11) DEFAULT NULL COMMENT 'serial check-in', `singnin_ count`int (11) DEFAULT NULL COMMENT 'check-in', `activity_ id`bigint (20) DEFAULT NULL COMMENT 'activity id', `remind_ status` tinyint (4) DEFAULT' 0' COMMENT'0 open check-in reminder 1 turn off check-in reminder', PRIMARY KEY (`id`), KEY `idx_ uid` (`uid`), KEY `idx_ time` (`signin_ time`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' activity check-in table'

The first version of development:

Select * from cultivate_game_signin where signin_time betweent '2018-1-13 00 and' 2018-1-14 00 limit 0100

Execute in a scheduled task until all the data is traversed. After the first edition was launched, the demand was basically realized. But in December, an observation of job found that the mission lasted nine hours until the wee hours of the next day. In fact, in the pushcode anti-interference policy, the push after 21:00 is no longer valid, and users cannot check it. Meaningless and a waste of resources.

Second edition optimization:

According to the troubleshooting, due to the surge in the number of users, the target push users reached 60W. In order to increase the retention of users and keep the new users, push optimization is just around the corner.

Optimization process

The optimization mainly takes two points:

Thread pool optimization sets a reasonable number of threads, and the push is completed while ensuring the stability of the service. There is not much detail here.

Push interface optimization needless to say using MySQL paging, the continuous increase of offset will inevitably lead to a decline in interface performance and can not meet the demand.

Limit paging optimization:

Recommended paging: paging method 1: Select * from table WHERE id > = 23423 limit 11; # 10 page 1 (10 entries per page) Select * from table WHERE id > = 23434 limit 11; paging method 2: Select * from table WHERE id > = (select id from table limit 10000 Select 10) limit 10; paging method 3: Select * from table INNER JOIN (SELECT id from table limit 10000 SELECT id from table limit 10) USING (id) paging mode 4: program ID: Select id from table limit 10000 10; Select * from table WHERE ID in (123456 …)

It is not difficult to see that the optimization is mainly to solve the problem of excessive offset.

Explain select * from cultivate_game_signin where signin_time between 0 and 1540443740869 limit 99 no. 1 null 129940 using where

The increasing number of scans will cause performance degradation. Therefore, id is used in push optimization, as long as the id is orderly.

Improved SQL:

Select * from cultivate_game_signin where id > id signin_time betweent begin_time and end_time limit 100

Take id as a parameter and as the query condition for the next execution statement. The generation of large offset is avoided. The effect is also immediate, and all target users can be pushed in 30 minutes.

The guiding principle of paging, query hits the index and avoids large offsets.

The answer to the question about MySQL paging optimization is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.