ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
25 сообщений из 32, страница 1 из 2
  1  все
Оптимизация запроса
    #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, 14:27
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39638069
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Индекс по (nomer, mes). Других вариантов не вижу.
...
Рейтинг: 0 / 0
28.04.2018, 15:13
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39638079
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
AkinaИндекс по (nomer, mes). Других вариантов не вижу.
не все поля в select входят в аггрегаты.
...
Рейтинг: 0 / 0
28.04.2018, 15:26
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39638083
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
ScareCrow , не понял фразы...
...
Рейтинг: 0 / 0
28.04.2018, 15:36
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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
28.04.2018, 16:16
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 10: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, 11:05
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:35
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 12:49
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:16
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39639395
Exec1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Exec1
Гость
Удаляем:

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

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


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

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

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

Сейчас попробую ..
...
Рейтинг: 0 / 0
03.05.2018, 13:44
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:20
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 14:45
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:21
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 15:28
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:54
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39639625
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
а, ну да
автор
Код: sql
1.
Copying to tmp table           | 5.644352
...
Рейтинг: 0 / 0
03.05.2018, 18:55
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39639748
Exec1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Exec1
Гость
>> агрегация вам тут нужна, не иначе

Так COUNT(nomer) - функция агрегации.
...
Рейтинг: 0 / 0
04.05.2018, 08:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 09:51
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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:05
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39640065
tip78
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tip78
Участник
Exec1>> агрегация вам тут нужна, не иначе

Так COUNT(nomer) - функция агрегации.
не эта ))
а в таблицу скидываете свои обработанные и посчитанные данные раз в минуту и уже с неё берёте
...
Рейтинг: 0 / 0
04.05.2018, 14:24
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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, 14:25
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #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
04.05.2018, 15:00
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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