大家帮看看这条语句怎么优化 --急急急急急

Question

   大家帮看看这条语句怎么优化     
select g from Game g left outer join game_file as f on g.id = g.game_id where g.pub = true
         and g.type = ? and g.category.id = ? 
         and ((g.type = ? and (f.model.id =? or f.series.id in ( ? )))  and f.channel = ?; game表  数据量5000条左右
game    CREATE TABLE `game` (                                                                     
          `id` bigint(20) NOT NULL auto_increment,                                                
          `chinese` bit(1) NOT NULL,                                                              
          `create_date` datetime NOT NULL,                                                        
          `detail` text,                                                                          
          `game_file_count` int(11) default NULL,                                                 
          `hide_key` varchar(255) default NULL,                                                   
          `mobile` text,                                                                          
          `modify_date` datetime NOT NULL,                                                        
          `name` varchar(100) NOT NULL,                                                           
          `origin` smallint(6) NOT NULL,                                                          
          `bargain` smallint(6) NOT NULL,                                                         
          `out_link` varchar(255) default NULL,                                                   
          `picture_count` int(11) default NULL,                                                   
          `price` float NOT NULL,                                                                 
          `public` bit(1) NOT NULL,                                                               
          `relative_key` varchar(255) default NULL,                                               
          `tag` varchar(255) default NULL,                                                        
          `type` smallint(6) NOT NULL,                                                            
          `category_id` int(11) default NULL,                                                     
          `channel` varchar(4000) NOT NULL default ' -1 ',                                        
          PRIMARY KEY  (`id`),                                                                    
          KEY `FK304BF2DBFB06DB` (`category_id`),                                                 
          KEY `idx_type` (`type`),                                                                
          KEY `idx_origin` (`origin`),                                                            
          KEY `id_index` (`id`),                                                                  
          CONSTRAINT `FK304BF2DBFB06DB` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)  
        ) ENGINE=InnoDB AUTO_INCREMENT=1994 DEFAULT CHARSET=utf8      
gamefile表  数据量8万左右
game_file  CREATE TABLE `game_file` (                                                               
             `id` bigint(20) NOT NULL auto_increment,                                               
             `create_date` datetime NOT NULL,                                                       
             `file_size` int(11) default NULL,                                                      
             `jad_file_name` varchar(255) default NULL,                                             
             `jar_file_name` varchar(255) default NULL,                                             
             `match_type` int(11) default NULL,                                                     
             `out_link` varchar(255) default NULL,                                                  
             `game_id` bigint(20) NOT NULL,                                                         
             `model_id` bigint(20) default NULL,                                                    
             `series_id` bigint(20) default NULL,                                                   
             `channel` int(4) NOT NULL default '-1',                                                
             PRIMARY KEY  (`id`),                                                                   
             KEY `FK3BA609A9814B4ADB` (`game_id`),                                                  
             KEY `FK3BA609A912BE6C7B` (`series_id`),                                                
             KEY `FK3BA609A9ED014AD9` (`model_id`),                                                 
             CONSTRAINT `FK3BA609A912BE6C7B` FOREIGN KEY (`series_id`) REFERENCES `series` (`id`),  
             CONSTRAINT `FK3BA609A9814B4ADB` FOREIGN KEY (`game_id`) REFERENCES `game` (`id`),      
             CONSTRAINT `FK3BA609A9ED014AD9` FOREIGN KEY (`model_id`) REFERENCES `model` (`id`)     
           ) ENGINE=InnoDB AUTO_INCREMENT=62443 DEFAULT CHARSET=utf8      
现在的问题是：c3p0的连接数就会用满，而且mysql中用的cpu达到400%以上。上线立刻就崩溃了。
请教高人指点下，急

Answer 1

