MySQL分区(innoDB) -大表

Question

我有一个MySQL非常大的数据库(10亿行)，如下所示：

数据库:产品(“名称”、“特征”)

这两列都是VARCHAR(50)。

实际上，它没有键sat，但“名称”将是唯一的，所以我想我会把它改为“名称”PRIMARY_KEY。(我早该这么做的.现在我需要在添加primary_key选项之前执行一个删除重复查询(我猜)

我的问题是，当在表上执行一个简单的查询时，需要花费很长时间。

SELECT caracteristics WHERE name=blabla LIMIT 1; //需要年龄。

我在考虑对现有的表进行分区。

以下是问题所在：

• 修复我的性能问题是个好主意吗？
• 我怎样才能做到这一点？
• 我把ALTER设置为“name”列为PRIMARY_KEY的想法也是个好主意吗？
• 另外，关于重复查询，我在这里发现了这个，我做得正确吗？(不想弄乱我的桌子.)

从产品a中删除a左联接(选择max(name) maxname，从产品组中选择caracteristics) b上的a.name = maxname和a.caracteristics= b.caracteristics，其中b.maxname为空；

Answer 1

还可以使用忽略选项直接设置主键，如下所示：

ALTER IGNORE TABLE `products` ADD PRIMARY KEY(name);

这将从名称中删除所有副本。

样本

MariaDB [l]> CREATE TABLE `products` (
    ->   `name` varchar(50) NOT NULL DEFAULT '',
    ->   `caracteristics` varchar(50) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [l]> INSERT INTO `products` (`name`, `caracteristics`)
    -> VALUES
    ->     ('val1', 'asdfasdfasdf'),
    ->     ('val2', 'asdasDasd'),
    ->     ('val3', 'aesfawfa'),
    ->     ('val1', '99999999');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [l]> select * from products;
+------+----------------+
| name | caracteristics |
+------+----------------+
| val1 | asdfasdfasdf   |
| val2 | asdasDasd      |
| val3 | aesfawfa       |
| val1 | 99999999       |
+------+----------------+
4 rows in set (0.00 sec)

MariaDB [l]> ALTER IGNORE TABLE `products` ADD PRIMARY KEY(name);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 1  Warnings: 0

MariaDB [l]> select * from products;
+------+----------------+
| name | caracteristics |
+------+----------------+
| val1 | asdfasdfasdf   |
| val2 | asdasDasd      |
| val3 | aesfawfa       |
+------+----------------+
3 rows in set (0.00 sec)

MariaDB [l]>

测试添加主键/插入忽略

下面是添加主键和插入忽略键之间的测试。在这个示例中，您可以看到add主键(90秒/ 120秒)稍微快了一点

MariaDB [l]> CREATE TABLE `bigtable10m` (
    ->   `id` varchar(32) NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [l]>
MariaDB [l]> INSERT INTO `bigtable10m`
    -> select lpad(seq,8,'0') from seq_1_to_10000000;
Query OK, 10000000 rows affected (24.24 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]>
MariaDB [l]> SELECT * FROM `bigtable10m` LIMIT 10;
+----------+
| id       |
+----------+
| 00000001 |
| 00000002 |
| 00000003 |
| 00000004 |
| 00000005 |
| 00000006 |
| 00000007 |
| 00000008 |
| 00000009 |
| 00000010 |
+----------+
10 rows in set (0.00 sec)

MariaDB [l]>
MariaDB [l]> CREATE TABLE `bigtable30m` (
    ->   `id` varchar(32) NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [l]>
MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (28.49 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (29.01 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (32.98 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]>
MariaDB [l]> ALTER IGNORE TABLE `bigtable30m` ADD PRIMARY KEY(id);
Query OK, 30000000 rows affected (1 min 32.34 sec)
Records: 30000000  Duplicates: 20000000  Warnings: 0

MariaDB [l]>
MariaDB [l]> DROP TABLE `bigtable30m`;
Query OK, 0 rows affected (0.52 sec)

MariaDB [l]>
MariaDB [l]> CREATE TABLE `bigtable30m` (
    ->   `id` varchar(32) NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

MariaDB [l]>
MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (37.29 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (41.87 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]> INSERT INTO `bigtable30m` SELECT * FROM `bigtable10m`;
Query OK, 10000000 rows affected (30.87 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

MariaDB [l]>
MariaDB [l]> CREATE TABLE bigtable_unique (
    ->   `id` varchar(32) NOT NULL DEFAULT '',
    ->  PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

MariaDB [l]>
MariaDB [l]> INSERT IGNORE bigtable_unique SELECT * FROM `bigtable30m`;
Query OK, 10000000 rows affected, 65535 warnings (1 min 57.99 sec)
Records: 30000000  Duplicates: 20000000  Warnings: 20000000

MariaDB [l]>

Answer 2

我认为聚会不是你解决这个问题的方式。你会怎么划分？根据什么标准？

我认为您的主要关注点是架构，并且应该在任何其他事情之前进行修复:唯一的记录并不是唯一的。

由于容量法的原因，我认为任何解决方案都需要一段时间才能执行。但我敢打赌这个是最快的：

CREATE TABLE products_unique (
 name VARCHAR(50) NOT NULL,
 characteristics VARCHAR(50),
 PRIMARY KEY (name)
);

INSERT IGNORE INTO products_unique SELECT * FROM products;

RENAME TABLE products TO products_backup;
RENAME TABLE products_unique TO products;

复制将被任意显示，但我认为这是你正在寻找的无论如何。如果时间太长，你应该试着通宵运行.我只是希望事务缓冲区不会爆炸到您身上，在这种情况下，我们必须在某些存储过程上工作，以便将插入分批分离。

Answer 3

是的，修复性能问题是个好主意。这是正确的答案，总是当你有严重的性能问题-足够怀疑性能修正。

您可以通过更改table并使name成为primary key来实现这一点，正如您已经意识到的那样。

您的查询不应该是必需的。您应该创建一个临时的table，而不是insert您认为必要的值。让我们假设那个表的名称是mytemptable。然后：

insert into mytemptable(name, characteristics)
select name, characteristics
from products
where not exists (select 1
                  from mytemptable t
                  where products.name = t.name);

然后使用以下方法从products中删除记录

delete from products;

然后alter products，确保它有name作为primary key，然后

insert into products(name, characteristics)
select name, characteristics
from mytemptable;

最后，drop您的临时表。

关于您的查询：

因为您删除了记录，如果您有一个与给定的max(name)值相关联的可能的name，那么name将等于组中的所有其他characteristics，这是非常安全的假设。因此，如果您有一个可能的characteristics值匹配单个name，您将删除该name的所有实例，所以是的，您的查询将扰乱您的数据。