文档章节

mysql 优化(1)

 真爱2015
发布于 2015/12/07 23:43
字数 2645
阅读 115
收藏 25

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

  一般我们使用SQL语句进行查询时,数据库服务器每次在收到客户端 发来SQL后,都会执行这条SQL语句。但当在一定间隔内(如1分钟内),接到完全一样的SQL语句,也同样执行它。虽然这样可以保证数据的实时性,但在 大多数时候,数据并不要求完全的实时,也就是说可以有一定的延时。如果是这样的话,在短时间内执行完全一样的SQL就有些得不偿失。  幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

1、我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲:

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

SELECT * FROM TABLE1SELECT * FROM TABLE1

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

2、临时关闭查询缓冲方法:

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

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1

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

  一般我们使用SQL语句进行查询时,数据库服务器每次在收到客户端发来SQL后,都会执行这条SQL语句。但当在一定间隔内(如1分钟内),接到完全一样的SQL语句,也同样执行它。虽然这样可以保证数据的实时性,但在大多数时候,数据并不要求完全的实时,也就是说可以有一定的延时。如果是这样的话,在短时间内执行完全一样的SQL就有些得不偿失。
  幸好MySQL为我们提供了查询缓冲的功能(只能在MySQL 4.0.1及以上版本使用查询缓冲)。我们可以通过查询缓冲在一定程度上提高查询性能。

1、我们可以通过在MySQL安装目录中的my.ini文件设置查询缓冲:

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

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

2、临时关闭查询缓冲方法:

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

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

3、临时开启查询缓冲方法:

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

二、MySQL对查询的自动优化

  索引对于数据库是非常重要的。在查询时可以通过索引来提高性能。但有时使用索引反而会降低性能。我们可以看如下的SALES表:
CREATETABLE SALES
(
   ID INT(10) UNSIGNED NOTNULL AUTO_INCREMENT,
   NAME VARCHAR(100) NOTNULL,
   PRICE FLOATNOTNULL,
   SALE_COUNT INTNOTNULL,
   SALE_DATE DATE NOTNULL,
 PRIMARYKEY(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' ORDERBY SALE_DATE DESC;

    在以上的SQL的WHERE子句中已经使用了NAME字段上的索引,因此,在对SALE_DATE进行排序时将不再使用索引。为了解决这个问题,我们可以对SALES表建立复合索引:
ALTERTABLE SALES DROPINDEX NAME, ADDINDEX (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来确定连接顺序。

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 GROUPBY FIELD1





































































































本文转载自:http://www.cnblogs.com/adforce/archive/2012/06/02/2532086.html

粉丝 18
博文 103
码字总数 30911
作品 0
东城
私信 提问
加载中

评论(1)

bqlin1987
bqlin1987
赞一个
MySQL基础、管理、高级、用法、架构、优化好文章收集

基础 管理 高级 1、mysql分区技术 http://my.oschina.net/liting/blog/465149 2、mysql 外键(foreign key)的详解和实例 用法 1、mysql实现用拼音搜索中文的数据库实现 http://my.oschina....

tomener
2016/04/25
149
0
经典分享MySQL的limit查询优化

以下的文章主要是对MySQL limit查询优化的具体内容的介绍,我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,...

五味格子
2011/05/11
0
0
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
2018/07/13
0
0
「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)

【mysql优化专题】:本专题全文围绕mysql优化进行全方位讲解,本篇为优化入门篇,让大家知道为什么要优化,究竟在优化什么。喜欢的朋友可以关注收藏。 优化,一直是面试最常问的一个问题。因...

java进阶架构师
2017/11/16
0
0
高性能MySQL06-查询优化(慢查询)

一、分析原因 SQL语句慢查询的原因有多种,如: 1)数据方面: 需要查询的表数据量太大导致性能下降; 是否向数据库请求了不需要的数据行或数据列; MySQL是否在扫描额外的记录 2)SQL语句太...

架构师springboot
05/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

基础工具类

package com.atguigu.util;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import com.alibaba.druid......

architect刘源源
今天
43
0
P30 Pro劲敌!DxO官宣新机:排行榜又要变

5月26日晚间,DxOMark官方推特预告,将在5月27日公布一款新机型的DxOMark评分,猜猜是哪款? 网友猜想的机型有:红米K20、谷歌Pixel 3a、索尼Xperia 1、诺基亚9 PureView等。 DxOMark即将公布...

linux-tao
昨天
15
0
Ubuntu18.04.2窗口过小不能自适应(二次转载)

解决Ubuntu在虚拟机窗口不能自适应 2018年09月06日 16:20:08 起不了名儿 阅读数 855 此博文转载:https://blog.csdn.net/nuddlle/article/details/77994080(原地址) 试了很多办法这个好用 ...

tahiti_aa
昨天
2
0
死磕 java同步系列之CountDownLatch源码解析

问题 (1)CountDownLatch是什么? (2)CountDownLatch具有哪些特性? (3)CountDownLatch通常运用在什么场景中? (4)CountDownLatch的初始次数是否可以调整? 简介 CountDownLatch,可以...

彤哥读源码
昨天
6
0
Nginx提供下载apk服务

有时候我们可能需要提供文件或者其他apk下载链接,通过 nginx 配置可以很简单地实现。 server {    listen 80;    server_name download.xxx.com;    root app;    locati...

Jack088
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部