Mysql Query Optimization

У меня есть следующий запрос SQL:

select expr1, operator, expr2, count(*) as c 
from log_keyword_fulltext 
group by expr1, operator, expr2 
order by c desc limit 2000;

Проблема: count(*)как часть моего заказа по убивает мое приложение, вероятно, потому что он не использует индекс. Я хотел бы знать, есть ли способ сделать это быстрее, например, selectвнутри другого select, или что-то в этом роде.

Мой SELECTобъяснил:

+----+-------------+----------------------+-------+---------------+-------+---------+------+--------+----------------------------------------------+
| id | select_type | table                | type  | possible_keys | key   | key_len | ref  | rows   | Extra                                        |
+----+-------------+----------------------+-------+---------------+-------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | log_keyword_fulltext | index | NULL          | expr1 | 208     | NULL | 110000 | Using index; Using temporary; Using filesort | 
+----+-------------+----------------------+-------+---------------+-------+---------+------+--------+----------------------------------------------+

ОБНОВЛЕНО:

Я пытался сделать такой подзапрос

select * from (select b.expr1,b.operator,b.expr2,count(*) as c 
from log_keyword_fulltext b group by b.expr1,b.operator,b.expr2) x 
order by x.c desc limit 2000;

это работает, но не быстрее, вот объяснение:

+----+-------------+------------+-------+---------------+-------+---------+------+--------+----------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows   | Extra          |
+----+-------------+------------+-------+---------------+-------+---------+------+--------+----------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL  | NULL    | NULL |  38398 | Using filesort | 
|  2 | DERIVED     | b          | index | NULL          | expr1 | 208     | NULL | 110000 | Using index    | 
+----+-------------+------------+-------+---------------+-------+---------+------+--------+----------------+

Вы можете проверить это сейчас, оно больше не использует временное, но оно все еще с той же производительностью. Любая рекомендация?

11.12.2008 13:22:47
6 ОТВЕТОВ
РЕШЕНИЕ

Что мне не хватает? Я не вижу предложения WHERE. Мне кажется, вы запрашиваете сканирование таблицы.

Если вы рассчитываете на свое предложение «LIMIT», вам не повезло - это совокупный расчет COUNT.

1
11.12.2008 19:59:34

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

Не уверен, что это работает в MySQL, но в PostreSQL или Oracle, это было бы

create foo as 
   select expr1, operator, expr2, count(*) as c
   from log_keyword_fulltext 
   group by expr1, operator, expr2;
select * from foo order by c desc limit 2000;

Кроме того, вам нужно будет выполнить все подсчеты, чтобы отсортировать их, поэтому предложение limit не помешает ему выполнить все эти вычисления.

-1
11.12.2008 16:09:25
@Nixar, что бы объяснить «использование временного» в плане запроса? Хорошо, неважно
Paul Tomblin 11.12.2008 14:41:57

Всегда пытайтесь подсчитать какой-то один столбец вместо подсчета (*), поскольку он учитывает перестановку каждого столбца каждой строки. Так что это занимает больше времени

Eg:

select expr1, operator, expr2, count(expr1) as c 
from log_keyword_fulltext 
group by expr1, operator, expr2 
order by c desc limit 2000;
1
11.12.2008 13:50:49
? Это не имеет никакого смысла вообще; если это одна таблица, то число строк одинаково, используете ли вы (*) или (expr1), и переводчик / оптимизатор запросов будет выполнять одно и то же действие независимо от того, какую из двух вы выберете.
Jason S 11.12.2008 14:16:41
это не имеет смысла, потому что, если expr1 принимает значение null, он не будет представлять все записи
VP. 11.12.2008 15:18:44
Как часть стандарта SQL задокументировано, что COUNT (*) вычисляет без ссылки на какие-либо поля.
dkretz 11.12.2008 19:58:43

Что вы подразумеваете под "убить ваше приложение"? Каков контекст? Как часто вы запускаете этот запрос? Что происходит с базой данных во время выполнения этого запроса? Этот конкретный результат должен быть в режиме реального времени? Каковы условия (вставки / с, выбирает / с, размер БД и т. Д.)

Вот что вы могли бы сделать:

  1. Сохраните счет в отдельной таблице, которую вы будете обновлять с помощью триггеров при вставке / удалении

  2. Если вы не можете заставить MySQL сделать это простым смахиванием таблицы с помощью какой-то магии, попробуйте хранимую процедуру, чтобы сделать что-то вроде (псевдокод):

    CREATE TEMP TABLE t (e1 EXP_T, op OP_T, e2 EXP_T, count INTEGER)
    ADD AN INDEX ON count
    FOR EACH LINE OF SELECT exp1,operator,exp2 FROM log_blah DO
           UPDATE t SET count=count+1 WHERE exp1=e1 AND operator=op AND exp2=e2
           IF IT DOES NOT WORK INSERT INTO t VALUES (exp1,operator,exp2,1)
    DONE
    SELECT * FROM t ORDER BY count DESC LIMIT 2000

1, вероятно, то, что вы хотите. И забудьте индексы, этот запрос в любом случае должен сильно ударить по всей таблице.

0
11.12.2008 14:10:50
это ежедневный хрон. Он висит с более чем 200000 записей. Я проверю ваш совет!
VP. 11.12.2008 14:29:02
Разве ваши "2" не совсем соответствуют тому, что я сказал, и за что вы меня отдали?
Paul Tomblin 11.12.2008 14:44:30
КСТАТИ: Я не бросаю вам вызов, я просто хочу знать, не ясно ли я.
Paul Tomblin 11.12.2008 14:59:52
Ну, я предполагал, что это был @niXar, который проголосовал за него, но это не очень важно по сравнению с тем, отличается ли его "2" от "create foo as select expr1, operator, expr2, count (*) как c из группы log_keyword_fulltext by expr1, operator, expr2 "
Paul Tomblin 11.12.2008 16:07:18

Вы выполняете запрос, который должен сканировать всю таблицу, он не масштабируется. Там нет предложения WHERE, поэтому он абсолютно необходим для сканирования всего этого.

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

2
11.12.2008 14:29:10

Лучший способ предотвратить сканирование таблицы - добавить индекс покрытия для тех полей, к которым вы регулярно обращаетесь. Существует одноразовая стоимость для создания индекса. Кроме того, для операций INSERT и DELETE в таблице предусмотрены некоторые дополнительные расходы, поэтому индекс можно обновлять.

Индексы покрытия не позволяют базе данных считывать всю запись в памяти, чтобы получить доступ к значениям для нескольких полей, которые вас интересуют. Весь запрос может быть выполнен вне индекса.

ALTER TABLE `log_keyword_fulltext` ADD INDEX `idx_name`(expr1, operator, expr2)

Если это не фактические поля, а операции над полями, например left (foo, 20), вы можете индексировать часть поля, которую вы будете использовать в будущих предложениях SELECT или WHERE.

Смотрите эту страницу для других идей оптимизации.

0
2.03.2019 07:29:42
привет, уже делаю вот мои индексы. моя проблема в том, что моя таблица в производстве имеет 12.277.737 записей. С уважением, Виктор
VP. 11.12.2008 19:50:31