在MySQL中，如果对foo进行了索引，放置SELECT foo的性能是否会提高? [英] In MySQL, does putting SELECT foo increase in performance if foo is indexed?

问题描述

在MySQL中，如果对foo进行了索引，放置SELECT foo的性能是否会提高?

In MySQL, does putting SELECT foo increase in performance if foo is indexed?

在 RedditMirror.cc 上，我有一个数据库，在GrabbedSites表中有120万条记录，每天增加大约500-2000.

At RedditMirror.cc, I have a database with 1.2 million records in the GrabbedSites table, a number that increases by approx 500-2000 per day.

在我职业生涯的早期，我曾被指导要被索引的 only 列是您

Early in my career, I was mentored that the only columns that should be indexed are those which you

1. 将来将在何处进行WHERE或JOIN SELECT/UPDATE，
2. 需要它们是唯一数据.

因此，GrabbedSites仅索引了一个键(除主键外):categoryID，但查询了8列.

Because of that, GrabbedSites only has one key indexed (besides the primary key): categoryID, but 8 columns are queried.

该网站收到大量的Flash流量，有时一天的访问量超过100,000，并且数据库的使用率大约为20％.

The website receives dramatic bursts of flash traffic, sometimes over 100,000 unique visitors a day, and the DB becomes "taxed" at about 20% usage.

所以我想知道，在MySQL中向所有8个经常查询的列添加索引是否会有性能优势?

查询为:

  SELECT url, 
         title, 
         published, 
         reddit_key, 
         UNIX_TIMESTAMP(last_fetched) last_fetched, 
         comment_link 
    FROM GrabbedSites 
   WHERE published BETWEEN DATE_SUB('2010-09-03', INTERVAL 1 DAY) 
                       AND '2010-09-03' 
ORDER BY published;

仅索引是已发布".

解释说:在哪里使用；使用文件排序

Explain says: Using where; Using filesort

推荐答案

首先要注意的是，MySQL每个psuedo-SELECT仅使用一个索引(不是语句)-使用EXPLAIN查看SELECT的输出时，您会发现会看到每个选择了哪个索引. EXPLAIN只能在SELECTS上运行，因此当您换出SELECT的语法时，我们必须假定DELETE/UPDATE使用相同的计划.

First thing to be aware of is that MySQL only uses one index per psuedo-SELECT (not statement) - when you view output of the SELECT using EXPLAIN, you'll see which index was chosen per. EXPLAIN can only be run on SELECTS, so we have to assume that a DELETE/UPDATE is using the same plan when you swap out the syntax for SELECT...

据我所知，大多数数据库(嵌入式数据库可能很奇怪)都支持在以下子句中使用索引:

Most databases (embedded ones can be odd) to my knowledge support the use of indexes in the following clauses:

• 选择
• JOIN(ANSI-92语法)
• 在哪里(因为这里同时有ANSI-89 和过滤器)
• 具有(等同于WHERE，但与WHERE不同-允许聚合使用而无需子查询)
• 订购

• SELECT
• JOIN (ANSI-92 syntax)
• WHERE (because there's both ANSI-89 and filteration here)
• HAVING (WHERE equivalent, but unlike WHERE - allows aggregate use without needing subquery)
• ORDER BY

我不是GROUP BY的100％会员，所以我暂时省略了它.

I'm not 100% on GROUP BY, so I'm omitting it for the time being.

最终，它是基于其算法和现有统计信息使用的优化器选择.您可以使用 ANALYZE TABLE语法刷新统计信息(定期，但不经常).

Ultimately, it's the optimizers choice for what to use based on it's algorithm and the statistics it has onhand. You can use the ANALYZE TABLE syntax to refresh the statistics (periodically, not constantly please).

MySQL还限制了分配索引的空间量-.由于MySQL每个伪SELECT仅使用一个索引，因此覆盖索引(包括多于一个列的索引)是一个好主意，但实际上它涉及到测试最常见的查询&尽最大可能对其进行优化.索引优先级应为:

MySQL also limits the amount of space that for allocating indexes - 1,000 bytes for MyISAM tables, and 767 bytes for InnoDB tables. Because of MySQL only using one index per psuedo-SELECT, covering indexes (indexes that include more than one column) are a good idea but it really comes to testing the most common query & optimizing for it as best you can. The indexing priority should be:

1. 主键(在v5中的某个地方，自动为pk创建索引)
2. 外键(下一个最有可能成为JOIN候选人
3. 过滤条件(假设您有空间)

这篇关于在MySQL中，如果对foo进行了索引，放置SELECT foo的性能是否会提高?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！