产品解决方案安全专区定价 WeTest生态支持与服务关于WeTest

一次对于mysql联表操作的优化

开发测试干货 2016-05-17 10:55 17976

联表是使用mysql中不可避免的操作，相信很多同学在使用联表操作的时候心里难免总有顾虑。

联表是使用mysql中不可避免的操作，相信很多同学在使用联表操作的时候心里难免总有顾虑。到底联表性能消耗怎么样？联表索引应该如果建立？分次查询好还是联表查询好？关于这些疑惑，我从一个实际的联表查询sql调优的案例来说明。

//用户和组多对一关系表
CREATE TABLE `Person_Group` (
  `person_id` int(11) unsigned NOT NULL COMMENT '用户id',
  `group_id` int(11) unsigned NOT NULL COMMENT '组id',
  `extend` varchar(1000) DEFAULT '[]' COMMENT '额外权限',
  PRIMARY KEY (`person_id`),
  KEY `person_group` (`person_id`,`group_id`),
  KEY `group` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

//组和权限多对多关系表
CREATE TABLE `Group_Privilege` (
  `group_id` int(11) unsigned NOT NULL COMMENT '组id',
  `privilege_id` int(11) unsigned NOT NULL COMMENT '权限',
  PRIMARY KEY (`group_id`,`privilege_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

//待优化语句
SELECT Person_Group.person_id FROM Person_Group JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008；

可以看出sql语句执行的结果是获取具有20008权限的所有用户id。这个sql很简单，但执行起来需要29毫秒，的确存在性能问题，需要优化。我们先explain看一下这个联表sql是如何被执行的。

EXPLAIN SELECT Person_Group.person_id FROM Person_Group JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person_Group
         type: ALL
possible_keys: group
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 988054
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Group_Privilege
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.Person_Group.group_id,const
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

从explain的结果看出造成联表查询慢的原因是遍历了Person_Group。Person_Group是一张近100万行的大表，Group_Privilege是一张300多行的小表。这就是典型的大表连小表的联表查询，而mysql如果在查询过程中遍历了大表，的确会严重的影响性能。大表联小表在日常写sql的时候也挺常见的，一般优化sql的方法是加索引，从而避免对大表的全遍历。但是这个语句的where限制的Group_Privilege的privilege_id，如果对privilege_id加索引，只是减少了对小表Group_Privilege的遍历次数，并不能减少对Person_Group的遍历。于是优化就陷入了僵局。

和有经验的同事讨论了一下，同事建议换一下大表和小表联表的顺序，试试看效果怎么样。由于Person_Group和Group_Privilege表都不存在group_id是NULL的情况，不管怎么联结果都是一样的，再加上之前也听说过小表联大表的查询速度要比大表联小表快，死马当作活马医，先试试再说。于是查询语句改成了下面的样子。

SELECT Person_Group.person_id FROM  Person_Group Right JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008；

explain一下看一下是如何执行的。

explain SELECT Person_Group.person_id FROM  Person_Group Right JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Group_Privilege
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 338
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person_Group
         type: ref
possible_keys: group
          key: group
      key_len: 4
          ref: test.Group_Privilege.group_id
         rows: 247293
        Extra: Using index
2 rows in set (0.00 sec)

虽然先遍历的小表只有338行了，但是之后遍历的大表却是24729行，算一下整个遍历次数不是338×247293=83585034，这个比之前的988054要大的多。这么看来换一下性能变得更差啊。唉，执行看一下怎么样吧。

*************************** 13. row ***************************
Query_ID: 1
Duration: 0.02994200
   Query: SELECT Person_Group.person_id FROM  Person_Group  JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008
*************************** 14. row ***************************
Query_ID: 2
Duration: 0.00039700
   Query: SELECT Person_Group.person_id FROM Person_Group RIGHT JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008

一看结果真的傻眼了，小表联大表竟然只要0.39毫秒，快了70多倍，这完全不科学！冷静下来赶紧翻书找原因。按照《高性能Mysql》的说法，Mysql对应关联都执行嵌套循环分解操作，即先从第一个表中循环取出数据，再将取出的数据放到下一个表中去寻找匹配的数据，直到找到所有行为止。对于我们之前的内连接查询，mysql会做如下处理。

   outer_iter = iterator_over Person_Group  
　 outer_row = outer_iter.next
　 while outer_row      #此处循环了988054次
　　　　inner_iter = iterator over Group_Privilege where group_id=outer_row.group_id  and privilege_id=20008   #此处直接命中联合主键（group_id 和 privilege_id ）
　　　　inner_row = inner_iter.next
　　　　while inner_row
　　　　　　output[outer_row.person_id]
　　　　　　inner_row = inner_iter.next
　　　　end
　　　　out_row = outer_iter.next
　 end

而一旦换成右连接的顺序，mysql的执行伪代码就变成了：

   outer_iter = iterator_over Group_Privilege  where privilege_id=20008
　 outer_row = outer_iter.next
　 while outer_row      #由于没有privilege_id索引，此处循环了338次
　　　　inner_iter = iterator over Person_Group where group_id=outer_row.group_id  #此处直接命中了Person_Group的索引group_id
　　　　inner_row = inner_iter.next
       if inner_row
　　　　    while inner_row
　　　　　　    output[inner_row.person_id]
　　　　　　    inner_row = inner_iter.next
　　　　    end
       else
          output[NULL] 
　　　 out_row = outer_iter.next
　 end

从mysql执行伪代码的过程中我们可以看出关键问题的所在。之前的查询傻傻的先遍历9万多行的Person_Group，而在内层的Group_Privilege的查询上，就直接1次命中了。最终这次查询循环了100万多次。而换了顺序之后，mysql一开始只需要遍历338行的Group_Privilege，然后在内层的Person_Group的查询上，直接用了Person_Group的group_id的索引！这样在内层查询上就很快了，根本不会全遍历Person_Group。这就是小表联大表比大表联小表快的多的原因。可见优化多表查询的关键还是在于是否正确的使用了索引。对于同样的表结构，更换了外内表的查询顺序，虽然看起来没有多大的差别，但是会改变查询使用的索引从而产生巨大的性能差距。

这里还有一个问题，为什么对小表联大表explain内层查询的行数247293呢？用了索引不应该这么多行？其实我也不清楚这个247293是怎么算出来的，虽然在内层查询上mysql知道会用到group_id上的索引，但是由于explain并不执行sql，因此在内层查询是不知道具体的group_id的值（这个是外层遍历的结果），所以mysql不知道索引命中的行数数多少，进而无法准确判断会遍历多少行。也就是说关于内层的遍历行数，explain很有可能是不准确的。

既然简单的加个right就能提升这么多的性能，那我们再接再厉继续优化。很明显在右连接查询在外层没有privilege_id索引，因此遍历了全部的Group_Privilege。虽然行数不多但是我们还是要优化一下的。于是我们加上索引，explain一下，发现外层只需要遍历3行就可以了。

EXPLAIN SELECT Person_Group.person_id FROM Person_Group RIGHT JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Group_Privilege
         type: ref
possible_keys: privilege
          key: privilege
      key_len: 4
          ref: const
         rows: 3
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person_Group
         type: ref
possible_keys: group
          key: group
      key_len: 4
          ref: test.Group_Privilege.group_id
         rows: 247011
        Extra: Using index
2 rows in set (0.00 sec)

这个语句优化到这里应该感觉差不多可以结束，可是当我无聊的explain一下最初的内连接sql的时候震惊的发现，一切都不一样了！

explain select Person_Group.person_id FROM Person_Group JOIN Group_Privilege ON Person_Group.group_id=Group_Privilege.group_id WHERE Group_Privilege.privilege_id=20008\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Group_Privilege
         type: ref
possible_keys: PRIMARY,privilege
          key: privilege
      key_len: 4
          ref: const
         rows: 3
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Person_Group
         type: ref
possible_keys: group
          key: group
      key_len: 4
          ref: test.Group_Privilege.group_id
         rows: 247011
        Extra: Using index
2 rows in set (0.00 sec)

对于内连接sql语句，mysql最初是外层查询Person_Group再内层查询Group_Privilege，但是在我们加上索引之后，就变成外层查询Group_Privilege再内层查询Person_Group了。加了索引之后内连接和右连接的执行效果是一样的了！在这里我不得不感叹真是mysql的优化机制真是琢磨不透。其实对于内连接sql语句，先展开左边表也好，先展开右边表也好，对查询结果是没有影响的，因此mysql会综合各方面因素选择最优的展开顺序。虽然mysql一般能优化的很好，但也不一定每次都是正确的，文中的例子就是最好的证明。所以sql的优化还是一个知识和经验的积累的过程，只有在实际业务上不断实践、分析和优化才能得到最好的结果。

到文章的最后稍微总结一下，虽然mysql自身对于联表有一定的优化，但是不一定靠谱。建议使用的时候挑出一些性能较差的sql查询，结合实际数据进行优化调整，选定联接顺序，建立适当的索引，从而提高查询速度。