mysql - 为动态选择在多列上建立索引的最佳方法

Question

所以基本上我有一个包含大约 7 亿行的表，并且每天不断更新大约 20 万到 30 万行，每个月末，我将清除超过 3 个月的数据。

CREATE TABLE TESTRECORD (
  TIMEADDED timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  SERIAL varchar(8) NOT NULL,
  ENDTIME varchar(14) NOT NULL,
  MODEL varchar(2) NOT NULL,
  PROCESS int(4) NOT NULL,
  PF varchar(4) NOT NULL,
  COMID varchar(6) NOT NULL,
  COMTP varchar(3) NOT NULL,
  TRIAL varchar(4) NOT NULL,
  TEST varchar(8) NOT NULL,
  SECTION int(2) NOT NULL,
  DATA_0 float NOT NULL,
  DATA_1 float NOT NULL,
  DATA_2 float NOT NULL,
  DATA_3 float NOT NULL,
  DATA_4 float NOT NULL,
  DATA_5 float NOT NULL,
  PRIMARY KEY (SN,ENDTIME,SECTION),
  UNIQUE KEY BASESN (SN,ENDTIME,MODEL,PROCESS,PF,COMID,TRIAL,TEST,SECTION),
  KEY COMID (COMID),
  KEY TRIAL (TRIAL),
  KEY PF (PF),
  KEY TEST (TEST)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

唯一键定义了将在 select 语句中使用的参数。由于这个表的基本功能是动态数据分析，所以没有具体的顺序where子句中会出现什么以及使用多少个，可以有一些随机分组由一两个cols也来自唯一的 key 。因此，几乎不可能索引所有可能的组合以确保对任何给定选择的快速操作。

根据我的理解，mysql 根据它们在模式中列出的顺序使用索引，所以在我的例子中，如果我在 select 语句中使用 SN、ENDTIME 和 PF，那么只有前 2 列将使用唯一键。是否有任何有效的方法可以将索引分解为每列 1 个索引或查询技术来加快速度或至少在 where 子句中的不同列组合上实现大致相同的性能？

非常感谢你~!!!

Answer 1

MySQL 中的索引就像您可能在书末找到的索引一样工作。如果您正在查找“意大利辣香肠比萨饼”的食谱，您首先会查找意大利辣香肠，然后是比萨饼。如果您只是在寻找“比萨饼”，那么该索引对您没有帮助，因为比萨饼在索引中仅次于意大利辣香肠——如果您先查找意大利辣香肠，您只能找到比萨饼。这就是 X、Y 列索引的工作方式。如果您计划按该顺序在列 X 和 Y 上运行查询，那么这两列上的索引一起是有意义的。如果您想在 X 上运行查询并在 Y 上运行查询，那么复合索引没有多大用处!

我建议您坐下来定义最常运行的查询类型，并分析您的存储和处理能力。索引会占用大量空间，尤其是在处理数百万行时。索引是存储空间和处理能力之间的经典权衡，没有人不熟悉您的数据库可以告诉您什么是适合您的特定情况的最佳索引数量或配置。

另请查看每列中存储的唯一值的数量。与 Oracle 不同，MySQL 不支持标准表的位图样式索引(它使用 B-Tree)。撇开技术细节不谈，这意味着在具有相对较少唯一值的列上构建索引不会为您提供每单位索引空间的值(value)，就像您想象的那样。

最后要注意的是，对于某些类型的数据分析，您可能需要考虑将部分数据导出到 MEMORY 表中。 MEMORY 表基本上是临时表，可以跨用户 session 保存它们的结构。当您使用完它们或发生崩溃时，它们会丢失数据，但不会丢失它们的结构。内存表支持散列索引，散列索引列的值以加速数据检索。它们在大多数情况下都非常快，并且在正确使用时可以显着提高性能。

如果您真的对数据库优化感兴趣，我建议您看看《高性能 MySQL》这本书。