4。直方图介绍和使用MySQL索引学习
一、导读 二、步骤2.1 SQL语句2.2 直方图案例2.3 查看直方图统计信息2.3 直方图分类2.4 使用限制 三、直方图使用案例 四、直方图资源消耗 五、总结 一、导读
MySQL 8.0.19 开始支持对InnoDB引擎表数据进行采样以生成直方图统计信息。
直方图(Histogram)是关系型数据库中提供的一种基础的统计信息,最典型的用途是估计查询谓词的选择率,以便选择优化的查询执行计划。
常见的直方图种类有:等宽直方图、等高直方图。 二、步骤2.1 SQL语句# 创建直方图 ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] ... [WITH N BUCKETS] # 删除直方图 ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name] ... 2.2 直方图案例
创建直方图,更新就是等于创建,会进行重新采样 mysql> analyze table t1 update histogram on tcol01 with 2 buckets; +---------+-----------+----------+---------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-----------+----------+---------------------------------------------------+ | test.t1 | histogram | status | Histogram statistics created for column "tcol01". | +---------+-----------+----------+---------------------------------------------------+ 1 row in set (6.38 sec)
删除直方图 mysql> analyze table t1 drop histogram on tcol01; +---------+-----------+----------+---------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-----------+----------+---------------------------------------------------+ | test.t1 | histogram | status | Histogram statistics removed for column "tcol01". | +---------+-----------+----------+---------------------------------------------------+ 1 row in set (0.02 sec) 2.3 查看直方图统计信息
查看直方图的视图信息 mysql> show create table information_schema.column_statisticsG *************************** 1. row *************************** View: COLUMN_STATISTICS Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS `SCHEMA_NAME`,`mysql`.`column_statistics`.`table_name` AS `TABLE_NAME`,`mysql`.`column_statistics`.`column_name` AS `COLUMN_NAME`,`mysql`.`column_statistics`.`histogram` AS `HISTOGRAM` from `mysql`.`column_statistics` where (0 <> can_access_table(`mysql`.`column_statistics`.`schema_name`,`mysql`.`column_statistics`.`table_name`)) character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.01 sec)
可以通过 information_schema.column_statistics 查看,会列出所有直方图信息mysql> select * from information_schema.column_statisticsG; *************************** 1. row *************************** SCHEMA_NAME: test TABLE_NAME: t_user COLUMN_NAME: age HISTOGRAM: {"buckets": [[1, 0.00002000013333422223], [10, 0.23445489636597577], [11, 0.46630977539850266], [12, 0.5326868845792305], [13, 0.5991973279821865], [14, 0.665747771651811], [15, 0.7325715504770032], [16, 0.7999486663244422], [17, 0.8668091120607471], [18, 0.9329928866192441], [19, 0.9999766665111101], [127, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-04-21 06:53:35.194420", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100} ...... 2.3 直方图分类
对于等宽直方图,每个桶包含两个值,大致信息如下 SCHEMA_NAME: test # 库名 TABLE_NAME: t1 # 表名 COLUMN_NAME: tcol01 # 列名 HISTOGRAM: { "buckets":[ [ 0, # 1.桶的值,表中实际数据的取值。类型是取决于字段数据类型,比如下面是`int`类型。 0.06585605673110825 # 2.取值频率,桶的值出现的大致频率,double类型。 ], ...... ], "data-type":"int", # 数据类型 "null-values":0, # 是否有NULL值 "collation-id":8, "last-updated":"2022-04-21 06:59:55.850333", # 桶最后更新时间,不会自动更新 "sampling-rate":0.4059331843720921, # 采样率,如果是1,表示采集所有数据 "histogram-type":"singleton", # 桶类型,等宽 "number-of-buckets-specified":100 # 桶数量 }
对于等高直方图,每个桶中包含四个值,大致信息如下 SCHEMA_NAME: test TABLE_NAME: t1 COLUMN_NAME: tcol10 HISTOGRAM: { "buckets":[ [ "2021-04-18 12:12:00.000000", # 1.最小值 "2021-04-22 05:05:56.000000", # 2.最大值 0.010002279268725782, # 3.桶的值出现的大致频率,double类型 3523 # 4.桶值出现的次数 ], ...... ], "data-type":"datetime", "null-values":0, "collation-id":8, "last-updated":"2022-04-21 07:00:43.232745", "sampling-rate":0.18943548604030958, "histogram-type":"equi-height", # 桶类型,等高 "number-of-buckets-specified":100 } 2.4 使用限制
直方图是对表进行操作,可以看下不同的表类型对直方图的支持情况: 1、直方图支持 InnoDB ,NDB ,MyISAM 表类型,支持分区表 类型,不支持 views 类型。2、加密表不支持创建,主要还是为了避免相关数据泄漏。 3、临时表不支持创建,连接会话关闭后,临时表就被清理了,也就没有创建必要。 4、单列唯一索引、主键索引不支持创建直方图,创建的时候会提示 Error ,并输出错误提示The column "id" is covered by a single-part unique index. 5、几何类型(空间数据)和JSON类型也暂时不支持直方图。
直方图采集的基本单位是表中的列数据,也就是当列数据或类型发生变更或删除的时候直方图可能也会出现相应变化,经过测试有如下情况: 1、当表被删除的时候,直方图会同步被删除, truncte操作不会有影响,同理insert、delete、update也不会有影响 。2、当修改字段类型、字段长度,直方图会被删除。 3、当修改表名后,直方图 TABLE_NAME 字段会同步更新,和新表建立关联。4、修改字段字符集后,直方图会被删除。例如 ALTER TABLE t1006 MODIFY utf8 VARCHAR(64) CHARACTER SET latin1;
其他注意点: 1、ANALYZE TABLE 分析过程需要从表定义缓存中删除表,所以该过程会产生一个 flush锁 。如果有长时间运行的语句或事务仍在使用表,则后续语句和事务必须等待这些操作完成后才释放flush锁 。2、直方图把统计数据存储在数据字典的的统计表内,所以当 innodb_read_only 参数开启的时候,可能由于无法更新数据字典t统计信息导致执行失败,三、直方图使用案例
先查看下 t_user 上age 各个年龄段的人数;当前t_user 上age 字段没有建立索引。mysql> select age,count(id) from t_user group by age; +------+-----------+ | age | count(id) | +------+-----------+ | 11 | 69556 | | 1 | 6 | | 127 | 7 | | 10 | 70330 | | 19 | 20095 | | 13 | 19953 | | 18 | 19855 | | 12 | 19913 | | 14 | 19965 | | 17 | 20058 | | 15 | 20047 | | 16 | 20213 | +------+-----------+ 12 rows in set (0.18 sec)
解析查询年龄段 > 10 age < 12 。
通过分析,可以看出执行过程 type=ALL 走了全表扫描,filtered=11.11 过滤比例还是比较低的,同时表上没有建立索引,所以key=NuLL 。mysql> explain select * from t_user where age>10 and age<12; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 299131 | 11.11 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
创建一个直方图;根据步骤1查出来,年龄段大概分类12,所以我们建立12个桶 mysql> analyze table t_user update histogram on age with 12 buckets; +-------------+-----------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------+-----------+----------+------------------------------------------------+ | test.t_user | histogram | status | Histogram statistics created for column "age". | +-------------+-----------+----------+------------------------------------------------+ 1 row in set (0.06 sec) # 查看建立的直方图信息 mysql> select * from information_schema.column_statisticsG; SCHEMA_NAME: test TABLE_NAME: t_user COLUMN_NAME: age HISTOGRAM: {{ "buckets": [ [1, 0.0002608242044861763], [10, 0.27339593114241006], [11, 0.5397496087636933], [12, 0.5968179447052686], [13, 0.6553990610328638], [14, 0.7131977047470005], [15, 0.7706311945748565], [16, 0.8261345852895148], [17, 0.8855503390714657], [18, 0.9423056859676577], [19, 0.9996870109546165], [127, 1.0] ], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-04-24 03:00:47.361704", "sampling-rate": 0.059696731054764834, "histogram-type": "singleton", "number-of-buckets-specified": 12 } 3 rows in set (0.00 sec)
再进行查询分析
通过分析,可以看出执行过程 type=ALL 在建立直方图后也是走了全表扫描,filtered=39.22 过滤有显著提升。mysql> explain select * from t_user where age>10 and age<12; +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 299131 | 39.22 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
总体而言,从两次执行计划的差别可以看出建立直方图后,filtered 列 39.22 比 没有建立直方图 11.11 值有更好的过滤效果。
PS:关于 filtered 列,这个字段表示存储引擎返回的数据在Server层过滤后,剩下多少满足查询的记录数量的比例。
进一步通过开启 TRACE 查看执行计划
由于优化器会默认的认为各个年龄段的数据分布是均匀的,所以当没有直方图扫描的范围会比较大,相应的在Server层过滤的数据也就较少。
同理,在有直方图的情况下,优化器可以通过直方图来分析年龄段的数据分布,从而调整扫描范围,过滤更多数据。 mysql> SET OPTIMIZER_TRACE = "enabled=on"; mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000; mysql> explain select * from t_user where age>10 and age<12; mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACEG; # 可以看到执行计划里面用到了`histogram_selectivity` "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`t_user`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 299131, "filtering_effect": [ { "condition": "(`t_user`.`age` > 10)", "histogram_selectivity": 0.726604 }, { "condition": "(`t_user`.`age` < 12)", "histogram_selectivity": 0.53975 } ], "final_filtering_effect": 0.392184, "access_type": "scan", "resulting_rows": 117314, "cost": 30193.9, "chosen": true } ] 四、直方图资源消耗
生成直方图需要对数据进行采样分析,这个过程需要消耗一定的内存资源和IO资源。
我们可以通过监控来查看下该过程需要消耗多少资源,当然我们也可以通过 sys.x$memory_by_thread_by_current_bytes 视图来对比采样前后内存的差值变化来大致预估直方图创建过程需要消耗多少内存,详细步骤可移步老叶茶馆阅读 https://mp.weixin.qq.com/s/7FI87f6t3UvbE9GGhw8iVA
另外可以通过调整参数 set session histogram_generation_max_mem_size = 1000000; 来限制内存的使用,同时这样也会相应降低采样率 。五、总结
本文对直方图的内容进行简单的介绍,篇幅所限,更多细节内容请移步官网进行查看,另外关于列中已经有索引的情况下,优化器会如何选择执行计划,篇幅所限,以后再进行测试。 https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html https://mp.weixin.qq.com/s/7FI87f6t3UvbE9GGhw8iVA
2000以内的超高性价比手机推荐纯主观想法真我GTneo闪速版与真我GTneo普通版这两款机型硬件配置上除了充电之外基本相同,闪速版拥有着65w的vooc闪充而普通版只有50w的vooc闪充,两者都搭载天玑1200处理器,
相机镜头的一些了解最近因为工作关系,和相机与镜头打交道,了解了一些这方面的知识。1快门(1)快门速度快门是照相机用来控制感光片有效曝光时间的机构。是照相机的一个重要组成部分,它的结构形式及功能是衡量
索尼发布三款视频向的全画幅小巧定焦镜头均价4699索尼刚刚发布了三款面向视频用家的定焦镜头,分别是FE50mmF2。5GFE40mmF2。5G以及FE24mmF2。8G,虽然不是f1。8但是这个光圈大小对于全画幅视频用户来说还是不
8533MHz!JEDEC新修订LPDDR5X规格频率惊人内存行业标准组织JEDEC刚刚发布了LPDDR5内存标准JESD2095B的新修订版,按照这份新的标准,除了在性能和功率作了改进以外,还对LPDDR5进行了扩展,提出了名为LPDD
腾龙推出28150mmF22。8DiIIIVXD镜头自从进入无反时代后后,腾龙推出的新镜头基本上都是做一些原厂不存在的规格,例如之前大受欢迎的2875mmF2。8(A036)以及70180F2。8,它们的特点是舍弃部分焦段强调轻便性
苹果亲自承认,iOS14。7存在一严重BUG苹果在前天(7月20日)凌晨一点的时候才刚刚为iPhone手机用户推送了iOS14。7正式版版本。紧接着打脸的事情就发生了,那就是该版本存在着问题,并且苹果官方也出来亲自承认了该版
TFZKINGRS动圈耳塞,售价1499元,声音却无疑堪称高级近两年,耳机市场的竞争日益白热化,这种白热化体现在品牌越来越多新品越来越多且新技术的应用越来越频繁而在这一系列无休止的激烈攻杀中,许多国产品牌似乎越来越勇猛,越来越占据上风。一个明
美女郭教练说健身探索君学健身系列郭教练1本人健身也有很长一段时间了,虽然中途受到其它事情的影响,断断续续的,但是一直还是坚持了下来。在健身的过程中,始终有几个疑惑。就是每隔一段时间,身体就会出现这
兴唐传瓦岗山异闻录人物解读樊虎五哥说瓦岗系列樊虎影视形象樊虎,最早加入管毅一行人,同时也是最尽职的龙套。龙套归龙套,大白对其还是很偏爱的,不难发现,基本上几个主要剧情里都有他的存在。临潼山救人,和管毅一起出现的
耗时7年的大型实验证明少吃一点多动一点根本减不了肥探索君说减肥系列发电厂1假如我们在管理一家发电厂,我们每天可以收到,并且燃烧100吨煤,同时仓库里还有一些是为了防止出现发电量不够用的情况,以备不时之需。但突然出现了问题,我们以后
Windows11现重大Bug现象一重启电脑后进入系统,任务栏直接卡死,一片惨白,不管是重启资源管理器,重启电脑,都无法恢复。桌面右键无法使用,但还是有一些软件可以打开。现象二windows11翻车了,自动更新