ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Огромная mysql база, оптимизировать SELECT
21 сообщений из 21, страница 1 из 1
Огромная mysql база, оптимизировать SELECT
    #38701822
system30101
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
system30101
Гость
Добрый день.
Есть 3 таблицы, table1 размер - 2 149 114 строк, table2 - 92 221 527 строк, table3 – статична.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE `table1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ct1` int(11) unsigned NOT NULL,
  `ct2` int(11) unsigned NOT NULL,
  `ct3` varchar(255) NOT NULL,
  `ct4` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ct4_idx` (`ct4`) USING BTREE,
  KEY `ct1_idx` (`ct1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2149114 DEFAULT CHARSET=utf8;


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE `table2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ct1` int(11) unsigned DEFAULT NULL,
  `ct2` int(11) unsigned DEFAULT NULL,
  `ct3` int(11) unsigned DEFAULT NULL,
  `ct4` int(11) unsigned DEFAULT NULL,
  `ct5` int(11) unsigned DEFAULT NULL,
  `ct6` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ct2_idx` (`ct2`) USING BTREE,
  KEY `ct1_idx` (`ct1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=92221527 DEFAULT CHARSET=utf8;


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE `table3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ct1` varchar(255) NOT NULL,
  `ct2` varchar(255) NOT NULL,
  `ct3` varchar(255) NOT NULL,
  `ct4` int(11) unsigned NOT NULL,
  `ct5` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ct5_idx` (`ct5`) USING BTREE,
  KEY `ct4_idx` (`ct4`) USING BTREE,
  KEY `ct3_idx` (`ct3`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=utf8;



Видно сколько в каждой таблице row.
UPDATE и INSERT нормально вроде работают, а вот запрос вот такой не могу оптимизировать что бы быстро выводил данные:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT
Sum(table2.ct3) AS wct3,
Sum(table2.ct4) AS bct4,
CEIL(Avg(table2.ct6)) AS bact6,
table3.ct1,
table3.`ct5`,
table3.ct3,
table3.ct2
FROM
table2
INNER JOIN table3 ON table3.ct4 = table2.ct2
INNER JOIN table1 ON table1.ct1 = table2.ct1
WHERE
table1.ct4 > 3424 AND
table1.ct4 < 3624 
AND table3.` ct5` = 6
GROUP BY
table2. ct2,
table3. ct1,
table3.` ct5`
ORDER BY bact6 DESC



Время выполнения запроса примерно 10-20 минут.
Конфиг mysql:
key_buffer_size = 254M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 32M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1536M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 70

Как можно увеличить скорость запроса?
...
Рейтинг: 0 / 0
21.07.2014, 12:43:03
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38701836
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
system30101Видно сколько в каждой таблице row.auto_increment <> количество записей. Ну да ладно, будем считать, что удалений и сбившихся вставок было мало (а с самим автоинкрементом никто не игрался), и записей там примерно столько. Но
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT
 Sum(table2.ct3) AS wct3,
 Sum(table2.ct4) AS bct4,
 CEIL(Avg(table2.ct6)) AS bact6,
 table3.ct1, table3.`ct5`, table3.ct3, table3.ct2
FROM
table2
INNER JOIN table3 ON table3.ct4 = table2.ct2
INNER JOIN table1 ON table1.ct1 = table2.ct1
WHERE
 table1.ct4 > 3424 AND table1.ct4 < 3624 
 AND table3.` ct5` = 6
GROUP BY
 table2. ct2, table3. ct1, table3.` ct5`
ORDER BY bact6 DESC

miksoftА за поля, которых нет в группировке, но есть в секциях SELECT и ORDER BY, пожалуй, в аду скоро отдельный котел организуют.И вообще, где explain?
...
Рейтинг: 0 / 0
21.07.2014, 12:51:11
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38701842
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
system30101,

У вас в секциях SELECT и ORDER BY используются поля, которых нет в GROUP BY и которые не "обернуты" агрегатными функциями. Вы в курсе, к чему это может привести?
...
Рейтинг: 0 / 0
21.07.2014, 12:52:43
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702035
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторВы в курсе, к чему это может привести
к тормозам, да.
...
Рейтинг: 0 / 0
21.07.2014, 14:59:34
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702039
alex564657498765453
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alex564657498765453
Участник
походу быстрее человек на марс долетит чем мы дождёмся эксплейна от ТС
...
Рейтинг: 0 / 0
21.07.2014, 15:01:28
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702148
system30101
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
system30101
Гость
Код: plaintext
1.
2.
1	SIMPLE	table1	range	ct4_idx,ct1_idx	ct4_idx	4		5929	Using where; Using temporary; Using filesort
1	SIMPLE	table2	ref	ct2_idx,ct1_idx	ct1_idx	5	wot.table1.ct1	24	Using where
1	SIMPLE	table3	ref	ct5_idx,ct4_idx,ct3_idx	ct4_idx	4	wot.table2.ct2	1	Using where
...
Рейтинг: 0 / 0
21.07.2014, 16:56:03
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702164
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
план с виду нормальный, но 142к (максимум) обрабатываемых промежуточных записей - и 20 минут?
попробуйте всё-таки правильный групбай написать, может, быстрее станет :)
кстати, а зачем группировать (и выводить) table3.` ct5`, если оно по условию равно 6?
...
Рейтинг: 0 / 0
21.07.2014, 17:04:29
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702211
alex564657498765453
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alex564657498765453
Участник
tanglirплан с виду нормальный, но 142к (максимум) обрабатываемых промежуточных записей - и 20 минут?
попробуйте всё-таки правильный групбай написать, может, быстрее станет :)
кстати, а зачем группировать (и выводить) table3.` ct5`, если оно по условию равно 6?

врятли это на чтото влияет сдесь существенно, ну да ладно.


другое дело сортировка! сколько там записей в выводе???
посмотрите как измениться время без сортировки, если сильно упадёт, то вопрос собственно про оптимизацию отпадает
...
Рейтинг: 0 / 0
21.07.2014, 17:47:52
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702327
system30101
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
system30101
Гость
Разница в запросах
было: 535.457 секунд
стало: 494.082 секунд
при запросе:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT
CEIL(Avg(table2.ct6)) AS ct6
FROM
table2
INNER JOIN table3 ON table3.ct4 = table2.ct2
INNER JOIN  ON table1.ct1 = table2.ct1
WHERE
table1.ct4 > 3424 AND
table1.ct4 < 3624 
AND table3.`ct5` = 6
GROUP BY
table2.ct2
ORDER BY ct6 DESC
...
Рейтинг: 0 / 0
21.07.2014, 20:42:53
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702328
system30101
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
system30101
Гость
alex564657498765453,

строк в запросе как правило меньше 100, но можно поставить LIMIT 10, но всё равно скорости не добавляет.
...
Рейтинг: 0 / 0
21.07.2014, 20:45:02
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702474
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
system30101....

Как можно увеличить скорость запроса?


1. преагрегацией
2. купить ССД, памяти и выделенку
3. уважить эстетов Танглира и Миксофта -- поставьте МАХ()
на все незаагрегировные поля, которые не входят в
ГРОУП БУ.
4. Проанализоровать задачу -- может уменьшить
запрашиваемый период
5. добавить индексов чтоб хватило на селект
6. проанализоровать данные, может индексы уникальные?
тогда лучше иностраный ключ поставить на ИД...
7. чем вас не устраивает 20 минут? поставьте на ночь :-).
...
Рейтинг: 0 / 0
22.07.2014, 06:32:09
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702490
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
javajdbcдобавить индексов чтоб хватило на селектсудя по эксплейну, нужные индексы есть и используются
javajdbcиностраный ключперевожу - имелся в виду foreign key :)
...
Рейтинг: 0 / 0
22.07.2014, 08:28:44
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702638
alex564657498765453
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alex564657498765453
Участник
system30101alex564657498765453,

строк в запросе как правило меньше 100, но можно поставить LIMIT 10, но всё равно скорости не добавляет.

я имел ввиду именно 100 - лимит ничего не даёт, всеравно 100 строк надо сортировать, я просто хотел убедиться что не 10000 000 сортируеться..

а вопрос второй, если без групировки брать - сколько строк получиться???

просто мыж понимаем что

select sum(f)
from table

если в таблице 100 строк или 100 000 000 - то время будет очень разным, и никакой индекс сдесь не поможет.
самое медленное действие это чтение с шдд. если ещо удачно лежат 100 000 000 строк так, что ни какие две записи не лежат в одной странице(не могут быть щитаны за одно атомарное (чтение одного кластера) обращение) - то это будет 100 лямов атомарных чтений, и если єто 0,1 милисекунда(ссд диск оптимистично) то подтребуеться на перечитывание этого дела 10 000 секунд, типо три часа. и тут хоть что делай, выше не прыгнешь.

- дык сколько записей в групировку идёт???
(у тя не ссд диск полагаю, там время чтения от пары мс до 10мс)
...
Рейтинг: 0 / 0
22.07.2014, 11:03:55
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38702689
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
tanglirjavajdbcдобавить индексов чтоб хватило на селектсудя по эксплейну, нужные индексы есть и используютсяточно не хватает ALTER table1 ADD KEY `ct4_ct1_idx` (CT4,CT1);
покрывающие индексы... скорее всего, не помогут, хотя тоже можно попробовать, зависит от селективности t2.ct1 и (t3.c5,t3.c4)
...
Рейтинг: 0 / 0
22.07.2014, 11:50:32
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703252
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Cygapb-007tanglirпропущено...
судя по эксплейну, нужные индексы есть и используютсяточно не хватает ALTER table1 ADD KEY `ct4_ct1_idx` (CT4,CT1);
покрывающие индексы... скорее всего, не помогут, хотя тоже можно попробовать, зависит от селективности t2.ct1 и (t3.c5,t3.c4)


кстати, у меня возникла непонятная идея:

а что если придумать синтакс выборке по индексу.
Точнее разрешить (с некими ограничениями) использовать
имя индекса в FROM секции

Это будет функционально совпадать с случаем
Using index в Ехтра секции Експлейна.
...
Рейтинг: 0 / 0
22.07.2014, 18:37:03
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703274
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
автора что если придумать синтакс выборке по индексу.
Точнее разрешить (с некими ограничениями) использовать
имя индекса в FROM секции

доку почитайте наконец. в части FORCE INDEX
...
Рейтинг: 0 / 0
22.07.2014, 19:11:27
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703283
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ScareCrowавтора что если придумать синтакс выборке по индексу.
Точнее разрешить (с некими ограничениями) использовать
имя индекса в FROM секции

доку почитайте наконец. в части FORCE INDEX


да про форсе-индех -- это то понятно.

Мне интересно что мешает использовать
индекс как отлично организованое материалайзед-вью
(кроме отсутсвия синтакса)
...
Рейтинг: 0 / 0
22.07.2014, 19:32:15
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703291
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
javajdbcScareCrowпропущено...


доку почитайте наконец. в части FORCE INDEX


да про форсе-индех -- это то понятно.

Мне интересно что мешает использовать
индекс как отлично организованое материалайзед-вью
(кроме отсутсвия синтакса)

ничего не мешает. все пользуются. "покрывающий индекс" называется
...
Рейтинг: 0 / 0
22.07.2014, 19:38:13
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703301
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ScareCrow,

спасибо за разяснение прописных истин.
Но вопрос был не в этом.
...
Рейтинг: 0 / 0
22.07.2014, 20:13:46
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703397
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
javajdbc,

ваши голоса в голове говорят для меня загадками.
...
Рейтинг: 0 / 0
23.07.2014, 00:06:03
| Ответить | Цитировать | Написать  
Огромная mysql база, оптимизировать SELECT
    #38703410
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
javajdbcкстати, у меня возникла непонятная идея:

а что если придумать синтакс выборке по индексу.
Точнее разрешить (с некими ограничениями) использовать
имя индекса в FROM секции

Это будет функционально совпадать с случаем
Using index в Ехтра секции Експлейна.пофантазирую тоже малость, попредметнее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT
   table3.ct1, table3.ct5, table3.ct3, table3.ct2
  ,Sum(table2.ct3) AS wct3
  ,Sum(table2.ct4) AS bct4
  ,CEIL(Avg(table2.ct6)) AS bact6
FROM KEY Table1.IX_ct4_ct1 t1 -- (покрывающий) индекс на Table1 (ct4,ct1)
JOIN table2 ON table2.ct1 = t1.ct1
JOIN table3 ON table3.ct4 = table2.ct2 AND table3.ct5 = 6
WHERE t1.ct4 > 3424 AND t1.ct4 < 3624 
GROUP BY table2. ct2, table3. ct1, table3.ct5
ORDER BY bact6 DESC

И какой в этом смысл? Индекс создаем сами, явно его указываем в запросе вместо таблицы (чтобы добиться в Extra строки Using index)
При этом мы выполняем (теоретически) вместо компилятора анализ статистики и навязываем своё вИдение оптимального выполнения запроса.
Так ведь и сейчас никто не запрещает использовать для этого FROM table1 t1 USE KEY(IX_ct4_ct1) .
Прямой выгоды 0, ятд, потому как при наличии индекса компилятор и сам в состоянии построить оптимальный план выполнения.

Другое дело, если бы сервер подсказал, что для более оптимального выполнения запроса с учетом имеющейся статистики было бы неплохо построить вот такой индекс, дал бы его описание, ну, и оценил бы в процентах повышение эффективности обработки с учетом нового индекса.
MS SQL это умеет, MySQL - хз...
...
Рейтинг: 0 / 0
23.07.2014, 00:47:24
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Огромная mysql база, оптимизировать SELECT
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38703252&tid=1834470]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 41ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 235ms
total:  362ms
созд. / загр.: 362ms
x
x
Закрыть


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