MySQL 数据库优化

1. 选择最适用的字段属性

MySQL 可以很好的支持大数据量的存取，但是一般来说，数据库中的表越小，在它上面执行的查询就越快，因此，在创建表的时候，为了获得更好的性能，可以将表中的字段宽度设置得尽可能小。例如，在定义邮政编码字段时，若将其设置为 char(255) ，显然给数据库增加了不必要的空间，甚至使用 varchar 这种类型也是没必要的，因为 char(6) 就可以很好的完成任务了。

另外一个提高效率的方法是尽量把字段设置为 not null (设置为 null 需要额外的空间) ，这样将来在执行查询的时候，数据库不用去比较 null 值。

对于某些文本字段，例如 “国家”、“省份”、“民族”、“性别”、“状态”、“部门” 这些字段的取值是有限且固定的，我们可以将其定义为 enum 类型，因为在 MySQL 中，enum 类型会被当做数值型数据来处理，而数值型数据被处理起来的速度比文本型快很多，这样可以达到提高数据库性能的目的。

2. 使用连接 (join) 来代替子查询

MySQL 从 4.1 开始支持 SQL 的子查询，这个技术可以使用 select 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们需要把学生表中没有选择课程的学生筛选出来，就可以利用子查询先从课程表中把所有选择课程的学生学号选择出来，然后把结果传递给主查询，如下图所示：

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时可以避免事务或者表锁死，并且写起来也很容易。子查询(一个查询的结果作为另一个 select 子句的条件)虽然灵活但执行效率不高，因为使用子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表，所以子查询的速度会受到一定的影响。而连接查询不需要建立临时表其查询速度比子查询快，在有些情况下，子查询可以被更有效的连接(join) 来代替，如上图的查询工作可以使用连接(join)来完成，速度会快很多。

3. 使用联合(union)来代替手动创建的临时表

MySQL 从 4.0 版本开始支持 union 查询，它可以把需要使用临时表的两条或者更多的 select 查询合并在一个查询中，在客户端会话结束后，临时表会被自动删除，从而保证数据库整齐、高效，使用 union 来创建查询的时候，我们只需要用 union 作为关键字把多个 select 语句连接起来就可以了，需注意的是所有 select 语句中的字段数目要相同。下面的例子演示了一个使用 union 的查询

4. 使用事务

尽管我们可以使用子查询、连接、联合来处理各种各样的查询，但不是所有的数据库操作都可以只用一条或者少数几条 SQL 语句就可以完成。更多的时候需要用一系列的语句来完成某种工作，但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中的每条语句都操作成功，要么都失败，这样就可以保持数据库中数据的一致性和完整性。事务以 begin 关键字开始，commit 关键字结束，在这之间若有一条 SQL 操作失败，那么 rollback 命令可以把数据库恢复到 begin 开始之前的状态。

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方式来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5. 锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题，但假设有成千上万的用户同时访问一个数据库系统，就会产生比较严重的响应延迟。在有些情况下我们可以通过锁定表的方法来获得更好的性能。如下图一是锁表 update 前的学生信息，图二为锁表 update 后学生的信息。

 这里，我们用一个 select 语句查看学生初始信息，之后用 update 语句更新学生的年龄值，包含有 write 关键字的 lock tables 语句可以保证在 unlock tables 命令被执行前，不会有其它的访问来对 student_test 表进行 insert、update、delete 操作。

6. 使用外键

锁定表的方法可以维护数据的完整性，但是却不能保证数据的关联性，这个时候我们就可以使用外键。

例如，外键可以保证每一条选课表记录都指向一个存在的学生和一个存在的课程，在这里外键可以可以把 student_test 表中的 Sno 映射到 sc_test 表中的 Sno ，任何一条没有合法 Sno 的记录都不会被更新或插入到 sc_test 表中。

在例子中的参数 "on delete cascade" 保证了当 student_test 或者 course_test 表中的一条记录被删除时，sc_test 表中所有与该记录有关的记录会被自动删除。如果在 MySQL 中使用外键，要确保表的类型是事务安全表 InnoDB 类型。  

