关于mysql:4直方图介绍和使用MySQL索引学习

40次阅读

共计 9195 个字符，预计需要花费 23 分钟才能阅读完成。

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

[toc]

一、导读

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_statistics\G
*************************** 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_statistics\G;
*************************** 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_statistics\G;
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_TRACE\G;

# 能够看到执行打算外面用到了 `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

Enjoy GreatSQL :)

文章举荐：

面向金融级利用的 GreatSQL 正式开源
https://mp.weixin.qq.com/s/cI…

Changes in GreatSQL 8.0.25 (2021-8-18)
https://mp.weixin.qq.com/s/qc…

MGR 及 GreatSQL 资源汇总
https://mp.weixin.qq.com/s/qX…

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

在 Linux 下源码编译装置 GreatSQL/MySQL
https://mp.weixin.qq.com/s/WZ…

# 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:

https://gitee.com/GreatSQL/Gr…

GitHub:

https://github.com/GreatSQL/G…

Bilibili：

https://space.bilibili.com/13…

微信 &QQ 群：

可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697

微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

正文完
mysql
发表至： mysql
2022-04-26
 0
关于mysql:记录一次mysqlbinlog恢复过程
关于mysql:火爆全网的MySQL性能优化手册居然让我直接刷进腾讯
关于索引:记一次线上SQL索引优化及索引选择错误原理分析
关于db-mysql:MySQL架构三-MySQL运行机制