网站首页 > 技术教程正文

MySQL8.0 · 优化器新特性 · Cost Model, 直方图优化器开销优化

goqiw 2024-10-10 08:12:30 技术教程 9 ℃ 0 评论

MySQL当前已经发布到MySQL8.0版本，在新的版本中，可以看到MySQL之前被人诟病的优化器部分做了很多的改动，由于笔者之前的工作环境是5.6，最近切换到最新的8.0版本，本文涵盖了一些本人感兴趣的和优化器相关的部分，主要包括MySQL5.7的cost model以及MySQL8.0的直方图功能。

本文基于当前最新的MySQL8.0.12版本，主要是讲下cost model 和 histogram的用法和相关代码

Cost Model

Configurable cost constants

为什么需要配置cost model常量 ? 我们知道MySQL已经发展了好几十年的历史，但是在优化器中依然使用了hardcode的权重值来衡量io, cpu等资源情况，而这些权重值实际上是基于多年前甚至十来年前的经验设定的。想想看，这么多年硬件的发展多么迅速。几十上百个核心的服务器不在少数甚至在某些大型公司大规模使用，ssd早就成为主流，NVME也在崛起。高速RDMA网络正在走入寻常百姓家。这一切甚至影响到数据库系统的实现和变革。显而易见，那些hardcode的权值已经过时了，我们需要提供给用户可定义的方式，甚至更进一步的，能够智能的根据硬件环境自动设定。

MySQL5.7引入两个新的系统表, 通过这两个系统表暴露给用户来进行更新，如下：

root@(none) 04:05:24>select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | NULL | 2018-04-23 13:55:20 | NULL | 20 |
| disk_temptable_row_cost | NULL | 2018-04-23 13:55:20 | NULL | 0.5 |
| key_compare_cost | NULL | 2018-04-23 13:55:20 | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 2018-04-23 13:55:20 | NULL | 1 |
| memory_temptable_row_cost | NULL | 2018-04-23 13:55:20 | NULL | 0.1 |
| row_evaluate_cost | NULL | 2018-04-23 13:55:20 | NULL | 0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)
其中default_value是generated column，其表达式已经固定死了默认值：
`default_value` float GENERATED ALWAYS AS (
(case `cost_name` 
when _utf8mb3'disk_temptable_create_cost' then 20.0 
when _utf8mb3'disk_temptable_row_cost' then 0.5 
when _utf8mb3'key_compare_cost' then 0.05 
when _utf8mb3'memory_temptable_create_cost' then 1.0 
when _utf8mb3'memory_temptable_row_cost' then 0.1 
when _utf8mb3'row_evaluate_cost' then 0.1 else NULL end)) VIRTUAL
root@(none) 04:05:35>select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name | cost_value | last_update | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default | 0 | io_block_read_cost | NULL | 2018-04-23 13:55:20 | NULL | 1 |
| default | 0 | memory_block_read_cost | NULL | 2018-04-23 13:55:20 | NULL | 0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+

你可以通过update语句来进行更新, 例如：

root@(none) 04:05:52>update mysql.server_cost set cost_value = 40 where cost_name = 'disk_temptable_create_cost';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@(none) 04:07:13>select * from mysql.server_cost where cost_name = 'disk_temptable_create_cost';
+----------------------------+------------+---------------------+---------+---------------+
| cost_name | cost_value | last_update | comment | default_value |
+----------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost | 40 | 2018-06-23 16:07:05 | NULL | 20 |
+----------------------------+------------+---------------------+---------+---------------+
1 row in set (0.00 sec)
//更新后执行一次flush optimizer_costs操作来更新内存
//但老的session还是会用老的cost数据
root@(none) 10:10:12>flush optimizer_costs;
Query OK, 0 rows affected (0.00 sec)

可以看到用法也非常简单，上面包含了两张表：server_cost及engine_cost，分别对server层和引擎层进行配置

相关代码:

全局cache Cost_constant_cache

全局cache维护了一个当前的cost model信息, 用户线程在lex_start时会去判断其有没有初始化本地指针，如果没有的话就去该cache中将指针拷贝到本地

初始化全局cache:

Cost_constant_cache::init
:
创建Cost_model_constants， 其中包含了两类信息: server层cost model和引擎层cost model, 类结构如下：
Cost_constant_cache ----> Cost_model_constants
 ---> Server_cost_constants
 //server_cost
 ---> Cost_model_se_info 
 --->SE_cost_constants
 //engine_cost 如果存储引擎提供了接口函数get_cost_constants的话,则从存储引擎那取

从系统表读取配置，适用于初始化和flush optimizer_costs并更新cache：

read_cost_constants()
|--> read_server_cost_constants
|--> read_engine_cost_constants

由于用户可以动态的更新系统表，执行完flush optimizer_costs后，有可能老的版本还在被某些session使用，因此需要引用计数，老的版本ref counter被降为0后才能被释放

线程cost model初始化

Cost_model_server

在每个线程的thd上，挂了一个Cost_model_server的对象THD::m_cost_model, 在lex_start()时，如果发现线程的m_cost_model没有初始化，就会去获取全局的指针，存储到本地：

Cost_model_server::init
const Cost_model_constants *m_cost_constants = cost_constant_cache->get_cost_constants();
// 会增加一个引用计数，以确保不会在引用时被删除
const Server_cost_constants *m_server_cost_constants = m_cost_constants->get_server_cost_constants();
// 同样获取的是全局指针

可见thd不创建自己的cost model, 只引用cache中的指针

Table Cost Model

struct TABLE::m_cost_model, 类型：Cost_model_table

其值取自上述thd中存储的cost model对象

