Mysql内核成本计算

最新推荐文章于 2024-04-05 21:42:08 发布

码农1008

最新推荐文章于 2024-04-05 21:42:08 发布

阅读量327

点赞数 2

分类专栏： java‘ 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_41435009/article/details/126991131

版权

java‘ 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

前言

Mysql一张表中往往建立多个索引，那么多对于多个索引，Mysql内核如果去做选择的呢？接下来就为大家介绍下MySQL5.6以及之后的版本中提出的一个optimizer trace的功能。

例子分析

首先我们查看trace是否开启，通过SHOW VARIABLES LIKE ‘optimizer_trace’ 查看,如果显示enabled=off,one_line=off，那么可以通过
SET optimizer_trace=“enabled=on”;开启。关闭SET optimizer_trace=“enabled=off”;

SELECT * FROM vehicle_info;

执行sql面语句:

 SELECT * FROM vehicle_info where buy_date < '2022-09-01 12:12:12';

在这里插入图片描述
从上面TRAEC字段中可以看到优化后执行的sql:如下:
思考:从下面的执行结果中我们也可以看出尽量少用 *这种查询。

"steps": [
          {
            "expanded_query": "/* select#1 */ select `vehicle_info`.`id` AS `id`,`vehicle_info`.`vms_id` AS `vms_id``vehicle_info`.`reapiring_id` AS `reapiring_id` from `vehicle_info` where (`vehicle_info`.`buy_date` < '2022-09-01 12:12:12')"
          }
        ]

从这个结果中我们可以看到扫描了45366条数据，cost为11545

range_analysis": {
                  "table_scan": {
                    "rows": 45366,
                    "cost": 11545
                  },
                  }

使用idx_vehicleinfo_buydate这条索引扫描22683，cost为27221

"range_scan_alternatives": [
                      {
                        "index": "idx_vehicleinfo_buydate",
                        "ranges": [
                          "NULL < buy_date < 0x6310311c"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 22683,
                        "cost": 27221,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],

所以综合对比mysql会选择不走索引，cost计算结果并不是具体的执行时间，只是在一次执行中大小计算的比较。

如何计算成本

通用上面的分析,优化器最终会选择成本最低的那种方案来作为最终的执行计划。但是这些成本怎么来的呢？搞明白了这些成本的由来，就不会再有诸如“为什么MySQL一定要全文扫描，不用索引呢？为什么MySQL要用A索引不用B索引之类的疑问？”了，因为以上的答案都可以用成本分析来解答。所以接下来，我们就要深入MySQL的内核来看看这些成本是如何计算的。

1.什么是成本:
MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，
或者说代价最低的那种方案去真正的执行查询。什么是执行成本呢？其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：
I/O成本:
我们都知道不管是Myisam还是Innodb存储引擎，数据都是存储到磁盘上，当我们查询数据时，会将数据从磁盘加载到内存中，这个过程就是I/O;
CPU成本:
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

了解了成本概念之后，以Innodb为例:页是磁盘和内存之间交互的基本单位，
MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成本常数。注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

单表查询的案例分析:

SELECT * FROM order_exp WHERE order_no
IN ('1','2','3') AND expire_time>'2021-03-22 18:28:28' AND
expire_time<= '2021-03-22 18:35:09' AND insert_time>expire_time
AND order_note LIKE '%7排1%' AND order_status = 0;

1.找出所有可能查询的索引
MySQL把一个查询中可能使用到的索引称之为possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：order_no
IN(‘1’,‘2’,‘3’)，这个搜索条件可以使用二级索引idx_order_no。
expire_time>‘2021-03-2218:28:28’ ANDexpire_time<=‘2021-03-2218:35:09’
，这个搜索条件可以使用二级索引idx_expire_time。
insert_time>expire_time，这个搜索条件的索引列由于没有和常数比较，
所以并不能使用到索引。order_note LIKE’%hello%'，
order_note即使有索引，但是通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引。order_status = 0由于该列上只有联合索引，
而且不符合最左前缀原则，所以不会用到索引。综上所述，上边的查询语句可能用到的索引，也就是possible keys只有idx_order_no,idx_expire_time。
在这里插入图片描述
2.全表扫描

出现了很多统计选项，但我们目前只需要两个：
Rows本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，
该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的order_exp表是使用InnoDB存储引擎的，所以虽然实际上表中有10567条记录，但是SHOW TABLE STATUS显示的Rows值只有10354条记录。
Data_length
本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，
该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，
该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：data_length = 聚簇索引的页面数量 x 每个页面的大小
我们的order_exp使用默认16KB的页面大小，而上边查询结果显示Data_length的值是1589248，
所以我们可以反向来推导出聚簇索引的页面数量：
页面数量 = 1589248/16/1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，
所以就可以计算全表扫描成本了。

现在可以看一下全表扫描成本的计算过程：
1.I/O成本97x1.0+1.1=98.1
97指的是聚簇索引占用的页面数，
1.0指的是加载一个页面的IO成本常数，
后边的1.1是一个微调值。
TIPS：MySQL在真实计算成本时会进行一些微调，
这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们大方向的分析。
2.CPU成本10354x0.2+1.0=2071.8
10354指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，
0.2指的是访问一条记录所需的CPU成本常数，
后边的1.0是一个微调值。
3.总成本：98.1+2071.8=2169.9

基于索引统计数据的成本
index dive
有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多参数）：SELECT * FROM order_exp WHERE order_no IN(‘aa1’
,‘aa2’,‘aa3’,…,‘zzz’);如果in20000个值，
这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗就很大，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都大了。MySQL考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下在MySQL5.7.21中这个系统变量的默认值：

show variables like '%dive%';默认是200

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。

show index from order_exp;查询索引基数

Cardinality索引列中不重复值的数量。后边我们会重点看这个属性的。
以order_exp表的idx_order_no索引为例，它的Rows值是10354，
它对应的Cardinality值是10225，所以我们可以计算order_no列平均单个值的重复次数就是：10354÷10225≈1.0126（条）

SELECT * FROM order_exp WHERE order_no IN(‘aa1’,‘aa2’,‘aa3’,…)
假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应1.012条记录，
所以总共需要回表的记录数就是：20000x1.0126=20252
使用统计数据来计算单点区间对应的索引记录条数比index dive的方式简单，
但是它的致命弱点就是：不精确！。使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。大家需要注意一下，在MySQL5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是indexdive的方式来计算查询成本。当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于eq_range_index_dive_limit值太小导致的。