原文：http://bbs.landingbj.com/t-0-240894-1.html
首先，我们看一个分页SQL：
SELECT time,
page
FROM `l_not_200_page`
WHERE time BETWEEN timestamp('2014-03-27') AND timestamp('2014-03-27 23:59:59')
AND DOMAIN = 1
AND status = 304 LIMIT 0,
30
page
FROM `l_not_200_page`
WHERE time BETWEEN timestamp('2014-03-27') AND timestamp('2014-03-27 23:59:59')
AND DOMAIN = 1
AND status = 304 LIMIT 0,
30
我们可以这样优化：
SELECT time,
page
FROM `l_not_200_page`
INNER JOIN
(SELECT id
FROM `l_not_200_page`
WHERE time BETWEEN timestamp('2014-03-27') AND timestamp('2014-03-27 23:59:59')
AND DOMAIN = 1
AND status = 304
ORDER BY page LIMIT 0,
30) AS t USING(id);
page
FROM `l_not_200_page`
INNER JOIN
(SELECT id
FROM `l_not_200_page`
WHERE time BETWEEN timestamp('2014-03-27') AND timestamp('2014-03-27 23:59:59')
AND DOMAIN = 1
AND status = 304
ORDER BY page LIMIT 0,
30) AS t USING(id);
这样优化的好处：
对于limit的定义是：limit x,y。表示从第x行开始选择y条记录。
如果我们这样优化：使用覆盖索引查询返回需要的主键、再根据这些主键关联原表获得需要的行。
这样的好处一是使用索引加快查询速度，二是剔除不需要的数据，减少逻辑读和物理读。