需要澄清一下MySQL索引

I've been thinking about my database indexes lately, in the past I just kind of non-chalantly threw them in as an afterthought, and never really put much thought into if they are correct or even helping. I've read conflicting information, some say that more indexes are better and others that too many indexes are bad, so I'm hoping to get some clarification and learn a bit here.

我最近一直在考虑我的数据库索引，过去我只是漫不经心地把它们作为事后考虑，从来没有真正考虑过它们是否正确，甚至是否有帮助。我读过一些相互矛盾的信息，有人说索引越多越好，有人说索引太多越不好，所以我希望在这里得到一些澄清和了解。

Let's say I have this hypothetical table:

假设我有一个假设的表格

CREATE TABLE widgets (
    widget_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    widget_name VARCHAR(50) NOT NULL,
    widget_part_number VARCHAR(20) NOT NULL,
    widget_price FLOAT NOT NULL,
    widget_description TEXT NOT NULL
);

I would typically add an index for fields that will be joined and fields that will be sorted on most often:

我通常会为将要加入的字段和最常排序的字段添加索引:

ALTER TABLE widgets ADD INDEX widget_name_index(widget_name);

So now, in a query such as:

现在，在查询中，例如:

SELECT w.* FROM widgets AS w ORDER BY w.widget_name ASC

The widget_name_index is used to sort the resultset.

widget_name_index用于对resultset排序。

Now if I add a search parameter:

现在如果我添加一个搜索参数:

SELECT w.* FROM widgets AS w 
WHERE w.widget_price > 100.00 
ORDER BY w.widget_name ASC

I guess I need a new index.

我想我需要一个新的指数。

ALTER TABLE widgets ADD INDEX widget_price_index(widget_price);

But, will it use both indexes? As I understand it it won't...

但是，它会同时使用两个索引吗?据我所知它不会……

ALTER TABLE widgets ADD INDEX widget_price_name_index(widget_price, widget_name);

Now widget_price_name_index will be used to both select and order the records. But what if I want to turn it around and do this:

现在widget_price_name_index将用于选择和排序记录。但是如果我想把它翻转过来

SELECT w.* FROM widgets AS w 
WHERE w.widget_name LIKE '%foobar%'
ORDER BY w.widget_price ASC

Will widget_price_name_index be used for this? Or do I need a widget_name_price_index also?

会使用widget_price_name_index吗?或者我也需要widget_name_price_index吗?

ALTER TABLE widgets ADD INDEX widget_name_price_index(widget_name, widget_price);

Now what if I have a search box that searches widget_name, widget_part_number and widget_description?

现在，如果我有一个搜索框来搜索widget_name、widget_part_number和widget_description，该怎么办?

ALTER TABLE widgets
ADD INDEX widget_search(widget_name, widget_part_number, widget_description);

And what if end users can sort by any column? It's easy to see how I could end up with more than a dozen indexes for a mere 5 columns.

如果终端用户可以按任何列进行排序呢?很容易看出，我如何能够在仅仅5列中得到超过12个索引。

If we add another table:

如果我们再加一张表:

CREATE TABLE specials (
    special_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    widget_id INT UNSIGNED NOT NULL,
    special_title VARCHAR(100) NOT NULL,
    special_discount FLOAT NOT NULL,
    special_date DATE NOT NULL
);
ALTER TABLE specials ADD INDEX specials_widget_id_index(widget_id);
ALTER TABLE specials ADD INDEX special_title_index(special_title);

SELECT w.widget_name, s.special_title
FROM widgets AS w
INNER JOIN specials AS s ON w.widget_id=s.widget_id
ORDER BY w.widget_name ASC, s.special_title ASC

I am assuming this will use widget_id_index and the widgets.widget_id primary key index for the join, but what about the sorting? Will it use both widget_name_index and special_title_index ?

我假设这将使用widget_id_index和小部件。连接的widget_id主键索引，但是排序呢?它会同时使用widget_name_index和special_title_index吗?

I don't want to ramble on too long, there are an endless number of scenarios I could conujure up. Obviously this can get much more complex with real world scenarios rather than a couple of simple tables. Any clarification would be appreciated.

我不想漫谈太长时间，我可以想象出无数的场景。显然，对于现实世界的场景来说，这可能会变得复杂得多，而不是一些简单的表。如有任何澄清，将不胜感激。

3 个解决方案

#1

By best practices, you do not have to create an index while defining the table schematics. It is always better to create an index as you create the queries in your application. In most cases, you will be starting with a single-column index to satisfy a query. If you want to use many columns in a query, you can create a covering index.

通过最佳实践，您不必在定义表原理图时创建索引。在应用程序中创建查询时，最好创建索引。在大多数情况下，您将从单列索引开始，以满足查询。如果希望在查询中使用多个列，可以创建覆盖索引。

A covering index is an index with two or more columns in it. If the index satisfies all the column requirements of a query, then the storage engine can obtain all the results from the index instead of kicking in a disk I/O operation. So, when creating a query that uses more columns, you can either create a new index covering all the required columns, or, you can extend the existing index to include more columns.

覆盖索引是包含两个或多个列的索引。如果索引满足查询的所有列需求，那么存储引擎就可以从索引中获取所有结果，而不必进行磁盘I/O操作。因此，当创建使用更多列的查询时，您可以创建一个包含所有所需列的新索引，或者，您可以将现有索引扩展到包含更多列。

You have to take some considerations while doing any one of the above. MySQL considers an index only when the left-most column of the index can be used in the query. Otherwise, it simply seeks the whole table for fetching results. So if you can extend an existing index without affecting all the queries that use that index, then it would be a wise choice. Otherwise, you can go ahead and create a new index for the new query. Sometimes, the queries can be adjusted to adapt to the index structure.

在做上述任何一种工作时，您都需要考虑一些问题。MySQL只在查询中可以使用索引的最左列时才考虑索引。否则，它只是寻找整个表来获取结果。因此，如果您可以在不影响所有使用该索引的查询的情况下扩展现有索引，那么这将是一个明智的选择。否则，您可以继续为新的查询创建一个新的索引。有时，可以调整查询以适应索引结构。

#2

An index speeds up selects, but slows down inserts and updates. You don't need to create an index for every possible combination of columns you can imagine. I usually just create the obvious indexes that I know I will be using often, and only add more if I can see that they are needed after taking performance measurements. The database can still use an index even if it doesn't cover all the columns in the query.

索引加速选择，但减慢插入和更新。您不需要为您可以想象的每个列组合创建索引。我通常只创建我知道我将经常使用的明显索引，并且只有在进行性能度量之后才能看到它们是必需的时才添加更多的索引。即使数据库没有覆盖查询中的所有列，它仍然可以使用索引。

#3

Only one index is ever used in a query. Fortunately, you can create an index covering multiple columns:

查询中只使用一个索引。幸运的是，您可以创建一个包含多个列的索引:

ALTER TABLE widgets ADD INDEX name_and_price_index(widget_name, widget_price);

The above index will be used if you SELECT by widget_name or widget_name + widget_price (but not just widget_price).

如果您选择widget_name或widget_name + widget_price(但不只是widget_price)，那么将使用上面的索引。

As MitMaro points out, use EXPLAIN on a query to see what indexes MySQL has to choose from, as well as what index it ends up using. See here for even more details.

正如MitMaro指出的，在查询中使用EXPLAIN查看MySQL必须选择什么索引，以及它最终使用什么索引。更多细节请看这里。

#1

#2

#3