Mysql执行成本-Part1(什麽是成本、单表查询的成本)

Mysql执行成本是什麽呢？
主要就两个

1. I/O成本：我们都已经知道储存引擎将资料存在磁碟中，而运算时我们需要把资料取放到磁碟中做运算，这资料从磁碟取出到放到磁碟所耗费的时间就是I/O成本。
2. CPU成本：判断纪录是否满足搜寻条件及对结果进行排序等...所耗费的时间都是CPU成本。

成本常数
什麽是成本常数呢？
对於Mysql来说页是磁碟与记忆体的基本互动单位，设计Mysql的工程师规定读取一个页面的预设成本是1.0，读取及判断纪录条件是否满足的成本是0.2。这1.0及0.2称为成本常数。

一样用之前的例子来说明

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> 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;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引，且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是个联合索引

在真正执行一笔语法前，Mysql会找出所有可以执行这个语法的方案，并比对找出耗费时间最少的成本，这也就是最终的执行计画，决定计画後才会真正呼叫储存引擎提供的介面来执行语法。

过程一步步来看会像是下面这样：

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%' => 由於是like%开头的字串，一样无法形成合适的扫描区间
common_field = '123' => 无索引
所以
第一步：根据搜索条件找出所有可能的索引
这语法可能会使用的索引就是idx_key1二级索引或uk_key2唯一二级索引

第二步：计算全表扫描的代价
全表扫描的意思就是把聚簇索引(帮大家复习一下聚簇索引的条件就是，第一照主键值去排序页和纪录，第二叶子节点存放的是完整的使用者纪录)中的资料都依照搜寻条件一一比较，我们已经知道查询成本是I/O成本+CPU成本。
所以要先知道两个数字：

1. 表中有多少笔纪录？
2. 聚簇索引占了几页？

这些资料可以透过统计资讯来得到，如下：

mysql> show table status like 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 10146
 Avg_row_length: 156
    Data_length: 1589248
Max_data_length: 0
   Index_length: 2359296
      Data_free: 4194304
 Auto_increment: 10001
    Create_time: 2021-09-10 10:18:09
    Update_time: 2021-09-10 13:56:04
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.22 sec)

第一个数字可以看到Rows:10146，对於MyISAM来说数字是准确的，但对Innodb来说只是个估计值。
第二个数字可以看到Data_length:1589248，它表示表占用的储存空间位元数。
对於MyISAM来说，该值就是资料档案的大小，对於Innodb来说该值就是聚簇索引占用的储存空间大小，也就是说可以由以下公式来计算：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
1589248 = 聚簇索引的页面数量 x 16(预设16kb)
聚簇索引的页面数量 = 1589248 / 16 / 1024 = 97页

数子都有了我们就可以来计算成本：
I/O成本：97(页数)x1.0(载入一页的成本常数)+1.1(MySQL工程师多加入的微调值，不必特别在意) = 98.1
CPU成本：10146(纪录数)x0.2(存取一笔纪录的成本常数)+1.0(MySQL工程师多加入的微调值，不必特别在意) = 2030.2
总成本：98.1+2030.2=2128.3

第三步：计算使用不同索引执行查询的代价
在第一步我们知道了可能会使用的索引是idx_key1二级索引或uk_key2唯一二级索引。
Mysql查询最佳化工具会优先分析唯一二级索引的成本，再来才是普通二级索引的成本，所以我们也照这个顺序来分析。

先使用uk_key2唯一二级索引来计算成本：
对於使用二级索引+回表方式执行的查询，我们要注意的数字是两个：

第一个：扫描区间数量
uk_key2查询的成本如下：
I/O成本：1+95x1.0=96对应的搜索条件是key2 > 10 and key2 < 1000，扫描区间也就是(10,1000)。
而MySQL工程师设计查询最佳化工具，不论索引占用了多少页，其会粗暴的认为读取索引的扫描区间I/O成本跟读取一个页面的I/O成本是一样的。而在本例中扫描区间只有一个(10,1000)，所以I/O成本就是1。

