简单分析MySQL中的primary key功能
在5.1.46中优化器在对primarykey的选择上做了一点改动:
Performance:Whilelookingfortheshortestindexforacoveringindexscan,theoptimizerdidnotconsiderthefullrowlengthforaclusteredprimarykey,asinInnoDB.Secondarycoveringindexeswillnowbepreferred,makingfulltablescanslesslikely。
该版本中增加了find_shortest_key函数,该函数的作用可以认为是选择最小keylength的
索引来满足我们的查询。
该函数是怎么工作的:
Whatfind_shortest_keyshoulddoisthefollowing.Iftheprimarykeyisacoveringindex
andisclustered,likeinMyISAM,thenthebehaviortodayshouldremainthesame.Ifthe
primarykeyisclustered,likeinInnoDB,thenitshouldnotconsiderusingtheprimary
keybecausethenthestorageenginewillhavetoscanthroughmuchmoredata.