Mysql Limit 调优

建表与插入数据
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对比

常规分页

select s.* from student s limit 1000000,10;

优化分页

select s.*

from student s

WHERE s.id >= (SELECT s.id FROM student LIMIT 1000000, 1)

LIMIT 10;

最优分页

SELECT s.* FROM student s WHERE s.id BETWEEN 1000000 AND 1000010;

性能对比图

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

巴特西

Mysql Limit 调优

基本数据

SQL对比

最新文章

热门文章