Php

Mysql 使用連接檢索記錄花費的時間太長

  • December 31, 2017

好的,這是我的情況,

我有一個包含我的“父”產品資訊的“產品”表。還有一個“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 )

引用自:https://dba.stackexchange.com/questions/194266