哪些列通常是好的索引?
作为什么是索引，我如何使用它们来优化我的数据库中的查询?" 我试图了解索引的地方，哪些列是好的索引候选?专门针对 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?
经过一些谷歌搜索后，我读到的所有内容都表明，通常会增加且唯一的列是一个很好的索引(例如 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 子句中使用的列.简而言之，应该对此类列建立索引，您需要根据这些列来搜索特定记录.假设我们有一个名为 purchase 的表，其中 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 */
由于在SELECT部分引用了buyer_id"，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 将如何执行查询.它显示了表的连接方式和顺序.这对于确定如何编写优化查询以及是否需要对列进行索引非常有用.
更新(2015 年 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.
相关文章