MySQL 分区 (innoDB) - 大表

Question

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

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

两列都是 VARCHAR(50)。

实际上，它没有 KEY sat，但“name”将是唯一的，所以我想我将其更改为“name”PRIMARY_KEY。 (我之前应该这样做。我想现在我需要在添加 primary_key 选项之前执行删除重复查询)

我的问题是，在对表执行简单查询时，它确实需要很长时间。

SELECT characteristics WHERE name=blabla LIMIT 1;//需要时间。

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

问题来了:

• 解决我的性能问题是个好主意吗？
• 我怎样才能做到这一点？

• 我的 ALTER TABLE 将“名称”列设置为 PRIMARY_KEY 的想法也是一个好主意吗？

• 还有关于重复查询的问题，我在这里发现了这个问题，我这样做是否正确？ (不想弄乱我的 table ...)

delete a
from products a
left join(
select max(name) maxname, caracteristics
from products
group by caracteristics) b
on a.name = maxname and
a.caracteristics= b.caracteristics
where b.maxname IS NULL;

Answer 1

你也可以直接设置一个PRIMARY KEY和ignore选项，像这样:

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]>

测试添加主键/插入忽略

这是添加主键和插入忽略之间的测试。并且您可以看到在此示例中添加主键(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]>