SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 19.04.2019 17:27:31

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Оптимизация запроса выборки из двух таблиц

Добрый день.

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

Мы делаем запрос на подбор товаров по разным параметрам (из таблицы 150тыс записей) [ВЫБОРКА 1].
Получаем Goods_ID товаров, что бы затем получить по нужным товарам данные (характеристики). До этого момента все Ок.

Далее нам нужно узнать в каком разделе (Goods_Sections.Section_ID) какое количество товаров из найденных.
В каких разделах содержится товар мы храним в таблице Goods_Sections. В ней всего два столбца:
Goods_ID | Section_ID
Записей 450 тыс. Есть все индексы.

Мы делаем это вот таким запросом [ВЫБОРКА 2]:


    SELECT Goods_Sections.Section_ID, Count(Goods_Sections.Section_ID) as Col
    FROM Goods_Sections
    WHERE
    Goods_Sections.Goods_ID in (147945,146358,146371,...номера всех товаров из ВЫБОРКИ 1)
   
    GROUP BY Goods_Sections.Section_ID
 


Если в ВЫБОРКА 1 мы получаем большое кол-во товаров (50-60 тыс), то ВЫБОРКА 2 выполняется довольно медленно (0.5-0.8 сек).
Если товаров до 1000шт., то выполняется всё мгновенно.

Подскажите, может быть нужно каким-то другим способом "тащить" номера товаров из ВЫБОРКИ 1 в ВЫБОРКУ 2 ?

Неактивен

 

#2 19.04.2019 18:22:11

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Оптимизация запроса выборки из двух таблиц

покажите:
1) show create table `Goods_Sections`;

2) explain выборка 2 с большим кол-вом товаров

3) flush status;
выборка 2 с большим кол-вом товаров;
show status like 'Created%';

Неактивен

 

#3 22.04.2019 17:13:32

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Re: Оптимизация запроса выборки из двух таблиц

1) show create table `Goods_Sections`;
CREATE TABLE `Goods_Sections` (
`Goods_ID` int(10) unsigned NOT NULL DEFAULT '0',
`Section_ID` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`Section_ID`,`Goods_ID`),
KEY `Section_ID_ind` (`Section_ID`),
KEY `Goods_ID_ind` (`Goods_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251


2) explain выборка 2 с большим кол-вом товаров
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | Goods_Sections | range | Goods_ID_ind | Goods_ID_ind | 4 | NULL | 14144 | Using index condition; Using temporary; Using filesort


3) show status like 'Created%';
Variable_name | Value
Created_tmp_disk_tables | 0
Created_tmp_files | 0
Created_tmp_tables | 0

Эту выборку производил так:
mysql < mysql_query_test.txt

Содержание mysql_query_test.txt:


    USE shop;
    flush status;
    SELECT Goods_Sections.Section_ID, Count(Goods_Sections.Section_ID) as Col
    FROM Goods_Sections
    WHERE
    Goods_Sections.Goods_ID in (147945,146358,146371 ...и т.д. все товары из ВЫБОРКИ 1)
    GROUP BY Goods_Sections.Section_ID;
   
    show status like 'Created%';
 

Неактивен

 

#4 22.04.2019 18:21:12

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Оптимизация запроса выборки из двух таблиц

для данного запроса нужен индекс (`Goods_ID`,`Section_ID`)
т.е. или заменить primary key или если тот порядок нужен для других запросов, то добавить ещё один составной индекс

обратите внимание, у вас избыточные индексы: в данный момент KEY `Section_ID_ind` (`Section_ID`) нужно удалить
см FAQ №5

Неактивен

 

#5 22.04.2019 18:57:39

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Re: Оптимизация запроса выборки из двух таблиц

Добавил индекс, но время запроса от этого только увеличилось.
Было 0.5-0.8 сек стало 0,7-0.9 сек

Вот EXPLAIN
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | Goods_Sections | range | Goods_ID_ind,Goods_ID | Goods_ID | 4 | NULL | 748 | Using where; Using index; Using temporary; Using filesort

show create table `Goods_Sections`;
CREATE TABLE `Goods_Sections` (
`Goods_ID` int(10) unsigned NOT NULL DEFAULT '0',
`Section_ID` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`Section_ID`,`Goods_ID`),
KEY `Section_ID_ind` (`Section_ID`),
KEY `Goods_ID_ind` (`Goods_ID`),
KEY `Goods_ID` (`Goods_ID`,`Section_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251


Про избыточность Section_ID_ind, спасибо. Уберем.

Неактивен

 

#6 22.04.2019 19:32:04

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Re: Оптимизация запроса выборки из двух таблиц

Добавлю, что если primary key поменять на (`Goods_ID`,`Section_ID`), то при выборке используется он, но время опять же 0,7-0.9 сек.

Посоветуйте, что еще проверить?

Неактивен

 

#7 22.04.2019 20:29:17

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Оптимизация запроса выборки из двух таблиц

0,7-0.9 сек это при однократном применении после создания нового индекса? меняется ли время, если выполнить запрос дважды?

какая версия MySQL?

Неактивен

 

#8 23.04.2019 18:22:03

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Re: Оптимизация запроса выборки из двух таблиц

Время постоянное при любом кол-ве запросов. Пробовали 10 раз. (естественно с SQL_NO_CACHE)

версия:
mysql  Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Неактивен

 

#9 25.04.2019 20:27:30

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Оптимизация запроса выборки из двух таблиц

Oparin написал:

2) explain выборка 2 с большим кол-вом товаров
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | Goods_Sections | range | Goods_ID_ind | Goods_ID_ind | 4 | NULL | 14144 | Using index condition; Using temporary; Using filesort


