MYSQL WHERE+IN+ORDER BY 优化问题？

Question

    近期对网站压力测试，遇到个SQL优化问题。    数据库中有100万条数据，用select查询时相应时间特长怎么解决？    1、数据库结构 CREATE TABLE `shinyv_content_main` (                                                 
                       `id` int(11) unsigned NOT NULL auto_increment,                                     
                       `title` varchar(100) collate utf8_unicode_ci default NULL,                         
                       `title_alias` varchar(100) collate utf8_unicode_ci default NULL,                   
                       `state` tinyint(3) default '0',                                                    
                       `sectionid` int(11) unsigned default '0',                                          
                       `catid` int(11) unsigned default '0',                                              
                       `created` datetime default '0000-00-00 00:00:00',                                  
                       `images` text collate utf8_unicode_ci,                                             
                       `ordering` int(11) default '0',                                                    
                       `hits` int(11) unsigned default '0',                                               
                       `siteUserId` int(11) default NULL,                                                 
                       `vidtype` tinyint(2) default '3',                                                  
                       PRIMARY KEY  (`id`),                                                               
                       KEY `idx_section` (`sectionid`),                                                   
                       KEY `hits` (`hits`),                                                               
                       KEY `ordering` (`ordering`),                                                       
                       KEY `created` (`created`),                                                         
                       KEY `idex_sectionid_state` (`sectionid`,`state`),                                  
                       KEY `idx_sectionid_ordoring` (`sectionid`,`ordering`),                             
                       KEY `idx_sectionid_created` (`sectionid`,`created`)                                
                     ) ENGINE=MyISAM 
     2、SQL语句 SELECT a.id as contentid
            FROM shinyv_content_main AS a 
            WHERE a.state = 1 AND a.sectionid in(85,81,78,82,74)
            AND a.vidtype=3  ORDER BY a.ordering desc limit 50     当运行2中的SQL时相应时间有4、5秒，太不能让我承受，查了下资料说是IN的问题（有人说要用强制索引，分表），试了但效果不理想，跪求各位大侠能给个好的办法。线上等
     

Answer 1

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE a range idx_section,idex_sectionid_state,idx_sectionid_ord... idx_section 5 NULL 130288 Using where; Using filesort 
这是 explain 结果

Answer 2

(initialization) 0.00010625 
Opening tables 0.00002600 
System lock 0.00000750 
Table lock 0.00001225 
init 0.00004375 
optimizing 0.00002150 
statistics 0.00145600 
preparing 0.00140250 
executing 0.00000475 
Sorting result 8.55317700 
Sending data 0.00087175 
end 0.00000900 
query end 0.00000500 
freeing items 0.00001500 
closing tables 0.00004600 
logging slow query 0.00000375 查询花费 8.5584 秒 

Answer 3

贴出你的 show index from shinyv_content_main;

Answer 4

在没有更多数据以供分析之前，你可以试一下
create index xxxx on shinyv_content_main(sectionid,vidtype,state)

Answer 5

AND a.vidtype=3  应该有索引
没有什么问题  5s 很正常

Answer 6

用explain运行一下看看select是如何运行的，代码如下：
explain
SELECT a.id as contentid
  FROM shinyv_content_main AS a  
  WHERE a.state = 1 AND a.sectionid in(85,81,78,82,74)
  AND a.vidtype=3 ORDER BY a.ordering desc limit 50把运行后的结果贴上来才好分析

Answer 7

1：加大max_length_for_sort_data参数的设置
2：增大sort_buffer_size的设置
3：

Answer 8

ORDER BY用的时间多，
sectionid,vidtype,state，ordering建立过索试试

Answer 9

where中有a.sectionid in(85,81,78,82,74)这个条件时 什么索引都不走了

Answer 10

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE a range idx_section,idex_sectionid_state,idx_sectionid_ord... idx_section 5 NULL 130288 Using where; Using filesort 

Answer 11

