Mysql子查询误区优化

  首先看条MySQL语句,用了子查询,满足了所有的需求,从表中取出merge_id在列表中的列,同时查询对应的分组信息,如果分组是相同的同样也需要查询出来。

SELECT * FROM merge_tbl
WHERE (
    merge_id IN (
        1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
    ) OR
    merge_group IN (
        SELECT merge_group FROM merge_tbl
        WHERE merge_id IN (
            1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
        )
    )
);

  简单的表结构如下,忽略其他字段,其中merge_group关联的是merge_id,用于分组。这里的关系就是自关联吧,如果用INJOIN 也可以实现,但不那么容易理解,但我却选择了一条愚蠢的。

+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| merge_id          | int(11) unsigned      | NO   | PRI | NULL    | auto_increment |
| merge_group       | int(11) unsigned      | YES  | MUL | 0       |                |
+-------------------+-----------------------+------+-----+---------+----------------+
2 rows in set

  对于上面的使用子查询的语句,看下执行时间:0.02246275秒,但数据量呢,才不到4000行,实在有点小吓人,假设这里的数据是10万,估计这条语句得执行个好几秒了,必然这不是我想要的。

  这种查询实际上是可以拆分为两条语句来执行的,很好理解既然已经有了ID列表,那个对应的分组值也很好确定,最后将该这两条语句拆分开来分别执行,看看对比,两种方案完全不在一个数量级,虽然多条SQL效率还是有很大提升,毕竟这都是有索引的。但是用子查询的时候也是有索引的,为什么就那么慢呢?

+----------+------------+------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                |
+----------+------------+------------------------------------------------------------------------------------------------------+
|        1 | 0.00336225 | select merge_group from merge_tbl where merge_id in (1,2,3,4,5,6) GROUP by merge_group               |
|        2 |   0.003234 | SELECT * FROM merge_tbl where (merge_id in (1,2,3,4,5,6)) OR (merge_group in (1,2,3,4,5,6,7,8,9,10)) |
+----------+------------+------------------------------------------------------------------------------------------------------+
2 rows in set

  然后我们来看看查询结果分析,使用Explain命令得出的数据,下面的是针对最开始的带只查询语句,可以看到一些关键性的数据type=ALL,rows=4000,看到这两个就应该明白了,这条语句查询了全表,所有的行,我的索引只是摆设而已。这也就告诉我一个既定的事实,这种语句不是按照我想象中的那样去执行,不会先去执行只查询然后再去执行外部的查询,而是读取一行然后做一次子查询,依次把整张表读完,然后不久就崩溃了。

+----+--------------------+-----------+----------------+---------------------+-------------+---------+------+------+-------------+
| id | select_type        | table     | type           | possible_keys       | key         | key_len | ref  | rows | Extra       |
+----+--------------------+-----------+----------------+---------------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | merge_tbl | ALL            | PRIMARY             | NULL        | NULL    | NULL | 4000 | Using where |
|  2 | DEPENDENT SUBQUERY | merge_tbl | index_subquery | PRIMARY,merge_group | merge_group | 5       | func |    1 | Using where |
+----+--------------------+-----------+----------------+---------------------+-------------+---------+------+------+-------------+
2 rows in set

  下面两条记录是针对单独查询的查询,其实已经没什么好说的了,类型range虽然不太容易理解什么意思,但和ALL对比,还是很浅显的。rows 20和56 也好理解,我只传入了20个值,一次搞定,这也就是为什么差距如此之大的原因了。

+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table     | type        | possible_keys       | key                 | key_len | ref  | rows | Extra                                              |
+----+-------------+---------------------------------+-------+---------------+---------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | merge_tbl | range       | PRIMARY             | PRIMARY             | 4       | NULL |   20 | Using where; Using temporary; Using filesort       |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | merge_tbl | index_merge | PRIMARY,merge_group | PRIMARY,merge_group | 4,5     | NULL |   56 | Using sort_union(PRIMARY,merge_group); Using where |
+----+-------------+-----------+-------------+---------------------+---------------------+---------+------+------+----------------------------------------------------+

  这部分内容呢,一是告诫自己不要再使用子查询这类破语句,效率永远不会高,就和多表查询一样。还有SQL分析器绝对比你笨,你想到的大多数它做不到,适当分析下有好处的。再者如果这里用JOIN语句,应该是可以的,下次试试。

  参考有关Explain命令的解释: http://www.oicto.com/mysql-sql-explain/

当前还没有任何评论

写下你最简单的想法