<>1. common problem

stay mysql On the basis of large amount of data , With the increase of page number in pagination , Query time will also increase the response . So when it comes to millions of data , We need to optimize the existing query code for reasonable and effective paging .

Normally , The page number is proportional to the query time
SELECT count(*) FROM `sys_user` result : 4344284 > time : 1.132s SELECT * FROM `
sys_user` LIMIT 10000,20 > time : 0.095s SELECT * FROM `sys_user` LIMIT 100000,20 >
time :0.181s SELECT * FROM `sys_user` LIMIT 1000000,20 > time : 1.302s SELECT * FROM
`sys_user` LIMIT 4000000,20 > time : 5.498s
<>2. Cause of occurrence

First we need to understand limit 4000000,20
Meaning scan meets the conditions 4000020 that 's ok , Throw away the one in front 4000000 that 's ok , Return to the last 20 that 's ok , That's the problem , In fact, we just need it every time 20 Data content of row , Then every subsequent query needs to be scanned for more than 400W+ that 's ok , The performance must be greatly reduced .

<>3. Solution

For the number of pages that exceed a specific threshold SQL rewrite :
SELECT a.* FROM `sys_user` a, ( SELECT id FROM `sys_user` LIMIT 4000000, 20 ) b
WHERE b.id = a.id > time : 0.699s
Extract statements into a reusable sql
SELECT a.* FROM surface 1 a, (select id from surface 1 where condition LIMIT 100000,20 ) b where a
<>4. principle

Using overlay index to query , Avoid returning to the table

explain : If a book needs to know 11 What is the title of the chapter , Will open No 11
The page corresponding to the chapter ? Just browse the catalogue , This directory is used to overwrite the index . The overlay index in our statement uses
SELECT id FROM `sys_user` LIMIT 4000000, 20
This one sql Get what we want to inquire directly 20 Of data id, Then go to the table to query its specific data

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?