MySQL数据库:理解MySQL的性能优化、优化查询

电子说

1.3w人已加入

描述

最近一直在为大家更新MySQL相关学习内容,可能有朋友不懂MySQL的重要性。在程序,语言,架构更新换代频繁的今天,MySQL 恐怕是大家使用最多的存储数据库了。由于MySQL的优化范围较广,从软件到硬件,从配置到应用,无法一一道来。

大量信息的存储和查询都会用到MySQL,因此它的优化就对系统性能提升就尤为重要了。

MySQL性能优化就是通过合理安排资源,调整系统参数使MySQL运行更快、更节省资源。MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等。今天,小编就和大家一起来分享下MySQL性能优化。

优化简介

优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化,一方面是找出系统的瓶颈,提高MySQL数据库整体的性能;另一方面需要合理的结构设计和参数调整,以提高用户操作响应的速度;同时还要尽可能节省系统资源,以便系统可以提供更大负荷的服务。本节将为大家介绍优化的基本知识。

MySQL数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。例如,通过优化文件系统,提高磁盘IO的读写速度;通过优化操作系统调度策略,提高MySQL在高负荷情况下的负载能力;优化表结构、索引、查询语句等使查询响应更快。

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

SHOW STATUS LIKE ‘value’;

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

Connections: 连接MySQL服务器的次数。

Uptime: MySQL 服务器的上线时间。

Slow_ queries: 慢查询的次数。

Com select: 查询操作的次数。

Com_ insert: 插入操作的次数。

Com_ update: 更新操作的次数。

Com_ delete: 删除操作的次数。

优化查询

查询是数据库中最频繁的操作,提高查询速度可以有效地提高MySQL数据库的性能。本节将为大家介绍优化查询的方法。

分析查询语句

通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句。MySQL中提供了EXPLAIN语句和DESCRIBE语句,用来分析查询语句。本小节将为大家介绍使用EXPLAIN语句和DESCRIBE语句分析查询语句的方法。

EXPLAIN语句的基本语法如下:

EXPLAIN [ EXTENDED] SELECT select options

使用EXTENED关键字,EXPLAIN 语句将产生附加信息。select _options 是SELECT语句的查询选项,包括FROM WHERE子句等。

执行该语句,可以分析EXPLAIN后面的SELECT语句的执行情况,并且能够分析出所查询的表的一些特征。

使用EXPLAIN语句来分析1个查询语句,执行如下语句:

性能优化

下面对查询结果进行解释。

1、id: SELECT识别符。这是SELECT的查询序列号。

2、select_ type: 表示SELECT语句的类型。它可以是以下几种取值:

SIMPLE表示简单查询,其中不包括连接查询和子查询;

PRIMARY表示主查询,或者是最外层的查询语句;

UNION表示连接查询的第2个或后面的查询语句;

DEPENDENT UNION,连接查询中的第2个或后面的SELECT语句,取决于外面的查询;

UNION RESULT,连接查询的结果; 。

SUBQUERY, 子查询中的第1个SELECT语句;

DEPENDENT SUBQUERY,子查询中的第1个SELECT,取决于外面的查询;

DERIVED, 导出表的SELECT (FROM子句的子查询)。

3、table: 表示查询的表。

type: 表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型:

system

该表是仅有一-行的系统表。这是const连接类型的一个特例。

const

数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的查询优化中作为常量对待。const表查询速度很快因为它们只读取一次。const用于使用常数值比较PRIMARYKEY或UNIQUE索引的所有部分的场合。

在下面的查询中,tbl_ name 可用于const表:

SELECT * from tbl name WHERE primary key=1 ;

SELECT* from tbl name

WHERE primary key part1=1AND primary key_ part2=2 ;

range

只检索给定范围的行,使用一一个索引来选择行。key 列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。

当使用=、《、》、》=、《、《=、IS NULL、《》、BETWEEN或者IN操作符,用常量比较关键字列时,类型为range。

下面介绍几种检索指定行情况:

性能优化

index

该连接类型与ALL相同,除了只扫描索引树。这通常比ALL快,因为索引文件通常比数据文件小。

ALL

对于前面的表的任意行组合,进行完整的表扫描。如果表是第一一个没标记const的表,这样不好,并且在其他情况下很差。通常可以增加更多的索引来避免使用ALL连接。

possible_ keys: 指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样,可以创建适合的索引来提高查询的性能。

key. 表示查询实际使用到的索引,如果没有选择索引,该列的值是NULL要想强制MySQL使用或忽视possible_ keys 列中的索引,在查询中使用FORCE INDEX. USE INDEX或者IGNORE INDEX.参见SELECT语法。

key_len:表示MySQL选择的索引字段按字节计算的长度,如果键是NULL,则长度为NULL。注意通过key_ len 值可以确定MySQL将实际使用一个多列索引中的几个字段。

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

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

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

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

DESCRIBE SELECT select_ options

DESCRIBE可以缩写成DESC。

索引对查询速度的影响

MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度,因此,索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。

如果查询时没有使用索引,查询语句将扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。如果使用索引进行查询,查询语句可以根据索引快速定位到待查询记录,从而减少查询的记录数,达到提高查询速度的目的。

性能优化

使用索引查询

索引可以提高查询的速度。但并不是使用带有索引的字段查询时,索引都会起作用。使用索引有几种特殊情况,在这些情况下,有可能使用带有索引的字段查询时,索引并没有起作用,下面重点介绍这几种特殊情况:

使用LIKE关键字的查询语句

使用多列索引的查询语句

使用OR关键字的查询语句

优化子查询

MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活,但执行效率不高。

执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。如果查询的数据量比较大,这种影响就会随之增大。

在MySQL中,可以使用连接(JOIN) 查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能会更好。连接之所以更有效率,是因为MySQL不需要在内存中创建临时表来完成查询工作。

打开APP阅读更多精彩内容
声明:本文内容及配图由入驻作者撰写或者入驻合作网站授权转载。文章观点仅代表作者本人,不代表德赢Vwin官网 网立场。文章及其配图仅供工程师学习之用,如有内容侵权或者其他违规问题,请联系本站处理。 举报投诉

全部0条评论

快来发表一下你的评论吧 !

×
20
完善资料,
赚取积分