python - MYSQL//当减少不同条目的数量时， "DISTINCT"的性能下降了大约 100000 个条目

Question

在我们的 SQL 查询中插入 DISTINCT 命令时，我们正在处理一些性能问题。
该问题仅在以下情况下发生:100000 个(或更多)条目中只有约 1%(或更少)的不同值。

我们将问题归结为以下最小的python示例(但它与python无关，mysql工作台的行为相同):

import mysql.connector
import time
import numpy as np

conn = mysql.connector.connect(user='user', password='password', host='server',
                                            database='database', raise_on_warnings=True, autocommit=False)
cursor = conn.cursor()

#define amount of entries
max_exponent = 4.7
n_entry = 10**max_exponent

# fill table with 10, 100, ... distinct entries
for n_distinct in np.logspace(1, max_exponent, num=int(max_exponent)):

    # Dropping BENCHMARK table if already exists and create new one
    cursor.execute("DROP TABLE IF EXISTS BENCHMARK")
    cursor.execute('CREATE TABLE BENCHMARK(ID INT)')

    # create distinct number set and insert random permutation of it into table
    distinct_numbers = range(int(n_distinct))
    random_numbers = np.random.randint(len(distinct_numbers), size=int(n_entry))
    value_string = ','.join([f"({i_name})" for i_name in random_numbers])
    mySql_insert_query = f"INSERT INTO BENCHMARK (ID) VALUES {value_string}"

    print(f'filling table with {n_entry:.0f} random values of {n_distinct:.0f} distinct numbers')
    cursor.execute(mySql_insert_query)
    conn.commit()

    # benchmark distinct call
    start = time.time()
    sql_query = 'SELECT DISTINCT ID from BENCHMARK'
    cursor.execute(sql_query)
    result = cursor.fetchall()
    print(f'Time to read {len(result)} distinct values: {time.time()-start:.2f}')

conn.close()

提取的基准时间显示出一种违反直觉的行为，其中表中的不同值越少，时间就会突然增加:



如果我们在不使用 DISTINCT 的情况下进行查询，则时间下降到 170 毫秒，与不同条目的数量无关。
我们无法理解这种依赖性(除了一些“硬件限制”，但 100000 个条目应该......可以忽略不计？)，因此我们请您了解这种行为的根本原因可能是什么。

我们用于数据库的机器具有以下规范:

CPU:Intel i5 @ 3.3GHz(CPU 负载在执行期间达到 30%)

Ram:8 GB(mysqld占用约2.4GB，查询执行时不上升，InnoDB Buffer使用率保持在42%，buffer_size = 4GB)

硬盘:500GB，约 90% 空

操作系统、Mysql:Windows 10、Mysql Server 8.0.18

谢谢阅读!

Answer 1

在 id 上有与没有索引可能会产生巨大的差异。

在某些时候，MySQL 换档——有多种方法可以做 GROUP BY或 DISTINCT询问:

在内存中有一个散列并计算每个散列的数量。

写入临时表，对其进行排序，然后通过它计算有多少不同的值

如果有可用的索引，则从一个值跳到下一个值。

优化器不一定能预测给定情况的最佳方式，因此有时它可能无法选择最佳情况。在旧的 5.5 版本(将近十年前)中可能无法深入了解优化器选择做什么。较新的版本有 EXPLAIN FORMAT=JSON和“优化器跟踪”。

另一个问题是 I/O。从磁盘读取数据会使查询速度减慢十倍。然而，这似乎不是问题，因为 table 很小。并且您似乎在构建表后立即运行查询；也就是说，该表可能完全缓存在 RAM(buffer_pool)中。

我希望这会在评论中添加一些细节，说明基准测试很困难。