排序和分组的MySQL查询优化

Question

我有这样的查询...

SELECT SQL_CACHE area3, area, area2, COUNT( area ) AS total
FROM wpthillsdatabase
GROUP BY area
ORDER BY area3 ASC , area ASC

它的 EXPLAIN 说它正在使用文件排序，没有索引和临时表...

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  wpthillsdatabaseV12 ALL NULL    NULL    NULL    NULL    9334    Using temporary; Using filesort

是否可以进一步优化它以删除带有索引的文件排序或临时表，或者这是否已经很好了？

仅供引用，表的结构如下...

CREATE TABLE `wpthillsdatabase` (
  `hillnumber` varchar(6) NOT NULL default '',
  `wikipedia` varchar(100) NOT NULL default '',
  `hillname` varchar(100) NOT NULL default '',
  `meters` char(6) NOT NULL default '',
  `GridZN` char(2) NOT NULL default '',
  `GridEast` char(5) NOT NULL default '',
  `GridNorth` char(5) NOT NULL default '',
  `numeast` char(7) NOT NULL default '',
  `numnorth` char(7) NOT NULL default '',
  `areadecimal` decimal(5,2) NOT NULL default '0.00',
  `area` varchar(3) NOT NULL default '',
  `area2` varchar(100) NOT NULL default '',
  `maps` varchar(100) NOT NULL default '',
  `waypointname` char(6) character set latin1 collate latin1_bin NOT NULL default '',
  `latitude` decimal(10,8) NOT NULL default '0.00000000',
  `longitude` decimal(10,7) NOT NULL default '0.0000000',
  `area3` smallint(2) NOT NULL default '0',
  `dip` decimal(5,1) NOT NULL default '0.0' COMMENT 'col height (m)',
  `climbedbytotal` smallint(6) NOT NULL default '0',
  `trigID` varchar(6) NOT NULL default '',
  `trigEast` varchar(7) NOT NULL default '',
  `trigNorth` varchar(7) NOT NULL default '',
  `trigLat` varchar(10) NOT NULL default '',
  `trigLon` varchar(10) NOT NULL default '',
  `SummitInfo` varchar(290) NOT NULL default '',
  PRIMARY KEY  (`hillnumber`,`area3`),
  UNIQUE KEY `meters` (`meters`,`hillnumber`),
  UNIQUE KEY `SortIndex1` (`hillnumber`,`hillname`,`meters`),
  UNIQUE KEY `NearByHills` (`numeast`,`numnorth`,`hillnumber`),
  UNIQUE KEY `hillnumber_only` (`hillnumber`),
  UNIQUE KEY `Area3_Sort` (`area3`,`hillnumber`,`hillname`,`meters`),
  UNIQUE KEY `GirdZN_sort` (`GridZN`,`hillnumber`,`hillname`,`meters`),
  UNIQUE KEY `hil_lat_lon` (`hillnumber`,`latitude`,`longitude`),
  KEY `trigID` (`trigID`,`hillname`,`meters`),
  KEY `climbedbytotal` (`climbedbytotal`),
  KEY `hillname` (`hillname`,`meters`),
  KEY `area3` (`area3`,`hillnumber`),
  KEY `hillname_only` (`hillname`),
  KEY `area3_trigID` (`area3`,`trigID`),
  KEY `Area_text` (`area`,`area3`),
  KEY `dip_area3` (`dip`,`area3`),
  KEY `lat_lon` (`latitude`,`longitude`,`meters`),
  KEY `trigID_area3` (`trigID`,`area3`),
  KEY `numeast_north` (`numeast`,`numnorth`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

Ta

菲尔

Answer 1

您的要求意味着什么:

• GROUP BY area 可以使用以 area 开头的索引进行优化
• ORDER BY are3 ASC, area ASC 可以使用以 area3, area 开头的索引进行优化

但是没有索引可以同时优化这两个位。

如果您有能力更改结果，请尝试更改排序顺序:ORDER BY area ASC, area3 ASC。

作为bobwienholt建议，您可以在表上测试覆盖索引，以至少优化 GROUP 表的提取，但索引应以 area 开头: KEY myKey (区域、区域 3、区域 2)。

但是，如果您的表变大并且经常更新，那么如果您的表经常更新，乘法索引(尤其是当它们包含 varchar(100) 列时)可能会影响性能。根据情况，你最好从 mysql 中提取未排序的数据，并在你的服务器代码中过滤/排序它们(php/ruby​​/python ...)