Mysql为什么选择走全表/索引：内核查询成本计算规则

最新推荐文章于 2024-04-09 16:41:45 发布

程序java圈

最新推荐文章于 2024-04-09 16:41:45 发布

阅读量725

点赞数

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

本文链接：https://blog.csdn.net/qq_35436158/article/details/122096730

版权

mysql 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

trace工具的介绍

使用方式

‐‐ 开启trace
SET optimizer_trace="enabled=on";
-- 查询数据
select * from file_info where file_name > 'a' order by create_time;
-- 查看trace信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

常见的trace字段信息

“join_preparation”: ‐‐第一阶段：SQL准备阶段，格式化sql
“join_optimization”: ‐‐第二阶段：SQL优化阶段
“condition_processing”: ‐‐条件处理
“table_dependencies”: ‐‐表依赖详情
“rows_estimation”: ‐‐预估表的访问成本
“table_scan”: ‐‐全表扫描情况
“potential_range_indexes”: ‐‐查询可能使用的索引
“index”: “PRIMARY”, ‐‐主键索引
“index”: “idx_name_age_position”, ‐‐辅助索引
“analyzing_range_alternatives”: ‐‐分析各个索引使用成本
“rowid_ordered”: false, ‐‐使用该索引获取的记录是否按照主键排序
“index_only”: false, ‐‐是否使用覆盖索引
“rows”: 5061, ‐‐索引扫描行数
“cost”: 6074.2, ‐‐索引使用成本
“chosen”: false, ‐‐是否选择该索引
“best_access_path”: ‐‐最优访问路径
“considered_access_paths”: ‐‐最终选择的访问路径
“access_type”: “scan”, ‐‐访问类型：为scan，全表扫描
“chosen”: true, ‐‐确定选择
“join_execution”: ‐‐第三阶段：SQL执行阶段

最终选择方式

从索引，全表扫描中选择cost值最小的一种进行执行

一条sql会产生什么成本

I/O成本：我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2

单表查询的成本

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

单表查询的索引选择过程

根据搜索条件，找出所有可能使用的索引：possible keys
计算全表扫描的代价：I/O成本（扫描的页数）* 1.0 + 1.1（固定值） + CPU成本（行数） * 0.2 + 1.0（固定值）
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

全表扫描的记录行数的值从哪里来？

MySQL为每个表维护了一系列的统计信息！
使用 SHOW TABLE STATUS 语句来查看表的统计信息
得到的Rows列信息：对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。
Data_length：表示表占用的存储空间字节数。

MyISAM存储引擎的表来说，该值就是数据文件的大小。
对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
对于我刚才的结果：49152 ÷ 16 ÷ 1024 = 3页

全表扫描成本：87*0.2 + 1.1 + 3*1 + 1.0=90

计算使用不同索引执行查询的代价

范围区间数量：一个连续的范围IO成本就是1。
in的数量：in中的每一个字符都是一个区间。也就是说in的每一条记录都是一次回表。
需要回表的记录数：估算这个这个范围内需要回表的次数。mysql5.7版本涉及到的页面小于10页，精确计算；如果涉及到的页面超过10页数据，进行估算（取前10页的记录行数进行估算，有几个页面可以找他的B+书的上一级）！每行回表都是一次记录！MySQL评估回表操作的I/O成本依旧很简单粗暴，他们认为每次回表操作都相当于访问一个页面；简单说就是有多少记录，回表几次。
回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立。cpu匹配。

in参数过多的处理

查看in参数精确匹配的个数值（MySQL 5.7.21中默认200）： show variables like ‘%dive%’;
in的参数个数超过这个eq_range_index_dive_limit的值：估算一个值的重复次数 ≈ Rows（全表扫描估计的行数） ÷ Cardinality（show index from 查询出来的当前索引Cardinality的和）。然后用估算后的值乘上当前的in的格式，就是实际计算的值。