我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。那么在 MySQL 中有那些方式是可以避免全表扫面的呢？除了我们大家很熟悉的通过使用索引列或分区等方式来进行查询的优化之外还有那些呢？

前些天看了一个老外写的程序，在 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 # 计算需要多少空间来解决查询。

如果需要在查询语句返回的列中包含一列表示该条记录在整个结果集中的行号， ISO SQL:2003 标准提出的方法是提供 ROW_NUMBER() / RANK() 函数。 Oracle 中可以使用标准方法（8i版本以上），也可以使用非标准的 ROWNUM ； MS SQL Server 则在 2005 版本中提供了 ROW_NUMBER() 函数；但在 MySQL 中似乎还没有这样的系统自带功能。虽然 LIMIT 可以很方便的对返回的结果集数量和位置进行过滤，但过滤出来的记录的行号却没办法被 SELECT 到。据说 MySQL 是早就想增加这个功能了，但我是还没找到。

解决方法是通过预定义用户变量来实现：

set @mycnt = 0;
select (@mycnt := @mycnt + 1) as ROWNUM , othercol from tblname order by othercol;

这样查询出来的结果集中 ROWNUM 就保存了行编号信息。这个行编号信息的某种用途在于当你需要根据需要对数据按照某种规则排序并取出排序之后的某一行数据，并且希望知道这行数据在之前排序中的位置时就用得着了。比如：

set @mycnt = 0;
select * from (
    select (@mycnt := @mycnt + 1) as ROWNUM , othercol
     from tblname order by othercol
) as A where othercol=OneKeyID;

当然你也可以通过创建临时表的方法把查询结果写到某个拥有 auto_increment 字段的临时表中再做查询，但考虑到临时表在 MySQL master / slave 模式下可能产生的问题，用这样临时用户定义变量的方式来计算查询结果集每一行对应的行号还是更为简洁 -- 除非你愿意在 PHP 或其他语言脚本中对返回的整个结果集再作处理。