Скорость работы MYSQL при использовании файловой сортировки

У меня есть простой запрос mysql, но когда у меня много записей (в настоящее время 103,0000), производительность очень низкая, и он говорит, что использует файловую сортировку, я не уверен, почему это так медленно. У кого-нибудь есть предложения по ускорению? или прекратить это с помощью сортировки файлов?

MYSQL-запрос:

SELECT adverts .*    
FROM adverts
WHERE (
price >='0'
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10

Объясните результаты:

id   select_type   table   type    possible_keys    key    key_len    ref    rows   Extra 
1    SIMPLE        adverts range   price            price  4          NULL   103854 Using where; Using filesort

Вот таблица объявлений и индексы:

CREATE TABLE `adverts` (
  `advert_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `type_id` tinyint(1) NOT NULL,
  `breed_id` int(10) NOT NULL,
  `advert_type` tinyint(1) NOT NULL,
  `headline` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `price` int(4) NOT NULL,
  `postcode` varchar(7) NOT NULL,
  `town` varchar(60) NOT NULL,
  `county` varchar(60) NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `telephone1` varchar(15) NOT NULL,
  `telephone2` varchar(15) NOT NULL,
  `email` varchar(80) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `approved` tinyint(1) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expiry_date` datetime NOT NULL,
  PRIMARY KEY (`advert_id`),
  KEY `price` (`price`),
  KEY `user` (`user_id`),
  KEY `type_breed` (`type_id`,`breed_id`),
  KEY `headline_keywords` (`headline`),
  KEY `date_updated` (`date_updated`),
  KEY `type_status_approved` (`advert_type`,`status`,`approved`)
) ENGINE=MyISAM AUTO_INCREMENT=103878 DEFAULT CHARSET=utf8
Источник

5 ответов

Решение

Проблема в том, что MySQL использует только один индекс при выполнении запроса. Если вы добавите новый индекс, который использует 3 поля в вашем WHERE предложение, он найдет строки быстрее.

ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);

Согласно документации MySQL оптимизация ORDER BY:

В некоторых случаях MySQL не может использовать индексы для разрешения ORDER BY, хотя он все еще использует индексы для поиска строк, которые соответствуют предложению WHERE. Эти случаи включают в себя следующее:
Ключ, используемый для выборки строк, не совпадает с ключом, используемым в ORDER BY.

Это то, что происходит в вашем случае. В качестве выхода EXPLAIN говорит нам, оптимизатор использует ключ price найти строки. Тем не менее ORDER BY на поле date_updated который не принадлежит ключу price,

Чтобы найти строки быстрее и отсортировать строки быстрее, вам нужно добавить индекс, который содержит все поля, используемые в WHERE и в ORDER BY статьи:

ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);

Поле, используемое для сортировки, должно быть в последней позиции в индексе. Бесполезно включать price в индексе, потому что условие, используемое в запросе, вернет диапазон значений.

Если EXPLAIN все еще показывает, что он использует файловую сортировку, вы можете попытаться заставить MySQL использовать выбранный вами индекс:

SELECT adverts.*
FROM adverts
FORCE INDEX(status_approved_date_updated)
WHERE price >= 0
AND adverts.status = 1
AND adverts.approved = 1
ORDER BY date_updated DESC 
LIMIT 19990, 10

Обычно нет необходимости форсировать индекс, потому что оптимизатор MySQL чаще всего делает правильный выбор. Но иногда это делает плохой выбор или не лучший выбор. Вам нужно будет запустить несколько тестов, чтобы увидеть, улучшает ли это производительность или нет.

Источник

Снимите галочки вокруг '0' - в настоящее время это может помешать использованию индекса, но я не уверен. Тем не менее, это лучший стиль, так как цена int введите, а не символьный столбец.

SELECT adverts .*    
FROM adverts
WHERE (
price >= 0
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10
Источник

У меня есть два предложения. Сначала удалите кавычки вокруг нуля в предложении where. Эта строка должна быть:

price >= 0

Во-вторых, создайте этот индекс:

CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);

Это должно позволить MySQL найти 10 строк, указанных вашим предложением LIMIT, используя только индекс. Файловая сортировка сама по себе неплохая вещь... количество строк, которые нужно обработать.

Источник

Ваш WHERE условие использования price, status, approved выбрать, а затем date_updated используется для сортировки.

Таким образом, вам нужен один индекс с этими полями; Я бы предложил индексацию на approved, status, price а также date_updated, в этом порядке.

Общее правило - сначала ставить равенства WHERE, затем диапазоны (больше, меньше или равно, между и т. Д.), А поля сортировки - последними. (Обратите внимание, что если вы пропустите одно поле, это может сделать индекс менее пригодным для использования или даже непригодным для этой цели).

CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);

Таким образом, доступ к данным таблицы необходим только после LIMIT сработал свое волшебство, и вы будете медленно извлекать только небольшое количество записей.

Я также удалил бы все ненужные индексы, которые ускорили бы INSERTс и UPDATEs.

Источник

MySQL не использует ключ date_updated для сортировки, но просто использует price ключ, как он используется в WHERE пункт. Вы можете попытаться использовать индексные подсказки:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Добавить что-то вроде

USE KEY FOR ORDER BY  (date_updated)
Источник
Другие вопросы по тегам