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 语句能够分析 InnoDBBDBMyISAM 类型的表。

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

      • Op:表示执行的操作。
      • Msg_type:表示信息类型,其值通常是状态(status)、信息(info)、注意(note)、警告(warning)和错误(error)之一。
      • Msg_text:显示信息。
  2. 检查表

    • CHECK TABLE 语句能够检查 InnoDBMyISAM 类型的表是否存在错误。对于 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 语句可以优化 InnoDBMyISAM 类型的表,但只能优化表中的 VARCHARBLOBTEXT 类型的字段。

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

      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:表示排序缓存区的大小。默认数值是 20971442MB)。
    • 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 语句可以刷新缓冲区,清理查询缓冲区中的碎片。

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    691 引用 • 535 回帖
  • 性能
    63 引用 • 180 回帖
  • 优化

    不成熟的优化是万恶之源。

    过度优化实则是劣化。

    32 引用 • 173 回帖
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    343 引用 • 720 回帖 • 2 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...