MySQL性能优化

性能优化是指通过某些有效的方法提高MySQL数据库的性能，性能优化的目的是为了使MySQL数据库运行速度更快，占用的磁盘空间更小，性能优化方面包括很多方面，例如优化查询速度，优化更新速度和优化MySQL服务器等。本博文将介绍性能优化的目的，优化查询，优化数据库结构和优化MySQL服务器的方法，以提高mysql数据库的速度。

1、优化概述

优化MySQL数据库是数据库管理员的必备技能，通过不同的优化方式达到提高MySQL数据库性能的目的。
MySQL数据库的用户和数据非常少的时候，很难判断一个mysql数据库的性能的好坏。只有当长时间运行，并且有大量用户进行频繁操作时，MySQL数据库的性能才能提现出来。例如，一个每天有几万用户同时在线的大型网站的数据库性能的优劣就会很明显。这么多用户同时连接MySQL数据库，并且进行查询、插入和更新的操作，如果mysql数据库的性能很差，很可能无法承受如此多用户的同时操作，试想如果用户查询一条记录需要花费很长时间，那么用户很难会喜欢这个网站。
因此，为了提高MySQL数据库的性能，需要进行一系列的优化措施。如果MySQL数据库需要进行大量的查询操作，那么就需要对查询语句进行优化。对于耗费时间的查询语句进行优化，可以提高整体的查询速度。如果连接MySQL数据库用户很多，那么就需要对MySQL服务器进行优化，否则，大量的用户同时连接mysql数据库，可能会造成数据库系统崩溃。

1.1、分析mysql数据库的性能

数据库管理员可以用show status语句查询mysql数据库的性能，语法形式：show status like 'value'；
其中value参数时常用的几个统计参数如下：

• connections:连接mysql服务器的次数。
• uptime：mysql服务器的上线时间
• slow_select：慢查询的次数。
• com_select：查询操作的次数
• com_insert：插入操作的次数
• com_delete：删除操作的次数
  mysql中存在查询InnoDB类型的表的一些参数。例如，Innodb_rows_read参数表示select语句查询的记录数；Innodb_rows_inserted参数表示insert 语句插入的记录数；Innodb_rows_updated参数表示update语句更新的记录数；Innodb_rows_deleted参数表示delete语句删除的记录数。
  如果需要查询mysql服务器的连接次数，可以执行下面的show_status语句：show status like 'Connections';
  通过这些参数可以分析mysql数据库性能，然后根据分析结果进行相应的性能优化。

1.2、通过profile工具分析语句消耗性能

在mysql的命令行窗口中输入查询语句后，在查询结果下方会自动显示查询所用时间，但是这个时间是以秒为单位，如果数据量少，机器配置又不低时，很难看出速度上的差异。这时可以通过MySQL提供的profile工具实现语句消耗性能的分析。

2、优化查询

查询是对数据库最频繁的操作，提高了查询速度可以有效的提高MySQL数据库的性能。

2.1、分析查询语句

在MySQL中，可以使用explain语句和describe语句来分析查询语句，应用explain关键字分析查询语句，其语法结构如下：explain select 语句；
“select 语句”参数为一般数据库查询命令，如“select * from students”.

• ID列：指出在整个查询中select 的位置
• table列：存放所查询的表名
• type列：连接类型，该列中存储着很多值，范围从const到All
• possible_keys列：指出为了提高查找速度，在mysql中可以使用的索引。
• key列：指出实际使用的键。
• rows列：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到该总行数，MySQL必须扫描处理整个查询，再乘以每个表的行值。
• extra列：包含一些其他信息，设计mysql如何处理查询。
  在mysql中，也可以应用describe语句来分析查询语句。describe语句的使用方法与explain语法是相同的，这两者的分析结果也大体相同。其中describe的语法结构如下：describe select 语句;

2.2、索引对查询速度的影响

在查询过程中使用索引，势必会提高数据库查询效率，应用索引来查询数据库中的内容，可以减少查询的记录数，从而达到优化查询的目的。
举例分析索引对查询速度的影响。
首先分析未使用索引时的查询情况，其代码如下：explain select * from tb_bookinfo where bookname='程序设计';
当数据库中存在的少数几条数据的时候，查询不会有太大影响，当数据库中存储了庞大的数据资料时，用户为了搜索一条数据而遍历整个数据库中的所有记录，这将会耗费很多时间。
现在，在book_name字段上建立一个名为index_name的索引，创建索引的代码如下：
create index index_name on tb_bookinfo(bookname);
在建立玩索引完毕后，然后再用explain关键字分析执行情况，其代码如下：
explain select * from tb_bookinfo where bookname='程序设计';
从执行结果看，创建了索引使访问的行数由之前5行减少到1行。所以，在查询操作中，使用索引不但会自动优化查询效率，同时也会降低服务器的开销。

2.3、使用索引查询

在MySQL中，索引可以提高查询的速度，蛋并不能充分发挥其作用，所以在应用索引查询时，也可以通过关键字或其他方式来对查询进行优化处理。

