mysql联表查询优化(以下查询方法可借鉴,但一定要按自己的业务来进行相对应的优化)
来源:互联网 发布:网络推广费用预算表 编辑:程序博客网 时间:2024/06/08 05:21
联表是使用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: ALLpossible_keys: group key: NULL key_len: NULL ref: NULL rows: 988054 Extra:*************************** 2. row *************************** id: 1 select_type: SIMPLE table: Group_Privilege type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: test.Person_Group.group_id,const rows: 1 Extra: Using index2 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: indexpossible_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: refpossible_keys: group key: group key_len: 4 ref: test.Group_Privilege.group_id rows: 247293 Extra: Using index2 rows in set (0.00 sec)
虽然先遍历的小表只有338行了,但是之后遍历的大表却是24729行,算一下整个遍历次数不是338×247293=83585034,这个比之前的988054要大的多。这么看来换一下性能变得更差啊。唉,执行看一下怎么样吧。
*************************** 13. row ***************************Query_ID: 1Duration: 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: 2Duration: 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: refpossible_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: refpossible_keys: group key: group key_len: 4 ref: test.Group_Privilege.group_id rows: 247011 Extra: Using index2 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: refpossible_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: refpossible_keys: group key: group key_len: 4 ref: test.Group_Privilege.group_id rows: 247011 Extra: Using index2 rows in set (0.00 sec)
对于内连接sql语句,mysql最初是外层查询Person_Group再内层查询Group_Privilege,但是在我们加上索引之后,就变成外层查询Group_Privilege再内层查询Person_Group了。加了索引之后内连接和右连接的执行效果是一样的了!在这里我不得不感叹真是mysql的优化机制真是琢磨不透。其实对于内连接sql语句,先展开左边表也好,先展开右边表也好,对查询结果是没有影响的,因此mysql会综合各方面因素选择最优的展开顺序。虽然mysql一般能优化的很好,但也不一定每次都是正确的,文中的例子就是最好的证明。所以sql的优化还是一个知识和经验的积累的过程,只有在实际业务上不断实践、分析和优化才能得到最好的结果。
到文章的最后稍微总结一下,虽然mysql自身对于联表有一定的优化,但是不一定靠谱。建议使用的时候挑出一些性能较差的sql查询,结合实际数据进行优化调整,选定联接顺序,建立适当的索引,从而提高查询速度。
- mysql联表查询优化(以下查询方法可借鉴,但一定要按自己的业务来进行相对应的优化)
- mysql优化查询的方法
- MySQL优化查询的方法
- mysql优化查询的方法
- 优化mysql查询的方法
- 优化mysql查询的方法
- mysql查询的优化
- MySQL的查询优化
- mysql 查询的优化
- mysql优化sql语句查询的方法
- MySQL查询优化的5个好用方法
- MySQL查询优化的5个好用方法
- mysql优化sql语句查询的方法
- sql语句查询优化的方法-mysql
- 【MySQL】基于MySQL的SQL优化(三)——对子查询进行优化
- mysql查询优化方法
- MySQL查询优化方法
- MySQL优化分库分表,为什么要分表,分表以后如何进行排序查询,业务如何设计?
- CodeIgniter框架源码学习之输出类--Output.php
- springmvc请求参数获取的几种方法
- 设计模式-观察者模式
- 观察者模式和订阅模式的案例代码
- 每天一个linux命令(59):rcp命令
- mysql联表查询优化(以下查询方法可借鉴,但一定要按自己的业务来进行相对应的优化)
- Java对List<Map<String,Object>>中的参数中文排序 包含特殊符号
- Unity Shader中有关设置Material的优化
- ES6 export和export default的区别
- Tensorflow01-神经网络简介
- 根据前序和中序遍历重建二叉树
- Linux 防火强IP访问控制,开放端口
- 互联网开发常识谱--REST
- EasyUI(五)表单验证