MySQL优化数据库结构

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

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

分解表

有些表在设计时设置了很多的字段，而有些字段的使用频率非常低。这样当这个表的数据量很大时，查询数据的速度就会很慢。下面介绍优化这种表的方法。

对于这种情况，我们可以将这些使用频率较低的字段分离出来形成新表。

例 1

在 student 数据表中有很多字段，其中 comment 字段用来存储学生的备注信息。备注信息的内容特别多，但是该字段很少使用。这时就可以分解出另外一个表。

分解出的表为 student_comment，表中存储 2 个字段，分别为 id 和 comment。其中，id 为学生的学号，comment 为学生备注信息。student_comment 的表结构如下：

mysql> DESC `student_comment`;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| id      | int(11) | NO   | PRI | NULL    |       |
| comment | text    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果需要查询某个学生的备注信息，可以使用学号（id）来查询。如果需要将学生的学籍信息与备注信息同时显示，可以使用表连接查询 student 表和 student_comment 表，查询语句如下：

SELECT * FROM student, student_comment WHERE student.id=student_comment.id;

通过以上方法，可以有效的提高 student 表的查询效率。

增加中间表

表连接会降低数据库的查询速度，所以对于经常使用表连接查询的表，我们可以建立中间表来提高查询速度。下面介绍增加中间表的方法。

首先分析经常需要同时查询哪几个表中的哪些字段，然后将这些字段建立一个中间表，并将原来那几个表的数据插入到中间表中，之后就可以使用中间表来进行查询和统计了。

例 2

下面有两个数据表，分别是 student（学生）表和 score（分数）表。这两个表的结构如下：

mysql> DESC student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| sex     | varchar(4)  | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> DESC score;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| stu_id | int(11)     | NO   | MUL | NULL    |       |
| c_name | varchar(20) | YES  |     | NULL    |       |
| grade  | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

实际应用中，经常需要查询学生的学号、姓名和成绩。对于这种情况，我们可以创建一个 temp_score 表。temp_score 表中存储 3 个字段，分别是 id、name 和 grade。

创建 temp_score  的 SQL 语句如下：

mysql> CREATE TABLE temp_score(
-> id INT NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> grade FLOAT
-> );
Query OK, 0 rows affected (0.00 sec)

然后从 student 表和 score 表中将记录导入到 temp_score 表中。INSERT 语句如下：

INSERT INTO temp_score SELECT student.id, student.name, score.grade
FROM student, score WHERE student.id=score.stu_id;

将这些数据插入到 temp_score 表中以后，可以直接从 temp_score 表中查询学生的学号、姓名和成绩。这样就省去了每次查询时进行表连接，提高了数据库的查询速度。

增加冗余字段

一般情况下，设计数据库时应尽量让表符合三大范式。但是，有时为了提高查询速度，可以有意识地在表中增加冗余字段。下面介绍通过增加冗余字段来提高查询速度的方法。

表的规范化程度越高，表与表之间的关系就越多，查询时也就经常需要在多个表之间进行连接查询，而连接操作会降低查询速度。例如，学生的信息存储在 student 表中，院系信息存储在 department 表中。通过 student 表中的 dept_id 字段与 department 表建立关联关系。

如果要查询一个学生所在系的名称，就必须从 student 表中查找学生所在院系的编号（dept_id），然后根据这个编号去 department 查找系的名称。这个连接查询会浪费很多的时间。因此可以在 student 表中增加一个冗余字段 deptname，用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。

技巧

分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能的角度来看，增加少量的冗余来提高数据库的查询速度是可以接受的。是否通过增加冗余来提高数据库性能，这要根据 MySQL 服务器的具体要求来定。如果磁盘空间很大，可以考虑牺牲一点磁盘空间。