How to find the source of slow MySQL queries?

Question

MySQL

Hello.
Site on 1s-Bitrix.
Faced the problem of a large load on the server caused by sql queries.
The logging pointed to problem requests, but I can't find the source of these requests.
How it is possible to find from where the request is caused?
A couple of examples:

SELECT DISTINCT BE.ID as ID,BE.IBLOCK_ID as IBLOCK_ID,BE.CODE as CODE,BE.XML_ID as XML_ID,BE.NAME as NAME,BE.ACTIVE as ACTIVE,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 DATE_ACTIVE_FROM,IF(EXTRACT(HOUR_SECOND FROM BE.ACTIVE_TO)>0, DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y %H:%i:%s'), DATE_FORMAT(BE.ACTIVE_TO, '%d.%m.%Y')) as DATE_ACTIVE_TO,BE.SORT as SORT,BE.PREVIEW_TEXT as PREVIEW_TEXT,BE.PREVIEW_TEXT_TYPE as PREVIEW_TEXT_TYPE,BE.DETAIL_TEXT as DETAIL_TEXT,BE.DETAIL_TEXT_TYPE as DETAIL_TEXT_TYPE,DATE_FORMAT(BE.DATE_CREATE, '%d.%m.%Y %H:%i:%s') as DATE_CREATE,BE.CREATED_BY as CREATED_BY,BE.TAGS as TAGS,DATE_FORMAT(BE.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') as TIMESTAMP_X,BE.MODIFIED_BY as MODIFIED_BY,BE.IBLOCK_SECTION_ID as IBLOCK_SECTION_ID,B.DETAIL_PAGE_URL as DETAIL_PAGE_URL,BE.DETAIL_PICTURE as DETAIL_PICTURE,BE.PREVIEW_PICTURE as PREVIEW_PICTURE,L.DIR as LANG_DIR,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 , CAT_P35.ID as CATALOG_PRICE_ID_35,  CAT_P35.CATALOG_GROUP_ID as CATALOG_GROUP_ID_35,  CAT_P35.PRICE as CATALOG_PRICE_35,  CAT_P35.CURRENCY as CATALOG_CURRENCY_35,  CAT_P35.QUANTITY_FROM as CATALOG_QUANTITY_FROM_35,  CAT_P35.QUANTITY_TO as CATALOG_QUANTITY_TO_35,  'Оптовая цена' as CATALOG_GROUP_NAME_35,  'Y' as CATALOG_CAN_ACCESS_35,  'Y' as CATALOG_CAN_BUY_35,  CAT_P35.EXTRA_ID as CATALOG_EXTRA_ID_35, CAT_PR.QUANTITY as CATALOG_QUANTITY, CAT_PR.QUANTITY_RESERVED as CATALOG_QUANTITY_RESERVED,  IF (CAT_PR.QUANTITY_TRACE = 'D', 'N', CAT_PR.QUANTITY_TRACE) as CATALOG_QUANTITY_TRACE,  CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE_ORIG,  IF (CAT_PR.CAN_BUY_ZERO = 'D', 'Y', CAT_PR.CAN_BUY_ZERO) as CATALOG_CAN_BUY_ZERO,  CAT_PR.CAN_BUY_ZERO as CATALOG_CAN_BUY_ZERO_ORIG,  IF (CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D', 'Y', CAT_PR.NEGATIVE_AMOUNT_TRACE) as CATALOG_NEGATIVE_AMOUNT_TRACE,  CAT_PR.NEGATIVE_AMOUNT_TRACE as CATALOG_NEGATIVE_AMOUNT_ORIG,  IF (CAT_PR.SUBSCRIBE = 'D', 'Y', CAT_PR.SUBSCRIBE) as CATALOG_SUBSCRIBE,  CAT_PR.SUBSCRIBE as CATALOG_SUBSCRIBE_ORIG,  CAT_PR.AVAILABLE as CATALOG_AVAILABLE,  CAT_PR.WEIGHT as CATALOG_WEIGHT, CAT_PR.WIDTH as CATALOG_WIDTH, CAT_PR.LENGTH as CATALOG_LENGTH, CAT_PR.HEIGHT as CATALOG_HEIGHT,  CAT_PR.MEASURE as CATALOG_MEASURE,  CAT_VAT.RATE as CATALOG_VAT, CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED,  CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE, CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE,  CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH, CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID,  CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER, CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE,  CAT_PR.PURCHASING_PRICE as CATALOG_PURCHASING_PRICE, CAT_PR.PURCHASING_CURRENCY as CATALOG_PURCHASING_CURRENCY,  CAT_PR.TYPE as CATALOG_TYPE, CAT_PR.BUNDLE as CATALOG_BUNDLE
          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
      INNER JOIN b_iblock_12_index FC on FC.ELEMENT_ID = BE.ID
 left join b_catalog_price CAT_P35 on (CAT_P35.PRODUCT_ID = BE.ID AND CAT_P35.CATALOG_GROUP_ID = 35)  left join b_catalog_product CAT_PR on (CAT_PR.ID = BE.ID)  left join b_catalog_iblock CAT_IB on ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)  left join b_catalog_vat CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID)) 

    
          WHERE 1=1 
      AND (
      
        (FC.SECTION_ID = 126 AND FC.FACET_ID = 1 AND FC.VALUE_NUM = 0 AND FC.VALUE in (0))
        AND (EXISTS (
            SELECT IBLOCK_ID FROM b_iblock_site WHERE IBLOCK_ID = B.ID
            AND (((SITE_ID='s1')))
          ))
        AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
        AND ((((BE.ACTIVE='Y'))))
        AND (
        
          (
          
            (
            
              ((((CAT_P35.PRICE BETWEEN '80' AND '1344'))))
            )
          )
          OR (BE.ID IN  (
              SELECT   FPS0.PROPERTY_165
              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
        INNER JOIN b_iblock_element_prop_s28 FPS0 ON FPS0.IBLOCK_ELEMENT_ID = BE.ID
 left join b_catalog_price CAT_P35 on (CAT_P35.PRODUCT_ID = BE.ID AND CAT_P35.CATALOG_GROUP_ID = 35)  left join b_catalog_product CAT_PR on (CAT_PR.ID = BE.ID)  left join b_catalog_iblock CAT_IB on ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)  left join b_catalog_vat CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID)) 

    
              WHERE 1=1
                
        AND (
        
          ((((CAT_P35.PRICE BETWEEN '80' AND '1344'))))
          AND ((((BE.IBLOCK_ID = '28'))))
          AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
          AND ((((BE.ACTIVE='Y'))))
        )
        AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
        AND (((FPS0.PROPERTY_165 IS NOT NULL)))
              ))
        )
        AND ((((BE.IBLOCK_ID = '12'))))
      )
      AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
          
           ORDER BY BE.SORT asc ,BE.ID desc 
         LIMIT 0, 40;

