我目前正在探索PARTITION, 对于我拥有的特定用例的使用。
我使用 InnoDB，每个表的文件。玛丽亚数据库 10.8。
我在 MySQL网页上阅读了 Rick 的PARTITION Maintenance 。
我想强调这一点：
WHERE X = 1234-- 这让“分区修剪”只在那个分区中查看。但这并不比INDEX(x)在非分区表上好。而且您可能无论如何都需要该索引；在第一次“修剪”到所需的分区后，您仍然需要索引。没有更快。
一个常见的谬误：“分区将使我的查询运行得更快”。它不会。思考“点查询”需要什么。没有分区，但有适当的索引，有一个 BTree（索引）可以向下钻取以找到所需的行。对于十亿行，这可能有 5 层深。对于分区，首先选择并“打开”分区，然后向下钻取较小的 BTree（比如 4 层）。嗯，较浅的 BTree 的节省被必须打开分区消耗掉了。类似地，如果您查看需要触及的磁盘块，以及其中哪些可能被缓存，您会得出结论，即可能有大约相同数量的磁盘命中。由于磁盘命中是查询的主要成本，因此分区不会获得任何性能（至少对于这种典型情况）。
我完全明白这意味着什么，但我有一个问题：
在 MySQL/MariaDB 中，Indexes 的性能会随着越来越大而下降吗？
对于 10 亿行或 1000 亿行，就性能而言，好的索引是否总是比分区更好？
--
还有一点最接近我想要受益的：
用例#3——热点。这个解释起来有点复杂。鉴于这种组合：
⚈ 一个表的索引太大而无法缓存，但一个分区的索引是可缓存的，并且
⚈ 索引是随机访问的，并且
⚈ 由于更新索引，数据摄取通常是 I/O 绑定
分区可以在 RAM 中保持所有索引“热”，从而避免大量 I/O。案例 #3 的重大胜利：改进缓存以减少 I/O 以加速操作。
“索引缓存”对 InnoDB 也有效吗？我的理解CACHE INDEX只适用于MyISAM。
或者这是否与它在 InnoDB 缓冲池中有关？
关于减少 I/O，这是否适用于 NVMe 服务器？我%iowait的是 0.00，而我的应用程序是写密集型的。
关于这一点我可以说几件事。
我们不能做出这种概括，因为它取决于查询。总的来说，每一种优化都是对正确类型查询的极大帮助，而牺牲了其他类型的查询。因此，在选择优化方法之前，您必须非常具体地了解要优化的查询。
这不是一个非此即彼的选择。您可以对表进行分区，还可以定义索引，以便在给定分区中优化搜索。
我不认为你有 1000 亿行。如果你这样做了，你就不会在 Stack Exchange 上问这个问题，你会分配给你的全职数据库架构师团队优化它的任务。他们无疑会带着使用许多服务器的设计回来。在单个表中存储 1000 亿行是不切实际的。你会如何备份它？你会如何添加一列？
InnoDB 使用 B 树索引（也是全文索引和空间索引，但对于本次讨论，我们假设索引的默认类型）。
B 树索引的插入和搜索复杂度均为O(log 2 n )，其中n是数据结构中的条目数。因此，随着索引变大，插入或搜索的开销会越来越大。
索引搜索所需的 I/O 是B 树深度的函数。即要遍历多少层非终端节点才能到达叶节点。深度取决于有多少索引条目，还取决于给定条目的数据类型有多大，因为 InnoDB 页面大小是固定的，所以一个页面只能容纳这么多索引节点。请参阅： https ://www.percona.com/blog/2009/04/28/the_depth_of_a_b_tree/
通过将索引页的子集保存在 InnoDB 缓冲池中的 RAM 中，可以减轻 I/O 成本。但是如果索引的增长比 RAM 大得多，则没有足够的缓冲池来容纳整个索引，因此如果您在整个索引上随机搜索，InnoDB 可能会驱逐您很快将再次需要的页面。这些页面将在您需要时从存储中重新加载，但这可能会导致额外的开销，因为页面会在 RAM 中换入和换出。
缓存索引只适用于 MyISAM。InnoDB 按需缓存页面，其中可能包含给定索引的子集。忘记任何将索引加载到缓存中的手动命令。老实说，我建议出于任何目的忘记 MyISAM。自 2000 年代以来，我还没有看到它被适当地使用过。
您询问了 NVMe 存储。NVMe 当然比旧的 SATA 接口更快，但它与 RAM 相比如何呢？这取决于您测量的内容，但对于访问时间和吞吐量（MB/秒），您可以指望 RAM 比最新一代 NVMe 快几倍。InnoDB 代码也被编写为假设页面必须在 RAM 中才能被读取。将数据和索引页缓存在 RAM 中仍然是一个胜利。
我同意 Rick 的一般性说法，即分区通常不会像您认为的那样对性能有很大帮助。它在正确的场景中很有用，但它并不是一个神奇的“一切都很快”的解决方案。所有其他类型的优化也是如此！
（除了比尔的评论......）
另一种分析性能的方法——“计算磁盘命中率”。
（我在最后两个陈述中做了很多“挥手”。）
我希望我已经给了你一些线索，让你自己判断你的桌子是否会随着它的增长而受到影响。如果您想进一步讨论，请提供
CREATE TABLE. 当有人谈论 bilion-row 表时，我喜欢缩小数据类型、重构架构、规范化、添加汇总表、考虑分片等。但我很少推荐分区。有时，我建议“保留汇总表，但扔掉事实表”。这消除了各种缩放和性能问题。这取决于你所说的“性能”是什么意思......
如果您的意思是“根据索引键查找一行或一系列行”，那么答案将是“一点点”。正如其他人所解释的那样，只要索引的工作集保留在缓存中，“大”可能会比“小”慢一点，但这很可能被物流、网络使用的其余查询时间所淹没，解析等。如果没有缓存叶子页面，那将添加一个随机 IO，因此您必须询问您的 IO 系统这需要多长时间。
但是您询问索引与分区，所以在这种情况下，如果数据在“分区”情况和“单表”情况下相同，则分区上索引的总大小将与索引几乎相同单人桌上。对于相同的查询负载，没有理由比另一个缓存得更好，因此两者之间可能没有区别。如果您只访问最新的行，那么这两种情况都将以同样的方式受益，因为只需要缓存索引的相应部分。
但是，如果您从全局的角度看待性能并添加诸如“删除所有早于 12 个月的行”之类的内容，并且在运行此归档操作时您有十亿行要删除，那么巨大的表和巨大的索引将成为一个极其糟糕的主意（TM ). 如果它是一个日期索引，也许它是可以容忍的，因为删除会命中它的一个连续块。但是，如果它是一个相当随机的列上的索引，那么每个删除的行都会触发索引中某个地方的随机写入，到处都是，这将永远存在。
然而，如果您使用分区，“DROP PARTITION”几乎是瞬间的，因为在幕后，它只是删除相应的文件。除非有要触发的 ON DELETE 触发器，否则如果数据库知道我们要删除整个分区，那么即使读取要删除的行也毫无意义。
如果没有对旧分区的写入，这可能会使备份更快，如果备份工具可以利用不需要备份未更改的分区这一事实。
当分区可以使您的查询更快时，我正在编辑以添加其他情况：
您没有足够的钱将整个表放在 SSD 上，因此您将很少访问的旧分区（和索引）放在笨重的慢速旋转 RAID 上，而最近的分区（和索引）可以看到大部分内容对某些快速 SSD 的操作。这是一个很好的“现金与性能”优化，但您必须偶尔移动分区。也许你甚至可以只复制最近的分区，或者在每个服务器上放置一个分区并并行运行它们，如果数据库支持的话，诸如此类。
此外，如果查询优化器搞砸了并决定进行全表扫描或类似的事情，也许如果您的表已分区并且查询对分区键有条件，则搞砸的大小可以限制为仅几个分区而不是整个表。