Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 32, страница 1 из 2
  1  все
28.04.2018, 14:27
    #39638044
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Вырос объем базы значительно.
Возможно ли как то оптимизировать запрос ?

Код: sql
1.
SELECT `mes`, `nomer`, COUNT(*) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2018, 15:13
    #39638069
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Индекс по (nomer, mes). Других вариантов не вижу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2018, 15:26
    #39638079
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
AkinaИндекс по (nomer, mes). Других вариантов не вижу.
не все поля в select входят в аггрегаты.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2018, 15:36
    #39638083
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ScareCrow , не понял фразы...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.04.2018, 16:16
    #39638110
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:
If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is nondeterministic. The server is free to select the value from any row:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 10:05
    #39639205
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
SHOW INDEX FROM alog

alog 	0 	PRIMARY 	1 	id 	A 	1863661 	NULL	NULL		BTREE 		
alog 	0 	id 	1 	id 	A 	1863661 	NULL	NULL		BTREE 		
alog 	1 	nomer_mes 	1 	nomer 	A 	89 	NULL	NULL	YES 	BTREE 		
alog 	1 	nomer_mes 	2 	mes 	A 	143358 	NULL	NULL	YES 	BTREE 		
alog 	1 	nomer 	1 	nomer 	A 	89 	NULL	NULL	YES 	BTREE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 11:05
    #39639238
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ScareCrow , если запрос выполняется (а поскольку автор хочет оптимизации - то выполняется) - либо у автора сравнительно старая версия, либо ONLY_FULL_GROUP_BY отключено. Это как бы очевидно.

Exec1 , лучше показывайте SHOW CREATE TABLE, гораздо нагляднее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 11:05
    #39639239
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Ну и, само собой, EXPLAIN запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:35
    #39639319
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
> SHOW CREATE TABLE alog

alog  | CREATE TABLE `alog` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `mesayc` varchar(3) DEFAULT NULL,
  `day` int(1) DEFAULT NULL,
  `god` int(1) DEFAULT NULL,
  `vremya` time DEFAULT NULL,
  `nomer` varchar(8) DEFAULT NULL,
  `mes` varchar(255) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `lev` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `nomer_mes` (`nomer`,`mes`),
  KEY `nomer` (`nomer`)
) ENGINE=MyISAM AUTO_INCREMENT=19066049 DEFAULT CHARSET=utf8




Код: sql
1.
2.
3.
4.
5.
6.
7.
> EXPLAIN  SELECT `mes`, `nomer`, COUNT(*) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | alog  | index | NULL          | nomer_mes | 795     | NULL | 1882173 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
1 row in set (0.02 sec)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 12:49
    #39639338
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
# cat my.cnf | grep sql_mode
#

Код: sql
1.
2.
3.
4.
5.
6.
> select version();
+------------+
| version()  |
+------------+
| 5.5.38-log |
+------------+
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 13:16
    #39639367
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Exec1
Код: sql
1.
2.
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),

Ну вот зачем тебе ДВА одинаковых индекса?
Exec1
Код: sql
1.
2.
  KEY `nomer_mes` (`nomer`,`mes`),
  KEY `nomer` (`nomer`)


При наличии первого второй практически теряет смысл.

В общем, можешь эти два индекса - уникальный по id и отдельный по nomer безболезненно удалить.

Далее...

Exec1
Код: sql
1.
SELECT `mes`, `nomer`, COUNT(*) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;

Ты отдаёшь себе отчёт, что если одному nomer соответствуют несколько различных mes, то будет выведен случайный из них?

Ещё далее...

У тебя выполняется группировка по полю, потенциально способному быть NULL. Это - правильно? И тебе для таких записей тоже требуется количество? Если хотя бы один раз "нет" - замени COUNT(*) на COUNT(nomer).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 13:44
    #39639395
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Удаляем:

UNIQUE KEY `id` (`id`),
KEY `nomer` (`nomer`)