所以需要你贴出你的show index from shinyv_content_main;应该是表中的没有合适的索引。

Answer 12

  键名                 类型       唯一    紧凑    字段        基数   整理 空 注释 
  PRIMARY           BTREE     是      否       id        153542  A     idx_section       BTREE     否      否       sectionid 23      A YES    idx_sectionid_created BTREE 否      否       sectionid 23      A YES  
                                             created   153542  A YES   idx_sectionid_ordering BTREE 否     否       sectionid 23      A YES  
                                             ordering   153542 A YES   idx_created            BTREE  否     否      created    153542 A YES    idx_ordering           BTREE  否     否      ordering   153542 A YES    idx_hits               BTREE  否     否      hits       153542 A YES    idx_sectionid_state_vidtype_ordering   BTREE  否    否      sectionid 23 A YES  
                                                            state 47 A YES 
                                                            vidtype 47 A YES 
                                                            ordering 153542 A YES 

Answer 13

从你这来来看，查询是使用了索引的，idx_section
你后面又说只要查询里有
“where中有a.sectionid in(85,81,78,82,74)这个条件时 什么索引都不走了”
没看明白。针对“where中有a.sectionid in(85,81,78,82,74)这个条件时 什么索引都不走了”
你试试
SELECT a.id as contentid FROM shinyv_content_main AS a  
  WHERE a.state = 1  AND a.vidtype=3 AND a.sectionid =85 union 
SELECT a.id as contentid FROM shinyv_content_main AS a  
  WHERE a.state = 1  AND a.vidtype=3 AND a.sectionid =81 union 
SELECT a.id as contentid FROM shinyv_content_main AS a  
  WHERE a.state = 1  AND a.vidtype=3 AND a.sectionid =78 union 
SELECT a.id as contentid FROM shinyv_content_main AS a  
  WHERE a.state = 1  AND a.vidtype=3 AND a.sectionid =82 union 
SELECT a.id as contentid FROM shinyv_content_main AS a  
  WHERE a.state = 1  AND a.vidtype=3 AND a.sectionid =74  这样对于每个小的SELECT，是肯定会走索引的。

Answer 14

explain 显示的索引是 idx_sectionid 但查询的时间100万条数据要8秒（问了别人说是where 中有IN就不走索引了）

Answer 15

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t2    |          0 | PRIMARY  |            1 | id          | A         |          40 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.11 sec)mysql>

Answer 16

mysql> show index  FROM shinyv_content_main;
+---------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
| Table               | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | In
dex_type | Comment |
+---------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
| shinyv_content_main |          0 | PRIMARY                |            1 | id          | A         |     1000437 |     NULL | NULL   |      | BT
REE      |         |
| shinyv_content_main |          1 | idx_section            |            1 | sectionid   | A         |          23 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | hits                   |            1 | hits        | A         |      500218 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | ordering               |            1 | ordering    | A         |      500218 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | created                |            1 | created     | A         |     1000437 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idex_sectionid_state   |            1 | sectionid   | A         |          23 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idex_sectionid_state   |            2 | state       | A         |          48 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idx_sectionid_ordoring |            1 | sectionid   | A         |          23 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idx_sectionid_ordoring |            2 | ordering    | A         |     1000437 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idx_sectionid_created  |            1 | sectionid   | A         |          23 |     NULL | NULL   | YES  | BT
REE      |         |
| shinyv_content_main |          1 | idx_sectionid_created  |            2 | created     | A         |     1000437 |     NULL | NULL   | YES  | BT
REE      |         |
+---------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
11 rows in set (0.00 sec)

Answer 17

问题已解决，是我数据库版本的问题，5.0和5.1有区别
   SELECT a.id as contentid
  FROM shinyv_content_main AS a   
  WHERE a.state = 1 AND a.sectionid in(85,81,78,82,74)
  AND a.vidtype=3 ORDER BY a.ordering desc limit 50
这条语句在两个版本下响应时间差几十倍，谢谢大家