约束具有很多字段的唯一键会损害MySQL性能吗? [英] Would constraint unique key with lot of fields hurt MySQL performance?

问题描述

我们计划在生产环境中创建一个如下所示的表，但要考虑到如果我们在MySQL中使用很多字段来创建约束键，则存储和查询/插入/更新性能将会如何: >

We are planning to create an table like below in production environment but concerning about what would happen to storage and query/insert/update performance if we create a constraint key with quite a lot of fields in MySQL like this:

create table table_name
(
    f1  int null,
    f2  int null,
    f3  int null,
    f4  int null,
    f5  int null,
    f6  int null,
    f7  int null,
    f8  int null,
    f9  int null,
    f10 int null,
    f11 int null,
    f12 int null,
    f13 int null,
    f14 int null,
    f15 int null,
    f16 int null,
    f17 int null,
    f18 int null,
    f19 int null,
    f20 int null,

    v1 int null,
    v2 int null,
    v3 int null,

    constraint update_key unique (f1, f2, f3, f4, f5, f6, f7, f8, f9, f10,
                                  f11, f12, f13, f14, f15, f16, f17, f18, f19, f20);

并使用SQL插入行，如下所示:

And insert rows with SQL like this:

insert into table_name (f1,....f20, v1, v2, v3)
        values (values ...)
        on duplicate key update
              v1=v1+values(v1),
              v2=v2+values(v2),
              v3=v3+values(v3);

有人可以给我们一些建议吗?

Could anyone give us some advice?

推荐答案

表中存在索引会增加INSERT/UPDATE/DELETE语句的开销.

The presence of an index in your table adds some overhead to your INSERT/UPDATE/DELETE statements.

20列的索引并不比1列的索引差.或者至少，性能差异很小，您不应该因此而更改设计.

A 20-column index isn't worse than a 1-column index. Or at least, the difference in performance is small enough that you shouldn't change your design because of it.

这是一个比喻:您的母亲要您去商店拿杂货.您必须穿上鞋子，乘公共汽车去商店，收集杂货，排队买票，坐公共汽车回家，然后将杂货放入冰箱.她要1件还是2件或20件重要吗?的确，提包要重一些，但是旅行所需的时间没有改变.

Here's an analogy: Your mother asks you to go to the store to pick up groceries. You have to put on your shoes, take the bus to the store, collect groceries, stand in line to pay for them, take the bus home, and put the groceries in the fridge. Does it matter a lot if she asks for 1 item or 2 or 20? It's true that it's a bit heavier to carry the bag, but the time taken for the trip doesn't change.

这篇关于约束具有很多字段的唯一键会损害MySQL性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！