我们重点分析实现同样功能的不同SQL 语句在性能方面会产生较大的差异的根本原因，并通过一个较为典型的示例来对我们的分析做出相应的验证。
为什么返回完全相同结果集的不同SQL 语句，在执行性能方面存在差异呢？这里我们先从SQL 语句在数据库中执行并获取所需数据这个过程来做一个大概的分析了。
当MySQL Server 的连接线程接收到Client 端发送过来的SQL 请求之后，会经过一系列的分解Parse，进行相应的分析。然后，MySQL 会通过查询优化器模块（Optimizer）根据该SQL 所设涉及到的数据表的相关统计信息进行计算分析，然后再得出一个MySQL 认为最合理最优化的数据访问方式，也就是我们常说的“执行计划”，然后再根据所得到的执行计划通过调用存储引擎借口来获取相应数据。然后再将存储引擎返回的数据进行相关处理，并以Client 端所要求的格式作为结果集返回给Client 端的应用程序。 注：这里所说的统计数据，是我们通过ANALYZE TABLE 命令通知MySQL 对表的相关数据做分析之后所获得到的一些数据统计量。这些统计数据对MySQL 优化器而言是非常重要的，优化器所生成的执行计划的好坏，主要就是由这些统计数据所决定的。实际上，在其他一些数据库管理软件中也有类似相应的统计数据。
我们都知道，在数据库管理软件中，最大的性能瓶颈就是在于磁盘IO，也就是数据的存取操作上面。而对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量可能会有天壤之别，所消耗的资源也自然是区别甚大。所以，当我们需要从数据库中查询某个数据的时候，所消耗资源的多少主要就取决于数据库以一个什么样的数据读取方式来完成我们的查询请求，也就是取决于SQL 语句的执行计划。
对于唯一一个SQL 语句来说，经过MySQL Parse 之后分解的结构都是固定的，只要统计信息稳定，其执行计划基本上都是比较固定的。而不同写法的SQL 语句，经过MySQL Parse 之后分解的结构结构就可能完全不同，即使优化器使用完全一样的统计信息来进行优化，最后所得出的执行计划也可能完全不一样。而执行计划又是决定一个SQL 语句最终的资源消耗量的主要因素。所以，实现功能完全一样的SQL 语句，在性能上面可能会有差别巨大的性能消耗。当然，如果功能一样，而且经过MySQL 的优化器优化之后的执行计划也完全一致的不同SQL 语句在资源消耗方面可能就相差很小了。当然这里所指的消耗主要是IO 资源的消耗，并不包括CPU 的消耗。
下面我们将通过一两个具体的示例来分析写法不一样而功能完全相同的两条SQL 的在性能方面的差异。
示例一需求：取出某个group（假设id 为100）下的用户编号（id），用户昵称（nick_name）、用户性别（ sexuality ） 、用户签名（ sign ） 和用户生日（ birthday ） ， 并按照加入组的时间（user_group.gmt_create）来进行倒序排列，取出前20 个。
解决方案一、SELECT id,nick_nameFROM user,user_groupWHERE user_group.group_id = 1and user_group.user_id = user.idlimit 100,20;解决方案二、SELECT user.id,user.nick_nameFROM (SELECT user_idFROM user_groupWHERE user_group.group_id = 1ORDER BY gmt_create desclimit 100,20) t,userWHERE t.user_id = user.id;
我们先来看看执行计划：sky@localhost : example 10:32:13> explain-> SELECT id,nick_name-> FROM user,user_group-> WHERE user_group.group_id = 1-> and user_group.user_id = user.id-> ORDER BY user_group.gmt_create desc-> limit 100,20\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user_grouptype: refpossible_keys: user_group_uid_gid_ind,user_group_gid_indkey: user_group_gid_indkey_len: 4ref: constrows: 31156Extra: Using where; Using filesort*************************** 2. row ***************************id: 1select_type: SIMPLEtable: usertype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: example.user_group.user_idrows: 1Extra:sky@localhost : example 10:32:20> explain-> SELECT user.id,user.nick_name-> FROM (-> SELECT user_id-> FROM user_group-> WHERE user_group.group_id = 1-> ORDER BY gmt_create desc-> limit 100,20) t,user-> WHERE t.user_id = user.id\G*************************** 1. row ***************************id: 1select_type: PRIMARYtable: <derived2>type: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 20Extra:*************************** 2. row ***************************id: 1select_type: PRIMARYtable: usertype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: t.user_idrows: 1Extra:*************************** 3. row ***************************id: 2select_type: DERIVEDtable: user_grouptype: refpossible_keys: user_group_gid_indkey: user_group_gid_indkey_len: 4ref: constrows: 31156Extra: Using filesort
执行计划对比分析： 解决方案一中的执行计划显示MySQL 在对两个参与Join 的表都利用到了索引，user_group 表利用了user_group_gid_ind 索引（ key: user_group_gid_ind ） ， user 表利用到了主键索引（ key:PRIMARY），在参与Join 前MySQL 通过Where 过滤后的结果集与user 表进行Join，最后通过排序取出Join 后结果的“limit 100,20”条结果返回。解决方案二的SQL 语句利用到了子查询，所以执行计划会稍微复杂一些，首先可以看到两个表都和解决方案1 一样都利用到了索引（所使用的索引也完全一样），执行计划显示该子查询以user_group 为驱动，也就是先通过user_group 进行过滤并马上进行这一论的结果集排序，也就取得了SQL 中的“limit 100,20”条结果，然后与user 表进行Join，得到相应的数据。这里可能有人会怀疑在自查询中从user_group表所取得与user 表参与Join的记录条数并不是20 条，而是整个group_id=1 的所有结果。那么清大家看看该执行计划中的第一行，该行内容就充分说明了在外层查询中的所有的20 条记录全部被返回。 通过比较两个解决方案的执行计划，我们可以看到第一中解决方案中需要和user 表参与Join 的记录数MySQL 通过统计数据估算出来是31156，也就是通过user_group 表返回的所有满足group_id=1 的记录数（系统中的实际数据是20000）。而第二种解决方案的执行计划中，user 表参与Join 的数据就只有20条，两者相差很大，通过本节最初的分析，我们认为第二中解决方案应该明显优于第一种解决方案
下面我们通过对比两个解决觉方案的SQL 实际执行的profile 详细信息，来验证我们上面的判断。由于SQL 语句执行所消耗的最大两部分资源就是IO和CPU，所以这里为了节约篇幅，仅列出BLOCK IO 和CPU两项profile 信息（Query Profiler 的详细介绍将在后面章节中独立介绍）： 先打开profiling 功能，然后分别执行两个解决方案的SQL 语句：
sky@localhost : example 10:46:43> set profiling = 1;Query OK, 0 rows affected (0.00 sec)sky@localhost : example 10:46:50> SELECT id,nick_name-> FROM user,user_group-> WHERE user_group.group_id = 1-> and user_group.user_id = user.id-> ORDER BY user_group.gmt_create desc-> limit 100,20;+--------+-----------+| id | nick_name |+--------+-----------+| 990101 | 990101 || 990102 | 990102 || 990103 | 990103 || 990104 | 990104 || 990105 | 990105 || 990106 | 990106 || 990107 | 990107 || 990108 | 990108 || 990109 | 990109 || 990110 | 990110 || 990111 | 990111 || 990112 | 990112 || 990113 | 990113 || 990114 | 990114 || 990115 | 990115 || 990116 | 990116 || 990117 | 990117 || 990118 | 990118 || 990119 | 990119 || 990120 | 990120 |+--------+-----------+20 rows in set (1.02 sec)sky@localhost : example 10:46:58> SELECT user.id,user.nick_name-> FROM (-> SELECT user_id-> FROM user_group-> WHERE user_group.group_id = 1-> ORDER BY gmt_create desc-> limit 100,20) t,user-> WHERE t.user_id = user.id;+--------+-----------+| id | nick_name |+--------+-----------+| 990101 | 990101 || 990102 | 990102 || 990103 | 990103 || 990104 | 990104 || 990105 | 990105 || 990106 | 990106 || 990107 | 990107 || 990108 | 990108 || 990109 | 990109 || 990110 | 990110 || 990111 | 990111 || 990112 | 990112 || 990113 | 990113 || 990114 | 990114 || 990115 | 990115 || 990116 | 990116 || 990117 | 990117 || 990118 | 990118 || 990119 | 990119 || 990120 | 990120 |+--------+-----------+20 rows in set (0.96 sec)查看系统中的profile 信息，刚刚执行的两个SQL 语句的执行profile 信息已经记录下来了：
sky@localhost : example 10:47:07> show profiles\G*************************** 1. row ***************************Query_ID: 1Duration: 1.02367600Query: SELECT id,nick_nameFROM user,user_groupWHERE user_group.group_id = 1and user_group.user_id = user.idORDER BY user_group.gmt_create desclimit 100,20*************************** 2. row ***************************Query_ID: 2Duration: 0.96327800Query: SELECT user.id,user.nick_nameFROM (SELECT user_idFROM user_groupWHERE user_group.group_id = 1ORDER BY gmt_create desclimit 100,20) t,userWHERE t.user_id = user.id2 rows in set (0.00 sec)sky@localhost : example 10:47:34> 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 |+--------------------+----------+-----------+------------+--------------+---------------+16 rows in set (0.00 sec)
sky@localhost : example 10:47:40> 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 语句性能。
转载于:https://www.cnblogs.com/isoftware/p/3707660.html
相关资源：MySQL性能调优与架构设计 中文版