您最常用的sql优化是什么?

What are your most common SQL optimization that you used?

您最常用的SQL优化是什么?

17 个解决方案

#1

Reducing the amount of data that is returned, by only returning the fields required and only returning the rows required. This is the most common, as you do it for every query that returns data.

通过只返回所需的字段和只返回所需的行来减少返回的数据量。这是最常见的，因为您对每个返回数据的查询都这样做。

Adding indexes. This is not done as frequently, as some tables doesn't need any other index than the one created for the primary key.

添加索引。这样做的频率并不高，因为除了为主键创建的索引之外，有些表不需要任何其他索引。

#2

My favorite list of tips (explained in detail here) is as follows

下面是我最喜欢的技巧列表(在这里详细解释)

Try to restrict the queries result set by using the WHERE clause.
尝试使用WHERE子句限制查询结果集。
Try to restrict the queries result set by returning only the particular columns from the table, not all the table's columns.
尝试通过只返回表中的特定列，而不是所有表的列来限制查询结果集。
Use views and stored procedures instead of heavy-duty queries.
使用视图和存储过程代替繁重的查询。
Whenever possible, try to avoid using SQL Server cursors.
尽可能避免使用SQL Server游标。
If you need to return the total table's row count, you can use an alternative way instead of the SELECT COUNT(*) statement.
如果需要返回total表的行数，则可以使用另一种方法，而不是SELECT count(*)语句。
Try to use constraints instead of triggers, whenever possible.
尽可能使用约束而不是触发器。
Use table variables instead of temporary tables.
使用表变量代替临时表。
Try to avoid the HAVING clause, whenever possible.
尽可能避免使用“有”条款。
Whenever possible, try to avoid using the DISTINCT clause.
尽可能避免使用不同的子句。
Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
将SET NOCOUNT ON语句包含到存储过程中，以停止指示T-SQL语句影响的行数的消息。
Use select statements with the TOP keyword or the SET ROWCOUNT statement if you need to return only the first n rows.
如果只需要返回前n行，则使用带有TOP关键字的select语句或SET ROWCOUNT语句。
Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
如果需要快速返回'number_rows'行，请使用快速的number_rows表提示。
Try to use UNION ALL statement instead of UNION, whenever possible.
尽可能使用UNION ALL语句而不是UNION。
Do not use optimizer hints in your queries.
不要在查询中使用优化器提示。

#3

By far and above: Making covering indexes

远远超过:编制覆盖指标

A covering index includes all the columns that the query will need, thereby avoiding the need to do lookups on the results of an index seek. This will then avoid the system feeling like a scan could be quicker (which is remarkably quick considering the cost of lookups).

覆盖索引包含查询所需的所有列，从而避免对索引查找的结果进行查找。这将避免系统感觉像扫描可能更快(考虑到查找成本，这是非常快的)。

But also worth mentioning:

但也值得一提:

Having an index that will allow a merge join. A MERGE join is able to occur when joining two tables that are ordered by the join conditions. But of course, in saying 'table', we really mean 'index', right...

具有允许合并连接的索引。合并连接可以在连接由连接条件排序的两个表时发生。但是当然，在说“table”的时候，我们的意思是“index”，对吧…

Also - removing scalar functions and using table-valued functions instead... as scalar functions are not able to be simplified out.

此外，删除标量函数并使用表值函数代替…由于标量函数不能被简化。

Also - putting a unique index on a column that you know to be unique, allowing the query optimizer to use this knowledge to make better optimization choices. Also applies to NOT NULL constraints.

另外——将惟一索引放在已知为惟一的列上，允许查询优化器使用这些知识进行更好的优化选择。也适用于非空约束。

Also - using Binary collation when comparing strings that are in a known case, so that the system doesn't have to consider the different case options.

另外——在比较已知情况下的字符串时使用二进制排序，这样系统就不必考虑不同的情况选项。

Of course I could go on all day...

当然，我可以继续干一整天……

Rob

罗伯

#4

