Order by/子查询 吸收索引 快速测试 ....

desc
SELECT b.sell_date_from, b.department_name, c.pt_brand_name, b.brand_name, a.cat_id, a.cat_name, a.block_sort, b.size_name, b.goods_money_block,
b.uv, b.pv, b.conversion_rate, b.sale_amount, b.sale_cut_money, b.sale_money, b.stock_sale_amount, b.stock_sale_money,
b.sale_user, b.sale_order, b.sum_goods_money, b.sum_goods_amount, b.stock_goods, b.nv_sale_goods, b.nv_sold_out_goods,
b.nv_dymamic_sale_rate, b.nv_sold_out_rate, b.sale_money_rate, b.sale_amount_rate, b.per_user_money, b.per_goods_money,
b.rush_index, b.nv_sale_out_amount, ifnull( s.amount_back_rate, 0.0 ) amount_back_rate, ifnull( s.money_back_rate, 0.0 ) money_back_rate
FROM vipcompass_bus_special_cat a
JOIN vipcompass_bus_special_cat b
ON ( a.block_sort = b.block_sort
AND a.cat_id = b.cat_id )
JOIN vipcompass_bus_special_brand c
ON ( b.department_name = c.department_name
AND b.brand_name = c.brand_name
AND b.sell_date_from = c.sell_date_from )
LEFT JOIN (
SELECT sell_date_from, department_name, brand_name, cat_id, amount_back_rate, money_back_rate
FROM vipcompass_bus_special_back
WHERE dim_group =2
AND sell_date_from
BETWEEN '2014-05-06'
AND '2014-07-05'
)s

ON ( b.sell_date_from = s.sell_date_from
AND b.department_name = s.department_name
AND b.brand_name = s.brand_name
AND b.cat_id = s.cat_id )

WHERE a.sell_date_from = '2014-07-05'
AND a.dim_group =1
AND a.department_name = '广州二部'
AND a.brand_name = '斐凡妮vcfani女装专场'
AND a.block_sort <> ''
AND b.dim_group =1
AND b.sell_date_from
BETWEEN '2014-05-06'
AND '2014-07-05'
AND c.dim_group =4
AND c.sell_date_from
BETWEEN '2014-05-06'
AND '2014-07-05'

ORDER BY b.block_sort, b.cat_id, b.sell_date_from DESC ;

order by 谓语无索引的情况下 explain如下:

+----+-------------+-----------------------------+-------+----------------+----------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table                       | type  | possible_keys  | key            | key_len | ref                        | rows | Extra                                        |
+----+-------------+-----------------------------+-------+----------------+----------------+---------+----------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | b                           | range | dim_group_2    | dim_group_2    | 4       | NULL                       | 6714 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | a                           | ref   | dim_group_2    | dim_group_2    | 4       | const,const                |   35 | Using where                                  |
|  1 | PRIMARY     | c                           | ref   | sell_date_from | sell_date_from | 3       | vip_sinan.b.sell_date_from | 2199 | Using where                                  |
|  1 | PRIMARY     | <derived2>                  | ALL   | NULL           | NULL           | NULL    | NULL                       | 5202 |                                              |
|  2 | DERIVED     | vipcompass_bus_special_back | range | dim_group      | dim_group      | 4       | NULL                       | 5201 | Using where                                  |
+----+-------------+-----------------------------+-------+----------------+----------------+---------+----------------------------+------+----------------------------------------------+

profiling如下:

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000234 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000059 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000083 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000034 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000094 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000036 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.011380 | 0.009999 |   0.001000 |            0 |             0 |
| init                 | 0.000101 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000047 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000123 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000060 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000062 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table | 2.586024 | 2.605603 |   0.005000 |            0 |            56 |
| Sorting result       | 0.000358 | 0.001000 |   0.000000 |            0 |             0 |
| Sending data         | 0.001058 | 0.001000 |   0.000000 |            0 |             0 |
| end                  | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000038 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000049 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000070 | 0.000000 |   0.000000 |            0 |            16 |
| cleaning up          | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

增加两个order by 谓语:

alter table vipcompass_bus_special_cat add key (block_sort, cat_id);

explain结果没变

+----+-------------+-----------------------------+-------+------------------------+----------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table                       | type  | possible_keys          | key            | key_len | ref                        | rows | Extra                                        |
+----+-------------+-----------------------------+-------+------------------------+----------------+---------+----------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | b                           | range | dim_group_2,block_sort | dim_group_2    | 4       | NULL                       | 6714 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | a                           | ref   | dim_group_2,block_sort | dim_group_2    | 4       | const,const                |   35 | Using where                                  |
|  1 | PRIMARY     | c                           | ref   | sell_date_from         | sell_date_from | 3       | vip_sinan.b.sell_date_from | 2199 | Using where                                  |
|  1 | PRIMARY     | <derived2>                  | ALL   | NULL                   | NULL           | NULL    | NULL                       | 5202 |                                              |
|  2 | DERIVED     | vipcompass_bus_special_back | range | dim_group              | dim_group      | 4       | NULL                       | 5201 | Using where                                  |
+----+-------------+-----------------------------+-------+------------------------+----------------+---------+----------------------------+------+----------------------------------------------+

总执行时间 :

2.5, 没多大提升,

profile:

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000252 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000056 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000091 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000037 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000103 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000037 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.011112 | 0.011998 |   0.000000 |            0 |             0 |
| init                 | 0.000107 | 0.001000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000178 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000060 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000065 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table | 2.554829 | 2.634599 |   0.075988 |            0 |             8 |
| Sorting result       | 0.000362 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.001049 | 0.001000 |   0.000000 |            0 |             0 |
| end                  | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000033 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000031 | 0.000000 |   0.001000 |            0 |             0 |
| removing tmp table   | 0.000031 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000041 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000049 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000065 | 0.000000 |   0.000000 |            0 |             8 |
| cleaning up          | 0.000030 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

删除刚才的key,新建全部order by的全key.

alter table vipcompass_bus_special_cat drop key block_sort;

alter table vipcompass_bus_special_cat add key (block_sort, cat_id, sell_date_from);

desc,profiles也没怎么变化...

所以..  主要瓶颈在  Copying to tmp table,,, 子查询....

而不在order by .