mysql - 如何索引和查询具有 60M 行和 50 列的超大型数据库

Question

我有一个包含 60M 行和 50 列的大表(列包括“company_idx”和“timestamp”)。因此，当我执行简单的 SQL 查询时，例如:

SELECT * FROM companies_Scores.Scores 
WHERE `company_idx`=11 
  AND `timestamp` BETWEEN  '"+start_date+" 00:00:00' AND '"+end_date+" 00:00:00'

运行基本上需要 4 分钟(这太长了)。因此，我考虑为我的表建立索引，所以我这样做了:

CREATE INDEX idx_time ON companies_Scores.Scores(company_idx, timestamp) USING BTREE;

但是，当我现在执行以下操作时，运行时间也需要 4 分钟。

SELECT * FROM companies_Scores.Scores 
USE INDEX(idx_time) 
WHERE `company_idx`=11 
  AND `timestamp` BETWEEN  '"+start_date+" 00:00:00' AND '"+end_date+" 00:00:00'

我确实是 SQL 和索引的初学者。所以我不太确定如何在查询中使用索引。我想我上面做的那件事是正确的吗？为什么需要这么多时间？我该如何改进它？我希望对每个 company_idx 的查询尽可能快。

当我运行 EXPLAIN 时，我得到:

[{'Cardinality': 115751,
  'Collation': 'A',
  'Column_name': 'company_idx',
  'Comment': '',
  'Index_comment': '',
  'Index_type': 'BTREE',
  'Key_name': 'idx_time',
  'Non_unique': 1,
  'Null': 'YES',
  'Packed': None,
  'Seq_in_index': 1,
  'Sub_part': None,
  'Table': 'Scores'},
 {'Cardinality': 45831976,
  'Collation': 'A',
  'Column_name': 'timestamp',
  'Comment': '',
  'Index_comment': '',
  'Index_type': 'BTREE',
  'Key_name': 'idx_time',
  'Non_unique': 1,
  'Null': 'YES',
  'Packed': None,
  'Seq_in_index': 2,
  'Sub_part': None,
  'Table': 'Scores'}]

Answer 1

您的索引对于查询来说看起来是正确的。您正在强制使用索引，因此我们可以假设正在使用索引(如果可能)。

一个问题可能是索引无法使用。如果列存在类型问题，就会发生这种情况。例如，比较值11是一个数字。如果 customer_idx 是一个字符串，那么就有问题了。比较应该是一个字符串 - '11'。

另一个问题是可能存在大量数据。即使只有百分之几的行符合条件，索引也无济于事。索引的主要用途之一是“大海捞针”查询。他们帮助找到一小部分。如果您需要全部或大部分干草堆，它们就没有帮助。