select g from Game g left outer join game_file as f on g.id = g.game_id where g.public = true
  and g.type = ? and g.category.id = ?  
  and ((g.type = ? and (f.mobile.id =? or f.series.id in ( ? ))) and f.channel = ? group by g.id order by g.modify_date  不好意思，补充下sql的下发，是从hibernate转换过来的

Answer 2

贴出你的show index from Game;
show index from game_file;
explain select g from Game g left outer join game_file as f on g.id = g.game_id where g.public = true
 and g.type = ? and g.category.id = ? 
 and ((g.type = ? and (f.mobile.id =? or f.series.id in ( ? ))) and f.channel = ? group by g.id order by g.modify_date  

Answer 3

mysql> show index from game;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| game  |          0 | PRIMARY          |            1 | id          | A         |        3609 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | FK304BF2DBFB06DB |            1 | category_id | A         |          20 |     NULL | NULL   | YES  | BTREE      |         | 
| game  |          1 | idx_origin       |            1 | origin      | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | id_index         |            1 | id          | A         |        3609 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | public           |            1 | public      | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | public           |            2 | type        | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | idx_type         |            1 | type        | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | idx_type         |            2 | public      | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | id               |            1 | id          | A         |        3609 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | id               |            2 | public      | A         |        3609 |     NULL | NULL   |      | BTREE      |         | 
| game  |          1 | id               |            3 | type        | A         |        3609 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show index from game_file;
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| game_file |          0 | PRIMARY            |            1 | id          | A         |       70030 |     NULL | NULL   |      | BTREE      |         | 
| game_file |          1 | FK3BA609A9814B4ADB |            1 | game_id     | A         |        8753 |     NULL | NULL   |      | BTREE      |         | 
| game_file |          1 | FK3BA609A912BE6C7B |            1 | series_id   | A         |         223 |     NULL | NULL   | YES  | BTREE      |         | 
| game_file |          1 | FK3BA609A9ED014AD9 |            1 | model_id    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         | 
| game_file |          1 | id_index           |            1 | id          | A         |       70030 |     NULL | NULL   |      | BTREE      |         | 
| game_file |          1 | game_id_2          |            1 | game_id     | A         |        8753 |     NULL | NULL   |      | BTREE      |         | 
| game_file |          1 | game_id_2          |            2 | series_id   | A         |       70030 |     NULL | NULL   | YES  | BTREE      |         | 
| game_file |          1 | game_id            |            1 | game_id     | A         |        8753 |     NULL | NULL   |      | BTREE      |         | 
| game_file |          1 | game_id            |            2 | model_id    | A         |       10004 |     NULL | NULL   | YES  | BTREE      |         | 
| game_file |          1 | game_id            |            3 | series_id   | A         |       70030 |     NULL | NULL   | YES  | BTREE      |         | 
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.01 sec)
mysql>  explain select * from game g left outer join game_file as f on g.id = f.game_id where g.public = true
    ->  and g.type = 1 and g.category_id = 5  and (f.model_id  =599 or f.series_id in (-10,-11,-12,-13,-14,-15)) and f.channel = -1 group by g.id order by g.modify_date;
+----+-------------+-------+-------------+----------------------------------------------------------------------------+-------------------------+---------+------------------------------+------+----------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                                              | key                     | key_len | ref                          | rows | Extra                                                                                  |
+----+-------------+-------+-------------+----------------------------------------------------------------------------+-------------------------+---------+------------------------------+------+----------------------------------------------------------------------------------------+
|  1 | SIMPLE      | g     | index_merge | PRIMARY,FK304BF2DBFB06DB,id_index,public,idx_type,id                       | public,FK304BF2DBFB06DB | 3,5     | NULL                         |    1 | Using intersect(public,FK304BF2DBFB06DB); Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | f     | ref         | FK3BA609A9814B4ADB,FK3BA609A912BE6C7B,FK3BA609A9ED014AD9,game_id_2,game_id | FK3BA609A9814B4ADB      | 8       | fun_platform_production.g.id |    8 | Using where                                                                            | 
+----+-------------+-------+-------------+----------------------------------------------------------------------------+-------------------------+---------+------------------------------+------+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)有点乱，不好意思啊，帮忙看看，谢谢

Answer 4

创建如下索引
create index xxxx on game(public,type,category_id,id,modify_date);
不过很奇怪，你的public，type 字段中的值都是相同的，也就是说public都是TRUE啊，type也都是1啊。另外，为什么要用LEFT JOIN 呢？你既然已经有了
and (f.model_id  =599 or f.series_id in (-10,-11,-12,-13,-14,-15)) 
and f.channel = -1 
也就是说不可能有f.channel为空的情况了，为什么不直接用 inner join ?需要进一步分析的地方还有很多，不清楚你的具体应用和背景，也无法理解你的SQL语句为什么这样写。暂时无法做出什么进一步的优化。

Answer 5

这些数字是我填上去的，实际上他们是从不同的地方得到的值，只根据hibernate的级联实现的，因为要在这里提问，所以自己转成sql的了，呵呵。郁闷