关于join的一点疑问
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| 1 | SIMPLE | b | ref | YLID | YLID | 4 | const | 679 | using where;Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | b.ID | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
a表主键是ID,索引比较多,但跟这个语句有关的只有HitNum2(HitNum).有28000行。表大小为14M。
b表无主键,只有一个索引:YLID(YLID),有170000行。表大小只有1.3M,同时只有两个字段ID和YLID。
刚开始很奇怪, 简朝阳的博客里说,MySQL 在进行排序的时候,只有当返回的数据和排序条件不在同一个表中的时候,才需要使用到临时表。我也一直这样认为,但这条语句显示
跟这个结论不一样。
而且,很奇怪,b表的操作在a表前面。找了一下资料,根据这个文章http://database.**.com/art/201011/234443.htm的观点,因为WHERE部分,查询条件是按照b表的字段来进
行筛选的,且b表刚好存在合适的索引,所以在查询时把b表作为主表更有利于缩小结果集。同时又是以a表的字段来排序,所以会造成无法使用索引。这个论点是否正确?
我在b表新建了一个复合索引ID(ID,YLID)。
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| 1 | SIMPLE | a | index | NULL | HitNum2 | 4 | NULL | 3 | |
| 1 | SIMPLE | b | ref | ID,YLID | ID | 8 | a.ID,const | 1 | Using where;Using index |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
结果出乎我意料的好,但为什么会出现这种结果却不是很明白。而且我发现执行时是以a为主表的,跟上面的那个结论相反。
现在有点乱,搞晕了,希望高手能解惑下。
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| 1 | SIMPLE | b | ref | YLID | YLID | 4 | const | 679 | using where;Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | b.ID | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
a表主键是ID,索引比较多,但跟这个语句有关的只有HitNum2(HitNum).有28000行。表大小为14M。
b表无主键,只有一个索引:YLID(YLID),有170000行。表大小只有1.3M,同时只有两个字段ID和YLID。
刚开始很奇怪, 简朝阳的博客里说,MySQL 在进行排序的时候,只有当返回的数据和排序条件不在同一个表中的时候,才需要使用到临时表。我也一直这样认为,但这条语句显示
跟这个结论不一样。
而且,很奇怪,b表的操作在a表前面。找了一下资料,根据这个文章http://database.**.com/art/201011/234443.htm的观点,因为WHERE部分,查询条件是按照b表的字段来进
行筛选的,且b表刚好存在合适的索引,所以在查询时把b表作为主表更有利于缩小结果集。同时又是以a表的字段来排序,所以会造成无法使用索引。这个论点是否正确?
我在b表新建了一个复合索引ID(ID,YLID)。
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| 1 | SIMPLE | a | index | NULL | HitNum2 | 4 | NULL | 3 | |
| 1 | SIMPLE | b | ref | ID,YLID | ID | 8 | a.ID,const | 1 | Using where;Using index |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
结果出乎我意料的好,但为什么会出现这种结果却不是很明白。而且我发现执行时是以a为主表的,跟上面的那个结论相反。
现在有点乱,搞晕了,希望高手能解惑下。
作者: wanan_YLF 发布时间: 2011-05-13
QUOTE:原帖由 wanan_YLF 于 2011-5-13 11:22 发表
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| 1 | SIMPLE | b | ref | YLID | YLID | 4 | const | 679 | using where;Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | b.ID | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
a表主键是ID,索引比较多,但跟这个语句有关的只有HitNum2(HitNum).有28000行。表大小为14M。
b表无主键,只有一个索引:YLID(YLID),有170000行。表大小只有1.3M,同时只有两个字段ID和YLID。
刚开始很奇怪, 简朝阳的博客里说,MySQL 在进行排序的时候,只有当返回的数据和排序条件不在同一个表中的时候,才需要使用到临时表。我也一直这样认为,但这条语句显示
跟这个结论不一样。
而且,很奇怪,b表的操作在a表前面。找了一下资料,根据这个文章http://database.**.com/art/201011/234443.htm的观点,因为WHERE部分,查询条件是按照b表的字段来进
行筛选的,且b表刚好存在合适的索引,所以在查询时把b表作为主表更有利于缩小结果集。同时又是以a表的字段来排序,所以会造成无法使用索引。这个论点是否正确?
我在b表新建了一个复合索引ID(ID,YLID)。
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| 1 | SIMPLE | a | index | NULL | HitNum2 | 4 | NULL | 3 | |
| 1 | SIMPLE | b | ref | ID,YLID | ID | 8 | a.ID,const | 1 | Using where;Using index |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
结果出乎我意料的好,但为什么会出现这种结果却不是很明白。而且我发现执行时是以a为主表的,跟上面的那个结论相反。
现在有点乱,搞晕了,希望高手能解惑下。
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
| 1 | SIMPLE | b | ref | YLID | YLID | 4 | const | 679 | using where;Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | b.ID | 1 | |
+----+-------------+-------+------+---------------+------------+---------+-----------------------+------+---------------------------------------------+
a表主键是ID,索引比较多,但跟这个语句有关的只有HitNum2(HitNum).有28000行。表大小为14M。
b表无主键,只有一个索引:YLID(YLID),有170000行。表大小只有1.3M,同时只有两个字段ID和YLID。
刚开始很奇怪, 简朝阳的博客里说,MySQL 在进行排序的时候,只有当返回的数据和排序条件不在同一个表中的时候,才需要使用到临时表。我也一直这样认为,但这条语句显示
跟这个结论不一样。
而且,很奇怪,b表的操作在a表前面。找了一下资料,根据这个文章http://database.**.com/art/201011/234443.htm的观点,因为WHERE部分,查询条件是按照b表的字段来进
行筛选的,且b表刚好存在合适的索引,所以在查询时把b表作为主表更有利于缩小结果集。同时又是以a表的字段来排序,所以会造成无法使用索引。这个论点是否正确?
我在b表新建了一个复合索引ID(ID,YLID)。
mysql> explain select a.* from a left join b on (a.ID=b.ID) where b.YLID=1050334 ORDER BY a.HitNum desc LIMIT 0,3;
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
| 1 | SIMPLE | a | index | NULL | HitNum2 | 4 | NULL | 3 | |
| 1 | SIMPLE | b | ref | ID,YLID | ID | 8 | a.ID,const | 1 | Using where;Using index |
+----+-------------+-------+-------+---------------------+----------+---------+-----------------------------+------+-------------------------+
结果出乎我意料的好,但为什么会出现这种结果却不是很明白。而且我发现执行时是以a为主表的,跟上面的那个结论相反。
现在有点乱,搞晕了,希望高手能解惑下。
先看此文:
http://www.mysqlops.com/2011/03/ ... ght-inner-join.html
纠正你的非LEFT JOIN 写法,然后就好纠正执行计划了....
作者: jinguanding 发布时间: 2011-05-13
这些并非我写的,我是宁愿多建一张表也不会想去进行join的,只是我早上监控的时候发现创建临时表的SQL很多,created_tmp_tables的值增长很快,于是只能去尝试优化一下。优化更多的是考虑索引的优化,因为改不改代码不是我说的算,而且现在机子还跑得正常。而这个语句暴露出来的并非只是SQL语句问题,更重要的还是当初他们建表的时候就没有做好。现在要去改动表几乎是不可能的,所以我只能从索引入手了。
从需要得到查询的结果看,这句根本不需要进行left join,而是进行inner join。
[ 本帖最后由 wanan_YLF 于 2011-5-13 12:00 编辑 ]
从需要得到查询的结果看,这句根本不需要进行left join,而是进行inner join。
[ 本帖最后由 wanan_YLF 于 2011-5-13 12:00 编辑 ]
作者: wanan_YLF 发布时间: 2011-05-13
从需要得到查询的结果看,这句根本不需要进行left join,而是进行inner join。
给你链接地址 就是让你意思到此......而且写了LEFT JOIN 会影响执行计划选择的...由于A表是LEFT JOIN左边的,且链接字段为主键...
给你链接地址 就是让你意思到此......而且写了LEFT JOIN 会影响执行计划选择的...由于A表是LEFT JOIN左边的,且链接字段为主键...
作者: jinguanding 发布时间: 2011-05-13