MySQL 两列上的索引

Question

我尝试通过这种方式在我的表上创建一个两列索引:

CREATE INDEX prod_hash_index ON components(producer_normalized, hash);

这会导致不是一个索引，而是两个索引(当我执行 SHOW INDEXES FROM Components; 时):

+------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name        | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| components |          0 | PRIMARY         |            1 | id                  | A         |     1570231 |     NULL | NULL   |      | BTREE      |         |               |
| components |          1 | item_num        |            1 | item_num            | A         |     1570231 |     NULL | NULL   |      | BTREE      |         |               |
| components |          1 | fs_index        |            1 | first_symbol        | A         |         303 |     NULL | NULL   |      | BTREE      |         |               |
| components |          1 | prod_hash_index |            1 | producer_normalized | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| components |          1 | prod_hash_index |            2 | hash                | A         |     1570231 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

这是什么意思？ MySQL 是否创建了额外的一列索引？

Answer 1

您在结果面板中的每一行中读取索引名称和索引中的列。

关于 prod_has_index 你有，第一行关于位置一的 Producer_normalized 列另一行的列位于位置 (seq_in_index) 2。