9大表设计优化技巧（让你的MySQL性能飞升）

表设计优化，是 MySQL 调优中至关重要的一个环节，也是大厂高频面试题之一。

一个优秀合理的表设计优化，可以有效地减少线上问题，极大地提升 MySQL 的性能和稳定性。

本文主要介绍 MySQL 调优的 9 个常用的优化表设计方法。

文末附：MySQL 面试题精选大全及答案（最新整理，附备面路线） – Java面试题宝

1. 避免深分页

从 MySQL 中查询大量数据时，可以使用分页来避免一次性返回大量的数据。但如果分页深度太深，就会影响 MySQL 的性能。所以，要避免深分页，保证每一页的数据量不要过多。

例如：

有一个用户表，里面存储了 1000 万条用户记录。

需要在网页上显示第 10000-10020 条记录，可以使用如下SQL 查询：

SELECT * FROM users LIMIT 10000, 20;

需要显示第 9999990-10000010 条记录，可以使用如下 SQL 查询：

SELECT * FROM users LIMIT 9999990, 20;

这个时候，MySQL 要扫描 9999990 条记录才能返回结果，这就严重影响到了 MySQL 的性能。

2. 单表字段不要超过 30 个

在设计表结构时，避免将过多的字段存储在单个表中。

如果一个表中的字段太多，可能会导致 MySQL 性能下降、工作量增加。

例如：

有一个用户表，需要存储用户的基本信息、订单信息、收货地址等信息。

我们可以将用户信息、订单信息、收货地址信息分别存储在 3 个表中，通过关联键进行关联，而不是将这些信息全部存储在一个表中。

3. 枚举字段不要使用字符类型

枚举字段通常是指一种具有预定义取值集合的字段类型。

在 MySQL 中，枚举字段通常被用于表示某个字段只有固定几个值可选的情况。如果使用字符类型来代替枚举类型去实现类似的功能，将会导致存储空间浪费、查询性能降低。

使用字符类型带来的问题：

1）占用更多的存储空间

使用字符类型来存储枚举字段会占用更多的存储空间，尤其是在该字段有大量取值时。

2）查询效率低下

使用字符类型的枚举字段，由于需要对字符类型进行比较，因此查询效率通常会比使用真正的枚举类型要低。

那么，如何使用真正的枚举类型呢？

1）使用 ENUM 类型

ENUM 类型是 MySQL 中的一种列类型，它允许列的值为预定义的值之一。

使用 ENUM 类型可以确保列的值只能是预定义的值，而且存储空间比字符类型要小。

2）避免定义过多的取值

枚举字段的取值不应该太多，一般建议在 5 个以内。否则，枚举类型的性能优势就会丧失。

3）避免在 WHERE 子句中使用枚举类型

枚举类型在 WHERE 子句中使用时，需要将枚举值转换为整数进行比较，这会影响查询效率。

示例：使用 ENUM 类型来存储枚举字段。

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'approved', 'rejected') NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

在这个示例中，orders 表中的 status 列只能取三个值之一：pending、approved、rejected。

使用ENUM类型，可确保列的值只能是预定义的值之一，存储空间也要比字符类型小。

综上所述：

当需要使用枚举类型时，尽可能使用 ENUM 类型。这样既避免了存储空间的浪费，同时又提高了查询性能。

4. 小数类型禁止使用 float 和 double

在 MySQL 中，小数类型包括 float、double、decimal。

虽然 float 和 double 可以存储比 decimal 更大的数值，但它们的精度问题可能会导致计算结果出错。

在实际开发中，为了保证计算结果的精度，通常避免使用 float 和 double 类型，并且尽可能使用 decimal 类型。

例如：

假设我们需要计算一个商品的折扣价，可以使用以下 SQL 语句：

SELECT price * discount FROM product;

如果 price 是 float 类型，discount 是 double 类型，那么计算结果就可能出错。

因此，我们应该将 price 和 discount 都设置为 decimal 类型。

5. 所有字段必须设置默认值、并且不允许为 null

在 MySQL 中，字段可以设置默认值和是否允许为 null 。

如果一个字段没有设置默认值，而且允许为 null，那么在插入数据时，如果不显式地指定该字段的值，那么该字段的值就会是 null，这可能导致数据错误或逻辑混乱。

正确的操作是，将所有的字段都设置默认值，并且不允许为null。

如果一个字段没有设置默认值，那么我们可以设置一个合理的默认值，以免在插入数据时出现错误。如果一个字段必须允许为 null，那么就要在应用程序中进行合理的判断，避免出现 null 值。

例如：

假设有一个 user 表，其中包括 id、name 、age 字段，可以使用以下 SQL 语句创建该表：

CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL DEFAULT '',
  age TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

在这个表中：

id 字段设置为主键，并且不允许为 null 。

name 字段和 age 字段都设置了默认值，并且不允许为 null。

这样，在插入数据时，如果不显式地指定 name 和 age 字段的值，那么它们的值就会是默认值。