Caching db output. Avoiding pressuring the database at all seems to be a prudent optimization.

缓存数据库输出。完全避免对数据库施加压力似乎是一种谨慎的优化。

+1 memcached.

+ 1 memcached。

#5

Index foreign keys!

指数外键!

Maybe this isn't a sql query syntax optimisation, but more a storage optimisation. But i see it re-occur all the time & its a pet peeve.

也许这不是sql查询语法优化，而是存储优化。但我看到它一直反复出现&这是我最讨厌的事。

#6

1) I've yet to find a situation where

我还没找到一个地方

SELECT Field1, Field2, (SELECT Count(*) FROM tblLinked WHERE tblLinked.Field3 = tblSource.Field3) AS TheTotal
FROM tblSource

isn't improved by a LEFT JOIN to a derived table.

由左连接到派生表的改进。

SELECT Field1, Field2, IsNull(Linked.TheTotal,0) AS TheTotal
FROM tblSource
LEFT JOIN (SELECT Field3, Count(*) AS TheTotal
    FROM tblLinked
    GROUP BY Field3) AS Linked ON tblSource.Field3 = Linked.Field3

2) Don't sort the results on the server unless the consuming app is unable to do this itself. This applies less often to web apps, but for desktop apps the client PC usually has plenty of power available and can happily do a sort.

2)不要在服务器上对结果进行排序，除非消费应用程序本身无法进行排序。这在web应用程序中不太常见，但对于桌面应用程序，客户端PC通常有大量可用的功能，并且可以很高兴地进行某种操作。

3) Use EXISTS instead of checking the Count of matching entries.

使用存在，而不是检查匹配项的计数。

4) Don't get obsessed with doing a query in just one SELECT clause. Judicious use of table variables (and sometimes temporary tables) can massively reduce the rows processed.

4)不要沉迷于在一个SELECT子句中执行查询。明智地使用表变量(有时是临时表)可以大量减少处理的行。

#7

The best optimisation I've ever had using SQL was to really understand what was needed to be done the data and REMOVE ton's of SQL from the query.

我使用SQL的最佳优化是真正理解需要做什么，并从查询中删除ton的SQL。

The fastest query is the query that does not have to be run.

最快的查询是不需要运行的查询。

REALLY THINK about what your doing to the data. Are you working row-by-row? (then use set based code).

想想你对数据做了什么。你工作逐行吗?(然后使用基于集合的代码)。

Do you really need to join to all those tables?

你真的需要加入所有这些表格吗?
Can two small (simple) queries do the job better and quicker than a single large query?

两个小(简单)查询能比一个大查询做得更好、更快吗?
If you combine these two queries into a single query can it run faster?

如果将这两个查询合并到一个查询中，它能运行得更快吗?

Finally, PROFILE your queries (EXPLAIN PLAN or SQL PROFILER) and look at the "IO gets". Generally you want to reduce the number of GET's to a ratio something like 10 gets per output row.

最后，配置查询(EXPLAIN PLAN或SQL PROFILER)并查看“IO get”。一般来说，你想要减少GET的数量与输出行的比率比如10。

#8

Lowering transaction isolation levels to get around table locks for user queries. Not all the time, but for gui's showing general information it works great.

降低事务隔离级别以绕过用户查询的表锁。不是所有时候，但是对于gui显示的一般信息来说，它工作得很好。

#9

If you're talking common as in really common then Indexes are the first thing that pops up into my head.

如果你说的是非常普遍的，那么索引是我脑海中最先想到的东西。

They are a powerful technique that are often misunderstood and quite often abused.

它们是一种强大的技术，经常被误解和滥用。

Then I would place de-normalization which can add in quite a bit of performance for many databases.

然后我将放置反规范化，它可以为许多数据库增加相当多的性能。

Query optimization is third and it helps a lot too. I use MySQL these days and Query logging helps a lot for optimization.

查询优化是第三个，它也有很大的帮助。我现在使用MySQL，查询日志对优化有很大帮助。

