Mysql中的查询优化

目录

优化思路与原则

MySQL中的Explain详解

MySQL的Profile

Mysql索引

Join语句优化

ORDER BY的实现与优化

GROUP BY的实现与优化

---

优化思路与原则

1. 优化更需要优化的Query
2. 定位优化对象的性能瓶颈，明确优化目标
3. 从Explain入手
4. 多使用Profile
5. 永远使用小结果集驱动大结果集
6. 尽可能在索引中完成排序
7. 只查询自己需要的列
8. 仅仅使用最有效的过滤条件
9. 尽可能避免复杂的Join和子查询

前三条可以理解为优化的基本思路，后面可认为是优化的基本原则

MySQL中的Explain详解

ID：MySQL Query Optimizer选定的执行计划中查询的序列号。
Select_type：所使用的查询类型，主要有以下这几种查询类型。
    DEPENDENT SUBQUERY：子查询内层的第一个SELECT，依赖于外部查询的结果集。
    DEPENDENT UNION：子查询中的UNION，且为UNION中从第二个SELECT开始的后面所有SELECT，同样依赖于外部查询的结果集。
    PRIMARY：子查询中的最外层查询，注意并不是主键查询。
    SIMPLE：除子查询或UNION之外的其他查询。
    SUBQUERY：子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集。
    UNCACHEABLE SUBQUERY：结果集无法缓存的子查询。
    UNION：UNION语句中第二个SELECT开始后面的所有SELECT，第一个SELECT为PRIMARY。
    UNION RESULT：UNION中的合并结果。
Table：显示这一步所访问的数据库中的表的名称。
Type：告诉我们对表使用的访问方式，主要包含如下集中类型。
    all：全表扫描。
    const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
    eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
    fulltext：进行全文索引检索。
    index：全索引扫描。
    index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
    index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
    rang：索引范围扫描。
    ref：Join语句中被驱动表索引引用的查询。
    ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
    system：系统表，表中只有一行数据；
    unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。
Possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null，这项内容对优化索引时的调整非常重要。
Key：MySQL Query Optimizer从possible_keys中所选择使用的索引。
Key_len：被选中使用索引的索引键长度。
Ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）的。
Rows：MySQL Query Optimizer通过系统收集的统计信息估算出来的结果集记录条数。
filtered：使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段。表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
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。

MySQL的Profile

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

（1）通过执行“set profiling”命令，可以开启关闭Query Profiler功能。

root@localhost : (none) 10:53:11> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

（2）执行查询sql。

（3）通过执行 “SHOW PROFILE” 命令获取当前系统中保存的多个Query的profile的概要信息

root@localhost : test 07:47:35> show profiles;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+------------+------------------------------------------------------------+
| 1 | 0.00183100 | show databases
|
| 2 | 1.90115800 | select status,count(*) from test_profiling group by status |
+----------+------------+------------------------------------------------------------+
2 rows in set (0.00 sec)

（4）针对单个 Query 获取详细的 profile 信息。通过执行“SHOW PROFILE *** FOR QUERY n”来获取。

root@localhost : test 07:49:24＞ show profile cpu, block io for query 6;

Mysql索引

是否需要创建索引的判定依据：

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在WHERE子句中的字段不该创建索引

索引的选择：

1.  对于单键索引，尽量选择针对当前Query过滤性更好的索引；
2. 在选择组合索引时，当前Query中过滤性最好的字段在索引字段顺序中排列越靠前越好
3. 在选择组合索引时，尽量选择可以包含当前Query的WHERE子句中更多字段的索引；
4. 尽可能通过分析统计信息和调整Query的写法来达到选择合适索引的目的，减少通过使用Hint人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

MySQL中索引的限制：
在使用索引的同时，还应该了解MySQL中索引存在的限制，以便在索引应用中尽可能地避开限制所带来的问题。

1. MyISAM存储引擎索引键长度的总和不能超过1000字节；
2. BLOB和TEXT类型的列只能创建前缀索引；
3. MySQL目前不支持函数索引；
4. 使用不等于（!= 或者＜＞）的时候，MySQL无法使用索引；
5. 过滤字段使用了函数运算（如abs（column））后，MySQL无法使用索引；
6. Join语句中Join条件字段类型不一致的时候，MySQL无法使用索引；
7. 使用LIKE操作的时候如果条件以通配符开始（如'%abc...'）时，MySQL无法使用索引；
8. 使用非等值查询的时候，MySQL无法使用Hash索引。

Join语句优化

1. 尽可能减少Join语句中Nested Loop的循环总次数
2. 优先优化Nested Loop的内层循环
3. 保证Join语句中被驱动表的Join条件字段已经被索引
4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足时，不要太吝惜Join Buffer的设置

ORDER BY的实现与优化

在MySQL中，ORDER BY的实现有如下两种类型：

• 一种是通过有序索引直接取得有序的数据，这样可不经排序直接得到满足客户端要求的有序数据并返回给客户端；
• 另外一种则须通过MySQL的排序算法将存储引擎中返回的数据进行排序后，再将排序后的数据返回给客户端。

MySQL目前可以通过两种算法来实现数据的排序操作：

• 取出满足过滤条件作为排序条件的字段，以及可以直接定位到行数据的行指针信息，在Sort Buffer中进行实际的排序操作，然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；
• 根据过滤条件一次取出排序字段及客户端请求的所有其他字段的数据，并将不须要排序的字段存放在一块内存区域中，然后在Sort Buffer中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配、合并结果集，再按照顺序返回给客户端。（MySQL 4.1版本才开始增加）

当无法避免排序操作时，应尽量选择第二种算法来排序

1. 加大max_length_for_sort_data参数的设置，当所有返回字段的最大长度小于这个参数值时，MySQL就会选择改进后的排序算法，反之，则选择老式的算法。
2. 去掉不必要的返回字段
3. 增大sort_buffer_size参数设置，增大sort_buffer_size是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段，因为分段会造成MySQL不得不使用临时表来进行交换排序。

GROUP BY的实现与优化

实现方式：

1、使用松散（Loose）索引扫描实现GROUP BY，当MySQL完全利用索引扫描来实现GROUPBY时，并不须要扫描所有满足条件的索引键即可完成操作，得出结果。要利用到松散索引扫描实现GROUP BY，需要至少满足以下几个条件：

• GROUP BY条件字段必须处在同一个索引中最前面的连续位置；
• 在使用GROUP BY的同时，只能使用MAX和MIN这两个聚合函数；
• 如果引用到了该索引中GROUP BY条件之外的字段条件，它就必须以常量形式存在；

2、使用紧凑（Tight）索引扫描实现GROUP BY，读取所有满足条件的索引键，然后再根据读取到的数据来完成GROUP BY操作，以得到相应结果

3、使用临时表实现GROUP BY，当MySQL QueryOptimizer无法找到可以利用的合适索引时，就不得不先读取需要的数据，然后通过临时表来完成GROUP BY操作

优化思路：

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