MySQL的性能优化

MySQL的性能优化包括查询速度优化，更新速度优化，MySQL服务器优化等。如果是SQL查询很慢，可以开启MySQL的慢查询日志。通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化。

EXPLAIN分析

通过EXPLAIN语句对查询语句进行分析，找出查询语句的执行瓶颈。上一章稍微介绍过EXPLAIN语句，这里再详细阐述一下。
基本语法：EXPLAIN SELECT select_options

id：SELECT识别符。SELECT的查询序列号。

select_type：SELECT语句的类型。取值说明如下：

SIMPLE表示简单查询，不包括连接查询和子查询。
PRIMARY表示主查询，或者是最外层的查询语句。
UNION表示连接查询的第二个或后面的查询语句。
DEPENDENT UNION表示连接查询中的第二个或后面的SELECT语句，取决于外面的查询。
UNION RESULT表示连接查询的结果。
SUBQUERY表示子查询中的第一个SELECT语句。
DERIVED表示导出表的SELECT中的子查询。

table：表示查询的表

type：表示表的连接类型。取值说明如下：（从最佳类型到最差类型排序）

system，该表示仅有一行的系统表。
const：数据表最多只有一个匹配行，它将在查询开始时被读取。查询速度很快，因为只读取一次。

1 2	SELECT * FROM tb_name WHERE primary_key=?; SELECT * FROM tb_name WHERE primary_key_part1=? AND primary_key_part2=?;

eq_ref：对于每个来自前面的表的行组合，从该表中读取一行。可以用于使用”=”操作符比较带索引的列。

1 2	SELECT * FROM ref_tb,other_tb WHERE ref_tb.key_column=other_tb.column; SELECT * FROM ref_tb,other_tb WHERE ref_tb.key_column_part1=other_tb.column AND ref_tb.key_column_part2=?;

ref：对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。可以用于使用”=”或”<=>”操作符带索引的列。

1
2
3

SELECT * FROM ref_tb WHERE key_column=expr;
SELECT * FROM ref_tb,other_tb WHERE ref_tb.key_column=other_tb.column;
SELECT * FROM ref_tb,other_tb WHERE ref_tb.key_column_part1=other_tb.column AND ref_tb.key_column_part2=?;

ref_or_null：如同ref，专门搜索包含NULL值的行。

1	SELECT * FROM ref_tb WHERE key_column=expr OR key_column IS NULL;

index_merge：使用了索引合并优化方法。key列中包含了使用的索引清单。
unique_subquery：索引查找函数，可以完全替换子查询。可以下面形式的IN子查询

1	value IN(SELECT primary_key FROM tb WHERE some_expr)

index_subquery：可以替换IN子查询，只适合下列形式的子查询中的非唯一索引。

1	value IN(SELECT key_column FROM tb WHERE some_expr)

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。当使用”=”，”<>”，”>”，”>=”，”<”，”<=”，”IS NULL”，”<=>”，”BETWEEN”，”IN”操作符，用常量比较关键字列时，类型为range。
index：只扫描索引树。
ALL：对于前面的表的任意行组合，进行完整的全表扫描。

possible_keys：能使用哪个索引在该表中找到行。NULL则没有相关的索引。
key：实际查询时使用到的索引。NULL则没有选择所有。
key_len：索引自动按字节计算的长度。数值越小，表示越快。
ref：使用哪个列或常数与索引一起来查询记录。
rows：在表中进行查询时必须检查的行数。
Extra：处理查询时的详细信息。

设计合理的索引

如下图，没有在f_name字段上增加索引。可以分析执行了全表。

查询在f_name字段上增加索引的选择性。选择性越高的索引价值越大。

建立索引再来分析。

索引未起作用的特殊情况

在使用LIKE关键字的查询语句中，如果匹配字符串的第一个字符为”%”，索引将不会起作用。只有”%”不在第一个位置，索引才会起作用。
使用多列索引，也就是组合索引。遵循最左前缀集合。比如在f_id、f_name、f_price三个字段创建组合索引。那么在查询条件包含f_id、f_name、f_price或f_id、f_name或f_id时会使用到索引。而f_name、f_price条件则索引将不会起作用。
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询才会使用到索引。

子查询说明

子查询虽然可以使查询语句很灵活，但执行效率不高。这是因为MySQL在执行子查询时，会为内层查询语句的查询结果建立一张临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。建议使用连接查询来替代子查询。连接查询不需要建立临时表。
SELECT语句中避免使用*号通配符。数据库在解析的过程中，会将*号依次转换成所有的列名，这个工作是通过查询数据字典完成的。意味着耗费更多的时间。

数据库的结构优化

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新的表。

范式

范式是为解决数据的存储与优化。保存数据的存储后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，终极目标是为了减少数据的冗余。范式是一种分层结构的规范，分为六层。每一层都比上一层更加严格，若要满足下一层范式，前提是满足上一层范式。
六层范式：1NF、2NF、3NF、4NF、5NF、6NF
MySQL属于关系型数据库，有空间上的浪费，其本身也是致力于节省存储空间，与范式所解决的问题不谋而合，在设计数据库的时候，会利用范式来指导设计，但不是强制规范。但是数据库不单是要解决空间问题，还要保证效率问题。范式只为解决空间问题，所以数据库的设计又不能完全按照范式的要求实现。一般情况下，满足3NF即可。
1NF：在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前，还需要额外的处理或拆分，那么该表的设计不满足第一范式。第一范式要求字段的数据具有原子性，不可再分。
2NF：在数据表设计的过程中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键的部分）。即存在字段依赖主键的部分，称之为部分依赖。第二范式就是要解决表的设计不允许出现部分依赖。
3NF：理论上讲，应该一张表中的所有字段都直接依赖主键（逻辑主键：代表的是业务主键）。如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键。把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。第三范式就是要解决传递依赖的问题。
逆规范化：在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以获取到想要的数据，但是效率低一点。这时，会刻意的在某些表中，不去保存另外表的主键（逻辑主键），而是直接保存想要的数据信息。这样一来，在查询数据时，一张表可以直接提供数据，而不需要多表查询（效率低），但是会导致数据冗余增加。同时还需保证数据的一致性。

其他优化

MySQL数据库配置优化这里就不说了。工作重点不在这一块，纯属了解了一下。