> For the complete documentation index, see [llms.txt](https://mysql.gitbook.teaho.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mysql.gitbook.teaho.net/schema-yu-shu-ju-lei-xing-you-hua/untitled-2.md).

# Schema设计相关优化

### 一些陷阱

* 避免太多列，成百上千的列的结构总是需要转化的。
* 太多关联，通常建议在12个表以内做关联。

### 范式与反范式，以及缓存汇总表

**首先，明确在商业应用中没有绝对的范式或反范式。**

然后，结合到我认为有代表性的例子：缓存、汇总表中。

比如一个商品要展示用户购买数，我们可以在商品表中增加用户购买数列，在每次发生用户购买时更新这个表。如果不是这样，每次用户访问一页商品都SUM啊UNION啊等等一顿操作就真的很蛋疼。

诸如此类等等吧，对于本点，究竟具体如何操作和设计，我认为要我们自己在实践中思考积累，具体需求具体分析吧。不赘叙。

{% hint style="info" %}
有兴趣可再查阅《高性能MySQL》中4.4.2节，计数器表（我认为也算是汇总表吧。。），其中谈到一个有意思的点，就是，使用汇总列或汇总表，有可能会因为访问大而发生锁等降低了性能的情况，那么，我们可以用书中那种方法多行计数汇总，然后取数时用SUM。
{% endhint %}

### 加速ALTER TABLE操作

MySQL执行大多数修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

常见做法两种：

* 先在一台比提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库切换
* 影子拷贝，创建一张目标新表，复制数据，通过重命名快速切换过去。

当然，在业界，深夜ALTER也是有的。


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://mysql.gitbook.teaho.net/schema-yu-shu-ju-lei-xing-you-hua/untitled-2.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.