关于索引的问题

sosflyer

表结构如下:
CREATE TABLE `vm_internal_data_201110` (
  `date` date NOT NULL,
  `mark` varchar(100) NOT NULL default '',
  `mark_1` varchar(100) NOT NULL default '',
  `from_id` char(255) NOT NULL default '00000000',
  `product_type` varchar(20) NOT NULL default '',
  `extend_type` int(5) NOT NULL default '0',
  `active_user` int(11) unsigned NOT NULL default '0',
  `sum_active_user` int(11) unsigned NOT NULL default '0',
  `updates` int(11) unsigned default NULL,
  `update_user` int(11) unsigned NOT NULL default '0',
  `update_user_single` int(11) unsigned NOT NULL default '0',
  `repost` int(11) unsigned NOT NULL default '0',
  `repost_user` int(11) unsigned NOT NULL default '0',
  `repost_user_single` int(11) unsigned NOT NULL default '0',
  `mth_effective_user` int(11) unsigned NOT NULL default '0',
  `effective_user` int(10) NOT NULL default '0',
  `effective_user_single` int(10) NOT NULL default '0',
  `bk_frozen` int(11) unsigned NOT NULL default '0',
  `bk_black` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`date`,`mark`,`mark_1`,`from_id`,`product_type`),
  KEY `index_power` (`extend_type`,`product_type`)
)

