`

MYSQL查询的性能优化

阅读更多

 

查询是数据库技术中最常用的操作 。查询操作的过程比较简单,首先从客户端发出查询的SQL语句,数据库服务端在接收到由客户端发来的SQL语句后, 执行这条SQL语句,然后将查询到的结果返回给客户端。虽然过程很简单,但不同的查询方式和数据库设置,对查询的性能将会有很在的影响。

 

因此,本文就在MySQL中常用的查询优化技术进行讨论。讨论的内容如:

 

 

 

  • 通过查询缓冲提高查询速度;

  • MySQL对查询的自动优化;

  • 基于索引的排序;

  • 不可达查询的检测和使用各种查询选择来提高性能。

 

 

 

一、 通过查询缓冲提高查询速度

 

一般我们使用SQL语句进行查询时,数据库服务器每次在收到客户端发来SQL后,都会执行这条SQL语句。但当在一定间隔内(如1分钟内),接到完 全一样的SQL语句,也同样执行它。虽然这样可以保证数据的实时性,但在大多数时候,数据并不要求完全的实时,也就是说可以有一定的延时。如果是这样的 话,在短时间内执行完全一样的SQL就有些得不偿失。

幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲。设置也非常简单,只需要将query_cache_type设为1即可。在设 置了这个属性后,MySQL在执行任何SELECT语句之前,都会在它的缓冲区中查询是否在相同的SELECT语句被执行过,如果有,并且执行结果没有过 期,那么就直接取查询结果返回给客户端。但在写SQL语句时注意,MySQL的查询缓冲是区分大小写的。如下列的两条SELECT语句:

  1. SELECT   *  from   TABLE1
  2. SELECT   *  FROM   TABLE1

上面的两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格,因此,在写SQL语句时,应尽量减少空格的使用,尤其是在SQL首和尾的空格(因为,查询缓冲并不自动截取首尾空格)。

虽然不设置查询缓冲,有时可能带来性能上的损失,但有一些SQL语句需要实时地查询数据,或者并不经常使用(可能一天就执行一两次)。这样就需要把 缓冲关了。当然,这可以通过设置query_cache_type的值来关闭查询缓冲,但这就将查询缓冲永久地关闭了。在MySQL 5.0中提供了一种可以临时关闭查询缓冲的方法:

  1. SELECT   SQL_NO_CACHE   field1 ,  field2   FROM   TABLE1

以上的SQL语句由于使用了SQL_NO_CACHE,因此,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找,每次都会执行它。

我们还可以将my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。

  1. SELECT   SQL_CALHE   *  FROM   TABLE1


二、MySQL对查询的自动优化

索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:

  1. CREATE   TABLE   SALES
  2. (
  3. ID   INT ( 10 )   UNSIGNED   NOT   NULL   AUTO_INCREMENT ,
  4. NAME   VARCHAR ( 100 )   NOT   NULL ,
  5. PRICE   FLOAT   NOT   NULL ,
  6. SALE_COUNT   INT   NOT   NULL ,
  7. SALE_DATE   DATE   NOT   NULL ,
  8. PRIMARY   KEY ( ID )
  9. INDEX   ( NAME )
  10. INDEX   ( SALE_DATE )
  11. ) ;

假设这个表中保存了数百万条数据,而我们要查询商品名为牛肉的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:
SELECT AVG(PRICE) FROM SALES

WHERE name = '牛肉' AND SALE_DATE BETWEEN '2004-01-01' AND '2005-12-31';

如果这种商品的数量非常多,差不多占了SALES表的记录的50%或更多。那么使用SALE_DATE字段上索引来计算平均数就有些慢。因为如果使 用索引,就得对索引进行排序操作。当满足条件的记录非常多时(如占整个表的记录的50%或更多的比例),速度会变慢,这样还不如对整个表进行扫描。因 此,MySQL会自动根据满足条件的数据占整个表的数据的比例自动决定是否使用索引进行查询。

对于MySQL来说,上述的查询结果占整个表的记录的比例是30%左右时就不使用索引了,这个比例是MySQL的开发人员根据他们的经验得出的。然而,实际的比例值会根据所使用的数据库引擎不同而不同。

三、 基于索引的排序

MySQL的弱点之一是它的排序。虽然MySQL可以在1秒中查询大约15,000条记录,但由于MySQL在查询时最多只能使用一个索引。因此,如果WHERE条件已经占用了索引,那么在排序中就不使用索引了,这将大大降低查询的速度。我们可以看看如下的SQL语句:

  1. SELECT   *  FROM   SALES   WHERE   NAME   = “ name   ORDER   BY   SALE_DATE   DESC ;

在以上的SQL的WHERE子句中已经使用了NAME字段上的索引,因此,在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题,我们可以对SALES表建立复合索引:

  1. ALTER   TABLE   SALES   DROP   INDEX   NAME ,  ADD   INDEX   ( NAME ,  SALE_DATE )

这样再使用上述的SELECT语句进行查询时速度就会大副提升。但要注意,在使用这个方法时,要确保WHERE子句中没有排序字段,在上例中就是不能用SALE_DATE进行查询,否则虽然排序快了,但是SALE_DATE字段上没有单独的索引,因此查询又会慢下来。

四、 不可达查询的检测

在执行SQL语句时,难免会遇到一些必假的条件。所谓必假的条件是无论表中的数据如何变化,这个条件都为假。如WHERE value < 100 AND value > 200。我们永远无法找到一个既小于100又大于200的数。

如果遇到这样的查询条件,再去执行这样的SQL语句就是多此一举。幸好MySQL可以自动检测这种情况。如我们可以看看如下的SQL语句:

  1. SELECT   *  FROM   SALES   WHERE   NAME   = “ name1   AND   NAME   = “ name2

以上的查询语句要查找NAME既等于name1又等于name2的记录。很明显,这是一个不可达的查询,WHERE条件一定是假。MySQL在执行 SQL语句之前,会先分析WHERE条件是否是不可达的查询,如果是,就不再执行这条SQL语句了。为了验证这一点。我们首先对如下的SQL使用 EXPLAIN进行测试:

  1. EXPLAIN   SELECT   *  FROM   SALES   WHERE   NAME   = “ name1

上面的查询是一个正常的查询,我们可以看到使用EXPLAIN返回的执行信息数据中table项是SALES。这说明MySQL对SALES进行操作了。再看看下面的语句:

  1. EXPLAIN   SELECT   *  FROM   SALES   WHERE   NAME   = “ name1   AND   NAME   = “ name2

我们可以看到,table项是空,这说明MySQL并没有对SALES表进行操作。

五、 使用各种查询选择来提高性能

SELECT语句除了正常的使用外,MySQL还为我们提供了很多可以增强查询性能的选项。如上面介绍的用于控制查询缓冲的SQL_NO_CACHE和SQL_CACHE就是其中两个选项。在这一部分,我将介绍几个常用的查询选项。

1. STRAIGHT_JOIN:强制连接顺序

当我们将两个或多个表连接起来进行查询时,我们并不用关心MySQL先连哪个表,后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估,最后得出的一个连接顺序决定的。如下列的SQL语句中,TABLE1和TABLE2并不一定是谁连接谁:

  1. SELECT   TABLE1 . FIELD1 ,  TABLE2 . FIELD2   FROM   TABLE1   , TABLE2   WHERE  

如果开发人员需要人为地干预连接的顺序,就得使用STRAIGHT_JOIN关键字,如下列的SQL语句:

  1. SELECT   TABLE1 . FIELD1 ,  TABLE2 . FIELD2   FROM   TABLE1   STRAIGHT_JOIN   TABLE2   WHERE  

由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

2. 干预索引使用,提高性能

在上面已经提到了索引的使用。一般情况下,在查询时MySQL将自己决定是否使用索引,使用哪一个索引。但在一些特殊情况下,我们希望MySQL只使用一个或几个索引,或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。

限制使用索引的范围

有时我们在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置。

  1. SELECT   *  FROM   TABLE1   USE   INDEX   ( FIELD1 ,  FIELD2 )  

从以上SQL语句可以看出,无论在TABLE1中已经建立了多少个索引,MySQL在选择索引时,只考虑在FIELD1和FIELD2上建立的索引。

限制不使用索引的范围

如果我们要考虑的索引很多,而不被使用的索引又很少时,可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引,而使用IGNORE INDEX是选择不被考虑的索引。

  1. SELECT   *  FROM   TABLE1   IGNORE   INDEX   ( FIELD1 ,  FIELD2 )  

在上面的SQL语句中,TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

强迫使用某一个索引

上面的两个例子都是给MySQL提供一个选择,也就是说MySQL并不一定要使用这些索引。而有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

  1. SELECT   *  FROM   TABLE1   FORCE   INDEX   ( FIELD1 )  

以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。

 

3. 使用临时表提供查询性能

当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。

  1. SELECT   SQL_BUFFER_RESULT   *  FROM   TABLE1   WHERE  

和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT,这个选项一般用于分组或DISTINCT关键字,这个选项通知MySQL,如果有必要,就将查询结果放到临时表中,甚至在临时表中进行排序。

  1. SELECT   SQL_BUFFER_RESULT   FIELD1 ,  COUNT ( * )   FROM   TABLE1   GROUP   BY   FIELD1
  2.  

六、 结论

在程序设计中同样存在一个“二八原则”,即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化,重点在于SQL的执行效率。而数据查询优化的重点,则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。

 

 

 

分享到:
评论

相关推荐

    2.mysql查询性能优化1

    优化查询语句是 MySQL 查询性能优化的另一个重要方面。优化查询语句可以减少查询时间、提高查询效率。MySQL 提供了多种查询优化技术,包括使用 UNION 优化、使用子查询优化、使用 JOIN 优化等。 6.3 MySQL 查询优化...

    高性能MySQL_ch04_查询性能优化.pdf

    ### 高性能MySQL查询性能优化知识点详解 #### 一、查询性能优化的重要性 在数据库管理与维护工作中,查询性能优化是提升系统响应速度、降低服务器负载的重要手段之一。随着业务量的增长,数据库面临的查询压力也...

    千金良方:MySQL性能优化金字塔法则.docx

    MySQL性能优化是指通过调整MySQL数据库的配置、优化数据库结构和查询语句等方式,提高MySQL数据库的性能和响应速度,以满足应用程序的需求。MySQL作为最流行的开源数据库之一,被广泛应用于各种规模的企业和应用程序...

    基于MySQL的数据库查询性能优化.pdf

    "基于MySQL的数据库查询性能优化" 在当前的Web应用开发中,数据库的查询操作已经成为整个应用的性能瓶颈, especialmente 对于使用 MySQL 的 Web 应用。因此,数据库查询性能的优化已经成为一个非常重要的课题。...

    释放MySQL性能:优化查询的艺术

    ### 释放MySQL性能:优化查询的艺术 #### 一、引言:查询性能优化的重要性 在当今数据驱动的时代,数据库管理成为任何业务成功的关键因素之一。MySQL作为一种流行的关系型数据库管理系统(RDBMS),广泛应用于各类...

    MySQL的SQL查询性能优化技术.pdf

    MySQL的SQL查询性能优化技术是数据库管理中至关重要的一环,对于提升系统效率和用户体验有着显著影响。本篇主要探讨了优化SQL查询的一些基本原则、企业级监控、配置与参数化、数据管理和性能考量等方面。 首先,...

    mysql性能优化.pptx

    MySQL性能优化是一个涵盖广泛的主题,涉及多个层面,包括SQL语句优化、索引优化、数据库表结构优化、系统级配置优化以及服务器硬件优化。以下是对这些方面进行详细说明: 1. **SQL语句优化** - **慢查询日志**:...

    基于MySQL数据库的查询性能优化研究.pdf

    基于 MySQL 数据库的查询性能优化研究 MySQL 数据库是当前网络环境中最流行的开源关系型数据库之一,随着网络环境中数据总量的急剧攀升,对数据库查询的响应时间提出了更严格的要求。为了提高查询效率,需要研究 ...

    MySQL数据库性能优化研究.docx

    MySQL 数据库性能优化研究 MySQL 数据库性能优化是当前数据库管理系统中最重要的研究领域之一。随着互联网技术的快速发展,MySQL 数据库被广泛应用于各种业务场景。然而,当数据库规模不断扩大,查询复杂度增加时,...

    MySQL性能优化和高可用架构实践.pptx

    本书深入浅出地讲解了MySQL性能优化的核心原理,包括索引设计、查询优化、缓存策略、高可用架构等方面的知识。同时,结合案例对这些原理进行了生动形象的阐述,方便读者理解和实践。 实践过程 本书详细描述了优化...

    mysql数据库性能优化

    本文将围绕《构建高性能Web站点》一书中有关MySQL性能优化的部分内容进行详细解读,帮助读者更好地理解和掌握MySQL性能优化的方法和技术。 #### 二、友好的状态报告与正确使用索引 ##### 1. 友好的状态报告 - **...

    mysql 性能优化与架构设计(word版)

    总结,MySQL性能优化与架构设计涵盖了许多方面,包括查询优化、索引策略、数据库设计、缓存利用、并行处理、架构设计、数据分布以及监控与调优工具的使用。理解和掌握这些知识点,能够帮助我们构建高效、稳定的...

    【荐】mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

    通过对my.cnf/my.ini配置文件的各项关键参数进行中文注释与解析,帮助DBA及运维人员更好地理解每个配置项的作用及其对MySQL性能的影响。 #### 关键配置项详解 ##### 1. **[client]** - **port=3306**:设定MySQL...

    MySQL性能优化中文手册

    MySQL性能优化是数据库管理员和开发人员的关键技能,它涉及到如何有效地使用SQL语句、存储过程和函数,以提高数据查询速度和系统整体效率。本手册将深入探讨这些关键领域,帮助你理解并实施最佳实践。 在SQL语句...

    Mysql性能优化教程

    总之,MySQL性能优化是一个综合性的过程,需要结合理论知识和实践经验,通过对查询优化、索引设计、系统配置等方面的调整,以达到提升系统性能的目标。在高并发环境下,合理的优化策略可以显著提高系统的响应速度和...

    mysql性能的优化

    MySQL性能优化是一个综合性的过程,涉及到SQL查询优化、数据库结构优化以及MySQL服务器配置等多个方面。通过上述方法和技术的应用,可以显著提高MySQL的运行效率,降低资源消耗,最终实现更好的用户体验和服务质量。

    两小时解决所有问题--MySQL数据库性能优化

    两小时解决所有问题--MySQL数据库性能优化

    Mysql 海量数据性能优化.rar

    MySQL是世界上最受欢迎的开源关系型数据库之一,尤其在处理海量数据时,其性能优化显得尤为重要。本资料"海量数据性能优化.rar"主要聚焦于如何在大数据环境下提升MySQL的运行效率和响应速度,确保系统的稳定性和可...

Global site tag (gtag.js) - Google Analytics