Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Хочу избавиться от USING temporary / 14 сообщений из 14, страница 1 из 1
04.09.2015, 11:34:44
    #39043222
Light91
Light91
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Добрый день, столкнулся с необходимостью оптимизировать ряд запросов и постоянно натыкаюсь на USING temprory, собственно сам запрос
Код: sql
1.
2.
3.
4.
5.
EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200

А вот что получаю в результате
Код: plaintext
1.
2.
3.
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	B 	index 	PRIMARY,LID_2 	LID 	2 	NULL	7 	Using index; Using temporary; Using filesort
1 	SIMPLE 	BE 	ref 	ix_iblock_element_1,ix_iblock_element_4,ix_iblock_... 	ix_iblock_element_4 	4 	test4.B.ID 	5131 	NULL
И не пойму почему он делает сортировку таблицы B(!), при этом создавая временную таблицу, в то время как указана сортировка таблицы BE по существующему индексу


Код: 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.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
-- --------------------------------------------------------

--
-- Структура таблицы `b_iblock`
--

CREATE TABLE IF NOT EXISTS `b_iblock` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `IBLOCK_TYPE_ID` varchar(50) NOT NULL,
  `LID` char(2) NOT NULL,
  `CODE` varchar(50) DEFAULT NULL,
  `NAME` varchar(255) NOT NULL,
  `ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `SORT` int(11) NOT NULL DEFAULT '500',
  `LIST_PAGE_URL` varchar(255) DEFAULT NULL,
  `DETAIL_PAGE_URL` varchar(255) DEFAULT NULL,
  `SECTION_PAGE_URL` varchar(255) DEFAULT NULL,
  `CANONICAL_PAGE_URL` varchar(255) DEFAULT NULL,
  `PICTURE` int(18) DEFAULT NULL,
  `DESCRIPTION` text,
  `DESCRIPTION_TYPE` char(4) NOT NULL DEFAULT 'text',
  `RSS_TTL` int(11) NOT NULL DEFAULT '24',
  `RSS_ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `RSS_FILE_ACTIVE` char(1) NOT NULL DEFAULT 'N',
  `RSS_FILE_LIMIT` int(11) DEFAULT NULL,
  `RSS_FILE_DAYS` int(11) DEFAULT NULL,
  `RSS_YANDEX_ACTIVE` char(1) NOT NULL DEFAULT 'N',
  `XML_ID` varchar(255) DEFAULT NULL,
  `TMP_ID` varchar(40) DEFAULT NULL,
  `INDEX_ELEMENT` char(1) NOT NULL DEFAULT 'Y',
  `INDEX_SECTION` char(1) NOT NULL DEFAULT 'N',
  `WORKFLOW` char(1) NOT NULL DEFAULT 'Y',
  `BIZPROC` char(1) NOT NULL DEFAULT 'N',
  `SECTION_CHOOSER` char(1) DEFAULT NULL,
  `LIST_MODE` char(1) DEFAULT NULL,
  `RIGHTS_MODE` char(1) DEFAULT NULL,
  `SECTION_PROPERTY` char(1) DEFAULT NULL,
  `PROPERTY_INDEX` char(1) DEFAULT NULL,
  `VERSION` int(11) NOT NULL DEFAULT '1',
  `LAST_CONV_ELEMENT` int(11) NOT NULL DEFAULT '0',
  `SOCNET_GROUP_ID` int(18) DEFAULT NULL,
  `EDIT_FILE_BEFORE` varchar(255) DEFAULT NULL,
  `EDIT_FILE_AFTER` varchar(255) DEFAULT NULL,
  `SECTIONS_NAME` varchar(100) DEFAULT NULL,
  `SECTION_NAME` varchar(100) DEFAULT NULL,
  `ELEMENTS_NAME` varchar(100) DEFAULT NULL,
  `ELEMENT_NAME` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ix_iblock` (`IBLOCK_TYPE_ID`,`LID`,`ACTIVE`),
  KEY `LID` (`LID`),
  KEY `LID_2` (`ID`,`LID`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=14 ;



ALTER TABLE `b_iblock`
  ADD CONSTRAINT `b_iblock_ibfk_1` FOREIGN KEY (`LID`) REFERENCES `b_lang` (`LID`),
  ADD CONSTRAINT `b_iblock_ibfk_2` FOREIGN KEY (`LID`) REFERENCES `b_lang` (`LID`);
-- --------------------------------------------------------

--
-- Структура таблицы `b_iblock_element`
--

CREATE TABLE IF NOT EXISTS `b_iblock_element` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP_X` datetime DEFAULT NULL,
  `MODIFIED_BY` int(18) DEFAULT NULL,
  `DATE_CREATE` datetime DEFAULT NULL,
  `CREATED_BY` int(18) DEFAULT NULL,
  `IBLOCK_ID` int(11) NOT NULL DEFAULT '0',
  `IBLOCK_SECTION_ID` int(11) DEFAULT NULL,
  `ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `ACTIVE_FROM` datetime DEFAULT NULL,
  `ACTIVE_TO` datetime DEFAULT NULL,
  `SORT` int(11) NOT NULL DEFAULT '500',
  `NAME` varchar(255) NOT NULL,
  `PREVIEW_PICTURE` int(18) DEFAULT NULL,
  `PREVIEW_TEXT` text,
  `PREVIEW_TEXT_TYPE` varchar(4) NOT NULL DEFAULT 'text',
  `DETAIL_PICTURE` int(18) DEFAULT NULL,
  `DETAIL_TEXT` longtext,
  `DETAIL_TEXT_TYPE` varchar(4) NOT NULL DEFAULT 'text',
  `SEARCHABLE_CONTENT` text,
  `WF_STATUS_ID` int(18) DEFAULT '1',
  `WF_PARENT_ELEMENT_ID` int(11) DEFAULT NULL,
  `WF_NEW` char(1) DEFAULT NULL,
  `WF_LOCKED_BY` int(18) DEFAULT NULL,
  `WF_DATE_LOCK` datetime DEFAULT NULL,
  `WF_COMMENTS` text,
  `IN_SECTIONS` char(1) NOT NULL DEFAULT 'N',
  `XML_ID` varchar(255) DEFAULT NULL,
  `CODE` varchar(255) DEFAULT NULL,
  `TAGS` varchar(255) DEFAULT NULL,
  `TMP_ID` varchar(40) DEFAULT NULL,
  `WF_LAST_HISTORY_ID` int(11) DEFAULT NULL,
  `SHOW_COUNTER` int(18) DEFAULT NULL,
  `SHOW_COUNTER_START` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SORT_2` (`SORT`,`ID`),
  UNIQUE KEY `ID` (`ID`,`SORT`),
  UNIQUE KEY `IBLOCK_ID_2` (`ACTIVE_TO`,`ACTIVE_FROM`,`ACTIVE`,`WF_STATUS_ID`,`WF_PARENT_ELEMENT_ID`,`ID`,`SORT`),
  KEY `ix_iblock_element_1` (`IBLOCK_ID`,`IBLOCK_SECTION_ID`),
  KEY `ix_iblock_element_4` (`IBLOCK_ID`,`XML_ID`,`WF_PARENT_ELEMENT_ID`),
  KEY `ix_iblock_element_3` (`WF_PARENT_ELEMENT_ID`),
  KEY `ix_iblock_element_code` (`IBLOCK_ID`,`CODE`),
  KEY `NAME` (`NAME`),
  KEY `SORT` (`SORT`),
  KEY `ID_2` (`ID`,`IBLOCK_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=65299 ;

ALTER TABLE `b_iblock_element`
  ADD CONSTRAINT `b_iblock_element_ibfk_1` FOREIGN KEY (`IBLOCK_ID`) REFERENCES `b_iblock` (`ID`);


Я уже понатыкал ключей "лишь бы были" так и это не помагает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 11:40:17
    #39043228
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Добавлю подсветку, ежели ты сам не умеешь...
Light91собственно сам запрос
Код: sql
1.
2.
3.
4.
5.
6.
EXPLAIN 
SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200


Ты сортируешь данные одной таблицы по данным другой. Чтобы выполнить сортировку, нужно сначала получить совокупную таблицу. Это невозможно сделать без USING temporary.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 11:44:32
    #39043236
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Конкретно для этого запроса я бы попробовал индекс (SORT, ID, IBLOCK_ID) на таблице b_iblock_element.
Но, во-первых, запрос не похож на логичный.
Во-вторых, а что это вы в битриксе запросы правите? Неужто кощунствуете, ядро правя?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 11:50:18
    #39043240
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Я бы предложил индекс (IBLOCK_ID,SORT), и select be.IBLOCK_ID вместо B.ID.
Да и FROM b_iblock - так ли уж необходимо?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 11:53:27
    #39043243
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Cygapb-007Да и FROM b_iblock - так ли уж необходимо?Всмысле - при наличии FK достаточно отфильтровать NOT NULL
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 12:01:43
    #39043254
Light91
Light91
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Ну давайте по порядку, нет, я не вношу правок в ядро и не склонен этого делать, просто раскручиваю сложные запросы и пытаюсь разобраться как их можно оптимизировать за счет создания ключей и/или правок настроек mysql.
Так что нужно пожалуй уточнить, никаких изменений запроса сделать не смогу.
Индексы сейчас попробую добавить.
Спасибо за скорые ответы.


Ты сортируешь данные одной таблицы по данным другой. Чтобы выполнить сортировку, нужно сначала получить совокупную таблицу. Это невозможно сделать без USING temporary.
А вот это уже интересно, что скажешь об этом?

EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT asc ,BE.ID desc
LIMIT 200;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE BE ALL ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_code,IBLOCK_ID 50990 Using filesort
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test4.BE.IBLOCK_ID 1 Using index

Запрос выполнил на другой версии mysql, с другими настройками, тндексы по моемпу те
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 12:02:49
    #39043255
Light91
Light91
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
по моему те же.
ps
Не могу отредактировать своё предыдущее сообщение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:14:35
    #39043371
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Light91, по-моему, Вам дан правильный совет - трансформировать запрос в
Код: sql
1.
2.
3.
4.
SELECT BE.IBLOCK_ID
FROM b_iblock B, b_iblock_element BE 
WHERE BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC


ну или во всякоразное
Код: sql
1.
2.
3.
4.
SELECT BE.IBLOCK_ID
FROM b_iblock_element BE 
WHERE EXISTS (SELECT 1 FROM b_iblock B WHERE BE.IBLOCK_ID = B.ID)
ORDER BY BE.SORT ASC , BE.ID DESC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:27:46
    #39043390
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
AkinaLight91, по-моему, Вам дан правильный совет - трансформировать запрос в
не совсем так:
Код: sql
1.
2.
3.
4.
SELECT /*DISTINCT*/ BE.IBLOCK_ID
FROM b_iblock_element BE 
WHERE BE.IBLOCK_ID IS NOT NULL
-- ORDER BY BE.SORT ASC , BE.ID DESC

Тот же результат
Да и нафига сортировка, если ее не видно?

Хотя если запросы исправить нельзя, только индексы и остаются для подстройки...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:29:05
    #39043391
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Опс, сорь, протупил с лимитом... Нужна сортировка...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:30:41
    #39043396
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Light91Добрый день, столкнулся с необходимостью оптимизировать ряд запросов и постоянно натыкаюсь на USING temprory, собственно сам запрос
[src sql]
EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200


Само по себе USING temporary -- ни хорошо, и ни плохо, так что избавляться от него как такового не нужно.


Но конкретно тут у тебя плохой запрос, в нём нет WHERE вообще, а значит, сортируются все записи, а затем из них отбираются 200 первых.

Тут надо избавляться от USING temprory, точнее, надо ускорять сортировку.

Для этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)

Теперь надо вспомнить, умеет ли MySQL создавать индексы с явно указанной сортировкой...
глядим: https://dev.mysql.com/doc/refman/5.0/en/create-index.html

авторAn index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.


Ой-ёй! Не умеет... А последняя версия 5.7 ? -- ай, тоже нет...

Ну, ты, мужик, попал!

Тебе либо надо избавиться от второго поля в ORDER BY BE.SORT ASC, BE.ID DESC
, если по логике приложения это подходит, либо сделать другое поле, зависимое от be.ID,
и растущее в противоположную сторону, и по нему сортировать и создавать индекс с его участием.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:35:23
    #39043399
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
MasterZivДля этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)
Стесняюсь напомнить, что индекс по SORT уже есть, а ID - РК, то есть содержится в любом другом индексе...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 13:47:31
    #39043413
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Cygapb-007MasterZivДля этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)
Стесняюсь напомнить, что индекс по SORT уже есть, а ID - РК, то есть содержится в любом другом индексе...

Я не смотрел структуру...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.09.2015, 14:06:59
    #39043442
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Хочу избавиться от USING temporary
Cygapb-007Тот же результат
А, да, там же FK...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Хочу избавиться от USING temporary / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 310ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1832756]:
 0ms
get settings:
 7ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 204ms
total:  310ms
x
x
Закрыть


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