mysql - Mysql Innodb性能-如何最小化多列索引？

Question

下表包含1000万行，

CREATE TABLE Sample1 (
  c1 bigint(20) NOT NULL AUTO_INCREMENT,
  c2 varchar(45) NOT NULL,
  c3 tinyint(4) NOT NULL DEFAULT 0,
  c4 tinyint(4) NOT NULL DEFAULT 0,
  c5 varchar(45) DEFAULT NULL,
  time bigint(20) DEFAULT NULL,
  PRIMARY KEY (c1),
  KEY varchar_time_idx (c2,Time),
  KEY varchar_c3_time_idx (c2,c3,Time),
  KEY varchar_c4_time_idx (c2,c4,Time),
  KEY varchar_c3_c4_time_idx (c2,c3, c4,Time)
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8;

选择
创建四个多列索引以选择具有以下条件的行，其中

1）c2和时间
例如：在时限30之前，从Sample1中选择c1，c5，其中c2 =“ sometext”顺序；

2）c2和c3与时间
例如：在时限30之前，从Sample1中选择c1，c5，其中c2 ='sometext'和c3 = int顺序；

3）c2和c4与时间
例如：在时限30之前，从Sample1中选择c1，c5，其中c2 ='sometext'和c4 = int顺序；

4）c2和c3和c4与时间
例如：在时限30之前，从Sample1中选择c1，c5，其中c2 ='sometext'且c3 = int和c4 = int顺序；

为了使上面的选择更快，创建了四个多列索引。

基数明智的c2，c3和c4非常低。
（例如：在一百万个c2中，c3和c4各自具有100个唯一列）。

也分布不均。 c2中的每个组的行数不均匀。
（例如：c2 = 1包含100000，c2 = 2包含1500000，依此类推）

列时间（以毫秒为单位的时间戳）主要包含唯一字段。

选择正常发生（一小时内10到30次，但应保持高速）

插入
插入非常频繁。
但是它会依次处理（一个接一个）。

更新资料
所有更新均基于C1（主键）。 （频率：插入时的20％）
更新Sample1设置c3 = INT，c4 = INT，时间= CurrentTimeInMilliSecond其中c1 = INT

表具有5个索引字段（4个多列）。由于此
1）插入和更新索引字段变得更昂贵
2）随着表格的不断增长（可能达到1亿），索引大小也将以更快的速度增长

请在mysql中提出解决此用例的好方法。

其他必要的细节
innodb_buffer_pool_size：16106127360（15 GB）;
CPU核心：32;
内存：32GB

Answer 1

注意：TMI即将到来。我不得不做一些猜测；如果您提供更多详细信息，我会更具体...

您拥有的4个辅助键最适合您列出的4个查询。

与流行的妻子的故事相反，基数与复合索引和SELECT性能无关。

在1亿行的情况下，表（包括索引）可能为20GB。你有多少RAM？ innodb_buffer_pool_size的值是什么？除非您有一个很小的RAM，否则这些可能无关紧要。

回到“基数”。

让我们看一下INDEX(c2, Time)，其中c2有100个不同的值，而Time基本上在不断增加。每个新的INSERT会将新行置于100个位置之一-每个c2簇的末端。这意味着有100个“热点”，并且意味着有100个块（大部分）足以应付更新这一索引的需要。 100个块= buffer_pool的1.6MB-希望是很小的一部分。

同时，PRIMARY KEY是AUTO_INCREMENT，因此存在一个热点和一个块-甚至更小部分。

但是...其他三个辅助键将具有更多热点（块），因此它们可能更重要。让我们来看最糟糕的一个(c2, c3, c4, Time)。暂时，将有100 * 100 * 100个热点。但是我认为这将超出整个索引中的块数。 （因此，数学崩溃了。）所以这会很忙。

题外话……您在一次交易中INSERT多少行？每秒多少行？ innodb_flush_log_at_trx_commit（flatc）的值是什么？好吧，我们将其简化为一次完全刷新一行，而不是批量刷新很多行。

回到计算...

在一个极端情况下：小型buffer_pool和单行事务以及flatc = 1和HDD：您将需要一些IOP。希望您插入的行数不必超过20行/秒。

在另一个极端：大型缓冲池和批处理，以及flatc = 2和SSD：平均少于1个IOP。您可能可以处理每秒插入的1000行以上。

规范化c2可能会减少20GB估计值的一半，从而在计算中进行多次调整。

回到SELECTs-您是否真的为给定的c2获取10万行？如果您还有更多过滤条件，例如ORDERing，LIMITing等，请向他们展示；在此分析中可能会有很大的不同。

回到标题-我还没有看到任何有用的方式来更改/最小化这些索引。它们对于SELECTs似乎非常有用，并且对INSERTs的危害很小。

哦，UPDATEs。在考虑此处的后果之前，我们需要先查看WHERE上的UPDATEs子句。

更多（对问题进行多次更新后）

PRIMARY KEY(c1)负责使UPDATEs尽可能快（除了需要最终更新索引外）。

SELECTs非常少见；我的索引使每次运行都尽可能快

15GB的Buffer_pool表示，整个表及其所有索引都将存在于池中（一旦对其进行预热）-对于当前的10M行。在100M行时，可能仍然可以。我之所以这样说，是因为可能会引起用户流失的查询是SELECTs，但是它们都说AND Time > ...。这意味着“工作集”即表的“结尾”。如果到达十亿行，则需要重新讨论此段。

即使使用最差的设置，MySQL每天也应该能够处理一百万个INSERTs。因此，如果您不希望3个月内就能获得1亿行，那么我认为INSERTs没问题。