第二个：需要回表的纪录数
查询最佳化工具需要知道二级索引在扫描区间内有几笔纪录，以此例就是uk_key2在(10,1000)有几笔纪录。
计算过程如下：
步骤1：先找到key2 > 10的第一笔纪录(称为区间最左边纪录)，在B+树内定位一笔纪录是很快的，是常数等级的，所以此过程性能消耗可忽略不计。
步骤2：再找到key2 < 1000的第一笔纪录(称为区间最右边纪录)，在B+树内定位一笔纪录是很快的，是常数等级的，所以此过程性能消耗可忽略不计。
步骤3：如果最左边与最右边纪录相隔不太远(在MySQL5.7.22中不要超过10个页面即可)，就可以精确地计算出key2 > 10 and key2 < 1000的纪录数。
大家还记得资料页的Page Header部分吗？
里面有一个参数是PAGE_N_RECS该页中使用者纪录的数量，所以如果最左边与最右边纪录相隔不太远，我们可以直接历遍这些页面，加总PAGE_N_RECS的数字即可。
那问题来了我们要怎麽知道最左边与最右边纪录有几个页面？
大家还记得前面提到的目录项纪录对应页面吧？
假设最左边纪录在页b，最右边纪录在页c，我们想知道页b与页c之间到底有几个页面？
因为每一笔目录项纪录都是对应一个页面，所以我们只要往上一层看到他们的父节点(页a)中对应的目录项纪录隔了几笔，就等於是知道有几页了呀！而在一个页面(页a)统计两笔纪录(页b与页c在页a中对应的目录项纪录)之间有几笔纪录的成本就很低了。

我们已经知道如何查询的细节，假设最终得到的纪录数是95
(1)读取这95笔纪录的cpu成本就是95(纪录数)x0.2(读取1笔纪录的成本常数)+0.01(微调值) = 19.01
(2)再来要去回表操作，MySQL工程师评估回表操作的I/O成本依然很粗暴，认为每次回表操作相当於存取一个页面，所以95笔纪录就是95x1(存取一个页面的I/O成本常数)
(3)前面回表得到完整的使用者纪录後，最後就是要检测其他搜索条件是否符合。
也就是95x0.2(检测是否符合其他条件的成本常数)=19

所以使用uk_key2查询的成本如下：
I/O成本：1(扫描区间数量)+95x1.0(二级索引纪录笔数)=96
CPU成本：95(纪录数)x0.2(读取1笔纪录的成本常数)+0.01(微调值)+95x0.2(检测是否符合其他条件的成本常数)=
38.01
总成本就是：96+38.01=134.01

再来使用idx_key1索引来计算成本：
key1 in ('a','b','c')对应的扫描区间是['a','a'],['b','b'],['c','c']
一样先了解
第一个：扫描区间数量
很明显有三个单点扫描区间，所以付出的I/O成本就是3x1.0=3.0

第二个：需要回表的纪录数
计算方法跟前面一样就不赘述
['a','a']是35
['b','b']是44
['c','c']是39
所以需要回表的总纪录数就是35+44+39=118
读取这些纪录的CPU成本就是118x0.2+0.01=23.61
再来回表操作所需的I/O成本是118x1.0=118.0
判断其他条件是否成立118x0.2=23.6

所以使用idx_key1查询的成本如下：
I/O成本：3(扫描区间数量)+118x1.0(二级索引纪录笔数)=121.0
CPU成本：118(纪录数)x0.2(读取1笔纪录的成本常数)+0.01(微调值)+118x0.2(检测是否符合其他条件的成本常数)=47.21
总成本就是：121+47.21=168.21

判断是否使用索引合并：
本例中key1和key2是用and连接起来，而对於idx_key1和uk_key2都是范围查询，也就是不会按照主键值排序，不符合索引合并的条件，因此不会使用。

比较方案
全资料：2128.3
使用uk_key2查询。：134.01
使用idx_key1查询：168.21
因此最终会使用uk_key2查询。