MySQL选择计数与组和顺序性能问题

Question

事实：

• 专用服务器，4核，16GB
• MySQL 5.5.29-0Ubuntu0.12.10.1-log-(Ubuntu)
• 一个表，1.9百万行，并且还在增长

mysql> SHOW CREATE TABLE data \G;
*************************** 1. row ***************************
       Table: data
Create Table: CREATE TABLE `data` (
  `data_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rel_id` int(11) NOT NULL,
  `some_text` varchar(255) DEFAULT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `some_bool` tinyint(1) DEFAULT NULL,
  `some_filter` varchar(40) DEFAULT NULL,
  `ano_filter` varchar(10) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  PRIMARY KEY (`data_id`),
  KEY `cnt_idx` (`some_filter`,`ano_filter`,`rel_id`,`lang`,`some_bool`,`some_text`,`day`)
) ENGINE=MyISAM AUTO_INCREMENT=1900099 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查询

mysql> EXPLAIN SELECT `some_text` , COUNT(*) AS `num` FROM `data` 
 WHERE `lang` = 'en' AND `day` BETWEEN '1364342400' AND
 '1366934399' GROUP BY `some_text` ORDER BY `num` DESC \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: data
         type: index
possible_keys: NULL
          key: cnt_idx
      key_len: 947
          ref: NULL
         rows: 1900098
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> SELECT `some_text` , COUNT(*) AS `num` FROM `data` 
 WHERE `lang` = 'en' AND `day` BETWEEN '1364342400' AND '1366934399' 
 GROUP BY `some_text` ORDER BY `num` DESC LIMIT 5 \G;
...
*************************** 5. row ***************************
5 rows in set (24.26 sec)

你知道怎么加快那东西的速度吗？“

Answer 1

由于索引中的列顺序，没有使用索引。索引从左到右工作。要使该查询使用索引，需要lang,day的索引。