mysql -大型表-索引性能和查询问题

Question

我简化了一个大得多的表格：

CREATE TABLE `core` (
  `id` int NOT NULL,
  `loc_country` enum('United States','Colombia','United Kingdom',       
       'Australia','India','Germany','Canada','Korea','Netherlands',
       '200 more')  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `loc_city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
  `job` enum('a','b','c','d') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
          ROW_FORMAT=COMPRESSED




explain format=json
    SELECT id FROM core
        WHERE id!=518601449
          AND loc_country='Mongolia'
          AND id < 518601449
          AND job='a'
        LIMIT 151\G

*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14002.99"
    },
    "table": {
      "table_name": "core",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY",
        "loc_country_2"
      ],
      "key": "loc_country_2",
      "used_key_parts": [
        "loc_country",
        "job",
        "loc_city",
        "id"
      ],
      "key_length": "34",
      "rows_examined_per_scan": 45657,
      "rows_produced_per_join": 45657,
      "filtered": "100.00",
      "using_index_for_skip_scan": true,
      "cost_info": {
        "read_cost": "9437.29",
        "eval_cost": "4565.70",
        "prefix_cost": "14002.99",
        "data_read_per_join": "1G"
      },
      "used_columns": [
        "id",
        "loc_country",
        "job"
      ],
      "attached_condition": "((`api`.`core`.`job` = 'a') and (`api`.`core`.`loc_country` = 'Mongolia') and (`api`.`core`.`id` <> 518601449) and (`api`.`core`.`id` < 518601449))"
    }
  }
}

这个查询需要14秒才能运行，我需要在0.01秒内完成

最大的问题似乎是使用id < XXX并按id排序，我认为这应该是“免费”使用，因为id是主键。

我需要id <和排序，因为我需要用每个查询从数据库中获取不同的部分，如果不使用它，我将为每个country+job接收相同的数据。

我无法理解表，因为我有几十个这样的查询使用不同的列，这只是一个例子。

我相信压缩会产生很大的影响，虽然我的NVME磁盘上没有不用压缩就能运行的存储空间，但这可能是我的大部分问题的原因。

将主键添加到我所拥有的索引中会有帮助吗？在最后？我担心它会浪费大量的存储空间。

有什么想法吗？

Answer 1

方面#1:索引

不需要向索引中添加主键值id。为什么？

根据MySQL的分组索引和次级索引，第1、2段，在副标题下How Secondary Indexes Relate to the Clustered Index文档，可以这样说：

聚集索引以外的所有索引都称为二级索引。在InnoDB中，辅助索引中的每个记录都包含行的主键列以及为辅助索引指定的列。InnoDB使用此主键值搜索聚集索引中的行。如果主键是长的，则辅助索引使用更多的空间，因此具有较短的主键是有利的。

因此，主键将自动添加到辅助索引中。

这一点的证据在XML输出中。

  "used_key_parts": [
    "loc_country",
    "job",
    "loc_city",
    "id"
  ],

和

  "used_columns": [
    "id",
    "loc_country",
    "job"
  ],

方面#2:表压缩

压缩可能是造成缓慢性的主要原因。毕竟，InnoDB必须解压缩来自该表的数据，才能驻留在InnoDB缓冲池中。此外，InnoDB缓冲池包含压缩和未压缩的数据和索引页。怎么会这样？

根据MySQL关于InnoDB表的压缩工作方式的文档，Compression and the InnoDB Buffer Pool副标题下的第1、2段如下：

在压缩InnoDB表中，每个压缩页(无论是1K、2K、4K还是8K)都对应16K字节的未压缩页(如果设置了innodb_page_size，则对应较小的大小)。要访问页面中的数据，如果压缩页尚未在缓冲池中，则MySQL从磁盘读取压缩页，然后将该页解压缩为其原始形式。本节描述InnoDB如何相对于压缩表的页管理缓冲池。为了最小化I/O并减少解压缩页的需要，缓冲池有时包含数据库页的压缩形式和未压缩形式。为了为其他所需的数据库页腾出空间，MySQL可以从缓冲池中删除一个未压缩的页，同时将压缩页留在内存中。或者，如果有一段时间没有访问某个页面，则可能会将该页的压缩形式写入磁盘，以便腾出空间用于其他数据。因此，在任何给定的时间，缓冲池可能同时包含页面的压缩形式和未压缩形式，或者只包含页面的压缩形式，或者两者都不包含。

建议#1

因为缓冲池包含压缩和未压缩的页面，所以增加InnoDB缓冲池的大小可能会稍微改进一些事情。怎么做到的？

InnoDB必须从缓冲区池中删除未使用的页面。更大的缓冲池减少了缓冲池被清除的次数，从而为新页创建了波形。

建议#2

也许改变压缩页面的大小可能会稍微改善一些事情。这将需要用新的压缩大小重新加载表。请看我的旧帖子诺姆b_文件_船型梭鱼关于改变KEY_BLOCK_SIZE将是必要的。

更新2020-12-12 19:52

下面是一个疯狂的小把戏

而不是查询

SELECT id FROM core
WHERE id!=518601449
AND loc_country='Mongolia'
AND id < 518601449 AND job='a'  LIMIT 151\G

尝试如下重构

SELECT id FROM (SELECT id FROM core WHERE loc_country='Mongolia' AND job='a') A 
WHERE id < 518601449 LIMIT 151\G

这将迫使loc_country_2索引首先在子查询中收集in。然后，所有ID< 518601449被驳回。最后，规定了151的限制。

我不能保证结果会更好。可能会更糟。

你不会知道，直到你尝试，看到或至少看看解释计划！

Answer 2

去掉前缀索引

KEY `loc_country_2` (`loc_country`,`job`,`loc_city`(6))

-->

KEY `loc_country_2` (`loc_country`,`job`)

它很少有好处。对于这个特定的查询，它阻碍了(并且损害了性能)。如果您过于简化查询，这里的任何建议都可能是无用的。

我需要用每个查询从数据库中得到一个不同的部分，

你是说“分页”吗？通过OFFSET？它会退化。或者id习惯于“记得你停下来的地方”？请参阅http://mysql.rjweb.org/doc.php/pagination

我有几十个这样的查询使用不同的列，

请再举几个例子--否则我们的建议就没用了。

请提供SHOW TABLE STATUS LIKE 'core'; --我希望看到表的大小、行大小和其他一些东西。

在我到目前为止看到的Using index merge intersect的每一种情况下，一个合适的综合指数都会更好地工作。