mysql 单表分区数量_MySQL性能：单表和分区上的多个表与索引

创建20,000个表是一个坏主意。你将需要40,000张桌子，然后再多一点。

我在我的书SQL Antipatterns中称这个综合症Metadata Tribbles。你会发现每次打算创建一个“每X表”或“每X列”时。

当您有成千上万的桌面时，这确实会导致真正的性能问题。每个表需要MySQL来维护内部数据结构，文件描述符，数据字典等。

还有实际的行动后果。您是否真的想创建一个系统，要求您每次新用户注册时创建一个新的表？

以下是分区表的示例：

CREATE TABLE statistics (

id INT AUTO_INCREMENT NOT NULL,

user_id INT NOT NULL,

PRIMARY KEY (id, user_id)

) PARTITION BY HASH(user_id) PARTITIONS 101;

这样，您可以定义一个逻辑表，同时在查询分区键的特定值时将表分为多个物理表，以便更快的访问。

例如，当您运行像您的示例的查询时，MySQL仅访问包含特定user_id的正确分区：

mysql> EXPLAIN PARTITIONS SELECT * FROM statistics WHERE user_id = 1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: statistics

partitions: p1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 8

ref: NULL

rows: 2

Extra: Using where; Using index

HASH分割方法意味着行按整数分区键的模数放置在分区中。这意味着许多user_id映射到相同的分区，但是每个分区平均只有平均值的N / N行数(N是分区数)。并且您定义了具有不断数量的分区的表，因此您每次获得新用户时都不必扩展它。

您可以选择任意数量的分区，最多1024个(在MySQL 5.6中为8192)，但有些人报告出现性能问题。

建议使用素数分区。如果您的user_id值遵循模式(如仅使用偶数)，则使用素数分区有助于更均匀地分布数据。

在评论中回复你的问题：

How could I determine a resonable number of partitions?

对于HASH分区，如果您使用101分区，如上例所示，则任何给定的分区平均约有1％的行。你说你的统计表有3000万行，所以如果你使用这个分区，你每个分区只有300k行。这对MySQL来说更容易阅读。您也可以(并应该)使用索引，每个分区都有自己的索引，它只有整个未分区表的索引的大小只有1％。

那么你如何确定一个合理数量的分区的答案是：你的整个表有多大，你想要平均多大的分区？

Shouldn’t the amount of partitions grow over time? If so: How can I automate that?

如果使用HASH分区，分区的数量不一定需要增长。最终，您可能总共有300亿行，但是我发现，当您的数据量增长数量级时，无论如何，这都需要一个新的架构。如果您的数据变大，您可能需要在多个服务器上进行分片，以及分割成多个表。

也就是说，您可以使用ALTER TABLE重新分区表：

ALTER TABLE statistics PARTITION BY HASH(user_id) PARTITIONS 401;

这必须重组表(像大多数ALTER TABLE更改)，所以期望它需要一段时间。

您可能需要监视分区中数据和索引的大小：

SELECT table_schema, table_name, table_rows, data_length, index_length

FROM INFORMATION_SCHEMA.PARTITIONS

WHERE partition_method IS NOT NULL;

与任何表格一样，您希望活动索引的总大小适合您的缓冲池，因为如果MySQL在SELECT查询期间必须交换部分索引进出缓冲池，则性能将受到影响。