彻底讲透：mysql的表如何设计效率高？

设计高效的MySQL表主要涉及以下几个关键点：

1. 选择合适的字段类型：

   • 根据数据的性质选择最小且最适合的数据类型，比如存储日期用DATE而不是VARCHAR。
   • 尽量避免使用TEXT、BLOB等大对象类型，除非必要，因为它们会降低查询和索引效率。
   • 对于整数类型，根据实际数值范围选择最窄的类型，如能用TINYINT就不用INT。

2. 合理拆分表（范式化）：

   • 遵循数据库设计的范式理论，减少数据冗余，将相关性强的数据放在一张表里，不相关的数据分散到不同表中，通过关联键来连接。
   • 当然，过度范式化可能导致大量的JOIN操作，有时适度反范式化以提高读取性能也是可取的。

3. 创建索引：

   • 在经常用于搜索、排序或作为JOIN条件的列上创建索引，可以显著加快查询速度。
   • 注意，唯一性索引（UNIQUE）不仅能加速查找，还可以保证数据唯一。
   • 索引并非越多越好，每个索引都会占用额外空间，并可能拖慢写入操作。

4. 考虑表分区和分表策略：

   • 对于非常大的表，可以采用分区技术按照某个字段进行分区，提高查询和维护效率。
   • 如果表中的数据是时间序列或者可以根据其他属性分割成多个逻辑块，可以考虑分表策略。

5. 避免NULL值：

   • NULL值在数据库中处理起来通常比非NULL值更复杂，尽量为允许为空的列设置默认值。

6. 合理设计主键：

   • 主键应尽可能短小精悍，自增ID（AUTO_INCREMENT）是一个常见做法，有利于插入性能和二级索引的存储空间。

熊二哥与光头强对话例子

（假设他们正在讨论设计一个用户行为日志表）：

---

熊二哥： 光头强啊，我看咱这用户行为日志表设计得不够高效呢，咋整？

光头强： 咋个不高效了？我这不是都记录了用户的每次操作吗？

熊二哥： 你倒是记录全了，但是你看这个表，用户的ID是字符串类型，每天百万条记录，这样查起来多慢啊！

光头强： 啊！那换成整型INT不就行了，还能设置为主键自增。

熊二哥： 对呀，还有，咱们不是老查某用户最近的行为吗？在user_id和timestamp上加个索引吧。

光头强： 好主意，这样一来，找某个用户最近的操作嗖嗖快！

熊二哥： 还有那个行为类型（action_type），频繁用来筛选统计，也给它加上索引。

光头强： 明白了，再就是别忘了，有些字段像备注啥的，如果不是必查项，就不要放到主表里，可以单独建个子表，减少主表的大小。

熊二哥： 正解！以后随着数据量增大，咱还可以考虑按月份分区或者分表存储日志，这样查询历史数据也更快。

光头强： 行，这就改去，让咱的日志表跑得跟光头一样快溜溜的！

实践例子：

假设我们要设计一个用户表users，用于存储网站用户的账户信息。我们按照高效设计的原则来创建这个表，并解释每一步的设计考量。

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(100) NOT NULL,
    nickname VARCHAR(50),
    gender ENUM('Male', 'Female', 'Other') DEFAULT NULL,
    birth_date DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    PRIMARY KEY (id)
);

解释：

1. 字段选择和类型

   • id：作为主键使用INT类型，并设置为自增（AUTO_INCREMENT），这样在插入新记录时无需手动指定ID，同时索引占用空间小，查询效率高。
   • username 和 email：都是唯一标识用户的字段，用作唯一索引（NOT NULL UNIQUE），确保数据的唯一性并加速查找。
   • password_hash：存储密码哈希值，用VARCHAR类型，长度根据实际需求设定。
   • nickname、gender、birth_date：非关键信息，允许为空或有默认值，可根据实际情况调整字段类型和约束。
   • registration_date 和 last_login：记录用户注册时间和最后登录时间，使用DATE或TIMESTAMP类型，并对registration_date设置默认值为当前时间戳。

2. 索引

   • 表中通过UNIQUE约束自动创建了对username和email列的唯一索引，这有助于提高查询速度，比如查找特定用户名或邮箱地址的用户。

3. 避免NULL值

   • 对于必须存在的信息如username和email设置了NOT NULL约束，避免了NULL值带来的复杂处理和可能的空间浪费。

4. 合理设计主键

   • 使用整数类型的id作为主键，既满足了自增的需求，又有利于查询性能和二级索引的大小。

————————————————————

有啥不会的问题可以评论区提问