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不知道确实少了哪部分，回表也计算了
