查詢時間過長

我的查詢花費了太多時間來執行。請在這裡幫助我。

mysql> explain 
select pcm.catalog_id 
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c  
where 
   pcm.product_id = 2520000  
   and ccm.catalog_id = pcm.catalog_id 
   and ccm.parent_catalog_id = 1000025 
   and levels <> 0 
   and c.catalog_id = pcm.catalog_id 
   and c.precedence is not null 
order by c.precedence;
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys                              | key      | key_len | ref                    | rows | Extra                       |
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ALL  | idx_1031,idx_17109                         | NULL     | NULL    | NULL                   | 2769 | Using where; Using filesort |
|  1 | SIMPLE      | pcm   | ref  | idx_2195,idx_1069                          | idx_2195 | 10      | cms.c.catalog_id,const |    1 | Using where; Using index    |
|  1 | SIMPLE      | ccm   | ref  | idx_2902,idx_651,fkey_cat_catal_catal_4433 | idx_2902 | 10      | cms.c.catalog_id,const |    1 | Using where                 |
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
3 rows in set (0.01 sec)


mysql> show create table cat_produ_catal_map_defer\G
*************************** 1. row ***************************
      Table: cat_produ_catal_map_defer
Create Table: CREATE TABLE `cat_produ_catal_map_defer` (
 `row_mod` datetime DEFAULT NULL,
 `row_create` datetime DEFAULT NULL,
 `product_id` int(11) DEFAULT NULL,
 `catalog_id` int(11) DEFAULT NULL,
 UNIQUE KEY `idx_2195` (`catalog_id`,`product_id`),
 KEY `idx_1069` (`product_id`),
 CONSTRAINT `fkey_cat_produ_catal_3725` FOREIGN KEY (`catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fkey_cat_produ_catal_3763` FOREIGN KEY (`product_id`) REFERENCES `cat_produ_defer` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table cat_catal_catal_map_defer\G
*************************** 1. row ***************************
      Table: cat_catal_catal_map_defer
Create Table: CREATE TABLE `cat_catal_catal_map_defer` (
 `row_mod` datetime DEFAULT NULL,
 `row_create` datetime DEFAULT NULL,
 `catalog_id` int(11) DEFAULT NULL,
 `parent_catalog_id` int(11) DEFAULT NULL,
 `levels` int(11) DEFAULT NULL,
 UNIQUE KEY `idx_2902` (`catalog_id`,`parent_catalog_id`),
 KEY `idx_651` (`levels`),
 KEY `fkey_cat_catal_catal_4433` (`parent_catalog_id`),
 CONSTRAINT `fkey_cat_catal_catal_3688` FOREIGN KEY (`catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fkey_cat_catal_catal_4433` FOREIGN KEY (`parent_catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table cat_catal_defer\G
*************************** 1. row ***************************
      Table: cat_catal_defer
Create Table: CREATE TABLE `cat_catal_defer` (
 `row_mod` datetime DEFAULT NULL,
 `row_create` datetime DEFAULT NULL,
 `catalog_id` int(11) DEFAULT NULL,
 `prod_result_changes_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
 `inheritance_mode` int(11) DEFAULT NULL,
 `name` varchar(100) COLLATE latin1_bin DEFAULT NULL,
 `datafile_regexp` varchar(255) COLLATE latin1_bin DEFAULT NULL,
 `product_change_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
 `add_category_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
 `delete_category_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
 `root_category_id` int(11) DEFAULT NULL,
 `deleted` int(11) DEFAULT NULL,
 `precedence` varchar(20) COLLATE latin1_bin DEFAULT NULL,
 UNIQUE KEY `idx_1031` (`catalog_id`),
 KEY `idx_1709` (`root_category_id`),
 KEY `idx_17109` (`precedence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

核心問題似乎是優化器不（或不能）使用索引idx_17109來尋找c.precedence IS NOT NULL謂詞。以下修改允許查找，但仍需要提示以避免排序：

SELECT
   pcm.catalog_id 
FROM cat_catal_defer AS c USE INDEX (idx_17109)
JOIN cat_produ_catal_map_defer AS pcm ON
   pcm.catalog_id = c.catalog_id
JOIN cat_catal_catal_map_defer AS ccm ON
   ccm.catalog_id = pcm.catalog_id
WHERE
   c.precedence > ''
   AND pcm.product_id = 2520000
   AND ccm.parent_catalog_id = 1000025
   AND ccm.levels <> 0
ORDER BY
   c.precedence;

解釋：

在此處嘗試SQLFiddle。

“使用文件排序”聽起來很糟糕。

您的請求在排序步驟上花時間，您真的需要對結果集進行排序嗎？

嘗試刪除 ORDER BY，以驗證問題來自這裡。

最大限度。

引用自：https://dba.stackexchange.com/questions/30820