MySQL 学习<十六> 性能优化

优化简介

MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

MySQL优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

在MySQL中，可以使用SHOW STATUS语句查询一些MySQL的性能参数


其中value是要查询的参数值，一些常用性能参数如下：

connections：连接MySQL服务器的次数

uptime：mysql服务器的上线时间

slow_queries：慢查询的次数

com_select:查询操作次数

com_insert:插入操作次数

com_update:更新操作次数

com_delete:删除操作次数

如果查询MySQL服务器的连接次数，可以执行如下语句
SHOW STASUS LIKE 'connections';

如果查询MySQL服务器的慢查询次数，可以执行如下语句
SHOW STASUS LIKE 'slow_queries';

优化查询

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

分析查询语句

通过对查询语句的分析，可以了解查询语句的执行情况找出查询语句执行的瓶颈， MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。

EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT SELECT_OPTION

使用EXTENDED关键字，EXPLAIN语句将产生附加信息。SELECT_OPTION是SELECT 语句的查询选项，包括FROM WHERE子句等。执行该语句，可以分析EXPLAIN后面的SELECT语句的执行情况，并且能够分析所查询的表的一些特征。

使用EXPLAIN语句来分析1个查询语句

EXPLAIN EXTENDED SELECT * FROM fruits;

下面对结果进行解释

1.id：SELECT识别符。这是SELECT的查询序列号。

2.select_type：SELECT类型，可以为以下任何一种：
SIMPLE：简单SELECT(不使用UNION或子查询)
PRIMARY：表示主查询，或者是最外层的查询语句（多表连接的时候）
UNION：表示连接查询的第二个或后面的查询语句
DEPENDENT UNION：UNION连接查询中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION连接查询的结果
SUBQUERY：子查询中的第一个SELECT语句
DEPENDENT SUBQUERY：子查询中的第一个SELECT语句，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

3.table：表示查询的表

4. type：表示表的联接类型

下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：
1).system
表仅有一行(=系统表)。这是const联接类型的一个特例。
2).const
表最多只有一个匹配行，它将在查询开始时被读取。余下的查询优化中被作为常量对待。const表查询速度很快，因为它们只读取一次。const用于常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。在下面的查询中，tbl_name可以用于const表：

SELECT * from tbl_name WHERE primary_key=1;

SELECT * from tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

3).eq_ref
对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY时。eq_ref可以用于使用“=” 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

4).ref
对于每个来自于前面的表的任意行组合，将从该表中读取所有匹配的行。如果联接只使用索引键的最左边的前缀，或如果索引键不是UNIQUE或PRIMARY KEY，则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中，MySQL可以使用ref联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

5).ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行，在解决子查询中经常使用该联接类型的优化。在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

6).index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了所用到的索引的清单，key_len列包含了所用到的索引的最长长度。

7).unique_subquery
该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一个索引查找类型，可以完全替换子查询，效率更高。

8). index_subquery
该联接类型类似于unique_subquery，不过索引类型不需要是唯一索引，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

9).range
只检索给定范围的行，使用一个索引来检索行数据。key列显示使用了哪个索引，key_len显示所使用索引的长度。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，类型为range。下面介绍几种检索指定行数据的情况:

SELECT * FROM tbl_name WHERE key_column = 10; 

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; 

SELECT * FROM tbl_name WHERE key_column IN (10,20,30); 

SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);

10).index
该联接类型与ALL相同，除了扫描索引树。其他情况都比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

11).ALL
对于每个来自于先前的表的行组合，进行完整的表扫描。如果第一个表没标记为const，这样执行计划就不会很好。通常可以增加更多的索引来摆脱ALL，使得行能基于前面的表中的常数值或列值被检索出。

索引对查询速度的影响

MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询速度，因此索引对查询速度有着至关重要的影响。

如果查询没有索引，查询语句将扫描表中所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

下面是查询语句中不使用索引和使用索引的对比，首先分析未使用索引的查询情况，EXPLAIN语句执行如下“：

首先可以看到，rows列的值是16说名SELECT 语句扫描了表中的16条记录；