SELECT COUNT(DISTINCT BE.ID) as C
            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
      INNER JOIN b_iblock_12_index FC on FC.ELEMENT_ID = BE.ID
 left join b_catalog_price CAT_P24 on (CAT_P24.PRODUCT_ID = BE.ID AND CAT_P24.CATALOG_GROUP_ID = 24)  left join b_catalog_product CAT_PR on (CAT_PR.ID = BE.ID)  left join b_catalog_iblock CAT_IB on ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)  left join b_catalog_vat CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID)) 

    
            WHERE 1=1 
      AND (
      
        (FC.SECTION_ID = 123 AND FC.FACET_ID = 270 AND FC.VALUE_NUM = 0 AND FC.VALUE in (111))
        AND (EXISTS (
            SELECT IBLOCK_ID FROM b_iblock_site WHERE IBLOCK_ID = B.ID
            AND (((SITE_ID='s1')))
          ))
        AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
        AND ((((BE.ACTIVE='Y'))))
        AND (
        
          (
          
            (
            
              ((((CAT_P24.PRICE BETWEEN '0' AND '5000'))))
            )
          )
          OR (BE.ID IN  (
              SELECT   FPS0.PROPERTY_165
              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
        INNER JOIN b_iblock_element_prop_s28 FPS0 ON FPS0.IBLOCK_ELEMENT_ID = BE.ID
 left join b_catalog_price CAT_P24 on (CAT_P24.PRODUCT_ID = BE.ID AND CAT_P24.CATALOG_GROUP_ID = 24)  left join b_catalog_product CAT_PR on (CAT_PR.ID = BE.ID)  left join b_catalog_iblock CAT_IB on ((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0) AND CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)  left join b_catalog_vat CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID)) 

    
              WHERE 1=1
                
        AND (
        
          ((((CAT_P24.PRICE BETWEEN '0' AND '5000'))))
          AND ((((BE.IBLOCK_ID = '28'))))
          AND (((BE.ACTIVE_TO >= now() OR BE.ACTIVE_TO IS NULL) AND (BE.ACTIVE_FROM <= now() OR BE.ACTIVE_FROM IS NULL)))
          AND ((((BE.ACTIVE='Y'))))
        )
        AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
        AND (((FPS0.PROPERTY_165 IS NOT NULL)))
              ))
        )
        AND ((((BE.IBLOCK_ID = '12'))))
      )
      AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
      AND ((
        B.ID IN (
      SELECT IBLOCK_ID
      FROM b_iblock_group IBG
      WHERE IBG.GROUP_ID IN (2)
      AND IBG.PERMISSION >= 'R'
    
        AND (IBG.PERMISSION='X' OR B.ACTIVE='Y')
      )
        OR (B.RIGHTS_MODE = 'E' AND EXISTS (
        SELECT ER.ELEMENT_ID
        FROM b_iblock_element_right ER
        INNER JOIN b_iblock_right IBR ON IBR.ID = ER.RIGHT_ID
        INNER JOIN b_user_access UA ON UA.ACCESS_CODE = IBR.GROUP_CODE AND UA.USER_ID = 0
        WHERE ER.ELEMENT_ID = BE.ID
        AND IBR.OP_EREAD = 'Y'
        
      ))
      ));

