Есть запрос из битрикса, который сильно тормозит.
Исходный запрос. Выполняется 12.5 секунд, и фетч 0.125
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.
SELECT
BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
BE.NAME as NAME,
IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) as ACTIVE_FROM,
DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
B.LIST_PAGE_URL as LIST_PAGE_URL,
BE.DETAIL_TEXT as DETAIL_TEXT,
BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
L.DIR as LANG_DIR,
BE.SORT as SORT,BE.CODE as CODE,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1
AND ( ((((BE.IBLOCK_ID = '2'))))
AND (EXISTS ( SELECT IBLOCK_ID
FROM b_iblock_site
WHERE IBLOCK_ID = B.ID AND (((SITE_ID='s2'))) ))
AND ((((BE.ACTIVE='Y')))) )
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
ORDER BY
BE.ID desc ,BE.SORT asc
LIMIT 0, 10
План запроса, все по индексам
{
"query_block": {
"select_id": 1,
"filesort": {
"temporary_table": {
"function": "buffer",
"table": {
"table_name": "BE",
"access_type": "index_merge",
"possible_keys": [
"ix_iblock_element_1",
"ix_iblock_element_4",
"ix_iblock_element_3",
"ix_iblock_element_code",
"ix_iblock_element_openregion_3",
"idx_b_iblock_element_ACTIVE",
"idx_b_iblock_element_WF_STATUS_ID"
],
"key_length": "3,4,5,5",
"index_merge": {
"intersect": {
"range": {
"key": "idx_b_iblock_element_ACTIVE",
"used_key_parts": ["ACTIVE"]
},
"range": {
"key": "ix_iblock_element_openregion_3",
"used_key_parts": ["IBLOCK_ID"]
},
"range": {
"key": "ix_iblock_element_3",
"used_key_parts": ["WF_PARENT_ELEMENT_ID"]
},
"range": {
"key": "idx_b_iblock_element_WF_STATUS_ID",
"used_key_parts": ["WF_STATUS_ID"]
}
}
},
"rows": 23529,
"filtered": 75.001,
"attached_condition": "((BE.WF_STATUS_ID = 1) and (BE.IBLOCK_ID = '2') and (BE.ACTIVE = 'Y') and isnull(BE.WF_PARENT_ELEMENT_ID))"
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "b_iblock_LID_IDX_openregion"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["ID"],
"ref": ["letters.BE.IBLOCK_ID"],
"rows": 1,
"filtered": 100
},
"table": {
"table_name": "b_iblock_site",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "idx_b_iblock_site_SITE_ID"],
"key": "PRIMARY",
"key_length": "10",
"used_key_parts": ["IBLOCK_ID", "SITE_ID"],
"ref": ["letters.BE.IBLOCK_ID", "const"],
"rows": 1,
"filtered": 100,
"attached_condition": "(b_iblock_site.SITE_ID = 's2')",
"using_index": true
},
"table": {
"table_name": "L",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"rows": 1,
"filtered": 100,
"attached_condition": "(L.LID = B.LID)"
}
}
}
}
}
А теперь просто уменьшим количество возвращаемых полей, не меняя выборку... запрос выполняется 2 секунды...
Запрос
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.
-- explain FORMAT=JSON
SELECT
BE.ID as ID,
/* BE.IBLOCK_ID as IBLOCK_ID,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
BE.NAME as NAME,
IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) as ACTIVE_FROM,
DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
B.LIST_PAGE_URL as LIST_PAGE_URL,
BE.DETAIL_TEXT as DETAIL_TEXT,
BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,*/
L.DIR as LANG_DIR,
BE.SORT as SORT,BE.CODE as CODE,
/*
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,*/
B.LID as LID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1
AND ( ((((BE.IBLOCK_ID = '2'))))
AND (EXISTS ( SELECT IBLOCK_ID
FROM b_iblock_site
WHERE IBLOCK_ID = B.ID AND (((SITE_ID='s2'))) ))
AND ((((BE.ACTIVE='Y')))) )
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
ORDER BY
BE.ID desc ,BE.SORT asc
LIMIT 0, 10
План запроса не изменился
{
"query_block": {
"select_id": 1,
"filesort": {
"temporary_table": {
"function": "buffer",
"table": {
"table_name": "BE",
"access_type": "index_merge",
"possible_keys": [
"ix_iblock_element_1",
"ix_iblock_element_4",
"ix_iblock_element_3",
"ix_iblock_element_code",
"ix_iblock_element_openregion_3",
"idx_b_iblock_element_ACTIVE",
"idx_b_iblock_element_WF_STATUS_ID"
],
"key_length": "3,4,5,5",
"index_merge": {
"intersect": {
"range": {
"key": "idx_b_iblock_element_ACTIVE",
"used_key_parts": ["ACTIVE"]
},
"range": {
"key": "ix_iblock_element_openregion_3",
"used_key_parts": ["IBLOCK_ID"]
},
"range": {
"key": "ix_iblock_element_3",
"used_key_parts": ["WF_PARENT_ELEMENT_ID"]
},
"range": {
"key": "idx_b_iblock_element_WF_STATUS_ID",
"used_key_parts": ["WF_STATUS_ID"]
}
}
},
"rows": 23529,
"filtered": 75.001,
"attached_condition": "((BE.WF_STATUS_ID = 1) and (BE.IBLOCK_ID = '2') and (BE.ACTIVE = 'Y') and isnull(BE.WF_PARENT_ELEMENT_ID))"
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "b_iblock_LID_IDX_openregion"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["ID"],
"ref": ["letters.BE.IBLOCK_ID"],
"rows": 1,
"filtered": 100
},
"table": {
"table_name": "b_iblock_site",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "idx_b_iblock_site_SITE_ID"],
"key": "PRIMARY",
"key_length": "10",
"used_key_parts": ["IBLOCK_ID", "SITE_ID"],
"ref": ["letters.BE.IBLOCK_ID", "const"],
"rows": 1,
"filtered": 100,
"attached_condition": "(b_iblock_site.SITE_ID = 's2')",
"using_index": true
},
"table": {
"table_name": "L",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"rows": 1,
"filtered": 100,
"attached_condition": "(L.LID = B.LID)"
}
}
}
}
}
Вернем поля, уберем один join на таблицу b_lang (к слову в ней только одна запись) Запрос выполняется 1.3 секунды....
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.
-- explain FORMAT=JSON
SELECT
BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
BE.NAME as NAME,
IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) as ACTIVE_FROM,
DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
B.LIST_PAGE_URL as LIST_PAGE_URL,
BE.DETAIL_TEXT as DETAIL_TEXT,
BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
-- L.DIR as LANG_DIR,
BE.SORT as SORT,BE.CODE as CODE,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID
FROM
b_iblock B
-- INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1
AND ( ((((BE.IBLOCK_ID = '2'))))
AND (EXISTS ( SELECT IBLOCK_ID
FROM b_iblock_site
WHERE IBLOCK_ID = B.ID AND (((SITE_ID='s2'))) ))
AND ((((BE.ACTIVE='Y')))) )
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
ORDER BY
BE.ID desc ,BE.SORT asc
LIMIT 0, 10
План запроса
{
"query_block": {
"select_id": 1,
"read_sorted_file": {
"filesort": {
"table": {
"table_name": "BE",
"access_type": "index_merge",
"possible_keys": [
"ix_iblock_element_1",
"ix_iblock_element_4",
"ix_iblock_element_3",
"ix_iblock_element_code",
"ix_iblock_element_openregion_3",
"idx_b_iblock_element_ACTIVE",
"idx_b_iblock_element_WF_STATUS_ID"
],
"key_length": "3,4,5,5",
"index_merge": {
"intersect": {
"range": {
"key": "idx_b_iblock_element_ACTIVE",
"used_key_parts": ["ACTIVE"]
},
"range": {
"key": "ix_iblock_element_openregion_3",
"used_key_parts": ["IBLOCK_ID"]
},
"range": {
"key": "ix_iblock_element_3",
"used_key_parts": ["WF_PARENT_ELEMENT_ID"]
},
"range": {
"key": "idx_b_iblock_element_WF_STATUS_ID",
"used_key_parts": ["WF_STATUS_ID"]
}
}
},
"rows": 23529,
"filtered": 75.001,
"attached_condition": "((BE.WF_STATUS_ID = 1) and (BE.IBLOCK_ID = '2') and (BE.ACTIVE = 'Y') and isnull(BE.WF_PARENT_ELEMENT_ID))"
}
}
},
"table": {
"table_name": "b_iblock_site",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "idx_b_iblock_site_SITE_ID"],
"key": "PRIMARY",
"key_length": "10",
"used_key_parts": ["IBLOCK_ID", "SITE_ID"],
"ref": ["letters.BE.IBLOCK_ID", "const"],
"rows": 1,
"filtered": 100,
"attached_condition": "(b_iblock_site.SITE_ID = 's2')",
"using_index": true
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["ID"],
"ref": ["letters.BE.IBLOCK_ID"],
"rows": 1,
"filtered": 100
}
}
}
Вернем как было... уберем сортировку время вполнения 0.093 секунды
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.
SELECT
BE.ID as ID,
BE.IBLOCK_ID as IBLOCK_ID,
BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,
BE.NAME as NAME,
IF(EXTRACT(HOUR_SECOND
FROM
BE.ACTIVE_FROM)>0, DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_FROM, '%d.%m.%Y')) as ACTIVE_FROM,
DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,
B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,
B.LIST_PAGE_URL as LIST_PAGE_URL,
BE.DETAIL_TEXT as DETAIL_TEXT,
BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,
BE.PREVIEW_TEXT as PREVIEW_TEXT,
BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,
BE.PREVIEW_PICTURE as PREVIEW_PICTURE,
L.DIR as LANG_DIR,
BE.SORT as SORT,BE.CODE as CODE,
BE.XML_ID as EXTERNAL_ID,
B.IBLOCK_TYPE_ID as IBLOCK_TYPE_ID,
B.CODE as IBLOCK_CODE,
B.XML_ID as IBLOCK_EXTERNAL_ID,
B.LID as LID
FROM
b_iblock B
INNER JOIN b_lang L ON B.LID=L.LID
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
WHERE
1=1
AND ( ((((BE.IBLOCK_ID = '2'))))
AND (EXISTS ( SELECT IBLOCK_ID
FROM b_iblock_site
WHERE IBLOCK_ID = B.ID AND (((SITE_ID='s2'))) ))
AND ((((BE.ACTIVE='Y')))) )
AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
-- ORDER BY BE.ID desc ,BE.SORT asc
LIMIT 0, 10
План запроса
{
"query_block": {
"select_id": 1,
"table": {
"table_name": "L",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"rows": 1,
"filtered": 100
},
"table": {
"table_name": "BE",
"access_type": "index_merge",
"possible_keys": [
"ix_iblock_element_1",
"ix_iblock_element_4",
"ix_iblock_element_3",
"ix_iblock_element_code",
"ix_iblock_element_openregion_3",
"idx_b_iblock_element_ACTIVE",
"idx_b_iblock_element_WF_STATUS_ID"
],
"key_length": "3,4,5,5",
"index_merge": {
"intersect": {
"range": {
"key": "idx_b_iblock_element_ACTIVE",
"used_key_parts": ["ACTIVE"]
},
"range": {
"key": "ix_iblock_element_openregion_3",
"used_key_parts": ["IBLOCK_ID"]
},
"range": {
"key": "ix_iblock_element_3",
"used_key_parts": ["WF_PARENT_ELEMENT_ID"]
},
"range": {
"key": "idx_b_iblock_element_WF_STATUS_ID",
"used_key_parts": ["WF_STATUS_ID"]
}
}
},
"rows": 23530,
"filtered": 75.002,
"attached_condition": "((BE.WF_STATUS_ID = 1) and (BE.IBLOCK_ID = '2') and (BE.ACTIVE = 'Y') and isnull(BE.WF_PARENT_ELEMENT_ID))"
},
"table": {
"table_name": "b_iblock_site",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "idx_b_iblock_site_SITE_ID"],
"key": "PRIMARY",
"key_length": "10",
"used_key_parts": ["IBLOCK_ID", "SITE_ID"],
"ref": ["letters.BE.IBLOCK_ID", "const"],
"rows": 1,
"filtered": 100,
"attached_condition": "(b_iblock_site.SITE_ID = 's2')",
"using_index": true
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "b_iblock_LID_IDX_openregion"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["ID"],
"ref": ["letters.BE.IBLOCK_ID"],
"rows": 1,
"filtered": 100,
"attached_condition": "(B.LID = L.LID)"
}
}
}
В общем вопрос... чего может не хватать для выполнения исходного запроса быстро? |
