MySQL优化数据库结构的5种方法（非常详细）

一个好的数据库设计方案对于提高数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。

数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。本节将为读者介绍优化数据库结构的方法。

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来，形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

【实例】假设会员表存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电话、个人描述字段。其中，地址、电话、个人描述等字段并不常用。

可以将这些不常用字段分离出来组成另外一个表，将这个表取名叫 members_detail。表中有 member_id、address、telephone、description 等字段。其中，member_id 是会员编号，address 字段存储地址信息，telephone 字段存储电话信息，description 字段存储会员个人描述信息。这样就把会员表分成两个表，分别为 members 表和 members_detail 表。

创建这两个表的 SQL 语句如下：

CREATE TABLE members (
Id int NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL ,
password varchar(255) DEFAULT NULL ,
last_login_time datetime DEFAULT NULL ,
last_login_ip varchar(255) DEFAULT NULL ,
PRIMARY KEY (Id)
) ;
CREATE TABLE members_detail (
member_id int NOT NULL DEFAULT 0,
address varchar(255) DEFAULT NULL ,
telephone varchar(16) DEFAULT NULL ,
description text
) ;

这两个表的结构如下：

mysql> desc members;
+-----------------+--------------+------+-----+---------+---------------+
| Field           | Type         | Null | Key | Default | Extra         |
+-----------------+--------------+------+-----+---------+---------------+
| Id              | int          | NO   | PRI | NULL    | auto_increment|
| username        | varchar(255) | YES  |     | NULL    |               |
| password        | varchar(255) | YES  |     | NULL    |               |
| last_login_time | datetime     | YES  |     | NULL    |               |
| last_login_ip   | varchar(255) | YES  |     | NULL    |               |
+-----------------+-------------+-------+-----+---------+---------------+
5 rows in set (0.00 sec)
mysql> DESC members_detail;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| member_id   | int          | NO   |     | 0       |       |
| address     | varchar(255) | YES  |     | NULL    |       |
| telephone   | varchar(16)  | YES  |     | NULL    |       |
| description | text         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

如果需要查询会员的详细信息，可以用会员的 id 来查询。如果需要将会员的基本信息和详细信息同时显示，可以将 members 表和 members_detail 表进行联合查询，查询语句如下：

SELECT * FROM members LEFT JOIN members_detail ON members.id=members_detail.member_id;

通过这种分解，可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。

增加中间表

对于需要经常联合查询的表，可以建立中间表，以提高查询效率。

通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

首先，分析经常联合查询表中的字段。然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入到中间表中。最后，使用中间表进行查询。

【实例】会员信息表和会员组信息表的 SQL 语句如下：

CREATE TABLE vip(
Id int NOT NULL AUTO_INCREMENT,
username varchar(255) DEFAULT NULL,
password varchar(255) DEFAULT NULL,
groupId INT DEFAULT 0,
PRIMARY KEY (Id)
) ;
CREATE TABLE vip_group (
Id int NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
remark varchar(255) DEFAULT NULL,
PRIMARY KEY (Id)
) ;

查询会员信息表和会员组信息表：

mysql> DESC vip;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| Id       | int          | NO   | PRI | NULL    | auto_increment |
| username | varchar(255) | YES  |     | NULL    |                |
| password | varchar(255) | YES  |     | NULL    |                |
| groupId  | int          | YES  |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> DESC vip_group;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| Id     | int          | NO   | PRI | NULL    | auto_increment |
| name   | varchar(255) | YES  |     | NULL    |                |
| remark | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

已知现在有一个模块需要经常查询带有会员组名称、会员组备注（remark）、会员用户名信息等会员信息。根据这种情况可以创建一个 temp_vip 表。temp_vip 表中存储用户名（user_name）、会员组名称（group_name）和会员组备注（group_remark）信息。

创建表的语句如下：

CREATE TABLE temp_vip (
Id int NOT NULL AUTO_INCREMENT,
user_name varchar(255) DEFAULT NULL,
group_name varchar(255) DEFAULT NULL,
group_remark varchar(255) DEFAULT NULL,
PRIMARY KEY (Id)
);

接下来，从会员信息表和会员组表中查询相关信息存储到临时表中：

mysql> INSERT INTO temp_vip(user_name, group_name, group_remark)
    SELECT v.username,g.name,g.remark
    FROM vip as v ,vip_group as g
    WHERE v.groupId =g.Id;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

以后，可以直接从 temp_vip 表中查询会员名、会员组名称和会员组备注，而不用每次都进行联合查询。这样可以提高数据库的查询速度。

增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。例如，员工的信息存储在 staff 表中，部门信息存储在 department 表中。通过 staff 表中的 department_id 字段与 department 表建立关联关系。如果要查询一个员工所在部门的名称，必须从 staff 表中查找员工所在部门的编号（department_id），然后根据这个编号去 department 表查找部门的名称。如果经常需要进行这个操作，连接查询会浪费很多时间。可以在 staff 表中增加一个冗余字段 department_name，该字段用来存储员工所在部门的名称，这样就不用每次都进行连接操作了。

注意，冗余字段会导致一些问题。比如：

• 冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段，否则就会使原本一致的数据变得不一致；
• 分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。

从数据库性能来看，为了提高查询速度而增加少量的冗余，大部分情形下是可以接受的。是否通过增加冗余来提高数据库性能，要根据实际需求来综合分析。

优化插入记录的速度

插入记录时，影响插入速度的主要因素是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。

