ITPub博客

首页 > 数据库 > MySQL > MYSQL高性能学习查询心得

MYSQL高性能学习查询心得

原创 MySQL 作者:dongyu2013 时间:2014-04-11 09:56:56 0 删除 编辑

对于低效的查询,分两步分析很有效:

1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也能是访问了太多的列

2.确认MySQl服务器层是否分析大量超过需要的数据行

消耗应用服务器的CPU和内存资源

1.查询不需要的记录

2.多表关联时返回全部列

3.总是取出全部列 (会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O,内存和CPU的消耗,优点:复用性高,有缓存机制的情况下可以考虑)

4.重复查询相同的数据

对于MySQL,最简单的衡量查询开销的三个指标如下:

响应时间 扫描的行数 返回的行数

一般MySQL能够使用如下三种方式应用where条件:从好到坏依次为:

1.在索引中使用where条件过滤不匹配的记录。这是在存储引擎完成的

2.使用索引覆盖扫描来返回记录(在Extra列中出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的,但无需再回表查询记录

3从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using where)。这在MySQL服务器层完成,MySQL需要先从数据库表读出记录然后过滤

如果发现需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它

1.使用索引覆盖扫描,把所有需要的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了

2.改变库表结构。例如使用单独的汇总表

3.重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询

重构查询的方式

1.一个复杂查询还是多个简单查询

2.切分查询

3.关联分解

  ·让缓存的效率更高
 
  ·将查询分解后,执行单个查询可以减少锁的竞争

  ·在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展

  ·查询本身效率也可能会有所提升

  ·可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而

    在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减

    少网络和内存的消耗

  ·更进一步,这样做相当于在应用中实现了哈希关联,而不是在使用MySQL的嵌套循环关联

MySQL客户端/服务器通信协议

通信方式:半双工

无法也无须将一个信息切成小块独立来发送

max_allowed_packet

当使用多数连接MySQL的库函数从MySQL获取数据时,其结果看起来都像是从MySQL服务器获取数据,而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题,但是如果需要返回一个很大的结果集的时候,这样做并不好,因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集,就能大大减少内存的消耗,这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是,对于服务器来说,需要查询完成后才能释放资源,所以在和客户端交互的整个过程中,服务器的资源都是被这个查询所占用的。

查询的状态

Sleep:线程正在等待客户端发送新的请求

Query:线程正在执行查询或者正在将结果发送给客户端

Locked:在MySQL服务器层,该线程正在等待表锁

Analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划

Copying to tmp table:线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做GROUP BY操作,要么是文件排序操作,或者是UNION操作

Sorting result:线程正在对结果集进行排序

Sending data:这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据

查询优化

MySQL使用基于成本的优化,它将尝试预测一个查询使用某种执行是的成本,并选择其中成本最小的一个。最初,成本的最小单位是随机读取一个4K数据页的成本,后来变得更加复杂,并且引入了一些因子来估算某系操作的代价,如当知心一次where条件比较的成本。可以通过查询当前会话的Last_query_cost的值来

得知MySQL计算的当前查询的成本

优化策略可以简单地分为两种:静态优化,动态优化

静态优化:可以直接对解析树进行分析,并完成优化

动态优化:则和查询的上下文有关,也可能和很多其他因素有关

优化类型

重新定义关联表的类型

将外连接转化为内连接

使用等价变化规则

优化count(),min()和max()

预估并转化为常数表达式

覆盖索引扫描

子查询优化

提前终止查询

等价转播

列表In()的比较

实际上,MySQL在优化阶段就为每个表创建一个handler实例,优化器根据这些实例的接口可以获取表的相关信息,包括表的列名,索引的统计信息,等等

关联查询优化(关联表的顺序不同)

排序优化(两种算法)

优化关联查询

>确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器的关联顺序是B,A,那么就不需要在B表的对应列上建索引

>确保任何的GROUP BY和OREDER BY中的表达式只涉及到一个表中的列,这样M有SQL才有可能使用索引来优化这个过程

>当升级MySQL的时候需要注意:关联语法,运算符优先级等其他可能会发生变化的地方


 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29012686/viewspace-1139935/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-25

  • 博文量
    263
  • 访问量
    208314