主页 / dba / 问题 / 54251
Accepted
dotancohen
Asked: 2013-12-03 04:58:58 +0800 CST

在哪些字段上建立索引？查询的字段 (WHERE) 或返回的字段 (SELECT)

  • 772

我在AWS RDS Medium 实例上有一个大型 MySQL 数据库表（约 100 万行并且还在增长）：

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

我经常需要执行以下查询的变体：

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

以及相关的EXPLAIN：

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

我应该进行哪些优化？我应该在 and 字段上添加索引name，zip还是在addedandsource字段上添加索引？

mysql optimization

1 个回答

  1. Best Answer

    接受的答案忽略了覆盖索引的概念，也没有提到多列索引在一个索引中的重要性。

    WHERE子句中两列的单个索引：

    ALTER TABLE clients ADD KEY(source,added) -- adding this 
ALTER TABLE clients ADD KEY(added,source) -- or this

    ...通常会比每列上的单个索引更能帮助您，因为优化器可能只会选择使用两列之一。上述指标中的哪一个更有帮助取决于“源”和“添加”中值的分布。用于查询的所选索引将出现在EXPLAIN输出的“键”中。“Using where”通常意味着，在选择的查询计划将导致提取的行中，服务器意识到其中一些仍然不符合选择标准并且必须随后由服务器过滤（如在例如，由于没有使用索引，可能必须过滤大量数据）。

    覆盖索引也可能特别有价值，因为与“引擎可以快速轻松地获取行中的字段”的断言相反，它们只是比通过扫描整个表来查找行更快更容易——它们仍然需要时间，并消耗资源。

    这就是覆盖索引的用武之地。使用 (source,added,zip,name) 添加索引可能会显着提高性能，因为一旦服务器使用索引找到相关行，就不需要查找其余数据，因为数据实际上在索引中。当使用覆盖索引时，explain 的“key”列将包含正在使用的索引的名称，“Extra”列将包含“using index”（意思是，使用索引实际检索数据，而不仅仅是找到它。）

    因此，虽然您确实根据选择标准编制了索引，但这并不是全部。

    另请注意，无论索引什么，索引只会用于从索引中最左边的列开始的实际搜索，直到遇到不在WHERE子句中的列。

    因此，(source,added) 上的索引可以优化WHERE子句中同时包含“source”和“added”的查询的行查找......WHERE用于在 where 子句中仅“添加”的查找，因为在其左侧有一列未被使用。类似地，(source,added,zip,name) 上的单个索引可以优化查询查找，其中WHERE子句提及 source ... 或 source and added ... 或 source and added and zip ... 或 source and added and zip and名称......但不仅仅是“zip”......不仅仅是“名称”......不是“添加”和“名称”和“zip”......你明白了。索引从以下位置开始和右侧无关紧要，WHERE

    请注意，只要所有条件都是 ，您在 where 子句中列出事物的顺序就没有区别AND。你会在网上找到这种误解。优化器将任何等效表达式理解为等效。

    此外，除非您明确禁用它，否则 IN('Foo','foo') 是多余的，因为由于排序规则，选择将不区分大小写，因此 'foo' 应该足以找到任何大写排列。

    • 5

相关问题