MySQL 내부 조인 및 잘못된 성능에 의한 정렬

MySQL 내부 조인 및 잘못된 성능에 의한 정렬

테이블이 두 개 있습니다.제품 및 제품_제품.

상품 : ~ 6276 445 행.

제품 이미지: ~ 22,888,685 행.

테이블 정의:

상품들

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `feed_id` bigint(20) unsigned NOT NULL,
  `feed_item_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_group_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `categorytext` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `categorytext_hash` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `manufacturer` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `price_vat` float(12,2) DEFAULT NULL,
  `price_vat_old` float(12,2) DEFAULT NULL,
  `vat` tinyint(4) DEFAULT NULL,
  `discount_percentage` tinyint(4) NOT NULL DEFAULT 0,
  `image_source_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_filename` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gtin` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ean` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `isbn` varchar(13) COLLATE utf8mb4_unicode_ci NOT NULL,
  `upc` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpn` varchar(70) COLLATE utf8mb4_unicode_ci NOT NULL,
  `missing_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `project_1` tinyint(4) NOT NULL DEFAULT 0,
  `project_2` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id_UNIQUE` (`unique_id`),
  KEY `feed_id_updated_at` (`feed_id`,`updated_at`),
  KEY `feed_id` (`feed_id`),
  KEY `feed_id_missing_at` (`feed_id`,`missing_at`),
  KEY `feed_id_categorytext_hash_id` (`feed_id`,`categorytext_hash`,`id`),
  KEY `categorytext_hash` (`categorytext_hash`),
  KEY `missing_at_deleted_at_categorytext_hash_feed_id_id` (`missing_at`,`deleted_at`,`categorytext_hash`,`feed_id`,`id`),
  KEY `project_1_id` (`project_1`,`id`),
  KEY `project_2_id` (`project_2`,`id`),
  KEY `project_1` (`project_1`),
  KEY `project_2` (`project_2`),
  CONSTRAINT `fk_products_feeds` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=122268834 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

product_build

CREATE TABLE `product_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `unique_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `feed_id` bigint(20) unsigned NOT NULL,
  `image_source_url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `image_filename` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_id_UNIQUE` (`unique_id`),
  KEY `feed_id_updated_at` (`feed_id`,`updated_at`),
  KEY `product_unique_id` (`product_unique_id`),
  KEY `product_unique_id_id` (`product_unique_id`,`id`),
  CONSTRAINT `fk_product_images_products1` FOREIGN KEY (`product_unique_id`) REFERENCES `products` (`unique_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=333584333 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ORDER BY가 없는 내 쿼리

SELECT IMG.id
    FROM products PR
    INNER JOIN product_images IMG on IMG.product_unique_id = PR.unique_id
    WHERE 
        PR.project_1 = 1
        -- ORDER BY IMG.id
    LIMIT 1000 OFFSET 0

기간: 0.016초

설명:

id|select_type|table|type|possible_keys                          |key              |key_len|ref                          |rows  |Extra      |
--+-----------+-----+----+---------------------------------------+-----------------+-------+-----------------------------+------+-----------+
 1|SIMPLE     |PR   |ref |unique_id_UNIQUE,project_1_id,project_1|project_1        |1      |const                        |286960|           |
 1|SIMPLE     |IMG  |ref |product_unique_id,product_unique_id_id |product_unique_id|130    |products-storage.PR.unique_id|2     |Using index|

ORDER BY를 사용한 내 쿼리

SELECT IMG.id
    FROM products PR
    INNER JOIN product_images IMG on IMG.product_unique_id = PR.unique_id
    WHERE 
        PR.project_1 = 1
    ORDER BY IMG.id
    LIMIT 1000 OFFSET 0

지속 시간 17.922초

설명:

id|select_type|table|type|possible_keys                          |key              |key_len|ref                          |rows  |Extra                          |
--+-----------+-----+----+---------------------------------------+-----------------+-------+-----------------------------+------+-------------------------------+
 1|SIMPLE     |PR   |ref |unique_id_UNIQUE,project_1_id,project_1|project_1        |1      |const                        |286960|Using temporary; Using filesort|
 1|SIMPLE     |IMG  |ref |product_unique_id,product_unique_id_id |product_unique_id|130    |products-storage.PR.unique_id|2     |Using index                    |

문제

주문자를 사용한 쿼리가 너무 느립니다.일부 인덱스 또는 서버 설정으로 쿼리 속도를 높일 수 있는 방법이 있습니까?페이지 작성을 위해 주문이 필요합니다.

목표는 프로젝트에 사용할 모든 이미지를 선택하고 api를 통해 다른 서버로 전송하는 것입니다.

mariadb 10.8.2를 사용하고 있습니다.

서버가 8GB RAM이 장착된 SSD에 있음

언급URL : https://stackoverflow.com/questions/73024688/mysql-inner-join-and-order-by-bad-performance