《數(shù)據(jù)庫系統(tǒng)》教學(xué)課件

《數(shù)據(jù)庫系統(tǒng)》教學(xué)課件,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫,系統(tǒng),教學(xué),課件 Physical DB DesignJianlin FengSchool of SoftwareSUN YAT-SEN UNIVERSITYcourtesy of Joe Hellerstein for some slidesPhysical DB DesignnQuery optimizer does what it can to use indices,clustering etc.nDataBase Administrator(DBA)is expected to set up physical design well.nGood DBAs understand query optimizers very well.One Key Decision:IndexesnWhich tablesnWhich field(s)should be the search key?nMultiple indexes?nClustering?Index SelectionnOne approach:qConsider most important queries in turn.qConsider best plan using the current indexesqSee if better plan is possible with an additional index.qIf so,create it.nBut consider impact on updates!qIndexes can make queries go faster,updates slower.qRequire disk space,too.Issues to Consider in Index SelectionnAttributes mentioned in a WHERE clause are candidates for index search keys.qRange conditions are sensitive to clustering qExact match conditions dont require clusteringnOr do they?:-)nChoose indexes that benefit many queries nNOTE:only one index can be clustered per relation!qSo choose it wisely!Example 1nB+tree index on D.dname supports Toy selection.qGiven this,index on D.dno is not needed.nB+tree on E.dno allows us to get matching(inner)Emp tuples for each selected(outer)Dept tuple.nWhat if WHERE included:.AND E.age=25?qCould retrieve Emp tuples using index on E.age,then join with Dept tuples satisfying dname selection.nComparable to strategy that used E.dno index.qSo,if E.age index is already created,this query provides much less motivation for adding an E.dno index.SELECT E.ename,D.mgrFROM Emp E,Dept DWHERE E.dno=D.dno AND D.dname=ToyExample 2nAll selections are on Emp so it should be the outer relation in any Index NL join.qSuggests that we build a B+tree index on D.dno.nWhat index should we build on Emp?qB+tree on E.sal could be used,OR an index on E.hobby could be used.qOnly one of these is needed,and which is better depends upon the selectivity of the conditions.nAs a rule of thumb,equality selections more selective than range selections.nHave to understand optimizers to get this right!SELECT E.ename,D.mgrFROM Emp E,Dept DWHERE E.sal BETWEEN 10000 AND 20000 AND E.hobby=Stamps AND E.dno=D.dnoExamples of ClusteringnB+tree index on E.age can be used to get qualifying tuples.qHow selective is the condition?qIs the index clustered?nConsider the GROUP BY query.qIf many tuples have E.age 10,using E.age index and sorting the retrieved tuples may be costly.qClustered E.dno index may be better!nEquality queries and duplicates:qClustering on E.hobby helps!SELECT E.dnoFROM Emp EWHERE E.age40SELECT E.dno,COUNT(*)FROM Emp EWHERE E.age10GROUP BY E.dnoSELECT E.dnoFROM Emp EWHERE E.hobby=Stamps