添加唯一约束会减慢速度吗？

Question

我的表中有三列。

+-----------+-----------------------+------+-----+---------+-------+
| Field     | Type                  | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+-------+
| hash      | mediumint(8) unsigned | NO   | PRI | 0       |       | 
| nums      | int(10) unsigned      | NO   | PRI | 0       |       | 
| acc       | smallint(5) unsigned  | NO   | PRI | 0       |       | 
+-----------+-----------------------+------+-----+---------+-------+

我预计数据中有重复项，因此我继续添加了一个唯一约束：

ALTER TABLE nt_accs ADD UNIQUE(hash,nums,acc);

我有大约 5 亿行要插入到该表中，并且该表已使用 nums 上的 RANGE 划分为大约 20 个分区。

1. 唯一约束会减慢插入速度吗？这与仅将两者设为主键而不是施加唯一约束有何不同？
2. 我有很多使用 hash 和 nums 列的 GROUP BY 类型查询。我是否继续添加转换索引 和/或只需添加单独的索引？

编辑：

在分区和插入一些测试数据后解释计划

1. mysql> explain partitions select * from nt_accs;
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index | 
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)



2. mysql> explain partitions select * from nt_accs WHERE nums=1504887570;
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | nt_accs   | p7         | index | NULL          | hash     | 7       | NULL |   10 | Using where; Using index | 
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

3. mysql> explain partitions select * from nt_accs WHERE hash=2347200;
+----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table     | partitions                                                                | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | nt_accs  | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | ref  | hash          | hash     | 3       | const |   27 | Using index | 
+----+-------------+-----------+---------------------------------------------------------------------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

4. mysql> EXPLAIN PARTITIONS SELECT hash, count(distinct nums) FROM nt_accs GROUP BY hash;
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index | 
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

5. mysql> EXPLAIN PARTITIONS SELECT nums, count(distinct hash) FROM nt_accs GROUP BY nums;
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table     | partitions                                                                | type  | possible_keys | key      | key_len | ref  | rows | Extra                       |
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | nt_accs   | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20 | index | NULL          | hash     | 7       | NULL |   10 | Using index; Using filesort | 
+----+-------------+-----------+---------------------------------------------------------------------------+-------+---------------+----------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

我对第一个和第二个查询非常满意，但我不确定第三个、第四个和第五个查询的性能。此时我还能做些什么来优化这个吗？

Answer 1

唯一约束是否会减慢插入速度？这与仅将两者设为主键而不是施加唯一约束有何不同？

是的，索引（MySQL 将唯一约束实现为索引）会减慢插入速度。
主键也是如此，这就是为什么需要高插入负载的表（即：用于日志记录）没有定义主键，以使插入速度更快。

我有很多使用 hash 和 nums 列的 GROUP BY 类型查询。我是否继续添加一个转换索引，或者只是添加单个索引？

唯一确定的方法是测试和测试。检查解释计划。

更新

根据提供的解释计划，我不认为对第三个和第二个的担忧。第四个版本。 MySQL 每种 select_type 只能使用一个索引。第五个版本可能受益于覆盖索引。

附录

只是想确保您知道：

ALTER TABLE nt_accs ADD UNIQUE(hash, nums, acc);

...意味着三列值的组合将是唯一的。 IE：这些是有效的，唯一约束将允许：

hash  nums  acc
----------------
1     1     1
1     1     2
1     2     1
2     1     1