Php
Mysql 使用連接檢索記錄花費的時間太長
好的，這是我的情況，
我有一個包含我的“父”產品資訊的“產品”表。還有一個“product_prices”表，其中包含父產品 ID 及其完整資訊該網站有不同的頁麵類型來顯示產品，其中一個是“登陸頁面”，其中包含自定義產品，該登陸頁面的主標題和最後一個表在這裡是包含所有
parent產品 ID的“landing_page_products”這是 4 個表的完整結構：
-- -- Table structure for table `landing_pages` -- CREATE TABLE `landing_pages` ( `id` int(10) UNSIGNED NOT NULL, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` text COLLATE utf8_unicode_ci NOT NULL, `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `landing_page_products` -- CREATE TABLE `landing_page_products` ( `id` int(10) UNSIGNED NOT NULL, `landing_page_id` int(10) UNSIGNED NOT NULL DEFAULT '1', `product_id` int(10) UNSIGNED NOT NULL DEFAULT '1', `deleted_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `products` -- CREATE TABLE `products` ( `id` int(10) UNSIGNED NOT NULL, `store_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `category_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `brand_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `deleted_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `lowest_price` double NOT NULL, `highest_price` double NOT NULL, `store_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `store_parent_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `fast_shipping` tinyint(1) NOT NULL, `label` varchar(255) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `product_prices` -- CREATE TABLE `product_prices` ( `id` int(10) UNSIGNED NOT NULL, `product_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `color` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `price` double(8,2) NOT NULL, `discount_value` double(8,2) NOT NULL, `discount_percentage` double(8,2) NOT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `sale_price` double NOT NULL, `other_options` text COLLATE utf8_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `store_product_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` text COLLATE utf8_unicode_ci NOT NULL, `keywords` text COLLATE utf8_unicode_ci NOT NULL, `image` text COLLATE utf8_unicode_ci NOT NULL, `images` text COLLATE utf8_unicode_ci NOT NULL, `weight` double(8,2) NOT NULL, `upc` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `product_url` text COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `landing_pages` -- ALTER TABLE `landing_pages` ADD PRIMARY KEY (`id`); -- -- Indexes for table `landing_page_products` -- ALTER TABLE `landing_page_products` ADD PRIMARY KEY (`id`); -- -- Indexes for table `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`id`); -- -- Indexes for table `product_prices` -- ALTER TABLE `product_prices` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `landing_pages` -- ALTER TABLE `landing_pages` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=67; -- -- AUTO_INCREMENT for table `landing_page_products` -- ALTER TABLE `landing_page_products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22052; -- -- AUTO_INCREMENT for table `products` -- ALTER TABLE `products` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48904; -- -- AUTO_INCREMENT for table `product_prices` -- ALTER TABLE `product_prices` MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=248270;每張表的總記錄：landing_pages: 66 shipping_page_products: 22050 “22K” products: 48903 “49K” product_prices: 248269 “248K”
當我執行以下查詢時，大約需要 7-8 秒
select `p`.`id`, `pp`.`title`, `pp`.`price`, `pp`.`sale_price`, `pp`.`discount_percentage`, `pp`.`image`, (SELECT MIN(p2.sale_price) FROM products as p2 WHERE p.id = p2.product_id) as lowest_price, (SELECT MAX(p3.sale_price) FROM products as p3 WHERE p.id = p3.product_id) as highest_price from `products` as `p` left join `product_prices` as `pp` on `p`.`id` = `pp`.`product_id` where `p`.`id` in (select `pt`.`product_id` from `landing_page_products` as `pt` where `pt`.`landing_page_id` in (18)) group by `p`.`id` order by `p`.`id` desc limit 28 offset 0我是否在這裡遺漏了一些使查詢太慢的東西？
我還考慮將 ‘products’ 表與 ‘product_prices’ 表合併到一個表中，以不使用連接。
我正在使用 laravel 5.3 和 PHP 7.0
編輯
我有以下外鍵；
表：landing_page_products product_idlanding_page_id
表：products category_id brand_id
表：product_prices product_id
您需要在連接或 where 條件中使用的欄位上添加索引。您可以
explain在查詢上執行以查看使用了哪些索引以及沒有索引的位置。在您的情況下，landing_page_id 和 parent_id 上的索引似乎有很大幫助
問題似乎不是join而是subselect和INn子句使用不當
您的查詢可以重構為
select p.id, pp.title, pp.price, pp.sale_price, pp.discount_percentage, pp.image, t.lowest_price, t.highest_price from products as p inner join ( select id, min(sale_price) as lowest_price, max(sale_price) as highest_price from products group by id ) t on t.id = p.id inner join ( select pt.product_id from landing_page_products as pt where pt.landing_page_id =18 ) t2 on p.id = t2.product_id left join product_prices as pp on p.id = pp.product_id一旦刪除了無用的子查詢和連接子句，您就可以評估表的正確索引
和表 product_price 列 product_id 上的索引
CREATE INDEX my_product_index ON product_price (product_id )和landing_page
CREATE INDEX my_landing_index ON landing_page(landing_page__id )