>> Ты отдаёшь себе отчёт, что если одному nomer соответствуют несколько различных mes, то будет выведен случайный из них?
Да, нужно просто для наглядности, при необходимости разворачивается все сообщения по коду.


>> У тебя выполняется группировка по полю, потенциально способному быть NULL. Это - правильно?
По идее оно не должно быть NULL. Если NULL то это скорее скрытая ошибка.

>> И тебе для таких записей тоже требуется количество?
Нет. Количество только для номеров.

>> Если хотя бы один раз "нет" - замени COUNT(*) на COUNT(nomer).
Да. По сути нужен только подсчет по nomer

Сейчас попробую ..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 14:10
    #39639416
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
> EXPLAIN  SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra                                        |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | alog  | index | NULL          | nomer_mes | 795     | NULL | 1887780 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+----------------------------------------------+
1 row in set (0.08 sec)



Время выполнения (повторно, не знаю берет он из кеша его или нет) запроса 6 сек.
Стало лучше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 14:20
    #39639425
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| Waiting for query cache lock   | 0.000011 |
| checking query cache for query | 0.000060 |
| checking permissions           | 0.000013 |
| Opening tables                 | 0.000023 |
| System lock                    | 0.026756 |
| Waiting for query cache lock   | 0.000373 |
| init                           | 0.000025 |
| optimizing                     | 0.000011 |
| statistics                     | 0.000022 |
| preparing                      | 0.000017 |
| Creating tmp table             | 0.016168 |
| Sorting for group              | 0.000021 |
| executing                      | 0.000015 |
| Copying to tmp table           | 5.652560 |
| Sorting result                 | 0.016986 |
| Sending data                   | 0.000120 |
| end                            | 0.000011 |
| removing tmp table             | 0.000198 |
| end                            | 0.000010 |
| query end                      | 0.000010 |
| closing tables                 | 0.000016 |
| freeing items                  | 0.000014 |
| Waiting for query cache lock   | 0.000009 |
| freeing items                  | 0.001226 |
| Waiting for query cache lock   | 0.000019 |
| freeing items                  | 0.000008 |
| storing result in query cache  | 0.000011 |
| logging slow query             | 0.000009 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
30 rows in set (0.00 sec)



Как возможно уменьшиь "Copying to tmp table"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 14:45
    #39639454
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Exec1Как возможно уменьшиь "Copying to tmp table"Перепиши, скажем, на коррелированный подзапрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT (SELECT aa.mes
        FROM alog aa
        WHERE aa.nomer = a.nomer
        LIMIT 1
       ) mes
     , a.`nomer`
     , COUNT(nomer) AS cnt 
FROM `alog` a
GROUP BY a.`nomer` 
ORDER BY 2 DESC;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 15:21
    #39639487
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Добавил рам диск.

Запустил первоначальный запрос.
(5.65 sec)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000047 |
| Waiting for query cache lock   | 0.000008 |
| checking query cache for query | 0.000010 |
| checking privileges on cached  | 0.000008 |
| checking permissions           | 0.000008 |
| sending cached result to clien | 0.000033 |
| logging slow query             | 0.000008 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+
8 rows in set (0.00 sec)



Запустил "коррелированный подзапрос"
(7 min 31.77 sec)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 15:28
    #39639493
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Ай сорри, первый запрос тот что (5.65 sec)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000023 |
| Waiting for query cache lock   | 0.000008 |
| checking query cache for query | 0.000056 |
| checking permissions           | 0.000011 |
| Opening tables                 | 0.000021 |
| System lock                    | 0.000015 |
| Waiting for query cache lock   | 0.000044 |
| init                           | 0.000024 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000022 |
| preparing                      | 0.000015 |
| Creating tmp table             | 0.000034 |
| Sorting for group              | 0.000010 |
| executing                      | 0.000008 |
| Copying to tmp table           | 5.644352 |
| Sorting result                 | 0.000090 |
| Sending data                   | 0.000093 |
| end                            | 0.000009 |
| removing tmp table             | 0.000053 |
| end                            | 0.000009 |
| query end                      | 0.000008 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000011 |
| Waiting for query cache lock   | 0.000008 |
| freeing items                  | 0.000056 |
| Waiting for query cache lock   | 0.000009 |
| freeing items                  | 0.000007 |
| storing result in query cache  | 0.000008 |
| logging slow query             | 0.000007 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+



