MySQL 查询优化之【分析工具、索引建议、优化思路】

最新推荐文章于 2022-08-01 14:32:11 发布

ppxin

最新推荐文章于 2022-08-01 14:32:11 发布

阅读量778

点赞数 1

分类专栏： Mysql笔记文章标签：索引调优 explain profile

本文链接：https://blog.csdn.net/ppxin/article/details/108768759

版权

Mysql笔记专栏收录该内容

17 篇文章 0 订阅

订阅专栏

10. 尽可能避免复杂的Join和子查询（复杂查询引起查询阻塞）

3、获取系统中保存的所有 Query 的 profile概要信息

4、针对单个 Query 获取详细的 profile 信息

2、ORDERBY，GROUPBY 和 DISTINCT 优化思路

一、查询优化基本思路和原则

分析如何优化 MySQL Query 之前，我们需要先了解一下 Query 语句优化的基本思路和原则。一
般来说， Query 语句的优化思路和原则主要提现在以下几个方面：

1. 优化更需要优化的Query

首先，什么Query 的优化能给系统整体带来更大的收益，就更需要优化。一般来说，高并发低消耗（相对）的
Query 对整个系统的影响远比低并发高消耗的 Query 大。

其次，如果通过 CPU 方面消耗的比较，原理和上面的完全一样。只要让第一个 Query 稍微节省一
小块资源，就可以让整个系统节省出一大块资源，尤其是在排序，分组这些对 CPU 消耗比较多的操作中
尤其突出。

最后，我们从对整个系统的影响来分析。一个频繁执行的高并发 Query 的危险性比一个低并发的
Query 要大很多。

2. 定位优化对象的性能瓶颈

首先要判断出这个 Query 的瓶颈到底是IO还是CPU。到底是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源。

3. 明确的优化目标

一般来说，我们首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该 Query 相关的数据库对象的各种信息，而且还要了解该 Query 在整个应用系统中所实现的功能。了解了数据库整体状态，我们就能知道数据库所能承受的最大压力，也就清楚了我们能够接受的最悲观情况。把握了该 Query 相关数据库对象的信息，我们就应该知道实现该 Query 的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该 Query 所实现的功能点在整个应用系统中的重要地位，我们可以大概的分析出该Query 可以占用的系统资源比例，而且我们也能够知道该 Query 的效率给客户带来的体验影响到底有多
大。
当我们清楚了这些信息之后，我们基本可以得出该 Query 应该满足的一个性能范围是怎样的，这也
就是我们的优化目标范围，然后就是通过寻找相应的优化手段来解决问题了。如果该 Query 实现的应用
系统功能比较重要，我们就必须让目标更偏向于理想值一些，即使在其他某些方面作出一些让步与牺
牲，比如调整schema设计，调整索引组成等，可能都是需要的。而如果该 Query 所实现的是一些并不
是太关键的功能，那我们可以让目标更偏向悲观值一些，而尽量保证其他更重要的 Query 的性能。这种
时候，即使需要调整商业需求，减少功能实现，也不得不应该作出让步。

4. 从 Explain 入手

Explain只是用来获取一个 Query 在当前状态的数据库中的执行计划，在优化动手之前，我们必须要根据优化目标在自己头脑中有一个清晰的目标执行计划。只有这样，优化的目标才有意义。

一个优秀的SQL 调优人员（或者成为 SQL Performance Tuner），在优化任何一个SQL语句之前，都应该在自己头脑中已经先有一个预定的执行计划，然后通过不断的调整尝试，再借助Explain来验证调整的结果是否满足自己预定的执行计划。对于不符合预期的执行计划需要不断分析Query 的写法和数据库对象的信息，继续调整尝试，直至得到预期的结果。

5. 多使用profile
6. 永远用小结果集驱动大的结果集

无论什么类型的 JOIN 连接时，都将选择小表（小结果）作为驱动表，大表（大结果集）作为被驱动表；如果连接属性上只有一个有索引，将选择有索引的连接属性所在的表为被驱动表。

