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

У меня проблема с этим простым запросом 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;

mysql php innodb percona xtradb
person zangetsKid    schedule 11.03.2016    source источник
comment
Вы можете использовать ограничение в своем запросе mysql. например select sender as id from message where status=1 and recipient=1 LIMIT 10 ИЛИ вы также можете использовать смещение, например select sender as id from message where status=1 and recipient=1 LIMIT 10 OFFSET 15   -  person Sanjay Chaudhari    schedule 11.03.2016
comment
@SanjayChaudhari Спасибо за ответ. Однако я хочу получить все результаты из таблицы. Вот почему я не использую LIMIT. Но я попытаюсь зациклить запрос для каждых 100 записей и посмотреть, будет ли это иметь какое-либо значение, даже если это не очень хорошее решение, поскольку в будущем запись будет расти.   -  person zangetsKid    schedule 11.03.2016
comment
Посмотрите, правильно ли используется ваш индекс. Кроме того, сделайте резервную копию таблицы и создайте ее заново. Затем импортируйте записи из дампа один раз. Разрозненные записи могут сойтись вместе и немного повысить производительность.   -  person Bimal Poudel    schedule 11.03.2016

Ответы (1)


arrow_upward
1
arrow_downward

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

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

Каково значение 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.

person Rick James    schedule 11.03.2016
comment
таблица составляет около 80 миллионов строк. Значение innodb_buffer_pool_size = 2 ГБ, а размер оперативной памяти — 8 ГБ. Раньше я настраивал innodb_buffer_pool_size на 5 ГБ, но когда я тестировал много запросов, например около 1000 запросов в секунду, MySQL внезапно умирал. Но когда я изменил его на 2 ГБ, я никогда не видел, чтобы MySQL умирал сам по себе. - person zangetsKid; 11.03.2016
comment
Не составляйте сразу большой список. Вы часто можете увидеть ошибку MySQL server go away при слишком быстром запросе несколько раз. Второй немедленный запрос, вероятно, будет выполняться быстрее, возможно, из-за производительности жесткого диска, когда головка жесткого диска находится очень близко к данным, которые нужно прочитать следующим. - person Bimal Poudel; 11.03.2016
comment
@BimalPoudel Я пытался использовать LIMIT в таком запросе: select sender as id from message where status=1 and recipient=1 LIMIT 0,100, но у меня все еще та же проблема. - person zangetsKid; 11.03.2016
comment
Укажите SHOW CREATE TABLE; Мне не хватает чего-то тонкого. - person Rick James; 11.03.2016
comment
@RickJames Вот сообщение SHOW CREATE TABLE 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; - person zangetsKid; 14.03.2016
comment
Попробуйте этот трехколоночный индекс. - person Rick James; 14.03.2016
comment
@RickJames Что вы подразумеваете под KEY, который является префиксом другого ключа, является избыточным? Кроме того, зачем мне индексировать отправителя, если его нет в предложении WHERE? Но я попробую и дам вам знать, что произойдет. - person zangetsKid; 14.03.2016
comment
sender делает его покрывающим индексом. - person Rick James; 14.03.2016
comment
INDEX(a), INDEX(a,b) -- в первом нет необходимости, так как второе может обрабатывать, скажем, WHERE a = 123, хотя b не упоминается. - person Rick James; 14.03.2016