MySQL性能优化(十五)

Updated on in MySQL with 0 views and 0 comments

性能优化

  • 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性能优化(十五)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/18/1731944090877.html