Я просматриваю журнал медленных запросов, чтобы попытаться определить, почему некоторые запросы ведут себя нестабильно. В целях согласованности запросы не кэшируются, и перед запуском теста была произведена очистка системного кеша. Запрос выглядит примерно так:
SELECT P.id, P.name, P.lat, P.lng, P.price * E.rate AS 'ask' FROM Property P
INNER JOIN Exchange E ON E.currency = P.currency
WHERE P.floor_area >= k?
AND P.closing_date >= CURDATE() // this and key_buffer_size=0 prevents caching
AND P.type ='c'
AND P.lat BETWEEN v? AND v?
AND P.lng BETWEEN v? AND v?
AND P.price * E.rate BETWEEN k? AND k?
ORDER BY P.floor_area DESC LIMIT 100;
k?
- это определяемые пользователем постоянные значения; v?
- это переменные , которые изменяются при перетаскивании или масштабировании карты пользователем. 100 результатов извлекаются из таблицы и сортируются по площади в порядке убывания.
Устанавливаются только ПЕРВИЧНЫЙ ключ на id
и ИНДЕКС на floor_area
. Никакой другой индекс не создается, чтобы MySQL постоянно использовал floor_area
в качестве единственного ключа. Время выполнения запроса и проверенные строки записываются следующим образом:
query number 1 2 3 4 5 6 7 8 9 10
user action on map start > + + < ^ + > v +
time in seconds 138 0.21 0.43 32.3 0.12 0.12 36.3 4.33 0.33 2.00
rows examined ('000) 43 43 43 60 43 43 111 139 133 176
План выполнения запроса выглядит следующим образом:
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+-------------+
| 1 | SIMPLE | P | range | id_flA | id_flA | 3 | NULL | 4223660 | Using where |
| 1 | SIMPLE | E | eq_ref | PRIMARY | PRIMARY | 3 | BuySell.P.currency | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+---------+-------------+
Тест выполняется несколько раз, и результаты полностью согласуются с приведенным выше. В чем может быть причина (ы) резкого увеличения количества запросов в запросах номер 4 и номер 7 и как мне его отключить?
ОБНОВЛЕНИЕ:
Результаты удаления ЗАКАЗА BY
, предложенный Digital Precision:
query number 1 2 3 4 5 6 7 8 9 10
user action on map start > + + < ^ + > v +
time in seconds 255 3.10 3.16 3.08 3.18 3.21 3.32 3.18 3.17 3.80
rows examined ('000) 131 131 131 131 136 136 136 136 136 157
План выполнения запроса такой же, как и выше, хотя он больше похож на сканирование таблицы. Обратите внимание, что я использую движок MyISAM версии 5.5.14.
В соответствии с запросом, ниже представлена схема:
| Property | CREATE TABLE `Property` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` char(1) NOT NULL DEFAULT '',
`lat` decimal(6,4) NOT NULL DEFAULT '0.0000',
`lng` decimal(7,4) NOT NULL DEFAULT '0.0000',
`floor_area` mediumint(8) unsigned NOT NULL DEFAULT '0',
`currency` char(3) NOT NULL DEFAULT '',
`price` int(10) unsigned NOT NULL DEFAULT '0',
`closing_date` date NOT NULL DEFAULT '0000-00-00',
`name` char(25) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `id_flA` (`floor_area`)
) ENGINE=MyISAM AUTO_INCREMENT=5000000 DEFAULT CHARSET=latin1
| Exchange | CREATE TABLE `Exchange` (
`currency` char(3) NOT NULL,
`rate` decimal(11,10) NOT NULL DEFAULT '0.0000000000',
PRIMARY KEY (`currency`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2-е ОБНОВЛЕНИЕ:
Я подумал, что было бы уместно опубликовать параметры, отличные от значений по умолчанию, в файле конфигурации my.cnf
, поскольку два ответчика упоминают о параметрах:
max_heap_table_size = 1300M
key_buffer_size = 0
read_buffer_size = 1300M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1300M
У меня на тестовом сервере 2 ГБ ОЗУ.