MySql deep paging problem solved

select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

-- Create two tables：Employee and department tables
-- Department list,Delete if it exists
drop table if EXISTS dep;
create table dep(
    id int unsigned primary key auto_increment,
    depno mediumint unsigned not null default 0,
    depname varchar(20) not null default "",
    memo varchar(200) not null default ""
);

-- Employee List,Delete if it exists
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);

-- Create two functions：Generate random strings and random numbers
-- Functions that produce random strings
delimiter $ 
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i+1;
    END WHILE;
    RETURN return_str;
END $
delimiter;

-- Functions that generate random department numbers
delimiter $ 
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
END $
delimiter;

Notes
-- Execution function problem，This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
-- This is what we've startedbin-log, We must specify whether our function is，DETERMINISTIC Uncertain， NO SQL NoSQlStatement，Of course, the data will not be modified.
-- existMySQLSolution to this error when creating a function in：set global log_bin_trust_function_creators=TRUE;
set global log_bin_trust_function_creators=TRUE;

-- Writing stored procedures，simulation 100W Employee data。
-- Create stored procedures：PastempInsert data in the table
 DELIMITER $
 drop PROCEDURE if EXISTS insert_emp;
 CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
 BEGIN
     DECLARE i INT DEFAULT 0;
     /*set autocommit =0 Set autocommit to 0, close the default commit*/
     SET autocommit = 0;
     REPEAT
     SET i = i + 1;
     INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
 END $
 DELIMITER;
 
-- insert500WData，A little long，Wait patiently，1409s
 call insert_emp(0,5000000);

-- Query department employee list
select * from emp LIMIT 1,10;

-- Writing stored procedures，simulation 120 Departmental data
-- Create stored procedures：PastdepInsert data in the table
 DELIMITER $
 drop PROCEDURE if EXISTS insert_dept;
 CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
 BEGIN
     DECLARE i INT DEFAULT 0;
     SET autocommit = 0;
     REPEAT
     SET i = i+1;
     INSERT  INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
 END $
 DELIMITER;
 
-- insert120Data
 call insert_dept(1,120);

-- Query department employee list
select * from dep;

-- Create index of key fields，This is to create an index after running the data，It will take a long time to create an index，But the data will be faster。
-- Create index of key fields:Sort、condition
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno); 

-- Verification test
-- The offset is100，Pick25，Time: 0.011s
SELECT ,,,,,
from emp a left join dep b on  =  order by  desc limit 100,25;

-- The offset is4800000，Pick25，Time: 10.242s
SELECT ,,,,,
from emp a left join dep b on  =  order by  desc limit 4800000,25;

-- Subquery to get offset100The position of the barid，Take back at this position25，Time: 0.04s
 SELECT ,,,,,
 from emp a left join dep b on  = 
 where  >= (select id from emp order by id limit 100,1)
 order by  limit 25;

-- Subquery to get offset4800000The position of the barid，Take back at this position25，Time: 1.549s
 SELECT ,,,,,
 from emp a left join dep b on  = 
 where  >= (select id from emp order by id limit 4800000,1)
 order by  limit 25;

-- Remember the last piece of data from the last page of the pageidyes100，Just skip this100，from101Start scanning the table，Time: 0.006s
 SELECT ,,,,,,
 from emp a left join dep b on  = 
 where  > 100 order by  limit 25;

-- Remember the last piece of data from the last page of the pageidyes4800000，Just skip this4800000，from4800001Start scanning the table，Time: 0.046s
 SELECT ,,,,,,
 from emp a left join dep b on  = 
 where  > 4800000
 order by  limit 25;