Index Tuning索引优化.pptVIP

Index Tuning Index An index is a data structure that supports efficient access to data Performance Issues Type of Query Index Data Structure Organization of data on disk Index Overhead Data Distribution Covering Types of Queries Point QuerySELECT balanceFROM accountsWHERE number = 1023; Multipoint QuerySELECT balanceFROM accountsWHERE branchnum = 100; Range QuerySELECT numberFROM accountsWHERE balance 10000; Prefix Match QuerySELECT *FROM employeesWHERE name = ‘Jensen’  and firstname = ‘Carl’  and age 30; Types of Queries Extremal QuerySELECT *FROM accountsWHERE balance =  max(select balance from accounts) Ordering QuerySELECT *FROM accountsORDER BY balance; Grouping QuerySELECT branchnum, avg(balance)FROM accountsGROUP BY branchnum; Join QuerySELECT distinct branch.adresseFROM accounts, branchWHERE  accounts.branchnum = branch.numberand accounts.balance 10000; Index An index is a data structure that supports efficient access to data Search Keys A (search) key is a sequence of attributes. create index i1 on accounts(branchnum, balance); Types of keys Sequential: the value of the key is monotonic with the insertion order (e.g., counter or timestamp) Non sequential: the value of the key is unrelated to the insertion order (e.g., social security number) Data Structures Most index data structures can be viewed as trees. In general, the root of this tree will always be in main memory, while the leaves will be located on disk. The performance of a data structure depends on the number of nodes in the average path from the root to the leaf. Data structure with high fan-out (maximum number of children of an internal node) are thus preferred. B+-Tree A B+-Tree is a balanced tree whose leaves contain a sequence of key-pointer pairs. B+-Tree Performance Tree levels Tree Fanout Size of key Page utilization Tree maintenance Online On inserts On deletes Offline Tree locking Tree root in main memory B+-Tree Performa