`
loamy
  • 浏览: 321015 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql 查询优化

    博客分类:
  • db
阅读更多
查询是数据库技术中最常用的操作。查询操作的过程比较简单,首先从客户端发出查询的SQL语句,数据库服务端在接收到由客户端发来的SQL语句后,执行这条SQL语句,然后将查询到的结果返回给客户端。虽然过程很简单,但不同的查询方式和数据库设置,对查询的性能将会有很在的影响。因此,本文就在 MySQL中常用的查询优化技术进行讨论。讨论的内容如:

1 通过查询缓冲提高查询速度
2 MySQL对查询的自动优化
3 基于索引的排序
4 不可达查询的检测
5 使用各种查询选择来提高性能

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

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

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

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

SELECT * from TABLE1
SELECT * FROM TABLE1

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

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

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

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

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

SELECT SQL_CALHE * FROM TABLE1

  二、MySQL对查询的自动优化

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

CREATE TABLE SALES
(
  ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(100) NOT NULL,
  PRICE FLOAT NOT NULL,
  SALE_COUNT INT NOT NULL,
  SALE_DATE DATE NOT NULL,
PRIMARY KEY(ID),
INDEX (NAME),
  INDEX (SALE_DATE)
)

   假设这个表中保存了数百万条数据,而我们要查询商品号为1000的商品在2004年和2005年的平均价格。我们可以写如下的SQL语句:

SELECT AVG(PRICE) FROM SALES
WHERE ID = 1000 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语句:

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

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

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

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

SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”

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

EXPLAIN SELECT * FROM SALES WHERE NAME = “name1”

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

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并不一定是谁连接谁:

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

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

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

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

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

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

   (1) 限制使用索引的范围

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

SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2) …

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

    (2) 限制不使用索引的范围

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

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

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

    (3) 强迫使用某一个索引

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

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

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

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

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

  SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

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

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

六、 结论

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

相关推荐

    MySQL查询优化浅析

    ### MySQL查询优化浅析 #### 重要概念:查询优化 查询优化是数据库管理系统(DBMS)中的关键组件之一,其核心目标在于寻找最有效的查询执行计划,以最小化资源消耗(如CPU时间、I/O操作)并加快数据检索速度。在...

    MySQL查询优化技术_索引.pdf

    MySQL查询优化技术_索引

    MySQL查询优化器的工作原理

    3. 进行基于规则的优化,这是一系列预定义的转换操作,用来优化查询语句。 4. 开展基于成本的优化,也称为统计式优化,这是最复杂且最有决定性的一步。优化器会考虑多种可能的执行计划,并为每个计划计算一个成本...

    mysql查询优化之索引优化

    - **避免全表扫描**:通过优化查询语句,减少不必要的全表扫描,例如,避免在WHERE子句中使用NOT IN、!=、等操作符。 - **监控和分析**:定期检查EXPLAIN计划,分析索引的使用情况,使用MySQL的性能分析工具如pt-...

    MySQL查询优化实践.pdf

    查询优化可以通过优化索引、优化查询语句和优化数据库结构来实现。OLTP 和 OLAP 是两种不同的查询优化方法,OLTP 适用于在线事务处理,而 OLAP 适用于在线分析处理。 OLTP 和 OLAP OLTP 和 OLAP 是两种不同的查询...

    MySQL查询优化技术讲座.pdf

    ### MySQL查询优化技术详解 #### 引言:MySQL查询优化的重要性 在当今互联网时代,网站的速度直接影响用户体验和业务效果。数据库查询效率成为决定网站响应速度的关键因素之一。MySQL作为广泛使用的开源关系型...

    MySQL查询优化系列讲座.rar

    接着,"MySQL查询优化系列讲座之数据类型与效率"强调了正确选择数据类型对于优化查询的重要性。不同数据类型占用的空间、存储效率以及参与计算的方式都不同,选择合适的数据类型可以减少存储需求,提高查询速度。这...

    MySQL查询优化

    本文将深入分析MySQL查询优化的三个主要方向,包括硬件升级、MySQL进程调优以及查询操作优化,并详细介绍如何利用Memcached软件作为第三方缓存工具来进一步优化MySQL查询。 首先,硬件升级是直接提高数据库性能的...

    Mysql查询优化器.rar

    MySQL查询优化器是数据库管理系统中的核心组件,负责解析SQL语句并制定出执行查询的最佳计划。这个过程涉及多个阶段,包括解析、预处理、优化和执行。本资料“Mysql查询优化器.rar”包含了对这一关键主题的深入探讨...

    MySQL查询优化.rar

    4. **减少临时表的使用**:尽量避免在复杂的查询中创建临时表,可以通过优化查询结构或者使用内存临时表来改善性能。 5. **合理使用GROUP BY和ORDER BY**:这两个操作可能导致排序,消耗大量资源。如果可能,应先...

    mysql查询优化的若干

    ### MySQL 查询优化的关键知识点 #### 1. 理解MySQL如何优化LEFT JOIN 在MySQL中,`A LEFT JOIN B` 的实现主要包括以下几个步骤: - **表B依赖于表A**:这意味着表B的处理顺序依赖于表A的存在,确保在处理B之前,A...

    MySQL数据库技术分享 MySQL查询优化浅析 共32页.pdf

    MySQL查询优化是数据库管理中至关重要的一个环节,其目的是通过找到执行SQL语句的最佳路径,以提高查询效率,减少资源消耗。在这个过程中,查询优化器起着核心作用,它会根据代价模型来评估不同的执行计划,并选择...

    MySQl 查询优化

    其次,优化查询语句结构是另一个关键环节。避免在WHERE子句中使用不等式或复杂的表达式,这可能导致无法利用索引。尽量使用JOIN操作替代子查询,因为JOIN的执行计划可能更高效。同时,避免使用SELECT *,明确指定...

    MySQL查询优化实践-最终版.pdf

    在执行查询时,MySQL优化器会根据统计信息和查询条件选择最佳的索引。 对于复杂查询优化,比如涉及多个表连接的查询,应考虑使用连接顺序、索引覆盖和子查询优化等策略。连接顺序对查询性能影响很大,MySQL会选择...

    MySQL查询优化技术讲座[收集].pdf

    7. **查询优化技巧**:除了索引,还可以通过其他方式优化查询,比如减少子查询、避免在WHERE子句中使用函数、使用EXPLAIN分析查询计划等。理解数据库查询执行的逻辑可以帮助找出性能瓶颈。 8. **存储引擎的选择**:...

    Mysql查询优化

    Mysql查询优化,查询优化器,子查询,分页查询1)在执行计划1中,哪张表是驱动表? 表的连接顺序是怎样的?每一步表的扫描类型是什么? 2)在执行计划2中,表的执行顺序是怎样的?每一步表的扫描类型是什么? 3)在...

Global site tag (gtag.js) - Google Analytics