MySQL 优化 基于查询成本的优化

MySQL知识总结

《MySQL是怎样运行的》知识总结

单表访问方法

表的连接原理

优化 基于查询成本的优化

优化需要的统计数据

优化 基于规则的优化

Explain详解

InnoDB缓冲区

事务

redo日志

undo 日志

MVCC原理

MySQL 锁

12 基于查询成本的优化

表结构

CREATE TABLE single_table (
    id           int not null auto_increment,
    key1         VARCHAR(100),
    key2         int,
    key3         varchar(100),
    key_part1    varchar(100),
    key_part2    varchar(100),
    key_part3    varchar(100),
    common_field varchar(100),
    primary key (id),
    key idx_key1 (key1),
    unique key uk_key2 (key2),
    key idx_key3 (key3),
    key idx_part (key_part1, key_part2, key_part3)
) ENGINE = INNODB
  CHARSET = utf8;

成本

​ MySQL在执行查询时可以有不同的执行方案,它会选择成本最低的那个执行方案去执行查询,成本是由两个方面组成:IO成本、CPU成本

IO成本:从磁盘到内存的加载过程损耗的时间

在使用MyISAM、InnoDB存储引擎都是将表的数据、索引存储到磁盘上,查询表中的记录时,需要将磁盘的数据或索引加载到内存,然后进行操作

CPU成本:读取记录、检测记录是否满足对应的搜索条件、对结果集进行排序等损耗的时间

​ 对于InnoDB存储引擎来说,页是磁盘和内存进行交互的基本单位,MySQL规定:

  • 读取一个页面花费的成本默认(成本常数)是1.0
  • 读取及检测一条记录是否符合查询条件的成本默认(成本常数)是0.2

单表查询成本

例子

select *
from single_table
where key1 in ('a', 'b', 'c')
  and key2  10
  and key2 < 1000
  and key3  key2
  and key_part1 like '%hello%'
  and common_field = '111'

可以使用的索引列为:

  1. key1,多个单点扫描区间
  2. key2,单个范围扫描区间

key3中与key比较,不是与常数进行比较

key_part1是使用通配符开头

基于成本的优化步骤

​ 在执行一条单表查询语句前,MySQL的优化器会找出所有可以用来执行这个查询的方案,并对比这些方案,找出成本最低的方案,成本最低的方案就是执行计划,之后再调用存储引擎的接口,真正地执行查询。这个过程是:

  1. 根据查询条件,找出可能用到的索引
  2. 计算全表扫描的成本
  3. 计算使用不同索引进行查询的成本
  4. 对比各种执行方案,找出成本最低的那个方案

找出可能用到的索引

​ 对于B+树索引来说,只要索引列和常数使用了=!=比较运算符INNOT INIS NULLIS NOT NULLBETWEENLIKE连接,就会产生一个扫描区间,以上的搜索条件都可能用到索引,MySQL把查询中可能用到的索引成为possible keys

计算全表扫描的成本

​ 对于InnoDB存储引擎来说,全表扫描就是把聚簇索引的记录依次与给定的条件进行比较,并把符合条件的记录加入到结果集中,所以需要把聚簇索引记录加载到内存,再检测记录是否符合条件,计算全表扫描时需要两个信息:

  • 聚簇索引占用的页面数
  • 表中的记录数
  • MySQL会为每个表维护一个统计信息
show table status like 'single_table';
NameEngineVersionRow_formatRowsAvg_row_lengthData_lengthMax_data_lengthIndex_lengthData_freeAuto_incrementCreate_timeUpdate_timeCheck_timeCollationChecksumCreate_optionsComment
single_tableInnoDB10Dynamic993754540672010321924194304100012022-01-08 20:37:222022-01-13 22:24:47NULLutf8_general_ciNULL
  • Row:表中记录数
    • 对于MyISAM存储引擎来说,这个数值是精确的
    • 对于InnoDB来说,这个值是估算的
  • Data_length:表占存储空间的大小
    • 对于MyISM存储引擎来说,这个数值是数据文件的大小
    • 对于InnoDB存储引擎来说,这个数值是聚簇索引占用的存储空间大小
      聚簇索引页面数量×页面大小
  • 全表扫描的总成本计算
    • 根据上面的信息可以知道,聚簇索引的页面为33个(540672÷16÷1024)
    • IO成本:33×1.0+1.1=34.1
    • CPU成本:10108*0.2+1.0=2022.6
  • 全表扫描时,是使用了聚簇索引,在查询数据时是获得最左边的叶子节点后,直接通过叶子节点组成的双向的链表将所有记录遍历,但在计算全表扫描的成本时,是直接使用聚簇索引占用的页面数量来作为IO成本的依据