6. 必须创建主键，最好是有序数值类型

主键是用来唯一标识表中每行数据的一列或一组列。

通过主键，可以很方便地进行数据的增删改查操作，同时提高查询效率。在设计表结构时，应该为每个表都定义一个主键。

在选择主键列时，尽量选择有序数值类型。这样可以保证每个新插入的记录的主键值是递增的，这种方式被称为自增主键。

自增主键的优点是：可以极大地提高插入新记录的效率，并且避免主键冲突的问题。

示例：

创建自增主键

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

在上面的示例中，id 列被设置为自增主键，并且被定义为整数类型。

这样，在每次插入新记录时，MySQL 会自动为 id 列赋一个递增的值，从而保证了每个新记录的主键值都是唯一的。

需要注意的是：

在创建表时，应该始终为每个表定义一个主键。
如果没有显式定义主键，MySQL 可能会选择默认的主键，这可能会导致性能问题。
同时，在选择主键列时，应该选择简单、唯一、递增的列。

7. in 条件中数量不宜过多

在开发过程中，我们经常会使用 IN 语句来查询多个值。

在使用 IN 语句时：

要尽可能减少 IN 语句中包含的值的数量。
如果需要查询多个值，可以考虑使用多个 OR 条件来替代 IN 语句。

原因是：

如果 IN 语句中包含的值过多，MySQL 将这些值全部加载到内存中，然后进行匹配，这样就会导致查询变慢。如果值的数量很大，内存占用过高，一旦超出了系统的内存限制，就会引发数据库崩溃。

示例：

使用 IN 语句来查询一组特定的数据，例如查询某个部门中一组特定的员工信息。

SELECT * FROM employees WHERE department_id IN (1, 2, 3, 4, 5);

当 IN 语句中包含大量的值时，查询的效率就会急剧下降，这是因为 MySQL 会逐一比较每个值，消耗大量的 CPU 和内存资源，造成查询变慢。

所以，如果在 IN 语句中包含大量的值时，需要考虑使用其他的查询方式。

常见的优化方法是将 IN 语句替换成 EXISTS 子查询。例如：

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM department d WHERE e.department_id = d.id AND d.id IN (1, 2, 3, 4, 5));

这个查询会返回与之前相同的结果，但查询效率更高了。这是因为 MySQL 会优先执行 EXISTS 子查询，判断某个部门是否存在于指定的 ID 列表中，如果存在，再去查询员工信息。

除了使用 EXISTS 子查询外，还有其他一些优化 IN 语句的方法：将 IN 语句中的值转换为临时表，然后进行 JOIN 操作；又或者使用多个 OR 条件替代 IN 语句。

但是需要注意的是，这些方法并不是适用于所有情况，要根据实际情况来选择最合适的方法。

8. 禁止创建预留字段

在数据库设计中，预留字段是指为将来可能需要而设置的字段，但目前并没有实际用途的字段。

预留字段的缺点：

预留字段看起来有助于扩展数据结构，实则增加了表的宽度，浪费存储空间，并降低查询性能。
预留字段的使用，可能增加代码复杂性，在开发时就要去考虑这些字段在未来的应用，甚至更改表结构，将投入大量时间和精力来处理数据迁移、以及其他相关问题。

因此，禁止创建预留字段，在需要时再添加新字段，这样可以更好地保证数据库的整洁和高效。

9. 单表索引数不要超过 5 个

索引是一种用于加速数据检索的数据结构。

在数据库中，为了提高查询效率，我们通常会在表的字段上创建索引。但是，索引会占用磁盘空间，维护索引也需要时间和资源。如果过度创建索引，将会导致查询变慢。

建议：

在一个表中，不要创建超过 5 个索引。
在确定要创建哪些索引时，可以优先考虑常用的查询条件和需要频繁更新的字段。例如：如果经常按日期范围查询，则可以在日期字段上创建索引。
在创建索引时，避免在具有大量重复值的列上创建索引，会浪费磁盘空间。
使用索引优化器来确定哪些索引最适合特定查询。索引优化器可以帮助我们避免创建不必要的索引，并确保查询性能的最大化。

【2023】MySQL 调优面试题

立即获取：MySQL 面试题精选大全及答案（最新整理，附备面路线） Java面试题宝

总结

本文从深分页、字段数量、字段类型、默认值、主键设计、记录判断、in条件、预留字段和索引数量等多个方面，全面介绍了表设计优化的注意事项和实用经验。

表设计优化是一个综合性的过程，需要从多个角度来考虑。

通过合理的表设计和优化，可以减少我们的开发工作量和线上问题，提升 MySQL 的性能和稳定性。

建议 Ctrl+D 收藏，可以用来参考学习备面、复盘本篇知识。

我是爱分享的程序员宝妹儿，谢谢关注 Java面试题宝。

如果觉得不错，请一键三连支持下。

—end—

{{userData.name}}已认证