|
优化器创建了一个直方图，大约读了c_birth_country列4.8%的数据。取样是不确定的，因此意义不大。同样的数据，同样的两条语句‘‘ANALYZE TABLE tbl UPDATE HISTOGRAM …’’，如果用了取样，得到的直方图可能就不一样。
查询案例
统计直方图可以带来些什么？我们可以看个例子，这个例子中用了直方图，在执行时间上会有很大的不同。
环境：
- TPC-DS Benchmark with scale factor of 1
- Intel Core i7-4770
- Debian Stretch
- MySQL 8.0 RC1
- innodb_buffer_pool_size = 2G
- optimizer_switch = "condition_fanout_filter=on"
Query 90
查询如下：上午售卖的数量与晚上售卖的数量的比率。
- mysql> SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) am_pm_ratio
- -> FROM (SELECT COUNT(*) amc
- -> FROM web_sales,
- -> household_demographics,
- -> time_dim,
- -> web_page
- -> WHERE ws_sold_time_sk = time_dim.t_time_sk
- -> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
- -> AND ws_web_page_sk = web_page.wp_web_page_sk
- -> AND time_dim.t_hour BETWEEN 9 AND 9 + 1
- -> AND household_demographics.hd_dep_count = 2
- -> AND web_page.wp_char_count BETWEEN 5000 AND 5200) at,
- -> (SELECT COUNT(*) pmc
- -> FROM web_sales,
- -> household_demographics,
- -> time_dim,
- -> web_page
- -> WHERE ws_sold_time_sk = time_dim.t_time_sk
- -> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
- -> AND ws_web_page_sk = web_page.wp_web_page_sk
- -> AND time_dim.t_hour BETWEEN 15 AND 15 + 1
- -> AND household_demographics.hd_dep_count = 2
- -> AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt
- -> ORDER BY am_pm_ratio
- -> LIMIT 100;
- +-------------+
- | am_pm_ratio |
- +-------------+
- | 1.27619048 |
- +-------------+
- 1 row in set (1.48 sec)
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