There are a lot of goods and customers, so the tables are heavy from those in the example of 50 - 100 mb.
The tables are indexed, but there are many indexes, since the search for data is carried out on several fields.
The field type is InnoDB.

Answer 1

Well, it's Bitrix! All requests are there! Their source is the very core of Bitrix. So you won't change anything serious in them.

Answer 2

Site on 1s-Bitrix.

Here is the problem.
There is no point in reading further.
The text of the query does not matter either - you need data about the tables themselves: size, indexes, types of fields that the joins go through.
This is a whole analytical work.
See execution plan

Answer 3

It's not clear why you need a source.
You have a request - you need to optimize it.

Answer 4

Reduce the number of items per page, enable caching, remove unused properties, display only the ones you need, optimize on the performance page, recreate faceted search. Check components for third party code. See the server - how much memory and how it is consumed, use php7
In short, here you can write a three-volume book of these tips)

Answer 5

Finding the place where these queries are generated is not difficult - just run the Performance Monitor with a record of sql queries.
So there is a place where a non-optimized query. On the tab with queries, you can start the analysis of indexes.

Answer 6

Try faster hosting first. On nvme for example

Answer 7

Many requests for Bitrix are slightly accelerated if you refine the request, for example, instead of 'IBLOCK_ID'=>10, specify '=IBLOCK_ID'=>10

Answer 8

Source component catalog.section
Normal query, if slow look at database engine and indexes.
There is also performance monitoring in the bitr, it will tell you what is missing.

Answer 9

100:1 that the supercoder, who considers Bitrix crap, has finalized the standard component and broken caching.
I have seen such projects. You need to learn - Bitrix is ​​a large commercial system with its own rules. And withstands million-plus stores - Eldorado, mvideo, wildberris, ulmart, etc.

Answer 10

it is possible to correct the select call code.
add comments to sql query with call trace :)
perversion!