计算使用索引执行查询的成本

​ 在前面的例子中,已经得知可能用到索引的列为key1、key2,MySQL需要分析单独使用索引进行查询的查询成本,最后还要分析是否出现索引合并。MySQL会先分析使用唯一索引的查询成本,再分析使用普通索引的成本。对于使用索引和回表的查询,MySQL在计算成本时,两方面的数据:扫描区间数量需要回表的记录数

  • 查询优化器认为读取索引的一个扫描区间的IO成本和读取一个页面的成本是相同的
  • 计算回表的记录数时,会查找区间的最左记录最右记录,只要这2个记录相隔不大于10个页面,就可以精确统计出记录数量,否则只沿着最左记录向右移动10个页面,计算每个页面记录数的平均值,然后再乘最左、右相隔的页面数量(相隔的页面数量可以根据目录项中确定)

例子中,对于key2的查询条件的扫描区间是(10, 1000),记录数为398条

  1. 使用索引查询数据成本,IO成本为1×1.0=1.0,CPU成本为:398×0.2+0.1=79.7,
  2. 回表的成本,IO成本为389×1.0=389.0,CPU成本为389×0.2=79.6

key1的查询条件的扫描区间是(‘a’, ‘b’, ‘c’),记录数为416条

  1. 索引查询成本,IO成本为3×1.0=3.0,CPU成本为416×0.2+0.01=83.21
  2. 回表成本,IO成本为416×1.0=416.0,CPU成本为416×0.2=83.2

对比执行方案的查询成本

​ 对比3中执行方案的成本,选择成本最低的执行方案,这里选择key列的索引进行查询。

基于索引统计数据进行成本计算

​ 使用IN语句 容易产生多个单点扫描区间,如果使用的索引不是唯一索引,那么我们不能确定单点扫描区间内的记录有多少条,我们需要获取最左、右的记录之间的记录数量才能进行计算,这种直接访问索引对应的B+树来计算某个扫描区间上的记录数量的方式是index dive

​ MySQL提供了系统变量对使用index dive方法进行了限制,默认情况下,当单点扫描的个数大于200时,就不会使用index dive方法记录扫描区间内的记录数量,而是使用索引统计数据进行计算。

查看使用index dive条件限制

# 查看使用index dive条件限制
show variables like '%index_dive%';

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+

索引统计数据

# 查看索引统计数据
show index from single_table;
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_comment
single_table0PRIMARY1idA9937NULLNULLBTREE
single_table0uk_key21key2A9937NULLNULLYESBTREE
single_table1idx_key11key1A4183NULLNULLYESBTREE
single_table1idx_key31key3A4209NULLNULLYESBTREE
single_table1idx_part1key_part1A4188NULLNULLYESBTREE
single_table1idx_part2key_part2A8492NULLNULLYESBTREE
single_table1idx_part3key_part3A9905NULLNULLYESBTREE

Cardinality:基数,某列不重复的值,对于InnDB来说,它是一个估算值。

# 表统计信息
show table status like 'single_table';
NameEngineVersionRow_formatRowsAvg_row_lengthData_lengthMax_data_lengthIndex_lengthData_freeAuto_incrementCreate_timeUpdate_timeCheck_timeCollationChecksumCreate_optionsComment
single_tableInnoDB10Dynamic993754540672010321924194304100012022-01-08 20:37:222022-01-13 22:24:47NULLutf8_general_ciNULL

​ 上述说到,当单点扫描区间的个数大于限制时,就不使用index dive的方式来计算扫描区间的记录数量,而是使用索引的统计数据,使用统计数据包括:

  1. 表统计数据的Rows
  2. 索引统计数据的Cardinality

Rows为9937,key1列的Cardinality为4183,9937÷4189≈2,则key1的单点扫描区间中大约为2条记录,如果IN语句中有10000个单点扫描区间,那就要进行回表操作20000次。

