最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

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

XAMPP相关 admin 467浏览 0评论

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 语句来查看表的统计信息

dra0009

•得到的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的格式,就是实际计算的值。

连接查询的成本

•连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

•扇出:对驱动表进行查询后得到的记录条数

•计算方式的细节与单表相同!

 

来源:程序JAVA圈

转载请注明:XAMPP中文组官网 » Mysql为什么选择走全表/索引:内核查询成本计算规则

您必须 登录 才能发表评论!