#mysql #sql #database
#mysql #sql #База данных
Вопрос:
Это запрос, который я сейчас выполняю:
SELECT * FROM `datalog`
WHERE world_id IN (2)
AND action IN (0,1,2,8,9,10,11,13,14,15)
AND x = -184.0 AND y = 98.0 AND z = 141.0
ORDER BY data_id DESC;
К сожалению, это занимает много времени, и я не знаю почему (5 секунд или более при 14 миллионах записей в базе данных). У меня есть индекс по world_id и action (поскольку существует не более 7 миров и 20 действий). Как еще я мог бы ускорить поиск?
РЕДАКТИРОВАТЬ — значение explain: SIMPLE datalog ALL NULL NULL NULL NULL 13510263 С использованием where; С использованием filesort
Комментарии:
1. Вероятно, помогли бы правильные индексы в data_id, x, y и z данных.
2. Каков результат
EXPLAIN SELECT * FROM datalog...(остальной части вашего запроса)?3. ceejayoz: Я бы не подумал, что индексы на них могут быть полезны, поскольку они могут варьироваться от -100,000.0 до 100,000.0 (возможно, больше)
4. Объясните: ПРОСТОЙ журнал данных ALL NULL NULL NULL NULL 13510263 С использованием where; С использованием filesort
5. Да, вы могли бы изменить порядок в своем клиентском коде. Кстати, не считается лучшей практикой полагаться на значение PK с автоматическим добавлением в качестве индикатора новизны; используйте значение datetime для этой цели. Однако многие люди полагаются на него для этой цели. Но в мире пуристов PK — это не что иное, как уникальный идентификатор для кортежа.
Ответ №1:
Попробуйте добавить индексы к x, y и z.
Вы сказали, что, по вашему мнению, это не сработает, поскольку они могут содержать большой диапазон значений.
Пока вы используете тип таблицы, который поддерживает BTREE индексы (это единственный тип индекса, который поддерживают MyISAM и INNODB), это не должно быть правдой. Если вы используете HASH индекс, это может иметь место, поскольку ему потребуется индексировать каждое значение. Но с BTREE индексом MySQL может быстро выполнить сортировку по определенному значению в индексе. Вот почему он также может использовать BTREE индексы в запросах с операторами сравнения ( < , > и т.д.)
Вы можете увидеть больше здесь:http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
Комментарии:
1. Спасибо, в итоге это сделало его очень быстрым (миллисекунды для 15 миллионов журналов) — я уже сократил его до нескольких секунд, удалив group by благодаря Тиму, но индексация x, y, z была вишенкой на вершине!
Ответ №2:
Вы могли бы попробовать заменить ваше условие в поле действия на AND action < 16 ?
Комментарии:
1. Хотя Action не меньше 16, это могут быть только эти конкретные значения.
2. Это не помогло бы, поскольку он сказал, что у него есть индекс для действия, и он ищет конкретные действия, а не все действия меньше 16.
Ответ №3:
Понятия не имею, сработает ли это, но вы пробовали изменить порядок ваших условий? x = -184.0, вероятно, будет быстрее, чем действие в (…). Если MySQL использует короткое замыкание, это может ускорить его.
Комментарии:
1. Нет, на самом деле это было медленнее. Если я удалю все элементы x, y, z вместе, это будет намного быстрее, чем с x, y, z
Ответ №4:
В дополнение к тому, что сказал @patapizza, вы должны использовать IN () только в том случае, если у вас в основном есть случайные вещи для поиска, так что: world_id = 2 и action < 16 , скорее всего, помогут.
Вероятно, вам нужны индексы для других столбцов, на которые вы ссылаетесь в WHERE инструкции, и, вероятно, для data_id также. Однако, как сказал @AJ, опубликуйте вывод EXPLAIN , и можно будет точно определить, почему он медленный.
Комментарии:
1. Я не могу использовать < 16, потому что мне нужно сопоставить определенные значения.
2. Вывод explain: SIMPLE datalog ALL NULL NULL NULL NULL 13510263 С использованием where; С использованием filesort
Ответ №5:
Я бы попробовал составной индекс: (actionid, worldid).
Комментарии:
1. У меня уже есть составной индекс для этих двух плюс еще один столбец (player — не актуально в данной ситуации)
2. Каков порядок столбцов в составном индексе def?