hnwyllmm
commented
1 month ago Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑🤝🧑👫🧑🏿🤝🧑🏻👩🏾🤝👨🏿👬🏿
Question
I read [Official Document](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000640146#%E6%99%AE%E9%80%9A%E8%A1%A8%E7% The following description exists in BB%93%E6%9E%84%E8%AE%BE%E8%AE%A1%E8%A7%84%E8%8C%83):
If there is an e-commerce SaaS, the [order table] has merchant ID (int) and order number (varchar(32)) with unique constraints. Should we also use these two fields as the joint primary key at this time?
As we all know, MySQL uses the B+Tree structure to store data. It is generally recommended that the ID be auto-incremented. This can avoid random insertion, which will lead to frequent splitting of MySQL data pages, which will lead to increased IO overhead when retrieving data.
However, the merchant ID + order number is obviously not self-increasing, so I have this question. For this scenario, if OceanBase still recommends using their union as the primary key, is it because LSM-Tree can handle the split page problem well?
In addition, in addition to the primary key index, we generally create several additional secondary indexes on key business tables. In MySQL, because the leaf node of each secondary index stores the primary key value of the corresponding row. So, if the primary key is larger, the space required for each secondary index leaf node will also be larger because they need to store larger primary key values.
If the primary key is a bigint auto-increment ID, stored on each secondary index, the associated primary key will also occupy 8 bytes of space.
If the primary key is a joint index such as int + varchar(32), does it mean that the associated primary key also takes up at least 4+32 bytes of space?
If there are multiple secondary indexes (usually 2 to 4), will they also occupy a lot of space? In this case, wouldn't the storage space and the cost of maintaining these secondary indexes also increase a lot?
Question
我看官方文档中存在如下描述：
如果有一个电商 SaaS，【订单表】有 商户ID（
int） 和 订单号（varchar(32)） 存在唯一约束，这个时候我们也应该用 这两个字段 做联合主键吗？众所周知，MySQL 采用的是 B+Tree 的结构来存储数据，一般建议 ID是自增的，这样可以避免随机插入，导致 MySQL 数据页频繁分裂，进而导致检索数据时 IO 开销增加。
然而，商户ID + 订单号 也很明显不是自增的，所以我才有此疑问。 对于这种场景，如果 OceanBase 仍然推荐使用它们联合作为主键，是因为 LSM-Tree 能够很好地处理 裂页 问题么 ？
此外，除了主键索引，我们一般还会额外在关键业务表上创建几个二级索引。 在 MySQL 中，因为每个二级索引的叶子节点都会存储相应行的主键值。所以，如果主键较大，那么每个二级索引的叶子节点所需的空间也会更大，因为它们需要存储更大的主键值。
如果主键是
bigint的自增ID，在每个二级索引上存储，关联主键也就占用 8个字节 的空间。 如果主键是int + varchar(32)这种联合索引，是不是就意味着，关联主键也要占用至少4+32个字节的空间呢？ 如果有多个二级索引（一般2~4个），是不是也要重复多占用不少空间 ？这样的话，存储空间 以及 维护这些二级索引的开销不是也增大了许多么 ？