Я бы посоветовал вам научиться использовать EXPLAIN. для анализа плана базы данных по оптимизации запросов. Также см. презентацию барона Шварца EXPLAIN Demystified (ссылка на PDF-файл его слайды есть на этой странице).
Узнайте, как создавать индексы — это не то же самое, что первичный ключ или псевдоключ с автоинкрементом. См. презентацию Дополнительные сведения об искусстве индексирования, автор Ёсинори Мацунобу.
Ваша таблица может использовать индекс для CP_FLAG и WEB_STATUS.
CREATE INDEX CW ON RAW_LAW_20100503 (CP_FLAG, WEB_STATUS);
Это помогает искать подмножество строк на основе вашего условия cp_flag.
Тогда вы все равно столкнетесь с досадной неэффективностью MySQL с GROUP BY запросами. Он копирует промежуточный набор результатов во временный файл на диске и сортирует его там. Дисковый ввод-вывод снижает производительность.
Вы можете увеличивать параметр конфигурации sort_buffer_size до тех пор, пока он не станет достаточно большим, чтобы MySQL мог сортировать набор результатов в памяти, а не на диске. Но это может не сработать.
Возможно, вам придется прибегнуть к предварительному расчету необходимых вам COUNT() и периодически обновлять эту статистику.
Комментарий от @Marcus дал мне еще одну идею. Вы группируете по веб-статусу, и набор различных значений веб-статуса представляет собой довольно короткий список, и они не меняются. Таким образом, вы можете запускать отдельный запрос для каждого отдельного значения и генерировать нужные результаты намного быстрее, чем при использовании запроса GROUP BY, который создает временную таблицу для сортировки. Или вы можете запустить подзапрос для каждого значения состояния и UNION их вместе:
(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 200)
UNION
(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 404)
UNION
(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 304)
UNION
...etc...
ORDER BY 1 DESC;
Поскольку ваш покрывающий индекс включает CP_FLAG и WEB_STATUS, этим запросам не нужно считывать фактические строки в таблице. Они только читают записи в индексе, к которым они могут получить доступ намного быстрее, потому что (а) они находятся в отсортированном дереве и (б) они могут быть кэшированы в памяти, если вы выделите достаточно для своего key_buffer_size.
Отчет EXPLAIN, который я пробовал (с 1 млн строк тестовых данных), показывает, что он хорошо использует индексы и не создает временную таблицу:
+------+--------------+------------------+------+--------------------------+
| id | select_type | table | key | Extra |
+------+--------------+------------------+------+--------------------------+
| 1 | PRIMARY | RAW_LOG_20100504 | CW | Using where; Using index |
| 2 | UNION | RAW_LOG_20100504 | CW | Using where; Using index |
| 3 | UNION | RAW_LOG_20100504 | CW | Using where; Using index |
| NULL | UNION RESULT | <union1,2,3> | NULL | Using filesort |
+------+--------------+------------------+------+--------------------------+
Using filesort для последней строки просто означает, что она должна сортироваться без индекса. Но сортировка трех строк, созданных подзапросами, тривиальна, и MySQL делает это в памяти.
При разработке оптимальных решений для баз данных редко бывают простые ответы. Многое зависит от того, как вы используете данные и какие запросы имеют более высокий приоритет для быстрого выполнения. Если бы был один простой ответ, который работал бы при любых обстоятельствах, программное обеспечение просто включило бы этот дизайн по умолчанию, и вам не пришлось бы ничего делать.
Вам действительно нужно прочитать множество руководств, книг и блогов, чтобы понять, как максимально эффективно использовать все доступные вам функции.
Да, я бы по-прежнему рекомендовал использовать индексы. Ясно, что это не работало раньше, когда вы запрашивали 100 миллионов строк без преимуществ индекса.
Вы должны понимать, что вы должны проектировать индексы, которые приносят пользу конкретному запросу, который вы хотите выполнить. У меня нет возможности узнать, подходит ли индекс, который вы только что описали в своем комментарии, потому что вы не показали другой запрос, который пытаетесь ускорить.
Индексация — сложная тема. Если вы определите индекс для неправильных столбцов или если вы получите столбцы в неправильном порядке, он может быть неприменим для данного запроса. Я поддерживаю разработчиков SQL с 1994 года и так и не нашел ни одного краткого правила, объясняющего, как проектировать индексы.
Кажется, вам нужен наставник, потому что вы находитесь на этапе, когда вам нужно получить ответы на множество вопросов. Есть ли на вашей работе кто-нибудь, кого вы могли бы попросить помочь вам?
person
Bill Karwin
schedule
04.05.2010
