MySQL 在没有缓存的情况下增强性能

我正在使用 MySQL 版本 5.5.14 从 500 万行的表运行以下查询：

SELECT P.ID, P.Type, P.Name, P.cty
     , X(P.latlng) as 'lat', Y(P.latlng) as 'lng'
     , P.cur, P.ak, P.tn, P.St, P.Tm, P.flA, P.ldA, P.flN
     , P.lv, P.bd, P.bt, P.nb
     , P.ak * E.usD as 'usP' 
FROM PIG P 
  INNER JOIN EEL E 
    ON E.cur = P.cur 
WHERE act='1' 
  AND flA >= '1615' 
  AND ldA >= '0' 
  AND yr >= (YEAR(NOW()) - 100) 
  AND lv >= '0' 
  AND bd >= '3' 
  AND bt >= '2' 
  AND nb <= '5' 
  AND cDate >= NOW() 
  AND MBRContains(LineString( Point(-65.6583, -87.8906)
                            , Point(65.6583, 87.8906)
                            ), latlng) 
  AND Type = 'g' 
  AND tn = 'l' 
  AND St + Tm - YEAR(NOW()) >= '30' 
HAVING usP BETWEEN 300/2 AND 300 LIMIT 100;

表定义为：

CREATE TABLE `PIG` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Email` char(50) NOT NULL,
  `Type` char(1) NOT NULL,
  `Name` char(25) DEFAULT NULL,
  `cty` char(2) DEFAULT NULL,
  `latlng` point NOT NULL,
  `tn` char(1) NOT NULL DEFAULT 'l',
  `St` smallint(4) unsigned NOT NULL DEFAULT '0',
  `Tm` smallint(3) unsigned NOT NULL DEFAULT '0',
  `yr` smallint(4) unsigned NOT NULL DEFAULT '0',
  `flA` mediumint(6) unsigned NOT NULL DEFAULT '0',
  `ldA` mediumint(6) unsigned NOT NULL DEFAULT '0',
  `flN` smallint(3) unsigned NOT NULL DEFAULT '1',
  `lv` smallint(3) unsigned NOT NULL DEFAULT '0',
  `bd` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `bt` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `nb` tinyint(1) unsigned NOT NULL DEFAULT '9',
  `cur` char(3) DEFAULT NULL,
  `ak` int(10) unsigned NOT NULL DEFAULT '0',
  `Des` tinytext,
  `pDate` datetime DEFAULT NULL,
  `cDate` date DEFAULT NULL,
  `act` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `bid` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ab` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `id_ca` (`cty`,`ak`),
  SPATIAL KEY `id_latlng` (`latlng`)
) ENGINE=MyISAM AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

And:

CREATE TABLE `EEL` (
  `cur` char(3) NOT NULL,
  `usD` decimal(11,10) NOT NULL,
  PRIMARY KEY (`cur`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

查询执行计划如下所示：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: P
         type: range
possible_keys: id_latlng
          key: id_latlng
      key_len: 34
          ref: NULL
         rows: 742873
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: E
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: BS.P.cur
         rows: 1
        Extra: 

由于 NOW() 函数的存在，该查询不使用查询缓存。从我之前的posting https://stackoverflow.com/questions/7429336/mysql-erratic-query-times，我发现存在其他形式的缓存来加速从初始时间开始的查询300秒降至不到2秒。我的问题是：“由于 latlng 的搜索条件不断变化，因此知道缓存不会有太大用处，如何改善上述查询时间？" 请注意，出于优化目的，已经构建了 latlng 的空间索引。

干杯，本

好的索引是具有高选择性的索引。您的条件主要是范围条件，这对可在复合索引中使用的字段造成了限制。

可能要调查的索引（由具有相等性检查的字段组成，最后添加一个具有范围检查的字段）：

(act, Type, tn, flA)

(act, Type, tn, cDate)

(act, Type, tn, nb)

要在不创建索引的情况下检查选择性，您可以使用：

SELECT COUNT(*)
FROM PIG P 
WHERE act='1' 
  AND Type = 'g' 
  AND tn = 'l' 
  AND flA >= '1615'

and

SELECT COUNT(*)
FROM PIG P 
WHERE act='1' 
  AND Type = 'g' 
  AND tn = 'l' 
  AND cDate >= NOW() 

and

SELECT COUNT(*)
FROM PIG P 
WHERE act='1' 
  AND Type = 'g' 
  AND tn = 'l' 
  AND nb <= '5' 

并将输出与742873你从空间索引中得到的。