谁便宜就选谁---基于成本的优化

robch

已于 2024-09-03 11:07:08 修改

阅读量867

点赞数 21

分类专栏：数据库文章标签： android

于 2024-09-01 12:31:11 首次发布

本文链接：https://blog.csdn.net/cpxsxn/article/details/141528022

版权

数据库专栏收录该内容

36 篇文章 0 订阅

订阅专栏

什么是成本

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下面这两个方面组成的：

I/O成本
  我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，
  当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。
  这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本
  读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大佬规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数我们后边再说。

单表查询的成本

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价

3.1. 范围区间数量
3.2. 需要回表的记录数: 需要确定区间最左记录和区间最右记录之间的数量
优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算idx_key2在(10, 1000)这个范围区间中包含多少二级索引记录，计算过程是这样的：
- 先根据key2 > 10这个条件访问一下idx_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录。
  我们前头说过在B+数树中定位一条记录的过程是贼快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的。
- 然后再根据key2 < 1000这个条件继续从idx_key2对应的B+树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。
- 如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。
  否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。
  那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：
  如图，我们假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页c之间有多少页面，而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点（也就是页a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就贼小了。
  不过还有问题，如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个页面中该咋办？继续递归啊，也就是再统计页b和页c对应的目录项记录所在页之间有多少个页面。之前我们说过一个B+树有4层高已经很了不得了，所以这个统计过程也不是很耗费性能。
3.3. 根据这些记录里的主键值到聚簇索引中做回表操作
3.4. 回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
对比各种执行方案的代价，找出成本最低的那一个

连接查询的成本

对于两表连接查询来说，它的查询成本由下面两个部分构成：

单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

调节成本参数

一条语句的执行其实是分为两层的：server层、存储引擎层。在 server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中。

Good SQL

show engine innodb status
show tabel status like ‘table_name'
show tables from 'db_name' like 'table_name'
show index from table_name

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2021-09-08 16:23:46 | NULL    |
| disk_temptable_row_cost      |       NULL | 2021-09-08 16:23:46 | NULL    |
| key_compare_cost             |       NULL | 2021-09-08 16:23:46 | NULL    |
| memory_temptable_create_cost |       NULL | 2021-09-08 16:23:46 | NULL    |
| memory_temptable_row_cost    |       NULL | 2021-09-08 16:23:46 | NULL    |
| row_evaluate_cost            |       NULL | 2021-09-08 16:23:46 | NULL    |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2021-09-08 16:23:46 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2021-09-08 16:23:46 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+
2 rows in set (0.00 sec)
mysql>