mysql优化必会-什么是成本

最新推荐文章于 2024-07-23 09:10:52 发布

热心的大肚皮

最新推荐文章于 2024-07-23 09:10:52 发布

阅读量797

点赞数

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

本文链接：https://blog.csdn.net/qq_36196218/article/details/122842777

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL查询执行时的成本计算，包括I/O成本和CPU成本，并通过实例解析了如何根据不同的索引选择执行计划。在示例中，解释了全表扫描与使用二级索引的成本计算方法，最终得出使用特定索引可以降低查询成本的结论。

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

大家好，我是热心的大肚皮，皮哥。

什么是成本

mysql中在执行查询时有多种方案，它会选着代价最低的方案去执行查询，一条查询语句在mysql的执行成本分为两块。
I/O成本：把数据或者索引加载到内存中，这个过程损耗的时间是I/O成本。规定读取一个页面的花费成本是1.0。
CPU成本：读取记录以及检测记录是否满足对应的搜索条件，对结果进行排序等操作所耗费的时间称为CPU成本。读取以及检测记录是否符合的成本默认是0.2。

如何计算成本

首先我们先搞个临时表，假设有1W条记录。

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_key_part(key_part1, key_part2, key_part3)
  )ENGINE = InnoDB CHARSET = utf8;

首先执行器会找出所有可以执行的方案，进行对比，找出最低的方案也就是所谓的执行计划，然后调用存储引擎的接口。过程如下。

1、根据条件，找出所有可能使用的索引。

2、计算全表扫描的代价。

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

4、对比代价，找出成本最小的方案。

举个例子。

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 = '123';

找出可能使用的索引

key1 in (‘a’, ‘b’, ‘c’) ：可以使用二级索引idx_key1。
key2 > 10 and key2 < 1000：可以使用二级索引uk_key2。
key3 > key2 ：没有与常数比较，无法使用索引。
key_part1 like ‘%hello%’：key_part1有索引但是hello前有%，无法使用索引。
common_field = ‘123’：没创建索引。
计算全表扫描的代价

虽然很多信息，咱们目前只关心两个。

Rows：表中的记录数，对于MyISAM来说，这个数是准的；对于InnoDB来说，这个是个估计值，但是计算成本时，以这个值为准。
Data_length：表占用的存储空间字节数。对于MyISAM来说，这个值就是文件的大小；对于InnoDB来说，这个就是聚簇索引占用的空间大小。Data_length = 聚簇索引页面数量*每个页面的大小。页面数量 = 1589248/16 /1024 = 97。
计算后的成本。
I/O成本：97*1.0 + 1.1 = 98.1，97是页面数，1.0是加载的成本常数，1.1是一个微调值，不用管。
CPU成本：9693*0.2 +1.0=1939.6，9693是行数，0.2是访问的成本常数，1.0是微调值
总成本：98.1+1939.6 = 2037.7

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

1、使用uk_key2执行的查询成本

uk_key2对应的条件是 key2>10 and key2<1000，也就是扫描的索引区间是（10，1000）。对于这种二级索引+回表的方式查询，成本计算依赖于扫描区间与需要回表的记录数。

扫描区间数量
作者规定，无论扫描区间的二级索引占用多少页面，都会被认为读取索引的一个扫描区间的I/O与读取一个页面的成本相同，所以成本是1*1.0=1.0。
需要回表的记录数。

步骤1. 先找到边界记录，也就是key2>10 与key2<1000 的首尾2条记
录，这个性能损耗是常数级别的，可以忽略。
步骤2. 如果两条记录页面相差小于10时，则统计精确的记录数，否则根
据前10个页面记录数算出平均记录数，在乘上页面数，当作记录数。至于怎么统计的精确记录数可以根据每个页的PAGE_N_RECS来计算，至于页面数，索引中每条记录代表一个页面，看两个索引的之间有多少条记录就可以知道有多少页面。假设有在区间范围内有95条记录，则成本=950.2+0.01 = 19.01，其中0.01是微调值。
步骤3. 根据扫描到的记录主键到聚簇索引中回表查询，规定每次回表都等于访问一个页面，所以成本是I/O成本 = 951.0=95。
步骤4. 根据判断其他条件是否符合，CPU成本= 95*0.2=19.0。
所以这种方式的成本是96.0+38.01=134.01。

I/O成本：1.0+95*1.0 = 96.0（扫描区间的数量+预估的二级索引记录数）
CPU成本：950.2 +0.01+950.2 = 38.01（读取索引的成本+读取并检测回表后的成本）。

2.使用idx_key1执行的查询成本

idx_key1对应的条件是 key1 in (‘a’, ‘b’, ‘c’)。

扫描区间数量
3*1.0=3.0。
需要回表的记录数

步骤1. 其中a的记录35，b的记录44，c的记录39，成本=（35+44+39）*0.2+0.01 = 23.61。
步骤2. 根据扫描到的记录主键到聚簇索引中回表查询，规定每次回表都等于访问一个页面，所以成本是I/O成本 = （35+44+39）1.0=118.0。
步骤3. 根据判断其他条件是否符合，CPU成本= 1180.2=23.6。
所以这种方式的成本是121.0+47.21=168.21。