主页 / dba / 问题 / 204076
Accepted
dr_
Asked: 2018-04-17 05:58:10 +0800 CST

为什么 pt-duplicate-key-checker 建议删除复合索引？

  • 772

pt-duplicate-key-checker这是搜索冗余索引的 Percona 工具的输出片段：

# Key myidx ends with a prefix of the clustered index
# Key definitions:
#   KEY `myidx` (`bar`,`foo`)
#   PRIMARY KEY (`foo`),
# Column types:
#         `bar` mediumint(8) unsigned not null default '0'
#         `foo` mediumint(8) unsigned not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `mydb`.`mytable` DROP INDEX `myidx`, ADD INDEX `myidx` (`bar`);

为什么该工具会提示这个？原来的复合索引就不能用了吗？

据我了解，bar给定 PK 上的索引值得删除(bar,foo)，但这里并非如此。

mysql index

3 个回答

  1. Best Answer

    主键是 InnoDB 中任何辅助键的一部分。

    • 2

  2. 我不同意该工具给出的分析。

    当我看到 时INDEX(bar, foo)，我假设有一些查询需要这两列按此顺序位于此复合索引中。

    fooPK 与INDEX(bar)上述指标相同的事实无关紧要。

    当我看到 justINDEX(bar)时，我假设有一些查询(bar)不需要id.

    当我看到两者时，我会说较短的是“多余的”并建议将其删除。

    此外，“缩短这个重复的聚集索引”是错误的。 INDEX(bar)不比INDEX(bar, foo). 而且它不是“聚集索引”。只有PK是“聚集的”。

    如果是UNIQUE(bar, foo)，那么我会建议更改UNIQUE为INDEX. 这样就INSERTs不必进行不必要的唯一性检查。

    • 2

  3. 让我们创建一个简单的表，看看 MySQL（5.7.20 MySQL 社区服务器）必须告诉我们什么：

    mysql> create table test_dupe_key (foo int unsigned not null auto_increment primary key, bar int unsigned not null, random int unsigned not null default 0, key(bar, foo)) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> show create table test_dupe_key\G
*************************** 1. row ***************************
       Table: test_dupe_key
Create Table: CREATE TABLE `test_dupe_key` (
  `foo` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `bar` int(10) unsigned NOT NULL,
  `random` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`foo`),
  KEY `bar` (`bar`,`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into test_dupe_key(bar) values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_dupe_key;
+-----+-----+--------+
| foo | bar | random |
+-----+-----+--------+
|   1 |   1 |      0 |
|   2 |   2 |      0 |
|   3 |   3 |      0 |
+-----+-----+--------+
3 rows in set (0.00 sec)

    这是一个可以从索引中读取的简单查询：

    mysql> explain format=json select foo, bar from test_dupe_key\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.60"
    },
    "table": {
      "table_name": "test_dupe_key",
      "access_type": "index",
      "key": "bar",
      "used_key_parts": [
        "bar",
        "foo"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.60",
        "prefix_cost": "1.60",
        "data_read_per_join": "48"
      },
      "used_columns": [
        "foo",
        "bar"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

    这表明密钥条已被使用并且有 2 个密钥部分，都被读取以产生长度为 8 (2x4) 的密钥。现在让我们切换单个字段的复合键并再次检查：

    mysql> alter table test_dupe_key drop key bar, add key(bar);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain format=json select foo, bar from test_dupe_key\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.60"
    },
    "table": {
      "table_name": "test_dupe_key",
      "access_type": "index",
      "key": "bar",
      "used_key_parts": [
        "bar"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 3,
      "rows_produced_per_join": 3,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.60",
        "prefix_cost": "1.60",
        "data_read_per_join": "48"
      },
      "used_columns": [
        "foo",
        "bar"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

    现在只有关键部分和长度发生了变化，这是符合预期的，但它仍然是一个索引读取。如果我们将其转换为对 PK 字段的范围查询，并对索引字段进行约束，让我们看看会发生什么：

    mysql> explain format=json select foo, bar from test_dupe_key where bar = 1 and foo > 0\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.20"
    },
    "table": {
      "table_name": "test_dupe_key",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "bar"
      ],
      "key": "bar",
      "used_key_parts": [
        "bar",
        "foo"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.20",
        "prefix_cost": "1.20",
        "data_read_per_join": "16"
      },
      "used_columns": [
        "foo",
        "bar"
      ],
      "attached_condition": "((`stack_204076`.`test_dupe_key`.`bar` = 1) and (`stack_204076`.`test_dupe_key`.`foo` > 0))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

    查询规划器已经考虑了查询的 PK，但选择了 的索引bar，它现在对之前读取的索引有一个有趣的变化，因为我们可以看到它现在再次显示为 2 个关键部分，长度为 8：

      "key": "bar",
  "used_key_parts": [
    "bar",
    "foo"
  ],
  "key_length": "8"

    这告诉我们MySQL已经访问了自动包含在二级索引中的PK

    • 2

相关问题