然后在fruits表加上索引，再执行SELECT语句，rows列的值为1，说明只扫描了表中的一条记录，其查询速度当然比扫描16条记录快。

使用索引查询

索引可以提高查询速度，但并不是使用带有索引的字段查询时，索引都会起作用。下面的几种情况跟跟SQLSERVER一样，有可能用不到索引：

1.使用like关键字的查询语句
使用like关键字进行查询的时候，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会起作用。

2.使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引可以包括16个字段（跟SQLSERVER一样）对于多列索引，只有查询条件中使用了
这些字段中的第一个字段时，索引才会被使用，这个字段叫：前导索引或前导列。

创建多列索引，验证多列索引的情况：

从第一条查询看出，WHERE f_id = 12的记录有一条，扫描了一条记录并且使用了index_id_price索引;从第二条记录可以看出，rows列的值为16，说明共扫描了16条记录，并且key列值为NULL，说明EXPLAIN SELECT * FROM fruits WHERE f_price =5.2
语句并没有使用索引。因为f_price字段是多列索引的第二个字段，只有查询条件中使用了f_id字段才会使用index_id_price 索引。

使用OR关键字的查询语句

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

举个栗子，查询语句使用OR关键字的情况：


因为s_id字段上没有索引，第一条语句没有使用索引，总共查询了16条记录；第二条语句使用了f_name和f_id两个索引，来个字段都有索引，故查询记录只有2条。

优化子查询

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑需要多个步骤才能完成的SQL操作。子查询虽然使查询语句灵活，但是执行效率不高。执行子查询时，MySQL需要为内层查询语句结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销临时表。因此，子查询的速度会受到一定影响，如果查询的数据量特别大，这种影响就会更大。
在MySQL中，可以使用连接（join）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询快，如果查询中使用索引的话，性能会更好。

优化数据库结构

一个好的数据库设计方案对于数据库的性能常常起到事半功倍的效果。数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面。

将字段很多的表拆分成多个表

有时候有些字段使用频率很低或者字段的数据类型比较大，那么可以考虑垂直拆分的方法，把不常用的字段和大字段拆分出去。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

增加冗余字段

设计数据库表时应尽量遵循范式理论，尽可能减少冗余字段，但是现今存储硬件越来越便宜，有时候查询数据的时候需要join多个表，这样在高峰期间会影响查询的效率，我们需要反范式而为之，增加一些必要的冗余字段，以空间换时间。需要这样做会增加开发的工作量和维护量，但是如果能换来可观的性能提升，这样做也是值得的。

优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据实际情况，可以分别进行优化

对于MyISAM表，常见优化方法如下：

1.禁用索引

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

ALTER TABLE table_name DISABLE KEYS ;

其中table_name是禁用索引的表的表名。

重新开启索引语句如下：

ALTER TABLE table_name ENABLE KEYS ;

对于空表批量导入数据，则不需要进行此操作，因为MyISAM表是在导入数据之后才建立索引。

2.禁用唯一性检查

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

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

3.使用批量插入
插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。后者通常比前者速度快。

4.使用LOAD DATA INFILE批量导入
当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快很多。


对于INNODB引擎的表，常见的优化方法如下：

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

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

2.禁用外键约束
插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下：

SET FOREIGN_KEY_CHECKS=0;

恢复对外键的检查语句如下：

SET FOREIGN_KEY_CHECKS=1;

3.禁止自动提交
插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作或显式指定事务。禁止自动提交的语句如下：

SET AUTOCOMMIT=0;

恢复自动提交的语句如下：

SET AUTOCOMMIT=1;

分析表、检查表、优化表、修复表和CHECKSUM表

MySQL提供了分析表、检查表和优化表的语句分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费。

1.分析表
MySQL中提供了ANALYZE TABLE 语句分析表，ANALYZE TABLE 语句的基本语法如下：

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，tbl_name为分析的表的表名，可以有一个或多个
使用ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。在分享期间，只能读取表的记录，不能更新和插入记录。ANALYZE TABLE 语句能分析InnoDB、BDB和MyISAM类型的表。

例如，使用ANALYZE TABLE 来分析fruits表，执行语句如下：

