MySQL8.0新特性：直方图

MySQL8.0MySQL8.0新特性直方图查询优化器负责将SQL查询转换为尽可能高效的执行计划但随着数据环境不断变化查询优化器可能无法找到最佳的执行计划导致SQL效率低下。造成这种情况的原因是优化器对查询的数据了解的不够充足例如每个表有多少行数据每列中有多少不同的值每列的数据分布情况。因此MySQL8.0.3推出了直方图(histogram)功能直方图是列的数据分布的近似值其向优化器提供更多的统计信息。比如字段NULL的个数每个不同值的百分比最大/最小值等。MySQL的直方图分为等宽直方图和等高直方图MySQL会自动分配使用哪种类型的直方图无法干预等宽直方图每个bucket保存一个值以及这个值的累计频率等高直方图每个bucket保存不同值的个数上下限以及累计频率直方图同时也存在一定的限制条件不支持几何类型以及json类型的列不支持加密表和临时表无法为单列唯一索引的字段生成直方图创建和删除直方图创建语法ANALYZETABLEtbl_nameUPDATEHISTOGRAMONcol_name[,col_name]WITHN BUCKETS;创建直方图时能够同时为多个列创建直方图但必须指定bucket数量范围在1-1024之间默认100。对于bucket数量应该综合考虑其有多少不同值、数据的倾斜度、精度等建议从较低的值开始不符合再依次增加。删除语法ANALYZETABLEtbl_nameDROPHISTOGRAMONcol_name[,col_name];直方图信息MySQL通过字典表column_statistics来保存直方图的定义每行记录对应一个字段的直方图以JSON格式保存。rootemployees13:49:selectjson_pretty(histogram)frominformation_schema.column_statisticswheretable_nameemployeesandcolumn_namefirst_name;;{buckets:[[base64:type254:QWFtZXI,base64:type254:QWRlbA,0.010176045588684237,13],data-type:string,null-values:0.0,collation-id:255,last-updated:2020-09-09 05:47:32.548874,sampling-rate:0.163495700259278,histogram-type:equi-height,number-of-buckets-specified:100}MySQL为employees的first_name字段分配了等高直方图默认为100个bucket。当生成直方图时MySQL会将所有数据都加载到内存中并在内存中执行所有工作。如果在大表上生成直方图可能会将几百M的数据读取到内存中的风险因此可以通过参数font stylecolor:rgb(255, 53, 2);background-color:rgb(248, 245, 236);hitogram_generation_max_mem_size/font来控制生成直方图最大允许的内存量当指定内存满足不了所有数据集时就会采用采样的方式。rootemployees14:12:selecthistogram-$.sampling-ratefrominformation_schema.column_statisticswheretable_nameemployeesandcolumn_namefirst_name;;---------------------------------|histogram-$.sampling-rate|---------------------------------|0.163495700259278|---------------------------------从MySQL8.0.19开始存储引擎自身提供了存储在表中数据的采样实现存储引擎不支持时MySQL使用默认采样需要全表扫描这样对于大表来说成本太高采样实现避免了全表扫描提高采样性能。通过INNODB_METRICS计数器可以监视数据页的采样情况这需要提前开启计数器rootemployees14:26:SELECTNAME,COUNTFROMINFORMATION_SCHEMA.INNODB_METRICSWHERENAMELIKEsampled%\G***************************1.row***************************NAME: sampled_pages_read COUNT:430***************************2.row***************************NAME: sampled_pages_skipped COUNT:4562rowsinset(0.04sec)采样率的计算公式为font stylecolor:rgb(255, 53, 2);background-color:rgb(248, 245, 236);sampled_page_read/(sampled_pages_read sampled_pages_skipped)/font优化案例复制一张表出来源表不添加直方图新表添加直方图rootemployees14:32:createtableemployees_likelikeemployees;Query OK,0rowsaffected(0.03sec)rootemployees14:33:insertintoemployees_likeselect*fromemployees;Query OK,300024rowsaffected(3.59sec)Records:300024Duplicates:0Warnings:0rootemployees14:33:ANALYZETABLEemployees_likeupdateHISTOGRAMonbirth_date,first_name;------------------------------------------------------------------------------------------------------|Table|Op|Msg_type|Msg_text|------------------------------------------------------------------------------------------------------|employees.employees_like|histogram|status|Histogramstatisticscreatedforcolumnbirth_date.||employees.employees_like|histogram|status|Histogramstatisticscreatedforcolumnfirst_name.|------------------------------------------------------------------------------------------------------分别在两张表上查看SQL的执行计划rootemployees14:43:explainformatjsonselectcount(*)fromemployeeswhere(birth_datebetween1953-05-01and1954-05-01)andfirst_namelikeA%;{query_block: {select_id:1,cost_info: {query_cost:30214.45},table: {table_name:employees,access_type:ALL,rows_examined_per_scan:299822,rows_produced_per_join:3700,filtered:1.23,cost_info: {read_cost:29844.37,eval_cost:370.08,prefix_cost:30214.45,data_read_per_join:520K},used_columns:[birth_date,first_name],attached_condition:((employees.employees.birth_date between 1953-05-01 and 1954-05-01) and (employees.employees.first_name like A%))} } } rootemployees14:45:explainformatjsonselectcount(*)fromemployeeswhere(birth_datebetween1953-05-01and1954-05-01)andfirst_namelikeA%;{query_block: {select_id:1,cost_info: {query_cost:18744.56},table: {table_name:employees,access_type:range,possible_keys:[idx_birth,idx_first],key:idx_first,used_key_parts:[first_name],key_length:58,rows_examined_per_scan:41654,rows_produced_per_join:6221,filtered:14.94,index_condition:(employees.employees.first_name like A%),cost_info: {read_cost:18122.38,eval_cost:622.18,prefix_cost:18744.56,data_read_per_join:874K},used_columns:[birth_date,first_name],attached_condition:(employees.employees.birth_date between 1953-05-01 and 1954-05-01)} } }可以看出Cost值从30214.45降到了18744.56扫描行数从299822降到了41654性能有所提升