SQL优化的五个建议

翻译至：http://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries，仅仅作为自己学习参考，翻译不好，请见谅。
SQL语言看起来很容易学习-SQL命令遵循一个简单的语法但是并不描述用于检索数据的特定算法，然而，简单可能只是表面的，并不是所有的数据库功能都有相同的操作效率，两个非常相似的查询语句在执行时间上可以有很大的不同，本文提出了一些能大大提升你的 SQL 查询的最佳做法。

1.学习如何恰当地创建索引(Learn How to Create Indexes Properly)

学习如何正确地创建索引是你可以提高你的SQL查询性能能做的最好的事情，在特殊情况下，索引能更快地访问数据库，对于数据库初学者来说，索引是一个神秘或者说困难的事情，他们要么什么都没检索到，要么试图检索所有东西。当然，这些方法都不正确，如果一点索引都没有，你的查询有可能会很慢；如果你索引所有东西，会导致你的updates和insert触发器效率很低下。

如你不确定你足够了解索引，去看 @MarkusWinand 的 three minute-test 索引入门教学，Markus的网站 “Use the Index, Luke” 是了解索引最好的起点。

2.只检索你真正需要的数据(Only Retrieve the Data You Really Need)

查询需要的列信息最常用的方法是使用*，但可能有些列不是你真正需要的；如果表很小，检索附加列都没有太大区别，但是，对于较大的数据集，指定列查询可能会节省大量的查询时间。然而，请牢记一点，许多流行的ORM 不会简单让你创建择表中的列的子集的一个查询。

Similarly, if you only need a limited number of rows you should use the LIMIT clause (or your database’s equivalent). Take a look at the following code:

For instance, if you only want to display the first 10 records out of 50,000 on your website, it is advisable to inform the database about it. This way, the database will stop the search after finding 10 rows rather than scan the whole table:

The LIMIT statement is available in MySQL and Postgresql, but other databases have ways to achieve a similar effect.

These above examples illustrate the general idea – you should always think whether you need all the rows returned by an SQL statement. If you don’t, there is always some room for improvement.

3.避免在左手边的运算符的功能(Avoid Functions on the Left Hand-Side of the Operator)

Functions are a handy way to provide complex tasks and they can be used both in the SELECTclause and in the WHERE clause. Nevertheless, their application in WHERE clauses may result in major performance issues. Take a look at the following example:

Even if there is an index on the appointment_date column in the table users, the query will still need to perform a full table scan. This is because we use the DATEDIFF function on the column appointment_date. The output of the function is evaluated at run time, so the server has to visit all the rows in the table to retrieve the necessary data. To enhance performance, the following change can be made:

This time, we aren’t using any functions in the WHERE clause, so the system can utilize an index to seek the data more efficiently.

4.考虑摆脱相关子查询(Consider Getting Rid of Correlated Subqueries)

A correlated subquery is a subquery which depends on the outer query. It uses the data obtained from the outer query in its WHERE clause. Suppose you want to list all users who have made a donation. You could retrieve the data with the following code:

In the above case, the subquery runs once for each row of the main query, thus causing possible inefficiency. Instead, we can apply a join:

If there are millions of users in the database, the statement with the correlated subquery will most likely be less efficient than the INNER JOIN because it needs to run millions of times. But if you were to look for donations made by a single user, the correlated subquery might not be a bad idea. As a rule of thumb, if you look for many or most of the rows, try to avoid using correlated subqueries. Keep in mind, however, that using correlated subqueries might be inevitable in some cases.

5.避免LIKE模式开头的通配符字符(Avoid Wildcard Characters at the Beginning of a LIKEPattern)

Whenever possible, avoid using the LIKE pattern in the following way:

The use of the % wildcard at the beginning of the LIKE pattern will prevent the database from using a suitable index if such exists. Since the system doesn’t know what the beginning of the name column is, it will have to perform a full table scan anyway. In many cases, this may slow the query execution. If the query can be rewritten in the following way:

then the performance may be enhanced. You should always consider whether a wildcard character at the beginning is really essential.

小贴士-读取执行计划(Read the Execution Plan)

The performance of your SQL queries depends on multiple factors, including your database model, the indexes available and the kind of information you wish to retrieve. The best way to keep track of what’s happening with your queries is to analyse the execution plan produced by the optimizer. You can use it to experiment and find the best solution for your statements