7. 尽可能在索引中完成排序
8. 只取出自己需要的Columns
9. 仅仅使用最有效的过滤条件
10. 尽可能避免复杂的Join和子查询（复杂查询引起查询阻塞）

11. 避免使用子查询，可以把子查询优化为 Join 操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

二、MySQL Explain工具

1、使用方法

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

2、信息解释

◆ ID： Query Optimizer 所选定的执行计划中查询的序列号；序号越大，越先执行，相同序号时，由上至下。

◆ Select_type：所使用的查询类型，主要有以下这几种查询类型
◇ SIMPLE：除子查询或者UNION之外的其他查询；
◇ DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集；
◇ DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有 SELECT，同样依赖于外部查询的结果集；
◇ PRIMARY：子查询中的最外层查询，注意并不是主键查询；
◇ SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集；
◇ UNCACHEABLE SUBQUERY：结果集无法缓存的子查询；
◇ UNION： UNION语句中第二个SELECT开始的后面所有SELECT，第一个SELECT为PRIMARY；
◇ UNION RESULT： UNION 中的合并结果；
◇ DERIVED：派生表的SELECT, FROM子句的子查询；

◆ Table：显示这一步所访问的数据库中的表的名称，也可能是简称；

◆ Type：告诉我们对表所使用的访问方式，主要包含如下几种类型；
◇ all：全表扫描
◇ index：全索引扫描；
◇ rang：索引范围扫描；
◇ ref： Join语句中被驱动表索引引用查询；
◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge之后再读
取表数据；
◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个
主键或者唯一索引；
◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；
◇ fulltext：
◇ ref_or_null：与 ref 的唯一区别就是，在使用索引引用查询之外再增加一个空值的查询；
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
◇ system：系统表，表中只有一行数据；
◇ null：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system

◆ Possible_keys：该查询可以利用的索引. 如果没有任何索引可以使用，就会显示成null，这一项内容对于优化时候索引的调整非常重要；

◆ Key： MySQL Query Optimizer 从 possible_keys 中所选择使用的索引；

◆ Key_len：被选中使用索引的索引键长度，不损失精确性的情况下，长度越短越好；

◆ Ref：列出是通过常量（ const），还是某个表的某个字段（如果是join）来过滤（通过key）的；

◆ Rows：通过系统收集到的统计信息估算出来的结果集记录条数；

◆Filtered：返回结果行数占需要读到取的行(rows列的值)的百分比，100时说明没有读取多余的数据，即读取效率。

◆ Extra：查询中每一步实现的额外细节信息，主要可能会是以下内容：
◇ Distinct：查找distinct 值，所以当mysql找到了第一条匹配的结果后，将停止该值的查询而转为后面其他值的查询；
◇ Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用使用；
◇ Impossible WHERE noticed after reading const tables： MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果；
◇ No tables： Query 语句中使用 FROM DUAL 或者不包含任何 FROM子句；
◇ Not exists：在某些左连接中 MySQL Query Optimizer 所通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数；
◇ Range checked for each record (index map: N)：通过 MySQL 官方手册的描述，当MySQL Query Optimizer 没有发现好的可以使用的索引的时候，如果发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合， MySQL检查是否可以使用range或index_merge访问方法来索取行。
◇ Select tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段的时候， MySQL Query Optimizer 会通过索引而直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或者MAX（）的时候；
◇ Using filesort：当我们的 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候， MySQL Query Optimizer 不得不选择相应的排序算法来实现。
◇ Using index：所需要的数据只需要在 Index 即可全部获得而不需要再到表中取数据；
◇ Using index for group-by：数据访问和 Using index 一样，所需数据只需要读取索引即可，而当 Query 中使用了 GROUP BY 或者 DISTINCT 子句的时候，如果分组字段也在索引中， Extra中的信息就会是 Using index for group-by；
◇ Using temporary：当 MySQL 在某些操作中必须使用临时表的时候，在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。
◇ Using where：如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息；
◇ Using where with pushed condition：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还需要通过打开 Condition Pushdown 优化功能才可能会被使用。控制参数为 engine_condition_pushdown 。

