词条信息

admin
admin
超级管理员
词条创建者 发短消息   

相关词条

热门词条

更多>>
2018 年最常见的 Python 面试题
【导读】Python 新手在谋求一份 Python 编程工[详细]
学习编程语言常用的10个工具
每个行业都有自己的工具,软件开发也没有什么不同。一个优秀的程序[详细]
Python软件的下载、安装和使用问题汇总
这是个Python开发环境相关问题的汇总,希望能让所有学习[详细]
人民日报三问区块链
近段时间,有关比特币的新闻非常吸睛,区块链也跟着火了一把。资本[详细]
2018年IT行业最赚钱的5个职位
前言  前几天看到一则新闻,说是某著名科技公司的程序员在相亲节[详细]
解决网 >>所属分类 >> 程序开发    MySQL   

MySQL 8.0 新特性之统计直方图

标签: MySQL8.0 统计 直方图

顶[0] 发表评论(0) 编辑词条

| 译者简介


译者 韩杰·沃趣科技MySQL数据库工程师 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

熟悉mysql体系架构、主从复制,熟悉问题定位与解决

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

出品 沃趣科技

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

原文链接:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

https://mysqlserverteam.com/histogram-statistics-in-mysql/ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 概览

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

MySQL8.0实现了统计直方图。利用直方图,用户可以对一张表的一列做数据分布的统计,特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。统计直方图的主要使用场景是用来计算字段选择性,即过滤效率。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

可以通过以下方式来创建或者删除直方图: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

ANALYZETABLEtbl_name UPDATEHISTOGRAM ONcol_name [, col_name] WITHN BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

ANALYZETABLEtbl_name DROPHISTOGRAM ONcol_name [, col_name]; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

buckets默认是100。统计直方图的信息存储在数据字典表"column_statistcs"中,可以通过视图information_schema.COLUMN_STATISTICS访问。直方图以灵活的JSON的格式存储。ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图(singleton)还是等高直方图(equi-height)。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 什么是直方图

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

数据库中,查询优化器负责将SQL转换成最有效的执行计划。有时候,查询优化器会走不到最优的执行计划,导致花费了更多不必要的时间。造成这种情况的主要原因是,查询优化器有时无法准确的知道以下几个问题的答案: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

每个表有多少行? www.solves.com.cn 电脑百科

每一列有多少不同的值? www.solves.com.cn 电脑百科

每一列的数据分布情况? www.solves.com.cn 电脑百科

举例说明:一张简单的表,两个字段,一个字段是person_id,另一个字段是time_of_day,表示睡觉时间

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

CREATETABLEbedtime (

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

person_id INT, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

time_of_day TIME); www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

对于time_of_day列,大部分人上床时间会在晚上11:00左右。所以下面第一个查询会比第二个查询返回更多的行数:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1) SELECT* FROMbedtime WHEREtime_of_day BETWEEN"22:00:00"AND"23:59:00"

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

2) SELECT* FROMbedtime WHEREtime_of_day BETWEEN"12:00:00"AND"14:00:00"

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

如果没有统计数据,优化器会假设time_of_day的值是均匀分配的,即一个人的上床时间在下午3点和晚上11点的概率差不多。如何才能使查询优化器知道数据的分布情况?一个解决方法就是在列上建立统计直方图。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

直方图能近似获得一列的数据分布情况,从而让数据库知道它含有哪些数据。直方图有多种形式,MySQL支持了两种:等宽直方图(singleton)、等高直方图(equi-height)。直方图的共同点是,它们都将数据分到了一系列的buckets中去。MySQL会自动将数据划到不同的buckets中,也会自动决定创建哪种类型的直方图。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 如何创建和删除统计直方图 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

为了管理统计直方图,ANALYZE TABLE命令新增了两个子句:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

ANALYZETABLEtbl_name UPDATEHISTOGRAM ONcol_name [, col_name] WITHN BUCKETS; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

