焱老师带你学习MYSQL系列第四篇 (MYSQL优化器详解)

weixin_44669461

已于 2022-07-28 14:17:36 修改

阅读量199

点赞数

分类专栏： MYSQL 文章标签： mysql 学习数据库

于 2022-07-27 23:41:06 首次发布

本文链接：https://blog.csdn.net/weixin_44669461/article/details/126023738

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文探讨了MYSQL中为何在建立索引时查询不走索引的问题，解释了优化器的选择过程和成本估算，提到了调整disk_temptable_row_cost和row_evaluate_cost的方法来促使更优的索引使用。同时，介绍了BufferPool在实际性能中的作用。

摘要由CSDN通过智能技术生成

前记

小明提问 : 为什么有些情况MYSQL 我明明正确建立索引但查看explain执行计划时发现没有走索引走的是all 全表扫描 .

1 . 优化器原理简介

1 . MYSQL 首先会分析出所有可能用到的索引的情况以及全表查询情况大致估算出一个时间(!!! 注意这个是估算实际执行可能存在很大偏差) . 选择时间最少的 .
举例子 : table_ a表针对 index_b 字段建立普通索引
以及针对index_c 字段建立普通索引 .
SQL 语句 : select * from table_a from index_a = 1 and index_b = 1
大家分析一下 : 可能会走哪些索引
case : 1 . 走 index_a 索引
2 . 走 index_b 索引
3 . 全表查询
MYSQL 内部会对三种情况进行模拟计算使用计算结果最小的记录 .

2 . 优化器是如何计算的

实际上 MYSQL 内部会对各种操作设置一个默认的时间变量值 . 操作单位时间如下 :
disk_temptable_create_cost 40.0 (创建基于磁盘的临时表成本)
disk_temptable_row_cost 1.0 (向基于磁盘的临时表写入/读取一条记录/以及回表的成本)
key_cimpare_cost 0.1 (两条记录进行比较操作成本 (多用于排序操作) 如果提高这个值 , 更倾向于使用索引完成排序)
memory_temptable_create_cost 2.0 (基于内存创建的临时表的成本)
memory_temptable_row_cost 0.2 (向基于内存的临时表写入/读取一条成本.)
row_evaluate_cost 0.2 (读取并检测一条记录是否符合搜索条件的成本)
!!! 注意此值仅仅是用来估算并不是实际值

假设 a表有 1w条数据 . index_a = 1 有 2000条(满足条件) , index_b > 1 (有2100条) (注意 !! 针对小数据量我们他是这个条件是准确值但对于大数据量他仅仅是估算值如何估算出是通过比如 index_a 有不重复值 1000个该表有1000w数据则 = 1的近似为 1000w / 1000 = 1w 但实际上可能天差地别)

那么case 1
IO成本为 1 (二级索引叶子节点读取数据) + 2000 * 1 (2000条记录回表所用时间) = 2001
CPU成本为 2000 * 0.2 (2000条记录判断是否是 index_b > 1) = 400
总成本为 : 2001 + 400 = 2401
那么case 2 同理为 1 + 2100 * 1 + 2100 * 0.2 = 2521

那么case 3
IO成本 : 整个数据表的字节数为 1589248 (改记录MYSQL有存储存储一个表的所占字节数) / 1024 /16 = 97页 + 1.1 (系统常数)
CPU成本 : 1w * 0.2 = 2000
总成本为 97 + 2000 = 2098.1

所以 2097 < 2401 < 2521 走全表查

3 . 优化器的糟糕

试问一下上面的三种case 真实执行会是全表查最快吗 ?
我觉得不一定是 . 甚至可以说一定不是 .
为什么 1 . MYSQL 内置了 bufferPool 在很短的时间之内哪怕针对case 1 数据很离散 (分布在整整97个页中) 大部分情况也是从内存中读取该页数据而非从磁盘中 .
那么真实情况可能更多的是 97 (所有聚簇数据页加载到内存中读取) + 2000 * 0.2 (2000条数据从内存中读取) + 2000 * 0.2 (2000条数据比对是否满足条件) = 900
大家有没有发现如果走索引实际情况可能是比走全部查性能提升50 % .
此刻如果我是个小白看到线上走全表查可能会慌 …
但不用慌我们已经发现问题那么他一定有解决方案 .
如果我们希望如果能走索引尽可能走索引的解法是 :
1 . 减少disk_temptable_row_cost 的值 1. 0是内置参数是可修改的 .
2 . 增加row_evaluate_cost的值都是可解的 .

如果要用上述方法理应增加BufferPool大小多缓存一些数据 .