Memcached is definitely not common, though caching of some sort is a part of many websites at the scripting end (ASP.Net or PHP).

Memcached绝对不是常见的，尽管某些类型的缓存是许多站点的脚本端(ASP)的一部分。净或PHP)。

#10

Making sure tables are being joined in the correct order.

确保以正确的顺序连接表。

#11

The two most important things in my experience are fewer joins and less queries. Other than those there's lots of DB specific stuff, COUNT(*) is relatively slow on PgSQL, subselects are dog slow on MySQL, etc.

在我的经验中，最重要的两件事是减少连接和查询。除了那些有很多数据库的东西，COUNT(*)在PgSQL上比较慢，在MySQL中subselect是dog slow，等等。

#12

The biggest optimizations i used recently where quite simple.

我最近使用的最大优化非常简单。

Keep as much of the business logic as close as possible to the sql server. Aka keep you business code on the same machine as the sql server. Let your business logic return as little as possible code back to the final client.

将尽可能多的业务逻辑保持在sql服务器附近。也就是将业务代码保存在与sql服务器相同的机器上。让您的业务逻辑尽可能少地将代码返回给最终的客户端。

Keep your SQL query's as 'short as possible' like Frost said, use single update statements over multiple statements.

尽量保持SQL查询的“尽可能短”，就像弗罗斯特说的，在多个语句中使用单个更新语句。

Only use transactions when you need them

只在需要时使用事务。

Create temp tables for partial joins to speed up the joins (dont forget to index them)

为部分连接创建临时表以加速连接(不要忘记索引它们)

#13

I have read all answers and I didn't found LIMIT and OFFSET usage hints. It is very common usage in pagination with "prev" and "next" links. But rendering such a display can consume more resources than the entire rest of the site. When offsetting large number items, query can become very slow. So avoid these queries.

我已经阅读了所有的答案，没有发现限制和使用提示。在使用“prev”和“next”链接的分页中，这是非常常见的用法。但是呈现这样的显示会比整个站点消耗更多的资源。当抵消大量项目时，查询会变得非常缓慢。所以避免这些查询。

Do not count total items.
不要计算所有项目。
Show only "n" number first items (for example only top 100).
只显示“n”数字的第一个项目(例如，只显示前100)。

Such methods uses Google, Twitter and other sites. In Google search there is no accurate number of results. There is only approximate number. Twitter doesn't allow user to view all past tweets. It shows only last n number (I can't remember how much).

这些方法使用谷歌、Twitter和其他网站。在谷歌搜索中没有精确的结果数。只有近似的数。Twitter不允许用户查看所有过去的tweet。它只显示最后一个n个数字(我记不清有多少)。

There is some link from MySQL performance blog.

有一些来自MySQL性能博客的链接。

#14

indexes its the most common optimization
索引它是最常见的优化
De normalizing the tables.
德规范表。
Removing constraints (only if you know what you are doing)
删除约束(只有当你知道你在做什么)

#15

Avoid the use of onboard functions like convertdate, stringreplace and such in your Views. If you can't make sure that the data is in a valid format use stored procedures which run reguallary to 'clean up' the data in your relevant tables.

避免在视图中使用内置函数，如convertdate、stringreplace等。如果您不能确保数据是有效的格式，请使用运行reguallary的存储过程来“清理”相关表中的数据。

this is nasty, but it saves the views time i.e. keeps the user happy... ^^

这很讨厌，但是它节省了视图时间，比如让用户高兴……^ ^

#16

Couple of hints: Use

两个提示:使用

delete from table where id>=1 and id<=3;

instead of

而不是

delete from table where id=1;
delete from table where id=2;
delete from table where id=3;

Also use 'IN' instead of 'OR' syntax

也可以用IN代替OR语法

#17

Do not put constraints if not required as constraints will add an index, the more number of indexes, the more time it takes for data insertion.

如果不需要，不要放置约束，因为约束会添加索引，索引数量越多，插入数据所需的时间就越多。

#1