总结：
• 不会告诉你关于触发器、存储过程的信息或用户自定义函数，对查询的影响情况;
• 不考虑各种Cache;
• 不能显示MySQL在执行查询时所作的优化工作;
• 部分统计信息是估算的，并非精确值;
• 只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

三、MySQL Profile工具

MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条
Query 在整个执行过程中多种资源的消耗情况，如 CPU， IO， IPC， SWAP等，以及发生的 PAGE FAULTS，
CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位
置。下面我们看看 Query Profiler 的具体用法。

1、开启 profiling 参数

root@localhost : (none) 10:53:11> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
通过执行 “ set profiling” 命令，可以开启关闭 Query Profiler 功能。

2、执行 Query

3、获取系统中保存的所有 Query 的 profile概要信息

root@localhost : test 07:47:35> show profiles;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------+
| 1 | 0.00183100 | show databases |
| 2 | 0.00007000 | SELECT DATABASE() |
| 3 | 0.00099300 | desc test |
| 4 | 0.00048800 | show tables |
| 5 | 0.00430400 | desc test_profiling |
| 6 | 1.90115800 | select status,count(*) from test_profiling group by status |
+----------+------------+------------------------------------------------------------+
3 rows in set (0.00 sec)
通过执行 “ SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。

4、针对单个 Query 获取详细的 profile 信息

在获取到概要信息之后，我们就可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中
详细的 profile 信息了，具体操作如下：
root@localhost : test 07:49:24> show profile cpu, block io for query 6;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000349 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000035 | 0.000999 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 1.900619 | 1.030844 | 0.197970 | 347 | 347 |
| Sorting result | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
上面的例子中是获取 CPU 和 Block IO 的消耗，非常清晰，对于定位性能瓶颈非常适用。希望得到
取其他的信息，都可以通过执行 “ SHOW PROFILE *** FOR QUERY n” 来获取。

四、合理设计并利用索引

1、常见的几种索引类型

在MySQL 中，主要有四种类型的索引，分别为： B-Tree 索引， Hash 索引， Fulltext 索引和 RTree 索引。

2、索引的优缺点

使用索引最大益处就是，可以极大地提高查询效率。除此之外，索引还有一个非常重要的用途，那就是降低数据的排序成本。

我们知道，每个索引中索引数据都是按照索引键键值进行排序后存放的。所以，当我们的 Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致， MySQL Query Optimizer就会告诉 mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的 Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。排序分组操作主要消耗的是我们的内存和 CPU 资源，如果我们能够在进行排序分组操作中利用好索引，将会极大的降低 CPU 资源的消耗。

使用索引的弊端：的确，索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。

假设我们在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作， MySQL 都需要在更新表中 Column ca的同时，也更新 Column ca 的索引数据，调整因为更新所带来键值变化后的索引信息。而如果我们没有对Column ca 进行索引的话，MySQL 所需要做的仅仅只是更新表中 Column ca 的信息。这样，所带来的最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外， Column ca 的索引idx_ta_ca 是需要占用存储空间的，而且随着 Table ta 数据量的增长， idx_ta_ca 所占用的空间也会不断增长。所以索引还会带来存储空间资源消耗的增长。

3、创建、修改和删除索引的语法

普通索引INDEX 或 KEY，唯一索引UNIQUE，全文索引FULLTEXT，主键索引 PRIMARY KEY。

（1）创建索引

CREATE TABLE mytable(

id INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username[(length)])

);

CREATE INDEX indexName ON tableName(columnName[(PrefixLength)]);
//带(PrefixLength)的为前缀索引，视索引字段重复程度，适当增加PrefixLength可以提高查询效率

ALTER TABLE tableName ADD INDEX indexName( columnName[(PrefixLength)] )[, algorithm=inplace, lock=none];

（2）删除索引

DROP INDEX [indexName] ON tableName;

