在使用JDBC的查询中使用Limit是否对性能有影响? [英] Does using Limit in query using JDBC, have any effect in performance?

问题描述

如果我们在还具有ORDER BY子句的查询中使用Limit子句并在JDBC中执行查询，那么性能会受到影响吗? (使用MySQL数据库)

If we use the Limit clause in a query which also has ORDER BY clause and execute the query in JDBC, will there be any effect in performance? (using MySQL database)

示例:

     SELECT modelName from Cars ORDER BY manuDate DESC Limit 1

我阅读了该论坛中的一个主题，默认情况下，一次获取一个固定大小.如何找到默认的提取大小?

I read in one of the threads in this forum that, by default a set size is fetched at a time. How can I find the default fetch size?

我只想要一个记录.最初，我的使用方式如下:

I want only one record. Originally, I was using as follows:

SQL查询:

 SELECT modelName from Cars ORDER BY manuDate DESC

在JAVA代码中，我提取的内容如下:

In the JAVA code, I was extracting as follows:

  if(resultSett.next()){
      //do something here.
  }

推荐答案

肯定LIMIT 1将对性能产生积极影响.从数据库服务器返回到Java代码的数学数据集整个(取决于默认的获取大小)，将只返回一行.这样可以节省大量网络带宽和Java内存使用量.

Definitely the LIMIT 1 will have a positive effect on the performance. Instead of the entire (well, depends on default fetch size) data set of mathes being returned from the DB server to the Java code, only one row will be returned. This saves a lot of network bandwidth and Java memory usage.

始终将尽可能多的约束(例如LIMIT，ORDER，WHERE等)委派给SQL语言，而不是在Java端这样做. DB将比您的Java代码做得更好(当然，如果表已正确索引).您应该尽可能编写SQL查询，使其尽可能准确地返回所需的信息.

Always delegate as much as possible constraints like LIMIT, ORDER, WHERE, etc to the SQL language instead of doing it in the Java side. The DB will do it much better than your Java code can ever do (if the table is properly indexed, of course). You should try to write the SQL query as much as possibe that it returns exactly the information you need.

编写特定于数据库的SQL查询的唯一缺点是，SQL语言不能完全在不同的DB服务器之间移植，这将要求您在每次更改DB服务器时都更改SQL查询.但无论如何，在现实世界中，切换到完全不同的数据库制造商非常罕见.无论如何，将SQL字符串外部化为XML或属性文件应该会有所帮助.

Only disadvantage of writing DB-specific SQL queries is that the SQL language is not entirely portable among different DB servers, which would require you to change the SQL queries everytime when you change of DB server. But it's in real world very rare anyway to switch to a completely different DB make. Externalizing SQL strings to XML or properties files should help a lot anyway.

这篇关于在使用JDBC的查询中使用Limit是否对性能有影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！