MySQL 性能优化（十五）

性能优化

• MySQL 性能优化包括查询速度优化、数据库结构优化、MySQL 服务器优化等。

优化简介

• MySQL 数据库优化是多方面的，优化表结构、索引、查询语句等使查询响应更快。

• 在 MySQL 中，可以使用 SHOW STATUS 语句查询一些 MySQL 数据库的性能参数。语句语法如下：

  SHOW STATUS LIKE 'value';
  

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

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

优化查询

分析查询语句

• MySQL 提供了 EXPLAIN 语句和 DESCRIBE 语句，用来分析查询语句。

• EXPLAIN 语句的基本语法如下：

  EXPLAIN [EXTENDED] SELECT select_options
  

  使用 EXTENDED 关键字，EXPLAIN 语句将产生附加信息。执行该语句，可以分析 SELECT 语句的执行情况，并且能够分析出所查询的表的一些特征。

• 查询结果的各个字段解释如下：

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

  • select_type：表示 SELECT 语句的类型。它可以是以下几种取值：

    • SIMPLE：表示简单查询，即不包括连接查询和子查询；
    • PRIMARY：表示主查询，或者是最外层的查询语句；
    • UNION：表示连接查询的第 2 个或后面的查询语句；
    • DEPENDENT UNION：与 UNION 相同，且取决于外面的查询；
    • UNION RESULT：连接查询的结果；
    • SUBQUERY：子查询中的第 1 个 SELECT 语句；
    • DEPENDENT SUBQUERY：与 SUBQUERY 相同，且取决于外面的查询；
    • DERIVED：导出表 SELECT（FROM 子句的子查询）

  • table：表示查询的表。

  • type：表示表的连接类型。

  • possible_keys：指出 MySQL 能使用哪个索引在该表中找到行。

  • key：表示查询实际使用到的索引。

  • key_len：表示 MySQL 选择的索引字段按字节计算的长度。通过 key_len 值可以确定 MySQL 将实际使用一个多列索引中的几个字段。

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

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

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

• DESCRIBE 语句的使用方法与 EXPLAIN 语句是一样的，并且分析结果也是一样的。DESCRIBE 语句的语法形式如下：

  DESCRIBE SELECT select_options
  

  DESCRIBE 可以缩写成 DESC。

索引对查询速度的影响

• 使用索引可以快速定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

使用索引查询

• 索引可以提高查询的速度，但不是使用带有索引的字段查询时索引都会起作用。

1. 使用 LIKE 关键字的查询语句

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

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

   MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件中使用了这些字段中的第 1 个字段时，索引才会被使用。

3. 使用 OR 关键字的查询语句

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

优化子查询

• 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，执行效率不高。
• 在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

优化数据库结构

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

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

增加中间表

• 对于需要经常联合查询的表，可以建立中间表以提高查询效率。

增加冗余字段

• 合理地加入冗余字段可以提高查询速度。
• 冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段，否则就会使原本一致的数据变得不一致。

优化插入记录的速度

• 插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。
• 对于 MyISAM 引擎的表，常见的优化方法如下：

1. 禁用索引

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

   禁用索引的语句如下：

   ALTER TABLE table_name DISABLE KEYS;
   

   重新开启索引的语句如下：

   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 语句导入数据的速度比 INSERT 语句块。

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

   （1）禁用唯一性检查

   这个和
   

   MyISAM 引擎的使用方法一样。

   （2）禁用外键检查

   禁用外键检查的语句如下：
   

   SET foreign_key_checks = 0;
   

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

   SET foreign_key_checks = 1;
   

   （3）禁止自动提交

   插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。
   

   禁止自动提交的语句如下：
   

   SET autocommit = 0;
   

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

   SET autocommit = 1;
   

分析表、检查表和优化表

• 分析表主要是分析关键字的分布；检查表主要是检查表是否有错误；优化表主要是消除删除或者更新造成的空间浪费。

1. 分析表

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

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

   • LOCAL 关键字是 NO_WRITE_TO_BINLOG 关键字的别名，二者都是执行过程不写入二进制日志。

   • 使用 ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。

   • 在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE 语句能够分析 InnoDB、BDB 和 MyISAM 类型的表。

   • 分析结果的字段说明如下：

     • Op：表示执行的操作。
     • Msg_type：表示信息类型，其值通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一。
     • Msg_text：显示信息。

2. 检查表

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

     该语句的基本语法如下：

     CHECK TABLE tb1_name [,tb1_name]...[option]...
     
     option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
     

     option 参数的各个选项的意义如下：

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

   • option 只对 MyISAM 类型的表有效。CHECK TABLE 语句在执行过程中也会给表加上只读锁。

3. 优化表

   • OPTILMIZE TABLE 语句可以优化 InnoDB 和 MyISAM 类型的表，但只能优化表中的 VARCHAR、BLOB 或 TEXT 类型的字段。

   • 该语句的基本语法如下：

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

     以上关键字与分析表的意义相同。

   • 通过 OPTILMIZE TABLE 语句可以消除删除和更新造成的文件碎片。OPTILMIZE TABLE 语句在执行过程也会给表加上只读锁。

优化 MySQL 服务器

优化服务器硬件

• 配置较大的内存。
• 配置高速磁盘系统。
• 合理分布磁盘 I/O。
• 配置多处理器。

优化 MySQL 参数

• MySQL 服务的配置参数都在 my.cnf 或者 my.ini 文件的[mysqld]组中。

• 下面列举几个对性能影响较大的参数：

  • key_buffer_size：表示索引缓冲区的大小。
  • table_cache：表示同时打开的表的个数。
  • query_cache_size：表示查询缓冲区的大小。该参数需要和 query_cache_type 配合使用，当 query_cache_type 值为 0 时，所有查询都不使用查询缓冲区；当值为 1 时，所有查询都将使用查询缓冲区，除非在查询语句中 SELECT 后指定 SQL_NO_CACHE；当值为 2 时，只有在查询语句中使用 SQL_CACHE 关键字，查询才会使用查询缓冲区。
  • sort_buffer_size：表示排序缓存区的大小。默认数值是 2097144（2MB）。
  • read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。在 MySQL 中，使用 SET SESSION read_buffer_size = n; 可临时设置该参数的值。
  • read_rnd_buffer_szie：表示每个线程保留的缓冲区的大小。
  • innodb_buffer_pool_size：表示 InnoDB 类型的表和索引的最大缓存。
  • max_connections：表示数据库的最大连接数。
  • innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于 InnoDB 引擎非常重要。该参数有 3 个值。值为 0 时表示每隔 1 秒将数据写入日志文件并将日志文件写入磁盘；值为 1 时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为 2 时表示每次提交事务时将数据写入日志文件，每隔 1 秒并将日志文件写入磁盘。该参数的默认值为 1。
  • back_log：表示 MySQL 暂停停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。换句话说，该值表示对到来的 TCP/IP 连接的侦听队列的大小。设定该值高于操作系统的限制将是无效的。
  • interactive_timeout：表示服务器在关闭连接前等待行动的秒数。
  • thread_cache_size：表示可以复用的线程的数量。
  • wait_timeout：表示服务器在关闭一个连接时等待行动的秒数，默认数值是 28800。

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

• 使用 FLUSH QUERY CACHE 语句可以刷新缓冲区，清理查询缓冲区中的碎片。