ALTER TABLE tableName DROP INDEX indexName;

4、如何判定是否需要创建索引

5、索引使用的限制

五、针对性优化思路

1、JOIN 优化思路

1）尽可能减少 Join 语句中的 Nested Loop 的循环总次数
如何减少 Nested Loop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一“ 永远用小结果集驱动大的结果集” 。
为什么？因为驱动结果集越大，意味着需要循环的次数越多，也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。比如，当两个表（表 A 和表 B） Join 的时候，如果表 A 通过WHERE 条件过滤后有 10 条记录，而表 B 有20条记录。如果我们选择表 A 作为驱动表，也就是被驱动表的结果集为20，那么我们通过 Join 条件对被驱动表（表 B）的比较过滤就会有10次。反
之，如果我们选择表 B 作为驱动表，则需要有 20 次对表 A 的比较过滤。当然，此优化的前提条件是通过 Join 条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要 Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化

2）优先优化Nested Loop 的内层循环
不仅仅是在数据库的 Join 中应该做的，实际上在我们优化程序语言的时候也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。

3）保证 Join 语句中被驱动表上 Join 条件字段已经被索引
保证被驱动表上 Join 条件字段已经被索引的目的，正是针对上面两点的考虑，只有让被驱动表的 Join 条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是优化内层循环的实际优化方法。

4）当无法保证被驱动表的 Join 条件字段被索引，且内存资源充足的前提下，不要太吝惜 JoinBuffer 的设置
当在某些特殊的环境中，我们的 Join 必须是 All， Index， range 或者是 index_merge 类型的时候， Join Buffer就会派上用场了。在这种情况下， Join Buffer 的大小将对整个 Join 语句的消耗起到非常关键的作用。（单位字节）

2、ORDERBY，GROUPBY 和 DISTINCT 优化思路

1）ORDER BY 优化

在 MySQL 中， ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；
◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端；以下为两种排序算法：

1. 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在 SortBuffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；
2. 根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在 Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集，再按照顺序返回给客户端。

当我们无法避免排序操作的时候，我们又该如何来优化呢？很显然，我们应该尽可能让 MySQL 选择使用第二种算法来进行排序。这样可以减少大量的随机IO操作，很大幅度的提高排序工作的效率。

1. 加大 max_length_for_sort_data 参数的设置
在 MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，MySQL 就会选择改进后的排序算法，反之，则选择老式的算法。所以，如果我们有充足的内存让MySQL 存放需要返回的非排序字段的时候，可以加大这个参数的值来让 MySQL 选择使用改进版的排
序算法。
2. 去掉不必要的返回字段
当我们的内存并不是很充裕的时候，我们不能简单的通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，因为如果那样可能会造成 MySQL 不得不将数据分成很多段然后进行排使用序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返回结果长度适应 max_length_for_sort_data 参数的限制。
3. 增大 sort_buffer_size 参数设置
增大 sort_buffer_size 并不是为了让 MySQL 可以选择改进版的排序算法，而是为了让 MySQL可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成 MySQL 不得不使用临时表来进行交换排序。

2）GROUP BY 优化

1. 使用松散（ Loose）索引扫描实现 GROUP BY
何谓松散索引扫描实现 GROUP BY 呢？实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候，并不需要扫描所有满足条件的索引键即可完成操作得出结果。

要想利用到松散索引扫描实现 GROUP BY，需要至少满足以下几个条件：
◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置；
◆ 在使用GROUP BY 的同时，只能使用 MAX 和 MIN 这两个聚合函数；（聚合字段也添加到GROUP BY的索引中）
◆ 如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候，必须以常量形式存在；

create index idx_gid_uid_gc on group_message(group_id,user_id,gmt_create);
->ELECT user_id,max(gmt_create)
-> FROM group_message
-> WHERE group_id < 10
-> GROUP BY group_id,user_id

2. 使用紧凑（ Tight）索引扫描实现 GROUP BY
紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候，读取所有
满足条件的索引键，然后再根据读取恶的数据来完成 GROUP BY 操作得到相应结果。

