MySQL性能优化速记

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL性能优化速记

优惠码发放 2018-07-02 12:42:59 浏览1591
展开阅读全文

MySQL性能优化速记http://www.bieryun.com/3064.html

总结自《MySQL 5.7从入门到精通(视频教学版)》刘增杰编著。

优化简介

MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。
在MySQL中,可以通过SHOW STATUS 语句查询一些MySQL的性能参数。如查询连接次数,可以执行如下语句:
SHOW STATUS LIKE 'Connections';
如果要查询慢查询次数,可以执行如下语句:
SHOW STATUS LIKE 'Slow_queries';
一些常用的性能参数如下:
- Connections:连接MySQL服务器的次数;
- Uptime:MySQL服务器的上线时间;
- Slow_queries:慢查询的次数;
- Com_select:查询操作的次数(select可以换insertupdatedelete,分别查询插入、更新、删除的次数)。

MySQL数据库优化可以从三方面考虑,分别是优化查询、优化数据库结构、优化MySQL数据库。

优化查询

  1. 分析查询语句。
  2. 利用索引来加快查询速度。
  3. 优化子查询。

分析查询语句

通过对查询语句的分析,可以了解查询语句的执行情况。MySQL中提供了EXPLAINDESCRIBE 来分析查询语句。
示例如下:
EXPLAIN SELECT * FROM `user` WHERE username = 'admin';
结果如图示:

几个参数说明一下:
- id:SELECT识别 符。这是SELECT的查询序列号。
- select_type:表示SELECT语句的类型。
- table:表示查询的表。
- type:表示表的连接类型。ALL表示 进行了全表扫描。这是最坏的结果。
- possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果为NULL,表示没有相关索引。
- key:表示查询实际使用到的索引。
- key_len:表示MySQL选择的索引字段按字节计算的长度。
- ref:表示使用哪个列或常数与索引一起来查询记录。
- rows:显示MySQL在表中进行查询时必须检查的行数。
- Extra:表示MySQL在处理查询时的详细信息。

利用索引来加快查询速度

MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效的查询数据的方法,并且加快了查询速度。如分析查询语句中,显示的type为ALL 就是表示进行了全表扫描,没有使用索引的情况。对username 添加一个名为username_index 普通索引,再来分析查询语句,结果如图示:

可以看到,type类型变为ref,这表示从表中读取所有匹配的行,用于索引既不是UNIQUE也不是PRIMARY KEY 的情况,或者查询中使用了索引列的左子集,即索引中左边的部分列组合。

使用索引有几种特殊情况,在这些情况下,有可能使用带有索引的字段查询时,索引并没有起效,下面介绍一下这几种特殊的情况。

  1. 使用LIKE关键字的查询语句
    在使用LIKE关键字进行查询的语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置,索引才会起作用。
  2. 使用多列索引的查询语句
    MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引,只有查询条件中使用了这些字段中的第一个字段时,索引才会起作用。
  3. 使用OR关键字的查询语句
    查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,查询中才使用索引。否则,查询将不使用索引。

优化子查询

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活,但是执行效率不高。执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快。

优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间,而且能够使查询速度更快。数据库结构的设计,需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

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

对于字段很多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段存在而变慢。

增加中间表

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

增加冗余字段

设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅。但是,合理地加入冗余字段可以提高查询速度。

优化插入记录的速度

插入记录时,影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。可以分别对此进行针对性优化。

分析表、检查表和优化表

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

  1. 分析表:ANALYZE TABLE 表名;
  2. 检查表:CHECK TABLE 表名;
  3. 优化表:OPTIMIZE TABLE 表名;(只能优化表中的VARCHAR、BLOB或TEXT类型的字段)

上述的这三个方法,数据库系统都会自动对表加一个只读锁。在分析表期间,只能读取表中的记录,不能更新和插入记录。在多数场景下,不需要使用优化表,即使对可变长度的行进行了大量的更新,也不需要经常运行,每周一次或每月一次即可,并且只需要对特定的表运行。

优化MySQL服务器

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈,也决定了数据库的运行速度和效率。一般常用的优化服务器硬件的方法如下:
1. 配置较大的内存。
2. 配置高速磁盘系统,以减少读盘的等待时间,提高响应速度。
3. 合理分布磁盘I/O,把磁盘I/O分散在多个设备上,以减少资源竞争,提高并行操作能力。
4. 配置多处理器,MySQL是多线程的数据库,多处理器可同时执行多个线程。

优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的。
MySQL服务的配置参数都在my.cnf或者my.ini文件的[MySQLd]组中。其中:

  • key_buffer_size:表示索引缓冲区的大小。索引缓冲区所有的线程共享。合理的缓冲区大小可以更好处理索引。
  • table_cache:表示同时打开表的个数。
  • query_cache_size:表示查询缓冲区的大小。
  • innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。值越大,查询的速度就会越快。
  • max_connections:表示最大连接数,该值不是越大越好,过多的连接数可能会导致MySQL服务器僵死。
    ……其它略。

补充

1.索引是不是越多越好?
合理的索引可以提高查询速度,但是索引也不是越多越好。在执行插入语句前,MySQL要为新插入的记录建立索引,所以过多的索引会导致插入操作变慢。
2. 如何使用查询缓冲区?
查询缓冲区可以提高查询速度,但是只适合查询语句比较多、更新语句比较少的情况,具体方式参照优化MySQL参数。

网友评论

作者关闭了评论
优惠码发放
+ 关注