网站地图
【www.gdgbn.com--mysql教程】
mysql教程 limit 的性能问题
有个几千万条记录的表 on mysql 5.0.x，现在要读出其中几十万万条左右的记录
常用方法，依次循环：
select * from mytable where index_col = xxx limit offset, limit;
经验：如果没有blob/text字段，单行记录比较小，可以把 limit 设大点，会加快速度
问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99%
速度不可接受。
调用 explain select * from mytable where index_col = xxx limit offset, limit;
显示 type = all
在 mysql optimization 的文档写到"all"的解释
a full table scan is done for each combination of rows from the previous tables. this is normally not good if the table is the first table not marked const, and usually very bad in all other cases. normally, you can avoid all by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.
看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？
因为 id 是递增的，也很好修改 sql
select * from mytable where id > offset and id < offset + limit and index_col = xxx
explain 显示 type = range, 结果速度非常理想，返回结果快了几十倍。
在 mysql 查询中使用了很多 limit 关键字，这就让我很感兴趣了，因为在我印象中， limit 关键字似乎更多被使用 mysql 数据库教程的程序员用来做查询分页（当然这也是一种很好的查询优化），那在这里举个例子，假设我们需要一个分页的查询 ，oracle中一般来说都是用以下 sql 句子实现：
select * from
( select a1.*, rownum rownum_
from testtable a1
where rownum > 20)
where rownum_ <= 1000
这个语句就能查询到 testtable 表中的 20 到 1000 记录，而且还需要嵌套查询，效率不会太高，看看 mysql 的实现：
select * from testtable a1 limit 20,980;
这样就能返回 testtable 表中的 21 条到（ 20 ＋ 980 ＝） 1000 条的记录。
实现语法确实简单，但如果要说这里两个 sql 语句的效率，那就很难做比较了，因为在 mysql 中 limit 选项有多种不同的解释方式，不同方式下的速度差异是很大的，因此我们不能从这语句的简洁程度就说谁的效率高。
不过对程序员来说，够简单就好，因为维护成本低，呵呵。
下面讲讲这个 limit 的语法吧：
select ……. --select 语句的其他参数
[limit {[offset,] row_count | row_count offset offset}]
这里 offset 是偏移量（这个偏移量的起始地址是 0 ，而不是 1 ，这点很容易搞错的）顾名思义就是离开起始点的位置，而 row-count 也是很简单的，就是返回的记录的数量限制。
eg. select * from testtable a limit 10,20 where ….
这样就能使结果返回 10 行以后（包括 10 行自身）的符合 where 条件的 20 条记录。
那么如果没有约束条件就返回 10 到 29 行的记录。
那这跟避免全表扫描有什么关系呢？ 下面是 mysql 手册对 limit 参数优化扫描的一些说明：
在一些情况中，当你使用 limit 选项而不是使用 having 时， mysql 将以不同方式处理查询。
l 如果你用 limit 只选择其中一部分行，当 mysql 一般会做完整的表扫描时，但在某些情况下会使用索引（跟 ipart 有关）。
l 如果你将 limit n 与 order by 同时使用，在 mysql 找到了第一个符合条件的记录后，将结束排序而不是排序整个表。
l 当 limit n 和 distinct 同时使用时， mysql 在找到一个记录后将停止查询。
l 某些情况下， group by 能通过顺序读取键 ( 或在键上做排序 ) 来解决，并然后计算摘要直到键值改变。在这种情况下， limit n 将不计算任何不必要的 group 。
l 当 mysql 完成发送第 n 行到客户端，它将放弃余下的查询。
l 而 limit 0 选项总是快速返回一个空记录。这对检查查询并且得到结果列的列类型是有用的。
l 临时表的大小使用 limit # 计算需要多少空间来解决查询。
百万数据模糊查找大改进！！！！！！ （0.03 sec）
select id,name from user where name like "%83%" or key like "%83%" limit 0,25;
分页
mysql中limit的用法详解[数据分页常用]
在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。
select * from table limit [offset,] rows | rows offset offset
limit 子句可以被用于强制 select 语句返回指定的记录数。limit 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初 始记录行的偏移量是 0(而不是 1)： 为了与 postgresql 兼容，mysql 也支持句法： limit # offset #。
mysql> select * from table limit 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> select * from table limit 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目：
mysql> select * from table limit 5; //检索前 5 个记录行
//换句话说，limit n 等价于 limit 0,n。
1. select * from tablename <条件语句> limit 100,15
从100条记录后开始取15条 (实际取取的是第101-115条数据)
2. select * from tablename <条件语句> limit 100,-1
从第100条后开始-最后一条的记录
3. select * from tablename <条件语句> limit 15
相当于limit 0,15 .查询结果取前15条数据