近期对网站压力测试,遇到个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的问题(有人说要用强制索引,分表),试了但效果不理想,跪求各位大侠能给个好的办法。线上等
     

解决方案 »

  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 结果
      

  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 秒 
      

  3. 贴出你的 show index from shinyv_content_main;
      

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

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

  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把运行后的结果贴上来才好分析
      

  7. 1:加大max_length_for_sort_data参数的设置
    2:增大sort_buffer_size的设置
    3:
      

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

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

  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 
      

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

  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 
      


  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,是肯定会走索引的。
      

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

  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>
      

  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)
      

  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
    这条语句在两个版本下响应时间差几十倍,谢谢大家
      

类似问题 »