时间戳

首页 > 解决方案 > 为什么索引列上的 SELECT DISTINCT 不是瞬时的？

mysql - 为什么索引列上的 SELECT DISTINCT 不是瞬时的？

问题描述

我有一个这样的表，它存储正在运行的各种程序的配置。它看起来像这样：

+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| Date         | date          | YES  | MUL | NULL    |       |
| Program      | varchar(20)   | YES  | MUL | NULL    |       |
| ConfigFile   | int(11)       | YES  |     | NULL    |       |
| Parameter    | varchar(20)   | YES  |     | NULL    |       |
| Value        | varchar(20)   | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+

该ConfigFile字段包含配置文件的编号——对于某些程序，可以选择多个配置文件。

它有几个索引，如下所示：

+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lists |          1 | Date     |            1 | Date         | A         |     1108060 |     NULL | NULL   | YES  | BTREE      |         |               |
| lists |          1 | Date     |            2 | Program      | A         |     1108060 |     NULL | NULL   | YES  | BTREE      |         |               |
| lists |          1 | Date     |            3 | Parameter    | A         |     1108060 |     NULL | NULL   | YES  | BTREE      |         |               |
| lists |          1 | Program  |            1 | Program      | A         |        4676 |     NULL | NULL   | YES  | BTREE      |         |               |
| lists |          1 | Program  |            2 | Parameter    | A         |      183706 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

现在假设我想知道给定程序的参数是什么。看来我应该能够做这样的事情：

SELECT DISTINCT Parameter FROM params WHERE Program = 'MyProgram';

这有以下解释计划：

+----+-------------+--------+------------+------+----------------+---------+---------+-------+-----------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys  | key     | key_len | ref   | rows      | filtered | Extra                    |
+----+-------------+--------+------------+------+----------------+---------+---------+-------+-----------+----------+--------------------------+
|  1 | SIMPLE      | params | NULL       | ref  | Date,Program   | Program | 23      | const | 137203382 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+------+----------------+---------+---------+-------+-----------+----------+--------------------------+

有 15 种不同的选择，每个程序Program可能有 10 到 100 个值。Parameter

根据我对数据库索引如何工作的理解，我希望这会立即完成。特别是，我希望底层数据结构是具有 15 个节点的二叉搜索树，我会搜索它以找到与我的程序相对应的节点；找到我的程序后，它会将我带到可能有 100 个或更少节点的第二个二叉搜索树，然后我会简单地遍历它。

但是，当我实际运行查询时，它最终需要几分钟。

对我来说，这表明二叉搜索树中可能存在相同值的多个副本，表的每个节点一个。这是正在发生的事情吗？如果是这样，我能做些什么来缓解这种情况？

我考虑过拥有一个具有唯一三元组（日期、程序、参数）的表并具有关系，但我不确定在这种情况下如何执行数据的批量插入。如果我错了为什么它这么慢那么当然这甚至没有帮助。

标签： mysqlmariadbb-treedatabase-indexes

解决方案

InnoDB 的 B+Tree 二级索引不是这样形成的。这样想：

  1. 对于每一行，构造一个由 , , 组成Program的Parameter字符串PK。
  2. 对这些字符串进行排序。
  3. 将它们放在 BTree 中。

注意：没有任何分裂的暗示Program。如果 99.9% 的项目都在项目 #5 中会怎样？那将是一个相当不平衡的 BTree。对于您的一个罕见的查询很方便，但对于大多数其他查询来说较慢。

使用平衡良好的 B+Tree，您的查询必须：

  1. 向下钻取 BTree 以找到第一个“行”Program = 'MyProgram'
  2. 通过 B+Tree 的叶子节点向前走，使用“+”从一个叶子块步进到下一个叶子块。
  3. 走路时，跟踪每一个新的Parameter。
  4. Program = 'MyProgram'失败时退出。

笔记：

  • DISTINCT通过了解物品的排序方式，在我的步骤 3 中很容易实现。
  • “使用索引”表示索引是“覆盖”——因为您只需要Programand Parameter（这些是 中的列INDEX）。PK 也隐含地可用于“覆盖”。
  • 您提供的 15 不同意“4676”的基数。但这只是指出，统计数据有时相差甚远。（统计信息对此查询的优化没有影响。）

我考虑过拥有一个具有唯一三元组（日期、程序、参数）的表

是的，拥有这样的表会使您的查询运行得更快。但是这样的维护值得吗？

该表可以让您做的另一件事是将这 3 列标准化为单个MEDIUMINT UNSIGNED（仅 3 个字节），而不是现在在平均行上使用 30 个字节。再一次，等的复杂性会JOINs超过好处吗？它可能会将磁盘占用空间缩小 50%。

推荐阅读