MySQL запрос иногда выполняется медленно, иногда быстро

Question

У меня проблема с этим простым запросом MySQL:

select sender as id from message where status=1 and recipient=1

где таблица отправителей имеет несколько миллионов строк.

Когда я запускаю это в SequelPro, в первый раз он работает очень медленно, ~4 секунды или более, а при следующем запуске он работает очень быстро, ~0,018 секунды. Однако, если я запустлю снова через пару минут, он снова сделает то же самое.

Я пытался использовать SQL_NO_CACHE, и он все еще дает мне тот же результат.

Механизм БД - это innoDB, а БД - это кластер MySQL Percona XtraDB. Вот объяснение результатов:

|id|select_type|table  |type|possible_keys         |key |key_len|ref            |row   |Extra
| 1|SIMPLE     |message|ref |recipient,status, sent|sent|12     |const,const    |2989   |NULL

"отправлено" - это индекс из нескольких столбцов (получатель, статус). У кого-нибудь есть идеи, чтобы решить эту проблему?

Спасибо.

Добавлено (из комментария)

CREATE TABLE 'message' (
    'id' int(20) NOT NULL AUTO_INCREMENT, 
    'sender' bigint(20) NOT NULL, 
    'recipient' bigint(20) NOT NULL, 
    'status' int(5) NOT NULL, 
    'date' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY ('id'), 
    KEY 'id' ('id'), 
    KEY 'recipient' ('recipient'), 
    KEY 'sender' ('sender'), 
    KEY 'date' ('date'), 
    KEY 'status' ('status'), 
    KEY 'sent' ('status','recipient')
) ENGINE=InnoDB AUTO_INCREMENT=90224500 DEFAULT CHARSET=latin1;

Answer 1

Эти симптомы указывают на проблемы с кэшированием. Я имею в виду не "кеш запросов", а кеш двигателя.

Насколько большой стол? Насколько велики все активные таблицы?

Какова стоимость innodb_buffer_pool_size?

Я подозреваю, что buffer_pool намного меньше таблиц, и много чего еще происходит. Следовательно, блоки запроса выталкиваются из ОЗУ, и для их возврата требуется несколько десятков операций чтения.

innodb_buffer_pool_size должно быть установлено около 70% доступной оперативной памяти.

Больше (на основе CREATE TABLE)

"Покрытие" INDEX(status, recipient, sender) будет быстрее - ему не придется возвращаться к данным; запрос может быть сделан полностью в индексе.

PRIMARY KEY это ключ, так INDEX(id) является избыточным и может быть DROPped,

KEY это префикс другого ключа избыточен. Я имею в виду (status) в вашем текущем CREATE TABLE,