大家好,我是热心的大肚皮,皮哥。
什么是成本
mysql中在执行查询时有多种方案,它会选着代价最低的方案去执行查询,一条查询语句在mysql的执行成本分为两块。
如何计算成本
首先我们先搞个临时表,假设有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;
首先执行器会找出所有可以执行的方案,进行对比,找出最低的方案也就是所谓的执行计划,然后调用存储引擎的接口。过程如下。
举个例子。
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';
找出可能使用的索引
计算全表扫描的代价
虽然很多信息,咱们目前只关心两个。
计算后的成本。
计算使用不同索引执行查询的代价
uk_key2对应的条件是 key2>10 and key2<1000,也就是扫描的索引区间是(10,1000)。对于这种二级索引+回表的方式查询,成本计算依赖于扫描区间与需要回表的记录数。
步骤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。
2.使用idx_key1执行的查询成本
idx_key1对应的条件是 key1 in ('a', 'b', 'c')。
步骤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。
计算使用不同索引执行查询的代价
对应的成本如下。
很显然使用uk_key2成本更低,所以当然选择uk_key2。