mysql+代价模型_mysql物理优化器代价模型分析

1.引言

mysql的sql server在根据where condition检索数据的时候，一般会有多种数据检索的方法，其会根据各种数据检索方法代价的大小，选择代价最小的那个数据检索方法。

比如说这个语句，where col1=x and col2=y and col3 >z ,同时存在inx_col1，inx_col2，inx_col3，inx_col1_col2_col3这四个索引，sql server要解决的问题有1)选择哪个索引、2)是索引range扫描还是ref扫描、3)table scan的方式是否可行。

mysql会根据以下几种数据检索策略选择代价最小的策略来从数据表中获取数据，1)各个索引的range scan代价2)各个索引的ref scan代价3)table scan的代价。如何计算这些代价，是本文详细说明的重点。

总代价cost = cpu cost + io cost。

2 .代价因子

mysql的代价因子在内存中有一份副本，由Server_cost_constants和SE_cost_constants两个类组成。这两个类的具体数据成员如下。

Mysql Server代价因子

Server_cost_constants {

m_row_evaluate_cost //行记录条件谓词评估代价m_key_compare_cost //键值比较代价m_memory_temptable_create_cost //内存临时表创建代价m_memory_temptable_row_cost //内存临时表的行代价m_disk_temptable_create_cost //磁盘临时表创建代价m_disk_temptable_row_cost

}

存储引擎代价因子

SE_cost_constants{

m_memory_block_read_cost //从buffer pool中读取一个页面的代价m_io_block_read_cost //从文件系统中读取一个页面的代价，buffer miss的场景m_memory_block_read_cost_default

m_io_block_read_cost_default

}

mysql的代价因子在系统的持久化系统表中也有一份副本，对应mysql.server_cost和mysql.engine_cost两个表，这两个表中的字段与 内存中的类字段相同。DBA可以根据实际的硬件情况测试，测试出最适合的代价因子，然后update系统表中对应的字段。再然后执行flush OPTIMIZER_COSTS命令，将修改反应到内存中数据，这样新连接上来的mysql session会读取到内存中数据，然后以新的代价因子计算代价数。

代价因子如何根据实际的硬件环境与负载压力自适应地调整，是一个重要的研究课题。

3 .统计信息

sql server需要的统计信息是由存储引擎innodb提供的，调用innodb提供的api可以获取这些统计信息，本文的后半部分会罗列这些api。innodb的统计信息根据需要可以持久化到系统表中。mysql.innodb_table_stats和mysql.innodb_index_stats存储了表的统计信息和索引的统计信息。

mysql.innodb_table_stats表中字段说明

database_name库名

table_name表名

n_rows表中的数据行数

clustered_index_size聚集索引的页面数

sum_of_other_index_sizes