美文网首页
Mysql Limit 调优

Mysql Limit 调优

作者: tanoak | 来源:发表于2019-04-10 22:53 被阅读0次
    1. 建表与插入数据
    2. SQL对比

    基本数据

    • 创建表

      CREATE TABLE student
      (
        id  int(10) NOT NULL AUTO_INCREMENT,
        name varchar(25) DEFAULT NULL,
        age tinyint(2) DEFAULT NULL,
        live varchar(255) DEFAULT NULL,
        PRIMARY KEY (id)
      ) ;
      
    • 批量插入1百万条数据

    DROP PROCEDURE IF EXISTS insert_Stu;
    DELIMITER $$
    CREATE PROCEDURE insert_Stu(IN max_num INT(10))
    BEGIN
      DECLARE i INT DEFAULT 0;
      REPEAT
        SET i = i + 1;
        INSERT INTO student (name, age, live) VALUES (rand_string(3), rand_num(), rand_string(6));
      UNTIL i = max_num
        END REPEAT;
      COMMIT;
    END $$
    
    DROP PROCEDURE IF EXISTS rand_string;
    DELIMITER $$
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
      DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
      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 $$;
    
    DROP PROCEDURE IF EXISTS rand_num;
    DELIMITER $$
    CREATE FUNCTION rand_num()
      RETURNS INT(5)
    BEGIN
      DECLARE i INT DEFAULT 0;
      SET i = FLOOR(100 + RAND() * 10);
      RETURN i;
    END $$
    ;
    
    call insert_Stu(1000000);
    

    SQL对比

    1. 常规分页

      select s.* from student s limit 1000000,10;
      
    2. 优化分页

      select s.*
      from student s
      WHERE s.id >= (SELECT s.id FROM student LIMIT 1000000, 1)
      LIMIT 10;
      
    3. 最优分页

      SELECT s.* FROM student s WHERE s.id BETWEEN 1000000 AND 1000010;
      
    4. 性能对比图

      可以看到三种SQL的耗时 ,为什么会有这么大的差距,这主要是MySQL的limit关键字会导致偏移量的原因

      image.png

    相关文章

      网友评论

          本文标题:Mysql Limit 调优

          本文链接:https://www.haomeiwen.com/subject/ffvaiqtx.html