|
通过这个直方图，查询花费了0.5秒左右。原因呢？主要的原因是，查询语句中的谓词“web_page.wp_char_count BETWEEN 5000 AND 5200”。没有直方图的时候，优化器会假设web_page表中符合谓词“web_page.wp_char_count BETWEEN 5000 AND 5200”的数据占到总数据11.11%左右。但，这是错误的。用下面的查询语句，可以看到实际上满足条件的数据只有1.6%。
- mysql> SELECT
- -> (SELECT COUNT(*) FROM web_page WHERE web_page.wp_char_count BETWEEN 5000 AND 5200)
- -> /
- -> (SELECT COUNT(*) FROM web_page) AS ratio;
- +--------+
- | ratio |
- +--------+
- | 0.0167 |
- +--------+
- 1 row in set (0.00 sec)
通过直方图，优化器会知道这个信息，并且更早进行表join，因此执行时间快了三倍。
Query 61
查询如下：在给定的年份和月份，有和没有广告宣传的情况下货物的售卖比率。
- mysql> SELECT promotions, -> total,
-
- -> CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100
- -> FROM (SELECT SUM(ss_ext_sales_price) promotions
- -> FROM store_sales,
- -> store,
- -> promotion,
- -> date_dim,
- -> customer,
- -> customer_address,
- -> item
- -> WHERE ss_sold_date_sk = d_date_sk
- -> AND ss_store_sk = s_store_sk
- -> AND ss_promo_sk = p_promo_sk
- -> AND ss_customer_sk = c_customer_sk
- -> AND ca_address_sk = c_current_addr_sk
- -> AND ss_item_sk = i_item_sk
- -> AND ca_gmt_offset = -5
- -> AND i_category = 'Home'
- -> AND ( p_channel_dmail = 'Y'
- -> OR p_channel_email = 'Y'
- -> OR p_channel_tv = 'Y' )
- -> AND s_gmt_offset = -5
- -> AND d_year = 2000
- -> AND d_moy = 12) promotional_sales,
- -> (SELECT SUM(ss_ext_sales_price) total
- -> FROM store_sales,
- -> store,
- -> date_dim,
- -> customer,
- -> customer_address,
- -> item
- -> WHERE ss_sold_date_sk = d_date_sk
- -> AND ss_store_sk = s_store_sk
- -> AND ss_customer_sk = c_customer_sk
- -> AND ca_address_sk = c_current_addr_sk
- -> AND ss_item_sk = i_item_sk
- -> AND ca_gmt_offset = -5
- -> AND i_category = 'Home'
- -> AND s_gmt_offset = -5
- -> AND d_year = 2000
- -> AND d_moy = 12) all_sales
- -> ORDER BY promotions,
- -> total
- -> LIMIT 100;
- +------------+------------+--------------------------------------------------------------------------+
- | promotions | total | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |
- +------------+------------+--------------------------------------------------------------------------+
- | 3213210.07 | 5966836.78 | 53.85114741 |
- +------------+------------+--------------------------------------------------------------------------+
- 1 row in set (2.78 sec)
（编辑：滁州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
