MYSQL高性能学习查询心得

对于低效的查询，分两步分析很有效：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29012686/viewspace-1139935/