MySQL分页的优化

朱雀 2021-05-12 11:49 474阅读 0赞

### 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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]  
2. **优化自带分页**

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]  
***可以清晰看到提升的速度还是很快的***

**为了证明不是缓存的原因**  
连续运行N(N>2)次自带的分页语句:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 2]  
**结果表明不是因为缓存造成的第二次查询速度变快**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]: /images/20210511/4b18bccca1ac4f55821c69483599dbc8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]: /images/20210511/efc45f31b3604f4dbf463dc17def31f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 2]: /images/20210511/65982d5b897f477b8ff7d8e861c261b4.png