使用统计数据进行计算成本可能与时间成本相差很大

连接查询的成本

条件过滤

​ MySQL中连接查询采用的是嵌套循环连接算法,驱动表会被访问一次,而被驱动表会被访问多次,显然,查询驱动表得到的记录数(扇出值)越少,对被驱动表的查询也就越少,查询驱动表时,可能用到全表扫描、索引。

使用全表扫描执行单表查询时,计算驱动表扇出值时需要猜测满足全部查询条件的记录数,这个猜测的过程叫做条件过滤,猜测的过程可能用到统计数据或索引。

两表连接成本

​ 连接查询的总成本:单次访问驱动表成本+驱动表扇出值×访问被驱动表的成本。

对于内连接来说,驱动表、被驱动表的位置可以互换。连接查询的成本可能因为驱动表、被驱动表的位置而不同,所以需要考虑表的连接顺序。

例子

select *
from single_table s1
         join single_table s2 on s1.key1 = s2.common_field
where s1.key2  10
  and s1.key2 < 1000
  and s2.key2  1000
  and s2.key2 < 2000;

​ 使用s1作为驱动表时,很明显将使用key2对应的索引执行单表查询,然后对被驱动表进行单表查询,查询条件是s2.common_field = 常数s2.key2 1000 and s2.key2 < 2000,对于s2的查询,将使用到key2的索引。

此时的查询成本为:key2索引访问s1的成本+s1的扇出值×使用key2索引访问s2的成本。

​ 如果使用s2作为驱动表,将使用key2的索引进行单表查询,在对s1表进行查询时,搜索条件是
s1.key1 = 常数s1.key2 10 and s1.key2 < 1000,可以用到的索引有key1使用ref的访问方法、key2使用range访问方法。ref方法方法的成本衡量方式是使用统计数据进行计算的,一般情况下ref方法方法的成本比range方法方法的成本低。

​ 优化器会从两种连接顺序中选出成本最低的那个,真正去执行查询,可以看出连接查询成本占比较大的部分是扇出值×访问被驱动表的成本,所有优化重点是:

  1. 减少驱动表的扇出
  2. 访问被驱动表的成本要低
  3. 尽量在被驱动表连接列上建立索引,这样可以使用到ref的访问方法

调节成本常数

​ 除了上述的两个成本常数外,还有其他成本常数

# 存储成本常数的表
show tables from mysql like '%cost%';

+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+

server_cost表

select *from mysql.server_cost;
cost_namecost_valuelast_updatecomment(描述)
disk_temptable_create_costNULL (40.0)2021-09-07 18:49:49NULL
disk_temptable_row_costNULL (1.0)2021-09-07 18:49:49NULL向磁盘临时表写入或读取一条记录的成本
key_compare_costNULL (0.1)2021-09-07 18:49:49NULL两条记录进行比较操作的成本,多用在排序操作中,filesort
memory_temptable_create_costNULL (2.0)2021-09-07 18:49:49NULL
memory_temptable_row_costNULL (0.2)2021-09-07 18:49:49NULL
row_evaluate_costNULL (0.2)2021-09-07 18:49:49NULL读取并检测一条记录是否符合查询条件的成本,增大这个值,可能会让优化器倾向于使用索引进行而不是全表扫描

​ 成本常数为NULL,则为默认值

​ 执行包含distinctgroup byunion的子句及某些特殊条件的排序,MySQL可能会创建临时表来辅助完成查询,将相应结果记录插入到临时表,如使用distinct子句进行去重操作。

​ 创建临时表、对临时表进行操作的成本还是很高的。

engine_cost表

select *from mysql.engine_cost;
engine_namedevice_typecost_namecost_valuelast_updatecomment
default0io_block_read_costNULL (1.0)2021-09-07 18:49:50NULL
default0memory_block_read_costNULL (1.0)2021-09-07 18:49:50NULL
  • default:这个成本常数适用于所有存储引擎
  • io_block_read_cost:从磁盘读取一个块的成本,对于InnoDB存储引擎来说,一个页就是一个块,对于MyISAM存储引擎来说,默认4096字节为一个块
  • memory_block_read_cost :从内存读取一个块的成本
  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:创作都市 设计师:CSDN官方博客 返回首页
评论

打赏作者

011eH

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值