MySQL学习之优化入门

一.了解MySQL的优化

MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能，这个时期的优化一般在开发完成后并使用了一段时间进行的。
另一方面需要合理的结构设计和参数调整，节省系统资源，这个时期的优化一般在开发期完成的，这时项目还没投入使用。

MySQL的优化原则是：减少系统瓶颈，节省资源，提高系统反应速度。例如通过优化文件系统提高磁盘I\O的读写速度 ， 通过优化操作系统的调度策略来提高MySQL在高负荷情况下的负载能力， 优化表结构，索引，查询语句来提高响应速度

二.学习一些常用的优化技术

1.优化操作预备命令学习

1）SHOW STATUS LIKE 'value';

其中的value是要查询的参数值，常用的有：

Connections    连接服务器的次数
Uptime         MySQL服务器的上线时间
Slow_queries   慢查询的次数
Com_select     查询操作的次数
Com_insert     插入操作的次数
Com_update     更新操作的次数
Com_delete     删除操作的次数

例如：（不区分大小写）

2）MySQL中还提供了EXPLAIN语句和DESCRIBE语句来分析查询语句

例如：

id: SELECT识别符，这是SELECT的查询序号。

select_type: 它表示EXPLAIN 后查询语句的类型，SIMPLE表示简单查询（就是不包括连接查询和子查询的查询），PRIMARY表示主查询，还有许多类型以后遇到再了解。

table: 表示查询的表。

type：表示表的连接类型，这里的ALL表示进行完整的表扫描来查找，ALL连接通常最为糟糕，所以我们要尽量通过索引来避免。

possible_keys:指出MySQL能使用那个索引在该表中找到行，这里为NULL说明没有相关索引。

key: 表示查询实际使用到的索引，如果没有选择，则为NULL。

key_len: 表示MySQL选择的索引字段按字节计算的长度。

ref：表示使用哪个列或常数与索引一起来查询记录。

rows： 表示MySQL在表中的查询必须检查的行数。

Extra: 表示MySQL在处理查询时的详细信息。

相对于EXPLAIN语句，DESCRIBE语句更为简洁（直接用desc代替即可）：

2.索引优化技术
我们可以对表添加合理的索引来提高性能。

1）索引可以提高查询速度，但不是使用带有索引的字段查询时都会起作用，使用索引有几种特殊的情况，在这些情况下有可能使用带有索引的字段查询时并没有起作用：

a.使用LIKE关键字的查询语句：
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 % ，这时索引不会起作用。只有 % 不在第一个位置，索引才会起作用。

b.使用多列索引的查询语句：
只有遵循最左原则索引才生效。

c.使用OR关键字的查询语句
查询语句中的查询条件中只有OR关键字，且OR前后两个条件的列都是索引列时，查询索引才生效，否则索引不生效。

3.优化子查询
我们都知道子查询虽然方便，但是它的执行效率不高，执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后，再撤销这些临时表，所以子查询的速度会比一般的慢。

在MySQL中，我们可以使用连接（JOIN）查询来代替子查询，因为连接查询不会建立临时表。

4.优化数据库结构
一个好的数据库设计方案对于数据库的性能影响非常大。合理的数据库结构设计能使执行效率最大化。数据库结构的设计需要考虑数据冗余，查询和更新的速度，字段的数据类型是否合理等。

下面是一些优化数据库结构的方法：

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

2）增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，这样提高了查询速度。

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

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

a.禁用索引：对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引，如果插入大量数据，建立索引会降低插入记录的速度，为了解决这种情况，我们可以在插入记录之前禁用索引，数据插入完毕后再开启索引。
禁用和开启索引的语句如下：

ALTER TABLE table_name DISABLE KEYS;//禁用索引
ALTER TABLE aable_name ENABLE KEYS;//开启索引

b.唯一性检查：插入数据时，MySQL会对插入的记录进行唯一性校验，这种校验也会降低插入记录的速度，同理，我们也可以通过禁用唯一性校验来加快速度：

SET UNIQUE_CHECKS=0;//禁用唯一性校验
SET UNIQUE_CHECKS=1;//开启唯一性校验

c.使用批量插入
插入多条记录时，可以使用一条INSERT语句插入一条语句，可以使用INSERT语句插入多条记录
显然，后者速度会更快：

INSERT INTO table_name VALUES
(key1,key2,key3,key4),
(key1,key2,key3,key4),
(key1,key2,key3,key4),
(key1,key2,key3,key4);

d.使用LOAD DATA INFILE批量导入
当需要批量导入时，如果能用这条语句，尽量使用，因为这条语句的执行效率再导入批量数据时比INSERT高。
对于InnoDB引擎的表，常见的优化方法如下：

禁用唯一性检查

禁用外键检查：

SET foreign_key_checks=0;//禁用
SET foreign_key_checks=1;//开启

禁止自动提交：

SET autocommit=0;//禁止
SET autocommit=1;//开启

5）分析表，检查表和优化表
MySQL提供了分析表，检查表和优化表的语句。

a.分析表主要是分析关键字的分布：

Op: 表示执行的操作，analyze表示进行分析操作。
Msg_type:表示信息类型，通常是 status , info ,note, warning,error之一。
Msg_text:显示信息。

b.检查表：MySQL可以使用CHECK TABLE语句来检查表，它可以检查表是否存在错误。

c.优化表：MySQL中使用OPTIMIZE TABLE语句来优化表，但是这个语句只能优化表中VARCHAR,BLOB,TEXT类型的字段。

上面的学习只是大概的了解MySQL优化技术，想要掌握还需要深入学习。