1、MyISAM引擎的表优化

对于 MyISAM 引擎的表，常见的优化方法如下：

1) 禁用索引

对于非空表，插入记录时，MySQL 会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。

为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引的语句如下：

ALTER TABLE table_name DISABLE KEYS;

其中，table_name 是禁用索引的表的表名。

重新开启索引的语句如下：

ALTER TABLE table_name ENABLE KEYS;

对于空表批量导入数据，则不需要进行此操作，因为 MyISAM 引擎的表是在导入数据之后才建立索引的。

2) 禁用唯一性检查

插入数据时，MySQL 会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。

禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS=0;

开启唯一性检查的语句如下：

SET UNIQUE_CHECKS=1;

3) 使用批量插入

插入多条记录时，可以使用一条 INSERT 语句插入一条记录；也可以使用一条 INSERT 语句插入多条记录。

插入一条记录的 INSERT 语句情形如下：

INSERT INTO fruits VALUES('x1', '101', 'mongo2', '5.7');
INSERT INTO fruits VALUES('x2', '101', 'mongo3', '5.7')
INSERT INTO fruits VALUES('x3', '101', 'mongo4', '5.7')

使用一条 INSERT 语句插入多条记录的情形如下：

INSERT INTO fruits VALUES
('x1', '101', 'mongo2', '5.7'),
('x2', '101', 'mongo3', '5.7'),
('x3', '101', 'mongo4', '5.7');

第 2 种情形的插入速度要比第 1 种情形要快。

4) 使用LOAD DATA INFILE 批量导入

当需要批量导入数据时，如果能用 LOAD DATA INFILE 语句，就尽量使用。因为 LOAD DATA INFILE 语句导入数据的速度比 INSERT 语句快。

2、InnoDB引擎的表优化

对于 InnoDB 引擎的表，常见的优化方法如下：

1) 禁用唯一性检查

插入数据之前执行 set unique_checks=0 来禁止对唯一索引的检查，数据导入完成之后再运行 set unique_checks=1。这个和 MyISAM 引擎的使用方法一样。

2) 禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

禁用外键检查的语句如下：

SET foreign_key_checks=0;

恢复对外键的检查语句如下：

SET foreign_key_checks=1;

3) 禁止自动提交

插入数据之前禁止事务的自动提交，数据插入完成之后，执行恢复自动提交操作。

禁止自动提交的语句如下：

set autocommit=0;

恢复自动提交的语句如下：

set autocommit=1;

分析表、检查表和优化表

MySQL 提供了分析表、检查表和优化表的语句：

• 分析表主要是分析关键字的分布；
• 检查表主要是检查表中是否存在错误；
• 优化表主要是消除删除或者更新造成的空间浪费。

1) 分析表

MySQL 中提供了 ANALYZE TABLE 语句来分析表。ANALYZE TABLE 语句的基本语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…

LOCAL 关键字是 NO_WRITE_TO_BINLOG 关键字的别名，二者都是执行过程不写入二进制日志，tbl_name 为分析表的表名，可以有一个或多个。

使用 ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE 语句能够分析 InnoDB、BDB 和 MyISAM 类型的表。

【实例】使用 ANALYZE TABLE 来分析 message 表，执行的语句及结果如下：

mysql> ANALYZE TABLE members;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+InnoDB临时表元数据不再存储于InnoDB
| test_db.members | analyze | status   | OK       |
+-----------------+---------+----------+----------+

上面结果显示的信息说明如下：

• Table：表示分析的表的名称；
• Op：表示执行的操作。analyze 表示进行分析操作；
• Msg_type：表示信息类型，其值通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一；
• Msg_text：显示信息。

2) 检查表

MySQL 中可以使用 CHECK TABLE 语句来检查表。CHECK TABLE 语句能够检查 InnoDB 和 MyISAM 类型的表是否存在错误。

对于 MyISAM 类型的表，CHECK TABLE 语句还会更新关键字统计数据。而且，CHECK TABLE 也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

其中，tbl_name 是表名。option 参数有 5 个取值，分别是 QUICK、FAST、MEDIUM、 EXTENDED 和 CHANGED，各个选项的意义如下：

• QUICK：不扫描行，不检查错误的连接；
• FAST：只检查没有被正确关闭的表；
• CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表；
• MEDIUM：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点；
• EXTENDED：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是花的时间较长。

option 只对 MyISAM 类型的表有效，对 InnoDB 类型的表无效。CHECK TABLE 语句在执行过程中也会给表加上只读锁。

3) 优化表

MySQL 中使用 OPTIMIZE TABLE 语句来优化表。该语句对 InnoDB 和 MyISAM 类型的表都有效。但是，OPTILMIZE TABLE 语句只能优化表中 VARCHAR、BLOB 或 TEXT 类型的字段。

OPTILMIZE TABLE 语句的基本语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

其中，LOCAL | NO_WRITE_TO_BINLOG 关键字的意义和分析表相同，都是指定不写入二进制日志；tbl_name 是表名。

通过 OPTIMIZE TABLE 语句可以消除删除和更新造成的文件碎片。OPTIMIZE TABLE 语句在执行过程中也会给表加上只读锁。

一个表使用了 TEXT 或者 BLOB 这样的数据类型，如果已经删除了表的一大部分，或者已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更新，则应使用 OPTIMIZE TABLE 来重新利用未使用的空间，并整理数据文件的碎片。在多数的设置中，根本不需要运行 OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。