01/10/2018, 16:07
Làm sao Fix using filesort, using temporary trong kế hoạch thực thi query
Chào mọi người, mình là newbie trong sql, muốn hỏi mọi người một việc như sau:
Mình có 1 câu query thế này:
SELECT
p.product_id as productId, p.product_name as productName, p.product_image_url as productImageUrl, p.material as productMaterial, p.size, p.price, p.made_in as madeIn, p.product_sex as productSex, mc.main_category_name as categoryMain, b.brand_name as brandName
FROM
products as p, main_categories as mc, brands as b, styles as st, products_colors as pc, shops_products as sp
WHERE
p.main_category_id = mc.main_category_id
AND p.brand_id = b.brand_id
AND p.size = 'S'
AND p.style_id = st.style_id
AND st.style_name = 'enim'
AND p.brand_id = '2'
AND mc.main_category_name = 'facere'
AND p.product_id = pc.product_id
AND pc.color_name = 'Orange'
AND p.product_id = sp.product_id
AND sp.shop_id = '1'
Group By p.product_id order By p.product_id LIMIT 0,10
kết quả thì ko có vấn đề gì, nhưng khi xem kế hoạch thực thi thì lại dính usingfilesort
, using temporary. vấn đề là ở chỗ dùng Group By, order By.
Mình đã đánh index ở các bảng nhưng ko ăn thua. Có vẻ như mình đánh sai index hoặc là câu query chưa ổn. Mong có pro nào chỉ giáo giùm. Dưới đây là kế hoạch thực thi và index hiện ở các bảng:
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+----------+---------------------------------+
| 1 | SIMPLE | b | NULL | const | PRIMARY,index_brandId_brandName | PRIMARY | 4 | const | 1 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | mc | NULL | ref | PRIMARY,index_main_category_name,index_categoryName_categoryId,fulltext_index_category_name | index_main_category_name | 1022 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | st | NULL | ref | PRIMARY,index_style_2,index_style_id_style_name,index_style_1 | index_style_2 | 402 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | p | NULL | ref | PRIMARY,products_style_id_foreign,products_sub_category_id_foreign,index_product_size,index_search_products,index_pId_bId_mcId_stId_pSize,index_brandId_productId_categoryId_styleId_productSize,fulltext_index_product_name | index_search_products | 174 | enst.mc.main_category_id,const,const,enst.st.style_id | 1 | 100.00 | Using index condition |
| 1 | SIMPLE | sp | NULL | eq_ref | PRIMARY,shops_products_shop_id_foreign | PRIMARY | 8 | enst.p.product_id,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | pc | NULL | ref | index_color_search_products | index_color_search_products | 1026 | enst.p.product_id,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+---------+-------------------------------------------------------+------+----------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)
index o cac bang:
mysql> show index from products;
+----------+------------+--------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products | 0 | PRIMARY | 1 | product_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | products_style_id_foreign | 1 | style_id | A | 29 | NULL | NULL | | BTREE | | |
| products | 1 | products_sub_category_id_foreign | 1 | sub_category_id | A | 29 | NULL | NULL | | BTREE | | |
| products | 1 | index_product_size | 1 | size | A | 1 | NULL | NULL | | BTREE | | |
| products | 1 | index_search_products | 1 | main_category_id | A | 29 | NULL | NULL | | BTREE | | |
| products | 1 | index_search_products | 2 | brand_id | A | 29440 | NULL | NULL | | BTREE | | |
| products | 1 | index_search_products | 3 | size | A | 29424 | NULL | NULL | | BTREE | | |
| products | 1 | index_search_products | 4 | style_id | A | 103824 | NULL | NULL | | BTREE | | |
| products | 1 | index_pId_bId_mcId_stId_pSize | 1 | product_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_pId_bId_mcId_stId_pSize | 2 | brand_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_pId_bId_mcId_stId_pSize | 3 | main_category_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_pId_bId_mcId_stId_pSize | 4 | style_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_pId_bId_mcId_stId_pSize | 5 | size | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_brandId_productId_categoryId_styleId_productSize | 1 | brand_id | A | 1000 | NULL | NULL | | BTREE | | |
| products | 1 | index_brandId_productId_categoryId_styleId_productSize | 2 | product_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_brandId_productId_categoryId_styleId_productSize | 3 | main_category_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_brandId_productId_categoryId_styleId_productSize | 4 | style_id | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | index_brandId_productId_categoryId_styleId_productSize | 5 | size | A | 107964 | NULL | NULL | | BTREE | | |
| products | 1 | fulltext_index_product_name | 1 | product_name | NULL | 107964 | NULL | NULL | | FULLTEXT | | |
+----------+------------+--------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set (0.00 sec)
mysql> show index from brands;
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| brands | 0 | PRIMARY | 1 | brand_id | A | 1000 | NULL | NULL | | BTREE | | |
| brands | 1 | index_brandId_brandName | 1 | brand_id | A | 1000 | NULL | NULL | | BTREE | | |
| brands | 1 | index_brandId_brandName | 2 | brand_name | A | 1000 | NULL | NULL | | BTREE | | |
| brands | 1 | fulltext_index_brand_name | 1 | brand_name | NULL | 1000 | NULL | NULL | | FULLTEXT | | |
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> show index from styles;
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| styles | 0 | PRIMARY | 1 | style_id | A | 30 | NULL | NULL | | BTREE | | |
| styles | 1 | index_style_2 | 1 | style_name | A | 27 | NULL | NULL | | BTREE | | |
| styles | 1 | index_style_id_style_name | 1 | style_id | A | 30 | NULL | NULL | | BTREE | | |
| styles | 1 | index_style_id_style_name | 2 | style_name | A | 30 | NULL | NULL | | BTREE | | |
| styles | 1 | index_style_1 | 1 | style_name | NULL | 30 | NULL | NULL | | FULLTEXT | | |
+--------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> show index from products_colors;
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products_colors | 0 | PRIMARY | 1 | color_id | A | 17039 | NULL | NULL | | BTREE | | |
| products_colors | 1 | index_color_search_products | 1 | product_id | A | 15741 | NULL | NULL | | BTREE | | |
| products_colors | 1 | index_color_search_products | 2 | color_name | A | 17016 | NULL | NULL | | BTREE | | |
| products_colors | 1 | index_colorId_colorName | 1 | color_id | A | 17030 | NULL | NULL | | BTREE | | |
| products_colors | 1 | index_colorId_colorName | 2 | color_name | A | 17030 | NULL | NULL | | BTREE | | |
+-----------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> show index from shops_products;
+----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shops_products | 0 | PRIMARY | 1 | product_id | A | 4135 | NULL | NULL | | BTREE | | |
| shops_products | 0 | PRIMARY | 2 | shop_id | A | 5332 | NULL | NULL | | BTREE | | |
| shops_products | 1 | shops_products_shop_id_foreign | 1 | shop_id | A | 994 | NULL | NULL | | BTREE | | |
+----------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> show index from main_categories;
+-----------------+------------+-------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+-------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| main_categories | 0 | PRIMARY | 1 | main_category_id | A | 30 | NULL | NULL | | BTREE | | |
| main_categories | 1 | index_main_category_name | 1 | main_category_name | A | 22 | NULL | NULL | | BTREE | | |
| main_categories | 1 | index_categoryName_categoryId | 1 | main_category_id | A | 30 | NULL | NULL | | BTREE | | |
| main_categories | 1 | index_categoryName_categoryId | 2 | main_category_name | A | 30 | NULL | NULL | | BTREE | | |
| main_categories | 1 | fulltext_index_category_name | 1 | main_category_name | NULL | 30 | NULL | NULL | | FULLTEXT | | |
+-----------------+------------+-------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
chủ đề thì chán,bài viết thì dài. Chắc cũng sẽ không có mấy bạn đọc. Các bạn đọc được đến đây mình cũng cảm ơn lắm rồi. Bạn nào có ý kiến thì comment lại giúp đỡ mình với nhé. Cảm ơn vì đã đọc bài.
Bài liên quan
