mysql - 如何在 MySQL 中正确使用索引

Question

我正在运行一个相当简单的自动目录

CREATE TABLE catalog_auto (
    id INT(10) UNSIGNED NOT NULL auto_increment,
    make varchar(35),
    make_t varchar(35),
    model varchar(40),
    model_t varchar(40),
    model_year SMALLINT(4) UNSIGNED,
    fuel varchar(35),
    gearbox varchar(15),
    wd varchar(5),
    engine_cc SMALLINT(4) UNSIGNED,
    variant varchar(40),
    body varchar(30),
    power_ps SMALLINT(4) UNSIGNED,
    power_kw SMALLINT(4) UNSIGNED,
    power_hp SMALLINT(4) UNSIGNED,
    max_rpm SMALLINT(5) UNSIGNED,
    torque SMALLINT(5) UNSIGNED,
    top_spd SMALLINT(5) UNSIGNED,
    seats TINYINT(2) UNSIGNED,
    doors TINYINT(1) UNSIGNED,
    weight_kg SMALLINT(5) UNSIGNED,
    lkm_def TINYINT(3) UNSIGNED,
    lkm_mix TINYINT(3) UNSIGNED,
    lkm_urb TINYINT(3) UNSIGNED,
    tank_cap TINYINT(3) UNSIGNED,
    co2 SMALLINT(5) UNSIGNED,
    PRIMARY KEY(id),
    INDEX `gi`(`make`,`model`,`model_year`,`fuel`,`gearbox`,`wd`,`engine_cc`),
    INDEX `mkt`(`make`,`make_t`),
    INDEX `mdt`(`make`,`model`,`model_t`)
);

到目前为止，该表大约有 60,000 行，因此，即使没有索引，也没有什么简单的查询无法处理。

关键是，我正在尝试掌握使用索引的窍门，所以我根据最频繁的查询做了一些。

假设我想要 engine_cc 来满足一组特定的条件，如下所示:

SELECT DISTINCT engine_cc FROM catalog_auto WHERE make='audi' AND model='a4' and model_year=2006 AND fuel='diesel' AND gearbox='manual' AND wd='front';

EXPLAIN 说:

+----+-------------+--------------+------+---------------+------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref                                 | rows | Extra                    |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------+------+--------------------------+
|  1 | SIMPLE      | catalog_auto | ref  | gi,mkt,mdt    | gi   | 408     | const,const,const,const,const,const |    8 | Using where; Using index |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------+------+--------------------------+

查询正在按预期使用 gi 索引，这里没问题。

选择基本标准后，我还需要其余的列:

SELECT * FROM catalog_auto WHERE make='audi' AND model='a4' and model_year=2006 AND fuel='diesel' AND gearbox='manual' AND wd='front' AND engine_cc=1968;

EXPLAIN 说:

+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref                                       | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+
|  1 | SIMPLE      | catalog_auto | ref  | gi,mkt,mdt    | gi   | 411     | const,const,const,const,const,const,const |    3 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+

它选择了一个 KEY 但没有使用索引。然而，查询速度非常快(集合中有 1 行(0.00 秒))，但由于该表没有那么多行，我假设即使没有索引，它也是一样的。

这样试过:

SELECT * FROM catalog_auto WHERE id IN (SELECT id FROM catalog_auto WHERE make='audi' AND model='a6' AND model_year=2009);

同样，在 EXPLAIN 中:

+----+--------------------+--------------+-----------------+--------------------+---------+---------+------+-------+-------------+
| id | select_type        | table        | type            | possible_keys      | key     | key_len | ref  | rows  | Extra       |
+----+--------------------+--------------+-----------------+--------------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY            | catalog_auto | ALL             | NULL               | NULL    | NULL    | NULL | 59060 | Using where |
|  2 | DEPENDENT SUBQUERY | catalog_auto | unique_subquery | PRIMARY,gi,mkt,mdt | PRIMARY | 4       | func |     1 | Using where |
+----+--------------------+--------------+-----------------+--------------------+---------+---------+------+-------+-------------+

仍然不使用任何索引，甚至不使用 PRIMARY KEY。这不应该至少使用 PRIMARY KEY 吗？

文档说:MySQL 可以忽略一个键，即使它找到一个键，如果它确定全表扫描会更快，这取决于查询。

这就是它不使用任何索引的原因吗？这是一个好习惯吗？如果不是，对于 SELECT * 语句，您会如何建议索引列始终使用索引，给定上述查询。

我不是 MySQL 专家，因此非常感谢任何指点。

将 MySQL 5.5 与 InnoDB 结合使用。

Answer 1

我基本上说的是与@DStanley 所说的相同的答案，但我想对它进行更多的扩展，以至于我无法在评论中发表。

“使用索引”注释表示查询仅使用索引来获取所需的列。
没有这个注释并不意味着查询没有使用索引。

您应该查看 EXPLAIN 报告中的 key 列:

+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref                                       | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+
|  1 | SIMPLE      | catalog_auto | ref  | gi,mkt,mdt    | gi   | 411     | const,const,const,const,const,const,const |    3 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-------------------------------------------+------+-------------+

key 列表示优化器选择使用 gi 索引。所以它是使用索引。 ref 列确认引用了该索引的所有七个列。

它必须获取更多列才能返回 * 的事实意味着它不能声明“使用 [only] 索引”。

另请阅读 https://dev.mysql.com/doc/refman/5.6/en/explain-output.html 中的这段摘录:

• Using index

  The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

---

我想到了这个类比，电话簿:

如果您在电话簿中查找公司，效率很高，因为电话簿是按名称的字母顺序排列的。当您找到它时，您还会在同一条目中找到电话号码。因此，如果这就是您所需要的，那将非常快。这是一个仅索引查询。

如果您想了解有关该企业的额外信息，例如他们的营业时间或证书，或者他们是否销售某种产品​​，您必须执行额外的步骤，即使用该电话号码给他们打电话询问。这是获得该信息的额外几分钟时间。但是您仍然能够找到电话号码，而无需阅读整个电话簿，所以至少不需要花费数小时或数天的时间。这是一个使用索引的查询，但还必须从表中查找行以获取其他数据。