MySQL性能优化

MySQL 性能优化   2017-03-06 14:43:27 发布
您的评价:
     
5.0
收藏     2收藏
文件夹
标签
(多个标签用逗号分隔)

MySQL性能优化

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

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

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

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

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

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

查询语句的查询条件中只有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的参数可以提高资源利用率,从而达到提高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.cnblogs.com/zhanglei93/p/6488762.html

 

扩展阅读

Mysql性能优化
MySQL性能优化指南
mysql性能优化
MySQL性能优化
MySQL性能优化总结

为您推荐

Mysql常用命令行大全
我的MYSQL学习心得(14) : 备份和恢复
MySQL 的最佳索引攻略
由浅入深探究 MySQL索引结构原理、性能分析与优化
理解 MySQL(4):并行数据库与分区(Partition)

更多

MySQL
性能优化