ANALYZETABLEtbl_name DROPHISTOGRAM ONcol_name [, col_name];

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

第一个表示一次可以为一个或多个列创建统计直方图: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount WITH 32BUCKETS; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|sakila.payment | histogram |status | Histogram statistics created forcolumn 'amount'. |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0. 27sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount, payment_date WITH 32BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|sakila.payment | histogram |status | Histogram statistics created forcolumn 'amount'. |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| sakila.payment |histogram | status |Histogram statistics created forcolumn 'payment_date'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

buckets的值必须指定,可以设置为1到1024,默认值是100。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

对于不同的数据集合,buckets的值取决于以下几个因素: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

这列有多少不同的值 www.solves.com.cn 电脑百科

数据的分布情况

www.solves.com.cn 电脑百科

需要多高的准确性 www.solves.com.cn 电脑百科

但是,某些buckets的值能提升的关于数据分布情况的准确性相当低。所以,建议的做法是,开始的时候将buckets的值设的低一点,比如32,然后如果没有满足期望,再往上增大。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

上面这个例子中,我们对于amount列建立了两次直方图。第一个语句,建立了一个新的直方图;第二个语句,amount列的直方图被重写了。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

如果需要删除已经创建的直方图,用DROP HISTOGRAM就可以实现:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE payment DROP HISTOGRAM ON payment_date; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|sakila.payment | histogram |status | Histogram statistics removed forcolumn 'payment_date'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+---------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

UPDATE HISTOGRAM可以一次性为多个列创建直方图。如果命令中间写错,ANALYZE TABLE仍然会起作用。比如,你指定了三列,但第二列不存在。MySQL仍然会为第一列和第三列创建直方图。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_day, c_foobar, c_birth_month WITH 32BUCKETS; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+----------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+----------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|tpcds.customer | histogram |status | Histogram statistics created forcolumn 'c_birth_day'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| tpcds.customer |histogram | status |Histogram statistics created forcolumn 'c_birth_month'. |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|tpcds.customer | histogram |Error | The column 'c_foobar' does notexist. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+----------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

