徐州设计公司网站的公司,英文外贸网站建设,wordpress 标签链接地址,江苏10大网站建设公司MySql系列整体栏目 内容链接地址【一】深入理解mysql索引本质https://blog.csdn.net/zhenghuishengq/article/details/121027025【二】深入理解mysql索引优化以及explain关键字https://blog.csdn.net/zhenghuishengq/article/details/124552080【三】深入理解mysql的索引分类覆盖索引(失效)回表MRRhttps://blog.csdn.net/zhenghuishengq/article/details/128273593【四】深入理解mysql事务本质https://blog.csdn.net/zhenghuishengq/article/details/127753772【五】深入理解mvcc机制https://blog.csdn.net/zhenghuishengq/article/details/127889365【六】深入理解mysql的内核查询成本计算https://blog.csdn.net/zhenghuishengq/article/details/128820477【七】深入理解mysql性能优化以及解决慢查询问题https://blog.csdn.net/zhenghuishengq/article/details/128854433【八】深入理解innodb和buffer pool底层结构和原理https://blog.csdn.net/zhenghuishengq/article/details/128993871【九】深入理解mysql执行的底层机制https://blog.csdn.net/zhenghuishengq/article/details/128100377【十】深入理解mysql集群的高可用机制https://blog.csdn.net/zhenghuishengq/article/details/126239652深入理解mysql的内核查询成本计算一mysql的内核查询成本1mysql单表查询成本计算1.1建表1.2Optimizer Trace1.3单表成本优化思路1.3.1找出所有可能使用到的索引1.3.2计算全表扫描的代价1.3.3分别计算其他索引的查询代价1.3.4对比全部扫描的代价和其他单个索引的代价2in查询内核成本分析3连接查询成本计算一mysql的内核查询成本
1mysql单表查询成本计算
在mysql中无论是innodb存储引擎还是MyIsam存储引擎主要是由两种时间成本组成分别是io成本 和 CPU成本 。io成本就是数据从磁盘加载到内存时需要花费的时间成本cpu成本就是需要去判定里面的where语句或者其他的范围查询in查询等是否符合要求所需要的时间成本。
在mysql中IO成本默认需要花费1个单位的成本CPU成本默认需要花费0.2个单位成本(不管是否存在需要过滤的条件)。因此在计算一个成本时其基本公式如下然后mysql内部会考虑一些微调值这里暂不考虑。
T(i/o) : 总页数 * 1.0
T(cpu) : 总条数 * 0.2
T(总) T(i/o) T(cpu)这些都是默认值mysql也可以对这些值进行调整。这里的1个单位指的是innodb存储引擎读取一页数据所花费的时间
1.1建表
接下来新建一张订单表其各个字段如下
CREATE TABLE order_exp (id bigint(22) NOT NULL AUTO_INCREMENT COMMENT 订单的主键,order_no varchar(50) NOT NULL COMMENT 订单的编号,order_note varchar(100) NOT NULL COMMENT 订单的说明,insert_time datetime(0) NOT NULL COMMENT 插入订单的时间,expire_duration bigint(22) NOT NULL COMMENT 订单的过期时长单位秒,expire_time datetime(0) NOT NULL COMMENT 订单的过期时间,order_status smallint(6) NOT NULL DEFAULT 0 COMMENT 订单的状态0未支付1已支付-1已过期关闭,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 10819 CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Dynamic;需要里面的表结构以及数据的话可以直接在百度网盘下载即可提取码为1234https://pan.baidu.com/s/12Py6QwzlZ7CXGuwNKp_bsA
接下来分析下面这句简单的sql语句
SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S, DD00_10S) 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;因此通过分析可知可以给order_no字段添加一个索引expire_time字段添加一个索引这两个字段都缩小了范围符合之前所说的一星索引而这个like由于%在前面根据B树的原则like添加索引的话会失效因此order_note字段不添加索引order_status这个字段只有01和 -1离散性太低肯定不走索引因此也不添加索引这个insert_time由于和这个expire_time都是变量而索引是一个变量跟常量进行比较的因此这里肯定也不走索引因此不在这个字段上加索引。
alter table order_exp add index idx_order_no (order_no);
alter table idx_expire_time add index idx_expire_time (expire_time);可以通过以下命令来查看当前表中存在的所有索引
show keys from order_exp;1.2Optimizer Trace
在获取底层如何是优化这个sql语句之前需要先了解一个工具就是这个 Optimizer Trace 。可以通过开启这个 Optimizer Trace 指令来查看底层优化器的执行过程可以查看mysql是如何选择的最佳的优化路线的。Trace工具可以从细节上分析MySQL是如何选择索引。
其开启的命令如下
SET optimizer_traceenabledon;开启完之后就可以输入需要查询的sql语句再输入具体需要查询的sql语句如下
SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S, DD00_10S) 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;可以再通过输入以下的命令就可以看到底层分析的过程以及结果了。
SELECT * FROM information_schema.OPTIMIZER_TRACE\G最后就可以出现一下的一大堆东西的界面有了这个结果之后就可以通过上面的结果来验证innodb底层的这个成本优化思路了。 1.3单表成本优化思路
基于成本的优化步骤主要由四个步骤组成。一是根据搜索条件找出所有可能使用到的索引二是先计算全表扫描的代价三是使用不同索引执行查询的代价四是对比各种执行的方案代价找出成本最低的那个
1.3.1找出所有可能使用到的索引
一条sql语句中找出全部可能使用到的索引主要是使用关键字 explain 在查询的sql语句之前加上explain这个关键字即可分析出可能会使用哪些索引
explain SELECT * FROM order_exp WHERE order_no IN (DD00_6S, DD00_9S, DD00_10S) 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;其具体分析结果如下type类型是range的范围查询然后可能使用到的key就是建立的那两个索引。 1.3.2计算全表扫描的代价
接下来就是先计算这个全表扫描的代价。全表扫描就是直接扫描聚簇索引的叶子结点由于所有的数据都在聚簇索引的叶子结点上因此就会通过这个遍历上面的每一个结点然后对每一个结点进行匹配看是否满足这个全部的要求
接下来就通过这个Optimizer Trace 工具获取到的里面的数据分析(上面已打开)然后找到全表扫描所花费的这个单位。如下全表扫描大概10354行并且通过这个Trace工具得知的大概需要花费2169.9个页单位。 由于这里计算的是全表扫描那么就需要知道总行数以及总页数因此可以使用下面这个命令
SHOW TABLE STATUS LIKE order_exp\G其结果如上图从图中可知 data_length 的长度为 1589248因此可以得知总页数为97这个长度就是数据的总字节数。
1589248 ÷ 16 ÷ 1024 97总条数为Rows 10343因此可以利用这个成本计算的公式这里会涉及到一个微调数这里的微调数是mysql底层的硬编码因此是必加的。因此和上面的这个全表扫描的成本对上了。
T(I/O) : 97 * 1.0 1.1(微调数) 98.1
T(CPU) : 10354 * 0.2 1.0(微调数) 2071.8
T(总) T(I/O) T(CPU) 2169.91.3.3分别计算其他索引的查询代价
在使用完主键索引之后那么就会计算二级索引的代价唯一索引会优先普通索引。接下来可以先查看这个 order_note 列所对应的索引这个索引是一个普通索引。
order_note 索引
由于这个 order_note 字段总涉及到三个范围那么在二级索引查询时需要查询三次那么只需要三次IO由于在使用该字段时查询的结果只有58行那么需要进行58次的cpu的判断最后涉及到回表在聚簇索引中也要花费一定的时间则整体成本代价如下
T(I/O二级索引) : 3 * 1.0 3
T(I/O回表) : 58 * 1.0 58
T(CPU) : 58 * 0.2 0.01(微调数) 11.61
T(CPU回表) : 58 * 0.2 11.6
T(总) T(I/O二级索引) T(I/O回表) T(CPU) T(CPU回表) 84.21如下图可知这个cost花费的成本是84.21那是因为在mysql内部将这个回表时的CPU所花费的这个时间成本给省去了如果84.21 - 11.6 那么刚好就是这个72.61这个值 expire_time索引
再计算这个expire_time这列索引的成本代价这里由于就一个范围时间因此只需要一次IO在单独使用这个字段作为索引时发现只涉及到39行数据因此这个需要进行39次cpu的判断同时在这个二级索引结束之后需要回表到一级索引里面通过一级索引去找到对应的值因此一级索引也需要一定的IO和CPU由于二级索引找到的值有39行数据那么需要回表39次其IO和CPU成本如下
T(I/O二级索引) : 1 * 1.0 1
T(I/O回表) : 39 * 1 39
T(CPU) : 39 * 0.2 0.01(微调数) 7.81
T(CPU回表) : 39 * 0.2 7.8
T(总) T(I/O二级索引) T(I/O回表) T(CPU) T(CPU回表) 55.61[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-82krlCGh-1675155949966)(img/1675128183273.png)]
系统显示是47.81而实际计算是 55.61 那是由于在进行这个比较计算的时候mysql内部会扣掉这个CPU回表的时间即55.61 - 7.8 47.81 那么就对上了。
1.3.4对比全部扫描的代价和其他单个索引的代价
因此在极端完上面这几个成本之后就可以进行一个最终的比较了通过这个cost成本比较得知这个expire_time索引花费的时间最小因此最终选择的是使用这个 expire_time 字段作为最终的选择的索引。
2in查询内核成本分析
在mysql中其内部对in这个关键字也做了相应的优化
select * from user where user_no in (123,xxx,xxx,...);在使用这个in查询时如果出现很多的这个单点区间的时候那么就会触发这个 index dive就是会有一个最大值去控制可以发现这个默认的最大值为200如果括号中的值是小于200的话就会进行一个精确的计算如果值大于200的话就会进行一个估算。
show variables like %dive%3连接查询成本计算
在使用这个连接查询时需要遵循一个原则就是小表驱动大表。其主要通过这个嵌套循环连接算法实现这个连接查询即驱动表查询一次被驱动表则需要查询多次。 而多次查询被驱动表的成本主要是取决于对驱动表查询的结果集中有多少条记录即驱动表看的不是表中有多少数据而是看查出来的结果集中的数据条数谁的结果集的数据小则用哪张表作为结果集。
如果是使用这个左连接右连接mysql内部很少做优化的东西如果是内连接那么mysql内部会做一个计算去统计结果集的数据然后区分谁做这个驱动表。
其成本计算的方式就是表1的成本 表2的扇出 x 表1的成本 。因此这个优化手段就是两个部分分别是 尽量减少驱动表的扇出对被驱动表的访问成本尽量低。 并且在这个《阿里最新Java编程规范泰山版》的规定当中 规定其超过三个表禁止join需要join的字段其数据类型保持绝对的一致在多表关联查询时保证被关联的字段走索引。
