mysql优化

Question

我正在建立一个有很多很多过滤器的网站。每次单击/缩小搜索范围时，都会重新计算所有过滤器的所有计数器。我已经意识到我在这里造成了一些问题，因为每次点击，我都会通过大约50次计数查询来重新计算所有的过滤器。当有许多用户同时使用该站点时，这将产生相当大的数据库负载。

过滤器计数器看起来像这样，如果你想要更多的上下文：http://screencast.com/t/3bxrWTAtm

优化这些计数的最佳方法是什么？我可以使用memcache，但它必须缓存大约15-30分钟，因为新的列表可能会显示出来。

以下是其中一个计数查询的示例：

SELECT COUNT(id) AS numOfRows FROM (SELECT `id`, `theLatitude`, `theLongitude`, ( 3959 * acos( cos( radians(45.5086699) ) * cos( radians( theLatitude ) ) * cos( radians( theLongitude ) - radians(-73.5539925) ) + sin( radians(45.5086699) ) * sin( radians( theLatitude ) ) ) ) AS distance  FROM `housing` WHERE  (`elevator` = '1') AND ( `property-type` = 'loft' )  HAVING distance < 75) AS `newid`

房屋表看起来像这样：

CREATE TABLE IF NOT EXISTS `housing` (
`id` varchar(10) NOT NULL,
`date` datetime NOT NULL,
`user` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`title` varchar(60) NOT NULL,
`address` varchar(120) DEFAULT NULL,
`formatted_address` varchar(120) NOT NULL,
`theLatitude` double DEFAULT NULL,
`theLongitude` double DEFAULT NULL,
`property-type` varchar(20) NOT NULL,
`square-feet` int(9) DEFAULT NULL,
`bathrooms` double NOT NULL DEFAULT '0',
`bedrooms` int(2) NOT NULL DEFAULT '0',
`price` int(10) DEFAULT NULL,
`priceUSD` int(10) NOT NULL,
`currency` varchar(3) DEFAULT NULL,
`period` varchar(5) DEFAULT NULL,
`lease-terms` varchar(20) NOT NULL,
`available-from` date NOT NULL,
`contactName` varchar(30) NOT NULL,
`contactPhone1` varchar(30) NOT NULL,
`contactPhone2` varchar(30) NOT NULL,
`description` text NOT NULL,
`cats` tinyint(1) NOT NULL DEFAULT '0',
`small-dogs` tinyint(1) NOT NULL DEFAULT '0',
`small-pets` tinyint(1) NOT NULL DEFAULT '0',
`big-dogs` tinyint(1) NOT NULL DEFAULT '0',
`alarm-system` tinyint(1) NOT NULL DEFAULT '0',
`air-conditioning` tinyint(1) NOT NULL DEFAULT '0',
`balcony` tinyint(1) NOT NULL DEFAULT '0',
`carpet-floors` tinyint(1) NOT NULL DEFAULT '0',
`dishwasher` tinyint(1) NOT NULL DEFAULT '0',
`electricity` tinyint(1) NOT NULL DEFAULT '0',
`fireplace` tinyint(1) NOT NULL DEFAULT '0',
`furniture` tinyint(1) NOT NULL DEFAULT '0',
`heating` tinyint(1) NOT NULL DEFAULT '0',
`high-ceilings` tinyint(1) NOT NULL DEFAULT '0',
`hot-water` tinyint(1) NOT NULL DEFAULT '0',
`natural-gas` tinyint(1) NOT NULL DEFAULT '0',
`spa-hot-tub` tinyint(1) NOT NULL DEFAULT '0',
`refrigerator` tinyint(1) NOT NULL DEFAULT '0',
`stove` tinyint(1) NOT NULL DEFAULT '0',
`storage-space` tinyint(1) NOT NULL DEFAULT '0',
`walk-in-closets` tinyint(1) NOT NULL DEFAULT '0',
`washer-dryer` tinyint(1) NOT NULL DEFAULT '0',
`doorman` tinyint(1) NOT NULL DEFAULT '0',
`elevator` tinyint(1) NOT NULL DEFAULT '0',
`health-facilities` tinyint(1) NOT NULL DEFAULT '0',
`inside-parking` tinyint(1) NOT NULL DEFAULT '0',
`laundry-facilities` tinyint(1) NOT NULL DEFAULT '0',
`outside-parking` tinyint(1) NOT NULL DEFAULT '0',
`pool-sauna` tinyint(1) NOT NULL DEFAULT '0',
`wheelchair-access` tinyint(1) NOT NULL DEFAULT '0',
`authnumber` varchar(15) NOT NULL,
`validated` tinyint(1) NOT NULL DEFAULT '0',

主键(id) ) ENGINE=MyISAM默认CHARSET=latin1；

用户可以根据50+字段进行筛选。

任何能给我指明正确方向的东西都将不胜感激。

Answer 1

1. 按照hellohellosharp答案中的建议使用Explain。
2. 尝试将index添加到表中的属性。
3. 使用Mysql缓存。
4. 使用memcached。强烈建议！

Answer 2

我过去也为一个网站做过类似的分类和产品。我的方法是首先为每组过滤器编写一个应用程序调用。换句话说，一个获取特性列表的调用就是一个例子。

接下来，您绝对希望在应用程序/web代码中缓存该调用。您可以缓存来自应用程序或为应用程序提供支持的服务的函数调用。例如，如果您有一个函数"getListingFeatures()"，您将在Memcached中缓存该函数2分钟。当它到期时，它当然会转到数据库并再次将其加载到缓存中。

您绝对可以将过期时间设置为较低的值。

还要考虑让你的过期时间相当随机，以避免“雷鸣般的羊群”(http://en.wikipedia.org/wiki/Thundering_herd_problem)。

我强烈建议您继续使用Memcached。

Answer 3

首先，在您的查询上使用Explain，看看您的mysql需要检查多少行。试着了解一下幕后发生了什么。

其次，如果可能的话，使用Join而不是嵌套查询。这大大减轻了mysql的负担。

第三，在您的过滤器列上试验不同的索引。

编辑: Bill Karwin教会了我索引的1,2,3规则。也就是说，WHERE，ORDER BY，SELECT。WHERE语句中的列通常也是添加索引的最重要的列。

现在，对于使用这种密集计算的查询，我不能说什么索引最适合您，这就是为什么您应该做一些实验。

Watch this slideshow来学习索引的基础知识。所有的功劳都归功于比尔·卡温。