3rows inset ( 0. 15sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 数据库内部发生了什么 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

当你读过MySQL手册,你可能已经注意到新的系统变量histogram_generation_max_mem_size。当用户建立统计直方图,这个值是用来控制大约多少内存能允许被使用。那么,为什么要控制这个呢?

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

当你在建立直方图的时候,MySQL server会将所有数据读到内存中,然后在内存中进行操作,包括排序。如果对一个很大的表建立直方图,可能会有风险将几百M的数据都读到内存中,但这是不明智的。为了规避这个风险,MySQL会根据给定的histogram_generation_max_mem_size的值计算该将多少行数据读到内存中。如果根据当前histogram_generation_max_mem_size的限制,MySQL认为只能读一部分数据,那么MySQL会进行取样。通过“sampling-rate”属性,可以观察到取样比率。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SET histogram_generation_max_mem_size = 1000000; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

Query OK, 0rows affected ( 0. 00sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_country WITH 16BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|tpcds.customer | histogram |status | Histogram statistics created forcolumn 'c_birth_country'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0. 22sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT histogram- >>'$."sampling-rate"' www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> FROM information_schema.column_statistics

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> WHERE table_name = "customer"

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND column_name = "c_birth_country"; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+---------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| histogram->>'$."sampling-rate"' |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+---------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 0.048743243211626014 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+---------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0. 00sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

优化器创建了一个直方图,大约读了c_birth_country列4.8%的数据。取样是不确定的,因此意义不大。同样的数据,同样的两条语句‘‘ANALYZE TABLE tbl UPDATE HISTOGRAM …’’,如果用了取样,得到的直方图可能就不一样。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 查询案例 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

统计直方图可以带来些什么?我们可以看个例子,这个例子中用了直方图,在执行时间上会有很大的不同。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

环境:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

TPC-DS Benchmark with scale factor of 1

www.solves.com.cn 电脑百科

Intel Core i7-4770

www.solves.com.cn 电脑百科

Debian Stretch www.solves.com.cn 电脑百科

MySQL 8.0 RC1 www.solves.com.cn 电脑百科

innodb_buffer_pool_size = 2G www.solves.com.cn 电脑百科

optimizer_switch = "condition_fanout_filter=on"

www.solves.com.cn 电脑百科

| Query 90 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

查询如下:上午售卖的数量与晚上售卖的数量的比率。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) am_pm_ratio www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> FROM (SELECT COUNT(*) amc

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> FROM web_sales,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> household_demographics, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> time_dim,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> web_page www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> WHERE ws_sold_time_sk = time_dim.t_time_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND ws_web_page_sk = web_page.wp_web_page_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND time_dim.t_hour BETWEEN 9 AND 9 + 1 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND household_demographics.hd_dep_count = 2 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND web_page.wp_char_count BETWEEN 5000 AND 5200) at,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> (SELECT COUNT(*) pmc

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> FROM web_sales,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> household_demographics,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> time_dim,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> web_page www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> WHERE ws_sold_time_sk = time_dim.t_time_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND ws_web_page_sk = web_page.wp_web_page_sk

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND time_dim.t_hour BETWEEN 15 AND 15 + 1

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND household_demographics.hd_dep_count = 2 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> ORDER BY am_pm_ratio www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> LIMIT 100;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| am_pm_ratio | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 1.27619048 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1 row in set (1.48 sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

可以看到,查询花费了1.5秒左右。看起来不算多,但是通过在一列上建立直方图,可以让执行速度快三倍。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE web_page UPDATE HISTOGRAM ON wp_char_count WITH 8BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------+-----------+----------+----------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table | Op | Msg_type | Msg_text |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------+-----------+----------+----------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| tpcds.web_page | histogram | status| Histogram statistics created forcolumn 'wp_char_count'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------+-----------+----------+----------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0.06sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT ...

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ -------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| am_pm_ratio |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ -------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 1.27619048|

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ -------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0.50sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

通过这个直方图,查询花费了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%。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> (SELECT COUNT(*) FROM web_page WHERE web_page.wp_char_count BETWEEN 5000 AND 5200) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> / www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> (SELECT COUNT(*) FROM web_page) AS ratio; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| ratio |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 0.0167 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1 row in set (0.00 sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

通过直方图,优化器会知道这个信息,并且更早进行表join,因此执行时间快了三倍。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Query 61 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

查询如下:在给定的年份和月份,有和没有广告宣传的情况下货物的售卖比率。 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql>SELECT promotions, -> total, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->FROM (SELECT SUM(ss_ext_sales_price) promotions

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->FROM store_sales,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->store,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->promotion,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->date_dim, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->customer, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->customer_address, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->item www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->WHERE ss_sold_date_sk = d_date_sk

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_store_sk = s_store_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_promo_sk = p_promo_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_customer_sk = c_customer_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ca_address_sk = c_current_addr_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_item_sk = i_item_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ca_gmt_offset = -5 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND i_category = 'Home'

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ( p_channel_dmail = 'Y' www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->OR p_channel_email = 'Y' www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->OR p_channel_tv = 'Y') www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND s_gmt_offset = -5 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND d_year = 2000

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND d_moy = 12) promotional_sales, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->(SELECT SUM(ss_ext_sales_price) total

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->FROM store_sales,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->store,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->date_dim, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->customer,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->customer_address, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->item

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->WHERE ss_sold_date_sk = d_date_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_store_sk = s_store_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_customer_sk = c_customer_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ca_address_sk = c_current_addr_sk

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ss_item_sk = i_item_sk www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND ca_gmt_offset = -5 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND i_category = 'Home' www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND s_gmt_offset = -5 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND d_year = 2000 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND d_moy = 12) all_sales www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->ORDER BY promotions, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->total

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->LIMIT 100;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| promotions | total | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 3213210.07 | 5966836.78 | 53.85114741 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1 row in set (2.78 sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

可以看到,查询花费了2.8秒左右。但是,查询优化器不知道s_gmt_offset列只有一个不同的值。没有统计数据的情况下,优化器会用所谓的“hard-coded guesstimates”,会假设10%的数据符合条件“ca_gmt_offset = -5“。如果在这个列上增加一个直方图,优化器会知道所有的数据都符合条件,因此会走一个更好的执行计划。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE store UPDATE HISTOGRAM ON s_gmt_offset WITH 8BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+-----------+----------+---------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+-----------+----------+---------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|tpcds.store | histogram |status | Histogram statistics created forcolumn 's_gmt_offset'. | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+-------------+-----------+----------+---------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0. 06sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT ... www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| promotions |total | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 3213210.07 |5966836.78| 53.85114741 | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 1.37sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

有了直方图,查询花了不到1.4秒,差不多提升了2倍。原因是:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

第一个执行计划,优化器选择了第一个派生表在store表上做了全表扫描,然后对表item, store_sales, date_dim, customer,customer_address分别做了主键查找。

www.solves.com.cn 电脑百科

但是,当MySQL意识到store表会比它猜测的返回更多的数据时,优化器会在item表上做全表扫描,然后对store_sales, store, date_dim, customer,customer_address 分别做主键查找。

www.solves.com.cn 电脑百科

| 为什么不用索引? www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

索引往往也能做上述工作,比如:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> CREATE INDEX s_gmt_offset_idx ON store (s_gmt_offset); www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

Query OK, 0rows affected ( 0. 53sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

Records:0Duplicates:0Warnings:0 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT ... www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| promotions |total | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 3213210.07 |5966836.78| 53.85114741 | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+------------+--------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 1.41sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

但是,用直方图而不是索引有以下两个原因:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

维护一个索引有代价。每一次的insert、update、delete都会需要更新索引,会对性能有一定的影响。而直方图一次创建永不更新,除非明确去更新它。所以不会影响insert、update、delete的性能。

www.solves.com.cn 电脑百科

如果有索引,优化器用使用index dives技术来估算符合条件范围的记录数量。这种方式也是有代价的,特别是查询语句条件中有很长的IN列表。直方图相对而言代价小,因此可能更合适。 www.solves.com.cn 电脑百科

| 检索统计直方图

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

统计直方图以JSON的形式存在数据字典中。可以用内建的JSON函数built-in JSON functions从直方图获取一些信息。举例来说,如果需要知道amount列的直方图的创建或者更新时间,可以用JSON unquoting extraction operator来获取信息:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql>SELECT www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->HISTOGRAM->>'$."last-updated"'AS last_updated

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->FROM INFORMATION_SCHEMA.COLUMN_STATISTICS

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->WHERE

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->SCHEMA_NAME = "sakila" www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND TABLE_NAME = "payment" www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

->AND COLUMN_NAME = "amount"; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| last_updated | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 2017-09-15 11:54:25.000000 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

如果要查找实际有多少个buckets,以及用analyze table时指定了多少个buckets,可以如下:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> TABLE_NAME,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> COLUMN_NAME, www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> HISTOGRAM- >>'$."number-of-buckets-specified"'AS num_buckets_specified,

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> WHERE www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> SCHEMA_NAME = "sakila";

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+--------------+-----------------------+---------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| TABLE_NAME |COLUMN_NAME | num_buckets_specified |num_buckets_created |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+--------------+-----------------------+---------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|payment | amount |32| 19 | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| payment |payment_date | 32 |32|

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+------------+--------------+-----------------------+---------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

经测试,num_buckets_created与字段的distinct值很接近,近似相等;但是num_buckets_created不会大于num_buckets_specified。如果num_buckets_created与num_buckets_specified相等,那么存在可能,在创建直方图的时候指定的buckets不够多,那么此时可以通过增加buckets的数量,来提高直方图的准确性。

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

buckets可以设置为1到1024 www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 优化器trace

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

如果你想要知道直方图做了什么,最简单的方式就是看一下执行计划: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1AND 10; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| id |select_type | table |partitions | type |possible_keys | key |key_len | ref |rows | filtered |Extra |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|1| SIMPLE |customer | NULL |ALL | NULL |NULL | NULL |NULL | 98633 |11.11| Using where | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset, 1warning ( 0. 00sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_day WITH 32BUCKETS;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+--------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| Table |Op | Msg_type |Msg_text | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+--------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|tpcds.customer | histogram |status | Histogram statistics created forcolumn 'c_birth_day'. |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----------------+-----------+----------+--------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0. 10sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1AND 10;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| id |select_type | table |partitions | type |possible_keys | key |key_len | ref |rows | filtered |Extra | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|1| SIMPLE |customer | NULL |ALL | NULL |NULL | NULL |NULL | 98633 |32.12| Using where | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset, 1warning ( 0. 00sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

可以看到filtered列,从默认的11.11%变成了更精确的32.12%。但是,如果有多个条件,有些有直方图,有些没有,就比较难判断优化器做了什么改进:

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day <= 20AND c_birth_year = 1967;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| id |select_type | table |partitions | type |possible_keys | key |key_len | ref |rows | filtered |Extra |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

|1| SIMPLE |customer | NULL |ALL | NULL |NULL | NULL |NULL | 98633 |6.38| Using where | www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset, 1warning ( 0. 00sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

如果想要知道更多关于直方图统计的细节,可以使用trace: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SET OPTIMIZER_TRACE = "enabled=on"; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

Query OK, 0rows affected ( 0.00sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

Query OK, 0rows affected ( 0.00sec)

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day <= 20AND c_birth_year = 1967; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT JSON_EXTRACT(TRACE, "$**.filtering_effect") FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| JSON_EXTRACT(TRACE, "$**.filtering_effect") |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------------------------------------------------------------------------------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| [[{"condition": "(`customer`.`c_birth_day` <= 20)", "histogram_selectivity": 0.6376}]]|

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+ ----------------------------------------------------------------------------------------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1row inset ( 0.00sec) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

这里用了JSON_EXTRACT从trace里取出相关的部分。对于每个条件,直方图被使用的话,就会看到估算过的字段的选择性。在这个例子里,通过直方图,对“c_birth_day <= 20”条件,估算出63.76%的数据满足条件。事实上,与实际的数据分布情况基本一致: www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

mysql> SELECT

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> (SELECT count(*) FROM customer WHERE c_birth_day <= 20) www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> / www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

-> (SELECT COUNT(*) FROM customer) AS ratio; www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| ratio |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

| 0.6376 |

www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

+--------+ www.solves.com.cn 电脑百科


www.solves.com.cn 电脑百科

1 row in set (0.03 sec) www.solves.com.cn 电脑百科

 

 

本文地址:解决网 http://www.solves.com.cn/doc-view-5467.html

TAGS:SOLVES , 电脑技术 , 电脑知识 , 网站建设, 电脑技术, 网络营销 , 网站托管 , 硬件技术 , 网络技术 , 软件技术, 维修网点

附件列表


按字母顺序浏览:A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

→我们致力于为广大网民解决所遇到的各种电脑技术问题
 如果您认为本词条还有待完善,请 编辑词条

上一篇为什么商家都退出淘宝?
下一篇微信小程序VS支付宝小程序,实体店老板要怎么选?

0
1. 本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
2. 本站内容仅供参考,如果您需要解决具体问题,建议您咨询相关领域专业人士。
3. 如果您没有找到需要的电脑技术问题词条,您可以到百科问答提问或创建词条,等待高手解答。

关于本词条的提问

查看全部/我要提问>>