MySQL分页的优化

MySQL自带的分页语法依据

 LIMIT [offset,]  rows 

对于MySQL自带分页方式的优化

创建模拟数据表以及模拟数据

-- 创建模拟数据SQL
drop table if exists student;
 
create table if not exists student
(
    student_id int not null auto_increment primary key,
    student_name varchar(50) not null
) engine = innodb default charset = utf8;
 
 
drop table if exists course;
 
create table if not exists course
(
    course_id int not null auto_increment primary key,
    course_name varchar(10) not null
) engine = innodb default charset = utf8;
 
 
drop table if exists studentcourse;
 
create table if not exists sc
(
    sc_id int not null auto_increment primary key,
    student_id int not null,
    course_id int not null,
    score int not null
) engine = innodb default charset = utf8;
 
-- 以下数据表对应的数据记录数
--      10  course
--  70,000  student
-- 700,000  studentcourse
-- 根据以上要求准备测试数据
 
insert into course (course_id, course_name)
select null as course_id, concat('course', ID) as course_name
from information_schema.`COLLATIONS`
order by ID asc
limit 0, 10;
 
insert into student (student_id, student_name)
select null as student_id, '' as student_name
from ( 
    select 1 as column_order_id
    from information_schema.`COLUMNS`
    limit 0, 3500
) as t
    cross join (
        select 1 as collation_order_id
        from information_schema.`COLUMNS`
        limit 0, 200
    ) as t2;
     
update student
set student_name = concat('student', student_id)
where student_name = '';
 
insert into sc (sc_id, student_id, course_id, score)
select null as sc_id, t2.student_id, t.course_id, ceiling(rand()*100) as score
from course as t
    cross join student as t2;

SELECT s.* from 

Student s

INNER JOIN SC sc

on sc.s_id = s.s_id

where sc.c_id=1 and sc.score=100;

1. 自带的分页查询

SELECT s_id,sc_id,c_id,score FROM sc ORDER BY sc_id LIMIT 6000000,10;

2. 优化自带分页

SELECT s_id,sc_id,c_id,score FROM sc where sc_id > 6000000 ORDER BY sc_id LIMIT 0,10

可以清晰看到提升的速度还是很快的

为了证明不是缓存的原因
连续运行N(N>2)次自带的分页语句:

结果表明不是因为缓存造成的第二次查询速度变快