7. 使用索引

索引的优点：创建索引可以大大提高系统的性能。

• 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是建立索引的最主要原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用 group 和 order by 进行数据检索时，同样可以显著减少查询中的分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

• 创建索引和维护索引要耗费时间，这种时间随数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占用一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

一般来说，应该在这些列上建立索引：

• 在经常需要搜索的列上，可以加快搜索的速度。
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
• 在经常需要排序的列上建立索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 在经常使用在 where 子句中的列上面建立索引，加快条件的判断速度。

一般来说，不应该创建索引的列具有下列的特点：

• 对于那些在查询中很少使用或者参考的列不应该建立索引，这是因为，既然这些列很少使用到，有无索引，并不能提高查询速度。若增加索引，反而降低了系统的维护速度并占用了空间。
• 对于那些只是很少数据值的列也不应该建立索引，这是因为，由于这些列取值很少，如学生表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例。即需要在表中搜索的数据行的比例很大，增加索引，并不能明显加快检索速度。
• 对于那些定义为 text、image、bit 数据类型的列不应该增加索引，这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引。
• 当修改性能远远大于检索性能时，不应该建立索引，这是因为，修改性能和检索性能是互相矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能；当减少索引时，会提高修改性能，降低检索性能。因此，当修改远远多于检索操作时，不应该创建索引。

补充：

• 使用 MySQL explain 或 desc 关键字对查询语句进行分析

如下图所示，其中的 rows 参数代表本次查询遍历的数据行数：

•  不使用索引查询成绩表中成绩为 60 的记录，结果如下图所示，其中 key 代表的是使用索引的名称，这里为 null ，没使用索引。

•  新建索引后，查询成绩表中成绩为 60 的记录，如下图所示，key 代表索引名，rows 为 1 表示本次查询只遍历了 1 次，不使用索引需遍历 8 次(可见上图)。

• 若是在子查询中，主句连接的索引无效，子句连接的索引有效，如下图所示，此处的 student_test 的 Sno 索引无效，sc_test 的 Grade 索引有效，主句的 key 为 null ，子句的 key 为 index_Grade 索引。

•  使用 like 关键字进行查询时，如果匹配字符串的第一个字符为 '%' ，索引不会起作用，如下图所示，key 为 null

• 上图中的查询 若 '%' 不在第一个位置时，索引起作用，如下图所示 

• 复合索引的使用：

如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，例如，我们在 course_test 上新建如下索引

create index index_course_test on course_test(Cno,Cname,Ccredit);

以上索引相当于建立了 index(Cno)、index(Cno,Cname)、 index(Cno,Cname,Ccredit) 这三个索引，在 SQL 语句的 where 条件中单独使用 Cname 或 Ccredit 时不会使用到该索引，必须使用 Cno 时才会使用到该索引。

8. 优化查询语句

• 当只需要一行数据时使用 limit 1

当你查询表的时候，你已经知道结果只会有一条结果，但因为你可能需要去 fetch 游标，或是你也许会去检查返回的记录数。在这种情况下，加上 limit 1 可以增加性能，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合条件的数据。

• 避免 select *

从数据库里读取越多的数据，那么查询就会越慢。并且，如果你的数据库服务器和 WEB 服务器是两台独立的服务器的话，还会增加网络传输的负载。所以，应该按需取所需要的字段，不需要的字段不取。

• 不要 order by rand()

如果你想把返回的数据行打乱，然后随机挑选一条记录，使用 order by rand() 会让你的数据库的性能呈指数级的下降。MySQL 会不得不去执行 rand() 函数(很耗 CPU 时间)，而且这是为了每一行记录去记行，然后再对其排序，计算你用了 limit 1 也无济于事(因为要排序)。

• 在建有索引的字段上尽量不要使用函数进行操作

例如，在一个 date 类型的字段上使用 year() 函数，将会使索引不能发挥应有的作用。

• 在搜索字符型字段时，我们有时会使用 like 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如，下面的查询将会比较表中的每一条记录。

如果换用下面的查询，返回结果一样，但速度上要快得多。