mysql优化必会-什么是成本

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

什么是成本

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条记录，则成本=95*0.2+0.01 = 19.01，其中0.01是微调值。

步骤3. 根据扫描到的记录主键到聚簇索引中回表查询，规定每次回表都等于访问一个页面，所以成本是I/O成本 = 95*1.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成本：95*0.2 +0.01+95*0.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。

步骤4.根据判断其他条件是否符合，CPU成本= 118*0.2=23.6。

所以这种方式的成本是121.0+47.21=168.21。

• I/O成本：3.0+118*1.0 = 121.0（扫描区间的数量+预估的二级索引记录数）
• CPU成本：118*0.2 +0.01+118*0.2 = 47.21（读取索引的成本+读取并检测回表后的成本）。

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

对应的成本如下。

• 全表扫描：2037.7。
• 使用uk_key2的成本：134.01。
• 使用idx_key1的成本：168.21。

很显然使用uk_key2成本更低，所以当然选择uk_key2。