mysql_query优化之explain&Profiling详解-蒲公英云

mysql_query优化之explain&Profiling详解

1、explain

要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。 MySQL本身的功能架构分为三个部分，分别是应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。

应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。
逻辑层，主要负责查询处理、事务管理等其他数据库功能处理

以查询为例。
首先接收到查询SQL之后，数据库会立即分配一个线程对其进行处理，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划，然后交由计划执行器来执行。
计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

物理层，实际物理磁盘上存储的文件，主要有分文数据文件，日志文件。

通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方便我们进行查看以及为作为性能分析的依据。

在这里插入图片描述

1.1、 id列

id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。

id相同时，优先级由上而下。

1.2、 select_type列

select_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION
RESULT联合临时表结果等。

1.3、 table列

table表示SQL语句查询的表名、表别名、临时表名。

1.4、 partitions列

partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。

1.5、type列

type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。具体有以下值，性能由好到差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL
system
当表中只有一行记录，也就是系统表，是 const 类型的特列。

const
表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。

eq_ref
表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。

ref
表示使用非唯一性索引进行等值查询。

ref_or_null
表示使用非唯一性索引进行等值查询，并且包含了null值的行。

index_merge
表示用到索引合并的优化逻辑，即用到的多个索引。

range
表示用到了索引范围查询。

index
表示使用索引进行全表扫描。

ALL
表示全表扫描，性能最差。

1.6、possible_keys列

表示可能用到的索引列，实际查询并不一定能用到。

1.7、 key列

表示实际查询用到索引列。

1.8、key_len列

表示索引所占的字节数。

每种类型所占的字节数如下：

1.9、 ref列

表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。如果没用到索引，则显示为NULL。

1.10. rows列

表示执行SQL语句所扫描的行数。

1.11. filtered列

表示按条件过滤的表行的百分比。

用来估算与其他表连接时扫描的行数，row x filtered = 252004 x 10% = 25万行

1.12、Extra列

表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。
Using where
表示使用了where条件搜索，但没有使用索引。

Using index
表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。

Using filesort
表示使用了外部排序，即排序字段没有用到索引。

Using temporary
表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。

Using join buffer
表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。
下面的示例中user_id在两张表中都没有建索引。

Using index condition
表示用到索引下推的优化特性。

2、使用Profiling进行query优化

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

query优化的其他技巧
索引设计依赖谓词表达式（条件表达式或者真值表达式——where后面的语句）
尽量避免Join
数据适当的冗余