MySQL无需缓存即可提高性能 [英] MySQL Enhancing Performance without Cache

问题描述

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

I am using MySQL version 5.5.14 to run the following query from a table of 5 Million rows:

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

并且:

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

以下显示了查询执行计划:

The following shows the query execution plan:

*************************** 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()函数，此查询不使用查询缓存.从我以前的发布中，我发现存在其他形式的缓存来加快从初始查询的速度时间从 300s缩短到不到2s .我的问题是:"如何知道上面的查询时间，因为latlng的搜索条件不断变化，因此不会有太多用处?"请注意，latlng上的空间索引已经出于优化目的而构建.

This query does not use query cache due to the presence of NOW() function. From my previous posting, I discovered that other forms of cache exist to speed up the query from initial time of 300s down to less than 2s. My question is: "how does one improve the above query time, knowing that the cache won't be of much use since the search criteria for latlng is constantly changing?" Note that a spatial index on latlng has already been built for optimisation purpose.

欢呼，本

推荐答案

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

Good indexes are the ones with high selectivity. Your conditions are mostly range conditions and this poses a limit on the fields that can be used in a composite index.

可能要研究的索引(由那些进行相等性检查并最后加上一个字段并进行范围检查的字段组成):

Possible indexes to investigate (composed from those fields that have an equality check with the addition in the end, of one field with a range check):

(act, Type, tn, flA)

(act, Type, tn, cDate)

(act, Type, tn, nb)

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

To check selectivity without creating indexes, you could use:

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

和

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

和

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

并将输出与空间索引中的742873进行比较.

and compare the output with the 742873 you have from the spatial index.

这篇关于MySQL无需缓存即可提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！