愿為雙鴻鵠奮翅起

AdatabaseDirection:PostgreSQL,MySQL,Greenplum,Blog: a2MySQL與PostgreSQLMySQLV5.7什么要改進代價模？MySQLV5.7代價模型有什么變MySQLV5.7代價模型解決了哪些問 MySQLV5.7的代價

365worklogs,added1007MTRtests,fixed2812bug Performance&Scalability:ImprovedInnoDBscalabilityandtemporarytableperformance,enablingfasteronlineandbulkloadoperations,andmore. JSONSupport:WiththenewlyaddedJSONsupportinMySQL,youcannowcombinetheflexibilityofNoSQLwiththestrengthofarelationaldatabase.pGIS:Comple ynewinMySQL5.7andincludingInnoDBspatialindexes,useofBoost.Geometry,alongwithincreasedcompletenessandstandardcompliance. Replicationimprovementsforincreasedavailabilityandperformance.Theyincludemulti-sourcereplication,multi-threadedslaveenhancements,onlineGTIDs,andenhancedsemi-syncreplication. PerformanceSchemadeliveringmuchbetterinsights.We’veaddednumerousnewmonitoringcapabilities,reducedthefootprintandoverhead,andsignificantlyimprovedeaseofusewiththenewSYSSchema. Security:Wearefulfilling“securebydefault”requirementsandmanynewMySQL5.7featureswilluserskeeptheirdatabase Optimizer:Wehaverewrittenlargepartsoftheparser,optimizer,andcostmodel.Thishasimprovedmaintainability,extendability,andperformance. MySQLV5.7的代價什么是代The“cost”inMySQLisan numbercloselyrelatedtothecost ngarandomreadfromaHDD.Atthetime,thiswasanobviouschoicesincethemaingoalwastoefficientlyutilizethediskbandwidth.Inthefuturewewouldprobablyliketorelatecosttosomethingelsethatmakesmoresensetousers,e.g.wallclocktime. MySQLV5.7的代價常規(guī)的代價總代=IO+CPU代價COST=P*a_page_io_time+W*s*P為計劃運行 MySQLV5.7的代價p通常，計算的是IO代價和CPU使使用文件排序時的IO代價計doubleget_merge_cost(ha_rowsnum_elements,ha_rowsnum_buffers,uintelem_size,constCost_model_table*cost_model){constdoubleio_ops=static_cast<double>(num_elements*elem_size)/IO_SIZE;constdoubleio_cost=cost_model->io_block_read_cost(io_ops);constdouble pare_cost(num_elements*log((double)num_buffers)使用使用文件排序時的CPU代價return2*io_cost+使用使用文件排序時的總代價 MySQLV5.7的代價pIO和CPU代價，分別有不同的計Computethecostofaquick_group_min_max_selectforaparticular分組分組操作，利用索引的代價void constCost_model_table*constcost_model=table-cost_est->add_io(cost_model-IO代IO代價的計1.0constdoubletree_traversal_cost=cost_model- CPU代價cost_model-CPU代價 MySQLV5.7的代價constCost_model_table*cost_model){constdoubleio_ops=static_cast<double>(num_elements*elem_size)/constdoublecpu_cost= return2*io_cost+}void constCost_model_table*constcost_model=table-constdoubletree_height=table_records==01.0 constdoublecpu_cost=num_groups*(tree_traversal_cost

計算方不同操作的具體的和CPU代價的計算方 MySQLV5.7的代價MySQL的代價模型（V5.6、 操作的價四個代價計四個代價計算的{doubleio_cost;doublecpu_cost;doublemem_cost;

///<costofI/O///<costofCPU///<costofremote///<memoryused///Returnssumoftime-consumingcosts,i.e.,notcountingmemorycostdoubletotal_cost()const{returnio_cost+cpu_cost+import_cost;}doubleget_io_cost() const{returnio_cost;}內(nèi)存代價暫沒有使doubleget_cpu_cost() const{returncpu_cost;}doubleget_import_cost()const內(nèi)存代價暫沒有使 MySQLV5.7的代價MySQL的代價模型（V5.6、 操作的代uint*buffer_size,Costofmemoryisnotincludedinthetotal_cost()functionandthuswillnotbeconsideredwhencomparingcosts.Still,werecorditinthecostestimateobjectforfutureuse.內(nèi)存代價計內(nèi)存代價計算但沒有 MySQLV5.7的代價MySQL的代價模型（V5.6、總代價=IO代價+CPU代價+ 標(biāo)準(zhǔn)的代價模 MySQL主節(jié) 節(jié)點（MySQL 計算 總 總數(shù)代 代被 據(jù)加

IO減

部 數(shù) 被代 存 PostgreSQL的代價模PostgreSQL的代價模型總代IOCPU代價啟動MySQLMySQL總部數(shù)考慮typedefstruct{}typedefstruct{}/*tagidentifyingscan/joinmethod/*therelationthispathcanbuildParamPathInfo*param_info;/*parameterizationinfo,orNULLifnone/*estimatedsize/costsforpath(seecostsize.cformoreinfo)/*estimatednumberofresulttuples/*costexpendedbeforefetchinganytuples /*totalcost(assumingalltuplesfetched)/* /*sortorderingofpath'soutputsisaListofPathKeynodes;seeabove PostgreSQL的代價模順序掃 PostgreSQL的代價模索引掃索引掃 ToprowDB的代價模ToprowDB的代價模型總代價=IO代價+CPU代價+ 標(biāo)準(zhǔn)的代價模 主節(jié)

節(jié)點 計算 總 總數(shù)代 代被 據(jù)加

IO減

部 數(shù) 被代 存 ToprowDB的代價模 MySQLV5.7什么要改進代價

larger

新的硬件架構(gòu) 與 引擎緊密綁 MySQLV5.7什么要改進代價/*V5.6/*V5.7.5classclass///ThecostofoneI/OstaticdoubleIO_BLOCK_READ_COST(){return1.0;m_io_block_read_cost;///Costconstantforreadingarandomdiskdouble 代價模型，需要與時俱WL#7315:Optimizercostmodel:mainmemorymanagement代價模型，需要與時俱 PostgreSQLV9.4.1支持新 設(shè)備嗎

seq_page_cost=DEFAULT_SEQ_PAGE_COST;random_page_cost=DEFAULT_RANDOM_PAGE_COST;cpu_tuple_cost=DEFAULT_CPU_TUPLE_COST;cpu_operator_cost=DEFAULT_CPU_OPERATOR_COST;{{"random_page_cost",PGC_USERSET,QUERY_TUNING_COST,gettext_noop("Setsthe nner'sestimateofthecostofa"NULL,NULL,NULL{{"cpu_tuple_cost",PGC_USERSET,QUERY_TUNING_COST,gettext_noop("Setsthe nner'sestimateofthecostof"

變量可配支持新 設(shè)NULL,NULL,NULL

"processingeachtuple MySQLV5.7價模型有什么變mysql>select*from | |cost_value| |comment | NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| NULL|2015-09-2315:58:28| |memory_temptable_create_cost NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| | NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| 2rowsinset(0.01ssrrr層 MySQLV5.7價模型有什么變mysql>select*from | |cost_value| |comment | |

NULL|-09-2315:58:28| -09-2315:58:28| 層NULL|2015-09-2315:58:28-09-2315:58:28| -09-2315:58:28| 層NULL|2015-09-2:2r|L|atesforgeneral|memory_temptable_create_cost NULL||

NULL|2015-09-2315:58:28|NULLoperations| NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28|

存儲引擎層 2rowsinset(0.01

Optimizercostestimatesforoperationsspecifictoparticularstorageengines MySQLV5.7價模型有什么變mysql>select*from | |cost_value| |comment | | |memory_temptable_create_cost

NULL|-09-2315:58:28| -09-2315:58:28| serverNULL|-09-2315:58:28| -09-2315:58:28| serverNULL|2015-09-2315:58:28| 1:5:2NU 引擎||

NULL| NULL|2015-09-2315:58:28| 6rowsinset(0.01mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| NULL|2015-09-2315:58:28| ++ | 0|memory_block_read_cost NULL|2015-09-2315:58:28|++ 2rowsinset(0.01

可配置的參數(shù)不同于server MySQLV5.7價模型有什么變mysql.server_cost的參數(shù)可配置含disk_temptable_create_costdisk_temptable_row_costpare_costmemory_temptable_row_costrow_evaluate_cost MySQLV5.7價模型有什么變mysql.server_cost的參數(shù)—什么時候會創(chuàng)建/使用臨時EvaluationofUNIONstatements,withsomeexceptionsdescribed Evaluationofsomeviews,suchthosethatusetheTEMPTABLEalgorithm,UNION,orEvaluationofderivedtables(subqueriesintheFROMTablescreatedforsubqueryorsemi-joinmaterialization(seeSection“Subquery EvaluationofstatementsthatcontainanORDERBYclauseandadifferentGROUPBYclause,orforwhichtheORDERBYorGROUPBYcontainscolumnsfromtablesotherthanthefirsttableinthejoinqueue.EvaluationofDISTINCTcombinedwithORDERBYmayrequireatemporary ForqueriesthatusetheSQL_SMALL_RESULToption,MySQLusesanin-memorytemporarytable,unlessthequeryalsocontainselements(describedlater)thatrequireon-diskEvaluationofmultiple-tableUPDATEEvaluationofGROUP_CONCAT()orCOUNT(DISTINCT) MySQLV5.7價模型有什么變mysql.engine_cost的參數(shù)可配置含從磁 從內(nèi)存的緩存 MySQLV5.7價模型有什么變示例1：修 層的SETcost_value=適用于所有 引WHERE適用于所有 引示例2：為InnoDB引擎設(shè)置INSERTINTO指向某個具體 引VALUES('InnoDB',0,'io_block_read_cost',3.0,CURRENT_TIMESTAMP,'Usinga指向某個具體 引 MySQLV5.7價模型解決了哪些問問題1：HDD換為了SDD怎么mysql>select*from |engine_name|device_type| |cost_value| |comment | 0| 1|2015-09-2315:58:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| SETcost_value=0.1WHEREcost_name=FLUSHmysql>select*from |engine_name|device_type| |cost_value| |comment | 0| 0.1|2015-09-2315:59:28| | 0|memory_block_read_cost NULL|2015-09-2315:58:28| MySQLV5.7價模型解決了哪些問問題1PostgreSQL,HDD換為了SDD怎么# nnerCostConstants#seq_page_cost=#random_page_cost=#cpu_tuple_cost=#cpu_index_tuple_cost=#cpu_operator_cost=#effective_cache_size=

#measuredonanarbitrary#samescaleasabove#samescaleasabove#samescaleasabove#samescaleas MySQLV5.7價模型解決了哪些問問題2：有多個不同 引擎怎么設(shè)置engine_namedevice_type 00 cpu_read_random_index_costNULL sequential_block_read_cost cpu_read_random_index_cost INSERTINTOCURRENT_TIMESTAMP,'UsingaslowerdiskforInnoDB');FLUSHOPTIMIZER_COSTS; MySQLV5.7價模型解決了哪些問問題3：索引的代價計算精度不夠準(zhǔn)確V5.7.4之前的版V5.7.4的版1.99下取整等于1.01下取整等于1.99依舊是1.01依舊是V5.8直方PostgreSQLV5.8直方PostgreSQL已經(jīng)支持直方ToprowDB已經(jīng)支持直方 MySQLV5.7價模型解決了哪些問問