(一)
mysql
limit大数据量分页优化方法
首先创建一个表
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE
TABLE
`ipdatas` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`uid`
int
(8)
NOT
NULL
DEFAULT
'0'
,
`ipaddress`
varchar
(50)
NOT
NULL
,
`source`
varchar
(255)
DEFAULT
NULL
,
`track`
varchar
(255)
DEFAULT
NULL
,
`entrance`
varchar
(255)
DEFAULT
NULL
,
`createdtime` datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
`createddate`
date
NOT
NULL
DEFAULT
'0000-00-00'
,
PRIMARY
KEY
(`id`),
KEY
`uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=68293856
DEFAULT
CHARSET=utf8;
|
插入大量数据100万
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
begin
declare
i
int
;
set
i = 10001 ;
myLoop: LOOP
if i = 100000
then
leave myLoop;
end
if;
/* do something */
-- 循环输出信息
insert
into
`ipdatas`(`uid`,`ipaddress`,`source`,`track`,`entrance`,`createdtime`,`createddate`)
values
(i,
'127.0.0.1'
,
'wanglitao'
,
'guoyanhui'
,
'127.0.0.1'
,now(),now());
set
i = i +1;
/* 循环结束 */
end
loop myLoop;
end
|
优化语句
原语句
select * from ipdatas order by id limit 900000,100
受影响的行: 0
时间: 16.408s
优化语句
Select * From ipdatas Where id>=(
Select id from ipdatas order by id limit 900000,1
)limit 100;