Получается рам диск не помог.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 18:54
    #39639624
tip78
tip78
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Exec1SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
я так понимаю, ~2 ляма строк мы сначала посчитали, а потом ОПТОМ положили в память, потому что их надо ОТСОРТИРОВАТЬ
прикольно...
без ORDER BY же за 0.5сек отрабатывает?
агрегация вам тут нужна, не иначе
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.05.2018, 18:55
    #39639625
tip78
tip78
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
а, ну да
автор
Код: sql
1.
Copying to tmp table           | 5.644352
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 08:10
    #39639748
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
>> агрегация вам тут нужна, не иначе

Так COUNT(nomer) - функция агрегации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 09:51
    #39639813
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
>> без ORDER BY же за 0.5сек отрабатывает?

Код: sql
1.
2.
3.
> SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` GROUP BY `nomer`;
...
86 rows in set (6.09 sec)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 14:05
    #39640047
Дормедонт Евлампиевич
Дормедонт Евлампиевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
создал такую же таблицу, нагенерил данных.

Ваш запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT `mes`, `nomer`, COUNT(*) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
...
87 rows in set (0.59 sec)

mysql> explain SELECT `mes`, `nomer`, COUNT(*) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
+----+-------------+-------+------------+-------+-----------------+-----------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key       | key_len | ref  | rows    | filtered | Extra                                        |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | alog  | NULL       | index | nomer_mes,nomer | nomer_mes | 795     | NULL | 1800000 |   100.00 | Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+------+---------+----------+----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

+-----------+
| @@version |
+-----------+
| 5.7.21-20 |
+-----------+



Может стоить добавить аппаратных мощей. Всё-таки они дешевле, чем труд программистов..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 14:24
    #39640065
tip78
tip78
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Exec1>> агрегация вам тут нужна, не иначе

Так COUNT(nomer) - функция агрегации.
не эта ))
а в таблицу скидываете свои обработанные и посчитанные данные раз в минуту и уже с неё берёте
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 14:25
    #39640066
tip78
tip78
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Exec1>> без ORDER BY же за 0.5сек отрабатывает?

Код: sql
1.
2.
3.
> SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` GROUP BY `nomer`;
...
86 rows in set (6.09 sec)


а без group by ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.05.2018, 15:00
    #39640102
Exec1
Exec1
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
>> Дормедонт Евлампиевич
Очень интересный результат. Я так понимаю надо смотреть на :
87 rows in set (0.59 sec)
?
Такое время смогу получить если:
- отключу запросы на добавление новых записей,
- выполню первый раз этот запрос, получу время около 6 сек
- после этого выполню второй раз запрос (который по сути возьмет данные из кеша, скорее всего )

По мощностям, к сожалению, что есть. Потому остается только что то придумывать.

>> а без group by ?

Код: sql
1.
2.
3.
> SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` ORDER BY `cnt` DESC;
...
1 row in set (57.77 sec)



На текущий момент я отключил рам диск, так как предыдущие результаты с рам диском не дали заметного прироста.

На всякий случай контрольный:
Код: sql
1.
2.
3.
> SELECT `mes`, `nomer`, COUNT(nomer) AS cnt FROM `alog` GROUP BY `nomer` ORDER BY `cnt` DESC;
...
86 rows in set (5.82 sec)


Ну да, что с рам диском, что нет.

Рам диск прописывал в конфиг:
tmpdir=/tmp/mysql/
И перезагружал.

Перед этим:
# mount -t tmpfs -o size=1024M tmpfs /tmp/mysql/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 32, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]