如何在mysql中明智地应用索引-Howtojudiciouslyapplyindexesinmysql

2  

As widely as they are used, an index is best used on any columns that are used often in both a statement's WHERE clause and ORDER BY clause.

与使用它们一样广泛,索引最好用于语句的WHERE子句和ORDER BY子句中经常使用的任何列。

Take for instance, the following table:

举个例子,如下表:

CREATE TABLE `people` (
    `id` int,
    `name` varchar(255),
    `phone` varchar(10),
    `state` varchar(50),
    `city` varchar(100),
    `zip` int
);

By default, you'll probably only have an index on the id column. Now, let's say you have 20k "people" in your database. When searching by any field other than the id field, you may start to notice a bit of lag. This is where indexes will come in.

默认情况下,您可能只在id列上有一个索引。现在,假设您的数据库中有20k“人”。当使用除id字段以外的任何字段进行搜索时,您可能会开始注意到一点滞后。这是索引进入的地方。

There's a good chance that you won't be searching by the phone field (possible, but not likely), so we wouldn't index that one. However, searching by state/city/zip are more likely. When you search by city/state, you often search with them both together with a WHERE clause similar to:

您很可能不会通过电话领域进行搜索(可能,但不太可能),因此我们不会将其编入索引。但是,更有可能按州/城市/邮编搜索。当你按城市/州搜索时,你经常用它们和WHERE子句一起搜索,类似于:

WHERE state = 'Florida' AND city = 'Miami';

Because they're often-used together, you should have an index created with both fields (see multiple-column indexes). On the other hand, when you search by zip, you often do so without a city/state attached, such as:

因为它们经常一起使用,所以应该使用两个字段创建索引(请参阅多列索引)。另一方面,当您通过zip搜索时,通常会在没有附加城市/州的情况下进行搜索,例如:

WHERE zip = 12345;

With this, you'll create an index just with the zip field.

有了这个,你将使用zip字段创建一个索引。

Also, it is worth a note that indexes take up not only space, but need to be modified during any INSERT or UPDATE on a table with them. If you have numerous indexes and a lot of records, a single INSERT's time can start to be noticed. So, be sure that you actually need the additional performance boost that the index will give before adding them like salt+pepper.

此外,值得注意的是索引不仅占用空间,而且需要在具有它们的表上的任何INSERT或UPDATE期间进行修改。如果您有许多索引和大量记录,则可以开始注意单个INSERT的时间。因此,请确保您在添加它们之前确实需要额外的性能提升,例如盐+胡椒。