参考：https://phpmianshi.com/?id=150
背景
我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因，并通过一个较为典型的示例来对我们的分析做出相应的验证。
为什么返回完全相同结果集的不同SQL语句，在执行性能方面存在差异呢？这里我们先从SQL 语句在数据库中执行并获取所需数据这个过程来做一个大概的分析了。
当 MySQL Server 的连接线程接收到 Client 端发送过来的 SQL 请求之后，会经过一系列的分解 Parse，进行相应的分析。然后，MySQL 会通过查询优化器模块（Optimizer）根据该 SQL 所涉及到的数据表的相关统计信息进行计算分析，然后再得出一个 MySQL 认为最合理最优化的数据访问方式，也就是 我们常说的“执行计划”，然后再根据所得到的执行计划通过调用存储引擎接口来获取相应数据。然后 再将存储引擎返回的数据进行相关处理，并以 Client 端所要求的格式作为结果集返回给 Client 端的应用程序。
注：这里所说的统计数据，是我们通过ANALYZE TABLE命令通知MySQL对表的相关数据做分析之后所获得到的一些数据统计量。这些统计数据对 MySQL 优化器而言是非常重要的，优化器所生成的执行计划的好坏，主要就是由这些统计数据所决定的。在数据库管理软件中，最大的性能瓶颈就是在于磁盘 IO，也就是数据的存取操作上 面。而对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量可能 会有天壤之别，所消耗的资源也自然是区别甚大。
下面我们将通过一两个具体的示例来分析写法不一样而功能完全相同的两条 SQL 的在性能方面的差 异。
示例一 需求：取出某个 group（假设 id 为 100）下的用户编号（id），用户昵称（nick_name）并 按 照 加 入 组 的 时 间 （user_group.gmt_create）来进行倒序排列，取出前 20 个。
解决方案一
SELECT id,nick_name FROM user,user_group WHERE user_group.group_id = 100 and user_group.user_id = user.id order by user_group.gmt_create desc limit 20;
解决方案二
SELECT user.id,user.nick_name FROM ( SELECT user_id FROM user_group WHERE user_group.group_id = 100 ORDER BY gmt_create desc limit 20) t,user WHERE t.user_id = user.id;
我们explain先来看看方案一执行计划：
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_group type: ref possible_keys: user_group_uid_gid_ind,user_group_gid_ind key: user_group_gid_ind key_len: 4 ref: const rows: 31156 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: user type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: example.user_group.user_id rows: 1 Extra:
explain看下方案二的执行计划
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 20 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: user type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: t.user_id rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: DERIVED table: user_group type: ref possible_keys: user_group_gid_ind key: user_group_gid_ind key_len: 4 ref: const rows: 31156 Extra: Using filesort执行计划对比分析：
解决方案一中的执行计划显示 MySQL 在对两个参与 Join 的表都利用到了索引，user_group 表利用了 user_group_gid_ind 索 引 （ key: user_group_gid_ind ） ， user 表 利 用 到 了 主 键 索 引 （ key: PRIMARY），在参与 Join 前 MySQL 通过 Where 过滤后的结果集与 user 表进行 Join，最后通过排序取出 Join 后结果返回。
解决方案二的 SQL 语句利用到了子查询，所以执行计划会稍微复杂一些，首先可以看到两个表都和 解决方案 1 一样都利用到了索引（所使用的索引也完全一样），执行计划显示该子查询以 user_group 为 驱动，也就是先通过 user_group 进行过滤并马上进行这一轮的结果集排序，也就取得了 SQL 中的 “limit 20”条结果，然后与 user 表进行 Join，得到相应的数据。这里可能有人会怀疑在自查询中 从user_group表所取得与user表参与 Join的记录条数并不是20条，而是整个group_id=100的所有结果。 那么请大家看看该执行计划中的第一行，该行内容就充分说明了在外层查询中的所有的 20 条记录全部被返回。 通过比较两个解决方案的执行计划，我们可以看到第一种解决方案中需要和user表参与Join的记录数MySQL通过统计数据估算出来是 31156，也就是通过 user_group 表返回的所有满足 group_id=100 的记录数（系统中的实际数据是20000）。而第二种解决方案的执行计划中，user 表参与Join的数据就只有20 条，两者相差很大，通过本节最初的分析，我们认为第二种解决方案应该明显优于第一种解决方案。
下面我们通过对比两个解决方案的SQL实际执行的profile详细信息，来验证我们上面的判断。由于SQL语句执行所消耗的最大两部分资源就是IO和CPU，所以这里为了节约篇幅，仅列出BLOCK IO和 CPU 两项 profile 信息
先打开 profiling 功能，然后分别执行两个解决方案的 SQL 语句：
set profiling = 1; #执行方案一的sql #执行方案二的sql查看系统中的 profile 信息，刚刚执行的两个 SQL 语句的执行 profile 信息已经记录下来了
show profiles\G *************************** 1. row *************************** Query_ID: 1 Duration: 1.02367600 Query: SELECT id,nick_name ... 此处省略 *************************** 2. row *************************** Query_ID: 2 Duration: 0.96327800 Query: SELECT user.id,user.nick_name ... 此处省略 SHOW profile CPU,BLOCK IO io FOR query 1;+--------------------+----------+-----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------+----------+-----------+------------+--------------+---------------+
| (initialization) | 0.000068 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000015 | 0 | 0 | 0 | 0 |
| System lock | 0.000006 | 0 | 0 | 0 | 0 |
| Table lock | 0.000009 | 0 | 0 | 0 | 0 |
| init | 0.000026 | 0 | 0 | 0 | 0 |
| optimizing | 0.000014 | 0 | 0 | 0 | 0 |
| statistics | 0.000068 | 0 | 0 | 0 | 0 |
| preparing | 0.000019 | 0 | 0 | 0 | 0 |
| executing | 0.000004 | 0 | 0 | 0 | 0 |
| Sorting result | 1.03614 | 0.5600349 | 0.428027 | 0 | 15632 |
| Sending data | 0.071047 | 0 | 0.004 | 88 | 0 |
| end | 0.000012 | 0 | 0 | 0 | 0 |
| query end | 0.000006 | 0 | 0 | 0 | 0 |
| freeing items | 0.000012 | 0 | 0 | 0 | 0 |
| closing tables | 0.000007 | 0 | 0 | 0 | 0 |
| logging slow query | 0.000003 | 0 | 0 | 0 | 0 |
+--------------------+----------+-----------+------------+--------------+---------------+
SHOW profile CPU,BLOCK IO io FOR query 2;
+--------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------+----------+----------+------------+--------------+---------------+
| (initialization) | 0.000087 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000018 | 0 | 0 | 0 | 0 |
| System lock | 0.000007 | 0 | 0 | 0 | 0 |
| Table lock | 0.000059 | 0 | 0 | 0 | 0 |
| optimizing | 0.00001 | 0 | 0 | 0 | 0 |
| statistics | 0.000068 | 0 | 0 | 0 | 0 |
| preparing | 0.000017 | 0 | 0 | 0 | 0 |
| executing | 0.000004 | 0 | 0 | 0 | 0 |
| Sorting result | 0.928184 | 0.572035 | 0.352022 | 0 | 32 |
| Sending data | 0.000112 | 0 | 0 | 0 | 0 |
| init | 0.000025 | 0 | 0 | 0 | 0 |
| optimizing | 0.000012 | 0 | 0 | 0 | 0 |
| statistics | 0.000025 | 0 | 0 | 0 | 0 |
| preparing | 0.000013 | 0 | 0 | 0 | 0 |
| executing | 0.000004 | 0 | 0 | 0 | 0 |
| Sending data | 0.000241 | 0 | 0 | 0 | 0 |
| end | 0.000005 | 0 | 0 | 0 | 0 |
| query end | 0.000006 | 0 | 0 | 0 | 0 |
| freeing items | 0.000015 | 0 | 0 | 0 | 0 |
| closing tables | 0.000004 | 0 | 0 | 0 | 0 |
| removing tmp table | 0.000019 | 0 | 0 | 0 | 0 |
| closing tables | 0.000005 | 0 | 0 | 0 | 0 |
| logging slow query | 0.000004 | 0 | 0 | 0 | 0 |
+--------------------+----------+----------+------------+--------------+---------------+
我们先看看两条 SQL 执行中的 IO 消耗，两者区别就在于“Sorting result”，我们回 顾一下前面执行计划的对比，两个解决方案的排序过滤数据的时机不一样，排序后需要取 得的数据量一个是 20000，一个是 20，正好和这里的 profile 信息吻合，第一种解决方案的 “Sorting result”的 IO 值是第二种解决方案的将近 500 倍。
然后再来看看 CPU 消耗，所有消耗中，消耗最大的也是“Sorting result”这一项，第 一个消耗多出的缘由和上面 IO 消耗差异是一样的。
通过上面两条功能完全相同的 SQL 语句的执行计划分析，以及通过实际执行后的 profile 数据的验证，都证明了第二种解决方案优于第一种解决方案。同时通过后者的实际 验证，也再次证明了我们前面所做的执行计划基本决定了 SQL 语句性能。
在整个系统的性能优化中，可以得出大概如下的数据：
需求和架构及业务实现优化：55%
Query 语句的优化：30%
数据库自身的优化：15%
不管DBA对数据库多么了解，对Query语句的优化多么精通，最终还是很难解决整个系统的性能问题。原因就在于并没有真正找到根本的症结所在。 所以，数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。
简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：商业需求合理化， 系统架构最优化，逻辑实现精简化，硬件设施理性化。