结果显示说明：
Table：表示分析的表名
Op：表示执行的操作，analyze表示进行分析操作
Msg_type：表示信息类型其值通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一
Msg_text：显示信息

检查表

MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。该语句基本语法如下：

CHECK TABLE TBL_NAME [,tbl_name]...[option]... 

option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

其中，tbl_name是表名；option参数有5个取值分别是QUICK、FAST、MEDIUM、EXTENDED、CHANGED;各个选项的意思分别是:

QUICK：不扫描行，不检查错误的连接
FAST：只检查没有被正确关闭的表
MEDIUM：扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
EXTENDED：对每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的，但是花的时间较长
CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表

option只对MyISAM表有效，对InnoDB表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

优化表

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

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,TBL_NAME]...

LOCAL和NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志；tbl_name是表名
通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

需要注意的是：

一个表使用了TEXT或者BLOB这样的数据类型，如果已经删除了表的一大部分，或者已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。在多数设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定表进行OPTIMIZE TABLE；OPTIMIZE TABLE语句类似于SQLSERVER的重建索引和收缩数据文件的功能。

优化MYSQL服务器

优化MySQL服务器主要从两个方面入手，一方面是对硬件进行优化；另一方面是对MySQL服务器的参数进行优化

1.优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MYSQL数据库的运行速度和效率。优化服务器硬件的几种方法
1）.配置较大的内存。足够大的内存，是提高MySQL数据库性能之一。内存速度比磁盘I/O快得多，可以通过增加系统缓冲区容量，使数据库在内存停留时间更长，以减少磁盘I/O。
2）.配置高速磁盘系统，以减少读盘等待时间，提高响应速度。
3）.合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
4）.配置多处理器，mysql是多线程的数据库，多处理器可同时执行多个线程

2.优化MySQL的参数
通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器的性能的目的。

MySQL服务器的配置参数都在my.cnf或者my.ini文件的[mysqld]组中。下面对几个对性能影响较大的参数进行介绍：

网络连接的性能配置项及对性能的影响：

● max_conecctions：整个 MySQL 允许的最大连接数；

这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量大于max_conecctions 的情况下，由MySQL 的设置限制，那么应用中必然会产生连接请求的等待，从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数设置的尽可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值

● max_user_connections：每个用户允许的最大连接数；
上面的参数是限制了整个 MySQL 的连接数，而 max_user_connections 则是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来
说，完全没有做太多的限制，可以尽量放开一些。

● net_buffer_length：网络包传输中，传输消息之前的 net buffer 初始化大小；
这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大小，所以造成的影响主要是当我们的每次消息都很大的时候 MySQL 总是需要多次申请扩展该缓冲区大小。系统默认大小为 16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到8KB。

● max_allowed_packet：在网络传输中，一次传消息输量的最大值；

这个参数与 net_buffer_length 相对应，只不过是 net buffer 的最大值。当我们的消息传输量大于 net_buffer_length 的设置时，MySQL 会自动增大 net buffer 的大小，直到缓冲区大小达到 max_allowed_packet 所设置的值。系统默认值为 1MB，最大为1GB，必须设定为 1024 的倍数，单位为字节。

● back_log：在 MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的所有请求将存放在一个等待队列中，这个队列就是 MySQL 的连接请求队列。当我们的系统存在瞬时的大量连接请求的时候，则应该注意 back_log 参数的设置。系统默认值为 50，最大可以设
置为 65535。当我们增大 back_log 的设置的时候，同时还需要主义 OS 级别对网络监听队列的限制，因为如果 OS 的网络监听设置小于 MySQL 的 back_log 设置的时候，我们加大“back_log”设置是没有意义的。

● join_buffer_size ：当我们的 Join 是 ALL ， index ， rang 或者 index_merge 的时候使用的Buffer；

实际上这种 Join 被称为 Full Join。实际上参与 Join 的每一个表都需要一个 Join Buffer，所以在 Join 出现的时候，至少是两个。Join Buffer 的设置在 MySQL 5.1.23 版本之前最大为 4GB，但是从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系统默认是 128KB。

● sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；
表示缓存区的大小。这个值越大，表示排序的速度越快。

配置完参数之后，需要重启MySQL服务才能生效。