哪些列一般会创建好的索引？ [英] What columns generally make good indexes?

问题描述

作为什么是索引，如何使用它们来优化我的数据库中的查询？我在试图了解索引，哪些列是良好的索引候选？特别是对于MS SQL数据库？

As a follow up to "What are indexes and how can I use them to optimise queries in my database?" where I am attempting to learn about indexes, what columns are good index candidates? Specifically for an MS SQL database?

在一些googling之后，我已经读过的一切建议通常增加和唯一的列创建一个好的索引（像MySQL的auto_increment）我理解这一点，但我使用MS SQL和我使用GUID的主键，所以看起来索引不会有利于GUID列...

After some googling, everything I have read suggests that columns that are generally increasing and unique make a good index (things like MySQL's auto_increment), I understand this, but I am using MS SQL and I am using GUIDs for primary keys, so it seems that indexes would not benefit GUID columns...

推荐答案

索引可以在查询优化和从表中快速搜索结果中发挥重要作用。因此，选择要索引的列是最重要的步骤。有两个主要的地方，我们可以考虑索引：在WHERE子句中引用的列和在JOIN子句中使用的列。简而言之，这些列应该建立索引，您需要根据这些列搜索特定记录。假设我们有一个名为buyer的表，SELECT查询使用如下的索引：

Indexes can play an important role in query optimization and searching the results speedily from tables. So it is most important step to select which columns to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns should be indexed against which you are required to search particular records. Suppose, we have a table named buyers where the SELECT query uses indexes like below:

SELECT
 buyer_id /* no need to index */
FROM buyers
WHERE first_name='Tariq' /* consider to use index */
AND last_name='Iqbal'   /* consider to use index */

由于buyer_id在SELECT部分​​被引用，MySQL不会使用它来限制所选择的行。因此，没有很大的需要索引它。下面是另一个例子，与上面的例子略有不同：

Since "buyer_id" is referenced in the SELECT portion, MySQL will not use it to limit the chosen rows. Hence, there is no great need to index it. The below is another example little different from the above one:

SELECT
 buyers.buyer_id, /* no need to index */
 country.name    /* no need to index */
FROM buyers LEFT JOIN country
ON buyers.country_id=country.country_id /* consider to use index */
WHERE
 first_name='Tariq' /* consider to use index */
AND
 last_name='Iqbal' /* consider to use index */

根据上面的查询first_name，last_name列可以索引，因为它们位于WHERE子句中。另外，来自国家表的附加字段country_id可以被考虑用于索引，因为它在JOIN子句中。因此，可以在WHERE子句或JOIN子句中的每个字段上考虑索引。

According to the above queries first_name, last_name columns can be indexed as they are located in the WHERE clause. Also an additional field, country_id from country table, can be considered for indexing because it is in a JOIN clause. So indexing can be considered on every field in the WHERE clause or a JOIN clause.

以下列表还提供了几个建议，在表中创建索引：

The following list also offers a few tips that you should always keep in mind when intend to create indexes into your tables:

• 仅索引WHERE和ORDER BY子句中所需的那些列。丰富的索引列将导致一些缺点。


• 尝试利用MySQL的索引前缀或多列索引功能。如果创建索引（如INDEX（first_name，last_name）），请勿创建INDEX（first_name）。但是，在所有搜索情况下不建议使用索引前缀或多列索引。


• 对于您认为建立索引的列，请使用NOT NULL属性，以便永远不会存储NULL值。


• 使用 - log-long-format选项用于记录未使用索引的查询。这样，您可以检查此日志文件并相应地调整您的查询。


• EXPLAIN语句可帮助您了解MySQL如何执行查询。它显示了如何以及以什么顺序连接表。这对于确定如何编写优化的查询以及列是否需要编入索引非常有用。

• Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.
• Try to take benefit of "index prefix" or "multi-columns index" feature of MySQL. If you create an index such as INDEX(first_name, last_name), don’t create INDEX(first_name). However, "index prefix" or "multi-columns index" is not recommended in all search cases.
• Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.
• Use the --log-long-format option to log queries that aren’t using indexes. In this way, you can examine this log file and adjust your queries accordingly.
• The EXPLAIN statement helps you to reveal that how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

Upadte （2月23日）：

任何索引（好/坏）都会增加插入和更新时间。

Any index (good/bad) increases insert and update time.

根据索引（索引数和类型），搜索结果。

Depending on your indexes (number of indexes and type), result is searched. If your search time is gonna increase because of index then that's bad index.

在任何书中，索引页可能包含章节起始页，主题页码开始，也是子主题页面启动。在索引页面有一些澄清有帮助，但更详细的索引可能会混淆你或吓唬你。索引也有内存。

Likely in any book, "Index Page" could have chapter start page, topic page number starts, also sub topic page starts. Some clarification in Index page helps but more detailed index might confuse you or scare you. Indexes are also having memory.

索引选择应该明智。请记住，并非所有列都需要索引。

Index selection should be wise. Keep in mind not all columns would require index.

这篇关于哪些列一般会创建好的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！