MySQL 在具有多列/单列索引的范围查询上高效选择/排序

Question

我正在尝试对具有 300 万条记录的表进行最有效的选择。

首先是一些详细信息

表格:

CREATE TABLE IF NOT EXISTS `activities_index` (
  `id` int(9) NOT NULL auto_increment,
  `activity_id` int(6) NOT NULL,
  `activity_status_id` int(2) NOT NULL,
  `activity_source_id` int(6) default NULL,
  `account_id` int(6) default NULL,
  `owner_account_id` int(4) default NULL,
  `date` date NOT NULL,
  `is_event` int(1) NOT NULL,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  `content` longtext collate utf8_unicode_ci,
  `location_name` varchar(255) collate utf8_unicode_ci default NULL,
  `location_content` longtext collate utf8_unicode_ci,
  `meta_keywords` varchar(255) collate utf8_unicode_ci default NULL,
  `thumb_filename` varchar(255) collate utf8_unicode_ci default NULL,
  `popular` int(1) NOT NULL default '0',
  `price` float default NULL,
  `city_id` int(9) default NULL,
  `province_id` int(4) default NULL,
  `country_id` int(4) default NULL,
  `activity_location_id` int(6) NOT NULL,
  `lat` decimal(10,6) default NULL,
  `lng` decimal(10,6) default NULL,
  `activity_modified` datetime default NULL,
  `activity_created` datetime NOT NULL,
  `activity_location_modified` datetime default NULL,
  `activity_location_created` datetime NOT NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `is_event_idx` (`is_event`),
  KEY `activity_id_idx` (`activity_id`),
  KEY `status_city_idx` (`activity_status_id`, `city_id`),
  KEY `date_idx` (`date`),
  FULLTEXT KEY `txt_fields_idx` (`name`,`location_name`,`meta_keywords`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14865 ;

查询:

SELECT SQL_NO_CACHE * FROM `activities_index` WHERE 
date BETWEEN '2009-12-23' AND '2010-1-23' AND
activity_status_id = 1 AND
city_id IN ('86', '84', '87', '2381', '453', '137', '1561', '1116', '1614', '2456', '512', '305', '443', '1182', '2229')
ORDER BY date
LIMIT 25

关于我的索引选择:
主要问题是 DATE 的范围选择。为什么我没有使用基于我认为正确的多列索引，如果我错了请纠正我: MySQL 在范围后不使用任何索引。因此索引 (DATE, ACTIVITY_STATUS_ID, CITY_ID) 将毫无用处。索引表上的排序方式只有在使用正确的前缀时才是正确的。因此，多列索引(CITY_ID、ACTIVITY_STATUS_ID、DATE)不会给出正确的排序结果，因为我们想对列 DATE 上的数据进行排序。

解释:
当对查询进行 EXPLAIN 时，possible_keys 顺序是 CITY_IDX, DATE_STATUS_IDX 而不是我认为将该顺序翻转为 DATE_IDX, CITY_IDX 在按 DATE 排序时会更有效率。

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra<br />
1  SIMPLE  activities_index  range  city_idx,date_idx  city_idx  5  NULL  1363  Using where; Using filesort

我的问题:
如何翻转 possible_keys 的顺序？
有没有更好的方法来解决这个问题:对具有 300 万条记录的表进行高效选择？
我的想法是否正确？

Answer 1

据我所知，sql-query-analyzer 从右到左解析查询 - 所以他遇到的第一个索引是 city-one，因为它是最右边的。也许您可以通过更改 in 和 between 子句的位置来翻转索引。您需要表格中的所有信息吗？如果不是，您可以通过仅选择所需的列来提高速度。