Cost_estimate

统一的对象类型cost_estimate来存储计算的cost结果，包含四个维度：

 double io_cost; ///< cost of I/O operations
 double cpu_cost; ///< cost of CPU operations
 double import_cost; ///< cost of remote operations
 double mem_cost; ///< memory used (bytes)

未来

目前来看，除非根据工作负载，经过充分的测试才能得出合理的配置值，但如何配置，什么是合理的值，个人认为应该是可以自动调整配置的。关键是找出配置和硬件条件的对应关系。这也是我们未来可以努力的一个方向。

reference:

1. Cost Model官方文档

2. 官方博客1:The MySQL Optimizer Cost Model Project

3. 官方博客2: A new dimension to MySQL query optimizations

4. Optimizer Cost Model Improvements in MySQL 5.7.5 DMR

5.Slide: MySQL Cost Model

Related Worklog:

WL#7182: Optimizer Cost Model API

WL#7209: Handler interface changes for new cost model

WL#7276: Configuration data base for Optimizer Cost Model

WL#7315 Optimizer cost model: main memory management of cost constants

WL#7316 Optimizer cost model: Command for online updating of cost model constants

Histogram

直方图也是MySQL一个万众期待的功能了，这个功能实际上在其他数据库产品中是很常见的，可以很好的指导优化器选择执行路径。利用直方图存储了指定列的数据分布。MariaDB从很早的10.0.2版本支持这个功能，而MySQL在最新的8.0版本中也开始支持

使用

MySQL里使用直方图是通过ANALYZE TABLE语法来执行：

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] ...

举个简单的例子：

直方图统计信息存储于InnoDB数据词典中，可以通过information_schema表来获取

root@information_schema 05:34:49>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 `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 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.00 sec)

从column_statistics表的定义可以看到，有一个名为mysql.column_statistics系统表，但被隐藏了，没有对外暴露

以下举个简单的例子：

root@sb1 05:58:55>ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k WITH 4 BUCKETS;
+-------------+-----------+----------+----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-----------+----------+----------------------------------------------+
| sb1.sbtest1 | histogram | status | Histogram statistics created for column 'k'. |
+-------------+-----------+----------+----------------------------------------------+
1 row in set (0.63 sec)
# 查询表上的直方图信息
root@sb1 06:00:43>SELECT JSON_PRETTY(HISTOGRAM) FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME='sb1' AND TABLE_NAME = 'sbtest1'\G
*************************** 1. row ***************************
JSON_PRETTY(HISTOGRAM): {
 "buckets": [
 [
 38671,
 99756,
 0.249795,
 17002
 ],
 [
 99757,
 100248,
 0.500035,
 492
 ],
 [
 100249,
 100743,
 0.749945,
 495
 ],
 [
 100744,
 172775,
 1.0,
 16630
 ]
 ],
 "data-type": "int",
 "null-values": 0.0,
 "collation-id": 8,
 "last-updated": "2018-09-22 09:59:30.857797",
 "sampling-rate": 1.0,
 "histogram-type": "equi-height",
 "number-of-buckets-specified": 4
}
1 row in set (0.00 sec)

从输出的json可以看到，在执行了上述语句后产生的直方图，有4个bucket，数据类型为Int, 类型为equi-height，即等高直方图(另外一种是等宽直方图，即SINGLETON)。每个Bucket中，描述的信息包括：数值的上界和下界, 频率以及不同值的个数。通过这些信息可以获得比较精确的数据分布情况，从而优化器来根据这些统计信息决定更优的执行计划。

如果列上存在大量的重复值，那么MySQL也可能选择等宽直方图，例如上例，我们将列k上的值更新为一半10一半为20，那么出来的直方图数据如下：

root@sb1 10:41:17>SELECT JSON_PRETTY(HISTOGRAM) FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME='sb1' AND TABLE_NAME = 'sbtest1'\G
*************************** 1. row ***************************
JSON_PRETTY(HISTOGRAM): {
 "buckets": [
 [
 10,
 0.499995
 ],
 [
 20,
 1.0
 ]
 ],
 "data-type": "int",
 "null-values": 0.0,
 "collation-id": 8,
 "last-updated": "2018-09-22 14:41:17.312601",
 "sampling-rate": 1.0,
 "histogram-type": "singleton",
 "number-of-buckets-specified": 100
}
1 row in set (0.00 sec)

如上，对于SINGLETON类型，每个bucket只包含两个值：列值，及对应的累计频率（即百分之多少的数据比当前Bucket里的值要小或相等）

注意这里的sampling-rate, 这里的值为1，表示读取了表上所有的数据来进行统计，但通常对于大表而言，我们可能不希望读太多的数据，因为可能产生过度的内存消耗，因此MySQL还提供了一个参数histogram_generation_max_mem_size来限制内存的使用上限。

如果表上的DML不多，那直方图基本是稳定的，但频繁写入的话，那我们就可能需要去定期更新直方图，MySQL本身不会去主动更新。

优化器通过histogram来计算列的过滤性，大多数的谓词都可以使用到。具体参阅官方文档

关于直方图影响查询计划，这篇博客及这篇博客

网站首页 > 技术教程正文

MySQL8.0 · 优化器新特性 · Cost Model, 直方图优化器开销优化

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

取消回复欢迎你发表评论:

网站首页 > 技术教程 正文

MySQL8.0 · 优化器新特性 · Cost Model, 直方图优化器开销优化

猜你喜欢

本文暂时没有评论，来添加一个吧(●'◡'●)

取消回复欢迎 你 发表评论:

网站首页 > 技术教程正文

取消回复欢迎你发表评论: