mysql分区和自动移动行

我有一个表，表中有约6m行，通过索引优化，每个查询提取约20000-30000行。但是，由于很多人连续提取这些行（每30秒左右一次），站点经常会超时。
我最近将数据库迁移到一个3服务器的mysql集群，该集群有大量的ram（每台服务器512gb），性能并没有太大的提高。
我想知道分区是否是提高性能的最佳方法。因为我完全没有分居的经验，所以我想在这里问一下。
我的问题是，所有这些行都有一个值为0、1、2或3的列。
有没有可能把值为1的所有行放在一个分区的某一列中，而把值为2的所有行放在另一个分区的某一列中？它们会根据主表中更新的值自动移动吗？最重要的是，它能帮助提高性能吗？因为它只需要在20000-30000行中查找一行，而不是6000000行

是的，mysql支持分区。您可以很好地定义分区，例如：

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  somestuff INT,
  otherstuff VARCHAR(100),
  KEY (somestuff)
) PARTITION BY HASH(id) PARTITIONS 4;

INSERT INTO MyTable () VALUES (), (), (), ();

在此之后，您可以验证每个分区中有多少行：

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='MyTable';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          1 |
| p2             |          1 |
| p3             |          1 |
+----------------+------------+

然而，当人们尝试在mysql中使用分区时，有两件事会让他们大吃一惊：
首先，作为https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html 说：
表上的每个唯一键都必须使用表分区表达式中的每一列。
这意味着如果你想用 somestuff 在上面的例子中，你不能。如果主键包含分区表达式中名为的列，则该要求将失败。

ALTER TABLE MyTable PARTITION BY HASH(somestuff) PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

您可以通过从表中删除任何主键或唯一键约束来解决这个问题，但这会给您留下一种格式不正确的表。
其次，只有在可以利用分区修剪的情况下，分区才能加快查询速度，而且只有在查询条件中包含分区表达式中使用的列时，才会发生这种情况。

mysql> EXPLAIN PARTITIONS SELECT * FROM MyTable WHERE SomeStuff = 3;
+----+-------------+---------+-------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table   | partitions  | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+---------+-------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | MyTable | p0,p1,p2,p3 | ref  | somestuff     | somestuff | 5       | const |    4 | NULL  |
+----+-------------+---------+-------------+------+---------------+-----------+---------+-------+------+-------+

注意，这表示它需要扫描分区p0、p1、p2、p3，即整个表。没有分区修剪，因此没有性能改进，因为它没有减少检查的行数。
如果在分区表达式中使用的列中搜索特定值，可以看到mysql能够减少它扫描的分区数：

mysql> EXPLAIN PARTITIONS SELECT * FROM MyTable WHERE id = 3;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | MyTable | p3         | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+

分区在非常特殊的情况下会有很大帮助，但是分区并不像大多数人认为的那样通用。
在大多数情况下，最好在表中定义更具体的索引，以支持需要运行的查询。

mysql分区和自动移动行

1条答案

相关问题

热门标签

最新问答