mysql cost成本计算初试

select * from mysql.server_cost;

select * from mysql.engine_cost;

全表扫描

IO cost = pages in table * io_block_read_cost 物理磁盘读

IO cost =pages in table * memory_block_read_cost

CPU cost = records * row_evaluate_cost

const double pages_in_mem = pages * in_mem;
const double pages_on_disk = pages - pages_in_mem;

const double cost =
buffer_block_read_cost(pages_in_mem) + io_block_read_cost(pages_on_disk);

IO成本=页面数 * 1 + 1.1
CPU成本=总行数 * 0.1

统计信息

select * from mysql.innodb_index_stats
where database_name=‘employees’ and table_name=‘employees’

show table status like ‘employees’G;

Table scan

explain format=tree SELECT * FROM employees

explain format=json select * from employees;

手工计算cost

read_cost= 929 * 1.0=929

read_cost= 929 * 0.25=232.25

eval_cost=299866 * 0.1=29986.6

REF查找

IO成本=扫描的行记录数 * 1
CPU成本=扫描的行记录数 * 0.1

统计信息

select * from mysql.innodb_index_stats
where database_name=‘employees’ and table_name=‘dept_emp’

show table status like ‘dept_emp’G;

REF

explain format=tree select * from dept_emp where dept_no=‘d003’

explain format=json select * from dept_emp where dept_no=‘d003’;

手工计算cost

read_cost= (353+737)* (33212/331962) * 1.0+ =109.05 +？

eval_cost=33212* 0.1=3321.2

对于ref查询 read_cost不知道确实少了哪部分，回表也计算了