码到三十五 ： 个人主页
在数据库管理系统中，查询优化器是一个至关重要的组件，它负责将用户提交的SQL查询转换为高效的执行计划。在MySQL中，查询优化器使用了一个称为“成本模型”的机制来评估不同执行计划的优劣，并选择此中成本最低的谁人。本文将深入探究MySQL的成本模型，以及如何使用这一知识来优化查询性能。
一、成本模型简介
成本模型是查询优化器用来估算查询执行成本的一组规则和算法。对于给定的查询，优化器会考虑多种可能的执行计划，并使用成本模型来预测每种计划的执行效率。执行成本通常是一个抽象的数值，它综合了CPU时间、I/O操作、内存使用等多个因素。
在MySQL中，成本模型主要基于以下几个方面的考量：
- 数据表的统计信息：包罗表的行数、列的基数（不同值的数量）、索引的唯一性等。这些信息对于评估查询的过滤效果和索引的选择性至关重要。
- 索引的使用：索引可以显著提高查询性能，但并非所有情况下都是最优选择。成本模型会评估使用索引带来的I/O减少与索引维护成本之间的衡量。
- 毗连操作：对于涉及多个表的查询，成本模型会考虑不同毗连计谋（如嵌套循环毗连、哈希毗连等）的成本。
- 排序和分组操作：这些操作通常须要额外的CPU和内存资源。成本模型会估算不同排序和分组计谋的成本，并选择最优方案。
二、优化器如何工作
MySQL的查询优化器在执行查询之前会经历以下几个步骤：
- 解析查询：将SQL文本转换为抽象语法树（AST）。
- 预处理：检查查询的语义正确性，举行常量折叠等优化。
- 查询重写：根据规则和开导式方法修改原始查询，以简化布局或提高性能。
- 天生执行计划：考虑所有可能的执行路径，并使用成本模型评估每种路径的成本。
- 选择最优执行计划：根据成本模型的估算效果，选择成本最低的执行计划。
- 执行查询：按照选定的执行计划执行查询并返回效果。
三、如何使用成本模型优化查询
了解MySQL的成本模型对于数据库管理员和开发来说是非常有价值的。下面的一些实践发起可以帮助你使用成本模型来优化查询性能：
- 保持统计信息更新：定期运行ANALYZE TABLE下令来更新表的统计信息，确保优化器有正确的数据来评估查询成本。
- 合理设计索引：根据查询模式和数据分布来设计索引，克制过分索引导致的性能降落。使用EXPLAIN下令来检查查询是否使用了符合的索引。
- 优化查询语句：简化复杂的SQL查询，克制不须要的毗连、子查询和计算。使用索引覆盖扫描（Covering Index）来减少数据查找的开销。
- 调解设置参数：某些MySQL设置参数会影响成本模型的计算方式。比方，optimizer_search_depth参数可以控制优化器搜索执行计划的深度。根据你的硬件环境和查询负载来调解这些参数。
- 监控和分析：使用性能监控工具（如Percona Monitoring and Management, PMM）来跟踪查询的性能指标，并找出性能瓶颈。团结EXPLAIN下令的输出和慢查询日记来分析题目查询的执行计划。
四、成本值的存储和设置
MySQL在server_cost和engine_cost这两个系统表中存储了默认的成本值。这些表位于MySQL的系统数据库中（通常是mysql数据库）。服务器在启动时会读取这些成本值到内存中，以便在运行时使用。如果须要，管理员可以通过执行特定的下令（如FLUSH OPTIMIZER_COSTS）来重新从磁盘加载成本表。
重要的是这些成本值是特定于服务器的，而且不会复制到副本或备用服务器。这意味着每台服务器的成本模型可能会根据其硬件设置、工作负载和性能调优计谋而有所不同。
常用的成本条目
- row_evaluate_cost（默认值通常为0.2）：这个成本值代表处理一行数据时的CPU成本。随着查询须要处理的行数增加，这个成本也会相应增加。计算公式是：CPU成本 = 行数 * row_evaluate_cost。
- io_block_read_cost 和 memory_block_read_cost（默认值通常为1.0）：这两个成本值分别代表从磁盘和内存中读取一个数据块（通常是一个数据页，大小约为16KB）的成本。IO成本的计算公式是：IO成本 = (总数据大小（以字节为单位）/ 1024) * io_block_read_cost 或 memory_block_read_cost。
- disk_iotask_cost（磁盘I/O任务成本）：这个值表示执行一次磁盘I/O操作的成本。由于磁盘I/O操作通常比内存操作要慢得多，因此这个成本值相对较高。优化器在考虑是否使用索引或举行全表扫描时会考虑这个成本。
- key_compare_cost（键比较成本）：当MySQL使用索引来过滤数据时，须要对索引键举行比较。这个成本条目表示举行一次键比较的成本。这个值通常较低，因为键比较操作相对较快。
- memory_temptable_create_cost（内存临时表创建成本）：在某些查询中，MySQL可能须要创建临时表来存储中心效果。这个成本条目表示在内存中创建一个临时表的成本。如果内存不足，MySQL可能会选择使用磁盘来存储临时表，这会增加I/O成本。
- memory_temptable_batch_row_cost（内存临时表批量行成本）：当向内存临时表中插入多行数据时，这个成本条目表示每插入一批数据的成本。这个值通常较低，因为批量插入比单独插入每一行要高效。
- disk_temptable_create_cost（磁盘临时表创建成本）：如果MySQL选择在磁盘上创建临时表，这个成本条目表示创建磁盘临时表的成本。这个值通常比内存临时表创建成本要高，因为磁盘操作更慢。
- disk_temptable_batch_row_cost（磁盘临时表批量行成本）：雷同于内存临时表批量行成本，但这个成本条目是针对磁盘临时表的。它表示向磁盘临时表中批量插入数据的成本。
- sort_merge_passes（排序归并传递成本）：在举行排序操作时，如果数据量很大且内存不足，MySQL可能须要使用归并排序算法。这个成本条目表示举行一次归并传递的成本。归并排序涉及多次归并传递，因此这个成本在评估排序操作的总体成本时很重要。
要获取特定MySQL实例中这些成本条目标现实值，可以查询mysql系统数据库中的server_cost和engine_cost表：
- SELECT * FROM mysql.server_cost;
- SELECT * FROM mysql.engine_cost;
要检察特定表的信息，包罗其数据大小（Data_length字段），可以执行以下SQL查询：
- SHOW TABLE STATUS LIKE 'your_table_name';
五、全表扫码成本计算
MySQL 优化器会考虑那些因素来决定是否执行全表扫描，以及如何计算其成本的呢，下面我们来基于成本原理计算一下：
我们有一个 employees 表，此中包含员工信息，如 ID、姓名、部分和薪水等。该表具有以下特点：
- 表大小：约 1GB（这取决于每行数据的大小和总行数）
- 总行数：5,000,000 行
- 每行数据大小：约 200 字节（包罗所有字段）
- 数据页大小：16KB（InnoDB 默认页大小）
- 存储引擎：InnoDB
- 无有用索引：对于我们要执行的特定查询，没有可以使用的索引
成本计算步骤
- 确定命据页数量：
- 起首，计算表占用的数据页数量。由于每行数据约 200 字节，每个数据页 16KB，每个数据页可以容纳大约 80 行数据（16,384 字节 / 200 字节 = 81.92，取整为 80）。
- 因此，整个表占用的数据页数量为 5,000,000 行 / 80 行/页 = 62,500 页。
- I/O 成本计算：
- 假设每次从磁盘读取一个数据页的成本是 1.0（这个值可能因硬件性能而异）。
- I/O 成本 = 数据页数量 × 每次读取成本 = 62,500 页 × 1.0 = 62,500。
- CPU 成本计算：
- CPU 成本通常与须要处理的行数成正比。假设每行数据处理的 CPU 成本是 0.2（这个值也是假设的，现实值可能不同）。
- CPU 成本 = 总行数 × 每行处理成本 = 5,000,000 行 × 0.2 = 1,000,000。
- 总成本计算：
- 总成本 = I/O 成本 + CPU 成本 = 62,500 + 1,000,000 = 1,062,500。
这个总成本是一个估算值，用于与优化器考虑的其他查询执行计划（如使用索引）举行比较。请注意，这里的成本是一个相对值，用于比较不同执行计划的优劣，而不是一个绝对值或货币成本。
优化器决策
基于上述成本计算，如果优化器发现使用索引的成本低于全表扫描的成本，它会选择使用索引。否则，如果没有符合的索引或全表扫描被认为更高效（比方，在须要检索表中大部分行的情况下），优化器将选择全表扫描。
现实考虑因素
在现实应用中，全表扫描的成本会受到多种因素的影响：
- 缓存中的数据：如果表的部分或全部数据已经缓存在内存中（如 InnoDB 的缓冲池），则现实的 I/O 成本可能会低落。
- 系统负载：高并发环境下的系统负载可能会影响 CPU 和 I/O 的性能。
- 表的布局和存储格式：表的列数、数据范例和存储格式（如压缩）都会影响数据的存储和检索效率。
- 硬件和设置：服务器的硬件设置（如 CPU 速度、内存大小、存储性能）和 MySQL 的设置设置（如缓冲区大小、I/O 干系参数）也会对全表扫描的成本产生显著影响。
结语
MySQL的成本模型是查询优化器的核心组件之一，它对于天生高效的执行计划至关重要。通过深入了解成本模型的工作原理，并团结现实的查询优化实践，可以显著提高数据库的性能和相应速度。
听说...关注下面公众号的人都变牛了，纯技术，纯干货 !
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
