MySQL性能优化

共计 4958 个字符，预计需要花费 13 分钟才能阅读完成。

MySQL 性能优化就是通过合理安排资源，调整系统参数使 MySQL 运行更快、更节省资源。MySQL 性能优化包括查询速度优化、更新速度优化、MySQL 服务器优化等。本篇博客将从查询优化、数据库结构优化、MySQL 服务器优化 3 个方面介绍。

MySQL 数据库优化，一方面是找出系统瓶颈，提高 MySQL 数据库整体性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。例如，通过优化文件系统，提高磁盘 I\O 的读写书读；通过优化操作系统调度策略，提高 MySQL 在高负荷下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

在 MySQL 中使用 SHOW STATUS 语句查询一些 MySQL 数据库的性能，其语法为：SHOW STATUS LIKE ‘value’;

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

1. Connections：连接 MySQL 服务器的次数；
2. Uptime：MySQL 服务器的上线时间；
3. Slow_queries：慢查询次数；
4. Com_select：查询操作的次数；
5. Com_insert：插入操作的次数；
6. Com_update：更新操作的次数；
7. Com_delete：删除操作的次数。

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

EXPLAIN 语句基本语法格式为：EXPLAIN [EXTENDED] SELECT select_options 使用 EXTENDED 关键字，EXPLAIN 语句将产生附加信息。select_options 是 SELECT 语句的查询选项，包括 FROM WHERE 子句等。通过该语句可以分析 EXPLAIN 后面的 SELECT 语句执行情况，并且分析所查询表的一些特征。

  如下是使用 EXPLAIN 语句来分析一个简单的查询语句：

表 1

查询结果解释如下：

1. id：SELECT 标识符，表示 SELECT 的查询序列号
2. select_type：表示 SELECT 语句的类型。可取值有：SIMPLE 表示简单的查询，不包括连接查询和子查询；PRIMARY 表示主查询，或者最外层的查询语句（见表 2）；UNION 表示连接查询的第二个或者后面的查询语句；DEPENDENT UNIO 连接查询中的第 2 个或后面的 SELECT 语句，取决于外面的查询；UNION RESULT 连接查询的结果；SUBQUERY 子查询中的第一个 SELECT 语句；DEPENDENT SUBQUERY 子查询中的第 1 个 SELECT, 取决于外面的查询（见表 2）；DERIVED 表示导出表的 SELECT
3. table：表示查询的表
4. type：表示表的连接类型，如下是从最佳类型到最差类型的介绍。
   • system：该表是仅有一行的系统表，是 const 连接类型的一个特例
   • const：数据表最多只有一个匹配行，将在查询开始时被读取，并在余下的查询优化中作为常量对待。const 用于使用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分的场合（见表 3）
   • eq_ref：对于每个来自前面表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是 UNIQUE 或 PRIMARY KEY 时，即可使用该类型
   • ref：对于来自前面的表的任意行组合，将从该表中读取所有匹配行。这种类型用于索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况，或者查询中使用了索引列的子集。ref 可以用于使用 = 或者 <=> 操作符的带索引的列（见表 4）
   • ref_or_null：该连接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值得行。在解决子查询中常使用该连接类型的优化。
   • index_merge：该连接类型使用了索引合并优化方法
   • unique_subquery：一个索引查找函数，可以完全替换子查询，效率更高（见表 2）
   • index_subquery：该连接类型类似于 unique_subquery，可以替换 IN 子查询
   • range：只检索给定范围的行，使用一个索引来选择行
   • index：该连接类型与 ALL 相同除了只扫描索引树。由于索引文件通常比数据文件小，因此 index 比 ALL 快（见表 2）
   • ALL：对于前面表的任意行组合，进行完整的表扫描，通常可以增加索引来避免使用 ALL 连接
5. possible_keys：表示 MySQL 能使用哪个索引在该表中找到行，如果为 NULL 表示没有相关索引。在这种情况下，通过检查 where 子句看它是否引用某些列或适合索引的列来提高查询性能。如果有，则可通过创建索引提高查询性能
6. key：表示查询实际使用的索引，该值为 NULL 表示没有选择索引。
7. key_len：表示 MySQL 选择的索引字段按子节计算的长度
8. ref：表示使用哪个列或常数与索引一起来查询记录
9. rows：显示 MySQL 在表中查询时必须检查的行数
10. Extra：该列 MySQL 在处理查询时的详细信息

表 2

表 3

表 4

• 索引对查询速度的影响

MySQL 中提高性能的方式是对数据表设计合理的索引，加快查询速度，首先如下图所示，我们在 tb_student 表上建立索引，再次查看 rows 列值得变化。发现查询的行数由表 1 中的 8 行变为下表中的 1 行，从而通过对 name 列创建索引来提高查询效率。

  在使用索引的过程中，也需要注意一些情况。在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。

• • 使用 LIKE 关键字的查询语句

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 ”%”，索引不会起作用。只有 ”%” 不在第一个位置时，索引才会有效，还是拿 tb_student 表的 name 字段进行查询，如我们查询名字末尾含有“亮”字的人。我们发现虽然我们对 name 字段建立了索引，但是索引并没有减少查询的行数。

• • 使用多列索引的查询语句

MySQL 可以为多个字段建立索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件中使用了这些字段中第 1 个字段时，索引才会被使用。如下图，我们首先对表 tb_score 的 cID 和 grade 两个字段建立索引，来验证多列索引的使用情况。

如下图是未对表的两个字段建立索引时，查询某个课程成绩大于 88 分的学生信���，从下图中可得该语句扫描了表中的 7 行数据。

  我们对该两个字段建立索引，然后同样查询某个课程且成绩大于 88 分的学生的信息，如下表所示，查询的行数缩减到了 4 行。

当我们把限制条件改为仅对成绩查询时，如只查询成绩大于 88 分的学生信息，如下表所示，则该语句查询了表中的全部 15 行数据。

• • 使用 OR 关键字的查询语句

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

  还是拿 tb_score 表进行举例，首先我们删除在该表上创建的索引 index_cID_grade，如下图所示：

然后查询某门课程或成绩大于 88 分的学生信息，发现该语句扫描了表中全部行。

我们再次给表 tb_score 的 cID 和 grade 创建索引后，再次执行该语句发现查询的行数减少了。

• 优化子查询

MySQL 使用子查询可以进行 SELECT 语句的嵌套查询，子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 SQL 操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询结束后再撤销临时表。因此查询速度会受到影响。

在 MySQL 中，可以使用连接（JOIN）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询更快，如果查询中使用索引的话，性能会更佳。连接之所以有更高的效率，是因为 MySQL 不需要再内存中创建临时表来完成查询工作。

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化，本节将介绍优化插入记录速度的几种方法。

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

1. 禁用索引。对于非空表插入记录时，MySQL 会根据表的索引对插入记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况可以在插入记录之前禁用索引，数据插入完毕后在开启索引。禁用索引的语句为：ALTER TABLE tb_name DISABLE KEYS;  重新开启索引的语句为：ALTER TABLE table_name ENABLE KEYS; 对于空表批量导入数据，则不需要进行此操作，因为 MyISAM 引擎的表是在导入数据之后才建立索引的。
2. 禁用唯一性检查：数据插入时，MySQL 会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启。禁用唯一性检查的语句为：SET UNIQUE_CHECKS=0; 开启唯一性检查的语句为：SET UNIQUE_CHECKS=1;
3. 使用批量插入。使用一条 INSERT 语句插入多条记录。如 INSERT INTO table_name VALUES(….),(….),(….)
4. 使用 LOAD DATA INFILE 批量导入 当需要批量导入数据时，使用 LOAD DATA INFILE 语句导入数据的速度比 INSERT 语句快。

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

1. 禁用唯一性检查。同 MyISAM 引擎相同，通过 SET UNIQUE_CHECKS=0;  导入数据之后将该值置 1。
2. 禁用外键检查。插入数据之前执行禁止对外键的查询，数据插入完成之后再恢复对外键的检查。禁用外键检查语句为：SET FOREIGN_KEY_CHECKS=0;  恢复对外键的检查语句为：SET FOREIGN_KEY_CHECKS=1;
3. 禁止自动提交。插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交语句为：SET AUTOCOMMIT=0;  恢复自动提交只需将该值置 1。

通过优化 MySQL 的参数可以提高资源利用率，从而达到提高 MySQL 服务器性能的目的。如下是一些该方面参数的介绍。该参数的配置信息都在 my.cnf 或者 my.ini 文件中。

1. key_buffer_size：表示索引缓冲区的大小。所有线程共享缓冲区。增加索引缓冲区可以得到更好处理的索引。当然如果这个值太大，会导致操作系统频换换页，降低系统性能。
2. table_cache：表示同时打开表的个数，该值越大能够同时打开的表的个数越多。如果打开表的个数太多会影响操作系统的性能。
3. query_cache_size：查询缓冲区的大小。该值和 query_cache_type 配合使用。当 query_cache_type=0, 所有查询都不使用缓冲区，但是 MySQL 并不会释放 query_cache_size 所配置的缓冲区内存。当 query_cache_type=1, 所有查询使用缓冲区，除非在查询语句中指定 SQL_NO_CACHE，如 SELECT SQL_NO_CACHE * FROM table_name; 当 query_cache_type=2, 只有在查询语句中使用 SQL_CACHE 关键字，查询才会使用缓冲区。使用查询缓冲区可以提高查询速度，这种方式适用于修改操作少且经常执行相同的查询操作的情况。
4. sort_buffer_size：排序缓冲区的大小，该值越大排序的速度越快。
5. innodb_buffer_pool_size：表示 InnoDB 类型的表和索引的最大缓存，该值越大查询速度就会越快。
6. max_connections：表示数据库的最大连接数。该值过大会浪费内存资源，严重可能会导致 MySQL 服务器僵死。
7. sort_buffer_size：表示每个需要排序线程分配的缓冲区的大小。增加该值可以提高 ORDER BY 或 GROUP BY 操作的速度。默认值为 2M。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-04/142497.htm