在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,结果速度非常理想,返回结果快了几十倍。
Limit语法:
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 //如果只给定一个参数,它表示返回最大的记录行数目,换句话说,LIMIT n 等价于 LIMIT 0,n mysql> SELECT * FROM table LIMIT 5; //检索前5个记录行
MySQL的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。同样是取10条数据,下面两句就不是一个数量级别的。
select * from table limit 10000,10 select * from table limit 0,10
文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。
这里我具体使用数据分两种情况进行测试。
1、offset比较小的时候:
select * from table limit 10,10 //多次运行,时间保持在0.0004-0.0005之间 Select * From table Where vid >=(Select vid From table Order By vid limit 10,1) limit 10 //多次运行,时间保持在0.0005-0.0006之间,主要是0.0006
结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。
2、offset大的时候:
select * from table limit 10000,10 //多次运行,时间保持在0.0187左右 Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10 //多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对小牛知识库的支持。如果你想了解更多相关内容请查看下面相关链接
问题内容: 我有2张桌子,和。用户可以有很多游戏。我需要所有有人数的人,以及他们的人数(有专栏的)。 附言:我需要将所有数据加载到管理表中。由于游戏太多。我决定对数据进行分页和限制。但是,甚至限制以下查询也需要花费相同的时间。如何更好地查询? 问题答案: 您可以在下面尝试使用表达式
我正在数据库中运行以下查询: 它输出500行,其中只有一个结果列,运行大约需要1分钟43秒。输出以下计划: 逻辑是:对于每个选择的(在500个id的列表中)计算整数列,返回该金额与数字2147483647之间的较小值。结果必须包含500行,每个id对应一行,我们已经知道它们将与子查询中的至少一行匹配,因此不会生成空值。 索引仅是上的一个b树,属于整数类型。索引是主键上的b树,也是整数类型。表中的每
问题内容: 我需要知道abt在mongo中的索引如何提高查询性能。并且当前我的数据库没有索引。如何索引现有数据库?我是否还需要创建一个仅用于索引的新字段? 问题答案: 从根本上说,MongoDB中的索引类似于其他数据库系统中的索引。MongoDB支持MongoDB集合中文档中包含的任何字段或子字段上的索引。 索引在这里详细介绍,我强烈建议您阅读本文档。 其中包括有关索引操作,策略和创建选项的部分,
问题内容: 有人愿意帮助我吗?在具有10000行的MEMORY表上,以下查询大约需要18秒。如果我没有“ where”约束,则只需不到一分钟的时间。我已经打开查询缓存以及将其作为准备好的语句来尝试。有什么我可以做的吗?索引还是什么? 问题答案: 我认为这将为您提供所需的信息,而不管您关注的滚动日期范围…我已经通过创建带有两个标识列的自己的“发票”表进行了测试。使用@mySQL变量实际上非常简单,可
我的Java spring应用程序中有一个原生查询,它访问oracle DB并获取一个很大的结果集(接近20000条记录)。当前的方法是,我使用spring的JdbcTemplate来执行查询,这需要大约几分钟的时间来执行查询。提高执行此查询的性能的更好方法是什么?
我们在Azure Table Storage中有一个表,自从新实现以来,它目前有50,000个条目。 PartitionKey:字符串形式的日期时间值< br> RowKey:字符串形式的数值 我们使用 生成筛选条件。PartitionKey filter是这样的: