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

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

有些表在设计时设置了很多的字段。这个表中有些字段的使用频率很低。当这个表的数据量很大时，查询数据的速度就会很慢。本小节将为读者介绍优化这种表的方法。 对于这种字段特别多且有些字段的使用频率很低的表，可以将其分解成多个表。

【示例18-6】下面的学生表中有很多字段，其中在extra字段中存储着学生的备注信息。有些备注信息的内容特别多。但是，备注信息很少使用。这样就可以分解出另外一个表。将这个取名叫student_extra。表中存储两个字段，分别为id和extra。其中，id字段为学生的学号，extra字段存储备注信息.student_extra表的结构如下：

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

如果需要查询某个学生的备注信息，可以用学号(id)来查询。如果需要将学生的学籍信息与备注信息同时显不时，可以将student表和student_extra的表进行联表查询，查询语句如下：

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

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

18.3.2 增加中间表

有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的査询速度。对于这种情况，可以建立中间表来提高查询速度。本小节将为读者介绍增加中间表的方法。

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

【示例18-7】下面有个学生表student加和分数表score。这两个表的结构如下：

mysql> DESC student;
+------------+-------------+------+-----+---------+-------+
|    Field   |    Type     | Null | Key | default | Extra |
+------------+-------------+------+-----+---------+-------+
|      id    |   int(10)   |  NO  | PRI |   NULL  |       |
|     name   | varchar(20) |  NO  | MUL |   NULL  |       |
|     sex    | varchar(4)  |  YES |     |   NULL  |       |
|    birth   |  year(4)    |  YES | MUL |   NULL  |       |
| department | varchar(20) |  YES |     |   NULL  |       |
|  address   | varchar(50) |  YES |     |   NULL  |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)
mysql> DESC score;
+--------+-------------+------+-----+---------+----------------+
| Field  |     Type    | Null | Key | Default |      Extra     |
+--------+-------------+------+-----+---------+----------------+
|   id   |    int(10)  |  NO  | PRI |   NULL  | auto_increment |
| stu_id |    int(10)  |  NO  | MUL |   NULL  |                |
| c_name | varchar(20) |  YES |     |   NULL  |                |
| grade  |    int(10)  | YES  |     |   NULL  |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

实际中经常要查学生的学号、姓名和成绩。根据这种情况可以创建一个temp_score表。tem_score比表中存储3个字段，分别是id,name,grade。CREATE语句执行如下：

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表中查询学生的学号、姓名和成绩。这样就省去了每次查询时进行表连接。这样可以一提高数据库的查询速度。

18.3.3 增加冗余字段

设计数据库表时尽量让表达到三范式。但是，有时为了提高查询速度，可以有意识地在表中增加冗余字段。本小节将为读者介绍通过增加冗余字段来提高查询速度的方法。

表的规范化程度越高，表与表之间的关系就越多；査询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低査询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号(dept_id)，然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。

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

18.3.4优化插入记录的速度

插入记录时，索引、唯一性校验都会影响到插入记录的速度。而且，一次插入多条记录和多次插入记录所耗费的时间是不一样的。根据这些情况，分别进行不同的优化。本小节将为读者介绍优化插入记录的速度的方法。

1. 禁用索引

插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时， 这些排序会降低插入记录的速度。为了解决这种情况，在插入记录之前先禁用索引。等到记录都插入完毕后再开启索引。禁用索引的语句如下：

ALTER TABLE 表名 DISABLE KEYS ;

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

ALTER TABLE 表名 ENABLE KEYS ;

对于新创建的表，可以先不创建索引。等到记录都导入以后再创建索引。这样可以提高导入数据的速度。

2. 禁用唯一性检査

插入数据时，MySQL会对插入的记录进行唯一性校验。这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下:

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

3. 优化INSERT语句

插入多条记录时，可以采取两种写INSERT语句的方式。第一种是一个INSERT语句插入多条记录。INSERT语句的情形如下:

INSERT INTO food VALUES
(NULL,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(NULL,'FF咖啡',FF咖啡厂,20,’2002’,5,'天津'),
(NULL,'GG奶糖','GG奶糖’,14,’2003’,3 ,'广东');

第二种是一个INSERT语句只插入一条记录,执行多个INSERT语句来插入多条记录。INSERT语句的情形如下：

INSERT INTO food VALUES (NULL,'EE果冻','EE果冻厂',1.5,'2007',2,'北京');
INSERT INTO food VALUES (NULL,'FF咖啡',FF咖啡厂,20,’2002’,5,'天津');
INSERT INTO food VALUES (NULL,'GG奶糖','GG奶糖’,14,’2003’,3 ,'广东');

第一种方式减少了与数据库之间的连接等操作，其速度比第二种方式要快。

技巧：当插入大量数据时，建议使用一个INSERT语句插入多条记录的方式。而且，如果能用LOAD DATA INFILE语句，就尽量用LOAD DATA INFILE语句。因为 LOAD DATA INFILE语句导入数据的速度比INSERT语句的速度快。

18.3.5 分析表、检查表和优化表

分析表主要作用是分析关键字的分布。检查表主要作用是检査表是否存在错误。优化表主要作用是消除删除或者更新造成的空间浪费。本小节将为读者介绍分析表、检査表和优化表的方法。

1. 分析表

MySQL中使用ANALYZE TABLE语句来分析表，该语句的基本语法如下:

ANALYZE TABLE 表名 1 [,表名 2...];

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

【示例18-8】下面使用ANALYZE TABLE语句分析score表，分析结果如下：

mysql> ANALYZE TABLE score;
+------------+---------+-----------+----------+
|    Table   |   Op    |  Msg_type | Msg_text |
+------------+---------+-----------+----------+
| test.score | analyze |   status  |     OK   |
+------------+---------+-----------+----------+
1 row in set (0.05 sec)

上面结果显示了4列信息，详细介绍如下：

Table：表示表的名称；

Op:表示执行的操作。analyze表示进行分析操作check表示进行检查查找optimize表示进行优化操作；

Msg_type:表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；

Msg_text:显示信息。

检查表和优化表之后也会出现这4列信息。

2. 检査表

MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB 和MyISAM类型的表是否存在错误。而且，该语句还可以检查视图是否存在错误。该语句的基本语法如下：

CHECK TABLE 表名 1[,表名 2...] [option];

其中，option参数有5个参数，分别是QUICK、FAST、CHANGED、MEDIUM和 EXTENDED。这5个参数的执行效率依次降低。option选项只对MyISAM类型的表有效， 对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

3. 优化表

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

OPTIMIZE TABLE 表名 1 [,表名 2...];

通过OPTIMIZE语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

说明：如果一个表使用了 TEXT或者BLOB这样的数据类型，那么更新、删除等操作就 会造成磁盘空间的浪费。因为，更新和删除操作后，以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来，以便以后再利用。