mysql insert limit_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关键字会导致偏移量的原因