create index idx_gid_uid_gc on group_message(group_id,user_id,gmt_create);
-> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id = 2 //group_id 补足了索引前缀的搜索关键字
-> GROUP BY user_id

3. 使用临时表实现 GROUP BY
MySQL 在进行 GROUP BY 操作的时候要想利用所有，必须满足 GROUP BY 的字段必须同时存放于同
一个索引中，且该索引是一个有序索引（如 Hash 索引就不能满足要求）。而且，并不只是如此，是否
能够利用索引来实现 GROUP BY 还与使用的聚合函数也有关系.

前面两种 GROUP BY 的实现方式都是在有可以利用的索引的时候使用的，当 MySQL Query
Optimizer 无法找到合适的索引可以利用的时候，就不得不先读取需要的数据，然后通过临时表来完成
GROUP BY 操作。

create index idx_gid_uid_gc on group_message(group_id,user_id,gmt_create);

> SELECT max(gmt_create)
-> FROM group_message
-> WHERE group_id > 1 and group_id < 10 //不是常量，而是一个范围
-> GROUP BY user_id

4. 针对于上面三种 MySQL 处理 GROUP BY 的方式，我们可以针对性的得出如下两种优化思路：

1）尽可能让 MySQL 可以利用索引来完成 GROUP BY 操作，当然最好是松散索引扫描的方式最佳。在系统允许的情况下，我们可以通过调整索引或者调整 Query 这两种方式来达到目的；
2）当无法使用索引完成 GROUP BY 的时候，由于要使用到临时表且需要 filesort，所以我们必须要有足够的 sort_buffer_size 来供 MySQL 排序的时候使用，而且尽量不要进行大结果集的 GROUPBY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据 copy 到磁盘上面再进行操作，这时候的排序分组操作性能将是成数量级的下降。

至于如何利用好这两种思路，还需要大家在自己的实际应用场景中不断的尝试并测试效果，最终才能得到较佳的方案。此外，在优化 GROUP BY 的时候还有一个小技巧可以让我们在有些无法利用到索引的情况下避免 filesort 操作，也就是在整个语句最后添加一个以 null 排序（ ORDER BY null）的子句，来强制对查询的结果禁用排序。

3）DISTINCT 优化

DISTINCT 实际上和 GROUP BY 的操作非常相似，只不过是在 GROUP BY 之后的每组中只取出一条记录而已。但是，和 GROUP BY 有一点差别的是， DISTINCT 并不需要进行排序。也就是说，在仅仅只是 DISTINCT 操作的 Query 如果无法仅仅利用索引完成操作的时候， MySQL 会利用临时表来做一次数据的“ 缓存” ，但是不会对临时表中的数据进行 filesort 操作。当然，如果我们在进行DISTINCT 的时候还使用了 GROUP BY 并进行了分组，并使用了类似于 MAX 之类的聚合函数操作，就无
法避免 filesort 了。

对于 DISTINCT 的优化，和 GROUP BY 基本上一致的思路，关键在于利用好索引，在无法利用索引的时候，确保尽量不要在大结果集上面进行 DISTINCT 操作，磁盘上面的IO操作和内存中的IO操作性能完全不是一个数量级的差距。

（摘自：Mysql性能调优和架构设计）

ppxin

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
MySQL 查询优化之【分析工具、索引建议、优化思路】

目录一、查询优化基本思路和原则1. 优化更需要优化的Query2. 定位优化对象的性能瓶颈3. 明确的优化目标4. 从 Explain 入手5. 多使用profile6. 永远用小结果集驱动大的结果集7. 尽可能在索引中完成排序8. 只取出自己需要的Columns9. 仅仅使用最有效的过滤条件10. 尽可能避免复杂的Join和子查询（复杂查询引起查询阻塞）二、MySQL Explain工具1、使用方法2、信息解释三、MySQL Profile工具1、开启 pro
复制链接

扫一扫