2.3.1、应用like关键字优化索引查询

	首先，应用like关键字，并且匹配字符串中含有百分号“%”符号，应用explain语句执行如下命令```explain select * from tb_bookinfo where bookname like '%设计';```
	在匹配字符串中，第一个字符为百分号“%”时，索引不会被使用，如果“%”所在匹配字符串中的位置不是第一位置，则索引不会被正常使用

2.3.2、查询语句中使用多列索引

多列索引是指在表的多个字段上创建一个索引。只有查询条件中使用了这些字段中的第一个字段时，索引才会被正常使用。
eg:应用多列索引在表tb_bookinfo 的多个字段（bookname和price字段）中创建一个索引，其命令如下：create index index_name on tb_bookinfo(bookname,price);
在应用price字段时，索引不能被正常使用。这就意味着索引并未在MySQL优化中起到任何作用，故必须使用第一字段bookname时，索引才可以被正常使用。

2.3.3、查询语句中使用OR关键字

在MySQL中，查询语句只有包含or关键字时，要求查询的两个字段必须同时为索引，如果所搜索的条件中，有一个字段不为索引，则在查询中不会应用索引进行查询。其中，应用OR关键字查询索引的命令如下：select * from tb_bookinfo where bookname='程序设计' or price = 89;
通过explain来分析使用or关键字的查询命令
在bookname字段上建立一个名为index_price的索引，创建索引的代码:
create index index_price on tb_bookinfo(price);
使用explain来分析使用OR关键字的查询，命令如下：
explain select * from tb_ where bookname = '程序设计' or price=89;

3、优化数据库结构

数据库结构是否合理，需要考虑是否存在冗余，对表的查询和更新的速度、表中字段的数据类型是否合理等多方面的内容。

3.1、将字段很多的表分解成多个表

有些表在设计时设置了很多字段，而这个表中有些字段的使用频率很低，当这个表的数据量很大时，查询数据的速度就会很慢。对于这种字段特别多，且有些字段的使用频率很低的表，可以将其分解成多个表。
eg:在学生表tb_student中有很多字段，其中extra字段中存储这学生的备注信息。有些备注信息的内容特别多，但是备注信息很少使用。这样就可以分解出另外一个表（同时将tb_student中的extra字段删除），将这个分解出来表取名为tb_student_extra。表中存储着两个字段，分别为id和extra。其中id字段为学生的学号，extra字段存储备注信息。
如果需要查询某个学生的备注信息，可以用学号（id）来查询。如果需要将学生的学籍信息与备注信息同时显示时，可以将tb_student表和tb_student_extra表进行联表查询，查询语句如下：select * from tb_student,tb_student_extra where tb_student.id = tb_student_extra.is;
通过这种分解，可以提高tb_student表的查询效率。因此遇到这种字段很多，而且有些字段使用不频繁的表时，可以通过这种分解的方式来优化数据库的性能。

3.2、增加中间表

有时需要经常查询两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。对于这种情况，可以建立中间表来提高查询速度。
先分析经常需要同时查询哪几个表中的哪些字段，然后将这些字段建立一个中间表，并将原来的那个表的数据插入到中间表正宗，之后就可以使用中间表来进行查询和统计。
eg:创建包含学生常用信息的中间表。
有两张数据表，既学生表tb_student和tb_classes。实际应用中，经常要查询学生的学号、姓名和班级。根据这种情况，可以创建一个temp_student表。temp_student表中存储3个字段，分别是id、name和classname。create 语句执行如下：

create table temp_student(
id int not null,
name varchar(45) not null,
classname varchar(45);)

然后从tb_student和tb_classes表中将记录仪导入到temp_student表中，insert 语句如下：
insert into temp_student select s.id,s.name,c.classname from tb_student s ,tb_classes c where s.id = c.id.
将这些数据插入到temp_student表中以后，可以直接从temp_student表中查询学生的学号、姓名、班级名称。这样就省去每次查询时进行表连接，可以提高数据库的查询速度。

3.3、优化插入记录的速度

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

3.3.1禁用索引

插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时，这些排序会降低插入记录的速度，为了解决这种情况，可以在插入记录之前先禁用索引，等到记录都插入完成后再开启索引，禁用索引的语句如下：
alter table 表名 disable keys;
重新开启索引的语句如下：
alter table 表名 enable keys;
对于新创建的表，可以先不创建索引，等到记录都导入以后在创建索引。这样可以提高导入数据的速度。

3.3.2禁用唯一性检查

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

3.3.3优化insert语句

插入多条记录时，可以采取两种写insert语句的方式。第一种是一个insert语句插入多条记录。insert语句的情形如下：
insert into tb_foods values(null,’果冻’,’1.8’),(null,’橙子’,’2.2’);
第二种是每一个insert 语句插入一条记录。很明显第一种方式减少了与数据库之间的连接操作，其速度比第二种方式要快。
当插入大量数据时，建议使用一个insert语句插入多条记录的方式。而且如果能用load data infile语句就尽量用load data infile语句。因为load data infile语句导入数据的速度比insert语句的速度快。

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

分析表的主要作用是分析关键的分布。检查表的主要作用是检查表是否存在错误。优化表的主要作用是消除删除或者更新造成的空间浪费。