3) show status like 'Created%';
Variable_name | Value
Created_tmp_disk_tables | 0
Created_tmp_files | 0
Created_tmp_tables | 0

только сейчас заметил, что не согласуется explain (показывающий Using temporary) и show status (где все по нулям)

ещё можно попробовать посмотреть на каком этапе выполнения тратится время.
В mysql_query_test.txt:

USE shop;
set profiling=1;
    SELECT Goods_Sections.Section_ID, Count(Goods_Sections.Section_ID) as Col
    FROM Goods_Sections
    WHERE
    Goods_Sections.Goods_ID in (147945,146358,146371 ...и т.д. все товары из ВЫБОРКИ 1)
    GROUP BY Goods_Sections.Section_ID;
show profile;

и для сравнения тоже самое для случая, когда выборка 1 небольшая.


С увеличением кол-ва товаров в выборке 1 время второго запроса увеличивается линейно или есть скачок? т.е последовательно сравнить ситуации, когда выборка 1 возвращает 1000 значений, 2000, 3000, ... , 50 000

Неактивен

 

#10 29.04.2019 18:45:36

Oparin
Участник
Зарегистрирован: 24.10.2009
Сообщений: 7

Re: Оптимизация запроса выборки из двух таблиц

после
show profile;
еще оставил show status like 'Created%';
На большой выборке:
Status    |    Duration
starting    |    0.032927
checking permissions    |    0.000018
Opening tables    |    0.000015
After opening tables    |    0.000005
System lock    |    0.000004
Table lock    |    0.000003
After opening tables    |    0.000005
init    |    0.028741
optimizing    |    0.012209
statistics    |    0.459371
preparing    |    0.002429
executing    |    0.000026
Sorting result    |    0.000004
Sending data    |    0.117739
end    |    0.000014
query end    |    0.000009
closing tables    |    0.000007
freeing items    |    0.001210
updating status    |    0.000023
cleaning up    |    0.001065

Variable_name    |    Value
Created_tmp_disk_tables    |    0
Created_tmp_files    |    0
Created_tmp_tables    |    1

Тут большое кол-во времени занимает statistics? А что это?


На малой выборке (500 товаров):
Status    |    Duration
starting    |    0.000135
checking permissions    |    0.000003
Opening tables    |    0.000010
After opening tables    |    0.000002
System lock    |    0.000003
Table lock    |    0.000006
After opening tables    |    0.000003
init    |    0.000082
optimizing    |    0.000031
statistics    |    0.001445
preparing    |    0.000037
executing    |    0.000002
Copying to tmp table    |    0.000018
Copying to tmp table    |    0.001859
Sorting result    |    0.000015
Sending data    |    0.000011
end    |    0.000002
removing tmp table    |    0.000004
end    |    0.000002
query end    |    0.000002
closing tables    |    0.000004
freeing items    |    0.000005
updating status    |    0.000008
cleaning up    |    0.000010

Variable_name    |    Value
Created_tmp_disk_tables    |    0
Created_tmp_files    |    0
Created_tmp_tables    |    2

Попробуем еще сделать сравнительную выборку по разному кол-ву, как Вы советуете.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson