Mysql分区对索引的影响

Question

我有多个用于业务数据的大表，最小的一个有 3800 万行(24G 数据，26G 索引大小)。我设置了索引来加快查找速度，并将缓冲池设置为总 RAM(116G) 的 80%。即使在这些设置之后，随着时间的推移，我们也开始观察到性能问题。我对磁盘大小(1T)有限制，并且目前无法选择分片。数据增长已增至每天 50 万行。这导致频繁的优化和主开关练习。表模式和索引已经被优化。因此，我开始考虑对表进行分区以提高性能。我的主要分区用例是通过删除分区每月删除数据，这样就不需要优化并改善读/写延迟。以下是其中一个大表的结构(由于法律原因列名已更改 - 假设定义索引的列具有查找用例):

   CREATE TABLE `table_name` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `data_1` int(11) NOT NULL,
     `data_2` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
     `data_3` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
     `data_4` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
     `created_at` datetime DEFAULT NULL,
     `updated_at` datetime DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `index_data1` (`data_1`),
     KEY `index_data2` (`data_2`)
   ) ENGINE=InnoDB AUTO_INCREMENT=100572 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我计划在created_at列上进行分区。然而，问题是分区列必须是所有唯一键的一部分。我可以将created_at列添加到主键，但这会导致索引大小增加，这反过来又会产生副作用。有没有一些解决方法或更好的解决方案？

除了解决这个问题之外，还有一些问题在现有的任何文档或文章中都找不到答案。1.为什么mysql保证分区列是唯一键的一部分？2. 来自 ORM 的查询不存在 create_at 子句，这意味着我们无法对读取进行修剪，但只要插入始终被修剪，我们就可以接受。然而，看起来情况并非如此。为什么mysql要打开所有分区进行插入？

Mysql 版本 - 5.6.33-79.0-log Percona Server (GPL)，版本 79.0，修订版 2084bdb

Answer 1

PRIMARY KEY(id,created_at) 只比 PRIMARY KEY(id) 多占用一点点空间。我估计你的数据远低于 1%。我无法告诉我们索引空间——你能向我们展示非主索引吗？

说明:数据的叶子节点(这是一个由PK组织的BTree)，大小不会改变。非叶节点将 created_at 添加到每个“行”。根据 InnoDB 中的经验法则，非叶节点大约占用 BTree 空间的 1%。

对于 INDEX BTree，叶节点需要额外的 4 个字节/行用于 created_at 除非 created_at已在索引中。

假设您当前有 INDEX(foo)，其中 foo 是 INT，id 也是 INT。总共 8 个字节(加上开销)。添加 created_at(4 字节 TIMESTAMP)会将每个叶“行”扩展到 12+ 开销。因此，该索引的大小可能会增加一倍。

猜测:您的 24G+26G 可能会增长到 25G+33G。

听起来你有几个索引。您是否明白，如果您还有 INDEX(a,b)，则 INDEX(a) 就没用了？在某些情况下，INDEX(x,y) 比 INDEX(x), INDEX(y) 好很多？让我们讨论一下您的索引。

PARTITIONing 的主要好处是您的用例 - DROP PARTITION 比 DELETE 快得多。我的blog就这样。

不要被分区所迷惑。您希望“读/写延迟得到改善”；这种情况不太可能发生。如果您想进一步解释，请提供您认为可能发生的SELECT。

您将划分多少个“月”？我建议不要超过 50 个。当存在大量分区时，PARTITIONing 会降低效率。

由于分区键需要位于UNIQUE键中，因此唯一性约束几乎完全没有用。将其放在 AUTO_INCRMENT id 的末尾不是问题。

考虑除 id 之外的其他内容是否可以作为 PK。

问题 1:当 INSERTing 一行时，所有 UNIQUE 键都会立即检查“dup key”。如果分区键不是唯一键的一部分，这将意味着探测每个分区。这成本太高，难以想象；所以它没有完成。 (将来，可能会实现“全局到表”UNIQUE 键。8.0 版对此有一些 Hook 。 )

问题 2a:是的，如果SELECT 的 WHERE 没有充分指定分区键，所有 分区都将被打开并查看。这是最小化分区数量的另一个原因。嗯...如果您在当月 31 日执行 SELECT 操作，并在第二天执行相同的 SELECT 操作，您可能会得到更少的行(即使没有任何删除，只是DROP PARTITION)；这似乎是“错误的”。

问题2b:“为什么mysql打开所有分区进行插入？” ——是什么让你认为它确实如此？有一种奇怪的情况，“第一个”分区“不必要”打开——分区键是DATETIME。