CMU SCS CMU SCS Administrivia Carnegie Mellon Univ. • HW6 is due right now . Dept. of Computer Science • HW7 is out today 15-415/615 - DB Applications – Phase 1: Wed Nov 11 th – Phase 2: Mon Nov 30th • Recitations (WEH 5302 ): C. Faloutsos – A. Pavlo – Tue Nov 10 th Lecture#18: Physical Database Design – Tue Nov 17 th Faloutsos/Pavlo CMU SCS 15-415/615 2 CMU SCS CMU SCS HW7: CMU “YikYak” Last Class • PHP Web Application • Decomposition – Lossless • Postgres Database – Dependency Preserving • Phase 1: Design Spec • Normal Forms • Phase 2: Implementation – 3NF – BCNF Faloutsos/Pavlo CMU SCS 15-415/615 3 Faloutsos/Pavlo CMU SCS 15-415/615 4
CMU SCS CMU SCS Today’s Class Introduction • Introduction • After ER design, schema refinement, and the view definitions, we have a conceptual • Index Selection and external schema for our database. • Denormalization • The next step is to create the physical • Decomposition design of the database. • Partitioning • Advanced Topics Faloutsos/Pavlo CMU SCS 15-415/615 5 Faloutsos/Pavlo CMU SCS 15-415/615 6 CMU SCS CMU SCS Physical Database Design Physical Database Design • Physical design is tightly linked to query • It is important to understand the optimization application’s workload: – Query optimization is usually a “top down” – What kind of queries/updates does it execute? concept. – How fast is the database growing? – But in this lecture we’ll discuss this from the – What is the desired performance metric? “bottom up” Faloutsos/Pavlo CMU SCS 15-415/615 7 Faloutsos/Pavlo CMU SCS 15-415/615 8
CMU SCS CMU SCS Understanding Queries Understanding Updates • For each query in the workload: • For each update in the workload: – Which relations does it access? – Which attributes are involved in predicates? – Which attributes are retrieved? – How selective are these conditions likely to be? – Which attributes are involved in selection/join – What types of update operations and what conditions? attributes do they affect? – How selective are these conditions likely to be? – How often are records inserted/updated/deleted? Faloutsos/Pavlo CMU SCS 15-415/615 9 Faloutsos/Pavlo CMU SCS 15-415/615 10 CMU SCS CMU SCS Consequences General DBA Advice • Changing a database’s design does not • Modifying the physical design of a magically make every query run faster. database is expensive. – May require you to modify your queries and/or • DBA’s usually do this when the application logic. application demand is low – Typically Sunday mornings. • APIs hide implementation details and can – May have to do it whenever the application help prevent upstream apps from breaking changes. when things change. Faloutsos/Pavlo CMU SCS 15-415/615 11 Faloutsos/Pavlo CMU SCS 15-415/615 12
CMU SCS CMU SCS Today’s Class Index Selection • Introduction • Which relations should have indexes? • Index Selection • What attributes(s) or expressions should be the search key? • Denormalization • What order to use for attributes in index? • Decomposition • How many indexes should we create? • Partitioning • For each index, what kind of an index • Advanced Topics should it be? Faloutsos/Pavlo CMU SCS 15-415/615 13 Faloutsos/Pavlo CMU SCS 15-415/615 14 CMU SCS CMU SCS Example #1 Example #1: Join Clause CREATE TABLE users ( CREATE TABLE locations ( • Examine the attributes in the join clause userID INT , locationID INT, servID VARCHAR , servID VARCHAR, – Is there an index? data VARCHAR , coordX FLOAT, updated DATETIME, coordY FLOAT – What is the cardinality of the attributes? PRIMARY KEY (userId) ); ); SELECT U.*, L.coordX, L.coordY SELECT U.*, L.coordX, L.coordY Get the location coordinates of a service FROM users AS U INNER JOIN locations AS L FROM users AS U INNER JOIN locations AS L for any user with an id greater than some value ON (U.servID = L.servID) ON (U.servID = L.servID) WHERE U.userID > $1 and whose record was updated on a Tuesday. WHERE U.userID > $1 AND EXTRACT (dow FROM U.updated) = 2; AND EXTRACT (dow FROM U.updated) = 2; Faloutsos/Pavlo CMU SCS 15-415/615 15 Faloutsos/Pavlo CMU SCS 15-415/615 16
CMU SCS CMU SCS Example #1: Where Clause Example #1: Output Clause • Examine the attributes in the where clause • Examine the query’s output clause – Is there an index? – What attributes from what tables are needed? – How are they be accessed? SELECT U.*, L.coordX, L.coordY SELECT U.*, L.coordX, L.coordY FROM users AS U INNER JOIN locations AS L FROM users AS U INNER JOIN locations AS L ON (U.servID = L.servID) ON (U.servID = L.servID) WHERE U.userID > $1 WHERE U.userID > $1 AND EXTRACT (dow FROM U.updated) = 2; AND EXTRACT (dow FROM U.updated) = 2; Faloutsos/Pavlo CMU SCS 15-415/615 17 Faloutsos/Pavlo CMU SCS 15-415/615 18 CMU SCS CMU SCS Example #1: Summary Index Selection • Join : U.servID , L.servID • We already have an index on U.userID . • Where : U.userID , U.updated – Why? • What if we created separate indexes for • Output : U.userID, U.servID, U.data, U.servID and L.servID ? U.updated , L.coordX , L.coordY SELECT U.*, L.coordX, L.coordY CREATE INDEX idx_u_servID ON users (servID); FROM users AS U INNER JOIN locations AS L CREATE INDEX idx_l_servID ON locations (servID); ON (U.servID = L.servID) WHERE U.userID > $1 AND EXTRACT (dow FROM U.updated) = 2; Faloutsos/Pavlo CMU SCS 15-415/615 19 Faloutsos/Pavlo CMU SCS 15-415/615 20
CMU SCS CMU SCS Index Selection (2) Index Selection (3) • We still have to look up U.updated . • The query outputs L.coordX and L.coordX . • What if we created another index? • This means that we have to fetch the location record. • This doesn’t help our query. Why? • We can create a covering index. SELECT U.*, L.coordX, L.coordY FROM users AS U INNER JOIN locations AS L CREATE INDEX idx_u_servID ON users (servID); CREATE INDEX idx_u_servID ON users (servID); ON (U.servID = L.servID) WHERE U.userID > $1 CREATE INDEX idx_l_servID ON locations (servID); CREATE INDEX idx_l_servID ON locations ( CREATE INDEX idx_l_servID ON locations (servID); AND EXTRACT (dow FROM U.updated) = 2; servID, coordX, coordY); CREATE INDEX idx_u_updated ON users ( CREATE INDEX idx_u_updated ON users (updated); CREATE INDEX idx_u_updated ON users ( CREATE INDEX idx_l_servID ON locations (servID) EXTRACT (dow FROM updated)); EXTRACT (dow FROM updated)); INCLUDE (coordX, coordY); Faloutsos/Pavlo CMU SCS 15-415/615 21 Faloutsos/Pavlo CMU SCS 15-415/615 22 Only MSSQL CMU SCS CMU SCS Index Selection (4) Index Selection (5) • Can we do any better? • Should we make the index on users a covering index? • Is the index U.servID necessary? Repeat for the – What if U.data is large? • Create a partial index other six days of the week! – Should userID come before servID ? – Do we still need the primary key index? CREATE INDEX idx_u_servID ON users (servID) CREATE INDEX idx_u_servID ON users (servID); WHERE EXTRACT (dow FROM updated) = 2; CREATE INDEX idx_u_everything ON users CREATE INDEX idx_u_everything ON users CREATE INDEX idx_u_everything ON users CREATE INDEX idx_l_servID ON locations ( (servID, userID) (servID, userID, data) (userID, servID) servID, coordX, coordY); WHERE EXTRACT (dow FROM updated) = 2; WHERE EXTRACT (dow FROM updated) = 2; WHERE EXTRACT (dow FROM updated) = 2; CREATE INDEX idx_u_updated ON users ( EXTRACT (dow FROM updated)); Faloutsos/Pavlo CMU SCS 15-415/615 23 Faloutsos/Pavlo CMU SCS 15-415/615 24
CMU SCS CMU SCS Other Index Decisions Today’s Class • What type of index to use? • Introduction – B+Tree, Hash table, Bitmap, R-Tree, Full Text • Index Selection • Denormalization • Decomposition • Partitioning • Advanced Topics Faloutsos/Pavlo CMU SCS 15-415/615 25 Faloutsos/Pavlo CMU SCS 15-415/615 26 CMU SCS CMU SCS Denormalization Game Example #1 CREATE TABLE players ( • Joins can be expensive, so it might be playerID INT PRIMARY KEY , ⋮ better to denormalize two tables back into ); one. CREATE TABLE prefs ( • This is goes against all of the BCNF playerID INT PRIMARY KEY REFERENCES player (playerID) , goodness that we talked about it. data VARBINARY ); – But we have bills to pay, so this is an example where reality conflicts with the theory… Faloutsos/Pavlo CMU SCS 15-415/615 27 Faloutsos/Pavlo CMU SCS 15-415/615 28
Recommend
More recommend