mysql> explain select date,mark,mark_1 from vm_internal_data_201110 where extend_type in (11,32,21,7,26,2,17,22,0,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;
+----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table                   | type | possible_keys       | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
| 1  | SIMPLE      | vm_internal_data_201110 | ALL  | PRIMARY,index_power |      |         |      | 7073288 | Using where; Using temporary; Using filesort |
+----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
为什么extend_type这个索引用不上呢
mysql> explain select date,mark,mark_1,sum(effective_user)/sum(sum_active_user) from vm_internal_data_201110 where product_type in ('PCAPP','WEIYU','APP','VMCLIENT','CMPC','VMWAP','AIYH','VMPC') and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;
+----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| 1  | SIMPLE      | vm_internal_data_201110 | ALL  | PRIMARY       |      |         |      | 7073288 | Using where; Using temporary; Using filesort |
+----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
如果sql语句里不加sum(effective_user)/sum(sum_active_user),则可以用到索引,加上这个就用不上了,求解啊。

sosflyer

我知道字很多啊，哪位高人帮忙看看吧，真心求啊

justlooks

possible_keys 只是显示可选索引好瓦，你的两个查询都不走索引

todayhero

mysql> explain select date,mark,mark_1,sum(effective_user)/sum(sum_active_user) from vm_internal_data_201110 where product_type in ('PCAPP','WEIYU','APP','VMCLIENT','CMPC','VMWAP','AIYH','VMPC') and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | vm_internal_data_201110 | range | PRIMARY       | PRIMARY | 105     | NULL |    1 | Using where |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select date,mark,mark_1 from vm_internal_data_201110 where product_type in ('PCAPP','WEIYU','APP','VMCLIENT','CMPC','VMWAP','AIYH','VMPC') and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | vm_internal_data_201110 | index | PRIMARY       | PRIMARY | 484     | NULL |    1 | Using where; Using index |
+----+-------------+-------------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql>

回复 2# sosflyer


加与不加都使用不到你所说的索引.

sosflyer

回复 4# todayhero


    你看这条

1. 
   
2. mysql> explain select date,mark,mark_1,sum(effective_user)/sum(sum_active_user) from vm_internal_data_201110 where product_type in ('PCAPP','WEIYU','APP','VMCLIENT','CMPC','VMWAP','AIYH','VMPC') and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;
   
3. +----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
   
4. | id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
   
5. +----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
   
6. | 1  | SIMPLE      | vm_internal_data_201110 | ALL  | PRIMARY       |      |         |      | 7368397 | Using where; Using temporary; Using filesort |
   
7. +----+-------------+-------------------------+------+---------------+------+---------+------+---------+----------------------------------------------+
   
8. 1 row in set (0.01 sec)

复制代码

怎么我执行的就是这样的呢？

todayhero

你根本没有建.extend_type这个单列索引.
只是建了一个组合索引.
index_power

sosflyer

回复 6# todayhero


    囧，确实，我以为主键里有了就可以不单独建了。。。

  在麻烦帮看下下面这个

1. mysql> explain select extend_type,sum(active_user) from vm_internal_data_201110 where date between '2011-10-01' and '2011-10-26' and extend_type in (11,32,21,7,26,2,17,22,0,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and product_type in ('APP','CMPC','AIYH','VMPC','VMWAP','VMCLIENT','WEIYU','PCAPP') group by extend_type,date;
   
2. +----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
   
3. | id | select_type | table                   | type | possible_keys       | key  | key_len | ref  | rows    | Extra                                        |
   
4. +----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
   
5. | 1  | SIMPLE      | vm_internal_data_201110 | ALL  | PRIMARY,index_power |      |         |      | 7368397 | Using where; Using temporary; Using filesort |
   
6. +----+-------------+-------------------------+------+---------------------+------+---------+------+---------+----------------------------------------------+
   
7. 1 row in set (0.09 sec)
   
8. 
   
9. mysql> explain select extend_type,sum(active_user) from vm_internal_data_201110 where date between '2011-10-01' and '2011-10-26' and extend_type in (11,32,21,7,26,2,17,22,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and product_type in ('APP','CMPC','AIYH','VMPC','VMWAP','VMCLIENT','WEIYU','PCAPP') group by extend_type,date;
   
10. +----+-------------+-------------------------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
    
11. | id | select_type | table                   | type  | possible_keys       | key         | key_len | ref  | rows   | Extra                                        |
    
12. +----+-------------+-------------------------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
    
13. | 1  | SIMPLE      | vm_internal_data_201110 | range | PRIMARY,index_power | index_power | 46      |      | 665369 | Using where; Using temporary; Using filesort |
    
14. +----+-------------+-------------------------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
    
15. 1 row in set (0.09 sec)

复制代码

两次执行就只有一个地方不一样extend_type in(0)，第一次多一个0这个值，怎么联合索引就用不上了呢？？？

todayhero

回复 7# sosflyer


    你两个语句是不是一样啊!我的执行结果都一样的.

1. mysql> explain select extend_type,sum(active_user) from vm_internal_data_201110 where date between '2011-10-01' and '2011-10-26' and extend_type in (11,32,21,7,26,2,17,22,0,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and product_type in ('APP','CMPC','AIYH','VMPC','VMWAP','VMCLIENT','WEIYU','PCAPP') group by extend_type,date;
   
2. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
   
3. | id | select_type | table                   | type  | possible_keys       | key     | key_len | ref  | rows | Extra                                        |
   
4. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
   
5. |  1 | SIMPLE      | vm_internal_data_201110 | range | PRIMARY,index_power | PRIMARY | 3       | NULL |    1 | Using where; Using temporary; Using filesort |
   
6. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. mysql> explain select extend_type,sum(active_user) from vm_internal_data_201110 where date between '2011-10-01' and '2011-10-26' and extend_type in (11,32,21,7,26,2,17,22,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and product_type in ('APP','CMPC','AIYH','VMPC','VMWAP','VMCLIENT','WEIYU','PCAPP') group by extend_type,date;
   
10. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
    
11. | id | select_type | table                   | type  | possible_keys       | key     | key_len | ref  | rows | Extra                                        |
    
12. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
    
13. |  1 | SIMPLE      | vm_internal_data_201110 | range | PRIMARY,index_power | PRIMARY | 3       | NULL |    1 | Using where; Using temporary; Using filesort |
    
14. +----+-------------+-------------------------+-------+---------------------+---------+---------+------+------+----------------------------------------------+
    
15. 1 row in set (0.00 sec)

复制代码

而且你的表有数据,有的表里没有数据,对测试的结果是有影响的.

todayhero

而且都用到了index_power这个组合索引.你具体想问什么?

eugene_jin

针对SQL语句：
mysql> explain select date,mark,mark_1 from vm_internal_data_201110 where extend_type in (11,32,21,7,26,2,17,22,0,30,16,13,23,29,6,27,25,28,3,9,12,15,14,20,4,24,10,19,31,5) and mark in ('9012','9012','8008') and date >='2011-10-01' and date <='2011-10-25' group by date,mark,mark_1;

建议创建一个组合索引

ALTER TABLE vm_internal_data_201110 ADD INDEX idx_date_mark_mark1(date,mark,mark1);

再执行EXPLAIN看下执行计划的效果如何....

顺道再推荐一篇文章：
MySQL中创建及优化索引组织结构的思路
URL：http://www.mysqlops.com/2011/05/ ... optimize-index.html