- 建表与插入数据
- SQL对比
1. 基本数据
- 创建表
CREATETABLE student
(
id int(10)NOTNULLAUTO_INCREMENT,
name varchar(25)DEFAULTNULL,
age tinyint(2)DEFAULTNULL,
live varchar(255)DEFAULTNULL,
PRIMARYKEY(id)
);
- 批量插入1百万条数据
DROPPROCEDUREIFEXISTS insert_Stu;
DELIMITER $$
CREATEPROCEDURE insert_Stu(IN max_num INT(10))
BEGIN
DECLARE i INTDEFAULT0;
REPEAT
SET i = i +1;
INSERTINTO student (name, age, live)VALUES(rand_string(3), rand_num(), rand_string(6));
UNTIL i = max_num
ENDREPEAT;
COMMIT;
END $$
DROPPROCEDUREIFEXISTS rand_string;
DELIMITER $$
CREATEFUNCTION rand_string(n INT)RETURNSVARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100)DEFAULT'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255)DEFAULT'';
DECLARE i INTDEFAULT0;
WHILE i < n DO
SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+ RAND()*52),1));
SET i = i +1;
ENDWHILE;
RETURN return_str;
END $$;
DROPPROCEDUREIFEXISTS rand_num;
DELIMITER $$
CREATEFUNCTION rand_num()
RETURNSINT(5)
BEGIN
DECLARE i INTDEFAULT0;
SET i = FLOOR(100+ RAND()*10);
RETURN i;
END $$
;
call insert_Stu(1000000);
2. SQL对比
- 常规分页
select s.*from student s limit1000000,10;
- 优化分页
select s.*
from student s
WHERE s.id>=(SELECT s.idFROM student LIMIT1000000,1)
LIMIT10;
- 最优分页
SELECT s.*FROM student s WHERE s.idBETWEEN1000000AND1000010;
性能对比图
可以看到三种SQL的耗时 ，为什么会有这么大的差距，这主要是MySQL的limit关键字会导致偏移量的原因
